Re: How to change the SID

From: cschang <cschang_at_maxinter.net>
Date: Sat, 05 Mar 2005 10:51:11 -0500
Message-ID: <112jlbv7fr2bu86@corp.supernews.com>

Sybrand Bakker wrote:

> 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

done this
> - copy the init.ora to the new name

done this
> - copy the passwordfile to the new name

done
> - edit the init.ora to reflect the new locations of the controlfile > and background_dump_dest and user_dump_dest

here was my puzzle. If i change to new localion of controlfile, am I supposed to change the content of the controlfile in order to reflect the change? otherwise system would show error?? and the controlfile (.CTL) under the 9.2 are binary, unreadable.

> - 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

Thanks for you input and soryy make you tired. Some article on net mentioned to use a utility call "UID?", which can change the database name (not just SID). What is it?

C Chang Received on Sat Mar 05 2005 - 09:51:11 CST