Database
DBlink Oracle for three databases in SQL Server
Category: Database Author: César Carvalho Date: 3 years ago Comments: 0

DBlink Oracle for three databases in SQL Server

Building access via dblink to three SQL Server databases.
We will use a native technology of the Oracle database, the Heterogeneous Services (HS).
 
Databases:
10.16.0.11 - Source Database Oracle database

10.16.0.15 - SQL Server target database

 

Create a user with the necessary read permissions in the SQL Server environment:

 

User: USR_CONSULTA

Snhea: P!!2012300

 

Use some Oracle/EPEL repository:

 

[root@instance-20220613-1754 yum.repos.d]# cat oracle-linux-ol7.repo

[ol7_latest]

name=Oracle Linux $releasever Latest ($basearch)

baseurl=https://yum.oracle.com/repo/OracleLinux/OL7/latest/$basearch/

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

gpgcheck=1

enabled=1

 

We can use the Microsoft repository too:

 

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo

 

Installing the necessary packages:

 

yum clean all

yum repolist

yum install unixODBC unixODBC-devel freetds -y

 

ODBC installation validation:

 

[root@olaria ~]# odbcinst -j

unixODBC 2.3.7

DRIVERS............: /etc/odbcinst.ini

SYSTEM DATA SOURCES: /etc/odbc.ini

FILE DATA SOURCES..: /etc/ODBCDataSources

USER DATA SOURCES..: /root/.odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

[root@olaria ~]#

 

Creating the configuration init of Heterogeneous Services in Oracle referring to the three SQL Server databases that we will access via dblink:
Name of the databases that we will access in SQL Server: AEQUUS,FINANCIAL,CONTABILIS

 

[oracle@olaria ~]$ cd $ORACLE_HOME/hs/admin/




[oracle@olaria admin]$ cat initAEQUUS.ora

HS_FDS_CONNECT_INFO = AEQUUS

HS_FDS_TRACE_LEVEL = OFF

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so # LIB

set ODBCINI=/etc/odbc.ini




[oracle@olaria admin]$ cat initFINANCIAL.ora

HS_FDS_CONNECT_INFO = FINANCIAL

HS_FDS_TRACE_LEVEL = OFF

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so # LIB

set ODBCINI=/etc/odbc.ini




[oracle@olaria admin]$ cat initCONTABILIS.ora

HS_FDS_CONNECT_INFO = CONTABILIS

HS_FDS_TRACE_LEVEL = OFF

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so # LIB

set ODBCINI=/etc/odbc.ini

 

Create if it doesn’t exist or edit the ODBC configuration file that we installed earlier, each bank must have a connection entry:

 

[root@olaria yum.repos.d]# cat /etc/odbc.ini




[DBC Data Sources]

AEQUUS = MSSQL Server




[AEQUUS]

# Reference driver from the "/etc/odbcinst.ini" file.

# Driver          = FreeTDS

# Or use a direct driver reference, rather than reference

# one in the "/etc/odbcinst.ini" file.

Driver=/usr/lib64/libtdsodbc.so.0

Description     = MSSQL Server

Trace           = No

Server          = 10.16.0.15

Database        = AEQUUS

Port            = 1433

TDS_Version     = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0

VarMaxAsLong = Yes

DisguiseGuid = Yes

text size = 64512

#AnsiNPW=YES

#VarMaxAsLong=YES

#client charset = UTF-8







[ODBC Data Sources]

CONTABILIS = MSSQL Server




[CONTABILIS]

# Reference driver from the "/etc/odbcinst.ini" file.

# Driver          = FreeTDS

# Or use a direct driver reference, rather than reference

# one in the "/etc/odbcinst.ini" file.

Driver=/usr/lib64/libtdsodbc.so.0

Description     = MSSQL Server

Trace           = No

Server          = 10.16.0.15

Database        = CONTABILIS

Port            = 1433

TDS_Version     = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0

VarMaxAsLong = Yes

text size = 64512

#QuotedId=YES

#AnsiNPW=YES

#VarMaxAsLong=YES

#client charset = UTF-8







[ODBC Data Sources]

FINANCIAL = MSSQL Server




[FINANCIAL]

# Reference driver from the "/etc/odbcinst.ini" file.

# Driver          = FreeTDS

# Or use a direct driver reference, rather than reference

# one in the "/etc/odbcinst.ini" file.

Driver=/usr/lib64/libtdsodbc.so.0

Description     = MSSQL Server

Trace           = No

Server          = 10.16.0.15

Database        = FINANCIAL

Port            = 1433

TDS_Version     = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0

VarMaxAsLong = Yes

text size = 64512

#QuotedId=YES

#AnsiNPW=YES

#VarMaxAsLong=YES

#client charset = UTF-8

 

Create if it doesn’t exist or edit the ODBC configuration file that we installed earlier, each bank must have a connection entry:

 

[root@olaria yum.repos.d]# cat /etc/odbc.ini




[DBC Data Sources]

AEQUUS = MSSQL Server




[AEQUUS]

# Reference driver from the "/etc/odbcinst.ini" file.

# Driver          = FreeTDS

# Or use a direct driver reference, rather than reference

# one in the "/etc/odbcinst.ini" file.

Driver=/usr/lib64/libtdsodbc.so.0

Description     = MSSQL Server

Trace           = No

Server          = 10.16.0.15

Database        = AEQUUS

Port            = 1433

TDS_Version     = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0

VarMaxAsLong = Yes

DisguiseGuid = Yes

text size = 64512

#AnsiNPW=YES

#VarMaxAsLong=YES

#client charset = UTF-8







[ODBC Data Sources]

CONTABILIS = MSSQL Server




[CONTABILIS]

# Reference driver from the "/etc/odbcinst.ini" file.

# Driver          = FreeTDS

# Or use a direct driver reference, rather than reference

# one in the "/etc/odbcinst.ini" file.

Driver=/usr/lib64/libtdsodbc.so.0

Description     = MSSQL Server

Trace           = No

Server          = 10.16.0.15

Database        = CONTABILIS

Port            = 1433

TDS_Version     = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0

VarMaxAsLong = Yes

text size = 64512

#QuotedId=YES

#AnsiNPW=YES

#VarMaxAsLong=YES

#client charset = UTF-8







[ODBC Data Sources]

FINANCIAL = MSSQL Server




[FINANCIAL]

# Reference driver from the "/etc/odbcinst.ini" file.

# Driver          = FreeTDS

# Or use a direct driver reference, rather than reference

# one in the "/etc/odbcinst.ini" file.

Driver=/usr/lib64/libtdsodbc.so.0

Description     = MSSQL Server

Trace           = No

Server          = 10.16.0.15

Database        = FINANCIAL

Port            = 1433

TDS_Version     = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0

VarMaxAsLong = Yes

text size = 64512

#QuotedId=YES

#AnsiNPW=YES

#VarMaxAsLong=YES

#client charset = UTF-8

 

Adjust tnsnames.ora with the access entries to the three SQL Server databases that we will use in dblink:

 

[oracle@olaria db_1]$ cat $ORACLE_HOME/network/admin/tnsnames.ora




# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.




AEQUUS =

   (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST=10.16.0.11)(PORT=1521))

      (CONNECT_DATA=(SID=AEQUUS))

      (HS=OK)

    )




CONTABILIS =

   (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST=10.16.0.11)(PORT=1521))

      (CONNECT_DATA=(SID=CONTABILIS))

      (HS=OK)

    )




FINANCIAL =

   (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST=10.16.0.11)(PORT=1521))

      (CONNECT_DATA=(SID=FINANCIAL))

      (HS=OK)

    )

 

Adjust the listener.ora with the access entries to the three SQL Server databases that we will use in the dblink:

 

[oracle@olaria db_1]$ cat $ORACLE_HOME/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.




LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = olaria)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

DEFAULT_SERVICE_LISTENER=orcl

ADR_BASE_LISTENER = /u01/app/oracle







SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = ORCL)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)

      (SID_NAME = ORCL)

    )

        (SID_DESC=

          (SID_NAME=AEQUUS)

          (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)

          (PROGRAM=dg4odbc)

        )




        (SID_DESC=

          (SID_NAME=CONTABILIS)

          (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)

          (PROGRAM=dg4odbc)

        )




        (SID_DESC=

          (SID_NAME=FINANCIAL)

          (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)

          (PROGRAM=dg4odbc)

        )




  )







