Oracle 11gR2 使用RMAN Duplicate複製資料庫

kunlunzhiying發表於2017-02-09

Oracle 11gR2 使用RMAN Duplicate複製資料庫

作者:吳偉龍 PrudentWoo QQ：286507175

前言:

上週剛做完一個專案，使用者要求RAC的資料庫能夠自動備份到另外一個單節點上，單節點能夠正常拿起來就能用。當時跟他們討論的是用ADG來做，但通過描述後，使用者覺得如果要人工干預的話太麻煩，它不想做任何的人工干預，實現資料自動到這臺單機上來，那只是一臺備用的資料庫，沒事的時候可以登入上去看看歷史的資料情況。

這個時候我提出了11g的RMAN Duplicate方案，它可以線上實現異機的線上備份，且無需人工干預，通過網路將資料傳輸過來後能夠自動open備機.

如圖：

RMAN Duplicate 是從Oracle 10g就已經開始有的一個功能；但是在11g的時候進行了很多增強，免去了10g版本下需要人工複製到備機上過來進行恢復的尷尬境地。通過11g的Duplicate可以建立一個完全相同但DBID不同的備機資料庫。在11g的RMAN Duplicate中可以通過Active database duplicate和Backup-based duplicate兩種方法實現。我們在使用者現場採用的是Active Database Duplicate;因為它不用先把目標資料庫進行RMAN備份，只需要目標資料庫處於歸檔模式下即可通過網路對資料庫進行恢復，且恢復完成後能夠自動Open Duplicate Database。這樣就正好滿足了使用者不用人工干預的需求。尤其是對於大資料庫特別是TB級別的資料庫其優點時非常明顯，恢復前不需要進行單獨的備份，減少了備份及拷貝備份檔案的時間，同時還節省了備份空間。下面我們來進行完整的RAC to Single Instance的Active Database Duplicate操作。

一、環境介紹

	*HostName*	*DBName*	*public ip*
11gR2 RAC	db01	woo1	192.168.7.51
11gR2 RAC	db02	woo2	192.168.7.52
Duplicate	standby	woo	192.168.7.55

二、開始配置Duplicate Database，步驟如下：
2.1 構建輔助資料庫目錄結構，配置輔助資料庫相關係統引數。

2.2 安裝軟體並建立資料庫。

2.3 開啟歸檔
2.3 建立pfile,並修改
2.4 建立輔助資料庫例項口令檔案
2.5 配置靜態監聽
2.6 用pfile檔案，將輔助資料庫啟動到nomount狀態
2.7 執行RMAN duplicate命令複製資料庫
2.8 建立spfile檔案

三、構建輔助資料庫目錄結構，配置輔助資料庫相關引數。（簡）

/usr/sbin/groupadd -g 502 dba
/usr/sbin/groupadd -g 501 oinstall
/usr/sbin/useradd -u 502 -g oinstall -G dba oracle
mkdir -p /DBSoft/oraInventory
mkdir -p /DBSoft/oracle/product/11.2.4/dbhome_1
chown -R oracle:oinstall /DBSoft
chmod -R 775 /DBSoft
echo "oracle"|passwd --stdin oracle
yum install -y setarch binutils compat-libstdc++-33 compat-libcap1 compat-db compat-libstdc++ compat-gcc elfutils-libelf elfutils-libelf-devel gcc gcc-c++ glibc glibc-common glibc
-devel libaio libaio-devel libgcc libstdc++ libstdc++-devel make ksh numactl sysstat libXp unixODBC unixODBC-devel udev
cat >> /etc/sysctl.conf < kernel.shmall = 4294967296
kernel.sem = 510 65280 510 128
kernel.shmmni = 4096
kernel.shmmax = 137438953472
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.file-max = 6815744
fs.aio-max-nr = 1048576
vm.swappiness = 0
vm.dirty_background_ratio = 3
vm.dirty_ratio = 80
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
net.ipv4.tcp_sack = 0
net.ipv4.tcp_timestamps = 0
net.ipv4.conf.default.rp_filter = 0
net.ipv4.tcp_wmem = 262144
net.ipv4.tcp_rmem = 4194304
EOF

/sbin/sysctl –p
cat >> /etc/security/limits.conf < oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 65536
oracle hard nofile 65536
oracle soft memlock 3145728
oracle hard memlock 3145728

