使用NID修改DBID和DBNAME实验

在Oracle中，DBID和DBNAME是两个低调但又是及其重要的对象。作为标记信息，DBID和DBNAME广泛的出现在参数文件、密码文件、数据文件和日志文件中，更有甚者在备份集合归档日志中。笔者看过一位前辈的文章中，强调称职DBA应该将管理数据库的DBID牢记于心。

一般情况下，已经创建好的数据库是不需要修改DBID和DBNAME信息的。因为，修改这些信息意味着Oracle关键信息的变化，和大量备份数据的废止。如果需要进行变更，一定要按照固定的操作程序，将对应的数据内容修改完好。

1、修改DBID和DBNAME，是一个大事情

DBID是一个十进制数字，Oracle依据唯一性算法计算得到作为内部数据库的标记信息。在数据文件、日志和备份集合中，DBID都是作为重要标记进行使用。DBNAME是用户设置的项目内容，分布在密码文件、参数文件和数据文件中。如果存在修改DBID的情况，Online Redo Log需要进行ResetLog操作，原有的归档和备份文件都需要废除失效。

对修改DBID和DBNAME操作，我们一定要明确后续调整和修复的范围动作。如果修改DBID，所有之前进行的备份和归档日志就没有效用了。整个过程就和全新创建数据库（除了数据文件存在）没有差异。我们启动数据库Open的时候，就需要resetlogs模式启动，刷新废除所有的online redo logs组，开启一个新的Sequence序列。在投产条件下，如果我们修改了DBID，就必须立即进行数据库备份动作，避免数据库裸奔。

如果修改了DBNAME，问题简单一些，是不需要我们resetlogs的。之前的备份集合和归档日志还是可以继续使用。DBNAME修改要求手工的在SPFile或者Pfile中修改初始化参数，密码文件也需要进行修改。注意：对应控制文件的备份，如果希望使用只前备份的控制文件恢复，就需要使用之前的参数文件进行启动。

下面，通过一系列的实验，演示如何使用Oracle NID工具进行DBID和DBNAME修改。

2、同时修改DBID和DBNAME

当前笔者使用11.2.0.4数据库进行测试，数据库处在归档模式下。当前数据库名称为MYTEDB，对应的DBID如下所示。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

SQL> select dbid, name from v$database;

      DBID NAME

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

2764682050 MYTEDB

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival            Enabled

首先，确认备份和归档日志情况。查看环境变量信息。

[oracle@MYTElife ~]$ env | grep ORA

ORACLE_SID=MYTEdb

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

[oracle@MYTElife ~]$ cd $ORACLE_HOME/bin

[oracle@MYTElife bin]$ pwd

/u01/app/oracle/product/11.2.0/dbhome_1/bin

如果配置了DBConsole，需要删除DBConsole对象。之后完全关闭，重新启动进行mount状态。同时，确认一下Oracle Net目录中三个文件：tnsnames.ora、listener.ora和sqlnet.ora，其中包括DBNAME项目，都需要修改。

[oracle@MYTElife bin]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 19 18:45:58 2016

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

SQL> conn / as sysdba

Connected.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 3540881408 bytes

Fixed Size                  2258320 bytes

Variable Size            855640688 bytes

Database Buffers        2667577344 bytes

Redo Buffers              15405056 bytes

Database mounted.

进入$ORACLE_HOME/bin文件夹，调用nid命令。

[oracle@MYTElife bin]$ nid target=sys/oracle dbname=testdb

DBNEWID: Release 11.2.0.4.0 - Production on Wed Oct 19 18:48:44 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to database MYTEDB (DBID=2764682050)

Connected to server version 11.2.0

Control Files in database:

    /u01/app/oracle/oradata/MYTEDB/controlfile/o1_mf_bw7762ov_.ctl

    /u01/app/oracle/fast_recovery_area/MYTEDB/controlfile/o1_mf_bw7762v3_.ctl

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

Proceeding with operation

Changing database ID from 2764682050 to 2708979596

Changing database name from MYTEDB to TESTDB

    Control File /u01/app/oracle/oradata/MYTEDB/controlfile/o1_mf_bw7762ov_.ctl - modified

    Control File /u01/app/oracle/fast_recovery_area/MYTEDB/controlfile/o1_mf_bw7762v3_.ctl - modified

    Datafile /u01/app/oracle/oradata/MYTEDB/datafile/o1_mf_system_bw773xok_.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/MYTEDB/datafile/o1_mf_sysaux_bw773xpr_.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/MYTEDB/datafile/o1_mf_undotbs1_bw773xqo_.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/MYTEDB/datafile/o1_mf_users_bw773xrv_.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/MYTEDB/datafile/o1_mf_MYTEdev_bw8xbqrz_.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/MYTEDB/datafile/o1_mf_inttestt_bw8xdnkt_.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/MYTEDB/datafile/o1_mf_epssite_by19vtnh_.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/MYTEDB/datafile/o1_mf_temp_bw776mow_.tm - dbid changed, wrote new name

    Control File /u01/app/oracle/oradata/MYTEDB/controlfile/o1_mf_bw7762ov_.ctl - dbid changed, wrote new name

    Control File /u01/app/oracle/fast_recovery_area/MYTEDB/controlfile/o1_mf_bw7762v3_.ctl - dbid changed, wrote new name

    Instance shut down