- Reload listener.ora settings

[oracle@olaria db_1]$ lsnrctl reload




LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 16-JUN-2022 10:16:12

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=olaria)(PORT=1521)))

The command completed successfully

 

Test the access via odbc by the osql utility:

 

osql -S AEQUUS -U USR_CONSULTA -P 12398546




Attempting connection as dblinkdev ...

+ isql AEQUUS USR_CONSULTA 12398546 -v

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL>













osql -S CONTABILIS -U USR_CONSULTA -P 12398546




Attempting connection as dblinkdev ...

+ isql CONTABILIS USR_CONSULTA 12398546 -v

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL>







osql -S FINANCIAL -U USR_CONSULTA -P 12398546




Attempting connection as dblinkdev ...

+ isql FINANCIAL USR_CONSULTA 12398546 -v

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL>

 

Create dblinks and perform access tests:

 

create public database link AEQUUS connect to "USR_CONSULTA" identified by "12398546" using 'AEQUUS';

create public database link CONTABILIS connect to "USR_CONSULTA" identified by "12398546" using 'CONTABILIS';

create public database link FINANCIALARACAJU connect to "USR_CONSULTA" identified by "12398546" using 'FINANCIALARACAJU';

 

Test select dblinks:

 

select "codigo","nome" from IMPACOES@AEQUUS;

select * from ALVARA@CONTABILIS;

select "nu_cnpj","sq_produto","nm_produto" from PRODUTO@FINANCIAL;

 

Note: If any SQL Server database has tables with the varchar(max) data type, we must create views to consume this data, because the varchar(max) data type is problematic with dblink, I could not get around this problem,
this way I created a view, see the example below:

 

CREATE VIEW VEW_IMPACOES

AS 

SELECT codigo,CONVERT(VARCHAR(2000),nome) nome FROM IMPACOES;

 

 

 
César Carvalho – DBA
Contact: https://twitter.com/Cesar_DBA
https://sgbdbrasil.wordpress.com/

LuxOUG – 2022
Category: Cloud,Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 3 years ago Comments: 0

LuxOUG - EMEA Community Tour 2022

 
 
As part of EMEA Community Tour 2022 , we invite everyone to attend our first face-to-face event in Luxembourg.

 

 

 

We will hold our first event in Luxembourg, covering various technologies such as DevOps, Engineered System, Middleware, Cloud and others.
Location: Luxembourg
Date: 05/10/2022
Event Schedule: 9:00 AM – 18:00 PM (CEST)
Atrium Business Park
Address:  25 Rue du puits Romain, 8070 Bertrange.
 
How to reach the event and some additional information about using public transportation here in Luxembourg.  Click here
 

 Register for the event :  Click here

Event Schedule



Presentation slides

Speaker – Christian Gohmann (ACE) :  xTTS – Lesson(s) learned.
 
Speaker – Edelweiss Kammermann (ACED) : FAW in a Nutshell / OAC: ML and AI Features and Integration. 
 
Speaker – Fernando Simon (ACE) : Lesser Known Tips & Tricks of Exadata.
 
Speaker – Kai Yu  (ACED) : Oracle In-database Machine Learning with OML4SQL/OML4Py in Oracle Autonomous Databases  /  Eliminating database I/O latency with Oracle 21c/19c Persistent Memory features.
Speaker – Kosseila Haddanele (ACE) : Script your way into the Cloud using Cloud CLI (OCI/Azure/AWS) and Interactive Shell Scripts.
 
Speaker – Lino Schildenfeld (ACE) : JavaScript in APEX fro PL/SQL developers
Speaker – Nelson Calero (ACED) : Terraform tips and tricks / Oracle databases on Kubernetes 101.
 
Speaker – Patrick Barel (ACED)  : Get to know your program by instrumentation /  A Collection’s a Collection, no matter how small.
Speaker – Philippe Bouvy (Kyndryl)  : Oracle ExaC@C at Kyndryl Luxembourg.
Speaker – Sandesh Rao (Oracle)  : Oracle Database Performance Tuning using oratop /  Analysis of Database Issues using AHF and Machine Learning.
Speaker – Vassilios Kakarakis (Kyndryl)  : Common Challenges in Data Analytics and Data Governance.



Event Photos

WhatsApp Image 2022-10-06 at 16.31.11
WhatsApp Image 2022-10-06 at 16.31.23
WhatsApp Image 2022-10-06 at 16.31.25
WhatsApp Image 2022-10-06 at 16.31.26 (1)
WhatsApp Image 2022-10-06 at 16.31.26 (2)
WhatsApp Image 2022-10-06 at 16.31.26
WhatsApp Image 2022-10-06 at 16.31.27




Sponsor


Export Oracle SQL PLAN Baselines from one database to another
Category: Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 4 years ago Comments: 0

Export Oracle SQL PLAN Baselines from one database to another

 
 
 
Hello everyone, a few weeks ago, during a project, our client requested that during the database migration process we take the SQL PLAN Baseline of the current databases.
 
I found the topic interesting to share with you, how to do this migration of SQL PLAN Baselines from one database to another.

NOTE: The database user must have “administer sql management object” to execute the steps below.

1 . Create a staging table from the source database. A staging table cannot be created in SYS schema.(ORA-19381)

I used the SYSTEM schema.

exec DBMS_SPM.CREATE_STGTAB_BASELINE('MIGBASELINE', MIGUSER);
2. Pack baselines in the source database:
 
Here we can do it in four ways.
 
A) Pack all baselines in the database.
set serveroutput on
DECLARE
x number;
BEGIN
x := DBMS_SPM.PACK_STGTAB_BASELINE('MIGBASELINE', MIGUSER);
dbms_output.put_line(to_char(x) || ' plan baselines packed');
END;
/
B) Pack ALL Baselines Plans of a query
set serveroutput on
DECLARE
x number;
BEGIN
x := DBMS_SPM.PACK_STGTAB_BASELINE('MIGBASELINE', MIGUSER, sql_handle => 'SYS_SQL_58d940ad9b3ac043');
dbms_output.put_line(to_char(x) || ' plan baselines packed');
END;
/
C) Pack a specific Baseline Plan of a Query
set serveroutput on
DECLARE
x number;
BEGIN
x := DBMS_SPM.PACK_STGTAB_BASELINE('MIGBASELINE', MIGUSER, sql_handle => ' SYS_SQL_58d940ad9b3ac043', plan_name => 'SYS_SQL_PLAN_d380ae0c0a76c437' );
dbms_output.put_line(to_char(x) || ' plan baselines packed');
END;

/

 
D) Pack only ACCEPTED Baseline Plans of a Query
 
set serveroutput on
DECLARE
x number;
BEGIN
x := DBMS_SPM.PACK_STGTAB_BASELINE('MIGBASELINE', MIGUSER, sql_handle => ' SYS_SQL_58d940ad9b3ac043', accepted => 'YES' );
dbms_output.put_line(to_char(x) || ' plan baselines packed');
END;
/
3. Export the staging table MIGBASELINE from the source database.

4. Import the staging table MIGBASELINE into the target database.

5. Unpack the baselines in the target database.
set serveroutput on
DECLARE
x number;
BEGIN
x := DBMS_SPM.UNPACK_STGTAB_BASELINE('MIGBASELINE', MIGUSERN);
dbms_output.put_line(to_char(x) || ' plan baselines unpacked');
END;
/
I hope this helps you!!!

Andre Luiz Dutra Ontalba

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited  to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful


How to Install Oracle Restart 21c without ASM or ACFS
Category: Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 4 years ago Comments: 0

How to install Oracle Restart 21c without ASM or ACFS.

This article contains all information related to the install Oracle Restart Database 21c (Linux) without ASM.
I did the test and you can use the same procedure for versions (12.2,18c,19c).
Just change the response_schema for the Grid and Database version you want to use, in the response file.

 

1.  Oracle Database Prerequisites Packages for Oracle Linux

 

Use this procedure to install the Oracle Database prerequisites group package for your Oracle software.
 
The oracle-database-preinstall-21c package installs all the packages required for an Oracle Database and Oracle Grid Infrastructure installation. It also creates the oracle user and the oinstall and dba groups for that user.
 
