使用rman duplicate 创建dg备库

使用rman duplicate搭建dg
一准备
1)两机器 安装数据库dbca，备库修改参数后创建pfile dbca删掉库

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(jinan,qingdao)' scope=both;        
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=jinan' scope=both;      
alter system set LOG_ARCHIVE_DEST_2='SERVICE=qingdao LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=qingdao'; 
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE ;    
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE ; 
alter system set FAL_SERVER=qingdao;   
alter system set FAL_CLIENT=jinan;           
alter system set STANDBY_FILE_MANAGEMENT=auto scope=both;

 

-----

alter system set db_unique_name=qingdao scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(jinan,qingdao)' scope=spfile;       
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=qingdao' scope=spfile;        
alter system set LOG_ARCHIVE_DEST_2='SERVICE=jinan LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=jinan' scope=spfile;    
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile;    
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=spfile;
alter system set FAL_SERVER=jinan;        
alter system set FAL_CLIENT=qingdao; 
alter system set DB_FILE_NAME_CONVERT='/oradata/jinan/','/data/qingdao/' scope=spfile;    
alter system set LOG_FILE_NAME_CONVERT='/oradata/jinan/','/data/qingdao/' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=auto scope=spfile; 

2)修改俩机器的tnsnames.ora

JINAN =   
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.221)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jinan)
    )
  )


QINGDAO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.222)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = qingdao)
    )
  )

3)在主端生成完全备份，包括归档日志与standby控制文件  

$rman target /   
    
RMAN> run{  
backup FORMAT '/rmanbackup/jinan_%U_%T'  
database  
include current controlfile for standby  
plus archivelog   
;  
}

 
4)将完全备份copy 到备机  

$ scp * 192.168.100.222:/rmanbackup/

  
5）--在备端以pfile启动到nomount状态，并创建spfile;(创建时db_name为qingdao这里要改为jinan）  

$ sqlplus / as sysdba  

 
Connected to an idle instance.  
  
SQL>  startup pfile=initorcl.ora nomount;  
ORACLE instance started.  
  
Total System Global Area  276824064 bytes  
Fixed Size          2020160 bytes  
Variable Size          96472256 bytes  
Database Buffers      176160768 bytes  
Redo Buffers            2170880 bytes  
SQL>  create spfile from pfile='/tmp/pfile2.ora';   该文件是我备库dbca建库修改完参数后创建的pfile，修改了db_name
  
File created. 

 
6)添加静态注册
=----

SID_LIST_LISTENER =
  (SID_LIST =
         (SID_DESC =
                   (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
                   (SID_NAME =qingdao)
         )
        
  )

-----
  动态注册实例中的服务是blocked
7）恢复  （此时注意，主库使用6中的静态注册连接到备库，两个convert参数要设置好，db_name一定要与主库一直，密码文件主库为orapwjinan
拷贝到备库为orapwqingdao，主库备份目录为rmanbackup备库也为/rmanbackup
在主端使用RMAN的Duplicate创建dg，如果数据文件与重做日志文件目录相同，要添加 nofilenamecheck ，否则不需要添加  

$ rmant  target /  
  
RMAN> connect auxiliary sys/oracle@qingdao


connected to auxiliary database: JINAN (not mounted)


RMAN> run{  
2> duplicate target database  
3> for standby nofilenamecheck dorecover;  
4> } 


Starting Duplicate Db at 21-JUN-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK


contents of Memory Script:
{
   set until scn  968316;
   restore clone standby controlfile;
}
executing Memory Script


executing command: SET until clause


Starting restore at 21-JUN-16
using channel ORA_AUX_DISK_1


channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /rmanbackup/jinan_03r8m46p_1_1_20160621
channel ORA_AUX_DISK_1: piece handle=/rmanbackup/jinan_03r8m46p_1_1_20160621 tag=TAG20160621T054603
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/data/qingdao/control01.ctl
output file name=/data/qingdao/control02.ctl
Finished restore at 21-JUN-16


contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script


sql statement: alter database mount standby database


contents of Memory Script:
{
   set until scn  968316;
   set newname for tempfile  1 to 
 "/data/qingdao/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/data/qingdao/system01.dbf";
   set newname for datafile  2 to 
 "/data/qingdao/sysaux01.dbf";
   set newname for datafile  3 to 
 "/data/qingdao/undotbs01.dbf";
   set newname for datafile  4 to 
 "/data/qingdao/users01.dbf";
   restore
   clone database
   ;
}
executing Memory Script


executing command: SET until clause


executing command: SET NEWNAME


renamed tempfile 1 to /data/qingdao/temp01.dbf in control file


executing command: SET NEWNAME


executing command: SET NEWNAME


executing command: SET NEWNAME


executing command: SET NEWNAME


Starting restore at 21-JUN-16
using channel ORA_AUX_DISK_1


channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /data/qingdao/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /data/qingdao/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /data/qingdao/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /data/qingdao/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /rmanbackup/jinan_02r8m3ss_1_1_20160621
channel ORA_AUX_DISK_1: piece handle=/rmanbackup/jinan_02r8m3ss_1_1_20160621 tag=TAG20160621T054603
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:15
Finished restore at 21-JUN-16


contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script


datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=915085237 file name=/data/qingdao/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=915085237 file name=/data/qingdao/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=915085237 file name=/data/qingdao/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=915085238 file name=/data/qingdao/users01.dbf


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


executing command: SET until clause


Starting recover at 21-JUN-16
using channel ORA_AUX_DISK_1


starting media recovery


channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=6
channel ORA_AUX_DISK_1: reading from backup piece /rmanbackup/jinan_04r8m47f_1_1_20160621
channel ORA_AUX_DISK_1: piece handle=/rmanbackup/jinan_04r8m47f_1_1_20160621 tag=TAG20160621T055142
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/arch/1_6_915074793.dbf thread=1 sequence=6
channel clone_default: deleting archived log(s)
archived log file name=/arch/1_6_915074793.dbf RECID=1 STAMP=915085252
media recovery complete, elapsed time: 00:00:09
Finished recover at 21-JUN-16
Finished Duplicate Db at 21-JUN-1616

8）添加standby redo（查看主库的日志数和大小）

SQL> select member from v$logfile;


MEMBER
--------------------------------------------------------------------------------
/data/qingdao/redo03.log
/data/qingdao/redo02.log
/data/qingdao/redo01.log


alter database add standby logfile 
    group 4 ('/data/qingdao/stdby_redo4.log') size 50m,
    group 5 ('/data/qingdao/stdby_redo5.log') size 50m,
    group 6 ('/data/qingdao/stdby_redo6.log') size 50m,
    group 7 ('/data/qingdao/stdby_redo7.log') size 50m;

9）启用备库恢复  

alter database recover managed standby database using current logfile disconnect from session;