利用nid工具修改db_name和instance_name

環境：
OS version: RHEL5.5
DB version: ORACLE10.2.0.4


一、利用oracle自帶工具nid修改db_name


在本例中，原db_name為CICUKNEW，需要更改到TPUKTEST
nid(New Database ID)工具為oracle自帶的工具，在$oracle_home/bin目錄下。
轉換步驟如下：
1.先停庫然後startup到mount狀態


SQL> select instance_name,status from v$instance;


INSTANCE_NAME    STATUS
---------------- ------------
cicuknewtest     OPEN


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.


Total System Global Area 1610612736 bytes
Fixed Size                  2084296 bytes
Variable Size             452985400 bytes
Database Buffers         1140850688 bytes
Redo Buffers               14692352 bytes
Database mounted.


2.執行nid轉換命令


SQL> !
[oracle@HQ-CICUTESTDB-01 ~]$ cd /home/oracle/oracle/product/10.2.0/db_1/bin
[oracle@HQ-CICUTESTDB-01 bin]$ id
uid=500(oracle) gid=501(oinstall) groups=500(dba),501(oinstall)
[oracle@HQ-CICUTESTDB-01 bin]$ nid target=sys/oracle dbname=tpuktest


DBNEWID: Release 10.2.0.4.0 - Production on Mon Jun 30 08:09:43 2014


Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to database CICUKNEW (DBID=1737794697)


Connected to server version 10.2.0


Control Files in database:
    /oradata/cicuknewtest/cicuknew/control01.ctl
    /oradata/cicuknewtest/cicuknew/control02.ctl
    /oradata/cicuknewtest/cicuknew/control03.ctl


Change database ID and database name CICUKNEW to TPUKTEST? (Y/[N]) => y


Proceeding with operation
Changing database ID from 1737794697 to 922964040
Changing database name from CICUKNEW to TPUKTEST
    Control File /oradata/cicuknewtest/cicuknew/control01.ctl - modified
    Control File /oradata/cicuknewtest/cicuknew/control02.ctl - modified
    Control File /oradata/cicuknewtest/cicuknew/control03.ctl - modified
    Datafile /oradata/cicuknewtest/cicuknew/system01.dbf - dbid changed, wrote new name
    Datafile /oradata/cicuknewtest/cicuknew/undotbs01.dbf - dbid changed, wrote new name
    Datafile /oradata/cicuknewtest/cicuknew/sysaux01.dbf - dbid changed, wrote new name
    Datafile /oradata/cicuknewtest/cicuknew/users01.dbf - dbid changed, wrote new name
    Datafile /oradata/cicuknewtest/cicuknew/tblspace_claim01.dbf - dbid changed, wrote new name
    Datafile /oradata/cicuknewtest/cicuknew/tblspace_claimidx01.dbf - dbid changed, wrote new name
    Datafile /oradata/cicuknewtest/cicuknew/TBLSPACE_CSMIG01.dbf - dbid changed, wrote new name
    Datafile /oradata/cicuknewtest/cicuknew/tblspace_oth01.dbf - dbid changed, wrote new name
    Datafile /oradata/cicuknewtest/cicuknew/tblspace_othidx01.dbf - dbid changed, wrote new name
    Datafile /oradata/cicuknewtest/cicuknew/tblspace_payment01.dbf - dbid changed, wrote new name
    Datafile /oradata/cicuknewtest/cicuknew/tblspace_paymentidx01.dbf - dbid changed, wrote new name
    Datafile /oradata/cicuknewtest/cicuknew/tblspace_prp01.dbf - dbid changed, wrote new name
    Datafile /oradata/cicuknewtest/cicuknew/tblspace_prpidx01.dbf - dbid changed, wrote new name
    Datafile /oradata/cicuknewtest/cicuknew/tblspace_reins01.dbf - dbid changed, wrote new name
    Datafile /oradata/cicuknewtest/cicuknew/tblspace_reinsidx01.dbf - dbid changed, wrote new name
    Datafile /oradata/cicuknewtest/cicuknew/tblspace_visa01.dbf - dbid changed, wrote new name
    Datafile /oradata/cicuknewtest/cicuknew/tblspace_visaidx01.dbf - dbid changed, wrote new name
    Datafile /oradata/cicuknewtest/cicuknew/temp01.dbf - dbid changed, wrote new name
    Control File /oradata/cicuknewtest/cicuknew/control01.ctl - dbid changed, wrote new name
    Control File /oradata/cicuknewtest/cicuknew/control02.ctl - dbid changed, wrote new name
    Control File /oradata/cicuknewtest/cicuknew/control03.ctl - dbid changed, wrote new name