To install the oracle-database-preinstall-21c package, log in as root, and run the following command on Linux:

 

yum install oracle-database-preinstall-21c

 

2. Installing Oracle Grid Infrastructure Standalone Server for Oracle Linux with Silent Mode

 

Complete these steps to install Oracle Grid Infrastructure for a standalone server and then create a database that is managed by Oracle Restart
 
Install Oracle Grid Infrastructure for a standalone server, which installs Oracle Restart.
 
You should have your network information, storage information, and operating system users and groups available to you before you start the installation. You should also be prepared to run root scripts or provide information to automate root scripts.
 
1 . Log in as the Oracle Restart software owner user (oracle).
 
2. If this is the first time you are installing Oracle software, then create the Oracle base and the Oracle inventory directories as per the Oracle Optimal Flexible Architecture (OFA) recommendations. Specify the correct owner, group, and permissions for these directories.

 

root@dutsDB ~]#  mkdir -p /u01/app/oraInventory

[root@dutsDB ~]#  chown -R oracle:oinstall /u01/app/oracle

[root@dutsDB ~]#  chown -R oracle:oinstall /u01/app/oraInventory

[root@dutsDB ~]#  chmod -R 775 /u01/app

 

3. Download the Oracle Grid Infrastructure for a standalone server installation image files, create the grid home directory, and extract the image files in this grid home directory.

 

[oracle@dutsDB u01]$ mkdir -p /u01/app/oracle/product/21.0.0/grid

[oracle@dutsDB u01]$ cd /u01/app/oracle/product/21.0.0/grid

[oracle@dutsDB grid]$ unzip -q /u01/binarios/Oracle_21_3_Grid.ziprm

[oracle@dutsDB grid]$ rm –rf OPatch

[oracle@dutsDB grid]$ unzip -q /u01/binarios/p6880880_190000_Linux-x86-64.zip

 

4. Prepare the response file grid_setup.rsp.

 

oracle.install.responseFileVersion=/oracle/install/rspfmt_crsinstall_response_schema_v21.0.0

INVENTORY_LOCATION=/u01/app/oraInventory

oracle.install.option=CRS_SWONLY 

ORACLE_BASE=/u01/app

oracle.install.asm.OSDBA=dba

oracle.install.asm.OSASM=oinstall

oracle.install.crs.config.ClusterConfiguration=STANDALONE

 

5. Log in as the Oracle Restart software owner user and run gridSetup.sh with option to applyRU and silent mode:

 

oracle@dutsDB grid]$ ./gridSetup.sh -silent -responseFile /u01/binarios/grid_setup.rsp

Launching Oracle Grid Infrastructure Setup Wizard...




The response file for this session can be found at:

 /u01/app/oracle/product/21.0.0/grid/install/response/grid_2021-09-10_10-55-55AM.rsp




You can find the log of this install session at:

 /tmp/GridSetupActions2021-09-10_10-55-55AM/gridSetupActions2021-09-10_10-55-55AM.log




As a root user, execute the following script(s):

/u01/app/oraInventory/orainstRoot.sh

/u01/app/oracle/product/21.0.0/grid/root.sh


Execute /u01/app/oraInventory/orainstRoot.sh on the following nodes:

[dutsDB]

Execute /u01/app/oracle/product/21.0.0/grid/root.sh on the following nodes:

[dutsDB]


Successfully Setup Software.

Moved the install session logs to:

 /u01/app/oraInventory/logs/GridSetupActions2021-09-10_10-55-55AM

[oracle@dutsDB grid]$

 

6. Execute the script /u01/app/oracle/product/21.0.0/grid/root.sh with root user.

 

[root@dutsDB ~]# /u01/app/oraInventory/orainstRoot.sh

Changing permissions of /u01/app/oraInventory.

Adding read,write permissions for group.

Removing read,write,execute permissions for world.


Changing groupname of /u01/app/oraInventory to oinstall.

The execution of the script is complete.

[root@dutsDB ~]# /u01/app/oracle/product/21.0.0/grid/root.sh

Check /u01/app/oracle/product/21.0.0/grid/install/root_dutsDB_2021-09-10_11-07-57-619366648.log for the output of root script

[root@dutsDB ~]#

[root@dutsDB ~]#

 

7. Execute the script /u01/app/oracle/product/21.0.0/grid/crs/install/roothas.sh with root user.

 

[root@dutsDB ~]# /u01/app/oracle/product/21.0.0/grid/crs/install/roothas.sh

Using configuration parameter file: /u01/app/oracle/product/21.0.0/grid/crs/install/crsconfig_params

2021-09-10 11:08:47: Got permissions of file /u01/app/crsdata/dutsdb/crsconfig: 0775

2021-09-10 11:08:47: Got permissions of file /u01/app/crsdata: 0775

2021-09-10 11:08:47: Got permissions of file /u01/app/crsdata/dutsdb: 0775

The log of current session can be found at:

  /u01/app/crsdata/dutsdb/crsconfig/roothas_2021-09-10_11-08-47AM.log

2021/09/10 11:09:03 CLSRSC-363: User ignored prerequisites during installation

Redirecting to /bin/systemctl restart rsyslog.service

LOCAL ADD MODE

Creating OCR keys for user 'oracle', privgrp 'oinstall'..

Operation successful.

LOCAL ONLY MODE

Successfully accumulated necessary OCR keys.

Creating OCR keys for user 'root', privgrp 'root'..

Operation successful.

CRS-4664: Node dutsdb successfully pinned.

2021/09/10 11:17:27 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'

dutsdb     2021/09/10 11:11:57     /u01/app/crsdata/dutsdb/olr/backup_20210910_113257.olr    

2021/09/10 11:33:32 CLSRSC-327: Successfully configured Oracle Restart for a standalone server




[oracle@dutsDB bin]$ ./crsctl status res -t

--------------------------------------------------------------------------------

Name           Target  State        Server                   State details

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.ons

               OFFLINE OFFLINE      dutsdb                   STABLE

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.cssd

      1        OFFLINE OFFLINE                               STABLE

ora.diskmon

      1        OFFLINE OFFLINE                               STABLE

ora.evmd

      1        ONLINE  ONLINE       dutsdb                   STABLE

--------------------------------------------------------------------------------

[oracle@dutsDB bin]$

 

3. Installing Oracle Database 21c for Oracle Linux with Silent Mode

 

Complete these steps to install Oracle Database 21c.

 

1. Log in as the Oracle software owner user (oracle).
 
2. Download the Oracle Database 21c installation image files, create the oracle home directory, and extract the image files in this oracle home directory.

 

[oracle@dutsDB grid]$ mkdir -p /u01/app/oracle/product/21.0.0/dbhome_1

[oracle@dutsDB grid]$ cd /u01/app/oracle/product/21.0.0/dbhome_1

[oracle@dutsDB dbhome_1]$ unzip -q /u01/binarios/Oracle_21_3_Database.zip

[oracle@dutsDB dbhome_1]$ rm -rf OPatch/

[oracle@dutsDB dbhome_1]$ unzip -q /u01/binarios/p6880880_190000_Linux-x86-64.zip

[oracle@dutsDB dbhome_1]$

 

3.  Prepare the response file.

 

oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v21.0.0

oracle.install.option=INSTALL_DB_SWONLY

UNIX_GROUP_NAME=dba

INVENTORY_LOCATION=/u01/app/oraInventory

ORACLE_HOME=/u01/app/oracle/product/21.0.0/dbhome_1

ORACLE_BASE=/u01/app

oracle.install.db.InstallEdition=EE

oracle.install.db.OSDBA_GROUP=dba

oracle.install.db.OSOPER_GROUP=oinstall

oracle.install.db.OSBACKUPDBA_GROUP=oinstall

oracle.install.db.OSDGDBA_GROUP=dba

oracle.install.db.OSKMDBA_GROUP=dba

oracle.install.db.OSRACDBA_GROUP=dba

 

4. Log in as the Oracle software owner user and run runinstaller with option to applyRU and silent mode:

 

[oracle@dutsDB dbhome_1]$ ./runInstaller -silent -responseFile /u01/binarios/db_setup.rsp -ignorePrereqFailure

Launching Oracle Database Setup Wizard...




