利用DBNEWID工具来修改DBNAME

qbqqq

原文地址：http://zjiu82.itpub.net/post/23993/226546
          http://space.itpub.net/7199859/viewspace-591684

• 什么是DBNEWID工具 在介绍DBNEWID工具之前，你可以人工创建一个数据库的副本，并且通过重新创建控制文件给予数据库一个新的数据库名称（DBNAME）。然而，你不能 给予这个数据库一个新的标识（DBID）。DBID是一个内容的，唯一的识别一个数据库的。因为RMAN是通过DBID区别数据库的，所以你不能在相同的 RMAN（repository）支持库里登记主数据库和副本数据库。DBNEWID工具解决了这个问题，允许你做以下操作：
  1）只改变数据库的DBID
  2）只改变数据库的DBNAME
  3）改变数据库的DBNAME和DBID


• 改变DBID和DBNAME的区别
  改变数据库的DBID是一个严肃（危险）的过程，当改变数据库的DBID后，所有以前的备份和归档重做日志文件都变的不可用了。你改变DBID之后，你必须 使用RESETLOGS选项打开数据库，它（RESETLOGS选项）将会重新创建联机重做日志文件和重置日志序列号为1（参见Oracle9i Database Administrator's Guide)。因此，你改变了DBID之后应该立刻备份完整的数据库。只改变DBNAME而不改变DBID的时候，打开数据库不需要带RESETLOGS 选项，因此数据库的备份和归档重做日志文件也不会无效。在改变了DBNAME之后，你必须改变DB_NAME这个初始参数反映这个新的名称。你也可以重新 创建ORACLE密码文件。如果你还原旧的备份控制文件（DBNAME改变之前），那么你应该使用DBNAME改变之前的初始参数文件和密码文件。

   利用DBNEWID工具来修改dbid和dbname,也可以只修改dbname
   我们先看下dbnewid的帮助
   C:\Documents and Settings\paulyi>nid help=y
   DBNEWID: Release 9.2.0.8.0 - Production
   Copyright (c) 1995, 2002, Oracle Corporation.  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

 

实验一:修改dbid和dbname
以下是操作步骤 首先数据库要处于mount状态,下面将数据库名TEST9I改为TEST,并且dbid也同时修改
C:\Documents and Settings\paulyi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on 星期五 4月 24 09:40:02 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> select dbid,name from v$database;

      DBID NAME
---------- ---------
2422073917 TEST9I

SQL> select global_name from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------

TEST9I.HNISI.COM.CN

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area  135339604 bytes
Fixed Size                   454228 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
SQL> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

 

C:\Documents and Settings\paulyi>nid target=/ dbname=test
DBNEWID: Release 9.2.0.8.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

Connected to database TEST9I (DBID=2422073917)

Control Files in database:
    D:\ORACLE\ORADATA\TEST\CONTROL01.CTL
    D:\ORACLE\ORADATA\TEST\CONTROL02.CTL
    D:\ORACLE\ORADATA\TEST\CONTROL03.CTL

Change database ID and database name TEST9I to TEST? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 2422073917 to 1983160527
Changing database name from TEST9I to TEST
    Control File D:\ORACLE\ORADATA\TEST\CONTROL01.CTL - modified
    Control File D:\ORACLE\ORADATA\TEST\CONTROL02.CTL - modified
    Control File D:\ORACLE\ORADATA\TEST\CONTROL03.CTL - modified
    Datafile D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF - dbid changed, wrote new name
    Datafile D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF - dbid changed, wrote new name

    Datafile D:\ORACLE\ORADATA\TEST\EXAMPLE01.DBF - dbid changed, wrote new name

    Datafile D:\ORACLE\ORADATA\TEST\INDX01.DBF - dbid changed, wrote new name
    Datafile D:\ORACLE\ORADATA\TEST\TOOLS01.DBF - dbid changed, wrote new name
    Datafile D:\ORACLE\ORADATA\TEST\USERS01.DBF - dbid changed, wrote new name
    Datafile D:\ORACLE\ORADATA\TEST\TEMP01.DBF - dbid changed, wrote new name
    Control File D:\ORACLE\ORADATA\TEST\CONTROL01.CTL - dbid changed, wrote new
name
    Control File D:\ORACLE\ORADATA\TEST\CONTROL02.CTL - dbid changed, wrote new
name
    Control File D:\ORACLE\ORADATA\TEST\CONTROL03.CTL - dbid changed, wrote new
name

Database name changed to TEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database TEST changed to 1983160527.
All previous backups and archived redo logs for this database are unusable.
Shut down database and open with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

修改db_name参数需要把数据库关闭再启动到nomount才可以修改

