程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle11gR2使用RMANDuplicate復制數據庫

Oracle11gR2使用RMANDuplicate復制數據庫

編輯：Oracle教程

Oracle11gR2使用RMANDuplicate復制數據庫

Oracle 11gR2 使用RMAN Duplicate復制數據庫

前言:

上周剛做完一個項目，用戶要求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 <> /etc/security/limits.conf <> /etc/profile <> /home/oracle/.bash_profile < 
 
四、安裝軟件並創建數據庫 
./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