11g新特性之activedatabaesduplicate
一，環境信息
主庫：rhel6.2 oracle 11.2.0.4
duplicate庫：orace linux 6.6 oracle 11.2.0.3
注意：數據庫軟件版本一定要相同，最起碼compatible參數要一致
二，在duplicate端創建參數文件和密碼文件
1.確認主庫數據文件和日志文件的路徑
<span style="font-size:12px;">SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle11g/oradata/DB11/datafile/o1_mf_system_bcc2dp1d_.dbf
/u01/app/oracle11g/oradata/DB11/datafile/o1_mf_sysaux_bcc2dp2c_.dbf
/u01/app/oracle11g/oradata/DB11/datafile/o1_mf_users_bcc2dp33_.dbf
/u01/app/oracle11g/oradata/DB11/datafile/o1_mf_new_user_bcp23qgn_.dbf
/u01/app/oracle11g/oradata/DB11/datafile/undotbs2.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle11g/oradata/DB11/onlinelog/o1_mf_3_bcc2hc3t_.log
/u01/app/oracle11g/oradata/DB11/onlinelog/o1_mf_2_bcc2hb1s_.log
/u01/app/oracle11g/oradata/DB11/onlinelog/o1_mf_1_bcc2h8rh_.log
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4.0</span>
2.在duplicate端創建目錄
<span style="font-size:12px;"> mkdir -p /u01/app/oradup/oradata/dupdb
mkdir -p /u01/app/oradup/admin/dupdb/adump
並授權
chown oradup:oinstall /u01/app/oradup/oradata/dupdb</span>
3.參數文件內容如下：
<span style="font-size:12px;">db111@testdb11a /u01/app/oradup/product/11.2.0/dbs$ vi initdupdb.ora
*.compatible='11.2.0.4.0' ---這個參數兩個數據庫一定要一致
*.control_files='/u01/app/oradup/oradata/dupdb/control01.ctl'
*.db_block_size=8192
*.db_name='dupdb'
#####下面的兩個參數分別設定數據文件和日志文件切換後的路徑
db_file_name_convert=('/u01/app/oracle11g/oradata/DB11/datafile/','/u01/app/oradup/oradata/dupdb')
log_file_name_convert=('/u01/app/oracle11g/oradata/DB11/datafile/','/u01/app/oradup/oradata/dupdb')</span>
4.創建密碼文件，密碼一定要和主庫的sys密碼一致
<span style="font-size:12px;">db111@testdb11a /u01/app/oradup/product/11.2.0/dbs$ orapwd file=orapwdupdb password=oracle entries=30
</span>
三，啟動duplicate數據庫到nomount狀態
<span style="font-size:12px;">db111@testdb11a /home/oracle$ export ORACLE_SID=dupdb
dupdb@testdb11a /home/oracle$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri May 22 05:16:22 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes</span>
四，在duplicate端配置網絡配置文件和tnsnames.ora
listener.ora文件如下：（說明：duplicate端必須使用靜態注冊,因為監聽如果是動態注冊在nomount狀態為blocked狀態，這種狀態是不能連接數據庫的）
<span style="font-size:12px;">vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = dupdb)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0)
(SID_NAME = dupdb)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.10)(PORT = 1521))
)</span>
tnsnames.ora如下（注意：主庫和duplicate庫上都要修改）：
<span style="font-size:12px;">db11 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.100)(PORT = 1526))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db11)
)
)
dupdb=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dupdb)
)
)</span>
五，啟動rman，連接到數據庫
<span style="font-size:12px;">db111@testdb11a /home/oracle$ export ORACLE_SID=dupdb
dupdb@testdb11a /home/oracle$ rman target sys/oracle@db11 auxiliary sys/oracle@dupdb
Recovery Manager: Release 11.2.0.3.0 - Production on Fri May 22 05:41:34 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB11 (DBID=1400765095)
connected to auxiliary database: DUPDB (not mounted)</span>
六，執行duplicate命令
<span style="font-size:12px;">RMAN> duplicate target database to 'dupdb' from active database nofilenamecheck;
</span>
附錄一，
RMAN> duplicate target database to 'dupdb' from active database nofilenamecheck;
Starting Duplicate Db at 22-MAY-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2251816 bytes
Variable Size 159384536 bytes
Database Buffers 50331648 bytes
Redo Buffers 5189632 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DB11'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DUPDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '/u01/app/oradup/oradata/dupdb/control01.ctl';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''DB11'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''DUPDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2251816 bytes
Variable Size 159384536 bytes
Database Buffers 50331648 bytes
Redo Buffers 5189632 bytes
Starting backup at 22-MAY-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle11g/product/11.2.0/dbhome_1/dbs/snapcf_db11.f tag=TAG20150224T154659 RECID=13 STAMP=872524019
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 22-MAY-15
database mounted
contents of Memory Script:
{
set newname for datafile 1 to
"/u01/app/oradup/oradata/dupdbo1_mf_system_bcc2dp1d_.dbf";
set newname for datafile 2 to
"/u01/app/oradup/oradata/dupdbo1_mf_sysaux_bcc2dp2c_.dbf";
set newname for datafile 4 to
"/u01/app/oradup/oradata/dupdbo1_mf_users_bcc2dp33_.dbf";
set newname for datafile 5 to
"/u01/app/oradup/oradata/dupdbo1_mf_new_user_bcp23qgn_.dbf";
set newname for datafile 6 to
"/u01/app/oradup/oradata/dupdbundotbs2.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oradup/oradata/dupdbo1_mf_system_bcc2dp1d_.dbf" datafile
2 auxiliary format
"/u01/app/oradup/oradata/dupdbo1_mf_sysaux_bcc2dp2c_.dbf" datafile
4 auxiliary format
"/u01/app/oradup/oradata/dupdbo1_mf_users_bcc2dp33_.dbf" datafile
5 auxiliary format
"/u01/app/oradup/oradata/dupdbo1_mf_new_user_bcp23qgn_.dbf" datafile
6 auxiliary format
"/u01/app/oradup/oradata/dupdbundotbs2.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 22-MAY-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle11g/oradata/DB11/datafile/o1_mf_system_bcc2dp1d_.dbf
output file name=/u01/app/oradup/oradata/dupdbo1_mf_system_bcc2dp1d_.dbf tag=TAG20150224T154706
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:40
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle11g/oradata/DB11/datafile/o1_mf_sysaux_bcc2dp2c_.dbf
output file name=/u01/app/oradup/oradata/dupdbo1_mf_sysaux_bcc2dp2c_.dbf tag=TAG20150224T154706
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle11g/oradata/DB11/datafile/o1_mf_users_bcc2dp33_.dbf
output file name=/u01/app/oradup/oradata/dupdbo1_mf_users_bcc2dp33_.dbf tag=TAG20150224T154706
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle11g/oradata/DB11/datafile/o1_mf_new_user_bcp23qgn_.dbf
output file name=/u01/app/oradup/oradata/dupdbo1_mf_new_user_bcp23qgn_.dbf tag=TAG20150224T154706
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle11g/oradata/DB11/datafile/undotbs2.dbf
output file name=/u01/app/oradup/oradata/dupdbundotbs2.dbf tag=TAG20150224T154706
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
Finished backup at 22-MAY-15
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "/u01/app/oracle11g/product/11.2.0/dbhome_1/dbs/arch1_84_868943464.dbf" auxiliary format
"/u01/app/oradup/product/11.2.0/dbs/arch1_84_868943464.dbf" ;
catalog clone archivelog "/u01/app/oradup/product/11.2.0/dbs/arch1_84_868943464.dbf";
switch clone datafile all;
}
executing Memory Script
Starting backup at 22-MAY-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=84 RECID=27 STAMP=872524304
output file name=/u01/app/oradup/product/11.2.0/dbs/arch1_84_868943464.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
Finished backup at 22-MAY-15
cataloged archived log
archived log file name=/u01/app/oradup/product/11.2.0/dbs/arch1_84_868943464.dbf RECID=27 STAMP=880393331
datafile 1 switched to datafile copy
input datafile copy RECID=13 STAMP=880393332 file name=/u01/app/oradup/oradata/dupdbo1_mf_system_bcc2dp1d_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=14 STAMP=880393332 file name=/u01/app/oradup/oradata/dupdbo1_mf_sysaux_bcc2dp2c_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=15 STAMP=880393332 file name=/u01/app/oradup/oradata/dupdbo1_mf_users_bcc2dp33_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=16 STAMP=880393332 file name=/u01/app/oradup/oradata/dupdbo1_mf_new_user_bcp23qgn_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=17 STAMP=880393332 file name=/u01/app/oradup/oradata/dupdbundotbs2.dbf
contents of Memory Script:
{
set until scn 1797603;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 22-MAY-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK
starting media recovery
archived log for thread 1 with sequence 84 is already on disk as file /u01/app/oradup/product/11.2.0/dbs/arch1_84_868943464.dbf
archived log file name=/u01/app/oradup/product/11.2.0/dbs/arch1_84_868943464.dbf thread=1 sequence=84
media recovery complete, elapsed time: 00:00:02
Finished recover at 22-MAY-15
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2251816 bytes
Variable Size 159384536 bytes
Database Buffers 50331648 bytes
Redo Buffers 5189632 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DUPDB'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''DUPDB'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 217157632 bytes
Fixed Size 2251816 bytes
Variable Size 159384536 bytes
Database Buffers 50331648 bytes
Redo Buffers 5189632 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/app/oracle11g/oradata/DB11/onlinelog/o1_mf_1_bcc2h8rh_.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u01/app/oracle11g/oradata/DB11/onlinelog/o1_mf_2_bcc2hb1s_.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u01/app/oracle11g/oradata/DB11/onlinelog/o1_mf_3_bcc2hc3t_.log' ) SIZE 50 M REUSE
DATAFILE
'/u01/app/oradup/oradata/dupdbo1_mf_system_bcc2dp1d_.dbf'
CHARACTER SET ZHS16GBK
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oradup/oradata/dupdbo1_mf_temp_bcc2hld2_.tmp";
switch clone tempfile all;
catalog clone datafilecopy "/u01/app/oradup/oradata/dupdbo1_mf_sysaux_bcc2dp2c_.dbf",
"/u01/app/oradup/oradata/dupdbo1_mf_users_bcc2dp33_.dbf",
"/u01/app/oradup/oradata/dupdbo1_mf_new_user_bcp23qgn_.dbf",
"/u01/app/oradup/oradata/dupdbundotbs2.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oradup/oradata/dupdbo1_mf_temp_bcc2hld2_.tmp in control file
cataloged datafile copy
datafile copy file name=/u01/app/oradup/oradata/dupdbo1_mf_sysaux_bcc2dp2c_.dbf RECID=1 STAMP=880393355
cataloged datafile copy
datafile copy file name=/u01/app/oradup/oradata/dupdbo1_mf_users_bcc2dp33_.dbf RECID=2 STAMP=880393355
cataloged datafile copy
datafile copy file name=/u01/app/oradup/oradata/dupdbo1_mf_new_user_bcp23qgn_.dbf RECID=3 STAMP=880393355
cataloged datafile copy
datafile copy file name=/u01/app/oradup/oradata/dupdbundotbs2.dbf RECID=4 STAMP=880393355
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=880393355 file name=/u01/app/oradup/oradata/dupdbo1_mf_sysaux_bcc2dp2c_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=880393355 file name=/u01/app/oradup/oradata/dupdbo1_mf_users_bcc2dp33_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=3 STAMP=880393355 file name=/u01/app/oradup/oradata/dupdbo1_mf_new_user_bcp23qgn_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=4 STAMP=880393355 file name=/u01/app/oradup/oradata/dupdbundotbs2.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 22-MAY-15
RMAN>
RMAN>
RMAN>
RMAN> exit
Recovery Manager complete.
[oradup@testdb11a ~]$
[oradup@testdb11a ~]$
[oradup@testdb11a ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 22 17:47:11 2015
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, OLAP, Data Mining and Real Application Testing options
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string /u01/app/oracle11g/oradata/DB1
1/datafile/, /u01/app/oradup/o
radata/dupdb
db_name string DUPDB
db_unique_name string DUPDB
global_names boolean FALSE
instance_name string dupdb
lock_name_space string
log_file_name_convert string /u01/app/oracle11g/oradata/DB1
1/datafile/, /u01/app/oradup/o
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
radata/dupdb
processor_group_name string
service_names string DUPDB
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
dupdb OPEN
附錄二，錯誤：
RMAN> duplicate target database to 'dupdb' from active database;
Starting Duplicate Db at 22-MAY-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=27 device type=DISK
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DB11'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DUPDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '/u01/app/oracle/oradata/dupdb/control01.ctl';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''DB11'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''DUPDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes
Starting backup at 22-MAY-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/22/2015 05:46:23
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/22/2015 05:46:23
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server
解決：
主庫上也要加入tnsnames.ora中的字符串
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/22/2015 06:03:11
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-00201: control file version 11.2.0.4.0 incompatible with ORACLE version 11.2.0.0.0
ORA-00202: control file: '/u01/app/oracle/oradata/dupdb/control01.ctl'
數據庫軟件版本必須一致，至少是compatible參數是一致的
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/22/2015 17:28:46
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary file name /u01/app/oracle11g/oradata/DB11/onlinelog/o1_mf_3_bcc2hc3t_.log conflicts with a file used by the target database
RMAN-05001: auxiliary file name /u01/app/oracle11g/oradata/DB11/onlinelog/o1_mf_2_bcc2hb1s_.log conflicts with a file used by the target database
RMAN-05001: auxiliary file name /u01/app/oracle11g/oradata/DB11/onlinelog/o1_mf_1_bcc2h8rh_.log conflicts with a file used by the target database
解決：
如果在RMAN恢復時不指定 nofilenamecheck 參數，則在數據文件相同文件名恢復時會出現RMAN-05501錯誤，當主庫，備庫的數據庫文件目錄是一樣的時候，必須使用 nofilenamecheck參數告訴rman主庫和被創建的備份庫擁有一樣的文件目錄和文件名。
