Efficient Tips for Renaming Oracle Instance!!

 Author: Naura Mike

Page: 1 2 3

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<oldSID>.ora and config<OLDSID>.ora to Init<NEWSID>.ora and Config<NewSid>.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
<leave all others the Same>
;

Open Database:

Finally you will have to open your database with RESETLOGS.

ALTER DATABASE OPEN RESETLOGS; Continued...

Page: 1 2 3

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Performance Gains by Managing Space: Segments, Tablespace & Datafiles!!
   Fast Data Loading in Oracle: Exciting prospects!!
   Beware of Failed Refreshes, Troubleshoot Broken Links!!
   Performance Impact of Low Cardinality Leading Columns in an Index!!