How To Change the database id and database name in RAC Databases

dbtut April 20, 2019 ORACLE

This article describes how to change the id and name of a database that is running as RAC, what operations should be done before and after.

In the examples, necessary steps have been performed to change the name of the database named ORCL as TEST.

Step 1

Change the value of the cluster_database parameter to false.

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 20 17:20:17 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 20 17:20:17 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

Step 2

Start database in mount mode on a node.

-bash-4.3$ srvctl stop database -d ORCL


SQL> startup mount
ORACLE instance started.

Total System Global Area 3.8215E+10 bytes
Fixed Size                  2254416 bytes
Variable Size            6979324336 bytes
Database Buffers         3.1004E+10 bytes
Redo Buffers              229019648 bytes
Database mounted.

-bash-4.3$ srvctl stop database -d ORCL

SQL> startup mount

ORACLE instance started.

Total System Global Area 3.8215E+10 bytes

Fixed Size 2254416 bytes

Variable Size 6979324336 bytes

Database Buffers 3.1004E+10 bytes

Redo Buffers 229019648 bytes

Database mounted.

Step 3

Change the database id and name information with the nid tool. We specify the new database name using DBNAME.

-bash-4.3$ nid TARGET=sys/welcome1@ORCL DBNAME=TEST

DBNEWID: Release 11.2.0.4.0 - Production on Fri Oct 20 17:22:12 2017

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

Connected to database ORCL (DBID=1485672324)

Connected to server version 11.2.0

Control Files in database:
    +DATA/orcl/controlfile/current.824.957891589
    +RECO/orcl/controlfile/current.49661.957891589

Change database ID and database name ORCL to TEST? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 1485672324 to 2256023237
Changing database name from ORCL to TEST
    Control File +DATA/orcl/controlfile/current.824.957891589 - modified
    Control File +RECO/orcl/controlfile/current.49661.957891589 - modified
    Datafile +DATA/orcl/datafile/system.846.95789151 - dbid changed, wrote new name
    Datafile +DATA/orcl/datafile/sysaux.849.95789151 - dbid changed, wrote new name
    Datafile +DATA/orcl/datafile/undotbs1.844.95789151 - dbid changed, wrote new name
    Datafile +DATA/orcl/datafile/users.843.95789151 - dbid changed, wrote new name
    Datafile +DATA/orcl/datafile/undotbs2.880.95789161 - dbid changed, wrote new name
    Datafile +DATA/orcl/tempfile/temp.879.95789159 - dbid changed, wrote new name
    Control File +DATA/orcl/controlfile/current.824.957891589 - dbid changed, wrote new name
    Control File +RECO/orcl/controlfile/current.49661.957891589 - dbid changed, wrote new name
    Instance shut down

Database name changed to TEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database TEST changed to 2256023237.
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.

-bash-4.3$ nid TARGET=sys/welcome1@ORCL DBNAME=TEST

DBNEWID: Release 11.2.0.4.0 - Production on Fri Oct 20 17:22:12 2017

Connected to database ORCL (DBID=1485672324)

Connected to server version 11.2.0

Control Files in database:

+DATA/orcl/controlfile/current.824.957891589

+RECO/orcl/controlfile/current.49661.957891589

Change database ID and database name ORCL to TEST? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 1485672324 to 2256023237

Changing database name from ORCL to TEST

Control File +DATA/orcl/controlfile/current.824.957891589 - modified

Control File +RECO/orcl/controlfile/current.49661.957891589 - modified

Datafile +DATA/orcl/datafile/system.846.95789151 - dbid changed, wrote new name

Datafile +DATA/orcl/datafile/sysaux.849.95789151 - dbid changed, wrote new name

Datafile +DATA/orcl/datafile/undotbs1.844.95789151 - dbid changed, wrote new name

Datafile +DATA/orcl/datafile/users.843.95789151 - dbid changed, wrote new name

Datafile +DATA/orcl/datafile/undotbs2.880.95789161 - dbid changed, wrote new name

Datafile +DATA/orcl/tempfile/temp.879.95789159 - dbid changed, wrote new name

Control File +DATA/orcl/controlfile/current.824.957891589 - dbid changed, wrote new name

Control File +RECO/orcl/controlfile/current.49661.957891589 - dbid changed, wrote new name

Instance shut down

Database name changed to TEST.

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

Database ID for database TEST changed to 2256023237.

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.

Step 4

Change the value of the db_name parameter as the new database name.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3.8215E+10 bytes
Fixed Size                  2254416 bytes
Variable Size            6979324336 bytes
Database Buffers         3.1004E+10 bytes
Redo Buffers              229019648 bytes
ORA-01103: database name 'TEST' in control file is not 'ORCL'