The response file for this session can be found at:

 /u01/app/oracle/product/21.0.0/dbhome_1/install/response/db_2021-09-10_12-17-55PM.rsp




You can find the log of this install session at:

 /u01/app/oraInventory/logs/InstallActions2021-09-10_12-17-55PM/installActions2021-09-10_12-17-55PM.log



As a root user, execute the following script(s):

/u01/app/oracle/product/21.0.0/dbhome_1/root.sh


Execute /u01/app/oracle/product/21.0.0/dbhome_1/root.sh on the following nodes:

[dutsDB]


Successfully Setup Software.

 

5. Execute the script /u01/app/oracle/product/21.0.0/dbhome_1/root.sh with root user

 

[root@dutsDB ~]# /u01/app/oracle/product/21.0.0/dbhome_1/root.sh

Check /u01/app/oracle/product/21.0.0/dbhome_1/install/root_dutsDB_2021-09-10_12-33-41-458028241.log for the output of root script

[root@dutsDB ~]#

[root@dutsDB ~]# cat /u01/app/oracle/product/21.0.0/dbhome_1/install/root_dutsDB_2021-09-10_12-33-41-458028241.log

Performing root user operation.




The following environment variables are set as:

    ORACLE_OWNER= oracle

    ORACLE_HOME=  /u01/app/oracle/product/21.0.0/dbhome_1

   Copying dbhome to /usr/local/bin ...

   Copying oraenv to /usr/local/bin ...

   Copying coraenv to /usr/local/bin ...




Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

[root@dutsDB ~]#

 

Now create your database and check if it was added in Oracle Restart.

 

[oracle@dutsDB ~]$ crsctl status res -t

--------------------------------------------------------------------------------

Name           Target  State        Server                   State details

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.ons

               OFFLINE OFFLINE      dutsdb                   STABLE

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.cssd

      1        OFFLINE OFFLINE                               STABLE

ora.diskmon

      1        OFFLINE OFFLINE                               STABLE

ora.duts.db

      1        ONLINE  ONLINE       dutsdb                   Open,HOME=/u01/app/o

                                                             racle/product/21.0.0

                                                             /dbhome_1,STABLE

ora.duts.dutspdb.pdb

      1        ONLINE  ONLINE       dutsdb                   STABLE

ora.evmd

      1        ONLINE  ONLINE       dutsdb                   STABLE

--------------------------------------------------------------------------------

[oracle@dutsDB ~]$




[oracle@dutsDB ~]$ sqlplus




SQL*Plus: Release 21.0.0.0.0 - Production on Fri Sep 10 14:19:51 2021

Version 21.3.0.0.0




Copyright (c) 1982, 2021, Oracle.  All rights reserved.




Enter user-name: / as sysdba




Connected to:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

Version 21.3.0.0.0




SQL> show pdbs




    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 DUTSPDB                        READ WRITE NO

SQL>

 

I hope this helps you!!!
Stay tuned by following on twitter @aontalba and Linkedin
Andre Luiz Dutra Ontalba

 

 

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited  to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful


How to configure Autonomous Data Guard
Category: Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 4 years ago Comments: 0

How to configure Autonomous Data Guard

Now let’s talk a little bit about Oracle Autonomous Data Guard.

 

When you enable Autonomous Data Guard, the system creates a standby database that continuously gets updated with the changes from the primary database.

With Autonomous Data Guard enabled Autonomous Database provides one identical standby database that allows the following, depending on the state of the primary database:

• If your primary database goes down, Autonomous Data Guard converts the standby database to the primary database with minimal interruption. After failover completes, Autonomous Data Guard creates a new standby database for you.

• You can perform a switchover operation, where the primary database becomes the standby database, and the standby database becomes the primary database.
Autonomous Database does not provide access to the standby database. You perform all operations, such as scaling up the OCPU Count and enabling Auto Scaling on the primary database and Autonomous Data Guard then performs the same actions on the standby database. Likewise, you only perform actions such as stopping or restarting the database on the primary database.

 

Autonomous Data Guard Features

 

Autonomous Data Guard monitors the primary database and if the Autonomous Database instance goes down, then the standby instance assumes the role of the primary instance.
The standby database is created in the same region as the primary database. For better resilience, the standby database is provisioned as follows:

 

  • In regions with more than one availability domain, the standby database is provisioned automatically in a different availability domain than the primary database.
 
  • In regions with a single availability domain, the standby database is provisioned automatically on a different physical machine than the primary database.
 
All Autonomous Database features from the primary database are available when the standby instance becomes the primary after the system fails over or after you perform a switchover operation, including the following:

 

  • OML Notebooks: Notebooks and users created in the primary database are available in the standby.

 

  • APEX Data and Metadata: APEX information created in the primary database is copied to the standby.

 

  • ACLs: The Access Control List (ACL) of the primary database is duplicated for the standby.

 

  • Private Endpoint: The private endpoint from the primary database applies to the standby.

 

  • APIs or Scripts: Any APIs or scripts you use to manage the Autonomous Database continue to work without any changes after a failover operation or after you perform a switchover.

 

  • Client Application Connections: Client applications do not need to change their connection strings to connect to the database after a failover to the standby database or after you perform a switchover.

 

  • Wallet Based Connections: You can continue using your existing wallets to connect to the database after a failover to the standby database or after you perform a switchover.

 

  • Database Options: The OCPU Count, Storage, Display Name, Database Name, Auto Scaling, Tags, and Licensing options have the same values after a failover to the standby database or after you perform a switchover.
 
When Autonomous Data Guard is enabled the RTO and RPO numbers are as follows:

 

  • Automatic Failover: the RTO is two (2) minutes and RPO is zero (0).
  • Manual Failover: the RTO is two (2) minutes and RPO is up to five (5) minutes.
 
Notes for enabling Autonomous Data Guard:
To enable Autonomous Data Guard the database version must be Oracle Database 19c or higher.
Autonomous Database generates the Enable Autonomous Data Guard work request. To view the request, under Resources click Work Requests.
While you enable Autonomous Data Guard, when the Peer State field shows Provisioning, the following actions are disabled for the database:
  • Move Resource
  • Stop
  • Restart
  • Restore

 

Enable Autonomous Data Guard

 

1. If you are not logged in to Oracle Cloud Console, log in and navigate to your Autonomous Database.
 
2. Under Autonomous Data Guard section, click Enable to enable the Data Guard feature.
3. In the Enable Autonomous Data Guard dialog, click Enable Autonomous Data Guard.
4. The Autonomous Database Lifecycle State changes to Updatingand on the Details page, under Autonomous Data Guard the Peer State field shows Provisioning. Depending on the size of your primary database this may take several minutes.
When the standby database is being provisioned, the primary database status becomes available and all database activities can continue as enabling Autonomous Data Guard is non-blocking.
When provisioning completes, the Peer State field shows Available.
Test Switchover to a Standby Database
After Autonomous Data Guard is enabled, if you perform a switchover operation the primary database becomes the standby database, and the standby database becomes the primary database, with no data loss. A switchover is typically done to test your application’s failover procedures when Autonomous Data Guard is enabled.
The Oracle Cloud Infrastructure console shows a switchover link in the Peer State field when both the primary database and the standby database are available. That is, the primary database Lifecycle State field shows Available or Stopped and the standby database is available (the Peer State field shows Available).
To perform a switchover, do the following:
1 .On the Details page, under Autonomous Data Guard, in the Peer State field, click Switchover
The database Lifecycle State changes to Updating and the Peer State field shows Role Change in Progress.
When the switchover operation completes, Autonomous Data Guard does the following:
The Primary database goes into the Available state and can be connected to for queries and updates.
The Peer State field will change to Available when the standby is ready. (The standby may go into a Provisioning state first, if necessary, without blocking operations on the Primary.)
You can see the time of the last switchover when you hover over the tooltip icon in the Peer State field.
I hope this helps you!!!
Andre Luiz Dutra Ontalba

 

Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited  to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful


Oracle Restart Database Upgrade 12c to 19c using Autoupgrade
Category: Database Author: Andre Luiz Dutra Ontalba (Board Member) Date: 4 years ago Comments: 0

Oracle Restart Database Upgrade 12c to 19c using Autoupgrade.

Today we are going to see a step by step, how to upgrade an Oracle restart Database 12c to 19c.
 
