Oracle rac使用nid和dbms_backup_restore包修改dbid和dbname
Oracle rac使用nid和dbms_backup_restore包修改dbid和dbname
之前的連線： http://blog.itpub.net/26736162/viewspace-2140640/
----nid 啟動到mount狀態
nid target=sys/lhr #只修改dbid
nid target=sys/lhr dbname=lhrdb setname=yes #只修改dbname，修改pfile檔案中的db_name引數
nid target=sys/lhr dbname=lhrxxt #同時修改dbname和dbid
select dbid,name,open_mode from gv$database;
DBID NAME OPEN_MODE
---------- --------- --------------------
2136828548 LHRRAC1 MOUNTED
RAC環境：
1、alter system set cluster_database=false scope=spfile sid='*';
2、啟動單例項到mount
否則：NID-00120: Database should be mounted exclusively
srvctl remove db -d lhrrax1
srvctl add database -d lhrxxt -o $ORACLE_HOME -c RAC
srvctl add instance -d lhrxxt -i lhrrac11 -n raclhr-11gR2-N1
srvctl add instance -d lhrxxt -i lhrrac12 -n raclhr-11gR2-N2
--啟動到READ ONLY模式，然後直接使用如下指令碼進行修改，對於RAC環境不用修改引數檔案內容
----more chg_dbname_dbid.sql
var old_name varchar2(20)
var old_dbid number
var new_name varchar2(20)
var new_dbid number
exec select name, dbid -
into :old_name,:old_dbid -
from v$database
print old_name
accept new_name prompt "Enter the new Database Name:"
accept new_dbid prompt "Enter the new Database ID:"
exec :new_name:='&&new_name'
exec :new_dbid:=&&new_dbid
set serveroutput on
exec dbms_output.put_line('Convert '||:old_name|| -
'('||to_char(:old_dbid)||') to '||:new_name|| -
'('||to_char(:new_dbid)||')')
declare
v_chgdbid binary_integer;
v_chgdbname binary_integer;
v_skipped binary_integer;
begin
dbms_backup_restore.nidbegin(:new_name,
:old_name,
:new_dbid,
:old_dbid,
0,
0,
10);
dbms_backup_restore.nidprocesscf(v_chgdbid, v_chgdbname);
dbms_output.put_line('ControlFile: ');
dbms_output.put_line(' => Change Name:' || to_char(v_chgdbname));
dbms_output.put_line(' => Change DBID:' || to_char(v_chgdbid));
for i in (select file#, name from v$datafile) loop
dbms_backup_restore.nidprocessdf(i.file#,
0,
v_skipped,
v_chgdbid,
v_chgdbname);
dbms_output.put_line('DataFile: ' || i.name);
dbms_output.put_line(' => Skipped:' || to_char(v_skipped));
dbms_output.put_line(' => Change Name:' || to_char(v_chgdbname));
dbms_output.put_line(' => Change DBID:' || to_char(v_chgdbid));
end loop;
for i in (select file#, name from v$tempfile) loop
dbms_backup_restore.nidprocessdf(i.file#,
1,
v_skipped,
v_chgdbid,
v_chgdbname);
dbms_output.put_line('DataFile: ' || i.name);
dbms_output.put_line(' => Skipped:' || to_char(v_skipped));
dbms_output.put_line(' => Change Name:' || to_char(v_chgdbname));
dbms_output.put_line(' => Change DBID:' || to_char(v_chgdbid));
end loop;
dbms_backup_restore.nidend;
end;
/
=======================修改資料庫dbid======================
1、檢視目前資料庫dbid以及dbname
[root@rac01 ~]# su - oracle
[oracle@rac01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 17:23:24 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl1
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string orcl
SQL> select dbid from v$database;
DBID
----------
2、將群集引數修改為false，否則修改不成功
SQL> alter system set cluster_database=false scope=spfile;
System altered.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
3、停止群集資料庫，並在一個節點啟動到mount
[oracle@rac01 ~]$ srvctl stop database -d orcl
[oracle@rac01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 17:34:34 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 452988064 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7319552 bytes
Database mounted.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
4、修改資料庫dbid
[oracle@rac01 ~]$ nid target=sys/oracle@orcl
DBNEWID: Release 11.2.0.4.0 - Production on Thu May 4 17:37:21 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database ORCL (DBID=1466421350)
Connected to server version 11.2.0
Control Files in database:
+DATA/orcl/controlfile/current.261.938640615
+DATA/orcl/controlfile/current.260.938640615
Change database ID of database ORCL? (Y/[N]) => y
Proceeding with operation
Changing database ID from 1466421350 to 1470887377
Control File +DATA/orcl/controlfile/current.261.938640615 - modified
Control File +DATA/orcl/controlfile/current.260.938640615 - modified
Datafile +DATA/orcl/datafile/system.256.93864038 - dbid changed
Datafile +DATA/orcl/datafile/sysaux.257.93864038 - dbid changed
Datafile +DATA/orcl/datafile/undotbs1.258.93864038 - dbid changed
Datafile +DATA/orcl/datafile/users.259.93864038 - dbid changed
Datafile +DATA/orcl/datafile/example.267.93864064 - dbid changed
Datafile +DATA/orcl/datafile/undotbs2.268.93864124 - dbid changed
Datafile +DATA/orcl/tempfile/temp.266.93864063 - dbid changed
Control File +DATA/orcl/controlfile/current.261.938640615 - dbid changed
Control File +DATA/orcl/controlfile/current.260.938640615 - dbid changed
Instance shut down
Database ID for database ORCL changed to 1470887377.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.
5、重建密碼檔案
[oracle@rac01 dbs]$ orapwd file='$ORACLE_HOME/dbs/orapworcl1' password=oracle entries=10
[oracle@rac01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 17:41:10 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 452988064 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7319552 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
6、以resetlogs模式開啟資料庫
SQL> alter database open resetlogs;
Database altered.
SQL> select dbid,name,open_mode,activation#,created from v$database;
DBID NAME OPEN_MODE ACTIVATION# CREATED
---------- --------- -------------------- ----------- ---------
ORCL READ WRITE 1470884038 14-MAR-17
7、將資料庫叢集引數修改為true，重啟資料庫
SQL> alter system set cluster_database=true scope=spfile;
System altered.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@rac01 dbs]$ srvctl stop database -d orcl
[oracle@rac01 dbs]$ srvctl start database -d orcl
[oracle@rac01 dbs]$ su - grid
[grid@rac01 ~]$ crsctl status res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE rac01
ONLINE ONLINE rac02
ora.LISTENER.lsnr
ONLINE ONLINE rac01
ONLINE ONLINE rac02
ora.VOTE.dg
ONLINE ONLINE rac01
ONLINE ONLINE rac02
ora.asm
ONLINE ONLINE rac01 Started
ONLINE ONLINE rac02 Started
ora.gsd
OFFLINE OFFLINE rac01
OFFLINE OFFLINE rac02
ora.net1.network
ONLINE ONLINE rac01
ONLINE ONLINE rac02
ora.ons
ONLINE ONLINE rac01
ONLINE ONLINE rac02
ora.registry.acfs
ONLINE ONLINE rac01
ONLINE ONLINE rac02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
ONLINE ONLINE rac01
ora.cvu
ONLINE ONLINE rac02
ora.oc4j
ONLINE ONLINE rac02
ora.orcl.db
ONLINE ONLINE rac01 Open
ONLINE ONLINE rac02 Open
ora.rac01.vip
ONLINE ONLINE rac01
ora.rac02.vip
ONLINE ONLINE rac02
ora.scan1.vip
ONLINE ONLINE rac01
==========================修改資料庫dbname=======================
1、檢視資料庫dbid及資料庫dbname
[oracle@rac01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 17:49:36 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
2、修改資料庫叢集引數為false，並建立pfile檔案
SQL> alter system set cluster_database=false scope=spfile;
System altered.
SQL> create pfile='/tmp/pfile.ora' from spfile;
File created.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
3、關閉資料庫並在一個節點啟動到mount
[oracle@rac01 ~]$ srvctl stop database -d orcl
[oracle@rac01 ~]$ cd /tmp/
[oracle@rac01 tmp]$ ls -l pfil*
-rw-r--r-- 1 oracle oinstall 1374 May 4 17:51 pfile.ora
[oracle@rac01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 17:59:50 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
SQL> quit
4、修改資料庫dbname為db11g
[oracle@rac01 dbs]$ nid TARGET=SYS/oracle@orcl DBNAME=db11g SETNAME=Y
DBNEWID: Release 11.2.0.4.0 - Production on Thu May 4 17:57:01 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database ORCL (DBID=1470887377)
Connected to server version 11.2.0
Control Files in database:
+DATA/orcl/controlfile/current.261.938640615
+DATA/orcl/controlfile/current.260.938640615
Change database name of database ORCL to DB11G? (Y/[N]) => y
Proceeding with operation
Changing database name from ORCL to DB11G
Control File +DATA/orcl/controlfile/current.261.938640615 - modified
Control File +DATA/orcl/controlfile/current.260.938640615 - modified
Datafile +DATA/orcl/datafile/system.256.93864038 - wrote new name
Datafile +DATA/orcl/datafile/sysaux.257.93864038 - wrote new name
Datafile +DATA/orcl/datafile/undotbs1.258.93864038 - wrote new name
Datafile +DATA/orcl/datafile/users.259.93864038 - wrote new name
Datafile +DATA/orcl/datafile/example.267.93864064 - wrote new name
Datafile +DATA/orcl/datafile/undotbs2.268.93864124 - wrote new name
Datafile +DATA/orcl/tempfile/temp.266.93864063 - wrote new name
Control File +DATA/orcl/controlfile/current.261.938640615 - wrote new name
Control File +DATA/orcl/controlfile/current.260.938640615 - wrote new name
Instance shut down
Database name changed to DB11G.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
5、嘗試startup無法啟動，因為在引數檔案中記錄的dbname仍然是orcl
[oracle@rac01 dbs]$ cat initorcl1.ora
SPFILE='+DATA/orcl/spfileorcl.ora'
[oracle@rac01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 17:59:50 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 452988064 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7319552 bytes
ORA-01103: database name 'DB11G' in control file is not 'ORCL'
6、修改pfile中的dbname引數，修改為新的dbname
[oracle@rac01 dbs]$ vi /tmp/pfile.ora
change *.db_name='orcl'
to *.db_name='db11g'
複製程式碼
複製程式碼
7、使用pfile啟動
[oracle@rac01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 18:03:20 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> startup pfile='/tmp/pfile.ora' mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 452988064 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7319552 bytes
Database mounted.
SQL> alter database open;
Database altered.
8、建立spfile，需指定路徑，因為spfile在ASM磁碟中，否則會在本地生成一個spfile
SQL> create SPFILE='+DATA/orcl/spfileorcl.ora' from pfile='/tmp/pfile.ora';
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
9、重啟資料庫
SQL> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 452988064 bytes
Database Buffers 1140850688 bytes
Redo Buffers 7319552 bytes
Database mounted.
Database opened.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/orcl/spfileorcl.ora
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string db11g
db_unique_name string db11g
global_names boolean FALSE
instance_name string orcl1
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string db11g
10、修改群集引數為true
SQL> alter system set cluster_database=true scope=spfile;
System altered.
SQL> select dbid,name,open_mode,activation#,created from v$database;
DBID NAME OPEN_MODE ACTIVATION# CREATED
---------- --------- -------------------- ----------- ---------
1470887377 DB11G READ WRITE 1470884038 14-MAR-17
11、重建密碼檔案
[oracle@rac01 dbs]$ orapwd file='$ORACLE_HOME/dbs/orapworcl1' password=oracle entries=10 force=y
[oracle@rac01 admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 04-MAY-2017 18:24:57
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 04-MAY-2017 17:17:18
Uptime 0 days 1 hr. 7 min. 38 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/rac01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.10)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "db11g" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully
12、修改tnsname.ora檔案的service_name為db11g
[oracle@rac01 dbs]$ cd ../network/admin/[oracle@rac01 admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db11g)
)
)
13、嘗試連線
[oracle@rac01 admin]$ sqlplus sys/oracle@orcl as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 4 18:25:53 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
連線成功。
14、將新的資料庫名稱加入群集進行管理，刪除舊的資料庫名稱
[oracle@rac01 ~]$ srvctl add database -d db11g -o $ORACLE_HOME
[oracle@rac01 ~]$ exit
logout
[root@rac01 ~]# su - grid
[grid@rac01 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE rac01
ora....ER.lsnr ora....er.type ONLINE ONLINE rac01
ora....N1.lsnr ora....er.type ONLINE ONLINE rac01
ora.VOTE.dg ora....up.type ONLINE ONLINE rac01
ora.asm ora.asm.type ONLINE ONLINE rac01
ora.cvu ora.cvu.type ONLINE ONLINE rac02
ora.db11g.db ora....se.type OFFLINE OFFLINE
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE rac01
ora.oc4j ora.oc4j.type ONLINE ONLINE rac02
ora.ons ora.ons.type ONLINE ONLINE rac01
ora.orcl.db ora....se.type ONLINE OFFLINE
ora....SM1.asm application ONLINE ONLINE rac01
ora....01.lsnr application ONLINE ONLINE rac01
ora.rac01.gsd application OFFLINE OFFLINE
ora.rac01.ons application ONLINE ONLINE rac01
ora.rac01.vip ora....t1.type ONLINE ONLINE rac01
ora....SM2.asm application ONLINE ONLINE rac02
ora....02.lsnr application ONLINE ONLINE rac02
ora.rac02.gsd application OFFLINE OFFLINE
ora.rac02.ons application ONLINE ONLINE rac02
ora.rac02.vip ora....t1.type ONLINE ONLINE rac02
ora....ry.acfs ora....fs.type ONLINE ONLINE rac01
ora.scan1.vip ora....ip.type ONLINE ONLINE rac01
[oracle@rac01 ~]$ srvctl add instance -d db11g -i orcl1 -n rac01
[oracle@rac01 ~]$ srvctl add instance -d db11g -i orcl2 -n rac02
[oracle@rac01 ~]$ srvctl remove database -d orcl
Remove the database orcl? (y/[n]) y
[oracle@rac01 ~]$ srvctl start database -d db11g
[grid@rac01 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE rac01
ora....ER.lsnr ora....er.type ONLINE ONLINE rac01
ora....N1.lsnr ora....er.type ONLINE ONLINE rac01
ora.VOTE.dg ora....up.type ONLINE ONLINE rac01
ora.asm ora.asm.type ONLINE ONLINE rac01
ora.cvu ora.cvu.type ONLINE ONLINE rac02
ora.db11g.db ora....se.type ONLINE ONLINE rac01
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE rac01
ora.oc4j ora.oc4j.type ONLINE ONLINE rac02
ora.ons ora.ons.type ONLINE ONLINE rac01
ora....SM1.asm application ONLINE ONLINE rac01
ora....01.lsnr application ONLINE ONLINE rac01
ora.rac01.gsd application OFFLINE OFFLINE
ora.rac01.ons application ONLINE ONLINE rac01
ora.rac01.vip ora....t1.type ONLINE ONLINE rac01
ora....SM2.asm application ONLINE ONLINE rac02
ora....02.lsnr application ONLINE ONLINE rac02
ora.rac02.gsd application OFFLINE OFFLINE
ora.rac02.ons application ONLINE ONLINE rac02
ora.rac02.vip ora....t1.type ONLINE ONLINE rac02
ora....ry.acfs ora....fs.type ONLINE ONLINE rac01
ora.scan1.vip ora....ip.type ONLINE ONLINE rac01
工作中不可避免地碰到需要修改 dbname 以及 dbid 的情形，如將資料庫恢復到同一臺機器的情形是其中之一。但 dbname 以及 dbid 是用於標識資料庫的重要標誌之一，尤其是 dbid ，具有唯一性，因此對其修改應慎重處理。
修改 dbname 以及 dbid 通常在使用 RMAN 還原到異機之後需要更改 dbname 以及 dbid 的情形 。對於這個修改我們可以藉助於命令列下的 nid 工具來完成。同時也可以直接呼叫 API 來實現。
本文描述了修改 dbname 以及 dbid 的步驟並給出示例。
nid 工具執行的命令實際也是呼叫 DBMS_BACKUP_RESTORE 包相應的儲存過程實現的。
1、 修改 dbid 及 dbname 的影響
a、 修改 dbid
等同於建立一個新的資料庫，不同的是資料已經存在於資料檔案。這是由 dbid 的唯一性決定的。
修改之後所有之前的備份與歸檔日誌將不可用，因為在恢復時會檢測 dbid ，由於不匹配，則所有備份無效。
修改之後需要使用 open resetlogs 開啟資料庫，一個新的 incarnation 會被建立，且 sequence 被置為 1 。
修改之後且成功 open 的情形下，建議一致性關閉資料庫，重啟並做一個完整的備份。
b、 修改 dbname
修改之後，無需使用 open resetlogs 開啟資料庫。 ( 注 dbname 不具有唯一性， global name 則具有唯一性 ) 。
修改之後，所有的備份即歸檔日誌依舊可用。
修改之後，需要修改 pfile/spfile 中對應的 db_name 引數以及重建相應的 Oracle 密碼檔案 。
如果需要使用舊的控制檔案恢復資料庫，應當使用修改之前的 pfile/spfile 以及密碼檔案啟動資料庫再進行恢復。
c、 同時修改 dbid 及 dbname
如果同時修改，則是上述兩種情形的綜合，修改完畢後需要 open resetlogs 以及修改 pfile/spifle ，密碼檔案，全備資料庫。
2、 nid 命令
:~> nid
DBNEWID: Release 10.2.0.3.0 - Production on Thu Apr 24 16:34:28 2013
Copyright (c) 1982, 2005, Oracle. 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
在執行 nid 命令時，該程式會校驗當前資料庫所有資料檔案以及控制檔案的頭部資訊，校驗成功後提示是否要修改。
如果使用了輸出到日誌檔案 logfile 則不會出現修改提示。
接下來 nid 將使用新的 dbid( 或者 dbname) 逐個修改控制檔案，資料檔案 ( 包括只讀檔案，正常離線檔案 ) 的頭部。
修改成功後自動關閉資料庫並退出。
注 : 對於只讀檔案，正常離線檔案本文未作測試。其次應確保資料庫處於歸檔狀態，可正常歸檔以及不存在需要 recover 的資料檔案。
3、 修改步驟
a 、全備資料庫，如果是熱備 (rman or os) 應確保所有的 歸檔日誌 以及 聯機日誌 可用
b 、刪除 dbconsole([ID 863800.1] 有此要求，如用到 dbconsole ，應考慮按此操作 )
c 、啟動需要修改的資料庫到 mount 狀態 (startup mount)
d 、如果使用 spfile 檔案啟動資料庫，備份 spfile 檔案到 pfile 用於後續修改 db_name
e 、釋出 nid 命令
nid target=sys/password # 此方式是僅僅修改 dbid
nid target=/ dbname=new_dbname [setname=yes]# / 表明連線到當前環境的 sid ，且使用作業系統認證
nid target=sys/password dbname=new_dbname [setname=yes] # setname=yes 僅僅修改資料庫名字，如果省略，則兩者同時修改
nid target=sys/pwdd@conn_string dbname=new_dbname [setname=yes] # 使用連線串連線到遠端主機並修改
f 、修改 Oracle 引數檔案 pfile( 使用先前從 spfile 備份的 ) 中的引數 db_name ，如果僅僅修改 dbid ，跳過此步驟
g 、使用新的引數檔案 pfile 啟動到 mount 狀態 ( 如果修改了 dbname ，如果僅僅是 dbid ，則直接使用原來的 pfile 或 spfile 啟動 )
h 、使用 open resetlogs 方式開啟資料庫 ( 修改非 dbid ，直接開啟即可 )
i 、重建當前資料庫的 Oralce 密碼檔案 及將 pfile 檔案轉換成 spfile 檔案
j 、修改相應的監聽器的配值，包括 listener.ora 以及 tnsnames.ora
k 、修改全域性 dbname ，如果有用到的話。 ALTER DATABASE RENAME GLOBAL_NAME TO <newname>.<domain>;
l 、重建 dbconsole $ emca -config dbcontrol db -repos recreate
m 、全備資料庫
1.1.1 修改 dbid
mount 狀態下：
[oracle@orcltest dbs]$ nid target=sys/lhr
DBNEWID: Release 11.2.0.3.0 - Production on Sat May 2 12:12:14 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database ORA11G (DBID=4274875142)
Connected to server version 11.2.0
Control Files in database:
/u02/app/oracle/oradata/orcltest/control01.ctl
/u02/app/oracle/flash_recovery_area/orcltest/control02.ctl
Change database ID of database ORA11G? (Y/[N]) => y
Proceeding with operation
Changing database ID from 4274875142 to 4274849246
Control File /u02/app/oracle/oradata/orcltest/control01.ctl - modified
Control File /u02/app/oracle/flash_recovery_area/orcltest/control02.ctl - modified
Datafile /u02/app/oracle/oradata/orcltest/system01.db - dbid changed
Datafile /u02/app/oracle/oradata/orcltest/sysaux01.db - dbid changed
Datafile /u02/app/oracle/oradata/orcltest/undotbs01.db - dbid changed
Datafile /u02/app/oracle/oradata/orcltest/users01.db - dbid changed
Datafile /u02/app/oracle/oradata/orcltest/example01.db - dbid changed
Datafile /u02/app/oracle/oradata/orcltest/temp01.db - dbid changed
Control File /u02/app/oracle/oradata/orcltest/control01.ctl - dbid changed
Control File /u02/app/oracle/flash_recovery_area/orcltest/control02.ctl - dbid changed
Instance shut down
Database ID for database ORA11G changed to 4274849246.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.
1.1.2 修改 dbname
$ export ORACLE_SID=CLBO
$ sqlplus / as sysdba
--> 當前資料庫的 dbid,name, 以及狀態 . 下面的演示中使用了 pfile 檔案來啟動資料庫 .
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 501059584 bytes
Fixed Size 2229744 bytes
Variable Size 339741200 bytes
Database Buffers 150994944 bytes
Redo Buffers 8093696 bytes
Database mounted.
SQL> select dbid,name,open_mode from v$database;
DBID NAME OPEN_MODE
---------- --------- ----------
1924111546 CLBO MOUNTED
$ nid target=sys/oracle dbname=MMBO setname=yes
DBNEWID: Release 10.2.0.3.0 - Production on Wed Apr 24 18:16:54 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to database CLBO (DBID=1924111546)
Connected to server version 10.2.0
Control Files in database:
/u02/database/SYBO/controlf/cntl1SYBO.ctl
/u02/database/SYBO/controlf/cntl2SYBO.ctl
/u02/database/SYBO/controlf/cntl3SYBO.ctl
Change database name of database CLBO to MMBO? (Y/[N]) => Y
Proceeding with operation
Changing database name from CLBO to MMBO
Control File /u02/database/SYBO/controlf/cntl1SYBO.ctl - modified
Control File /u02/database/SYBO/controlf/cntl2SYBO.ctl - modified
Control File /u02/database/SYBO/controlf/cntl3SYBO.ctl - modified
................. 省略 .................
Database name changed to MMBO.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
-- 修改 pfile 檔案中的 db_name 引數，並重新啟動資料庫 ( 略 )
-- 校驗修伽後的狀態， dbid 未發生變化，但 dbname 已經 被修改
$ export ORACLE_SID=MMBO
$ sqlplus / as sysdba
SQL> select dbid,name,open_mode from v$database;
DBID NAME OPEN_MODE
---------- --------- ----------
1924111546 MMBO READ WRITE
一.1.1.3 同時修改 dbname以及dbid
[oracle@testdb dbs]$ nid target=sys/lhr dbname=love
DBNEWID: Release 11.2.0.3.0 - Production on Sat Jan 24 14:51:39 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database lihu (DBID=888888)
NID-00121: Database should not be open
Change of database name failed during validation - database is intact.
DBNEWID - Completed with validation errors.
$ export ORACLE_SID=MMBO
SQL> startup mount pfile=/u02/database/SYBO/initMMBO.ora
$ nid target=/ dbname=SYBO
DBNEWID: Release 10.2.0.3.0 - Production on Wed Apr 24 19:12:57 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to database MMBO (DBID=1924111546)
Connected to server version 10.2.0
Control Files in database:
/u02/database/SYBO/controlf/cntl1SYBO.ctl
/u02/database/SYBO/controlf/cntl2SYBO.ctl
/u02/database/SYBO/controlf/cntl3SYBO.ctl
Change database ID and database name MMBO to SYBO? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 1924111546 to 4235963258
Changing database name from MMBO to SYBO
Control File /u02/database/SYBO/controlf/cntl1SYBO.ctl - modified
Control File /u02/database/SYBO/controlf/cntl2SYBO.ctl - modified
Control File /u02/database/SYBO/controlf/cntl3SYBO.ctl - modified
Datafile /u02/database/SYBO/oradata/sysSYBO.dbf - dbid changed, wrote new name
................
Datafile /u02/database/SYBO/temp/tmp.dbf - dbid changed, wrote new name
Control File /u02/database/SYBO/controlf/cntl1SYBO.ctl - dbid changed, wrote new name
Control File /u02/database/SYBO/controlf/cntl2SYBO.ctl - dbid changed, wrote new name
Control File /u02/database/SYBO/controlf/cntl3SYBO.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to SYBO.
Modify parameter file and generate a new password file before restarting.
Database ID for database SYBO changed to 4235963258.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
-- 修改 pfile 檔案的 db_name ，並重新命名了 pfile 檔案為 initSYBO.ora
SQL> startup pfile=/u02/database/SYBO/initSYBO.ora
ORACLE instance started.
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select dbid,name,open_mode from v$database;
DBID NAME OPEN_MODE
---------- --------- ----------
2315014338 SYBO READ WRITE
-- 接下來重建 spfile 檔案以及密碼檔案 , 配值監聽 ,tnsnames.ora, 修改 global name, 重建 dbconsole, 全備等 , 此處省略 .
-- 對於僅僅修改 dbid 的情形不再演示 . mount 狀態下發布命令 nid target=sys/pwd 即可 .
1.1
本文透過 dbms_backup_restore 方式來修改 dbname 及 dbid ，供大家參考。
建立 dbms_backup_restore 包：
SQL> @?/rdbms/admin/dbmsbkrs.sql
1、 修改 dbid 及 dbname 的步驟
a 、一致性關閉資料庫並啟動資料庫到 read only 狀態 ( 需要呼叫 dbms_backup_restore ，因此要 open 資料庫 )
b 、呼叫指令碼修改 dbname 或者 dbid( 根據提示輸入 )
c 、修改 spfile 或 pfile 中的 db_name 的值，如果僅改變 dbid ，此步驟可忽略
d 、以 open resetlogs 方式開啟資料庫
2 、實戰演習
robin@SZDB:~/dba_scripts/custom/sql> export ORACLE_SID=ES0481
robin@SZDB:~/dba_scripts/custom/sql> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Mar 29 20:18:28 2014
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
sys@ES0481> shutdown immediate;
sys@ES0481> startup open read only;
-- startup open pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initLIHUARONG.ora' read only;
sys@ES0481> select name,dbid,open_mode from v$database;
NAME DBID open_mode
--------- ---------- -----------
ES0481 123456 READ ONLY
sys@ES0481> @chg_dbname_dbid
PL/SQL procedure successfully completed.
OLD_NAME
------------------------------------------------------
ES0481
Enter the new Database Name:ES0480
Enter the new Database ID:654321
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Convert ES0481(123456) to ES0480(654321)
PL/SQL procedure successfully completed.
ControlFile:
=> Change Name:1
=> Change DBID:1
DataFile: /u02/database/ES0481/oradata/sysES0481.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /u02/database/ES0481/undo/undotbsES0481.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
.................
DataFile: /u02/database/ES0481/temp/ES0481_tempES0481.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
PL/SQL procedure successfully completed.
sys@ES0481> create pfile from spfile;
File created.
sys@ES0481> ho cat $$$$ORACLE_HOME/dbs/initES0481.ora |sed "s/db_name='ES0481'/db_name='ES0480'/">$ORACLE_HOME/dbs/initES0480.ora
sys@ES0481> shutdown immediate;
sys@ES0481> exit
Disconnected from Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
robin@SZDB:~/dba_scripts/custom/sql> export ORACLE_SID=ES0480
robin@SZDB:~/dba_scripts/custom/sql> sqlplus / as sysdba
idle> startup pfile=/users/oracle/OraHome10g/dbs/initES0480.ora mount;
ORACLE instance started.
Total System Global Area 599785472 bytes
Fixed Size 2074568 bytes
Variable Size 167774264 bytes
Database Buffers 423624704 bytes
Redo Buffers 6311936 bytes
Database mounted.
idle> alter database open resetlogs;
Database altered.
idle> create spfile from pfile='/users/oracle/OraHome10g/dbs/initES0480.ora';
File created.
idle> startup force;
idle> select name,dbid from v$database;
NAME DBID
--------- ----------
ES0480 654321
3 、指令碼 chg_dbname_dbid.sql
robin@SZDB:~/dba_scripts/custom/sql> more chg_dbname_dbid.sql
var old_name varchar2(20)
var old_dbid number
var new_name varchar2(20)
var new_dbid number
exec select name, dbid -
into :old_name,:old_dbid -
from v$database
print old_name
accept new_name prompt "Enter the new Database Name:"
accept new_dbid prompt "Enter the new Database ID:"
exec :new_name:='&&new_name'
exec :new_dbid:=&&new_dbid
set serveroutput on
exec dbms_output.put_line('Convert '||:old_name|| -
'('||to_char(:old_dbid)||') to '||:new_name|| -
'('||to_char(:new_dbid)||')')
declare
v_chgdbid binary_integer;
v_chgdbname binary_integer;
v_skipped binary_integer;
begin
dbms_backup_restore.nidbegin(:new_name,
:old_name,
:new_dbid,
:old_dbid,
0,
0,
10);
dbms_backup_restore.nidprocesscf(v_chgdbid, v_chgdbname);
dbms_output.put_line('ControlFile: ');
dbms_output.put_line(' => Change Name:' || to_char(v_chgdbname));
dbms_output.put_line(' => Change DBID:' || to_char(v_chgdbid));
for i in (select file#, name from v$datafile) loop
dbms_backup_restore.nidprocessdf(i.file#,
0,
v_skipped,
v_chgdbid,
v_chgdbname);
dbms_output.put_line('DataFile: ' || i.name);
dbms_output.put_line(' => Skipped:' || to_char(v_skipped));
dbms_output.put_line(' => Change Name:' || to_char(v_chgdbname));
dbms_output.put_line(' => Change DBID:' || to_char(v_chgdbid));
end loop;
for i in (select file#, name from v$tempfile) loop
dbms_backup_restore.nidprocessdf(i.file#,
1,
v_skipped,
v_chgdbid,
v_chgdbname);
dbms_output.put_line('DataFile: ' || i.name);
dbms_output.put_line(' => Skipped:' || to_char(v_skipped));
dbms_output.put_line(' => Change Name:' || to_char(v_chgdbname));
dbms_output.put_line(' => Change DBID:' || to_char(v_chgdbid));
end loop;
dbms_backup_restore.nidend;
end;
/
About Me
|
........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub（ http://blog.itpub.net/26736162 ）、部落格園（ http://www.cnblogs.com/lhrbest ）和個人weixin公眾號（ xiaomaimiaolhr ）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文部落格園地址： http://www.cnblogs.com/lhrbest ● 本文pdf版、個人簡介及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： ........................................................................................................................ ● QQ群號： 230161599 （滿） 、618766405 ● weixin群：可加我weixin，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友 （ 646634621 ） ，註明新增緣由 ● 於 2018-08-01 06:00 ~ 2018-08-31 24:00 在魔都完成 ● 最新修改時間：2018-08-01 06:00 ~ 2018-08-31 24:00 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店 ： ● 小麥苗出版的資料庫類叢書 ： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班 ： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麥苗騰訊課堂主頁 ： https://lhr.ke.qq.com/ ........................................................................................................................ 使用weixin 客戶端 掃描下面的二維碼來關注小麥苗的weixin公眾號（ xiaomaimiaolhr ）及QQ群（DBA寶典）、新增小麥苗weixin， 學習最實用的資料庫技術。
........................................................................................................................ |
|
|
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2212464/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 修改dbid和dbnameOracle
- oracle nid修改dbnameOracle
- Oracle NID工具修改資料庫DBID和資料庫名稱Oracle資料庫
- 通過dbms_backup_restore修改oracle的dbidRESTOracle
- oracle 之修改DBIDOracle
- 使用NID修改Oracle資料庫名Oracle資料庫
- dbnewid修改dbid和庫名
- Oracle RAC修改IP和VIP地址Oracle
- oracle 11g 更改sid和dbnameOracle
- nid 改變資料庫名，DBID資料庫
- 在RAC中用NID修改資料庫名稱資料庫
- 手工修改Oracle資料庫DBNAME-轉載Oracle資料庫
- 利用nid工具修改db_name和instance_name
- [轉] 使用NID 修改資料庫名(Oracle9或以上)資料庫Oracle
- 使用ORACLE RAC GUARD命令——RAC GUARD概念和管理Oracle
- oracle dbms包和其他包的使用大全Oracle
- oracle dbms包和其他包的使用大全 (十二)Oracle
- oracle dbms包和其他包的使用大全 (十一)Oracle
- oracle dbms包和其他包的使用大全 (十)Oracle
- oracle dbms包和其他包的使用大全 (九)Oracle
- oracle dbms包和其他包的使用大全 (八)Oracle
- oracle dbms包和其他包的使用大全 (七)Oracle
- oracle dbms包和其他包的使用大全 (六)Oracle
- oracle dbms包和其他包的使用大全 (五)Oracle
- oracle dbms包和其他包的使用大全 (四)Oracle
- oracle dbms包和其他包的使用大全 (三)Oracle
- oracle dbms包和其他包的使用大全（二）Oracle
- oracle dbms包和其他包的使用大全(一)Oracle
- 利用DBNEWID工具來修改DBNAME
- oracle工具之nid命令的使用Oracle
- RAC和Dataguard環境下修改sys使用者密碼密碼
- Oracle RAC建立表空間和使用者Oracle
- oracle RAC 建立使用者和表空間Oracle
- 【原創】使用nid命令修改資料庫名稱資料庫
- 【Services】Oracle 11g RAC使用Manual和Policy Managed方法配置和使用ServicesOracle
- Oracle RAC 修改 IP 地址Oracle
- 【RAC】Oracle RAC如何修改心跳網路Oracle
- nid修改資料庫名稱資料庫