利用DATAGUARD迁移大数据量的单实例至RAC环境(附DG之GAP修复)v10-副本26

利用Dataguard 迁移单实例至 RAC环境第一章 概述1.0 实验目的本文档以 xx局数据质量平台（ xx.xx.xx.xx）迁移至 RAC环境，通过 11g Dataguard迁移的模拟测试。并附加测试以 xx局在归档缺失下 Dataguard Gap修复案例背景的实验（另文档描述）。拟通过该实验能够更了解整个迁移、修复存在的风险及提出相关的优化建议。1.1 环境拓扑文档控制序号

更改人

版本号

日期

备注1 培植

V1.0

2013-04-29

编写第二章 DATAGUARD 环境迁移2.0RMAN 恢复方式搭建STANDBY2.0.1 主库的备份、开启归档startupmountalterdatabasearchivelog;、开启强制日志ALTERDATABASEFORCELOGGING;、检查日志模式SQL>selectname,log_mode,force_loggingfrom gv$database;NAME LOG_MODE FOR------------------------ORCL ARCHIVELOG YES、数据库全库备份run{allocatechannelc1typedisk;allocatechannelc2typedisk;backupdatabaseformat'/u01/rmanbak/Full_%U.bak';backuparchivelogallformat'/u01/rmanbak/ARC_%U.bak';backupdevicetypediskformat'/u01/rmanbak/standby_%U.ctl'currentcontrolfileforstandby;releasechannelc1;releasechannelc2;}5、备份片的生成情况，并拷贝备份集至rac的节点一[oracle@gfs3~]$ls-lrt/u01/rmanbaktotal1982188-rw-r-----1oracleoinstall510877696Jan1817:38Full_02oud3b6_1_1.bak-rw-r-----1oracleoinstall660815872Jan1817:38Full_01oud3b6_1_1.bak-rw-r-----1oracleoinstall98304Jan1817:38Full_04oud3d9_1_1.bak-rw-r-----1oracleoinstall9830400Jan1817:38Full_03oud3d8_1_1.bak-rw-r-----1oracleoinstall838291968Jan1817:39ARC_05oud3dr_1_1.bak-rw-r-----1oracleoinstall9830400Jan1817:40standby_06oud3fu_1_1.ctl拷贝备份集至 RAC节点上：[oracle@gfs3u01]$scp-rrmanbak0:/u012.0.2Dataguard 的配置、主库参数文件配置（需备份好原来的参数文件）*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'*.audit_trail='db'*.compatible='.0'*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'*.db_block_size=8192*.db_domain=''*.db_name='orcl'*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'*.db_recovery_file_dest_size=4322230272*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)'*.log_archive_format='%t_%s_%r.dbf'*.memory_target=451936256*.open_cursors=300*.processes=300*.remote_login_passwordfile='none'*.service_names='orcl'*.sessions=335*.undo_tablespace='UNDOTBS1'*.FAL_CLIENT='primary'*.FAL_SERVER='standby'*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl_st)'*.LOG_ARCHIVE_DEST_1='LOCATION=/archVALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=orcl'*.LOG_ARCHIVE_DEST_2='SERVICE=standbyLGWRASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES)DB_UNIQUE_NAME=orcl_st'*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'*.LOG_ARCHIVE_MAX_PROCESSES=6*.LOG_FILE_NAME_CONVERT='+DATA/orcl/DATAFILE/','/u01/app/oracle/oradata/orcl/'*.DB_FILE_NAME_CONVERT='+DATA/orcl/DATAFILE/','/u01/app/oracle/oradata/orcl/'*.remote_login_passwordfile='EXCLUSIVE'*.STANDBY_FILE_MANAGEMENT='AUTO'2、备库参数文件的配置（ RAC环境需注意标红部分）orcl1.__oracle_base='/u01/app/oracle'orcl2.__oracle_base='/u01/app/oracle'*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'*.audit_trail='db'*.cluster_database=TRUE*.cluster_database_instances=2*.compatible='.0'*.control_files='+DATA/orcl/controlfile/control01.ctl','+DATA/orcl/controlfile/control02.ctl'*.db_block_size=8192*.db_create_file_dest='+DATA'*.db_name='orcl'*.db_recovery_file_dest='+DATA'*.db_recovery_file_dest_size=48318382080*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)'orcl1.instance_number=1orcl2.instance_number=1*.log_archive_format='%t_%s_%r.dbf'*.memory_target=797152000*.nls_language='AMERICAN'*.open_cursors=300*.processes=350*.remote_listener='grid-scan:1521'*.remote_login_passwordfile='exclusive'*.session_cached_cursors=545*.sessions=280orcl1.thread=1orcl2.thread=2*.undo_management='AUTO'orcl2.undo_tablespace='UNDOTBS2'orcl1.undo_tablespace='UNDOTBS1'*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcl_st)'*.LOG_ARCHIVE_DEST_1='LOCATION=/arch1VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=orcl_st'orcl1.LOG_ARCHIVE_DEST_2='SERVICE=primaryLGWRASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES)DB_UNIQUE_NAME=orcl'*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'*.LOG_ARCHIVE_MAX_PROCESSES=6*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','+DATA/orcl/DATAFILE/'*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl/','+DATA/orcl/DATAFILE/'*.remote_login_passwordfile='EXCLUSIVE'*.STANDBY_FILE_MANAGEMENT='AUTO'*.FAL_CLIENT='standby'*.FAL_SERVER='primary'3、建立备库的 spfile创建备库spfile:SQL>createspfile='+DATA/orcl/spfileorcl.ora'frompfile;Gfs1节点上echoSPFILE='+DATA/orcl/spfileorcl.ora'>$ORACLE_HOME/dbs/initorcl1.oraGfs2节点上echo SPFILE='+DATA/orcl/spfileorcl.ora'>$ORACLE_HOME/dbs/initorcl2.ora创建密码文件：从主库拷贝密码文件至备库，并更名如下：$mv$ORACLE_HOME/dbs/orapworcl$mv$ORACLE_HOME/dbs/orapworcl

