Re: How to change the SID

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 05 Mar 2005 06:53:52 +0100
Message-ID: <pshi21ti17rundp3cjhvnip649mfv42nge@4ax.com>

On Fri, 04 Mar 2005 23:49:01 -0500, cschang <cschang_at_maxinter.net> wrote:

>I have a 9.2.0.5 databse set up locally. I wan to change the SID so my
>weblogic 8.1.4 can talk to it. Sorry I can not change Weblogic side to
>match Oracle side. When I look into the solutions (most of them are for
>8i and before), most of them mention to change alter the controlfile so
>that they can be called by new SID. But how? the .ctl file under 9.2
>are binary coded and can not be read. Follwoing steps were I did before
>I stuck.
>
>C:\>sqplus 'sys/<password>@local as sysdba'
>SQL>create pfile='%path%\pfile<oldSID>.ora' from
>spfile='%path%\spfile<oldSID>.ora';
>
>file created
>SQL>shutdown immediate
>
>Then i used the notepad to open the pfile<oldSID>.ora and change all the
> <oldSID> to <newSID> inside the file and save as a pfile<newSID>.ora
>in the a new oracle\admin\newSID\ and also rename the
>d:\oracle\oradata\oldSID\ to d:\oracle\oradata\newSID\ where all the DBF
>and CTL are located.
>
>SQL>startup pfile=d:\oracle\admin\newSID\pfile<newSID>.ora
>
>SQL>....
>...
>error : unable to open the file
>error: control file can not be located or something like such.
>
>I can understood why error, because the new pfile includes a new CTL
>which can be found no where. Those new CTl files were supposely
>modified from old ones. But I could not modifed them. Although I have
>tried one with
>SQL>alter database backup controlfile to trace;
>
>As some article suggested. I really have no clue what this did. what it
>related to the new controlfile. Can someone give me some real steps for
> this change on 9i. Thanks.
>
>Chisoon

I don't know for sure and I didn't verify, but I seem to recall the procedure is documented.
<sarcasm mode on>
Obviously it is faster to start to hack yourself out, and run to this newsgroup for help than reading the doco. <sarcasm mode off>

You need to

- shutdown the old instance
- copy the init.ora to the new name
- copy the passwordfile to the new name
- edit the init.ora to reflect the new locations of the controlfile

and background_dump_dest and user_dump_dest - As there are no database files listed in the init.ora those don't need to be changed.
- If you use the @ instead of the sid you don't need to edit anything at all, but in your situation I wouldn't try this.

- *copy the controlfiles to the new location*
- *copy the datafiles to the new location*
- *create the new service by issuing the oradim command in a DOS box*
- *start the service, not the instance*
- startup nomount the database
- follow the normal procedure to rename database files, as documented
- alter database open resetlogs

And that is all there is to it.

You are making me feel a bit tired.

--
Sybrand Bakker, Senior Oracle DBA

Received on Fri Mar 04 2005 - 23:53:52 CST