Oracle NID工具修改数据库DBID和数据库名称

        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