1.  Software downloads
 JDK 8: https://www.oracle.com/java/technologies/javase/javase-jdk8-downloads.html
Oracle 19.3 Database and Grid Infrastructure:  https://www.oracle.com/database/technologies/oracle-database-software-downloads.html#19c
Database Release Update 19.11: Patch 32545013
Grid Infrastructure Release Update 19.11 : Patch 32545008
Autoupgrade https://support.oracle.com/epmos/faces/DocumentDisplay?id=2485457.1
OPatch : Patch 6880880
 
2. Prerequisites Grid Infrastructure
 
According to the Oracle Document 2539751.1, you must apply the patch to fix the bug 21255373 in the 12.1.0.2 Grid home directory. Check if this patche applied in your environment:

 

opatch lsinventory |grep 21255373
 
If you have not installed it, the 12.1.0.2.190416 GI PSU patch 29176115 contains the fix for this bug.
 
3. Upgrade Grid Infrastructure to 19.11
Create a directory on Oracle Grid Infrastructure
mkdir -p /u01/app/grid/product/19.3.0/grid
 
Copy the 19c grid software to the folder and unzip it:
cd /u01/app/grid/product/19.3.0/grid

unzip -q /tmp/binaries/LINUX.X64_193000_db_home.zip

 

3.1   Pre-requisites checks
 
According to Oracle document 1457357.1, the user that owns Grid needs to run the Orachk tool.
Make sure to download the latest version of Orachk from AHF , then run the following commands:
cd /u01/app/grid/product/19.3.0/grid/suptools/orachk

export GRID_HOME= /u01/app/grid/product/19.3.0/grid

export RAT_PROMPT_WAIT_TIMEOUT=15

export RAT_ORACLE_HOME=/u01/app/grid/12.1.0

export RAT_DB=12.1.0.2.0

cd /u01/app/grid/product/19.3.0/grid/suptools/orachk

./orachk -u -o pre -profile clusterware,as


This process generates an HTML report as shown in the following image:
Make sure to review the report for all failed, critical, and warning checks and resolve them before you move to the next step.
3.3 Install GI RU 19.11
You need to apply the patch 32545008 in the 19c home directory of the Grid infrastructure.
cd /u01/app/grid/product/19.3.0/grid

 

./gridSetup.sh -silent -applyRU /tmp/binaries/p32545008_19000.zip
 
Finally, it asks to run rootupgrade.sh. Do not run the script yet because you need to run it at the end of the Upgrade.

 

3.4  Validate Grid Infrastructure
Log in as the Grid OS owner user and run the cluster verification utility with the following commands:
cd /u01/app/grid/product/19.3.0/grid/

 

./runcluvfy.sh stage -pre hacfg -fixup -verbose

Verifying node application existence ...PASSED
Verifying check incorrectly sized ASM disks ...PASSED
Verifying ASM disk group free space ...PASSED
Verifying network configuration consistency checks ...PASSED
Verifying file system mount options for path GI_HOME ...PASSED
Verifying /boot mount ...PASSED
Verifying OLR Integrity ...PASSED
Verifying Verify that the ASM instance was configured using an existing ASM parameter file. ...PASSED
Verifying User Equivalence ...PASSED
Verifying RPM Package Manager database ...INFORMATION (PRVG-11250)
Verifying Network interface bonding status of private interconnect network interfaces ...PASSED
Verifying /dev/shm mounted as temporary file system ...PASSED
Verifying file system mount options for path /var ...PASSED
Verifying DefaultTasksMax parameter ...PASSED
Verifying zeroconf check ...PASSED
Verifying ASM filter driver configuration ...PASSED
Verifying Systemd login manager IPC parameter ...PASSED
Verifying Kernel retpoline support ...PASSED
3.5  Upgrade Oracle Grid Infrastructure
Before starting the Upgrade, run the following command to bring down the all database running and ensure that the grid services are running on the server. Make sure cluster upgrade status is normal:
unset ORACLE_BASE
unset ORACLE_HOME
unset ORACLE_SID

 

cd /u01/app/grid/product/19.3.0/grid

./gridsetup.sh -silent -ignorePrerequisites -responseFile /u01/app/grid/product/19.3.0/grid/install/response/duts_upgrade.rsp

 

 
Finally, it asks to run rootupgrade.sh. Do run the script to finish the Upgrade.
 
At this point, the process upgrades Grid to 19c, and all the cluster services are running.
crsctl query has softwareversion

 

4. Upgrade Oracle Database 12c to 19.11

 

4.1  Install Oracle 19.3

 

Create a directory on Oracle Database

 

mkdir -p /u01/app/oracle/product/19.3.0/dbhome_1

 

Copy the 19c Oracle Database software to the folder and unzip it:

 

cd /u01/app/oracle/product/19.3.0/dbhom_1    

 

unzip -q /tmp/binaries/LINUX_X64_193000_db_home.zip
 
4.2  Apply Oracle Database RU – 19.11

 

You need to apply the patch 31281355 in the 19c home directory.  

 

cd /u01/app/oracle/product/19.3.0/dbhome_1

 

./runInstaller -silent -applyRU /tmp/binaries/p32545013_19000.zip -responseFile /u01/app/oracle/product/19.3.0/oracle/install/response/duts_upgrade.rsp

 

Finally, it asks to run rootupgrade.sh. Do run the script to finish the Upgrade.

 

4.3  Upgrade from 12c to 19c using Autoupgrade

 

For the Upgrade we’ll use the autoupgrade utility.
Create a directory on Oracle Database

 

mkdir -p /u01/app/oracle/autoupgrade

 

Copy the autoupgrade.jar to the folder:

 

cd /u01/app/oracle/autoupgrade

 

cp /tmp/binaries/autoupgrade.jar .
Create a sample file to use in autoupgrade:

 

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar autoupgrade.jar -create_sample_file config

 

mv sample_file.cfg 12c_19c.cfg

vi 12c_19c.cfg

#Global configurations
#Autoupgrade's global directory, non-job logs generated,
#temp files created and other autoupgrade files will be
#send here
global.autoupg_log_dir=/u01/app/oracle/autoupgrade   
#
# Database number 1
#
upg1.dbname=DUTS
upg1.start_time=NOW
upg1.source_home=/u01/app/oracle/product/12.1.0/dbhome_1
upg1.target_home=/u01/app/oracle/product/19.3.0/dbhome_1
upg1.sid=DUTS
Run autoupgrade in analyze mode:

 

cd /u01/app/oracle/autoupgrade

 

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar autoupgrade.jar -12c_19c.cfg -mode analyze
Note the log files which have been created for each database – as we have only run autoupgrade with the Analyze option, the only directory which is created is prechecks directory.

 

For each database which has been analyzed, we can review the HTML file which lists the pre-check warnings and recommendations.
 
Run autoupgrade in fixups mode:

 

cd /u01/app/oracle/autoupgrade

 

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar autoupgrade.jar -12c_19c.cfg -mode fixups

 

Run autoupgrade in deploy mode:

 

cd /u01/app/oracle/autoupgrade

 

$ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar autoupgrade.jar -12c_19c.cfg -mode deploy
After completing the migration review the HTML file that lists the items that have been updated update.

 

Change the compatible of Database
alter system set compatible =’19.0.0.0’scope=spfile;
 
Check if the restore point is removed by autoupgrade tool.

 

I hope this helps you!!!

 

Stay tuned, following on twitter @aontalba and on Linkedin

 

André Ontalba

 

Disclaimer: “The postings on this site are my own and don’t necessarily represent my actual employer positions, strategies or opinions. The information here was edited to be useful for general purposes. Specific data and identifications were removed to allow reach generic audience and be useful.”

 


Do you NID to rename an Oracle DB on ASM?
Category: Database Author: Rodrigo Mufalani (Board Member) Date: 5 years ago Comments: 0

Do you NID to rename an Oracle DB on ASM?

