當我們手動copy了整個資料庫，並透過重建控制檔案給資料庫指定了新的dbname，但是卻不能給資料庫分配新的dbid.對於以上問題我們可以透過nid命令來對資料庫分配一個全新的dbid。同時需要注意rman也是透過dbid來區分資料庫。
一命令解釋

[oracle@source ~]$ nid help=yes
DBNEWID: Release 11.2.0.2.0 - Production on Thu Dec 5 00:09:50 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Keyword Description (Default)

----------------------------------------------------

TARGET Username/Password (NONE) 指定連線資料庫的使用者名稱和密碼

DBNAME New database name (NONE) DBNAME=new_db_name 改變資料庫的名字

LOGFILE Output Log (NONE) LOGFILE=logfile指定輸出訊息到指定的日誌檔案，預設nid覆蓋之前的日子檔案

REVERT Revert failed change NO 指定yes表明更改dbid失敗時能夠恢復之前的狀態

SETNAME Set a new database name only NO 指定yes表明僅僅更改資料庫db_name

APPEND Append to output log NO 指定yes標識輸出追加到已經存在的日誌檔案

HELP Displays these messages NO 指定yes顯示幫助資訊

注意：可以同時更改資料庫的dbid和db_name，也可以僅改變資料庫的db_name、抑或僅更改資料庫的dbid。語法分別如下：
改變dbid和db_name : nid target=sys/dhhzdhhz dbname=crm_test （也可以target=/）
僅改變db_name: nid target=sys/dhhzdhhz dbname=crm_test setname=yes （也可以target=/）
僅更改dbid： nid target=sys/dhhzdhhz （也可以target=/）

二使用nid的注意事項
1 確保有能夠對資料庫進行完全恢復的備份。
2 確保執行更改dbid操作時資料庫處於mounted狀態且mounted之前資料庫是經過shutdown immediate關閉的。
3 使用nid更改資料庫的dbid後，資料庫需要alter database open resetlogs啟動，啟動之後須對資料庫進行一次全備份，因為之前的備份和歸檔已經不能再使用了。
4 使用nid更改資料庫dbname後，需更改初始化引數檔案中的DB_NAME引數並重建密碼檔案。
5 使用nid不能更改全域性資料庫名。
6 確保所有資料檔案處於online狀態且不需要恢復。
7 儘量確保oracle沒有離線的資料檔案和只讀表空間，如果有使其正常化。
DBID是資料庫的唯一識別符號，在一些特殊場合會涉及到DBID的相關內容，本篇文章的目的是演示將DB_NAME的值從ORCL1修改為ORCL。
一.檢視當前的環境。
[oracle@rhel2 ~]$ echo $ORACLE_SID
orcl1
[oracle@rhel2 ~]$ sql

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 21 12:41:26 2011

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string ORCL1
SQL> show parameter service_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string ORCL1
SQL> show parameter instance_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string orcl1
SQL> select dbid,name from v$database;

DBID NAME
---------- ---------
1152218060 ORCL1

SQL> !
[oracle@rhel2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 21-NOV-2011 12:42:06

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rhel2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 20-NOV-2011 23:28:15
Uptime 0 days 13 hr. 13 min. 50 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rhel2)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "ORCL1_XPT" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl1" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rhel2 ~]$ exit
exit

二.啟動資料庫到MOUNT模式。
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 369098752 bytes
Fixed Size 2020864 bytes
Variable Size 113248768 bytes
Database Buffers 251658240 bytes
Redo Buffers 2170880 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

三.使用NID工具將DB_NAME從orcl1修改為orcl。
[oracle@rhel2 ~]$ nid target=sys/oracle dbname=orcl

DBNEWID: Release 10.2.0.1.0 - Production on Mon Nov 21 12:43:13 2011

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

Connected to database ORCL1 (DBID=1152218060)

Connected to server version 10.2.0

Control Files in database:
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/oradata/orcl/control02.ctl
/u01/app/oracle/oradata/orcl/control03.ctl

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

