Renaming Your Oracle Instance, Part 2

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/orcl rather than $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 ~/.bashrc

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 11.2.0.1.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
http://docs.oracle.com/cd/E11882_01/server.112/e25494/control003.htm#i1106242 

Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces
http://docs.oracle.com/cd/E11882_01/server.112/e25494/control003.htm#i1006277

Relocating and Renaming Redo Log Members
http://docs.oracle.com/cd/E11882_01/server.112/e25494/onlineredo004.htm#i1006447

---

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 create_rename_script.sql

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.sql in 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.

*audit._file_dest='home/oracle/app/oracle/admin/jgdb/adump'

*.control_files='/home/oracle/app/oracle/oradata/jgdb/control01.ctl','/home/oracle/app/oracle/flash_recovery_area/jgdb/control02.ctl'

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.ora with the location of the control files and adump, we can start the database up again and mount it.

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.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.sql that we created earlier and open the database for business...

SQL> @rename_files.sql
Database altered.
.
.
.
Database altered.

SQL> alter database open;
Database altered.

Done!

---

If you have any feedback whatsoever on the steps I followed to rename my Oracle database, I'd be grateful for your input!

---