SQL> alter system set db_name=TEST scope=spfile sid='*';

System altered.


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> startup mount

ORACLE instance started.

Total System Global Area 3.8215E+10 bytes

Fixed Size 2254416 bytes

Variable Size 6979324336 bytes

Database Buffers 3.1004E+10 bytes

Redo Buffers 229019648 bytes

ORA-01103: database name 'TEST' in control file is not 'ORCL'

SQL> alter system set db_name=TEST scope=spfile sid='*';

System altered.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

Step 5

On all nodes, change the name of the corresponding initSID.ora file under the $ORACLE_HOME/dbs directory to initNEWSID.ora.

-bash-4.3$ cd $ORACLE_HOME/dbs
-bash-4.3$ mv initORCL1.ora initTEST1.ora

-bash-4.3$ ssh oradb2
-bash-4.3$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs
-bash-4.3$ mv initORCL2.ora initTEST2.ora

-bash-4.3$ cd $ORACLE_HOME/dbs

-bash-4.3$ mv initORCL1.ora initTEST1.ora

-bash-4.3$ ssh oradb2

-bash-4.3$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs

-bash-4.3$ mv initORCL2.ora initTEST2.ora

Step 6

Create the new password file and distribute it to all nodes.

-bash-4.3$ cd $ORACLE_HOME/dbs
-bash-4.3$ orapwd file=orapwTEST1 password=welcome1 entries=10

-bash-4.3$ scp orapwTEST1 oradb2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwTEST2
orapwTEST1                                                                                                                                                   100% 2560     2.5KB/s   00:00

-bash-4.3$ cd $ORACLE_HOME/dbs

-bash-4.3$ orapwd file=orapwTEST1 password=welcome1 entries=10

-bash-4.3$ scp orapwTEST1 oradb2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwTEST2

orapwTEST1 100% 2560 2.5KB/s 00:00

Step 7

Show the new database name to the listeners on all nodes.

-bash-4.3$ . oraenv
ORACLE_SID = [TEST1] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle
-bash-4.3$ lsnrctl reload

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 20-OCT-2017 17:35:20

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
The command completed successfully

-bash-4.3$ . oraenv

ORACLE_SID = [TEST1] ? +ASM1

The Oracle base remains unchanged with value /u01/app/oracle

-bash-4.3$ lsnrctl reload

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 20-OCT-2017 17:35:20

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

The command completed successfully

Step 8

Start the database in mount mode with the new SID environment variables and open it as resetlogs.

-bash-4.3$ . oraenv
ORACLE_SID = [TEST1] ? 
The Oracle base remains unchanged with value /u01/app/oracle
-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 20 17:37:20 2017

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3.8215E+10 bytes
Fixed Size                  2254416 bytes
Variable Size            4563405232 bytes
Database Buffers         3.3420E+10 bytes
Redo Buffers              229019648 bytes
Database mounted.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

-bash-4.3$ . oraenv

ORACLE_SID = [TEST1] ?

The Oracle base remains unchanged with value /u01/app/oracle

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 20 17:37:20 2017

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 3.8215E+10 bytes

Fixed Size 2254416 bytes

Variable Size 4563405232 bytes

Database Buffers 3.3420E+10 bytes

Redo Buffers 229019648 bytes

Database mounted.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

Step 9

Set the cluster_database parameter to true and open the database on all nodes.

SQL> alter system set cluster_database=true scope=spfile sid='*';

System altered.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

-bash-4.3$ srvctl status database -d ORCL
Instance ORCL1 is not running on node oradb1
Instance ORCL2 is not running on node oradb2

SQL> alter system set cluster_database=true scope=spfile sid='*';

System altered.

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

-bash-4.3$ srvctl status database -d ORCL

Instance ORCL1 is not running on node oradb1

Instance ORCL2 is not running on node oradb2

Step 10

Check the current database configuration.

-bash-4.3$ srvctl config database -d ORCL
Database unique name: ORCL
Database name: ORCL
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/ORCL/spfileORCL.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL
Database instances: ORCL1,ORCL2
Disk Groups: DATA,RECO
Mount point paths: 
Services: 
Type: RAC
Database is administrator managed

-bash-4.3$ srvctl config database -d ORCL

Database unique name: ORCL

Database name: ORCL

Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/ORCL/spfileORCL.ora

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: ORCL

Database instances: ORCL1,ORCL2

Disk Groups: DATA,RECO

Mount point paths:

Services:

Type: RAC

Database is administrator managed

Step 11

Create the pfile from the spfile file that is being used. In this pfile, change the SID values earlier than “*” in the instance-based parameters to the new SID, and then create the new spfile from this pfile.

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 20 17:47:23 2017

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

Connected to an idle instance.