$ORACLE_HOME/dbs/orapworcl1$ORACLE_HOME/dbs/orapworcl22.0.3STANDBY 的初始化、恢复控制文件RMAN> restorestandbycontrolfilefrom'/home/oracle/rmanbak/standby_06oud3fu_1_1.ctl';Startingrestoreat2014-04-2522:33:28usingtargetdatabasecontrolfileinsteadofrecoverycatalogallocatedchannel:ORA_DISK_1channelORA_DISK_1:SID=37instance=orcl1devicetype=DISKchannelORA_DISK_1:restoringcontrolfilechannelORA_DISK_1:restorecomplete,elapsedtime:00:00:01outputfilename=+DATA/orcl/controlfile/control01.ctloutputfilename=+DATA/orcl/controlfile/control02.ctlFinishedrestoreat2014-04-2522:33:30RMAN>ASMCMD>lscontrol01.ctlcontrol02.ctlcurrent.257.845850809current.258.845850811ASMCMD>pwd+data/orcl/controlfileASMCMD>、注册备份集RMAN>catalogstartwith'/home/oracle/rmanbak/';searchingforallfilesthatmatchthepattern/home/oracle/rmanbak/ListofFilesUnknowntotheDatabase=====================================FileName:/home/oracle/rmanbak/ARC_05oud3dr_1_1.bakFileName:/home/oracle/rmanbak/standby_06oud3fu_1_1.ctlFileName:/home/oracle/rmanbak/Full_04oud3d9_1_1.bakFileName:/home/oracle/rmanbak/Full_01oud3b6_1_1.bakFileName:/home/oracle/rmanbak/Full_03oud3d8_1_1.bakFileName:/home/oracle/rmanbak/Full_02oud3b6_1_1.bakDoyoureallywanttocatalogtheabovefiles(enterYESorNO)?yescatalogingfiles...catalogingdoneListofCatalogedFiles=======================FileName:/home/oracle/rmanbak/ARC_05oud3dr_1_1.bakFileName:/home/oracle/rmanbak/standby_06oud3fu_1_1.ctlFileName:/home/oracle/rmanbak/Full_04oud3d9_1_1.bakFileName:/home/oracle/rmanbak/Full_01oud3b6_1_1.bakFileName:/home/oracle/rmanbak/Full_03oud3d8_1_1.bakFileName:/home/oracle/rmanbak/Full_02oud3b6_1_1.bak、恢复数据文件RMAN>run{allocatechannelc1typedisk;allocatechannelc2typedisk;restoredatabase;releasechannelc1;releasechannelc2;}报错信息如下：原因分析：这里可以看出，单实例和

