利用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
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 利用nid工具修改db_name時hang住
- ORACLE_SID,INSTANCE_NAME和DB_NAMEOracle
- Oracle修改instance_name、db_name、db_unique_name、service_namesOracle
- instance_name、db_name、ORACLE_SID、db_name等的區別Oracle
- oracle nid修改dbnameOracle
- Oracle NID工具修改資料庫DBID和資料庫名稱Oracle資料庫
- 關於db_name,instance_name等近似概念的理解????
- DB_NAME、DB_UNIQUE_NAME、SERVICE_NAME和INSTANCE_NAME等的區別
- nid修改資料庫名稱資料庫
- Oracle rac使用nid和dbms_backup_restore包修改dbid和dbnameOracleREST
- 用NID修改資料庫名稱資料庫
- 使用NID修改Oracle資料庫名Oracle資料庫
- NID修改資料庫名稱時候碰到NID-00137資料庫
- 利用DBNEWID工具來修改DBNAME
- oracle工具之nid命令的使用Oracle
- oracle sid,instance_name,db_name,oracle_sid之間的關係Oracle
- 修改資料庫db_name的方法資料庫
- 在RAC中用NID修改資料庫名稱資料庫
- 修改oracle例項名(sid)和資料庫名(db_name)Oracle資料庫
- 關於資料庫名(db_name)、例項名(instance_name)、ORACLE_SID資料庫Oracle
- 【原創】使用nid命令修改資料庫名稱資料庫
- (轉)關於資料庫名(db_name)、例項名(instance_name)、ORACLE_SID資料庫Oracle
- [轉]ORACLE中的DB_NAME,SERVICE_NAME,INSTANCE_NAME,ORACLE_SID,GLOBAL_DBNAMEOracle
- CUUG 筆記ORACLE db_name instance_name gobal_name service_name db_unique_name分析筆記OracleGo
- ORACL中的DB_NAME,SERVICE_NAME,INSTANCE_NAME,ORACLE_SID,GLOBAL_DBNAME介紹Oracle
- ORACLE中的DB_NAME,SERVICE_NAME,INSTANCE_NAME,ORACLE_SID,GLOBAL_DBNAME介紹Oracle
- 轉貼:關於資料庫名(db_name)、例項名(instance_name)、ORACLE_SID資料庫Oracle
- SID、ORACLE_SID、INSTANCE_NAME、DB_NAME、SERVER_NAMES、GLOBAL_DBNAME等區別OracleServer
- ORACLE資料庫修改資料庫名db_nameOracle資料庫
- [轉] 使用NID 修改資料庫名(Oracle9或以上)資料庫Oracle
- 記一次通過DBNEWID工具修改DBID時的NID-00135: There are 1 active threads錯誤thread
- Oracle修改(db_name,db_unique_name, servie_names,db_domain)方法OracleAI
- 在oracle 10g for solaris 5.10上修改db_name|db_idOracle 10g
- db_name和db_unique_name的區別
- 更改資料庫SID名和db_name名資料庫
- 關於ORACLE_SID和DB_NAME的理解Oracle
- DB_NAME DB_UNIQUE_NAME 和 SID 的理解
- DBNEWID的用法----NID命令