SQL> create pfile='/tmp/testpfile' from spfile='+DATA/ORCL/spfileORCL.ora';

File created.

-bash-4.3$ vi /tmp/testpfile

*.audit_file_dest='/u01/app/oracle/admin/ORCL/adump'
*.audit_trail='db'
*.cluster_database=TRUE
*.compatible='11.2.0.4.0'
*.control_files='+DATA/orcl/controlfile/current.824.957891589','+RECO/orcl/controlfile/current.49661.957891589'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='TEST'
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
TEST1.instance_number=1
TEST2.instance_number=2
*.open_cursors=300
*.pga_aggregate_target=12767461376
*.processes=150
*.remote_listener='oradb-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=38302384128
TEST2.thread=2
TEST1.thread=1
TEST2.undo_tablespace='UNDOTBS2'
TEST1.undo_tablespace='UNDOTBS1'

SQL> create spfile='+DATA/ORCL/spfileTEST.ora' from pfile='/tmp/testpfile';

File created.

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 20 17:47:23 2017

Connected to an idle instance.

SQL> create pfile='/tmp/testpfile' from spfile='+DATA/ORCL/spfileORCL.ora';

File created.

-bash-4.3$ vi /tmp/testpfile

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

*.audit_trail='db'

*.cluster_database=TRUE

*.compatible='11.2.0.4.0'

*.control_files='+DATA/orcl/controlfile/current.824.957891589','+RECO/orcl/controlfile/current.49661.957891589'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain=''

*.db_name='TEST'

*.db_recovery_file_dest='+RECO'

*.db_recovery_file_dest_size=4621074432

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

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

TEST1.instance_number=1

TEST2.instance_number=2

*.open_cursors=300

*.pga_aggregate_target=12767461376

*.processes=150

*.remote_listener='oradb-scan:1521'

*.remote_login_passwordfile='exclusive'

*.sga_target=38302384128

TEST2.thread=2

TEST1.thread=1

TEST2.undo_tablespace='UNDOTBS2'

TEST1.undo_tablespace='UNDOTBS1'

SQL> create spfile='+DATA/ORCL/spfileTEST.ora' from pfile='/tmp/testpfile';

File created.

Step 12

Add new databases and instances to the CRS.

-bash-4.3$ srvctl add database -d TEST -o /u01/app/oracle/product/11.2.0/dbhome_1 -p '+DATA/ORCL/spfileTEST.ora' -n TEST
-bash-4.3$ srvctl status database -d TEST
Database is not running.
-bash-4.3$ srvctl add instance -d TEST -i TEST1 -n oradb1
-bash-4.3$ srvctl add instance -d TEST -i TEST2 -n oradb2
-bash-4.3$  srvctl status database -d TEST
Instance TEST1 is not running on node oradb1
Instance TEST2 is not running on node oradb2

-bash-4.3$ srvctl add database -d TEST -o /u01/app/oracle/product/11.2.0/dbhome_1 -p '+DATA/ORCL/spfileTEST.ora' -n TEST

-bash-4.3$ srvctl status database -d TEST

Database is not running.

-bash-4.3$ srvctl add instance -d TEST -i TEST1 -n oradb1

-bash-4.3$ srvctl add instance -d TEST -i TEST2 -n oradb2

-bash-4.3$ srvctl status database -d TEST

Instance TEST1 is not running on node oradb1

Instance TEST2 is not running on node oradb2

Step 13

Start the database created with the new name.

-bash-4.3$  srvctl start database -d TEST
-bash-4.3$  srvctl status database -d TEST
Instance TEST1 is running on node oradb1
Instance TEST2 is running on node oradb2

-bash-4.3$ srvctl start database -d TEST

-bash-4.3$ srvctl status database -d TEST

Instance TEST1 is running on node oradb1

Instance TEST2 is running on node oradb2

Step 14

Remove the instance and database registered with the old name in CRS.

-bash-4.3$ srvctl remove instance -d ORCL -i ORCL1
Remove instance from the database ORCL? (y/[n]) y
-bash-4.3$ srvctl remove instance -d ORCL -i ORCL2
Remove instance from the database ORCL? (y/[n]) y
-bash-4.3$ srvctl remove database -d ORCL
Remove the database ORCL? (y/[n]) y

-bash-4.3$ srvctl remove instance -d ORCL -i ORCL1

Remove instance from the database ORCL? (y/[n]) y

-bash-4.3$ srvctl remove instance -d ORCL -i ORCL2

Remove instance from the database ORCL? (y/[n]) y

-bash-4.3$ srvctl remove database -d ORCL

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

Step 15

At this stage, the db_name and db_unique_name parameters must be new, and the directories in the ASM disk groups must be created automatically with the new name.