RAC实例存储数据文件的存储方式肯定是不相同的，

那我们使用rman

的duplicate

功能时，使用

log_file_name_convert

和

db_file_name_convert来进行转换才会生效，本次使用的是

RMAN

的异机恢复，所以只能在

restore

的时候用

setnewname 来进行转换。、正确的恢复命令run{setnewnamefordatafile1to'+DATA/orcl/DATAFILE/system01.dbf';setnewnamefordatafile2to'+DATA/orcl/DATAFILE/sysaux01.dbf';setnewnamefordatafile3to'+DATA/orcl/DATAFILE/undotbs01.dbf';setnewnamefordatafile4to'+DATA/orcl/DATAFILE/users01.dbf';setnewnamefordatafile5to'+DATA/orcl/DATAFILE/ggdata.dbf';setnewnamefordatafile6to'+DATA/orcl/DATAFILE/users02.dbf';setnewnamefordatafile7to'+DATA/orcl/DATAFILE/single_rac01.dbf';setnewnamefortempfile1to'+DATA/orcl/DATAFILE/temp01.dbf';restoredatabase;switchdatafileall;switchtempfileall;setuntilscn1571225;recoverdatabase;}恢复过程如下：executingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEStartingrestoreat2014-04-2523:16:51usingtargetdatabasecontrolfileinsteadofrecoverycatalogallocatedchannel:ORA_DISK_1channelORA_DISK_1:SID=36instance=orcl1devicetype=DISKchannelORA_DISK_1:startingdatafilebackupsetrestorechannelORA_DISK_1:specifyingdatafile(s)torestorefrombackupsetchannelORA_DISK_1:restoringdatafile00002to+DATA/orcl/DATAFILE/sysaux01.dbfchannelORA_DISK_1:restoringdatafile00004to+DATA/orcl/DATAFILE/users01.dbfchannelORA_DISK_1:restoringdatafile00005to+DATA/orcl/DATAFILE/ggdata.dbfchannelORA_DISK_1:restoringdatafile00007to+DATA/orcl/DATAFILE/single_rac01.dbfchannelORA_DISK_1:readingfrombackuppiece/home/oracle/rmanbak/Full_02oud3b6_1_1.bakchannelORA_DISK_1:piecehandle=/home/oracle/rmanbak/Full_02oud3b6_1_1.baktag=TAG20140118T173741channelORA_DISK_1:restoredbackuppiece1channelORA_DISK_1:restorecomplete,elapsedtime:00:00:25channelORA_DISK_1:startingdatafilebackupsetrestorechannelORA_DISK_1:specifyingdatafile(s)torestorefrombackupsetchannelORA_DISK_1:restoringdatafile00001to+DATA/orcl/DATAFILE/system01.dbfchannelORA_DISK_1:restoringdatafile00003to+DATA/orcl/DATAFILE/undotbs01.dbfchannelORA_DISK_1:restoringdatafile00006to+DATA/orcl/DATAFILE/users02.dbfchannelORA_DISK_1:readingfrombackuppiece/home/oracle/rmanbak/Full_01oud3b6_1_1.bakchannelORA_DISK_1:piecehandle=/home/oracle/rmanbak/Full_01oud3b6_1_1.baktag=TAG20140118T173741channelORA_DISK_1:restoredbackuppiece1channelORA_DISK_1:restorecomplete,elapsedtime:00:00:35Finishedrestoreat2014-04-2523:17:52datafile1switchedtodatafilecopyinputdatafilecopyRECID=8STAMP=845853473filename=+DATA/orcl/datafile/system01.dbfdatafile2switchedtodatafilecopyinputdatafilecopyRECID=9STAMP=845853473filename=+DATA/orcl/datafile/sysaux01.dbfdatafile3switchedtodatafilecopyinputdatafilecopyRECID=10STAMP=845853473filename=+DATA/orcl/datafile/undotbs01.dbfdatafile4switchedtodatafilecopyinputdatafilecopyRECID=11STAMP=845853473filename=+DATA/orcl/datafile/users01.dbfdatafile5switchedtodatafilecopyinputdatafilecopyRECID=12STAMP=845853473filename=+DATA/orcl/datafile/ggdata.dbfdatafile6switchedtodatafilecopyinputdatafilecopyRECID=13STAMP=845853473filename=+DATA/orcl/datafile/users02.dbfdatafile7switchedtodatafilecopyinputdatafilecopyRECID=14STAMP=845853474filename=+DATA/orcl/datafile/single_rac01.dbfrenamedtempfile1to+DATA/orcl/DATAFILE/temp01.dbfincontrolfileexecutingcommand:SETuntilclauseStartingrecoverat2014-04-2523:20:16usingchannelORA_DISK_1startingmediarecoverymediarecoverycomplete,elapsedtime:00:00:02Finishedrecoverat2014-04-2523:20:202.0.4STANDBY 同步1、添加线程 2的在线日志组和 undo 表空间ALTERDATABASEADDLOGFILETHREAD2GROUP4‘/u01/app/oracle/oradata/orcl/redo04'size50m;ALTERDATABASEADDLOGFILETHREAD2GROUP5‘/u01/app/oracle/oradata/orcl/redo05'size50m;ALTERDATABASEADDLOGFILETHREAD2GROUP6‘/u01/app/oracle/oradata/orcl/redo06'size50m;ALTERDATABASEENABLETHREAD2;createundotablespaceUNDOTBS2datafile'+DATA/orcl/DATAFILE/undotbs02.dbf'size100m;2、若启动实时同步，需要添加standbylogSQL>alterdatabaseaddstandbylogfilethread1group7size50m,group8size50m,group9size50m,group10size50m;alterdatabaseaddstandbylogfilethread2group11size50m,group12size50m,group13size50m，group14size50m；3、检查Dataguard的日志传送和日志应用情况SQL>SELECTREGISTRAR,CREATOR,THREAD#,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#FROMV$ARCHIVED_LOG;REGISTRCREATORTHREAD#SEQUENCE#FIRST_CHANGE#NEXT_CHANGE#-----------------------------------------------------------SRMNSRMN14115709061571225RFSARCH14615919451591949RFSARCH14515919411591945RFSARCH14315881471588237RFSARCH14415882371591941RFSARCH14215712251588147RFSARCH14815923141592335RFSARCH14715919491592314RFSARCH14915923351593065RFSARCH15015930651593194SQL>selectSEQUENCE#,FIRST_TIME,NEXT_TIME,CREATOR,APPLIED,ARCHIVED,status,STANDBY_DESTfromv$archived_log;SEQUENCE#FIRST_TIMNEXT_TIMECREATORAPPLIEDARCSTATUSSTANDBY_DE-------------------------------------------------------------------4118-JAN-1418-JAN-14SRMNYESYESDNO4619-JAN-1419-JAN-14ARCHNOYESANO4519-JAN-1419-JAN-14ARCHNOYESANO4319-JAN-1419-JAN-14ARCHNOYESANO4419-JAN-1419-JAN-14ARCHNOYESANO4218-JAN-1419-JAN-14ARCHNOYESANO4819-JAN-1419-JAN-14ARCHNOYESANO4719-JAN-1419-JAN-14ARCHNOYESANO4919-JAN-1419-JAN-14ARCHNOYESANO5019-JAN-1419-JAN-14ARCHNOYESANOSQL>SELECTREGISTRAR,CREATOR,THREAD#,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#FROMV$ARCHIVED_LOG;REGISTRCREATORTHREAD#SEQUENCE#FIRST_CHANGE#NEXT_CHANGE#-----------------------------------------------------------SRMNSRMN14115709061571225RFSARCH14615919451591949RFSARCH14515919411591945RFSARCH14315881471588237RFSARCH14415882371591941RFSARCH14215712251588147RFSARCH14815923141592335RFSARCH14715919491592314RFSARCH14915923351593065RFSARCH15015930651593194SQL>SELECTREGISTRAR,CREATOR,THREAD#,SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#FROMV$ARCHIVED_LOG;REGISTRCREATOR THREAD# SEQUENCE#FIRST_CHANGE#NEXT_CHANGE#--------------