Oracle 11gR2 使用RMAN Duplicate複製資料庫
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
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 <<EOF
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 <<EOF
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 <<EOF
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 <<EOF
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
-
-
Copyright (c) 1982, 2013, Oracle. All rights reserved.
-
-
-
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/28211342/viewspace-2133249/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 11gR2 使用 RMAN duplicate from active database 複製資料庫OracleDatabase資料庫
- 【RMAN】使用duplicate本地複製資料庫資料庫
- 【RMAN】使用RMAN duplicate複製同機資料庫資料庫
- 使用rman的命令duplicate複製資料庫資料庫
- Oracle11gR2使用RMAN duplicate複製資料庫Oracle資料庫
- duplicate rman複製資料庫技術資料庫
- 使用RMAN高階應用之Duplicate複製資料庫資料庫
- RMAN duplicate from active database 複製資料庫Database資料庫
- Rman duplicate資料庫複製(單系統)資料庫
- 【RMAN】使用RMAN duplicate複製同機資料庫遇到的問題資料庫
- Oracle 之 Duplicate 複製資料庫Oracle資料庫
- oracle rman複製資料庫Oracle資料庫
- RMAN高階應用之Duplicate複製資料庫(3)複製流程資料庫
- 使用RMAN複製資料庫資料庫
- Oracle備份與恢復系列（四）續 RMAN Duplicate複製資料庫Oracle資料庫
- RMAN實戰系列之一：用duplicate複製資料庫資料庫
- RMAN高階應用之Duplicate複製資料庫(1)概述資料庫
- DG rman duplicate 複製庫錯誤
- 使用RMAN建立Duplicate資料庫資料庫
- RMAN高階應用之Duplicate複製資料庫(4)實戰資料庫
- RMAN高階應用之Duplicate複製資料庫(5)補充資料庫
- DUPLICATE遠端複製資料庫資料庫
- 使用RMAN複製資料庫 active database資料庫Database
- 使用RMAN進行資料庫複製資料庫
- 實戰10g新特性之rman duplicate複製資料庫資料庫
- duplicate複製資料庫（rac-rac）資料庫
- RMAN複製資料庫（十)資料庫
- RMAN複製資料庫（九）資料庫
- RMAN複製資料庫（八）資料庫
- RMAN複製資料庫（七)資料庫
- RMAN複製資料庫（六）資料庫
- RMAN複製資料庫（五）資料庫
- RMAN複製資料庫（四）資料庫
- RMAN複製資料庫（三）資料庫
- RMAN複製資料庫（二）資料庫
- RMAN複製資料庫（一）資料庫
- RMAN高階應用之Duplicate複製資料庫(2)輔助例項資料庫
- 續上_在同一節點上利用rman duplicate複製資料庫資料庫