Modify the Oracle Instance name (sid) and database name (db_name)

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 the sid and dbname, mainly completed by two main processes: 1. Repair

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 the sid and dbname, mainly completed by two main processes: 1. Repair

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

2. Modify the Database Name (dbname)

Although the Instance name (sid) has been modified, the Database Name (dbname is still the original name orcl)

Idle> conn/as sysdba
Connected.
Sys @ ORCL> show parameter name

NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_file_name_convert string
Db_name string orcl
Db_unique_name string orcl
Global_names boolean FALSE
Instance_name string cnhtm
Lock_name_space string
Log_file_name_convert string
Service_names string orcl

Follow these steps to modify the Database Name (dbname)

2.1 switch online logs to make the database checkpoint

Sys @ ORCL> alter system archive log current;

System altered.

2.2 generate a script for recreating the control file

Sys @ ORCL> alter database backup controlfile to trace resetlogs;

Database altered.

2.3 shut down the database. You must close the database cleanly. You cannot shutdown abort.

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

2.4 enter $ ORACLE_BASE/admin/ Find the newly generated trc file in the/udump directory. This is the script for recreating the control file.

Oracle @ oracle [/oracle/admin/orcl/udump]> ls-lrt
Total 2608
-Rw-r ----- 1 oracle oinstall 577 Nov 7 orcl_ora_1201_trc

......

-Rw-r ----- 1 oracle oinstall 4407 Dec 20 cnhtm_ora_7789.trc

2.5 copy the trc file and name it ccf. SQL.

Oracle @ oracle [/oracle/admin/orcl/udump]> cp cnhtm_ora_7789.trc ccf. SQL

2.6 modify ccf. SQL

Find the startup nomount statement and delete all the rows above this line.
Search for all rows starting with -- and delete these rows.
Change all orcl to cnhtm, and change all ORCL to CNHTM.
Find the create controlfile reuse database... statement and change the REUSE to SET
Locate the recover database using backup controlfile statement and comment out the statement with the double horizontal line (--).
If you have the energy, you can modify the datafile and logfile in this script to use the new file name. In fact, this part can be modified without modification. I modified it for the purpose of testing, after modification, remember to rename the data file and log file, and match the corresponding data file and log file with the name here