Oracle Rename Database SID in RedHat - Includes OEM

Product: Oracle RDBMS 10.2 - 11.2 in RedHat

While looking online for existing steps to rename Oracle SID and hostname, I come to realized that most DBA are blogging in Windows environment, and skip OEM configurations. Therefore, I am wrote this post to cover the Linux environment and includes the missing OEM part

Please note that OEM may not be functioning in your database prior of this change, so my post does not cover troubleshooting your broken OEM

1. Login as oracle user, or the user who each own ORACLE_HOME
2. Startup database in mount mode. You can use any approach to do so, but I am connecting locally
2.1. ORACLE_SID=[Old SID]
2.2. sqlplus '/ as sysdba'
2.3. SQL> alter system checkpoint;
2.4. SQL> shutdown immediate;
2.5. SQL> startup mount
3. nid dbname=[New SID] target=/
3.1. Press "y" to confirm rename
4. Set DB_NAME in spfile.ora
4.1. sqlplus '/ as sysdba'
4.2. startup nomount
4.3. alter system set db_name=[New SID] scope=spfile;
4.4. create pfile from spfile;
4.5. shutdown
5. Rename pfile
5.1. cd $ORACLE_HOME/dbs
5.2. mv pfile[Old SID].ora pfile[New SID].ora
6. Replace all names in pfile with vi, but you can use any editor to do so
6.1. Type ":%s/[Old SID]/[New SID]/g
7. Move all the following files to new directory name reflecting new SID
7.1. Control file
7.2. admin/[Old SID]
7.3. Online redo log
7.4. Temp file
7.5. Other tablespace
7.6. Flash recovery area
7.7. For 11.1 and 11.2: $ORACLE_BASE/diag/rdbms/[Old SID]
8. Startup database in MOUNT mode and observe alert.log to fix any missing steps
8.1. ORACLE_SID=[New SID]
8.2. sqlplus '/ as sysdba'
8.3. SQL> startup mount
9. Rename all files in database
9.1. SQL> select member from v$logfile;
9.2. SQL> alter database rename file '[Old filename]' to '[New file name]';
9.3. SQL>  select name from v$database;
9.4. SQL> alter database rename file '[Old filename]' to '[New file name]';
9.5. SQL> alter database open resetlogs;
10. Startup database with spfile
10.1. SQL> create spfile from pfile;
10.2. SQL> shutdown immediate
10.3. SQL> startup
11. Modify /etc/oratab to rename the SID





Next is Oracle Enterprise Manager, AKA the daemon started by "emctl start dbconsole"
1. UNIX hostname is modified by changing /etc/hosts and /etc/sysconfig/network
2. Reboot UNIX. Manually shutdown Oracle database if /etc/init.d is not setup to shutdown database
3. Rename hostname in $ORACLE_HOME/network/admin/listener.ora, if file exists
4. Rename following to reflect new hostname and SID
4.1. $ORACLE_OME/oc4j/j2ee/OC4J_DBConsole_[Old hostname]_[Old SID]
4.2. $ORACLE_HOME/[Old hostname]_[Old SID]
5. Re-create OEM DBConsole repository. Required entering SID, SYS password, SYSMAN password, and DBSNMP password (when create)
5.1. emctl stop dbconsole
5.2. emca -deconfig dbcontrol db -repos drop
5.3. emca -config dbcontrol db -repos create