Database name changed to TESTDB.

Modify parameter file and generate a new password file before restarting.

Database ID for database TESTDB changed to 2708979596.

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 NID名称将数据文件中的DBID和Name信息修改，并且在提示中提醒了需要修改内容。

在alert log中，我们看到了DBID和Name的变化过程。

Wed Oct 19 18:49:04 2016

*** DBNEWID utility started ***

DBID will be changed from 2764682050 to new DBID of 2708979596 for database MYTEDB

DBNAME will be changed from MYTEDB to new DBNAME of TESTDB

Starting datafile conversion

Datafile conversion complete

Database name changed to TESTDB.

Modify parameter file and generate a new password file before restarting.

Database ID for database TESTDB changed to 2708979596.

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 ***

下面，需要修改Spfile中的dbname信息。方法和以前用到的相同，都是通过spfile生成pfile，手工修改其中的db_name参数。

[oracle@MYTElife trace]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 19 18:51:26 2016

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

SQL> conn / as sysdba

Connected to an idle instance.

SQL> create pfile from spfile;

File created.

修改生成initMYTEdb.ora文件。

[oracle@MYTElife trace]$ cd $ORACLE_HOME/dbs

[oracle@MYTElife dbs]$ ls -l

total 9544

-rw-r----- 1 oracle oinstall    1544 Oct 19 18:49 hc_MYTEdb.dat

-rw-r--r-- 1 oracle oinstall    2851 May 15  2009 init.ora

-rw-r--r-- 1 oracle oinstall    1092 Oct 19 18:51 initMYTEdb.ora

-rw-r----- 1 oracle oinstall      24 Aug  7  2015 lkMYTEDB

-

[oracle@MYTElife dbs]$ vi initMYTEdb.ora 

MYTEdb.__large_pool_size=184549376

*.db_create_file_dest='/u01/app/oracle/oradata'

*.db_domain=''

*.db_name='testdb'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=10737418240

如果使用ASM，还要修改ASM文件名。之后就可以使用新的testdb来启动。

[oracle@MYTElife dbs]$ export ORACLE_SID=testdb

[oracle@MYTElife dbs]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 19 18:54:26 2016

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

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup mount pfile=initMYTEdb.ora       

ORACLE instance started.

Total System Global Area 3540881408 bytes

Fixed Size                  2258320 bytes

Variable Size            855640688 bytes

Database Buffers        2667577344 bytes

Redo Buffers              15405056 bytes

Database mounted.

Open数据库时候，由于是一个全新的DBID，所以需要resetlogs模式。在日志上，我们的确也看到了Oracle删除原有online redo log动作的过程。

SQL> alter database open resetlogs;

Database altered.

--alert log信息

Wed Oct 19 18:56:59 2016

alter database open resetlogs

RESETLOGS after complete recovery through change 1719246

Resetting resetlogs activation ID 2764689218 (0xa4c9cf42)

Deleted Oracle managed file /u01/app/oracle/oradata/MYTEDB/onlinelog/o1_mf_1_bw77672y_.log

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/MYTEDB/onlinelog/o1_mf_1_bw7767d4_.log

Deleted Oracle managed file /u01/app/oracle/oradata/MYTEDB/onlinelog/o1_mf_2_bw776938_.log

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/MYTEDB/onlinelog/o1_mf_2_bw7769cc_.log

Deleted Oracle managed file /u01/app/oracle/oradata/MYTEDB/onlinelog/o1_mf_3_bw776cck_.log

Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/MYTEDB/onlinelog/o1_mf_3_bw776cmv_.log

Wed Oct 19 18:57:03 2016

Setting recovery target incarnation to 2

重新生成spfile。

SQL> create spfile from memory;

File created.

创建密码文件，对应新的testdb的数据库名称。

[oracle@MYTElife dbs]$ ls -l | grep orapw

-rw-r----- 1 oracle oinstall    1536 Aug  7  2015 orapwMYTEdb

[oracle@MYTElife dbs]$ orapwd file=orapwtestdb password=oracle entries=10 force=y

[oracle@MYTElife dbs]$ ls -l | grep orapw

-rw-r----- 1 oracle oinstall    1536 Aug  7  2015 orapwMYTEdb

-rw-r----- 1 oracle oinstall    2560 Oct 19 19:02 orapwtestdb

查看监听器状态，修改Oracle NET文件。

[oracle@MYTElife dbs]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-OCT-2016 19:09:51

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

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

Alias                    LISTENER

（篇幅原因，有省略……）

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))

Services Summary...

Service "testdb" has 1 instance(s).

  Instance "testdb", status READY, has 1 handler(s) for this service...

Service "testdbXDB" has 1 instance(s).

  Instance "testdb", status READY, has 1 handler(s) for this service...

The command completed successfully

对应新生成的dbid和dbname信息。

SQL> select dbid, name from v$database;

      DBID NAME

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

2708979596 TESTDB

最后，如果使用了global_name参数，也要进行修改。

注意：如果是Windows环境的话，DBNAME修改，在Services列表中需要重新生成服务。另外，修改DBID之后，原有的所有备份都失效了，需要重新备份。