Open the database in mount mode, ensure that the existing datafiles are created in the new ASM directory, and delete the old ones.

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 20 17:37:20 2017

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3.8215E+10 bytes
Fixed Size                  2254416 bytes
Variable Size            4563405232 bytes
Database Buffers         3.3420E+10 bytes
Redo Buffers              229019648 bytes
Database mounted.

-bash-4.3$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Oct 20 18:15:29 2017

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

connected to target database: TEST (DBID=2256023237, not open)

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

Starting backup at 20-OCT-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/orcl/datafile/system.846.957891511
output file name=+DATA/test/datafile/system.885.957896199 tag=TAG20171020T181638 RECID=7 STAMP=957896200
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.849.957891511
output file name=+DATA/test/datafile/sysaux.886.957896203 tag=TAG20171020T181638 RECID=8 STAMP=957896203
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.844.957891511
output file name=+DATA/test/datafile/undotbs1.887.957896205 tag=TAG20171020T181638 RECID=9 STAMP=957896205
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/orcl/datafile/undotbs2.880.957891619
output file name=+DATA/test/datafile/undotbs2.888.957896207 tag=TAG20171020T181638 RECID=10 STAMP=957896206
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/test/controlfile/backup.889.957896209 tag=TAG20171020T181638 RECID=11 STAMP=957896208
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/orcl/datafile/users.843.957891511
output file name=+DATA/test/datafile/users.890.957896209 tag=TAG20171020T181638 RECID=12 STAMP=957896209
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 20-OCT-17
channel ORA_DISK_1: finished piece 1 at 20-OCT-17
piece handle=+DATA/test/backupset/2017_10_20/nnsnf0_tag20171020t181638_0.891.957896211 tag=TAG20171020T181638 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-OCT-17

RMAN> list copy of database;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time       
------- ---- - --------------- ---------- ---------------
7       1    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/test/datafile/system.885.957896199
        Tag: TAG20171020T181638

8       2    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/test/datafile/sysaux.886.957896203
        Tag: TAG20171020T181638

9       3    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/test/datafile/undotbs1.887.957896205
        Tag: TAG20171020T181638

12      4    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/test/datafile/users.890.957896209
        Tag: TAG20171020T181638

10      5    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/test/datafile/undotbs2.888.957896207
        Tag: TAG20171020T181638


RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/test/datafile/system.885.957896199"
datafile 2 switched to datafile copy "+DATA/test/datafile/sysaux.886.957896203"
datafile 3 switched to datafile copy "+DATA/test/datafile/undotbs1.887.957896205"
datafile 4 switched to datafile copy "+DATA/test/datafile/users.890.957896209"
datafile 5 switched to datafile copy "+DATA/test/datafile/undotbs2.888.957896207"

RMAN> list copy of database;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time       
------- ---- - --------------- ---------- ---------------
13      1    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/orcl/datafile/system.846.957891511

14      2    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/orcl/datafile/sysaux.849.957891511

15      3    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/orcl/datafile/undotbs1.844.957891511

16      4    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/orcl/datafile/users.843.957891511

17      5    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/orcl/datafile/undotbs2.880.957891619


RMAN>  delete copy of database;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=113 instance=TEST1 device type=DISK
List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time       
------- ---- - --------------- ---------- ---------------
13      1    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/orcl/datafile/system.846.957891511

14      2    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/orcl/datafile/sysaux.849.957891511

15      3    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/orcl/datafile/undotbs1.844.957891511

16      4    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/orcl/datafile/users.843.957891511

17      5    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/orcl/datafile/undotbs2.880.957891619


Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy file name=+DATA/orcl/datafile/system.846.957891511 RECID=13 STAMP=957896229
deleted datafile copy
datafile copy file name=+DATA/orcl/datafile/sysaux.849.957891511 RECID=14 STAMP=957896229
deleted datafile copy
datafile copy file name=+DATA/orcl/datafile/undotbs1.844.957891511 RECID=15 STAMP=957896229
deleted datafile copy
datafile copy file name=+DATA/orcl/datafile/users.843.957891511 RECID=16 STAMP=957896229
deleted datafile copy
datafile copy file name=+DATA/orcl/datafile/undotbs2.880.957891619 RECID=17 STAMP=957896229
Deleted 5 objects


RMAN> exit


Recovery Manager complete.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 20 17:37:20 2017

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 3.8215E+10 bytes

Fixed Size 2254416 bytes

Variable Size 4563405232 bytes

Database Buffers 3.3420E+10 bytes

Redo Buffers 229019648 bytes

Database mounted.

-bash-4.3$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Oct 20 18:15:29 2017

connected to target database: TEST (DBID=2256023237, not open)

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

