Use Nid to Change dbname
2024-07-21 02:11:28
供稿：網友
link:
http://www.eygle.com/faq/use.nid.to.change.your.dbname.htm
nid是oracle從9ir2開始提供的工具，可以用來更改數據庫名稱，而無需通過之前重建控制文件等繁瑣方式.
需要說明的是，雖然這個工具來自9ir2，但是仍然可以被用于oracle8i.
先看一下幫助:
c:/>nid -helpdbnewid: release 10.1.0.2.0 - productioncopyright (c) 2001, 2004, oracle. all rights reserved.關鍵字 說明 (默認值)----------------------------------------------------target 用戶名/口令 (無)dbname 新的數據庫名 (無)logfile 輸出日志 (無)revert 還原失敗的更改 否setname 僅設置新的數據庫名 否append 附加至輸出日志 否help 顯示這些消息 否
我們通過范例來看一下用法:
1．數據庫當前設置
sql> select * from v$version;banner----------------------------------------------------------------oracle9i enterprise edition release 9.2.0.1.0 - productionpl/sql release 9.2.0.1.0 - productioncore 9.2.0.1.0 productiontns for 32-bit windows: version 9.2.0.1.0 - productionnlsrtl version 9.2.0.1.0 - productionsql> show parameter namename type value------------------------------------ ----------- ------------------------------db_file_name_convert stringdb_name string eyglevglobal_names boolean falseinstance_name string eyglevlock_name_space stringlog_file_name_convert stringoracle_trace_collection_name stringoracle_trace_facility_name string oracledplsql_native_make_file_name stringservice_names string eyglev
2．shutdown數據庫
sql> connect sys/orasys as sysdbaconnected.sql> shutdown immediatedatabase closed.database dismounted.oracle instance shut down.
3．startup mount
sql> startup mount
oracle instance started.
total system global area 135338868 bytes
fixed size 453492 bytes
variable size 109051904 bytes
database buffers 25165824 bytes
redo buffers 667648 bytes
database mounted.
4. 使用nid更改
sql> hostmicrosoft windows 2000 [version 5.00.2195](c) 版權所有 1985-2000 microsoft corp.c:/>nid target=sys/orasys dbname=eyglendbnewid: release 9.2.0.1.0 - productioncopyright (c) 1995, 2002, oracle corporation. all rights reserved.connected to database eyglev (dbid=677189177)control files in database: e:/oracle/oradata/eyglen/control01.ctl e:/oracle/oradata/eyglen/control02.ctl e:/oracle/oradata/eyglen/control03.ctlchange database id and database name eyglev to eyglen? (y/[n]) => yproceeding with operationchanging database id from 677189177 to 3955758099changing database name from eyglev to eyglen control file e:/oracle/oradata/eyglen/control01.ctl - modified control file e:/oracle/oradata/eyglen/control02.ctl - modified control file e:/oracle/oradata/eyglen/control03.ctl - modified datafile e:/oracle/oradata/eyglen ystem01.dbf - dbid changed, wrote new name datafile e:/oracle/oradata/eyglen/undotbs01.dbf - dbid changed, wrote new name datafile e:/oracle/oradata/eyglen/cwmlite01.dbf - dbid changed, wrote new name datafile e:/oracle/oradata/eyglen/drsys01.dbf - dbid changed, wrote new name datafile e:/oracle/oradata/eyglen/indx01.dbf - dbid changed, wrote new name datafile e:/oracle/oradata/eyglen/odm01.dbf - dbid changed, wrote new name datafile e:/oracle/oradata/eyglen/tools01.dbf - dbid changed, wrote new name datafile e:/oracle/oradata/eyglen/users01.dbf - dbid changed, wrote new name datafile e:/oracle/oradata/eyglen/xdb01.dbf - dbid changed, wrote new name datafile e:/oracle/oradata/eyglen/eygle.dbf - dbid changed, wrote new name datafile e:/oracle/oradata/eyglen/temp01.dbf - dbid changed, wrote new name control file e:/oracle/oradata/eyglen/control01.ctl - dbid changed, wrote new name control file e:/oracle/oradata/eyglen/control02.ctl - dbid changed, wrote new name control file e:/oracle/oradata/eyglen/control03.ctl - dbid changed, wrote new namedatabase name changed to eyglen.modify parameter file and generate a new password file before restarting.database id for database eyglen changed to 3955758099.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.
5．shutdown database
sql> shutdown immediate
ora-01109: database not open
database dismounted.
oracle instance shut down.
6．修改初始化參數文件、spfile文件（init.ora/spfile）
###########################################
instance_name=eyglen
#instance_name=eyglev
###########################################
db_domain=""
db_name=eyglen
# db_name=eyglev
###########################################
7．重建spfile文件
如果你沒有使用spfile，當然無需重建，跳至8
sql> startup pfile=e:/oracle/admin/eyglen/pfile/init.oraoracle instance started.total system global area 135338868 bytesfixed size 453492 bytesvariable size 109051904 bytesdatabase buffers 25165824 bytesredo buffers 667648 bytesora-01991: invalid password file 'e:/oracle/ora9ir2/database/pwdeyglen.ora'sql> create spfile='e:/oracle/ora9ir2/database pfileeyglen.ora' from 2 pfile='e:/oracle/admin/eyglen/pfile/init.ora';file created.
8．重建口令文件
sql> host
microsoft windows 2000 [version 5.00.2195]
(c) 版權所有 1985-2000 microsoft corp.
c:/>orapwd file=e:/oracle/ora9ir2/database/pwdeyglen.ora password=oracle entries=5
9.shutdown數據庫
如果不使用spfile,則可以跳至10
sql> shutdown immediate
ora-01109: database not open
database dismounted.
oracle instance shut down.
10．startup mount,resetlogs打開
sql> startup mountoracle instance started.total system global area 135338868 bytesfixed size 453492 bytesvariable size 109051904 bytesdatabase buffers 25165824 bytesredo buffers 667648 bytesdatabase mounted.sql> alter database open resetlogs 2 /database altered.sql>
11．修改后的參數
sql> show parameter namename type value------------------------------------ ----------- ------------------------------db_file_name_convert stringdb_name string eyglenglobal_names boolean falseinstance_name string eyglenlock_name_space stringlog_file_name_convert stringoracle_trace_collection_name stringoracle_trace_facility_name string oracledplsql_native_make_file_name stringservice_names string eyglen
12．對數據庫做個全備份
附：使用nid更改817的數據庫
1． 更改前
svrmgr> startup mount已啟動 oracle 實例。系統全局區域合計有 61970460個字節fixed size 75804個字節variable size 17645568個字節database buffers 44171264個字節redo buffers 77824個字節已裝入數據庫。svrmgr> show parameter namename type value----------------------------------- ------- ------------------------------db_file_name_convert 字符串db_name 字符串 vilenglobal_names 布爾值 trueinstance_name 字符串 vilenlock_name_space 字符串log_file_name_convert 字符串oracle_trace_collection_name 字符串oracle_trace_facility_name 字符串 oracledservice_names 字符串 vilensvrmgr>
2． 修改
c:/>nid target=sys/[email protected] dbname=vilenedbnewid: release 9.2.0.1.0 - productioncopyright (c) 1995, 2002, oracle corporation. all rights reserved.connected to database vilen (dbid=1535443189)control files in database: c:/oracle/oradata/vilen/control01.ctl c:/oracle/oradata/vilen/control02.ctl c:/oracle/oradata/vilen/control03.ctlchange database id and database name vilen to vilene? (y/[n]) => yproceeding with operationchanging database id from 1535443189 to 681857412changing database name from vilen to vilene control file c:/oracle/oradata/vilen/control01.ctl - modified control file c:/oracle/oradata/vilen/control02.ctl - modified control file c:/oracle/oradata/vilen/control03.ctl - modified datafile c:/oracle/oradata/vilen ystem01.dbf - dbid changed, wrote new name datafile c:/oracle/oradata/vilen/rbs01.dbf - dbid changed, wrote new name datafile c:/oracle/oradata/vilen/users01.dbf - dbid changed, wrote new name datafile c:/oracle/oradata/vilen/temp01.dbf - dbid changed, wrote new name datafile c:/oracle/oradata/vilen/tools01.dbf - dbid changed, wrote new name datafile c:/oracle/oradata/vilen/indx01.dbf - dbid changed, wrote new name datafile c:/oracle/oradata/vilen/eqsp01.dbf - dbid changed, wrote new name datafile c:/oracle/oradata/vilen/perfstat.dbf - dbid changed, wrote new name control file c:/oracle/oradata/vilen/control01.ctl - dbid changed, wrote new name control file c:/oracle/oradata/vilen/control02.ctl - dbid changed, wrote new name control file c:/oracle/oradata/vilen/control03.ctl - dbid changed, wrote new namedatabase name changed to vilene.modify parameter file and generate a new password file before restarting.database id for database vilene changed to 681857412.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.
3． 關閉數據庫
svrmgr> shutdown immediate
ora-01109: 數據庫未打開
已卸下數據庫。
已關閉 oracle 實例。
4． 修改參數文件
db_name = "vilene"
#db_name = "vilen"
instance_name = vilene
#instance_name = vilen
5． 重建口令文件
c:/oracle/database>orapwd file=pwdvilen.ora password=oracle entries=5
c:/oracle/database>
6． mount數據庫
svrmgr> startup mountoracle instance started.total system global area 61970460 bytesfixed size 75804 bytesvariable size 17645568 bytesdatabase buffers 44171264 bytesredo buffers 77824 bytesdatabase mounted.
7． 打開數據庫
svrmgr> alter database open resetlogs
2> /
statement processed.
8． 修改后的參數
svrmgr> show parameter namename type value----------------------------------- ------- ------------------------------db_file_name_convert stringdb_name string vileneglobal_names boolean trueinstance_name string vilenelock_name_space stringlog_file_name_convert stringoracle_trace_collection_name stringoracle_trace_facility_name string oracledservice_names string vilen
