利用DBNEWID工具來修改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>