#signaturelevin
EOF
cat >> /etc/profile <

if [ \$USER = "oracle" ]; then
if [ \$SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
EOF

cat >> /home/oracle/.bash_profile < export TMP=/tmp
export TMPDIR=\$TMP
export EDIT=vi
alias vi=vim
export ORACLE_HOSTNAME=$hostname
export ORACLE_UNQNAME=$db_name
export ORACLE_BASE=/DBSoft/oracle
export ORACLE_HOME=\$ORACLE_BASE/product/11.2.4/dbhome_1
export ORACLE_SID=$instance_name
export ORACLE_TERM=xterm
export PATH=/usr/sbin:\$PATH
export PATH=\$ORACLE_HOME/bin:\$PATH
export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=\$ORACLE_HOME/JRE:\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib
export NLS_LANG=AMERICAN_AMERICA AL32UTF16
umask 022
EOF

四、安裝軟體並建立資料庫

./runInstaller -silent -debug -force -responseFile /home/oracle/database/response/db_install.rsp

dbca -silent -responsefile /home/oracle/database/response/dbca.rsp

五、開啟歸檔

SQL> alter system set log_archive_dest_1=\'location=+FRA001/archive\' scope=spfile sid=\'woo1\';

System altered.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination +FRA001/archive

Oldest online log sequence 978

Next log sequence to archive 979

Current log sequence 979

SQL> alter system switch logfile;

alter system switch logfile;

System altered.

SQL>

System altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination +FRA001/archive

Oldest online log sequence 980

Next log sequence to archive 981

Current log sequence 981

六、配置靜態監聽

RAC節點監聽資訊如下:

$ cd $ORACLE_HOME/network/admin
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan.woo.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = woo.com)
(ORACLE_HOME = /DBSoft/oracle/product/11.2.0/db_1)
(SID_NAME = woo)
)
)
ADR_BASE_LISTENER = /DBSoft/oracle

單節點監聽資訊如下:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.55)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = woo.com)
(ORACLE_HOME = /DBSoft/oracle/product/11.2.0/db_1)
(SID_NAME = woo)
)
)
ADR_BASE_LISTENER = /DBSoft/oracle

配置所有節點tnsname資訊如下:

WOO =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan.woo.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = woo)
)
)
DUPLICATE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.55)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = woo)
)
)

六、啟動資料庫到nomount狀態

SQL> startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 954155008 bytes
Fixed Size 1368632 bytes
Variable Size 306187720 bytes
Database Buffers 641728512 bytes
Redo Buffers 4870144 bytes
SQL>exit ---------必須退出
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

七、在rac端任意節點通過RMAN連線到目標例項和輔助例項，執行duplicate命令複製資料庫：

[oracle@db01 ~]$ rman target sys/oracle@woo auxiliary sys/oracle@standby

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jun 3 19:39:00 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: WOO (DBID=4218366793)
connected to auxiliary database: WOO (not mounted)

RMAN>
RMAN> duplicate target database to woo from active database nofilenamecheck;

Starting Duplicate Db at 03-JUN-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 "alter system set db_name =
''WOO'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''WOO'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '/DBSoft/oracle/oradata/woo/control01.ctl';
restore clone controlfile to '/DBSoft/oracle/fast_recovery_area/woo/control02.ctl' from
'/DBSoft/oracle/oradata/woo/control01.ctl';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''WOO'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''WOO'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 954155008 bytes

Fixed Size 1368632 bytes
Variable Size 306187720 bytes
Database Buffers 641728512 bytes
Redo Buffers 4870144 bytes

Starting backup at 03-JUN-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/snapcf_woo.f tag=TAG20150603T194000 RECID=3 STAMP=881437202
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
Finished backup at 03-JUN-15

Starting restore at 03-JUN-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 03-JUN-15

database mounted

