11g利用指令nid改db_name、db_id

原本的db_name為orcl,測試將改為testdb

直接用nid來更改db_name、db_id,可免去許多步驟,參數help='Y'可以看到說明
$ nid help='Y'
DBNEWID: Release 11.2.0.2.0 - Production on Mon Jun 17 01:26:21 2013
Copyright (c) 1982, 2009, 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

關閉DB後,重啟到mount
SQL> shutdown immediate;
SQL> startup mount;

這次只更改db_name,所以setname=Y
$ nid target=sys/oracle dbname=test_db setname=Y
DBNEWID: Release 11.2.0.2.0 - Production on Mon Jun 17 00:39:05 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to database ORCL (DBID=1229390655)
Connected to server version 11.2.0
Control Files in database:
    /home/oracle/app/oracle/oradata/orcl/control01.ctl
    /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
Change database name of database ORCL to TEST_DB? (Y/[N]) => Y
Proceeding with operation
Changing database name from ORCL to TEST_DB
    Control File /home/oracle/app/oracle/oradata/orcl/control01.ctl - modified
    Control File /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl - modified
    Datafile /home/oracle/app/oracle/oradata/orcl/system01.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/sysaux01.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/undotbs01.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/users01.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/example01.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/APEX_1246426611663638.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/APEX_1265209995679366.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/rman.db - wrote new name
    Datafile /home/oracle/app/oracle/oradata/orcl/temp01.db - wrote new name
    Control File /home/oracle/app/oracle/oradata/orcl/control01.ctl - wrote new name
    Control File /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl - wrote new name
    Instance shut down
Database name changed to TEST_DB.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

已成功更改db_name為testdb,重建密碼檔
$ orapwd file=$ORACLE_HOME/dbs/orapwtest_db password=oracle

重開db發現錯誤
SQL> startup
ORA-01103: database name 'TEST_DB' in control file is not 'ORCL'

這是因為參數檔仍然記錄舊的db_name,在系統提示下複製一個pfile
$ cp $ORACLE_HOME/dbs/initorcl.ora inittest_db.ora

重新設定$ORACLE_SID,也可以修改.profile
$ export ORACLE_SID=test_db

手動修改inittest_db.ora的設定值
$ vi $ORACLE_HOME/dbs/initorcl.ora
將*.db_name='orcl'改為*.db_name='test_db'

如果有設定開機自動開啟Oracle,則要修改oratab的設定
$ vi /etc/oratab
將orcl改為test_db,完整如下:
test_db:/home/oracle/app/oracle/product/11.2.0/dbhome_2:Y

以pfile來啟動db之後重新建立spfile
SQL> shutdown abort
SQL> startup pfile=?/dbs/inittest_db.ora
SQL> create spfile from pfile;

再把db關閉後重開db,就沒有錯誤訊息了
SQL> shutdown abort
SQL> startup
ORACLE instance started.
Total System Global Area  456146944 bytes
Fixed Size              1344840 bytes
Variable Size            394267320 bytes
Database Buffers        54525952 bytes
Redo Buffers              6008832 bytes
Database mounted.
Database opened.

如果要同時更改db_name及db_id,可執行下列語法:
$  nid TARGET=SYS/password DBNAME=test_db setname='N'

如果只要變更db_id,則語法如下:
$ nid TARGET=SYS/password

注意,如果有變更到db_id,則必須用resetlogs來open資料庫
SQL> startup mount;
SQL> alter database open resetlogs;