Starting backup at 20-OCT-17

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+DATA/orcl/datafile/system.846.957891511

output file name=+DATA/test/datafile/system.885.957896199 tag=TAG20171020T181638 RECID=7 STAMP=957896200

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.849.957891511

output file name=+DATA/test/datafile/sysaux.886.957896203 tag=TAG20171020T181638 RECID=8 STAMP=957896203

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.844.957891511

output file name=+DATA/test/datafile/undotbs1.887.957896205 tag=TAG20171020T181638 RECID=9 STAMP=957896205

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=+DATA/orcl/datafile/undotbs2.880.957891619

output file name=+DATA/test/datafile/undotbs2.888.957896207 tag=TAG20171020T181638 RECID=10 STAMP=957896206

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

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=+DATA/test/controlfile/backup.889.957896209 tag=TAG20171020T181638 RECID=11 STAMP=957896208

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

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=+DATA/orcl/datafile/users.843.957891511

output file name=+DATA/test/datafile/users.890.957896209 tag=TAG20171020T181638 RECID=12 STAMP=957896209

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

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 20-OCT-17

channel ORA_DISK_1: finished piece 1 at 20-OCT-17

piece handle=+DATA/test/backupset/2017_10_20/nnsnf0_tag20171020t181638_0.891.957896211 tag=TAG20171020T181638 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 20-OCT-17

RMAN> list copy of database;

List of Datafile Copies

=======================

Key File S Completion Time Ckp SCN Ckp Time

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

7 1 A 20-OCT-17 1099335 20-OCT-17

Name: +DATA/test/datafile/system.885.957896199

Tag: TAG20171020T181638

8 2 A 20-OCT-17 1099335 20-OCT-17

Name: +DATA/test/datafile/sysaux.886.957896203

Tag: TAG20171020T181638

9 3 A 20-OCT-17 1099335 20-OCT-17

Name: +DATA/test/datafile/undotbs1.887.957896205

Tag: TAG20171020T181638

12 4 A 20-OCT-17 1099335 20-OCT-17

Name: +DATA/test/datafile/users.890.957896209

Tag: TAG20171020T181638

10 5 A 20-OCT-17 1099335 20-OCT-17

Name: +DATA/test/datafile/undotbs2.888.957896207

Tag: TAG20171020T181638

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/test/datafile/system.885.957896199"

datafile 2 switched to datafile copy "+DATA/test/datafile/sysaux.886.957896203"

datafile 3 switched to datafile copy "+DATA/test/datafile/undotbs1.887.957896205"

datafile 4 switched to datafile copy "+DATA/test/datafile/users.890.957896209"

datafile 5 switched to datafile copy "+DATA/test/datafile/undotbs2.888.957896207"

RMAN> list copy of database;

List of Datafile Copies

=======================

Key File S Completion Time Ckp SCN Ckp Time

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

13 1 A 20-OCT-17 1099335 20-OCT-17

Name: +DATA/orcl/datafile/system.846.957891511

14 2 A 20-OCT-17 1099335 20-OCT-17

Name: +DATA/orcl/datafile/sysaux.849.957891511

15 3 A 20-OCT-17 1099335 20-OCT-17

Name: +DATA/orcl/datafile/undotbs1.844.957891511

16 4 A 20-OCT-17 1099335 20-OCT-17

Name: +DATA/orcl/datafile/users.843.957891511

17 5 A 20-OCT-17 1099335 20-OCT-17

Name: +DATA/orcl/datafile/undotbs2.880.957891619

RMAN> delete copy of database;

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=113 instance=TEST1 device type=DISK

List of Datafile Copies

=======================

Key File S Completion Time Ckp SCN Ckp Time

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

13 1 A 20-OCT-17 1099335 20-OCT-17

Name: +DATA/orcl/datafile/system.846.957891511

14 2 A 20-OCT-17 1099335 20-OCT-17

Name: +DATA/orcl/datafile/sysaux.849.957891511

15 3 A 20-OCT-17 1099335 20-OCT-17

Name: +DATA/orcl/datafile/undotbs1.844.957891511

16 4 A 20-OCT-17 1099335 20-OCT-17

Name: +DATA/orcl/datafile/users.843.957891511

17 5 A 20-OCT-17 1099335 20-OCT-17

Name: +DATA/orcl/datafile/undotbs2.880.957891619

Do you really want to delete the above objects (enter YES or NO)? YES

deleted datafile copy

datafile copy file name=+DATA/orcl/datafile/system.846.957891511 RECID=13 STAMP=957896229

deleted datafile copy

datafile copy file name=+DATA/orcl/datafile/sysaux.849.957891511 RECID=14 STAMP=957896229

deleted datafile copy

