Use Nid to Change dbname

來源:互聯網
上載者:User


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/orasys@vilen 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

 

 


相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。