Use Nid to Change dbname(轉)

zhouwf0726發表於2019-06-04

Nid是Oracle從9iR2開始提供的工具，可以用來更改資料庫名稱，而無需通過之前重建控制檔案等繁瑣方式.
需要說明的是，雖然這個工具來自9iR2，但是仍然可以被用於Oracle8i.

先看一下幫助:

C:>nid -help
DBNEWID: Release 10.1.0.2.0 - Production
Copyright (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 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SQL> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      eyglev
global_names                         boolean     FALSE
instance_name                        string      eyglev
lock_name_space                      string
log_file_name_convert                string
oracle_trace_collection_name         string
oracle_trace_facility_name           string      oracled
plsql_native_make_file_name          string
service_names                        string      eyglev

2．Shutdown資料庫

SQL> connect sys/orasys as sysdba
Connected.
SQL> shutdown immediate
Database 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> host
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版權所有 1985-2000 Microsoft Corp.

C:>nid target=sys/orasys dbname=eyglen
DBNEWID: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

Connected to database EYGLEV (DBID=677189177)

Control Files in database:
    E:ORACLEORADATAEYGLENCONTROL01.CTL
    E:ORACLEORADATAEYGLENCONTROL02.CTL
    E:ORACLEORADATAEYGLENCONTROL03.CTL

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

Proceeding with operation
Changing database ID from 677189177 to 3955758099
Changing database name from EYGLEV to EYGLEN
    Control File E:ORACLEORADATAEYGLENCONTROL01.CTL - modified
    Control File E:ORACLEORADATAEYGLENCONTROL02.CTL - modified
    Control File E:ORACLEORADATAEYGLENCONTROL03.CTL - modified
    Datafile E:ORACLEORADATAEYGLENSYSTEM01.DBF - dbid changed, wrote new name
    Datafile E:ORACLEORADATAEYGLENUNDOTBS01.DBF - dbid changed, wrote new name
    Datafile E:ORACLEORADATAEYGLENCWMLITE01.DBF - dbid changed, wrote new name
    Datafile E:ORACLEORADATAEYGLENDRSYS01.DBF - dbid changed, wrote new name
    Datafile E:ORACLEORADATAEYGLENINDX01.DBF - dbid changed, wrote new name
    Datafile E:ORACLEORADATAEYGLENODM01.DBF - dbid changed, wrote new name
    Datafile E:ORACLEORADATAEYGLENTOOLS01.DBF - dbid changed, wrote new name
    Datafile E:ORACLEORADATAEYGLENUSERS01.DBF - dbid changed, wrote new name
    Datafile E:ORACLEORADATAEYGLENXDB01.DBF - dbid changed, wrote new name
    Datafile E:ORACLEORADATAEYGLENEYGLE.DBF - dbid changed, wrote new name
    Datafile E:ORACLEORADATAEYGLENTEMP01.DBF - dbid changed, wrote new name
    Control File E:ORACLEORADATAEYGLENCONTROL01.CTL - dbid changed, wrote new name
    Control File E:ORACLEORADATAEYGLENCONTROL02.CTL - dbid changed, wrote new name
    Control File E:ORACLEORADATAEYGLENCONTROL03.CTL - dbid changed, wrote new name

Database 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:Oracleadmineyglenpfileinit.ora
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
ORA-01991: invalid password file 'e:oracleOra9iR2DATABASEPWDeyglen.ORA'


SQL> CREATE SPFILE='E:OracleOra9iR2databaseSPFILEEYGLEN.ORA' FROM
  2  PFILE='E:Oracleadmineyglenpfileinit.ora';

File created.

8．重建口令檔案

SQL> host
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版權所有 1985-2000 Microsoft Corp.
C:>orapwd file=E:OracleOra9iR2databasePWDeyglen.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 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.
SQL> alter database open resetlogs
  2  /

Database altered.

SQL>

11．修改後的引數

SQL> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      eyglen
global_names                         boolean     FALSE
instance_name                        string      eyglen
lock_name_space                      string
log_file_name_convert                string
oracle_trace_collection_name         string
oracle_trace_facility_name           string      oracled
plsql_native_make_file_name          string
service_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 name
NAME                                TYPE    VALUE
----------------------------------- ------- ------------------------------
db_file_name_convert                字串
db_name                             字串  vilen
global_names                        布林值  TRUE
instance_name                       字串  vilen
lock_name_space                     字串
log_file_name_convert               字串
oracle_trace_collection_name        字串
oracle_trace_facility_name          字串  oracled
service_names                       字串  vilen
SVRMGR>

2．修改

C:>nid target=sys/orasys@vilen dbname=vilene
DBNEWID: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

Connected to database VILEN (DBID=1535443189)

Control Files in database:
    C:ORACLEORADATAVILENCONTROL01.CTL
    C:ORACLEORADATAVILENCONTROL02.CTL
    C:ORACLEORADATAVILENCONTROL03.CTL

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

Proceeding with operation
Changing database ID from 1535443189 to 681857412
Changing database name from VILEN to VILENE
    Control File C:ORACLEORADATAVILENCONTROL01.CTL - modified
    Control File C:ORACLEORADATAVILENCONTROL02.CTL - modified
    Control File C:ORACLEORADATAVILENCONTROL03.CTL - modified
    Datafile C:ORACLEORADATAVILENSYSTEM01.DBF - dbid changed, wrote new name
    Datafile C:ORACLEORADATAVILENRBS01.DBF - dbid changed, wrote new name
    Datafile C:ORACLEORADATAVILENUSERS01.DBF - dbid changed, wrote new name
    Datafile C:ORACLEORADATAVILENTEMP01.DBF - dbid changed, wrote new name
    Datafile C:ORACLEORADATAVILENTOOLS01.DBF - dbid changed, wrote new name
    Datafile C:ORACLEORADATAVILENINDX01.DBF - dbid changed, wrote new name
    Datafile C:ORACLEORADATAVILENEQSP01.DBF - dbid changed, wrote new name
    Datafile C:ORACLEORADATAVILENPERFSTAT.DBF - dbid changed, wrote new name
    Control File C:ORACLEORADATAVILENCONTROL01.CTL - dbid changed, wrote new name
    Control File C:ORACLEORADATAVILENCONTROL02.CTL - dbid changed, wrote new name
    Control File C:ORACLEORADATAVILENCONTROL03.CTL - dbid changed, wrote new name

Database 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:oracledatabase>orapwd file=PWDvilen.ORA password=oracle entries=5
C:oracledatabase>

6． mount資料庫

SVRMGR> startup mount
ORACLE instance started.
Total System Global Area                         61970460 bytes
Fixed Size                                          75804 bytes
Variable Size                                    17645568 bytes
Database Buffers                                 44171264 bytes
Redo Buffers                                        77824 bytes
Database mounted.

7．開啟資料庫

SVRMGR> alter database open resetlogs
2> /
Statement processed.

8．修改後的引數

SVRMGR> show parameter name
NAME                                TYPE    VALUE
----------------------------------- ------- ------------------------------
db_file_name_convert                string
db_name                             string  vilene
global_names                        boolean TRUE
instance_name                       string  vilene
lock_name_space                     string
log_file_name_convert               string
oracle_trace_collection_name        string
oracle_trace_facility_name          string  oracled
service_names                       string  vilen

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242163/，如需轉載，請註明出處，否則將追究法律責任。

oracle nid修改dbname
2018-04-26
Oracle
Oracle rac使用nid和dbms_backup_restore包修改dbid和dbname
2018-08-21
OracleREST
【轉載】Kano Model — Ways to use it and NOT use it
2018-03-28
轉置原理小練習：Do Use FFT
2024-04-04
FFT
sqlserver Change Data Capture&Change Tracking
2020-12-23
SQLServerAPT
[20200407]nid修改DBID還原.txt
2020-04-08
nid修改資料庫名稱
2019-02-19
資料庫
用NID修改資料庫名稱
2019-07-21
資料庫
使用NID修改Oracle資料庫名
2021-05-06
Oracle資料庫
Madagascar Projection Change
2024-12-03
Project
JavaScript change 事件
2018-11-07
JavaScript事件
[20201112]nid改變資料庫名字.txt
2020-11-12
資料庫
nginx use
2024-07-30
Nginx
change ^M to new line
2024-05-06
change tabs in Adobe Acrobat
2024-05-27
BAT
chmod命令(change mode)
2018-06-18
322. Coin Change
2018-08-08
在RAC中用NID修改資料庫名稱
2019-06-16
資料庫
WireGuard Use Notes
2024-07-17
plsql use skills
2022-07-20
SQL
Be better to use NPM
2018-12-27
NPM
[20190312]檢視v$datafile欄位OFFLINE_CHANGE#, ONLINE_CHANGE#.txt
2019-03-12
mysql change buffer小結
2020-06-20
MySql
Innodb特性之change buffer
2020-10-26
HDU 1792 A New Change Problem
2019-02-08
How to change the background color for PyCharm
2018-08-14
PyCharm
gerrit "missing Change-Id"
2018-07-10
[Typescript] Use Bitwise Flags
2024-11-19
TypeScript
[Vue] Use render function
2024-11-23
VueFunction
efcore This MySqlConnection is already in use
2024-03-28
MySql
use "jsdelivr" to host file
2022-09-14
JSVR
Use IE userdata behavior
2021-09-09
IPFS_basic_use
2020-12-31
Use PHP7
2019-02-16
PHP
the NTP socket is in use, exiting
2019-03-18
CSS中will-change如何使用
2021-09-11
CSS
「 CodeForces」10E Greedy Change
2021-05-15
LeetCode - Medium - 322. Coin Change
2020-12-29
LeetCode

Use Nid to Change dbname(轉)

相關文章