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.

Iklan

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout /  Ubah )

Foto Google+

You are commenting using your Google+ account. Logout /  Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout /  Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout /  Ubah )

w

Connecting to %s