C:\Documents and Settings\paulyi>exit

SQL> alter system set db_name='test' scope=spfile;
alter system set db_name='test' scope=spfile
*
ERROR 位于第 1 行:
ORA-32017: failure in updating SPFILE
ORA-32016: parameter "db_name" cannot be updated in SPFILE

SQL> exit
从Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production中断开

要重新建立密码文件，要不然会报ORA-01991错误  --注意这个密码文件是按SID来命名的
C:\Documents and Settings\paulyi>orapwd file=d:\oracle\ora92\database\PWDtest9i.OR
A password=abcdefg entries=10

C:\Documents and Settings\paulyi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on 星期五 4月 24 09:43:32 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> shutdown immediate
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area  135339604 bytes
Fixed Size                   454228 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL> alter system set db_name='test' scope=spfile;

系统已更改。

SQL> shutdown immediate;
ORA-01507: ??????

ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area  135339604 bytes
Fixed Size                   454228 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
用open resetlogs 选项打开数据库
SQL> alter database open resetlogs;

数据库已更改。

下面看到dbid和dbname已经更改成功

SQL> select dbid,name from v$database;

      DBID NAME
---------- ---------
1983160527 TEST

SQL> alter database rename global_name to TEST.HNISI.COM.CN
  2  ;

数据库已更改。

SQL>

SQL> select global_name from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------

TEST.HNISI.COM.CN

实验二:只修改dbname 将TEST9I改为TEST,不需要用open resetlogs方式打开，因为没有修改dbid
以下是操作步骤 首先数据库要处于mount状态,下面将数据库名TEST9I改为TEST,并且dbid不修改
C:\Documents and Settings\paulyi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on 星期五 4月 24 10:10:42 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> select dbid,name from v$database;

      DBID NAME
---------- ---------
1983160527 TEST9I

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area  135339604 bytes
Fixed Size                   454228 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
SQL> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

nid参数加上setname=y 选项

C:\Documents and Settings\paulyi>nid target=/ dbname=test setname=y
DBNEWID: Release 9.2.0.8.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

Connected to database TEST9I (DBID=1983160527)

Control Files in database:
    D:\ORACLE\ORADATA\TEST\CONTROL01.CTL
    D:\ORACLE\ORADATA\TEST\CONTROL02.CTL
    D:\ORACLE\ORADATA\TEST\CONTROL03.CTL

Change database name of database TEST9I to TEST? (Y/[N]) => Y

Proceeding with operation
Changing database name from TEST9I to TEST
    Control File D:\ORACLE\ORADATA\TEST\CONTROL01.CTL - modified
    Control File D:\ORACLE\ORADATA\TEST\CONTROL02.CTL - modified
    Control File D:\ORACLE\ORADATA\TEST\CONTROL03.CTL - modified
    Datafile D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF - wrote new name
    Datafile D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF - wrote new name
    Datafile D:\ORACLE\ORADATA\TEST\EXAMPLE01.DBF - wrote new name
    Datafile D:\ORACLE\ORADATA\TEST\INDX01.DBF - wrote new name
    Datafile D:\ORACLE\ORADATA\TEST\TOOLS01.DBF - wrote new name
    Datafile D:\ORACLE\ORADATA\TEST\USERS01.DBF - wrote new name
    Datafile D:\ORACLE\ORADATA\TEST\TEMP01.DBF - wrote new name
    Control File D:\ORACLE\ORADATA\TEST\CONTROL01.CTL - wrote new name
    Control File D:\ORACLE\ORADATA\TEST\CONTROL02.CTL - wrote new name
    Control File D:\ORACLE\ORADATA\TEST\CONTROL03.CTL - wrote new name

Database name changed to TEST.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

C:\Documents and Settings\paulyi>orapwd file=D:\oracle\ora92\DATABASE\PWDtest.OR
A password=abcdefg entries=10

C:\Documents and Settings\paulyi>exit

SQL> shutdown immediate;
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area  135339604 bytes
Fixed Size                   454228 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL> alter system set db_name='test' scope=spfile;

系统已更改。

SQL> shutdown immediate
ORA-01507: ??????

ORACLE 例程已经关闭。
SQL> startup;
ORACLE 例程已经启动。

Total System Global Area  135339604 bytes
Fixed Size                   454228 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
数据库已经打开。

修改成功，只修改dbname,没修改dbid

SQL> select dbid,name from v$database;

      DBID NAME
---------- ---------
1983160527 TEST

SQL> show parameter db_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      test

SQL> alter database rename global_name to TEST.HNISI.COM.CN;

数据库已更改。

SQL> select global_name from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------

TEST.HNISI.COM.CN

SQL>