当前位置： 首页 > Oracle, oracle 10g, oracle 11g > 正文

使用NID修改数据库DB NAME

Jun192015
作者：dbdream   发布：2015-06-19 18:02   分类：Oracle, oracle 10g, oracle 11g     2条评论  

之前整理过ORACLE数据库各种和名字有关的参数，以及如何修改，其中也介绍了如何通过修改控制文件的方法来修改数据库的DB NAME，详见http://www.dbdream.com.cn/2014/10/28/instance_name%E3%80%81db_name%E3%80%81db_unique_name%E3%80%81global_names%E3%80%81service_names%E7%9A%84%E5%90%AB%E4%B9%89%E5%8F%8A%E4%BF%AE%E6%94%B9/。本文使用另一种方法，使用NID工具修改数据库的DB NAME。

NID是9i开始提供的工具，主要用来修改数据库的DBID和DB NAME，下面看下NID命令的介绍。

[oracle@SL010A-ISITDB3 ~]$ nid -help

DBNEWID: Release 11.2.0.4.0 - Production on Fri Jun 19 16:24:26 2015

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

Keyword     Description                    (Default)
----------------------------------------------------
TARGET      Username/Password              (NONE)
DBNAME      New database name              (NONE)
LOGFILE     Output Log                     (NONE)
REVERT      Revert failed change           NO
SETNAME     Set a new database name only   NO
APPEND      Append to output log           NO
HELP        Displays these messages        NO

TARGET：链接数据库的用户名和密码。

DBNAME：指定新的DBNAME。

SETNAME：是否只修改DBNAME，默认参数值是NO，在修改DB NAME时DBID也会被修改。

下面是案例演示：

使用NIS工具修改DBID、DB NAME需要先将数据库启动到MOUNT状态。