datafile copy file name=+DATA/orcl/datafile/undotbs1.844.957891511 RECID=15 STAMP=957896229

deleted datafile copy

datafile copy file name=+DATA/orcl/datafile/users.843.957891511 RECID=16 STAMP=957896229

deleted datafile copy

datafile copy file name=+DATA/orcl/datafile/undotbs2.880.957891619 RECID=17 STAMP=957896229

Deleted 5 objects

RMAN> exit

Recovery Manager complete.

Step 16

Start the database on all nodes.

-bash-4.3$ srvctl start database -d TEST
-bash-4.3$ srvctl status database -d TEST
Instance TEST1 is running on node oradb1
Instance TEST2 is running on node oradb2

-bash-4.3$ srvctl start database -d TEST

-bash-4.3$ srvctl status database -d TEST

Instance TEST1 is running on node oradb1

Instance TEST2 is running on node oradb2

Step 17

Make sure that the datafiles are under the directory created with the new database name in the ASM disk group.

SQL> select FILE#,NAME from v$datafile;

     FILE# NAME
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1 +DATA/test/datafile/system.885.957896199
         2 +DATA/test/datafile/sysaux.886.957896203
         3 +DATA/test/datafile/undotbs1.887.957896205
         4 +DATA/test/datafile/users.890.957896209
         5 +DATA/test/datafile/undotbs2.888.957896207

SQL> select FILE#,NAME from v$datafile;

FILE# NAME

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

1 +DATA/test/datafile/system.885.957896199

2 +DATA/test/datafile/sysaux.886.957896203

3 +DATA/test/datafile/undotbs1.887.957896205

4 +DATA/test/datafile/users.890.957896209

5 +DATA/test/datafile/undotbs2.888.957896207

Step 18

Move the existing controlfiles under the directory created with the new database name in the ASM disk group.

SQL> show parameter control

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
control_file_record_keep_time        integer
7
control_files                        string
+DATA/orcl/controlfile/current
.824.957891589, +RECO/orcl/con
trolfile/current.49661.9578915
89
control_management_pack_access       string
DIAGNOSTIC+TUNING


SQL> show parameter db_name

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_name                              string
TEST
SQL> show parameter db_unique_name

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_unique_name                       string
TEST

SQL> show parameter control

NAME TYPE

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

VALUE

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

control_file_record_keep_time integer

control_files string

+DATA/orcl/controlfile/current

.824.957891589, +RECO/orcl/con

trolfile/current.49661.9578915

control_management_pack_access string

DIAGNOSTIC+TUNING

SQL> show parameter db_name

NAME TYPE

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

VALUE

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

db_name string

TEST

SQL> show parameter db_unique_name

NAME TYPE

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

VALUE

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

db_unique_name string

TEST

Step 19

Change the current control_files parameter to include one old controlfile and the names of the disk groups.

SQL> alter system set control_files='+DATA/orcl/controlfile/current.824.957891589','+DATA','+RECO' scope=spfile sid='*';

System altered.

SQL> alter system set control_files='+DATA/orcl/controlfile/current.824.957891589','+DATA','+RECO' scope=spfile sid='*';

System altered.

Step 20

Start the database in nomount mode, and create new controlfiles with the restore controlfile operation.

-bash-4.3$ srvctl stop database -d TEST


SQL> startup nomount


-bash-4.3$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Oct 24 11:18:53 2017

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

connected to target database: TEST (not mounted)

RMAN> restore controlfile from '+DATA/orcl/controlfile/current.824.957891589';

Starting restore at 24-OCT-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=109 instance=TEST1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/orcl/controlfile/current.824.957891589
output file name=+DATA/test/controlfile/current.899.958216739
output file name=+RECO/test/controlfile/current.49227.958216739
Finished restore at 24-OCT-17

RMAN> exit


Recovery Manager complete.

-bash-4.3$ srvctl stop database -d TEST

SQL> startup nomount

-bash-4.3$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Oct 24 11:18:53 2017

connected to target database: TEST (not mounted)

RMAN> restore controlfile from '+DATA/orcl/controlfile/current.824.957891589';

Starting restore at 24-OCT-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=109 instance=TEST1 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=+DATA/orcl/controlfile/current.824.957891589

output file name=+DATA/test/controlfile/current.899.958216739

output file name=+RECO/test/controlfile/current.49227.958216739

Finished restore at 24-OCT-17

RMAN> exit

Recovery Manager complete.

Step 21

Change the existing control_files parameter with the controlfiles created under the new database directory. (example: test)

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 24 11:19:50 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> alter system set control_files='+DATA/test/controlfile/current.899.958216739','+RECO/test/controlfile/current.49227.958216739' scope=spfile sid='*';

System altered.

