Rename Oracle SID


Some of my fellows ask me how to change the oracle SID. For those who never done these steps see that this is a difficult thing, but it’s quite simple though.

1. Login as sysdba
[ora9i@training01 ~]$ sqlplus “/ as sysdba”
SQL*Plus: Release 9.2.0.4.0 – Production on Mon Nov 13 16:23:56 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 – Production

2. Note the user dump directory
SQL> show parameter user_dump
NAME TYPE VALUE
———————————— —————————————–
user_dump_dest string /apps/ora9i/OraHome1/admin/ora
sid/udump

3. Switch logfile several times using the following command.
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;

4. Create the basic SQL script for renaming the SID.
SQL> alter database backup controlfile to trace;
Database altered.

5. Shutdown the database
SQL> shutdown
SQL> exit

6. Find the file created from step 4 in directory in step 2, and rename it to appropriate name.
[ora9i@training01 ~]$ cd /apps/ora9i/OraHome1/admin/orasid/udump
[ora9i@training01 ~]$ ls –ltr
-rw-r—– 1 ora9i dba 1617 Nov 3 16:05 oldsid_ora_7120.trc
-rw-r—– 1 ora9i dba 1617 Nov 3 16:08 oldsid_ora_7837.trc
-rw-r—– 1 ora9i dba 1617 Nov 3 16:13 oldsid_ora_3482.trc
-rw-r—– 1 ora9i dba 1616 Nov 3 16:16 oldsid_ora_4412.trc
-rw-r—– 1 ora9i dba 1617 Nov 3 17:59 oldsid_ora_1818.trc
-rw-r—– 1 ora9i dba 3284 Nov 6 11:31 oldsid_ora_1770.trc
-rw-r—– 1 ora9i dba 7095 Nov 13 21:52 oldsid_ora_32410.trc
[ora9i@training01 ~]$ mv oldsid_ora_32410.trc renameorasid.sql

7. Delete unwanted lines and update as necessary, the result appears as below. Please notify the one in bold.
[ora9i@training01 ~]$ vi renameorasid.sql
CREATE CONTROLFILE set DATABASE “orasid” RESETLOGS NOARCHIVELOG
— SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 (
‘/apps/ora9i/OraHome1/database/datafiles/orasid/logg1m1orasid.log’,
‘/apps/ora9i/OraHome1/database/datafiles/orasid/logg1m2 orasid.log’
) SIZE 8M,
GROUP 2 (
‘/apps/ora9i/OraHome1/database/datafiles/orasid/logg2m1orasid.log’,
‘/apps/ora9i/OraHome1/database/datafiles/orasid/logg2m2orasid.log’
) SIZE 8M,
GROUP 3 (
‘/apps/ora9i/OraHome1/database/datafiles/ orasid/logg3m1orasid.log’,
‘/apps/ora9i/OraHome1/database/datafiles/orasid/logg3m2orasid.log’
) SIZE 8M
— STANDBY LOGFILE
DATAFILE
‘/apps/ora9i/OraHome1/database/datafiles/orasid/systemorasid.dbf’,
‘/apps/ora9i/OraHome1/database/datafiles/ orasid/undoorasid.dbf’,
‘/apps/ora9i/OraHome1/database/datafiles/orasid/exampleorasid.dbf’,
‘/apps/ora9i/OraHome1/database/datafiles/ orasid/toolsorasid.dbf’,
‘/apps/ora9i/OraHome1/database/datafiles/orasid/userorasid.dbf’
CHARACTER SET US7ASCII
;

