Alrighty then... Let's finish this thing up, shall we?
As I had mentioned in part 1 of this 2 part post, I don't feel that a database rename is complete until all of the associated file system structures also reflect the name change. I mean, how'd you like to be the next DBA to be working on this database, now named 'JGDB', and not be able to quickly locate the data files because they are still under
$ORACLE_BASE/oradata/jgdb, as you'd expect?
First, let's shut down the database and put the init.ora file where it belongs and name it such that we don't have to define the PFILE location when starting the instance.
SQL> shutdown SQL> exit $ mv /home/oracle/jgdb_init.ora $ORACLE_HOME/dbs/initjgdb.ora
Another piece of housekeeping to attend to, if you haven't done so already, is to change your environment variables. Mine are being set in
export ORACLE_SID=jgdb export ORACLE_UNQNAME=jgdb # set for 'emctl start dbconsole'
Now we start back up again, without designating the PFILE location, as it should be found automatically now that we have renamed it and placed in in the proper location.
$ sqlplus /nolog SQL*Plus: Release 126.96.36.199.0 Production on Wed May 23 11:57:43 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. SQL> connect / as sysdba Connected to an idle instance. SQL> startup ORACLE instance started.
My steps below are based upon the following Oracle docs:
Creating Additional Copies, Renaming, and Relocating Control Files
Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces
Relocating and Renaming Redo Log Members
We have three major items to move: data files, redo logs, and control files.
I like to avoid any work that the system can do for me. (I like to call this 'efficiency'!) So, I will use SQL to create the SQL needed to update the database with the location to which I will be moving the redo logs and data files. I saved the following as
set linesize 200 set pagesize 100 set heading off spool rename_files.sql select 'ALTER DATABASE RENAME FILE ''' || file_name || ''' TO ''' || replace( file_name, 'orcl', 'jgdb') || ''';' from dba_data_files / select 'ALTER DATABASE RENAME FILE ''' || member || ''' TO ''' || replace( file_name, 'orcl', 'jgdb') || ''';' from v$logfile / spool off
You should now have a file named
rename_files.sqlin the directory in which you ran the script.
Shut down the database. It's time to move stuff... 'n things...
SQL > shutdown SQL > exit
Taking a look at
initjgdb.ora, you will note that there are a couple of parameters that refer to OS file system locations with 'orcl' in them. They are listed below. I have replaced 'orcl' with 'jgdb' as we did in part 1.
Moving the redo logs, data files, control files, and adump destination...
$ mv $ORACLE_BASE/oradata/orcl $ORACLE_BASE/oradata/jgdb $ mv $ORACLE_BASE/flash_recovery_area/orcl $ORACLE_BASE/flash_recovery_area/jgdb
Because we've updated
initjgdb.orawith the location of the control files and adump, we can start the database up again and mount it.
$ sqlplus /nolog SQL*Plus: Release 188.8.131.52.0 Production on Wed May 23 12:15:32 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. SQL> connect / as sysdba Connected to an idle instance. SQL> startup mount ORACLE instance started.
Now we can update the data file and redo log locations using our script
rename_files.sqlthat we created earlier and open the database for business...
SQL> @rename_files.sql Database altered. . . . Database altered. SQL> alter database open; Database altered.
If you have any feedback whatsoever on the steps I followed to rename my Oracle database, I'd be grateful for your input!