Re: How to change ORACLE_SID?

Hi violin,

I used the following steps to change the oracle SID, it was successful. Actually I got this information from comp.databases.oracle.serves group.Let me know if you need anything more.

0. Take a backup of the database. Any valid hot or cold backup will do.

1. On your current database, issue an "alter database backup controlfile to trace;"
2. Go to your user_dump_dest in the filesystem on the database host. If you don't know where that is, look it up this way: "select value from v$parameter where name = 'user_dump_dest';"
3. Look for the trace file you just created by sorting the trace files by date: in Unix, "ls -lt" will sort by date. In VMS, I think it is something like "dir /date"
4. Copy the trace file to a location and file name where you can more easily work on it.
5. Edit the trace file. It will have a create controlfile statement in there, among other things. Delete everything before the CREATE CONTROLFILE statement. The first line should read: CREATE CONTROLFILE REUSE DATABASE "<database-name>" NORESETLOGS <archive-mode>
6. Change this line to read: CREATE CONTROLFILE SET DATABASE "<new-database-name>" RESETLOGS <archive-mode>
7. Go tho the bottom of the file, and delete everything after the semicolon at the end of the CREATE CONTROLFILE statement. If you are using OFA-compliant file paths, take this opportunity to change the sid in the paths in the CREATE CONTROLFILE statement to your new sid.
8. Save the file and exit your editor.
9. Shut down the database using SHUTDOWN IMMEDIATE or SHUTDOWN NORMAL.
10. If you are using OFA-compliant file paths, take this opportunity to change the sid directories to be named for the new sid.
11. Rename your init<sid>.ora file, and any other pfiles to be named for

    your new instance name. Update any references to the SID in these     files. Make sure that any file paths you change in here have also been

    changed at the filesystem level.

1. Update any references to the SID in your listener.ora, and distribute a new tnsnames.ora with the new database name, if you wish.
2. Set your ORACLE_SID environment variable to the new sid, and update any unix shell .login, .rc or any other shell startup files to set the ORACLE_SID to the new sid.
3. Move your old controlfiles out of the way, and start the database up

    in NOMOUNT mode (STARTUP NOMOUNT).

1. Run the create-coltrolfile script that you edited before, like this:

    SVRMGRL> @create-controlfile.sql (or whatever name you gave it).     You need to be running svrmgrl from the same directory where you saved

    the script, or else specify the full path name in the @ command.

1. When the create-controlfile command completes (it should be quite fast) the database will be in MOUNT mode.
2. Open the database with the RESETLOGS option, like this: SVRMGRL> alter database open resetlogs;

Good luck. I think I have everything in these steps. If anyone sees anything missing, just post it here. Starting from a backup, this is a great way to set up a test database that has a different name from your production database.

John

Violin wrote:

> Hello,
> I have 2 databases Oracle7.3 ,one on NT4.0 and
> the other on NetWare 4.10,
> 'Cause the 2 databases were created when installing,
> the ORACLE_SID were both ORCL.
> Now I want to change the ORACLE_SID of them,
> could any one teach me how to change ORACLE_SID?
> Thank you in advance.
> Please Cc to: violin.hsiao_at_mail.pouchen.com.tw
>
> Violin.
Received on Sat Oct 17 1998 - 01:44:24 CDT