ORacle - Change SID name after Creating Database

Change SID name after Creating Database

Recreate the Control file to achieve this .

1. SVRMGR>Alter Database backup controlfile to trace;
This will generate an Ascii Trace file in $USER_DUMP_DEST directory which will have the Control File Creation Script.

2. Shutdown the Database and Do a Physical Backup of all the Datafiles,Controlfiles,RedoLog files,Archived Redo log files etc

3. Rename the Init.ora and config.ora to Init.ora and Config.ora files in $ORACLE_HOME/dbs This is to prevent any errors during Database Startups looking for default 'pfile' names.

4. Rename the Old Controlfiles to say control01.old etc This is to Create New Controlfile and not reuse the existing one.

5. Edit the Control File creation Script ..It should read like:
Startup nomount;
Create Controlfile set Database 'NEW_SID' Resetlogs
......
;

6. Open your database:
alter database open resetlogs;