contents of Memory Script:
{
sql clone 'alter database flashback off';
set newname for datafile 1 to
"/DBSoft/oracle/oradata/woo/system01.dbf";
set newname for datafile 2 to
"/DBSoft/oracle/oradata/woo/sysaux01.dbf";
set newname for datafile 3 to
"/DBSoft/oracle/oradata/woo/undotbs01.dbf";
set newname for datafile 4 to
"/DBSoft/oracle/oradata/woo/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/DBSoft/oracle/oradata/woo/system01.dbf" datafile
2 auxiliary format
"/DBSoft/oracle/oradata/woo/sysaux01.dbf" datafile
3 auxiliary format
"/DBSoft/oracle/oradata/woo/undotbs01.dbf" datafile
4 auxiliary format
"/DBSoft/oracle/oradata/woo/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

sql statement: alter database flashback off

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME
Starting backup at 03-JUN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/DBSoft/oracle/oradata/woo/system01.dbf
output file name=/DBSoft/oracle/oradata/woo/system01.dbf tag=TAG20150603T194019
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:04:47
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/DBSoft/oracle/oradata/woo/sysaux01.dbf
output file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf tag=TAG20150603T194019
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/DBSoft/oracle/oradata/woo/undotbs01.dbf
output file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf tag=TAG20150603T194019
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/DBSoft/oracle/oradata/woo/users01.dbf
output file name=/DBSoft/oracle/oradata/woo/users01.dbf tag=TAG20150603T194019
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 03-JUN-15

sql statement: alter system archive log current

contents of Memory Script:
{
backup as copy reuse
archivelog like "/DBArchive/archive/1_63_878860684.dbf" auxiliary format
"/DBArchive2/archive/1_63_878860684.dbf" ;
catalog clone archivelog "/DBArchive2/archive/1_63_878860684.dbf";
switch clone datafile all;
}
executing Memory Script

Starting backup at 03-JUN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=63 RECID=38 STAMP=881437711
output file name=/DBArchive2/archive/1_63_878860684.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 03-JUN-15

cataloged archived log
archived log file name=/DBArchive2/archive/1_63_878860684.dbf RECID=38 STAMP=881437696

datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=881437696 file name=/DBSoft/oracle/oradata/woo/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=881437696 file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=881437696 file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=881437696 file name=/DBSoft/oracle/oradata/woo/users01.dbf

contents of Memory Script:
{
set until scn 1338740;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 03-JUN-15
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 63 is already on disk as file /DBArchive2/archive/1_63_878860684.dbf
archived log file name=/DBArchive2/archive/1_63_878860684.dbf thread=1 sequence=63
media recovery complete, elapsed time: 00:00:00
Finished recover at 03-JUN-15
Oracle instance started

Total System Global Area 954155008 bytes

Fixed Size 1368632 bytes
Variable Size 306187720 bytes
Database Buffers 641728512 bytes
Redo Buffers 4870144 bytes

contents of Memory Script:
{
sql clone "alter system set db_name =
''WOO'' 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 = ''WOO'' 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 954155008 bytes

Fixed Size 1368632 bytes
Variable Size 306187720 bytes
Database Buffers 641728512 bytes
Redo Buffers 4870144 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "WOO" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/DBSoft/oracle/oradata/woo/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/DBSoft/oracle/oradata/woo/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/DBSoft/oracle/oradata/woo/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/DBSoft/oracle/oradata/woo/system01.dbf'
CHARACTER SET WE8MSWIN1252

contents of Memory Script:
{
set newname for tempfile 1 to
"/DBSoft/oracle/oradata/woo/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/DBSoft/oracle/oradata/woo/sysaux01.dbf",
"/DBSoft/oracle/oradata/woo/undotbs01.dbf",
"/DBSoft/oracle/oradata/woo/users01.dbf";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /DBSoft/oracle/oradata/woo/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf RECID=1 STAMP=881437718
cataloged datafile copy
datafile copy file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf RECID=2 STAMP=881437718
cataloged datafile copy
datafile copy file name=/DBSoft/oracle/oradata/woo/users01.dbf RECID=3 STAMP=881437718

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=881437718 file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=881437718 file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=881437718 file name=/DBSoft/oracle/oradata/woo/users01.dbf

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Reenabling controlfile options for auxiliary database
Executing: alter database flashback on
Finished Duplicate Db at 03-JUN-15

RMAN>

至此，duplicate已經完成，standby資料庫已經起來了。

[oracle@db02 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 3 19:53:02 2015
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> col host_name format a15
SQL> col file_name format a40
SQL> set line 150
SQL> select host_name,instance_name,status from gv$instance;
HOST_NAME INSTANCE_NAME STATUS
--------------- ---------------- ------------
db02 woo OPEN
SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ----------------------------------------
SYSTEM /DBSoft/oracle/oradata/woo/system01.dbf
SYSAUX /DBSoft/oracle/oradata/woo/sysaux01.dbf
UNDOTBS1 /DBSoft/oracle/oradata/woo/undotbs01.dbf
USERS /DBSoft/oracle/oradata/woo/users01.dbf

注意:這裡的nofilenamecheck引數需要解釋下:

如果在複製時，位置不同時，我們會用引數db_file_name_convert 對檔案位置進行轉換。但是在這個複製示例中我們用的是相同的位置。所以這裡必須加上nofilenamecheck引數。該引數通知複製操作不必在執行還原操作前確認檔名是不同的。如果沒有指定nofilenamecheck引數，rman會給出如下錯誤：

RMAN-05001: auxiliary filename /DBSoft/oracle/oradata/woo/users01.dbf conflicts with a file used by the target database

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

Oracle 11gR2 使用 RMAN duplicate from active database 複製資料庫
2017-11-09
OracleDatabase資料庫
【RMAN】使用duplicate本地複製資料庫
2014-11-27
資料庫
【RMAN】使用RMAN duplicate複製同機資料庫
2011-08-17
資料庫
使用rman的命令duplicate複製資料庫
2009-09-18
資料庫
Oracle11gR2使用RMAN duplicate複製資料庫
2015-07-15
Oracle資料庫
duplicate rman複製資料庫技術
2013-05-30
資料庫
使用RMAN高階應用之Duplicate複製資料庫
2014-08-31
資料庫
RMAN duplicate from active database 複製資料庫
2014-10-28
Database資料庫
Rman duplicate資料庫複製(單系統)
2011-05-10
資料庫
【RMAN】使用RMAN duplicate複製同機資料庫遇到的問題
2011-08-17
資料庫
Oracle 之 Duplicate 複製資料庫
2014-11-13
Oracle資料庫
RMAN高階應用之Duplicate複製資料庫(3)複製流程
2007-12-05
資料庫
oracle rman複製資料庫
2007-06-04
Oracle資料庫
使用RMAN複製資料庫
2014-05-22
資料庫
Oracle備份與恢復系列（四）續 RMAN Duplicate複製資料庫
2014-11-27
Oracle資料庫
RMAN實戰系列之一：用duplicate複製資料庫
2004-09-22
資料庫
RMAN高階應用之Duplicate複製資料庫(1)概述
2007-12-03
資料庫
DG rman duplicate 複製庫錯誤
2015-03-24
RMAN高階應用之Duplicate複製資料庫(4)實戰
2007-12-06
資料庫
RMAN高階應用之Duplicate複製資料庫(5)補充
2007-12-07
資料庫
使用RMAN建立Duplicate資料庫
2009-04-22
資料庫
DUPLICATE遠端複製資料庫
2014-10-13
資料庫
使用RMAN複製資料庫 active database
2019-02-14
資料庫Database
使用RMAN進行資料庫複製
2011-04-14
資料庫
實戰10g新特性之rman duplicate複製資料庫
2013-02-15
資料庫
duplicate複製資料庫（rac-rac）
2017-04-26
資料庫
RMAN複製資料庫（十)
2015-03-27
資料庫
RMAN複製資料庫（九）
2015-03-27
資料庫
RMAN複製資料庫（八）
2015-03-27
資料庫
RMAN複製資料庫（七)
2015-03-27
資料庫
RMAN複製資料庫（六）
2015-03-26
資料庫
RMAN複製資料庫（五）
2015-03-26
資料庫
RMAN複製資料庫（四）
2015-03-26
資料庫
RMAN複製資料庫（三）
2015-03-26
資料庫
RMAN複製資料庫（二）
2015-03-26
資料庫
RMAN複製資料庫（一）
2015-03-26
資料庫
RMAN高階應用之Duplicate複製資料庫(2)輔助例項
2007-12-04
資料庫
續上_在同一節點上利用rman duplicate複製資料庫
2009-11-30
資料庫

Oracle 11gR2 使用RMAN Duplicate複製資料庫

Oracle 11gR2 使用RMAN Duplicate複製資料庫

至此，duplicate已經完成，standby資料庫已經起來了。

相關文章