[oracle@SL010A-ISITDB3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 15 10:30:17 2015

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Total System Global Area 4275781632 bytes
Fixed Size                  2260088 bytes
Variable Size             956302216 bytes
Database Buffers         3305111552 bytes
Redo Buffers               12107776 bytes
Database mounted.

下面使用NID工具修改DBNAME，在不设置SETNAME的情况下，DBID也会一起修改。

[oracle@SL010A-ISITDB3 ~]$ nid target=/as sysdba dbname=IVLUAT

DBNEWID: Release 11.2.0.4.0 - Production on Mon Jun 15 10:31:05 2015

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

Password: 
Connected to database XXXX (DBID=1353335250)

Connected to server version 11.2.0

Control Files in database:
    /data/u01/XXXX/control01.ctl
    /opt/oracle/fast_recovery_area/XXXX/control02.ctl

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

此处会需要确认是否需要修改DBID和是否要将DB NAME从XXXX修改为IVLUAT，输入Y确认后就会修改控制文件和数据文件头了。

Proceeding with operation
Changing database ID from 1353335250 to 2742253550
Changing database name from IVLDB to IVLUAT
    Control File /data/u01/IVLDB/control01.ctl - modified
    Control File /opt/oracle/fast_recovery_area/IVLDB/control02.ctl - modified
    Datafile /data/u01/IVLDB/system01.db - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/sysaux01.db - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/undotbs01.db - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/users01.db - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_MC01.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_IV01.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_IV02.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_IV03.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_IV04.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_IV05.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_IV06.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_IV07.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_IV08.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_IV09.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_IV10.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_WLIF01.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_WLIF02.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TB_TS_TS_TK01.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/IDX_TS_IN01.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/IDX_TS_IN02.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TS_MIG01.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TS_MIG02.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TS_MIG03.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TS_MIG04.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TS_MIG05.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TS_MIG06.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TS_MIG07.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TS_MIG08.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TS_MIG09.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/datafile/TS_MIG10.DB - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/system02.db - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/system03.db - dbid changed, wrote new name
    Datafile /data/u01/IVLDB/temp01.db - dbid changed, wrote new name
    Control File /data/u01/IVLDB/control01.ctl - dbid changed, wrote new name
    Control File /opt/oracle/fast_recovery_area/IVLDB/control02.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to IVLUAT.
Modify parameter file and generate a new password file before restarting.
Database ID for database IVLUAT changed to 2742253550.
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.

这部分上面的信息提示DBID和DB NAME已经修改，下半部分的信息比较关键。

Modify parameter file and generate a new password file before restarting.
这部分告诉我们，在打开数据库之前，要修改参数文件中的DB_NAME参数和重建或重命名密码文件，LINUX系统密码文件的命名规则是orapw+DBNAME，DB NAME修改后，如不重建密码文件或者重命名正确DBNAME的密码文件，在使用SYSDBA角色登录数据库时将找不到密码文件。
All previous backups and archived redo logs for this database are unusable.

这部分告诉我们，之前的备份和归档日志都已经失效，我们需要尽快对数据库进行备份。

Database has been shutdown, open database with RESETLOGS option.

这部分告诉我们，数据库必须以RESETLOGS的方式打开。

下面尝试启动数据库。

[oracle@SL010A-IVO03 ~]$ cd $ORACLE_HOME/dbs
[oracle@SL010A-IVO03 dbs]$ mv orapwIVLDB orapwIVLUAT

如果没有修改参数文件中的DB_NAME参数，那么在MOUNT的时候，会报ORA-01103错误。

[oracle@SL010A-ISITDB3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 15 10:31:51 2015

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size                  2260088 bytes
Variable Size             956302216 bytes
Database Buffers         3305111552 bytes
Redo Buffers               12107776 bytes
ORA-01103: database name 'IVLUAT' in control file is not 'IVLDB'

修改DB_NAME参数，尝试直接打开数据库。

SQL> alter system set db_name='IVLUAT' SCOPE=SPFILE;

System altered.

SQL> STARTUP FORCE
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size                  2260088 bytes
Variable Size             956302216 bytes
Database Buffers         3305111552 bytes
Redo Buffers               12107776 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

提示必须使用RESETLOGS的方式才能打开数据库。

SQL> alter database open resetlogs;

Database altered.

默认情况下，db_unique_name 和service_names都会伴随着DB NAME一起改变，此时由于service_names发生了变化，正常情况下应用是无法连接数据库的(以SID连接方式除外)。

SQL> show parameter name 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      IVLUAT
db_unique_name                       string      IVLUAT
global_names                         boolean     FALSE
instance_name                        string      IVLDB
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string      IVLUAT

为了不需改应用程序的连接配置，需要将service_names参数修改回原先的值。

SQL> alter system set service_names=IVLDB;

System altered.

重新注册监听后，应用程序即可访问数据库。

SQL> alter system register;

System altered.

一般情况下并不会有修改DBID和DB NAME的需求，但如果需要使用数据库备份在同一台服务器上恢复出一个数据库，这样DBID和DB NAME就会冲突，导致只有一个数据库可以正常工作，这时就需要修改DBID和DB NAME了。

本文固定链接: http://www.dbdream.com.cn/2015/06/%e4%bd%bf%e7%94%a8nid%e4%bf%ae%e6%94%b9%e6%95%b0%e6%8d%ae%e5%ba%93db-name/ | 信春哥，系统稳，闭眼上线不回滚！

该日志由 dbdream 于2015年06月19日发表在 Oracle, oracle 10g, oracle 11g 分类下， 你可以发表评论，并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 使用NID修改数据库DB NAME | 信春哥，系统稳，闭眼上线不回滚！
关键字: , , , , ,
【上一篇】
【下一篇】

您可能还会对这些文章感兴趣！

使用NID修改数据库DB NAME：目前有2 条留言

  1. 沙发
    xiaoyu:

    春哥，如果库是rman恢复的，没有临时表空间，用nid改一半不能改了，怎么办呢?

    2015-06-19 23:05 [回复]

发表评论

点击这里取消回复。

快捷键：Ctrl+Enter