This post has been a bit long in the making… Whilst finishing it off I noted the dates in the output generated when I was first doing this… Just under 12 months to get round to finishing it off… There’s never enough time to do everything I want to do 
Anyway, as part of some Data Guard testing, which included backing up from a physical standby, I created a couple of new virtual machines using a Oracle supplied template. When I found that the database in the template was named something other than what I wanted my mind ran ahead to wondering what happens when you use NEWDBID with a database that resides in ASM…
I thought that maybe Oracle just handles everything for you: You run NEWDBID to change the database name and Oracle moves everything inside ASM to reflect the new database name (not that it would really need to “move” anything)… Well, it doesn’t, and maybe that would be Oracle taking a little too much control away from the DBA? Anyway, here is what happened and how I “fixed” it.
The virtual machine template “OVM_EL5U2_X86_64_ORACLE10G_PVM” comes with a preconfigured Oracle 10g database named “orcl”:
[oracle@zinc ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 3 22:49:39 2009 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. SQL> conn / as sysdba Connected. SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string orcl SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Which comes configured to use ASM…
[oracle@zinc ~]$ . oraenv
ORACLE_SID = [orcl] ? +ASM
[oracle@zinc ~]$ asmcmd -p
ASMCMD [+] > ls -l
State Type Rebal Unbal Name
MOUNTED NORMAL N N DATA/
ASMCMD [+] > cd DATA
ASMCMD [+DATA] > ls -l
Type Redund Striped Time Sys Name
Y DB_UNKNOWN/
Y ORCL/
ASMCMD [+DATA] >
But, I didn’t want my database to be called “orcl”. Although, there was no reason why I NEEDED to change the database name for my planned Data Guard testing. Once I’d considered renaming the database there was no turning back…
Whilst connected to the ASM instance I checked the location of the spfile:
ASMCMD [+] > ls -l DATA/ORCL
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y TEMPFILE/
N spfileorcl.ora => +DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.269.683246731
ASMCMD [+] >
I didn’t like that. “DB_UNKNOWN” is a bit ugly to my eyes. It was time to run NEWDBID (nid) and see what happens to the ASM directory structure.
Resetting my environment:
[oracle@zinc ~]$ . oraenv ORACLE_SID = [+ASM] ? orcl
Opening the database in mount mode:
[oracle@zinc ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 3 22:58:57 2009 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. SQL> conn / as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 612368384 bytes Fixed Size 2085872 bytes Variable Size 180358160 bytes Database Buffers 423624704 bytes Redo Buffers 6299648 bytes Database mounted. SQL> exit
Running the “nid” command:
[oracle@zinc ~]$ nid target=/ setname=true dbname=dg10g
DBNEWID: Release 10.2.0.4.0 - Production on Fri Apr 3 23:00:50 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to database ORCL (DBID=1192025583)
Connected to server version 10.2.0
Control Files in database:
+DATA/orcl/controlfile/current.261.664244847
+DATA/orcl/controlfile/current.260.664244847
Change database name of database ORCL to DG10G? (Y/[N]) => Y
Proceeding with operation
Changing database name from ORCL to DG10G
Control File +DATA/orcl/controlfile/current.261.664244847 - modified
Control File +DATA/orcl/controlfile/current.260.664244847 - modified
Datafile +DATA/orcl/datafile/system.256.664244797 - wrote new name
Datafile +DATA/orcl/datafile/undotbs1.258.664244797 - wrote new name
Datafile +DATA/orcl/datafile/sysaux.257.664244797 - wrote new name
Datafile +DATA/orcl/datafile/users.259.664244797 - wrote new name
Datafile +DATA/orcl/tempfile/temp.268.664244883 - wrote new name
Control File +DATA/orcl/controlfile/current.261.664244847 - wrote new name
Control File +DATA/orcl/controlfile/current.260.664244847 - wrote new name
Instance shut down
Database name changed to DG10G.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
Creating a pfile to point to the spfile, which has been PARTIALLY updated with parameters for dg10g as part of running nid:
[oracle@zinc ~]$ cat << EOF > $ORACLE_HOME/dbs/initdg10g.ora > spfile='+DATA/ORCL/spfileorcl.ora' > EOF
Creating a backup of the newly created pfile as it will be overwritten as part of later stages:
[oracle@zinc ~]$ cp $ORACLE_HOME/dbs/initdg10g.ora $ORACLE_HOME/dbs/initdg10g.ora.spfile
Creating a password file for dg10g:
[oracle@zinc ~]$ orapwd file=$ORACLE_HOME/dbs/orapwdg10g password=xxxxxxxxxx*
* It strikes me that not being able to run orapwd without the password argument is a bit of security flaw – This as been addressed at 11.1 and above.
Modifying /etc/oratab to reflect the new database name:
Change “orcl:/u01/app/oracle/product/10.2.0/db_1:W” to “dg10g:/u01/app/oracle/product/10.2.0/db_1:W”
Seting environment for new database name and sid:
[oracle@zinc ~]$ . oraenv ORACLE_SID = [orcl] ? dg10g [oracle@zinc ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 3 23:35:19 2009 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. SQL> conn / as sysdba Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 612368384 bytes Fixed Size 2085872 bytes Variable Size 167775248 bytes Database Buffers 436207616 bytes Redo Buffers 6299648 bytes SQL> create pfile from spfile; File created. SQL> shutdown ORA-01507: database not mounted ORACLE instance shut down. SQL> exit
Editing $ORACLE_HOME/dbs/initdg10g.ora
orcl.__db_cache_size=423624704 dg10g.__db_cache_size=436207616 orcl.__java_pool_size=4194304 orcl.__large_pool_size=4194304 dg10g.__large_pool_size=4194304 orcl.__shared_pool_size=171966464 orcl.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.background_dump_dest='/u01/app/oracle/admin/orcl/bdump' *.compatible='10.2.0.3.0' *.control_files='+DATA/orcl/controlfile/current.261.664244847','+DATA/orcl/controlfile/current.260.664244847' *.core_dump_dest='/u01/app/oracle/admin/orcl/cdump' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='orcl' *.db_recovery_file_dest='+DATA' *.db_recovery_file_dest_size=2147483648 *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.job_queue_processes=10 *.local_listener='(ADDRESS = (PROTOCOL=TCP) (HOST=zinc) (PORT=1521))' *.open_cursors=300 *.pga_aggregate_target=203423744 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=612368384 *.shared_servers=5 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/orcl/udump'
Note that some parameters have already been set for “dg10g”. In order to achieve what I want in terms of removing all references to orcl from the directory structure, all parameters that contain “orcl” in their value were updated. My perferred approach is to use “:%s/orcl/dg10g/g” in vi, but approaches to this type of task are often down to personal preference
My find and replace approach left me with two entires for __db_cache_size and __large_pool_size, so one of each was removed.
As I would be recreating the control files in the desired location the CONTROL_FILES parameter was further modified to simply provide the ASM disk group that the files are to be created in.
My final parameter file is show below:
dg10g.__db_cache_size=436207616 dg10g.__java_pool_size=4194304 dg10g.__large_pool_size=4194304 dg10g.__shared_pool_size=171966464 dg10g.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/dg10g/adump' *.background_dump_dest='/u01/app/oracle/admin/dg10g/bdump' *.compatible='10.2.0.3.0' *.control_files='+DATA','+DATA' *.core_dump_dest='/u01/app/oracle/admin/dg10g/cdump' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='dg10g' *.db_recovery_file_dest='+DATA' *.db_recovery_file_dest_size=2147483648 *.dispatchers='(PROTOCOL=TCP) (SERVICE=dg10gXDB)' *.job_queue_processes=10 *.local_listener='(ADDRESS = (PROTOCOL=TCP) (HOST=zinc) (PORT=1521))' *.open_cursors=300 *.pga_aggregate_target=203423744 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=612368384 *.shared_servers=5 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/dg10g/udump'
Creating the *_dest directories:
[oracle@zinc ~]$ mkdir -p /u01/app/oracle/admin/dg10g/adump [oracle@zinc ~]$ mkdir /u01/app/oracle/admin/dg10g/bdump [oracle@zinc ~]$ mkdir /u01/app/oracle/admin/dg10g/cdump [oracle@zinc ~]$ mkdir /u01/app/oracle/admin/dg10g/udump
In order to have Oracle automatically update the spfile with the location of the new control files via the = ALTER SYSTEM SET CONTROL_FILES command, an spfile should be created from the above pfile.
[oracle@zinc ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 3 23:42:20 2009 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. SQL> conn / as sysdba Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 612368384 bytes Fixed Size 2085872 bytes Variable Size 167775248 bytes Database Buffers 436207616 bytes Redo Buffers 6299648 bytes SQL> create spfile = '+DATA/DG10G/spfiledg10g.ora' from pfile; File created. SQL> shutdown ORA-01507: database not mounted ORACLE instance shut down. SQL> exit
The backup (manually created earlier) pfile will now be used to point to the new ASM spfile.
[oracle@zinc dbs]$ mv initdg10g.ora initdg10g.ora.pfile [oracle@zinc dbs]$ cp initdg10g.ora.spfile initdg10g.ora
Modifying initdg10g.ora to point to the correct spfile:
SPFILE='+DATA/gg02/spfilegg02.ora'
Using the controlfiles within +DATA/ORCL to create a new controlfiles under +DATA/DG10G:
[oracle@zinc ~]$ rman target / Recovery Manager: Release 10.2.0.4.0 - Production on Fri Apr 3 23:45:39 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database (not started) RMAN> startup nomount Oracle instance started Total System Global Area 612368384 bytes Fixed Size 2085872 bytes Variable Size 167775248 bytes Database Buffers 436207616 bytes Redo Buffers 6299648 bytes RMAN> restore controlfile from '+DATA/orcl/controlfile/current.261.664244847'; Starting restore at 03-APR-09 using channel ORA_DISK_1 channel ORA_DISK_1: copied control file copy output file name=+DATA/dg10g/controlfile/current.270.683250567 output file name=+DATA/dg10g/controlfile/current.271.683250767 Finished restore at 03-APR-09 RMAN> sql "alter system set control_files = ''+DATA/dg10g/controlfile/current.270.683250567'',''+DATA/dg10g/controlfile/current.271.683250767'' scope = spfile"; sql statement: alter system set control_files = ''+DATA/dg10g/controlfile/current.270.683250567'',''+DATA/dg10g/controlfile/current.271.683250767'' scope = spfile RMAN> shutdown Oracle instance shut down RMAN> startup mount connected to target database (not started) Oracle instance started database mounted Total System Global Area 612368384 bytes Fixed Size 2085872 bytes Variable Size 167775248 bytes Database Buffers 436207616 bytes Redo Buffers 6299648 bytes
Using report schema to see where the datafiles are currently located within ASM and then using BACKUP AS COPY to create a new copy of the files in the DG10G directory.
RMAN> report schema; Report of database schema List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 600 SYSTEM *** +DATA/orcl/datafile/system.256.664244797 2 460 UNDOTBS1 *** +DATA/orcl/datafile/undotbs1.258.664244797 3 400 SYSAUX *** +DATA/orcl/datafile/sysaux.257.664244797 4 5 USERS *** +DATA/orcl/datafile/users.259.664244797 List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 29 TEMP 32767 +DATA/orcl/tempfile/temp.268.664244883 RMAN> backup as copy database; Starting backup at 04-APR-2009 00:35:53 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile fno=00001 name=+DATA/orcl/datafile/system.256.664244797 output filename=+DATA/dg10g/datafile/system.259.683253353 tag=TAG20090404T003553 recid=17 stamp=683253441 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35 channel ORA_DISK_1: starting datafile copy input datafile fno=00002 name=+DATA/orcl/datafile/undotbs1.258.664244797 output filename=+DATA/dg10g/datafile/undotbs1.275.683253449 tag=TAG20090404T003553 recid=18 stamp=683253517 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15 channel ORA_DISK_1: starting datafile copy input datafile fno=00003 name=+DATA/orcl/datafile/sysaux.257.664244797 output filename=+DATA/dg10g/datafile/sysaux.257.683253525 tag=TAG20090404T003553 recid=19 stamp=683253586 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06 channel ORA_DISK_1: starting datafile copy input datafile fno=00004 name=+DATA/orcl/datafile/users.259.664244797 output filename=+DATA/dg10g/datafile/users.274.683253589 tag=TAG20090404T003553 recid=20 stamp=683253590 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy copying current control file output filename=+DATA/dg10g/controlfile/backup.258.683253593 tag=TAG20090404T003553 recid=21 stamp=683253595 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 Finished backup at 04-APR-2009 00:40:00 RMAN> list copy of database; List of Datafile Copies Key File S Completion Time Ckp SCN Ckp Time Name ------- ---- - -------------------- ---------- -------------------- ---- 17 1 A 04-APR-2009 00:37:21 1172322 04-APR-2009 00:26:45 +DATA/dg10g/datafile/system.259.683253353 18 2 A 04-APR-2009 00:38:37 1172322 04-APR-2009 00:26:45 +DATA/dg10g/datafile/undotbs1.275.683253449 19 3 A 04-APR-2009 00:39:46 1172322 04-APR-2009 00:26:45 +DATA/dg10g/datafile/sysaux.257.683253525 20 4 A 04-APR-2009 00:39:50 1172322 04-APR-2009 00:26:45 +DATA/dg10g/datafile/users.274.683253589 RMAN> switch database to copy; datafile 1 switched to datafile copy "+DATA/dg10g/datafile/system.259.683253353" datafile 2 switched to datafile copy "+DATA/dg10g/datafile/undotbs1.275.683253449" datafile 3 switched to datafile copy "+DATA/dg10g/datafile/sysaux.257.683253525" datafile 4 switched to datafile copy "+DATA/dg10g/datafile/users.274.683253589" RMAN> alter database open; database opened RMAN> report schema; Report of database schema List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 600 SYSTEM *** +DATA/dg10g/datafile/system.259.683253353 2 460 UNDOTBS1 *** +DATA/dg10g/datafile/undotbs1.275.683253449 3 400 SYSAUX *** +DATA/dg10g/datafile/sysaux.257.683253525 4 5 USERS *** +DATA/dg10g/datafile/users.274.683253589 List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 29 TEMP 32767 +DATA/orcl/tempfile/temp.268.664244883
Next job is to move the tempfile
RMAN> sql 'alter tablespace temp add tempfile'; sql statement: alter tablespace temp add tempfile RMAN> sql "alter database tempfile ''+DATA/orcl/tempfile/temp.268.664244883'' drop"; sql statement: alter database tempfile ''+DATA/orcl/tempfile/temp.268.66424488'' drop RMAN> exit
At this point the only files (used by the database/instance) left in +DATA/orcl are the Online logs. As far as I’m aware the only way to move these is to drop and recreate them, but with a little bit of thought and appropriate us of commands this can be done with the database open and in use.
Using the following command to create a new online redo log group. This relies on DB_CREATE_FILE_DEST, or DB_CREATE_ONLINE_LOG_DEST_n, parameters being set.
SQL> alter database add logfile group <a group number that does not already exist>;
Once the new online redo log group has been created in the DG10G directory within the ASM diskgroup, the existing online redo log groups in the ORCL directory were dropped using:
SQL> alter database drop logfile group <a group number that is not in use and is in the ORCL directory>;
Something to note is that, quite obviously, you cannot drop a log file group that is currently in use (ORA-01623), one that is need for crash recovery (ORA-01624) or reduced the number of redo log groups to less than 2 (ORA-01567)… Hence the comment about thought and appropriate commands. If you are trying this or doing something similar then the following commands might be helpful:
SQL> alter system switch logfile; SQL> alter system checkpoint; SQL> alter system archive log group <group requiring archiving>;
Anyway, after a bit of juggling I got what I wanted…
SQL> select member from v$logfile 2 order by member; MEMBER -------------------------------------------------------------------------------- +DATA/dg10g/onlinelog/group_1.262.683378145 +DATA/dg10g/onlinelog/group_1.263.683378123 +DATA/dg10g/onlinelog/group_2.264.683377935 +DATA/dg10g/onlinelog/group_2.265.683377913 +DATA/dg10g/onlinelog/group_3.266.683378249 +DATA/dg10g/onlinelog/group_3.267.683378229 6 rows selected.
Whilst finally writing this up it has occurred to me that there are other/better ways of achieving the same result, i.e., just duplicate the database… Nonetheless, I found this a useful exercise and if you read this far then it can’t have been too dull
Pingback: Blogroll Report 02/04 /2010 – 09/04/2010 « Coskan’s Approach to Oracle
Was looking for renaming 11g database and associated directories in ASM and found this to be exact hit! Quite easy to follow and very helpful, thanks for sharing.
Was looking for mvdir commands within asm but there is none. it would have made life easier by just re-creating control file.
anyways, thanks.
Hi ,
Thank you very much for the post . It really solved my problem of changing the dbname directory in ASM at time of refreshment from prod to dev
Pingback: Rename Database Having Datafiles on ASM – 11gR2 « Anand's Blog
Excellent article.. helped me immensely.. Thanks Mr Martin Nash.
Great. Really glad it was useful to you and thank you for the feedback.
Pingback: Orphaned Files in ASM « ORAganism