Oracle工具——DBNEWID

DBNEWID工具用來修改一個資料庫的DBID或DBNAME。

 

 

使用修改控制檔案的方法可以修改DBNAME，但是無法修改DBID，而RMAN工具是透過DBID來唯一確認一個資料庫的。

只有透過DBNEWID工具才能修改資料庫的ID，DBNEWID也可以同時修改DBNAME，或者僅修改DBNAME。

將資料庫正常關閉，且置於MOUNT狀態：

[oracle@bjtest ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on 星期二 4月 7 17:08:19 2009

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

連線到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> select dbid, name from v$database;

      DBID NAME
---------- ---------
2657054673 BJTEST

SQL> select global_name from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
BJTEST

SQL> shutdown immediate
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> startup mount
ORACLE 例程已經啟動。

Total System Global Area 9432971568 bytes
Fixed Size                   756016 bytes
Variable Size             838860800 bytes
Database Buffers         8589934592 bytes
Redo Buffers                3420160 bytes
資料庫裝載完畢。

下面利用DBNEWID修改資料庫的ID和DBNAME：

SQL> host
[oracle@bjtest ~]$ nid target=/ dbname=test9i
DBNEWID: Release 9.2.0.4.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

Connected to database BJTEST (DBID=2657054673)

Control Files in database:
    /data/oradata/bjtest/control01.ctl
    /data/oradata/bjtest/control02.ctl
    /data/oradata/bjtest/control03.ctl

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

Proceeding with operation
Changing database ID from 2657054673 to 2420633656
Changing database name from BJTEST to TEST9I
    Control File /data/oradata/bjtest/control01.ctl - modified
    Control File /data/oradata/bjtest/control02.ctl - modified
    Control File /data/oradata/bjtest/control03.ctl - modified
    Datafile /data/oradata/bjtest/system01.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/undotbs01.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/cwmlite01.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/drsys01.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/example01.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/indx01.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/odm01.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/tools01.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/users01.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/xdb01.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/ndmain01.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/ndmain02.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/ndmain03.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/ndmain04.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/ndmain05.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/ndmain06.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/ndmain07.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/ndmain08.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/ndmain09.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/ndindex01.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/ndindex02.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/ndindex03.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/ndindex04.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/ndindex05.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/ndindex06.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_ts01.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_ts02.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_ts03.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_ts04.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_ts05.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_ts06.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_ts07.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_ts08.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_ts09.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_ts10.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_ts11.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_ts12.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_ts13.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_ts14.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_ts15.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_ts16.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/log01.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_index_ts01.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_index_ts02.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_index_ts03.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_index_ts04.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_index_ts05.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_index_ts06.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_index_ts07.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_index_ts08.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_index_ts09.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_index_ts10.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_index_ts11.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_index_ts12.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_index_ts13.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_index_ts14.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/lt_index_ts15.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/ndmain10.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/perfstat01.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/js01.dbf - dbid changed, wrote new name
    Datafile /data/oradata/bjtest/temp01.dbf - dbid changed, wrote new name
    Control File /data/oradata/bjtest/control01.ctl - dbid changed, wrote new name
    Control File /data/oradata/bjtest/control02.ctl - dbid changed, wrote new name
    Control File /data/oradata/bjtest/control03.ctl - dbid changed, wrote new name

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

在9i中需要重建密碼檔案，否則資料庫啟動會報錯ORA-1991：

[oracle@bjtest dbs]$ orapwd file=orapwbjtest password=test

然後修改SPFILE檔案中的內容，注意需要關閉資料庫後，再次開啟才能修改SPFILE的內容：

[oracle@bjtest ~]$ exit
exit

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

SQL> shutdown immediate
ORA-01109: 資料庫未開啟

已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> startup nomount
ORACLE 例程已經啟動。

Total System Global Area 9432971568 bytes
Fixed Size                   756016 bytes
Variable Size             838860800 bytes
Database Buffers         8589934592 bytes
Redo Buffers                3420160 bytes
SQL> alter system set db_name = 'test9i' scope = spfile;

系統已更改。

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

ORACLE 例程已經關閉。
SQL> startup mount
ORACLE 例程已經啟動。

Total System Global Area 9432971568 bytes
Fixed Size                   756016 bytes
Variable Size             838860800 bytes
Database Buffers         8589934592 bytes
Redo Buffers                3420160 bytes
資料庫裝載完畢。
SQL> alter database open resetlogs;

資料庫已更改。

檢查資料庫的DBID和DBNAME：

SQL> select dbid, name from v$database;

      DBID NAME
---------- ---------
2420633656 TEST9I

SQL> select global_name from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
BJTEST

SQL> alter database rename global_name to test9i;

資料庫已更改。

SQL> select global_name from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
TEST9I

修改GLOBAL_NAME後，修改DBID和DBNAME的操作完成。