SQL> shu immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 24 11:19:50 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> alter system set control_files='+DATA/test/controlfile/current.899.958216739','+RECO/test/controlfile/current.49227.958216739' scope=spfile sid='*';

System altered.

SQL> shu immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Step 22

Start the database to make sure that it uses the new controlfiles.

-bash-4.3$ srvctl status database -d TEST
Instance TEST1 is not running on node oradb1
Instance TEST2 is not running on node oradb2
-bash-4.3$ srvctl start database -d TEST
-bash-4.3$ srvctl status database -d TEST
Instance TEST1 is running on node oradb1
Instance TEST2 is running on node oradb2
-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 24 11:21:24 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter control

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
control_file_record_keep_time        integer
7
control_files                        string
+DATA/test/controlfile/current
.899.958216739, +RECO/test/con
trolfile/current.49227.9582167
39
control_management_pack_access       string
DIAGNOSTIC+TUNING

-bash-4.3$ srvctl status database -d TEST

Instance TEST1 is not running on node oradb1

Instance TEST2 is not running on node oradb2

-bash-4.3$ srvctl start database -d TEST

-bash-4.3$ srvctl status database -d TEST

Instance TEST1 is running on node oradb1

Instance TEST2 is running on node oradb2

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 24 11:21:24 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> show parameter control

NAME TYPE

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

VALUE

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

control_file_record_keep_time integer

control_files string

+DATA/test/controlfile/current

.899.958216739, +RECO/test/con

trolfile/current.49227.9582167

control_management_pack_access string

DIAGNOSTIC+TUNING

Step 23

In the previous steps, we created the spfile with new name under the old database in the ASM. In this step, we will re-create the spfile with new name under the new directory.

-bash-4.3$ srvctl config database -d TEST
Database unique name: TEST
Database name: TEST
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/ORCL/spfileTEST.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: TEST
Database instances: TEST1,TEST2
Disk Groups: DATA,RECO
Mount point paths: 
Services: 
Type: RAC
Database is administrator managed
-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 24 11:31:19 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter spfile

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
spfile                               string
+DATA/orcl/spfiletest.ora
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> create pfile='/tmp/pfiletest.ora' from spfile='+DATA/orcl/spfiletest.ora';

File created.

SQL> create spfile='+DATA/test/spfiletest.ora' from pfile='/tmp/pfiletest.ora';

File created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

-bash-4.3$ srvctl config database -d TEST

Database unique name: TEST

Database name: TEST

Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/ORCL/spfileTEST.ora

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: TEST

Database instances: TEST1,TEST2

Disk Groups: DATA,RECO

Mount point paths:

Services:

Type: RAC

Database is administrator managed

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 24 11:31:19 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> show parameter spfile

NAME TYPE

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

VALUE

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

spfile string

+DATA/orcl/spfiletest.ora

SQL>

SQL> create pfile='/tmp/pfiletest.ora' from spfile='+DATA/orcl/spfiletest.ora';

File created.

SQL> create spfile='+DATA/test/spfiletest.ora' from pfile='/tmp/pfiletest.ora';

File created.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Step 24

After changing the spfile, change the database configuration so that it uses the new spfile.

-bash-4.3$ srvctl modify database -d TEST -p +DATA/test/spfiletest.ora
-bash-4.3$ srvctl config database -d TEST
Database unique name: TEST
Database name: TEST
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/test/spfiletest.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: TEST
Database instances: TEST1,TEST2
Disk Groups: DATA,RECO
Mount point paths: 
Services: 
Type: RAC
Database is administrator managed

-bash-4.3$ srvctl modify database -d TEST -p +DATA/test/spfiletest.ora

-bash-4.3$ srvctl config database -d TEST

Database unique name: TEST

Database name: TEST

Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/test/spfiletest.ora

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: TEST

Database instances: TEST1,TEST2

Disk Groups: DATA,RECO

Mount point paths:

Services:

Type: RAC

Database is administrator managed

Step 25

Stop the database and change the corresponding row in the $ORACLE_HOME/dbs/initSID.ora file on all nodes to show the new spfile.

-bash-4.3$ srvctl stop database -d TEST


-bash-4.3$ cat $ORACLE_HOME/dbs/initTEST1.ora
SPFILE='+DATA/ORCL/spfileTEST.ora'              # line added by Agent


-bash-4.3$ vi $ORACLE_HOME/dbs/initTEST1.ora


-bash-4.3$ cat $ORACLE_HOME/dbs/initTEST1.ora
SPFILE='+DATA/test/spfiletest.ora'              # line added by Agent

-bash-4.3$ srvctl stop database -d TEST

-bash-4.3$ cat $ORACLE_HOME/dbs/initTEST1.ora

SPFILE='+DATA/ORCL/spfileTEST.ora' # line added by Agent

