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.
[[email protected] ~]$ 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.
[[email protected] ~]$ 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.
[[email protected] dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwmatrix.ora ignorecase=y entries=3 Enter password for SYS: [[email protected] dbs]$
Create a backup spfile for matrix database
[[email protected] dbs]$ cat initORCL.ora SPFILE='+DATA/orcl/spfileorcl.ora' [[email protected] dbs]$ cat initMATRIX.ora.spfile SPFILE='+DATA/matrix/spfilematrix.ora'
Modify the /etc/oratab to reflect the new database name
[[email protected] dbs]$ vi /etc/oratab [[email protected] dbs]$ cat /etc/oratab | grep MATRIX MATRIX:/u01/app/oracle/product/11.2.0/dbhome_1:N [[email protected] dbs]$
Seting environment for new database name and sid . This will create the bdump,trace,cdump and required directories in the diagnostic_dest
[[email protected] ~]$ . oraenv ORACLE_SID = [ORCL] ? MATRIX The Oracle base remains unchanged with value /u01/app/oracle [[email protected] ~]$ 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.
[[email protected] 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 –
[[email protected] 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
[[email protected] ~]$ 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”
[[email protected] ~]$ 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
[email protected] ~]$ 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>
[email protected] ~]$ 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/
8 thoughts on “Rename Database Having Datafiles on ASM – 11gR2”
Good document about conversion of datafiles. Thanks!
SQL> show parameter db_create
NAME TYPE VALUE
———————————— ———– ——————————
db_create_file_dest string +DATA
What is the parameter db_recovery_file_dest set to? Also what do you see in “list copy of database;”
after doing switch copy all data files got created on NFS instead of ASM
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 700 SYSTEM *** /dump/backup/rman/PTEST/MATRIX_7_1_936176256.dmp_data_D-MATRIX_I-1855252257_TS-SYSTEM_FNO-1_07rspqk0
2 600 SYSAUX *** /dump/backup/rman/PTEST/MATRIX_8_1_936176263.dmp_data_D-MATRIX_I-1855252257_TS-SYSAUX_FNO-2_08rspqk7
3 200 UNDOTBS1 *** /dump/backup/rman/PTEST/MATRIX_9_1_936176270.dmp_data_D-MATRIX_I-1855252257_TS-UNDOTBS1_FNO-3_09rspqke
4 200 UNDOTBS2 *** /dump/backup/rman/PTEST/MATRIX_10_1_936176273.dmp_data_D-MATRIX_I-1855252257_TS-UNDOTBS2_FNO-4_0arspqkh
5 5 USERS *** /dump/backup/rman/PTEST/MATRIX_11_1_936176277.dmp_data_D-MATRIX_I-1855252257_TS-USERS_FNO-5_0brspqkl
seriously awesome….
all your blogs are awesome…
Thank you…. 🙂
Thank you for visiting the blog.
Hi, thank you for a wonderful step-by-step. Helped me a lot, instead of reinventing a weel.
Is this method only for ASM ? Will it work on non ASM as well ?