Modify the sid and dbname of an Oracle database in Linux

Sometimes we need to modify the sid and dbname of the database. In addition to using rman for backup and recovery, we can also manually modify it, mainly through two main processes:
1. Modify the Instance name (SID)
2. Modify the Database Name (dbname)

The following shows how to change the database sid and dbname from orcl to cnhtm:

1. Modify the Instance name (sid)

1.1 check the original database instance name (sid)

Oracle @ oracle [/home/oracle]> echo $ ORACLE_SID
Orcl
Oracle @ oracle [/home/oracle]> sqlplus/as sysdba
SQL * Plus: Release 10.2.0.1.0-Production on Sun Dec 20 11:14:49 2009
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
Sys @ ORCL> select instance from v $ thread;
INSTANCE
--------------------------------------------------------------------------------
Orcl

1.2 shut down the database

Note that you cannot use shutdown abort. You can only use shutdown immediate or shutdown normal.

Sys @ ORCL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Sys @ ORCL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options

1.3 modify oracle user's ORACLE_SID environment variable, for example, from orcl to cnhtm

Oracle @ oracle [/home/oracle]> cat ~ /. Bash_profile | grep-I sid
ORACLE_SID = cnhtm
Export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH

1.4 modify the/etc/oratab file and change the sid Name from the old one to the new one, for example, from orcl to cnhtm

Oracle @ oracle [/home/oracle]> cat/etc/oratab
......
Cnhtm:/oracle/apps/10.1: Y
+ ASM:/oracle/app/10.1: Y

1.5 go to the $ ORACLE_HOME/dbs directory

Modify all files whose names contain the original sid to the corresponding new sid
For example, if I change the following file to the corresponding file

Hc_orcl.dat-> hc_cnhtm.dat
LkORCL-> lkCNHTM
Orapworcl-> orapwcnhtm
Snapcf_orcl.f-> snapcf_cnhtm.f
Spfileorcl. ora-> spfilecnhtm. ora

1.6 make the new ORACLE_SID environment variable take effect

Oracle @ oracle [/oracle/app/10.1/dbs]> .~ /. Bash_profile
Oracle @ oracle [/oracle/app/10.1/dbs]> echo $ ORACLE_SID
Cnhtm

1.7 re-build the password file

Because the password file cannot be used in the new instance after it is renamed

Oracle @ oracle [/oracle/app/10.1/dbs]> orapwd file = $ ORACLE_HOME/dbs/orapw $ ORACLE_SID password = oracle entries = 5 force = y
Oracle @ oracle [/oracle/app/10.1/dbs]> ls-lrt orapw *
-Rw-r ----- 1 oracle oinstall 2048 Dec 20 orapwcnhtm

1.8 start the database

Oracle @ oracle [/oracle/app/10.1/dbs]> sqlplus/as sysdba
SQL * Plus: Release 10.2.0.1.0-Production on Sun Dec 20 11:29:53 2009
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connected to an idle instance.
Idle> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 62916876 bytes
Database Buffers 96468992 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.

1.9 check the database instance name.

Check the database instance name using the following statement and find that the Instance name has changed from orcl to cnhtm.

Idle> select instance from v $ thread;
INSTANCE
--------------------------------------------------------------------------------
Cnhtm

1 2 Next Page