8. Create/move directories and rename the datafiles.
[ora9i@training01 ~]$ mkdir -p /apps/ora9i/OraHome1/admin/orasid
[ora9i@training01 ~]$ mkdir /apps/ora9i/OraHome1/admin/orasid /cdump
[ora9i@training01 ~]$ mkdir /apps/ora9i/OraHome1/admin/orasid/udump
[ora9i@training01 ~]$ mkdir /apps/ora9i/OraHome1/admin/orasid/bdump
[ora9i@training01 ~]$ cd /apps/ora9i/OraHome1/database/datafiles/
[ora9i@training01 ~]$ mv oldsid orasid
[ora9i@training01 ~]$ cd orasid
[ora9i@training01 ~]$ mv logg1m1oldsid.log logg1m1orasid.log
[ora9i@training01 ~]$ mv logg1m2oldsid.log logg1m2orasid.log
[ora9i@training01 ~]$ mv logg2m1oldsid.log logg2m1orasid.log
[ora9i@training01 ~]$ mv logg2m2oldsid .log logg2m2orasid.log
[ora9i@training01 ~]$ mv logg3m1oldsid.log logg3m1orasid.log
[ora9i@training01 ~]$ mv logg3m2oldsid.log logg3m2orasid .log
[ora9i@training01 ~]$ mv systemoldsid.dbf systemorasid.dbf
[ora9i@training01 ~]$ mv undooldsid.dbf undoorasid.dbf
[ora9i@training01 ~]$ mv example oldsid.dbf exampleorasid.dbf
[ora9i@training01 ~]$ mv toolsoldsid.dbf toolsorasid.dbf
[ora9i@training01 ~]$ mv useroldsid.dbf user orasid.dbf
[ora9i@training01 ~]$ mv tempoldsid.dbf temporasid.dbf

9. Update initialization parameter file
[ora9i@training01 ~]$ cd /apps/ora9i/OraHome1/dbs/
[ora9i@training01 ~]$ vi initorasid.ora
*.compatible=’9.2.0.0.0′
*.control_files=’/apps/ora9i/OraHome1/database/datafiles/orasid/control1orasid.ctl’,’/apps/ora9i/OraHome1/database/datafiles/ orasid/control2orasid.ctl’,’/apps/ora9i/OraHome1/database/datafiles/orasid/control3orasid.ctl’
*.core_dump_dest=’/apps/ora9i/OraHome1/admin/orasid /cdump’
*.user_dump_dest=’/apps/ora9i/OraHome1/admin/orasid/udump’
*.background_dump_dest=’/apps/ora9i/OraHome1/admin/orasid/bdump’
*.db_block_size=8192
*.db_cache_size=134217728
*.db_domain=”
*.db_file_multiblock_read_count=32
*.db_files=1024
*.db_name=’orasid’
*.dml_locks=500
*.fast_start_mttr_target=300
*.global_names=FALSE
*.instance_name=’ orasid’
*.java_pool_size=52428800
*.job_queue_processes=2
*.log_buffer=1048576
*.max_dump_file_size=’10240′
*.max_enabled_roles=48
*.open_cursors=400
*.os_authent_prefix=”
*.processes=150
*.remote_login_passwordfile=’NONE’
*.remote_os_authent=TRUE
*.shared_pool_size=54525952
*.sort_area_size=524288
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS’
*.utl_file_dir=’*’

9. Create password file
[ora9i@training01 ~]$ cd /apps/ora9i/OraHome1/dbs/
[ora9i@training01 ~]$ orapwd file=orapw password=o entries=10

10. Update the environment file in ora9i home directory, and then relogin
[ora9i@training01 ~]$ cd ~
[ora9i@training01 ~]$ vi .login
setenv ORACLE_SID orasid
setenv ORACLE_OWNER ora9i
setenv ORACLE_BASE /apps/ora9i
setenv ORACLE_HOME ${ORACLE_BASE}/OraHome1

11. Running the script.
[ora9i@training01 ~]$ sqlplus “/ as sysdba”
SQL> startup nomount
SQL> @renameorasid.sql
SQL> recover database using backup controlfile;
SQL> alter database open resetlogs;
SQL> alter database rename global_name to orasid;

12. Restart the database
[ora9i@training01 ~]$ sqlplus “/ as sysdba”
SQL> shutdown
SQL> startup

13. Setting up the client
[ora9i@training01 ~]$ cd /apps/ora9i/OraHome/network/admin
[ora9i@training01 ~]$ vi tnsadmin.ora
orasid =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orasid)
)
)

You see that the main steps are step 4 and 7, which create the script and rename.
Okay, you’re now ready with your new name of the database instance. Good luck.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s