Hello, my friends, it’s been a while since my last post. By the end of 2020, there were some very exciting days, with new projects and new challenges, and also I had some time off, dedicated to my family and to me. As 2021 is in the very beginning, I would like to wish you, reading this, a Happy new year, if I didn’t do yet, through my social media.
Well, let’s get into the subject of this post!!! Sometimes, we create a database, deliver it to the customer and people start to use it immediately and after some days or months of usage, they discover the database must be named in another way and request to change. This is a normal thing and that’s why Oracle provides the NID utility. The title of this post was not a typo.
The utilization of NID utility is pretty simple and straightforward, basically we will put the database on MOUNT MODE and issue the utility connecting to the database we intend to change the name and as a parameter a new name using DBNAME parameter. Just to get more familiar with NID utility you can issue the help as I issued below:

 

[oracle@oel82 ~]$ nid help=y




DBNEWID: Release 19.0.0.0.0 - Production on Wed Jan 20 20:13:31 2021




Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.




Keyword     Description                    (Default)

----------------------------------------------------

TARGET      Username/Password              (NONE)

DBNAME      New database name              (NONE)

LOGFILE     Output Log                     (NONE)

REVERT      Revert failed change           NO

SETNAME     Set a new database name only   NO

APPEND      Append to output log           NO

HELP        Displays these messages        NO

 

We gonna see in the future steps of this blogpost the utilization of NID and also how to perform this situation when your database is running on ASM. We can use two approaches if we created the database using DBCA and not created before the directories on ASM. Note: If the directory is created by system you cannot rename it!!!
I’ve checked for that on 19c documentation and still true. For those who already read this post Renaming directory on ASM. So, my advice is, create the directories on ASM manually with the name of the database as I will do later here to allows you to rename the directory, which would be the third approach for this current post. So, let’s do it for a case where we didn’t create the directory manually and DBCA flagged on ASM the directories as SYSTEM created. Like the picture below:

 

 

As you noticed, I have created a database named orcl and we will rename it to prdorcl (don’t judge me, I was running out of good names). My database name will be change from orcl to prdorcl.

 

As I mentioned before, the database must be on MOUNT mode to use NID

 

[oracle@oel82 ~]$ sqlplus "/as sysdba"




SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 20 20:26:25 2021

Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.




Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0




SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount

ORACLE instance started.




Total System Global Area 2415917880 bytes

Fixed Size                  8899384 bytes

Variable Size             520093696 bytes

Database Buffers         1879048192 bytes

Redo Buffers                7876608 bytes

Database mounted.

SQL>

 

With the database mounted, we can apply the NID using this command line

 

[oracle@oel82 ~]$ nid target=/ dbname=prdorcl logfile=prdorcl.log







[oracle@oel82 ~]$ cat prdorcl.log




DBNEWID: Release 19.0.0.0.0 - Production on Wed Jan 20 20:29:57 2021




Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.




Connected to database ORCL (DBID=1590110414)




Connected to server version 19.8.0




Control Files in database:

    +DATA/ORCL/CONTROLFILE/current.261.1062308079

    +DATA/ORCL/CONTROLFILE/current.260.1062308079




Changing database ID from 1590110414 to 2783918790

Changing database name from ORCL to PRDORCL

    Control File +DATA/ORCL/CONTROLFILE/current.261.1062308079 - modified

    Control File +DATA/ORCL/CONTROLFILE/current.260.1062308079 - modified

    Datafile +DATA/ORCL/DATAFILE/system.256.106230789 - dbid changed, wrote new name

    Datafile +DATA/ORCL/DATAFILE/sysaux.257.106230794 - dbid changed, wrote new name

    Datafile +DATA/ORCL/DATAFILE/undotbs1.258.106230797 - dbid changed, wrote new name

    Datafile +DATA/ORCL/DATAFILE/users.259.106230798 - dbid changed, wrote new name

    Datafile +DATA/ORCL/TEMPFILE/temp.268.106230810 - dbid changed, wrote new name

    Control File +DATA/ORCL/CONTROLFILE/current.261.1062308079 - dbid changed, wrote new name

    Control File +DATA/ORCL/CONTROLFILE/current.260.1062308079 - dbid changed, wrote new name

    Instance shut down




Database name changed to PRDORCL.

Modify parameter file and generate a new password file before restarting.

Database ID for database PRDORCL changed to 2783918790.

All previous backups and archived redo logs for this database are unusable.

Database is not aware of previous backups and archived logs in Recovery Area.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

 

We saw the process was easy and quick to apply, don’t be afraid, but NID utility will shutdown your database and later on we will need to open using resetlogs.
Now as I haven’t created a pfile or a spfile with the new name I will fail to start the instance

 

[oracle@oel82 ~]$ export ORACLE_SID=prdorcl

[oracle@oel82 ~]$ sqlplus "/as sysdba"




SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 20 20:32:09 2021

Version 19.8.0.0.0




Copyright (c) 1982, 2020, Oracle.  All rights reserved.




Connected to an idle instance.




SQL> startup mount

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/19.8.0/db_1/dbs/initprdorcl.ora'

 

This orcl database was created using DBCA, so the spfile is stored on ASM by default

 

[oracle@oel82 dbs]$ srvctl config database -d orcl

Database unique name: orcl

Database name: orcl

Oracle home: /u01/app/oracle/product/19.8.0/db_1

Oracle user: oracle

Spfile: +DATA/ORCL/PARAMETERFILE/spfile.269.1062310177

Password file:

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Disk Groups: DATA

Services:

OSDBA group: oinstall

OSOPER group: oinstall

Database instance: orcl

 

We could identify the path of spfile from above command, so now I can create a pfile, edit it and start my new instance with name prdorcl.

 

[oracle@oel82 dbs]$ sqlplus "/as sysdba"




SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 20 20:34:22 2021

Version 19.8.0.0.0




Copyright (c) 1982, 2020, Oracle.  All rights reserved.




Connected to an idle instance.




SQL> create pfile='/u01/app/oracle/product/19.8.0/db_1/dbs/initprdorcl.ora' from spfile='+DATA/ORCL/PARAMETERFILE/spfile.269.1062310177';




File created.

 

So, just as reference, the original pfile looks like this… except because I removed dynamic memory parameter from the top of the file. There are some parameters do change, audit_file_dest, control_files, db_name and dispatchers. We will see the new file later on (wait for it).

 

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='19.0.0'

*.control_files='+DATA/ORCL/CONTROLFILE/current.261.1062308079','+DATA/ORCL/CONTROLFILE/current.260.1062308079'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_name='orcl'

*.db_recovery_file_dest='+DATA'

*.db_recovery_file_dest_size=8256m

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.local_listener='LISTENER_ORCL'

*.open_cursors=300

*.pga_aggregate_target=767m

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=2301m

*.undo_tablespace='UNDOTBS1'

 

As I mentioned prior, in the beginning of the post. It’s strongly recommendable to create the path on ASM manually.

 

[oracle@oel82 dbs]$ . oraenv <<< +ASM

ORACLE_SID = [+ASM] ? The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oel82 dbs]$




[oracle@oel82 dbs]$ asmcmd

ASMCMD> cd DATA

mkdir PRDORCL

mkdir PRDORCL/CONTROLFILE

mkdir PRDORCL/DATAFILE

mkdir PRDORCL/PARAMETERFILE

mkdir PRDORCL/ONLINELOG

mkdir PRDORCL/TEMPFILE

 

Now I have the new path created by me and not flagged as “SYSTEM”

 

 

It’s time to edit my new pfile to reflect the new database name and configurations

 

*.audit_file_dest='/u01/app/oracle/admin/prdorcl/adump'

*.audit_trail='db'

*.compatible='19.0.0'

*.control_files='+DATA/PRDORCL/CONTROLFILE/control01.ctl','+DATA/PRDORCL/CONTROLFILE/control02.ctl'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_name='prdorcl'

*.db_recovery_file_dest='+DATA'

*.db_recovery_file_dest_size=8256m

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=prdorclXDB)'

*.local_listener='LISTENER_PRDORCL'

*.open_cursors=300

*.pga_aggregate_target=767m

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=2301m

*.undo_tablespace='UNDOTBS1'

 

Create the folder to store audit files on OS

 

[oracle@oel82 dbs]$ mkdir -p /u01/app/oracle/admin/prdorcl/adump

 

Change your tnsnames to reflect the new names

 

from:




LISTENER_ORCL =

  (ADDRESS = (PROTOCOL = TCP)(HOST = oel82.fritz.box)(PORT = 1521))




ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oel82.fritz.box)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )




to:




