| How to change the SID of the database ? [message #60605] |
Wed, 18 February 2004 18:27  |
cuihh6
Messages: 12
Registered: February 2004
|
Junior Member |
|
|
Oracle 8.1.7.4 on AIX 4.3.3
I have created a database, and its SID is "test".
Now i need to change the SID to "chhtest", What should i do ?
Any method be introduced to verify the change of SID ?
Thanks in advance.
|
|
|
|
| Re: How to change the SID of the database ? [message #60612 is a reply to message #60605] |
Thu, 19 February 2004 04:51  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
- shutdown the database
- change service_name,instance_name,mts_service in the init.ora [[ or if using spfile, alter system to change them accordingly when the instance was up]]
- rename the init.ora/spfile.ora accordingly (for eg initnewsid.ora)
- rename the password file accordinly(for eg orapwnewsid )
- Edit listener.ora,tnsnames.ora to change the SID
- Edit /var/opt/oracle/oratab to change the SID
- export ORACLE_SID=[[newsid]]
- restart the listener process
- startup the database
- confirm the instance_name change by
SQL>select instance_name from v$instance;
and
ps -ef|grep [[newsid]]
I may have missed something,but this should work.
-Thiru
|
|
|
|
| Re: How to change the SID of the database ? [message #60613 is a reply to message #60612] |
Thu, 19 February 2004 05:25 |
Prasad
Messages: 104
Registered: October 2000
|
Senior Member |
|
|
Hi
Follow What Thiru has mentioned and One step I think missed
When the database is up, run this command to generate control file
sql> alter database backup controlfile to trace.
This Generates a readble file in user_dump. Goto that directory and change the create controlfile to create database command.
In init.ora change the path of new database control files.
Since You have not mentioed its on NT Or Unix
In NT you have to create new service using oradim utility.
Regards
Prasad
|
|
|
|
| Re: How to change the SID of the database ? [message #60614 is a reply to message #60613] |
Thu, 19 February 2004 05:41 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Prasad,
Recreating controlfile as you have mentioned is necessary only for changing the database name, not the SID. The original poster only wanted to change the SID, I believe.
If you want to change the database name,then I'd do it with 'nid' utility in 9i. If on 8i & prior, then it involves creating the control file trace and SET DATABASE when recreating the controlfile etc .
-Thiru
|
|
|
|
| Re: How to change the SID of the database ? [message #60618 is a reply to message #60614] |
Thu, 19 February 2004 20:23 |
Prasad
Messages: 104
Registered: October 2000
|
Senior Member |
|
|
Hi Thiru
Thanks for the update, I heard about that utility but never used(Noteven 9i upgrade exam talks abt this). Moreover most of the time I interpret SID and database name one and the same.Unless told explicitly. Anyhow The original got enuf information for both cases.
Regards
Prasad
|
|
|
|
| Re: How to change the SID of the database ? [message #60653 is a reply to message #60612] |
Tue, 24 February 2004 03:38 |
cuihh6
Messages: 12
Registered: February 2004
|
Junior Member |
|
|
Hi Thiru
I have tried like you say.
But When i startup the database, the following error info appears:
SVRMGR> startup pfile=/oracle/app/oracle/admin/test2/inittest2.ora
ORACLE instance started.
Total System Global Area 94763132 bytes
Fixed Size 116860 bytes
Variable Size 77697024 bytes
Database Buffers 16777216 bytes
Redo Buffers 172032 bytes
ORA-01103: database name 'TEST1' in controlfile is not 'TEST2'
Here test1 is the old SID and test2 is the new one.
And I can not connect to the database whether on the localhost or through network.(ORA-01033: ORACLE initialization or shutdown in progress)
|
|
|
|
| Re: How to change the SID of the database ? [message #60654 is a reply to message #60653] |
Tue, 24 February 2004 04:04 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Hi
Did you recreate the controlfile also with TEST2 ? You shouldnt ,if you are just changing the SID. If you want to change the database name also,then you will be recreating the controlfile and also changing db_name value in the init.ora file.
SID is not the same as the database name.
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
