This content has been marked as final. Show 7 replies
1. do: alter database backup controlfile to trace;
2. extract the "create controlfile" command from the
3. shutdown the DB.
4. Change the DB-Name in your init<SID>.ora and change the init<NEWSID>.ora
5. for Unix : Change the SID in the /etc/oratab or /var/opt/oracle/oratab
6. Change the SID in your environment and source it
7. Startup the database to mount-status
8. Re-Create the controlfile with the statement from position 2.
9. Do a alter database rename global_name to <SID>
10.Change the TNS-Configuration accordingly
$ORACLE_HOME/network/admin/*.ora Look for SID and GLOBAL_NAME
Toni Lazarin wrote:OP is a newbie, may be he is an student or learner. encourage forum OP to develop their skills.
785092 wrote:This link helped me once ,
pls help me
i hope it will do the needful for you , Changing ORACLE SID
Status Level: Newbie
Registered: Jul 28, 2010
Total Posts: 2
Total Questions: 2 (2 unresolved)
Posters, please mind these common-sense rules when participating here: - When asking a question, provide all the details that someone would need to answer it. Consulting documentation first is highly recommended. - When answering a question, please be courteous and respectful; there are different levels of experience represented here. A poorly worded question is better ignored than flamed - or better yet, help the poster ask a better question. - It is considered good etiquette to reward answerers with points (as "helpful" - 5 pts - or "correct" - 10pts). - See more tips in the FAQ Thanks for doing your part to make this community as valuable as possible for everyone! - OTN
You can the your backup control file trace and making appropriatechanged on the same and recreating at the nomount instacne:
CREATE CONTROLFILE REUSE SET DATABASE "SID9" RESETLOGS ARCHIVELOG
GROUP 1 '/oracle/oradata/SID9/redo01.log' SIZE 50M,
GROUP 2 '/oracle/oradata/SID9/redo02.log' SIZE 50M,
GROUP 3 '/oracle/oradata/SID9/redo03.log' SIZE 50M
-- STANDBY LOGFILE
CHARACTER SET WE8ISO8859P9
You have to idenitfy all the ablove datafiles,logfiles,undofiles etc.
Just a notice: SID and DBNAME are two different things. But the usual practice is to have SID = DBNAME, so let me assume you want to change both SID and DBNAME.
Either you can do it manually by recreating the controlfile as orawiss suggests. You may discover some challenges around the way (for instance, for the command CREATE CONTROLFILE SET "new_dbname" to work, you have do delete or rename the old controlfiles first).
Or, you can change it easier by using nid utility. The detailed howto and all implications can be found on:
SQL> create pfile from spfile;
SQL> shutdown immediate;
nid TARGET=sys/password@oldname DBNAME=newname
copy the initoldname.ora to initnewname.ora and edit the DB_NAME parameter to reflect the new name
sqlplus / as sysdba
SQL> create spfile from pfile;
SQL> alter database open resetlogs;
Then don't forget to generate new password file for the new dbname (using orapw) and make changes in tnsnames.ora and listener.ora if needed.
Just make sure, the database has been properly shut down before changing the db_name either manualy by recreating controlfile or by the newid utility, as the renamed database has to be opened with resetlogs - making any crash recover of improperly shut down database impossible.
Martin Kucera wrote:Yes instance parameter INSTANCE_NAME has a default value set to DBNAME and you can set INSTANCE_NAME to a different value: this would be easy on Unix but on Windows we should remember that each Oracle instance has a Windows service with SID value hardcoded namely OracleService<SID>. Changing INSTANCE_NAME on Windows must also include removing service and adding a new service. These steps are documented by Tim Hall http://www.oracle-base.com/articles/9i/DBNEWID.php.
Just a notice: SID and DBNAME are two different things.