Efficient Tips for Renaming Oracle Instance!!.

Efficient Tips for Renaming Oracle Instance!!.
Today we will discuss some efficient tips for renaming Oracle Instance.
Finding ORACLE_SID of Oracle Database:
There are a number of options available for finding ORACLE_SID for an Oracle database.
• You can find the current values of ORACLE_SID by querying Oracle data dictionary V$THREAD view.
• As ORACLE_SID is the instance name so you can look into init.ora file for instance_name parameter.
• It may be or may not be possible that your ORACLE_SID name is the same as your database name. Therefore you can check init.ora for db_name parameter.
• You can check /etc/oratab file for a list of instances on the server. Depending on your Oracle version this can be /var/opt/oracle/oratab
Changing ORACLE_SID after Creating Database:
In order to change ORACLE_SID after creating database you will have to recreate the Control file.
Generate Trace file for Control File Creation Script:
First of all you will need to generate an ASCII Trace file in $USER_DUMP_DEST directory. This trace file will contain the control file creation script.
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Shutdown Database:
Now shutdown the database.
Backup:
Now backup all datafiles, controlfiles, redolog files, archived redo log files etc.
Rename files:
Now you will need to rename the Init.ora and config.ora to Init.ora and Config.ora files in $ORACLE_HOME/DBS. Simialrly you will need to rename the old controlfiles (control01.old to control01.new).
Edit the Control File creation Script:
Now you will have to edit the Control File creation Script as follows.
STARTUP NOMOUNT;
CREATE CONTROLFILE SET DATABASE 'NEW_SID' RESETLOGS

;
Open Database:
Finally you will have to open your database with RESETLOGS.
ALTER DATABASE OPEN RESETLOGS;

Changing ORACLE_SID without recreating Database:
Shutdown Instance:
First of all shutdown the instance.
Backup Files:
Now backup all datafiles, controlfiles, redolog files, archived redo log files etc.
Re-define ORACLE_SID:
Now re-define the ORACLE_SID variable so that it now has a new value.
Change Locations:
Now change the locations to the DBS directory and rename the files init.ora file (Init.ora to Init.ora), control files (control01.old to control01.new), crdb.sql, crdb2.sql. If you have renamed old control files then make sure that the control_files parameter does not point to old file names. Similarly you will need to change the locations to RDBMS/ADMIN directory and rename the startup.sql file. Although this is optional but you must ensure that the contents of this file do not reference old init.ora files that have been renamed.
Rename Files:
Now you will need to rename the database files and redo log files
Change ORACLE_SID value:
Now change the ORACLE_SID to new value.
Startup Database:
If password file is enabled in ORACLE_HOME/DBS and ORAPW file exists then you will need to first issue ORAPWD FILE=ORAPW PASSWORD=?? ENTRIES= to create a new SID. Otherwise you can just start up the database and verify that it works.
Backup:
Finally you will have to shutdown the database and take backup of all control files, redo files and data files.
Start Instance:
Now when your instance is started, you will find your control file updated with the current ORACLE_SID.
Oracle DBNEWID Utility:
You can also make use of DBNEWID utility in Oracle versions 9i and beyond for this purpose.
Backup:
First of all you will have to backup the database.
Shutdown:
Now shutdown your database.
SHUTDOWN IMMEDIATE
Mount:
Now you will have to mount the database.
STARTUP MOUNT
Invoke the DBNEWID Utility:
Now you will have to invoke the DBNEWID utility (nid) specifying the new DBNAME.
NID TARGET=SYS/PASSWORD@TSH1 DBNAME=TSH2
Assuming the validation is successful the utility prompts for confirmation before performing the actions.
Shutdown:
Now again shutdown your database.
SHUTDOWN IMMEDIATE
Modify DB_NAME Parameter:
Now you will have to modify DB_NAME parameter in initialization parameter file. If will give an error but proceed with it.
STARTUP MOUNT
ALTER SYSTEM SET DB_NAME=TSH2 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
Creating Password File:
Now we will have to create a new password file.
ORAPWD FILE=C:\ORACLE\920\DATABASE\PWDTSH2.ORA PASSWORD=PASSWORD ENTRIES=10
Rename SPFILE:
Now you will have to rename the SPFILE. Make sure that it matches the new DBNAME.
Re-create Services:
On Windows environment you will have to recreate the service so as to make sure that it uses correct name and parameter file.
ORADIM -DELETE -SID TSH1
ORADIM -NEW -SID TSH2 -INTPWD PASSWORD -STARTMODE A -PFILE C:\ORACLE\920\DATABASE\SPFILETSH2.ORA
In UNIX/Linux environment you will have to just reset ORACLE_SID environment variable.
ORACLE_SID=TSH2;
export ORACLE_SID
Alter .ora File Setting:
Now you will have to alter the listener.ora and tnsnames.ora setting so that it matches the new database name.
Restart Listener:
Now restart the listener:
LSNRCTL RELOAD
Open Database:
Now you will have to open your database with RESETLOGS.
STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS;
Backup:
Finally you will have to backup your database.