--到這裡的時候，等待了相當長的時間，然後從alert日誌中看到DBNEWID utility 其實是已經執行成功了，於是便按了Ctrl+C。同時要退出這個會話（sys會話），否則不能成功關庫。

NID-00600: Internal Error - [28] [1013] [0] [0]

Change of database name and ID failed during validation - database is intact.
DBNEWID - Completed with validation errors.


alert日誌：
*** DBNEWID utility started ***
DBID will be changed from 1737794697 to new DBID of 922964040 for database CICUKNEW
DBNAME will be changed from CICUKNEW to new DBNAME of TPUKTEST
Starting datafile conversion
Setting recovery target incarnation to 1
Datafile conversion complete
Database name changed to TPUKTEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database TPUKTEST changed to 922964040.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open with RESETLOGS option.
Succesfully changed database name and ID.
*** DBNEWID utility finished succesfully ***
Shutting down instance: further logons disabled


3.停庫再到mount狀態下修改引數檔案的db_name：
SQL> startup mount
ORACLE instance started.


Total System Global Area 1610612736 bytes
Fixed Size                  2084296 bytes
Variable Size             452985400 bytes
Database Buffers         1140850688 bytes
Redo Buffers               14692352 bytes
ORA-01103: database name 'TPUKTEST' in control file is not 'CICUKNEW'


SQL> alter system set db_name=tpuktest scope=spfile;


System altered.


SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

4.重新建立password檔案


HQ-CICUTESTDB-01:cicutedb>orapwd file=orapwcicuknewtest password=oracle entries=5

PS：此時雖然db_name已經改成了tpuktest ，但instance_name此時是cicuknewtest ，所以，passwd檔案必須跟orapwcicuknewtest 一樣。


5.resetlogs開啟資料庫
HQ-CICUTESTDB-01:cicutedb>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jun 30 09:24:39 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2084296 bytes
Variable Size             452985400 bytes
Database Buffers         1140850688 bytes
Redo Buffers               14692352 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.


SQL> select dbid,name from v$database;

      DBID NAME
---------- ---------
922964040 TPUKTEST

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
cicuknewtest     OPEN

可以看到此時db_name已經更改成功，但instance_name還沒改。

二、更改instance_name
1、首先停庫

2、在bash_prfile修改oracle使用者的環境變數

3、修改 /etc/oratab 檔案：

#cicuknewtest:/home/oracle/oracle/product/10.2.0/db_1:N
tpuktest:/home/oracle/oracle/product/10.2.0/db_1:N
    
 4、更改dbs目錄下相關配置檔案
HQ-CICUTESTDB-01:cicutedb>mv hc_cicuknewtest.dat hc_tpuktest.dat   
HQ-CICUTESTDB-01:cicutedb>mv lkCICUKNEWTEST lkTPUKTEST
HQ-CICUTESTDB-01:cicutedb>mv spfilecicuknewtest.ora spfiletpuktest.ora
HQ-CICUTESTDB-01:cicutedb>mv orapwcicuknewtest orapwtpuktest

5、重建orapwd密碼檔案
HQ-CICUTESTDB-01:cicutedb>orapwd file=orapwtpuktest password=oracle entries=5

OPW-00005: File with same name exists - please delete or rename
HQ-CICUTESTDB-01:cicutedb>orapwd file=orapwtpuktest password=oracle entries=5 force=y

6、啟庫
SQL> startup
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2084296 bytes
Variable Size             385876536 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14692352 bytes
Database mounted.
Database opened.

7、檢驗
SQL>  select dbid,name from v$database;

      DBID NAME
---------- ---------
922964040 TPUKTEST


SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
tpuktest         OPEN