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