利用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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25465866/viewspace-1201005/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25465866/viewspace-1201005/