LISTENER_PRDORCL =

  (ADDRESS = (PROTOCOL = TCP)(HOST = oel82.fritz.box)(PORT = 1521))







PRDORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oel82.fritz.box)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = prdorcl)

    )

  )

 

Edit the /etc/oratab file to reflect the new name

 

+ASM:/u01/app/19.8.0/grid:N             # line added by Agent

prdorcl:/u01/app/oracle/product/19.8.0/db_1:N           # line added by Agent

 

I’m about to start the instance and restore the controlfiles to the destination I put on parameter controlfiles and then open the database with resetlogs.

 

SQL> show parameter control_files




NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_files                        string      +DATA/PRDORCL/CONTROLFILE/cont

                                                 rol01.ctl, +DATA/PRDORCL/CONTR

                                                 OLFILE/control02.ctl

 

Exit from sqlplus and login on RMAN to restore the controlfiles and open the database

 

[oracle@oel82 dbs]$ export ORACLE_SID=prdorcl

[oracle@oel82 dbs]$ rman target /




Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jan 20 20:57:02 2021

Version 19.8.0.0.0




Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.




connected to target database (not started)




RMAN> startup nomount




Oracle instance started




Total System Global Area    2415917880 bytes




Fixed Size                     8899384 bytes

Variable Size                520093696 bytes

Database Buffers            1879048192 bytes

Redo Buffers                   7876608 bytes




RMAN> restore controlfile from '+DATA/ORCL/CONTROLFILE/current.261.1062308079';




Starting restore at 20-JAN-21

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=261 device type=DISK




channel ORA_DISK_1: copied control file copy

output file name=+DATA/PRDORCL/CONTROLFILE/control01.ctl

output file name=+DATA/PRDORCL/CONTROLFILE/control02.ctl

Finished restore at 20-JAN-21




RMAN> alter database open resetlogs;




Statement processed

 

The controlfiles were restored where we desired and we can double check it on asmcmd as well.

 

[oracle@oel82 dbs]$ . oraenv <<< +ASM

ORACLE_SID = [prdorcl] ? The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oel82 dbs]$

[oracle@oel82 dbs]$ asmcmd

ASMCMD> cd data

ASMCMD> ls

ORCL/

PRDORCL/

ASMCMD> cd PRDORCL

ASMCMD> cd CONTROLFILE

ASMCMD> ls -l

Type         Redund  Striped  Time             Sys  Name

CONTROLFILE  UNPROT  FINE     JAN 20 06:00:00  N    control01.ctl => +DATA/PRDORCL/CONTROLFILE/current.270.1062312847

CONTROLFILE  UNPROT  FINE     JAN 20 06:00:00  N    control02.ctl => +DATA/PRDORCL/CONTROLFILE/current.271.1062313075

CONTROLFILE  UNPROT  FINE     JAN 20 06:00:00  Y    current.270.1062312847

CONTROLFILE  UNPROT  FINE     JAN 20 06:00:00  Y    current.271.1062313075

 

And you probably is questioning yourself now. OK, good, the controlfiles are there, but. What about the datafiles?

 

ASMCMD> cd ..

ASMCMD> cd datafile

ASMCMD> ls

ASMCMD>

ASMCMD>

ASMCMD> pwd

+data/PRDORCL/datafile

ASMCMD> ls -l

ASMCMD>

 

  1. Approach Number #1 – Aliases
Nothing is on datafiles folder. Do you rememer when I said we could use two approaches on this case? Well, I will use the approach documented on Doc Id 564993.1 using aliases as the first approach. In this approach, the files still on the same place, but we create aliases and then we can safely rename the existing datafiles to the folder with the correct name (PRDORCL). This way is recommended when you don’t care about having multiple folders on ASM and you cannot afford a huge downtime.
I don’t need to remember you, we cannot remove the original folder without deleting all your datafiles.
Let’s take a look on the situation now. I have my controlfiles on the correct path and also redologs, when I issued open resetlogs it already created new logs for me on the correct path.

 

[oracle@oel82 dbs]$ . oraenv <<< prdorcl

ORACLE_SID = [prdorcl] ? The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oel82 dbs]$

[oracle@oel82 dbs]$ sqlplus "/as sysdba"




SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 20 21:05:48 2021

Version 19.8.0.0.0




Copyright (c) 1982, 2020, Oracle.  All rights reserved.







Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0




SQL> select name from v$datafile;




NAME

--------------------------------------------------------------------------------

+DATA/ORCL/DATAFILE/system.256.1062307897

+DATA/ORCL/DATAFILE/sysaux.257.1062307943

+DATA/ORCL/DATAFILE/undotbs1.258.1062307979

+DATA/ORCL/DATAFILE/users.259.1062307981




SQL> select name from v$tempfile;




NAME

--------------------------------------------------------------------------------

+DATA/ORCL/TEMPFILE/temp.268.1062308105







SQL> select member from v$logfile;




MEMBER

--------------------------------------------------------------------------------

+DATA/PRDORCL/ONLINELOG/group_3.267.1062313129

+DATA/PRDORCL/ONLINELOG/group_3.265.1062313131

+DATA/PRDORCL/ONLINELOG/group_2.266.1062313127

+DATA/PRDORCL/ONLINELOG/group_2.264.1062313129

+DATA/PRDORCL/ONLINELOG/group_1.263.1062313125

+DATA/PRDORCL/ONLINELOG/group_1.262.1062313127




6 rows selected.




SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

I brought the database down to rename the datafiles, but first we need to create the aliases connected on ASM instance via sqlplus “/ as sysasm”

 

[oracle@oel82 dbs]$ . oraenv <<< +ASM

ORACLE_SID = [prdorcl] ? The Oracle base remains unchanged with value /u01/app/oracle

[oracle@oel82 dbs]$

[oracle@oel82 dbs]$ sqlplus "/as sysasm"




SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 20 21:49:27 2021

Version 19.8.0.0.0




Copyright (c) 1982, 2020, Oracle.  All rights reserved.







Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0




SQL> alter diskgroup DATA add alias '+DATA/PRDORCL/DATAFILE/system01.dbf' for  '+DATA/ORCL/DATAFILE/system.256.1062307897';




Diskgroup altered.




SQL> alter diskgroup DATA add alias '+DATA/PRDORCL/DATAFILE/sysaux01.dbf' for  '+DATA/ORCL/DATAFILE/sysaux.257.1062307943';SQL>




Diskgroup altered.




SQL> alter diskgroup DATA add alias '+DATA/PRDORCL/DATAFILE/undotbs1.dbf' for  '+DATA/ORCL/DATAFILE/undotbs1.258.1062307979';




Diskgroup altered.




SQL> alter diskgroup DATA add alias '+DATA/PRDORCL/DATAFILE/users01.dbf'  for  '+DATA/ORCL/DATAFILE/users.259.1062307981';




Diskgroup altered.




SQL> alter diskgroup DATA add alias '+DATA/PRDORCL/TEMPFILE/temp01.dbf' for '+DATA/ORCL/TEMPFILE/temp.268.1062308105';




Diskgroup altered.

 

Once the aliases were created, back to the database instance on MOUNT mode and rename all the datafiles to point to the aliases recently created.

 

SQL> alter database rename file '+DATA/ORCL/DATAFILE/system.256.1062307897'   to '+DATA/PRDORCL/DATAFILE/system01.dbf' ;




Database altered.




SQL> alter database rename file '+DATA/ORCL/DATAFILE/sysaux.257.1062307943'   to '+DATA/PRDORCL/DATAFILE/sysaux01.dbf';




Database altered.




SQL> alter database rename file '+DATA/ORCL/DATAFILE/undotbs1.258.1062307979' to '+DATA/PRDORCL/DATAFILE/undotbs1.dbf' ;




Database altered.




SQL> alter database rename file '+DATA/ORCL/DATAFILE/users.259.1062307981'    to '+DATA/PRDORCL/DATAFILE/users01.dbf';




Database altered.




SQL> alter database rename file '+DATA/ORCL/TEMPFILE/temp.268.1062308105' to '+DATA/PRDORCL/TEMPFILE/temp01.dbf';




Database altered.




SQL> alter database open;




Database altered.

 

Putting the spfile back to ASM on the correct PATH

 

[oracle@oel82 ~]$ sqlplus "/as sysdba"




SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 20 22:38:02 2021