Proceeding with operation
Changing database ID from 1152218060 to 1295536737
Changing database name from ORCL1 to ORCL
Control File /u01/app/oracle/oradata/orcl/control01.ctl - modified
Control File /u01/app/oracle/oradata/orcl/control02.ctl - modified
Control File /u01/app/oracle/oradata/orcl/control03.ctl - modified
Datafile /u02/system01.dbf - dbid changed, wrote new name
Datafile /u02/sysaux01.dbf - dbid changed, wrote new name
Datafile /u02/users01.dbf - dbid changed, wrote new name
Datafile /u02/undotbs01.dbf - dbid changed, wrote new name
Datafile /u02/temp01.dbf - dbid changed, wrote new name
Control File /u01/app/oracle/oradata/orcl/control01.ctl - dbid changed, wrote new name
Control File /u01/app/oracle/oradata/orcl/control02.ctl - dbid changed, wrote new name
Control File /u01/app/oracle/oradata/orcl/control03.ctl - dbid changed, wrote new name
Instance shut down

Database name changed to ORCL.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORCL changed to 1295536737.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

四.新建密碼檔案。
[oracle@rhel2 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle entries=5

五.修改引數檔案中的DB_NAME引數值。
由於nid修改了控制檔案和資料檔案中的DB_NAME值，所以將引數檔案中的DB_NAME修改來和控制檔案、資料檔案相同，修改為ORCL。
[oracle@rhel2 ~]$ echo $ORACLE_SID
orcl1
[oracle@rhel2 ~]$ sql

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 21 12:43:33 2011

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 369098752 bytes
Fixed Size 2020864 bytes
Variable Size 113248768 bytes
Database Buffers 251658240 bytes
Redo Buffers 2170880 bytes
SQL> show parameter db_name

NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_name string
ORCL1
SQL> alter system set db_name=orcl scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 369098752 bytes
Fixed Size 2020864 bytes
Variable Size 113248768 bytes
Database Buffers 251658240 bytes
Redo Buffers 2170880 bytes
Database mounted.

確定DB_NAME修改之後，資料庫開啟必須以RESETLOGS的方式開啟。
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

注意：以resetlogs模式開啟資料庫之後需要立即執行全庫備份。

六.檢視修改後的環境。

修改後的資料庫DB_NAME和Service_name都會自動發生變化，instance_name不會發生變化。
SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string ORCL
SQL> show parameter service

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string ORCL
SQL> show parameter instance_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string orcl1

DBID和資料庫名稱都發生了變化。
SQL> select dbid,name from v$database;

DBID NAME
---------- ---------
1295536737 ORCL

SQL> !
監聽狀態中，Service的名稱自動變成了ORCL，這是由Service_name控制的，Instance沒有發生變化，這是由instance_name控制的。
[oracle@rhel2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 21-NOV-2011 12:45:06

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rhel2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 20-NOV-2011 23:28:15
Uptime 0 days 13 hr. 16 min. 50 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rhel2)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "ORCL" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "ORCL_XPT" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully

從以上的例子還可以看出，環境變數ORACLE_SID也沒有發生變化，這只是用於找到引數檔案而已。

重建控制檔案不會導致DBID和資料庫名稱發生變化。例子如下：
SQL> select dbid,name from v$database;

DBID NAME
---------- ---------
1295536737 ORCL

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 369098752 bytes
Fixed Size 2020864 bytes
Variable Size 117443072 bytes
Database Buffers 247463936 bytes
Redo Buffers 2170880 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u02/redo01.log' SIZE 50M,
GROUP 2 '/u02/redo02.log' SIZE 50M,
GROUP 3 '/u02/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u02/system01.dbf',
'/u02/sysaux01.dbf',
'/u02/users01.dbf',
'/u02/undotbs01.dbf'
CHARACTER SET ZHS16GBK
;

Control file created.

SQL> alter database open;

Database altered.

SQL> select dbid ,name from v$database;

DBID NAME
---------- ---------
1295536737 ORCL

SYS@PROD2 > show parameter name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string GUO
db_unique_name string GUO
global_names boolean FALSE
instance_name string GUO
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string GUO
SYS@PROD2 >

Oracle NID工具修改資料庫DBID和資料庫名稱

相關文章