Question:
>Hi:
>
>Surely there's are ways to change SID without recreating a db.
>
>I've scanned through the posted messages but found none, unfortunately.
>
>Can someone give me a hint or point me to where I could find some info on
>this?
>
>Thanks in advance!
>
>Best regards,
>Cheong
>
>
 

Answer 1:

Please take care that the database sid and the database name are 2 different
things!
The database name is the name that belongs to the data (that is stored in your
database files)
The database sid is the name of the running processes that make the database
work.
Most of the time these are the same.

[Assuming you have Oracle on UNIX]
If you only want to change the SID:
- shutdown
- rename the sid in the file /etc/oratab
- rename the file $ORACLE_HOME/dbs/initSID.ora to the new SID
- startup
- ready (don't forget to tell all clients that the SID changed: change their
environment variable ORACLE_SID or their TNS names definition to reflect the
new SID)

If you want to change the database name:
- Use the procedure below (in the original reply)

    Erwin

D Parent wrote:

> In essence you use the ALTER DATABASE BACKUPCONTROLFILE TO TRACE command to
> create a control file recreation scipt, edit the script to set the name you
> want, shutdown the database, change the definition for ORACLE_SID and then
> use the script to rebuild the control file and restart the database
>
> <mailto:manash@superlinknet> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I have to change the sid of my database,
> > I am not very clear about the steps
> > should be followed to do this job.
> >
> > I need  help from any body has experience
> > of this nature or has any knoledge of this operation,
> > please
> >
> > thanks in advance
> > dhruba
> >
> >

--
Erwin Dondorp
<http://www.wxs.nl/~erwindon>

-------------------------------------------------------------------------
 

Answer 2:

To change the SID rename the init<SID>.ora file, reset the variable
ORACLE_SID and start the instance.

To rename the database, checkout how to recreate the controlfile from the
manuals (available on line @ http://technet.oracle.com/ - free registration)

Alex

Answer 3:

You need to 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 etc...for
Safety.

3. Rename the Init<oldSID>.ora and config<OLDSID>.ora to Init<NEWSID>.ora and
Config<NewSid>.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
...... <all others remain the Same>
;
5.alter database open resetlogs;
.
Hope this helps

Actually this Procedure Renames the Database as well as the 'SID' .. If you want
to Change only the SID , then carry out only the Step 3 +  Change ORACLE_SID
environmental Variable , /var/opt/oracle/oratab or /etc/oratab and all other
references to ORACLE_SID..

-Thiru

Answer 4:

It works! (of course) --- thanks a whole bunch!

What were omitted were (step 3): change the db_name parameter in initSID.ora
or configSID.ora file, and make the ifile to point to the proper
configSID.ora file (if the ifile exists in initSID.ora file) before one runs
the CREATE CONTROLFILE script.

Save for the annoying
ORA-00283: Recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
that scares me off initially --- only to find out that that was because of
the RESETLOGS parameter (that requires one to open the database using the
RESETLOGS option, which was already done at the end of the CREATE CONTROFILE
script).

Have fun!

Cheong