Version 19.8.0.0.0




Copyright (c) 1982, 2020, Oracle.  All rights reserved.







Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0




SQL> create spfile='+DATA/PRDORCL/PARAMETERFILE/spfileprdorcl.ora' from pfile='/u01/app/oracle/product/19.8.0/db_1/dbs/initprdorcl.ora';




File created.

 

Adding the new database on Oracle Restart

 

[oracle@oel82 ~]$ srvctl add database -db prdorcl -oraclehome /u01/app/oracle/product/19.8.0/db_1 -spfile '+DATA/PRDORCL/PARAMETERFILE/spfileprdorcl.ora'

[oracle@oel82 ~]$ srvctl start database -db prdorcl

[oracle@oel82 ~]$ crsctl stat res -t

--------------------------------------------------------------------------------

Name           Target  State        Server                   State details

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.DATA.dg

               ONLINE  ONLINE       oel82                    STABLE

ora.LISTENER.lsnr

               ONLINE  ONLINE       oel82                    STABLE

ora.asm

               ONLINE  ONLINE       oel82                    Started,STABLE

ora.ons

               OFFLINE OFFLINE      oel82                    STABLE

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.cssd

      1        ONLINE  ONLINE       oel82                    STABLE

ora.diskmon

      1        OFFLINE OFFLINE                               STABLE

ora.evmd

      1        ONLINE  ONLINE       oel82                    STABLE

ora.orcl.db

      1        OFFLINE OFFLINE                               STABLE

ora.prdorcl.db

      1        ONLINE  ONLINE       oel82                    Open,HOME=/u01/app/o

                                                             racle/product/19.8.0

                                                             /db_1,STABLE

--------------------------------------------------------------------------------

 

And finally removing the old reference for the old database on Oracle Restart

 

[oracle@oel82 ~]$ srvctl remove database -db orcl

Remove the database orcl? (y/[n]) y

[oracle@oel82 ~]$

 

Adjusting the listener on Grid Home to register my new database name

 

[oracle@oel82 ~]$ cd $ORACLE_HOME

[oracle@oel82 grid]$ cd network/admin/

[oracle@oel82 admin]$ vi listener.ora




[oracle@oel82 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/19.8.0/grid/network/admin/listener.ora

# Generated by Oracle configuration tools.




#Backup file is  /u01/app/oracle/crsdata/oel82/output/listener.ora.bak.oel82.oracle line added by Agent




SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = prdorcl)

      (ORACLE_HOME = /u01/app/oracle/product/19.8.0/db_1)

      (SID_NAME = prdorcl)

    )

  )




VALID_NODE_CHECKING_REGISTRATION_LISTENER = ON




LISTENER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oel82.fritz.box)(PORT = 1521))

  )




ADR_BASE_LISTENER = /u01/app/oracle




ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

 

Then restart the listener

 

[oracle@oel82 admin]$ srvctl stop listener

[oracle@oel82 admin]$ srvctl start listener

[oracle@oel82 admin]$ lsnrctl status




LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 20-JAN-2021 22:49:46




Copyright (c) 1991, 2020, Oracle.  All rights reserved.




Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel82.fritz.box)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production

Start Date                20-JAN-2021 08:49:20

Uptime                    0 days 0 hr. 0 min. 25 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/19.8.0/grid/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/oel82/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel82)(PORT=1521)))

Services Summary...

Service "prdorcl" has 1 instance(s).

  Instance "prdorcl", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[oracle@oel82 admin]$

 

Approach Number #2 – Copy
In this approach you have more downtime because you need to copy the datafiles from one folder to another one. This will physically move the data to the destination folder, so we can remove the source folder once we finished. If downtime is not so important and your database is not so big you might have enough space to do it using backup as copy from rman, or you could do it tablespace by tablespace, in this case you don’t need to have the double of your database size in storage to perform this procedure.
I’m gonna show to you how to use copy command and I’m not using archivelog mode for this test database, I will do the commands offline, I will copy datafile per datafile to the destination folder using rman copy command.
Note my commands are poiting to the aliases because I tested the #1 approach before to execute this one

 

RMAN> copy datafile '+DATA/PRDORCL/DATAFILE/system01.dbf' to '+DATA';




Starting backup at 20-JAN-21

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+DATA/PRDORCL/DATAFILE/system01.dbf

output file name=+DATA/PRDORCL/DATAFILE/system.272.1062318091 tag=TAG20210120T142128 RECID=1 STAMP=1062339697

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16

Finished backup at 20-JAN-21




Starting Control File Autobackup at 20-JAN-21

piece handle=+DATA/PRDORCL/AUTOBACKUP/2021_01_20/n_1062339305.273.1062318107 comment=NONE

Finished Control File Autobackup at 20-JAN-21




RMAN> copy datafile '+DATA/PRDORCL/DATAFILE/sysaux01.dbf' to '+DATA';




Starting backup at 20-JAN-21

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=+DATA/PRDORCL/DATAFILE/sysaux01.dbf

output file name=+DATA/PRDORCL/DATAFILE/sysaux.274.1062318281 tag=TAG20210120T142439 RECID=2 STAMP=1062339885

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

Finished backup at 20-JAN-21




Starting Control File Autobackup at 20-JAN-21

piece handle=+DATA/PRDORCL/AUTOBACKUP/2021_01_20/n_1062339305.275.1062318289 comment=NONE

Finished Control File Autobackup at 20-JAN-21




RMAN> copy datafile '+DATA/PRDORCL/DATAFILE/undotbs1.dbf' to '+DATA';




Starting backup at 20-JAN-21

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=+DATA/PRDORCL/DATAFILE/undotbs1.dbf

output file name=+DATA/PRDORCL/DATAFILE/undotbs1.276.1062318293 tag=TAG20210120T142453 RECID=3 STAMP=1062339899

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08

Finished backup at 20-JAN-21




Starting Control File Autobackup at 20-JAN-21

piece handle=+DATA/PRDORCL/AUTOBACKUP/2021_01_20/n_1062339305.277.1062318305 comment=NONE

Finished Control File Autobackup at 20-JAN-21




RMAN> copy datafile '+DATA/PRDORCL/DATAFILE/users01.dbf' to '+DATA';




Starting backup at 20-JAN-21

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00007 name=+DATA/PRDORCL/DATAFILE/users01.dbf

output file name=+DATA/PRDORCL/DATAFILE/users.278.1062318311 tag=TAG20210120T142509 RECID=4 STAMP=1062339910

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 20-JAN-21




Starting Control File Autobackup at 20-JAN-21

piece handle=+DATA/PRDORCL/AUTOBACKUP/2021_01_20/n_1062339305.279.1062318313 comment=NONE

Finished Control File Autobackup at 20-JAN-21

 

Important step is “rename” the files again on your controlfile, we can do that, easily, from RMAN

 

RMAN> switch database to copy;




datafile 1 switched to datafile copy "+DATA/PRDORCL/DATAFILE/system.272.1062318091"

datafile 3 switched to datafile copy "+DATA/PRDORCL/DATAFILE/sysaux.274.1062318281"

datafile 4 switched to datafile copy "+DATA/PRDORCL/DATAFILE/undotbs1.276.1062318293"

datafile 7 switched to datafile copy "+DATA/PRDORCL/DATAFILE/users.278.1062318311"

 

 

As all the files are now stored on the correct folder, on approach 2 we can remove safely the source folder to avoid issues in the future with space.

 

ASMCMD> ASMCMD> rm -rf ORCL

ASMCMD>

ASMCMD>

ASMCMD> pwd

+DATA

ASMCMD> ls -l

Type  Redund  Striped  Time  Sys  Name

                             N    PRDORCL/

ASMCMD>

 

One thing about the temporary tablespace, Oracle will recreate it for you when you open the database.
Summary: Keep in mind we saw two different approaches here to solve the same issue and if you created the ASM folder manually you can also apply a 3rd approach describe by this blogpost here: Renaming directory on ASM
 
I hope you liked this post, keep posted on my database adventures following me on twitter @mufalani and on linkedin
 
Best Regards,

 

Rodrigo Mufalani

 


1 2 3 … 7
LUXOUG – LUXEMBOURG ORACLE USERS GROUP
© 2025 LUXOUG – LUXEMBOURG ORACLE USERS GROUP