Rename Database Having Datafiles on ASM – 11gR2

This blog post is about renaming the database when using ASM. Here i will be renaming the database name from “ORCL” to “MATRIX”.This method can be used for the database on lower versions (than 11gR2) also.

[oracle@anand-lab ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jun 19 10:33:31 2011

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             364906656 bytes
Database Buffers          163577856 bytes
Redo Buffers                5832704 bytes
Database mounted.
Database opened.
SQL> show parameter instance_name 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      ORCL
SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      ORCL

Bring the database is mount state to run the “NID” utility.As i want to change only the db name, the parameter “setname” is used.

[oracle@anand-lab ~]$ nid target=/ setname=yes dbname=matrix

DBNEWID: Release 11.2.0.2.0 - Production on Sun Jun 19 10:58:47 2011

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

Connected to database ORCL (DBID=1277530579)

Connected to server version 11.2.0

Control Files in database:
    +DATA/orcl/controlfile/current.260.749749843
    +DATA02/orcl/controlfile/current.257.753732681
Change database name of database ORCL to MATRIX? (Y/[N]) => Y

Proceeding with operation
Changing database name from ORCL to MATRIX
    Control File +DATA/orcl/controlfile/current.260.749749843 - modified
    Control File +DATA02/orcl/controlfile/current.257.753732681 - modified
    Datafile +DATA/orcl/datafile/system.256.74974966 - wrote new name
    Datafile +DATA/orcl/datafile/sysaux.257.74974966 - wrote new name
    Datafile +DATA/orcl/datafile/undotbs1.258.74974966 - wrote new name
    Datafile +DATA/orcl/datafile/users.259.74974966 - wrote new name
    Datafile +DATA/orcl/datafile/example.265.74974989 - wrote new name
    Datafile +DATA02/orcl/datafile/test.db - wrote new name
    Datafile +DATA/orcl/tempfile/temp.264.74974988 - wrote new name
    Control File +DATA/orcl/controlfile/current.260.749749843 - wrote new name
    Control File +DATA02/orcl/controlfile/current.257.753732681 - wrote new name
    Instance shut down

Database name changed to MATRIX.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

Alert log shows

*** DBNEWID utility started ***
DBNAME will be changed from ORCL to new DBNAME of MATRIX
Starting datafile conversion
Datafile conversion complete
Database name changed to MATRIX.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
*** DBNEWID utility finished succesfully ***

Create a password file for the matrix using orapwd utility.From 11.1 you run the orapwd without password argument.

[oracle@anand-lab dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwmatrix.ora ignorecase=y entries=3

Enter password for SYS: 
[oracle@anand-lab dbs]$ 

Create a backup spfile for matrix database

[oracle@anand-lab dbs]$ cat initORCL.ora
SPFILE='+DATA/orcl/spfileorcl.ora'
[oracle@anand-lab dbs]$ cat initMATRIX.ora.spfile
SPFILE='+DATA/matrix/spfilematrix.ora'

Modify the /etc/oratab to reflect the new database name

[oracle@anand-lab dbs]$ vi /etc/oratab 
[oracle@anand-lab dbs]$ cat /etc/oratab | grep MATRIX
MATRIX:/u01/app/oracle/product/11.2.0/dbhome_1:N
[oracle@anand-lab dbs]$

Seting environment for new database name and sid . This will create the bdump,trace,cdump and required directories in the diagnostic_dest

[oracle@anand-lab ~]$ . oraenv
ORACLE_SID = [ORCL] ? MATRIX
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@anand-lab ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jun 19 12:01:32 2011

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             331352224 bytes
Database Buffers          197132288 bytes
Redo Buffers                5832704 bytes
SQL> create pfile from spfile;

File created.

SQL> shu immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> exit

Pfile with the name initMATRIX.ora is created in the $ORACLE_HOME/dbs folder.

[oracle@anand-lab dbs]$ ls -lrt ini*
-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r--r-- 1 oracle oinstall   35 Jun 19 11:44 initMATRIX.ora.spfile
-rw-r----- 1 oracle oinstall  861 Jun 19 11:53 initORCL.ora
-rw-r--r-- 1 oracle oinstall  999 Jun 19 12:03 initMATRIX.ora

In the initMATRIX.ora replace the orcl with matrix –

[oracle@anand-lab dbs]$ more initMATRIX.ora
MATRIX.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.audit_file_dest='/u01/app/oracle/admin/MATRIX/adump'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/matrix/controlfile/current.260.749749843','+DATA02/matrix/controlfile/current.257.753732681'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='MATRIX'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=MATRIXXDB)'
*.event=''
*.instance_name='MATRIX'
*.memory_target=536870912
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

Create the adump folder on the specific location as per the pfile and start the instance to create the spfile

[oracle@anand-lab ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jun 19 13:23:19 2011

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             327157920 bytes
Database Buffers          201326592 bytes
Redo Buffers                5832704 bytes
SQL> create spfile='+DATA/MATRIX/spfilematrix.ora' from pfile;

File created.

SQL> shu immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> exit

Once the spfile file is created change the initMATRIX.ora.spfile which point to the new ASM spfile to initMATRIX.ora

Use the controlfile in “+DATA/orcl” to create a new controlfile in “+DATA/matrix”

[oracle@anand-lab ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Jun 19 13:28:56 2011

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

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area     535662592 bytes

Fixed Size                     1345376 bytes
Variable Size                327157920 bytes
Database Buffers             201326592 bytes
Redo Buffers                   5832704 bytes

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

Starting restore at 19-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/matrix/controlfile/current.268.754234199
output file name=+DATA02/matrix/controlfile/current.258.754234201
Finished restore at 19-JUN-11
RMAN> shutdown
Oracle instance shut down
RMAN> exit

From 11gR2 (not tested in 11gR1) there is no need to update the spfile using alter system set control_files command to the new output file name.

Start the instance with the spfile

oracle@anand-lab ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jun 19 13:34:25 2011

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1345376 bytes
Variable Size             327157920 bytes
Database Buffers          201326592 bytes
Redo Buffers                5832704 bytes
SQL> alter database mount;

Database altered.
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/matrix/controlfile/current.268.754234199
+DATA02/matrix/controlfile/current.258.754234201

Check the parameter db_recovery_file_dest_size if not set, do set it. If already set check for sufficient space.

SQL> alter system set db_recovery_file_dest_size = 2G;

System altered.

SQL>  alter system set db_recovery_file_dest='+DATA02';

System altered.

SQL> 
oracle@anand-lab ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Jun 19 13:39:40 2011

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

connected to target database: MATRIX (DBID=1277530579, not open)

RMAN> report schema;

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

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    710      SYSTEM               ***     +DATA/orcl/datafile/system.256.749749661
2    590      SYSAUX               ***     +DATA/orcl/datafile/sysaux.257.749749667
3    320      UNDOTBS1             ***     +DATA/orcl/datafile/undotbs1.258.749749667
4    5        USERS                ***     +DATA/orcl/datafile/users.259.749749667
5    100      EXAMPLE              ***     +DATA/orcl/datafile/example.265.749749897
6    10       TEST                 ***     +DATA02/orcl/datafile/test.dbf

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

RMAN> 

RMAN> backup as copy database;

This will create the output file in +DATA02 diskgorup as my db_recovery_file_dest is set to +DATA02. Once donw you can list the copy of the database using

RMAN> list copy of database;

Then switch the datafile to copy - 

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA02/matrix/datafile/system.259.754249711"
datafile 2 switched to datafile copy "+DATA02/matrix/datafile/sysaux.260.754249783"
datafile 3 switched to datafile copy "+DATA02/matrix/datafile/undotbs1.261.754249833"
datafile 4 switched to datafile copy "+DATA02/matrix/datafile/users.265.754249879"
datafile 5 switched to datafile copy "+DATA02/matrix/datafile/example.262.754249859"
datafile 6 switched to datafile copy "+DATA02/matrix/datafile/test.263.754249875"

RMAN> report schema;

Report of database schema for database with db_unique_name MATRIX

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    710      SYSTEM               ***     +DATA02/matrix/datafile/system.259.754249711
2    590      SYSAUX               ***     +DATA02/matrix/datafile/sysaux.260.754249783
3    320      UNDOTBS1             ***     +DATA02/matrix/datafile/undotbs1.261.754249833
4    5        USERS                ***     +DATA02/matrix/datafile/users.265.754249879
5    100      EXAMPLE              ***     +DATA02/matrix/datafile/example.262.754249859
6    10       TEST                 ***     +DATA02/matrix/datafile/test.263.754249875

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

RMAN> 

From the RMAN command , open the database

RMAN> alter database open;

database opened

RMAN> sql'alter tablespace temp add tempfile';

sql statement: alter tablespace temp add tempfile

RMAN> report schema;

Report of database schema for database with db_unique_name MATRIX

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    710      SYSTEM               ***     +DATA02/matrix/datafile/system.259.754249711
2    590      SYSAUX               ***     +DATA02/matrix/datafile/sysaux.260.754249783
3    320      UNDOTBS1             ***     +DATA02/matrix/datafile/undotbs1.261.754249833
4    5        USERS                ***     +DATA02/matrix/datafile/users.265.754249879
5    100      EXAMPLE              ***     +DATA02/matrix/datafile/example.262.754249859
6    10       TEST                 ***     +DATA02/matrix/datafile/test.263.754249875

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    67       TEMP                 32767       +DATA/orcl/tempfile/temp.264.749749887
2    100      TEMP                 32767       +DATA/matrix/tempfile/temp.272.754250315


RMAN> sql "alter database tempfile ''+DATA/orcl/tempfile/temp.264.749749887'' drop";

sql statement: alter database tempfile ''+DATA/orcl/tempfile/temp.264.749749887'' drop

RMAN> report schema;

Report of database schema for database with db_unique_name MATRIX

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    710      SYSTEM               ***     +DATA02/matrix/datafile/system.259.754249711
2    590      SYSAUX               ***     +DATA02/matrix/datafile/sysaux.260.754249783
3    320      UNDOTBS1             ***     +DATA02/matrix/datafile/undotbs1.261.754249833
4    5        USERS                ***     +DATA02/matrix/datafile/users.265.754249879
5    100      EXAMPLE              ***     +DATA02/matrix/datafile/example.262.754249859
6    10       TEST                 ***     +DATA02/matrix/datafile/test.263.754249875

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
2    100      TEMP                 32767       +DATA/matrix/tempfile/temp.272.754250315

RMAN> 

Remember the redo logfiles would still be in the “+DATA/orcl” folder so , drop and create the new redo logfile groups using the alter database drop/add logfile group command.

SQL> select name from v$database;

NAME
---------
MATRIX

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
MATRIX

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DATA/matrix/onlinelog/group_3.263.754252875
+DATA/matrix/onlinelog/group_2.262.754253053
+DATA/matrix/onlinelog/group_1.261.754253323

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA02/matrix/datafile/system.259.754249711
+DATA02/matrix/datafile/sysaux.260.754249783
+DATA02/matrix/datafile/undotbs1.261.754249833
+DATA02/matrix/datafile/users.265.754249879
+DATA02/matrix/datafile/example.262.754249859
+DATA02/matrix/datafile/test.263.754249875

6 rows selected.

SQL> 

Once, everything is done and checked, remove the datafile,controlfile,tempfile,parameter files from the ORCL folder in the diskgoup.

From 11gR2, i believe, we can use the “cp” command to do the same things. Next is to try changing from “MATRIX” to “ORCL” using “cp” command and few other changes. 😉

Reference – http://oraganism.wordpress.com/2010/04/03/rename-database-when-using-asm/

4 thoughts on “Rename Database Having Datafiles on ASM – 11gR2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s