-bash-4.3$ vi $ORACLE_HOME/dbs/initTEST1.ora

-bash-4.3$ cat $ORACLE_HOME/dbs/initTEST1.ora

SPFILE='+DATA/test/spfiletest.ora' # line added by Agent

Step 26

Open the database and make sure that the new spfile is visible in the configuration, and the database opened with the new spfile.

-bash-4.3$ srvctl start database -d TEST
-bash-4.3$ srvctl config database -d TEST
Database unique name: TEST
Database name: TEST
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/test/spfiletest.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: TEST
Database instances: TEST1,TEST2
Disk Groups: DATA,RECO
Mount point paths: 
Services: 
Type: RAC
Database is administrator managed

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 24 11:41:19 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter spfile

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
spfile                               string
+DATA/test/spfiletest.ora

-bash-4.3$ srvctl start database -d TEST

-bash-4.3$ srvctl config database -d TEST

Database unique name: TEST

Database name: TEST

Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/test/spfiletest.ora

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools: TEST

Database instances: TEST1,TEST2

Disk Groups: DATA,RECO

Mount point paths:

Services:

Type: RAC

Database is administrator managed

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 24 11:41:19 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> show parameter spfile

NAME TYPE

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

VALUE

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

spfile string

+DATA/test/spfiletest.ora

Step 27

Finally, re-create the existing temp tablespace so that it can use the new directory in the ASM.

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 24 11:50:43 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED TABLESPACE GROUP '' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

Database altered.

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED TABLESPACE GROUP '' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

Database altered.

SQL> DROP TABLESPACE TEMP1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 24 11:50:43 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED TABLESPACE GROUP '' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

Database altered.

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED TABLESPACE GROUP '' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

Database altered.

SQL> DROP TABLESPACE TEMP1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

Step 28

Delete directories created with the old database name in ASM disk groups.

-bash-4.3$ . oraenv
ORACLE_SID = [TEST1] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle

-bash-4.3$ asmcmd
ASMCMD> ls
DATA/
RECO/
ASMCMD> cd DATA
ASMCMD> rm -rf ORCL
ASMCMD> cd RECO
ASMCMD> rm -rf ORCL
ASMCMD>

-bash-4.3$ . oraenv

ORACLE_SID = [TEST1] ? +ASM1

The Oracle base remains unchanged with value /u01/app/oracle

-bash-4.3$ asmcmd

ASMCMD> ls

DATA/

RECO/

ASMCMD> cd DATA

ASMCMD> rm -rf ORCL

ASMCMD> cd RECO

ASMCMD> rm -rf ORCL

ASMCMD>

Step 29

Make sure that all datafile and tempfiles are under the new directory in ASM.

-bash-4.3$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Oct 24 11:57:35 2017

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

connected to target database: TEST (DBID=2256023237)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name TEST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    740      SYSTEM               ***     +DATA/test/datafile/system.885.957896199
2    570      SYSAUX               ***     +DATA/test/datafile/sysaux.886.957896203
3    150      UNDOTBS1             ***     +DATA/test/datafile/undotbs1.887.957896205
4    5        USERS                ***     +DATA/test/datafile/users.890.957896209
5    25       UNDOTBS2             ***     +DATA/test/datafile/undotbs2.888.957896207

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    1024     TEMP                 32767       +DATA/test/tempfile/temp.879.958218705

-bash-4.3$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Oct 24 11:57:35 2017

connected to target database: TEST (DBID=2256023237)

RMAN> report schema;

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name TEST

List of Permanent Datafiles

===========================

File Size(MB) Tablespace RB segs Datafile Name

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

1 740 SYSTEM *** +DATA/test/datafile/system.885.957896199

2 570 SYSAUX *** +DATA/test/datafile/sysaux.886.957896203

3 150 UNDOTBS1 *** +DATA/test/datafile/undotbs1.887.957896205

4 5 USERS *** +DATA/test/datafile/users.890.957896209

5 25 UNDOTBS2 *** +DATA/test/datafile/undotbs2.888.957896207

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

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

1 1024 TEMP 32767 +DATA/test/tempfile/temp.879.958218705

NOTE: Because redologs are automatically created under the new directory in the ASM, they are not manually recreated.

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

How To Change the database id and database name in RAC Databases

Step 1

Step 2

Step 3

Step 4

Step 5

Step 6

Step 7

Step 8

Step 9

Step 10

Step 11

Step 12

Step 13

Step 14

Step 15

Step 16

Step 17

Step 18

Step 19

Step 20

Step 21

Step 22

Step 23

Step 24

Step 25

Step 26

Step 27

Step 28

Step 29

About dbtut

Leave a Reply Cancel reply