Oracle11GDataGuard配置

1、目录1 .判断 DataGuard 是否安装 22 .网络配置 23 .监听配置 24 .主库前期准备 35 .创建口令文件 36 .修改主库初始化参数 47 .修改数据库运行在归档模式下 48 .创建备份库需要的控制文件 49 .备份生产数据库 510 .修改备库 pfile511 .将控制文件 512 .在备库上创建口令文件 513 .在备库上创建 spfile514 .启动物理备用数据库 615 .配置 StandbyRedoLog616 .StartRedoApply61.判断 DataGuard 是否安装select*fromv$optionwhereparameter=Oracle

2、DataGuard;2 .网络配置192.168.1.10(orcl)192.168.1.20(dg)3 .监听配置主库oraclenode1$cd/u01/app/product/11.2.0/db_1/network/adminoraclenode1admin$catlistener.oraLISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1)(PORT=1521)oraclenode1admin$cattnsnames.oraORCL=(DESCRIPTION=(ADDRESS_LIST=(A

3、DDRESS=(PROTOCOL=TCP)(HOST=node1)(PORT=1521)(CONNECT_DATA=(SERVICE_NAME=orcl)DG=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node2)(PORT=1521)(CONNECT_DATA=(SERVICE_NAME=dg)备库oraclenode1admin$catlistener.oraLISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node2)(PORT=1

4、521)oraclenode1admin$cattnsnames.oraORCL=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node1)(PORT=1521)(CONNECT_DATA=(SERVICE_NAME=orcl)DG=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node2)(PORT=1521)(CONNECT_DATA=(SERVICE_NAME=dg)4 .主库前期准备设置强制写日志SQLselectFORCE_LOGGINGfromv$da

5、tabase;NOSQLalterdatabaseforcelogging;SQLselectFORCE_LOGGINGfromv$database;YES5 .创建口令文件orapwdfile=$ORACLE_HOME/dbs/orapwdb1password=oracleentries=56 .修改主库初始化参数创建主库 pfilesqlcreatepfilefromspfile;修改 pfileDB_UNIQUE_NAME=orclLOG_ARCHIVE_CONFIG=DG_CONFIG=(orcl,dg)LOG_ARCHIVE_DEST_1=LOCATION=/u01/app/arch

6、iveVALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=orclLOG_ARCHIVE_DEST_2=SERVICE=dgLGWRASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=dgLOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLELOG_ARCHIVE_FORMAT=%t_%s_%r.arcFAL_SERVER=dgFAL_CLIENT=orclSTANDBY_FILE_MANAGEMENT=AUTO

7、Pfile 拷贝到备库上scp-rp/u01/app/product/11.2.0/db_1/dbs/initorcl.oranode2:/u01/app/product/11.2.0/db_1/dbs/7 .修改数据库运行在归档模式下SHUTDOWNIMMEDIATE;STARTUPMOUNT;ALTERDATABASEARCHIVELOG;ALTERDATABASEOPEN;8 .创建备份库需要的控制文件创建控制文件ShutdownimmediateSTARTUPMOUNT;ALTERDATABASECREATESTANDBYCONTROLFILEAS/tmp/orcl.ctl;ALTER

8、DATABASEOPEN;创建主库 pfileShutdownimmediatestartuppfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/20011.orasqlcreatespfilefrompfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/20011.ora;(先把原来的干掉)shutdownimmediate;startup9 .备份生产数据库scp-rp/u01/oradata/ORCLnode2:/u01/oradata/scp-rp/u01/app/admin/orclnode

9、2:/u01/app/admin(记得在备库创建 admin)10 .修改备库 pfileDB_UNIQUE_NAME=orclLOG_ARCHIVE_CONFIG=DG_CONFIG=(orcl,dg)LOG_ARCHIVE_FORMAT=log%t_%s_%r.arcLOG_ARCHIVE_DEST_1=LOCATION=/u01/app/archiveVALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=dgLOG_ARCHIVE_DEST_2=SERVICE=orclASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMAR

10、Y_ROLE)DB_UNIQUE_NAME=orclLOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLEFAL_SERVER=orclFAL_CLIENT=dgSTANDBY_FILE_MANAGEMENT=AUTO11.将控制文件scp-rp/tmp/dg.ctlnode2:/u01/oradata/ORCL/controlfile/scp-rp/tmp/dg.ctlnode2:/u01/flash_recovery_area/orcl/controlfile/12 .在备库上创建口令文件orapwdfile=$ORAC

11、LE_HOME/dbs/orapwdb1password=oracleentries=513 .在备库上创建 spfileShutdownimmediatestartuppfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/20011.oraCREATESPFILEFROMPFILE;14 .启动物理备用数据库STARTUPMOUNT;15 .配置 StandbyRedoLog在两边都配置 standbyredolog在主库查看日志组的数量和每个日志文件的大小SQLSELECTGROUP#,BYTESFROMV$LOG;在备库库查看日志组的数量和每

12、个日志文件的大小SQLSELECTGROUP#,BYTESFROMV$STANDBY_LOG;创建日志组和 redolog 文件SQLALTERDATABASEADDSTANDBYLOGFILEgroup/oracle/dbs/slog1.rdo)SIZE50M;SQLALTERDATABASEKDDSTANDBYJDGFILEgroup5(/oracle/dbs/slog2.rdo)SIZE50M;SQLALTERDATABASEADDSTANDBYLOGFILEgroup6o(-acle/dbs/slog3.rdo)SIZE50M;SQLALTERDATABASEADDSTANDBYLOG

13、FILEgroup/otacle/dbs/slog4.rdo)SIZE50M;16 .StartRedoApplyALTERDATABASERECOVERMANAGEDSTANDBYDATABASEUSINGCURRENTLOGFILEDISCONNECTFROMSESSION;查看哪些归档日志被 APPLYT在备库SELECTSEQUENCE#,APPLIEDFROMV$ARCHIVED_LOGORDERBYSEQUENCE#;在主库强制日志切换到当前的 onlineredologfile.ALTERSYSTEMARCHIVELOGCURRENT;在备库查看新白被归档的 redodataSE

14、LECTSEQUENCE#,FIRST_TIME,NEXT_TIMEFROMV$ARCHIVED_LOGORDERBYSEQUENCE#;在备库查看接收到的被应用的 redoSQLSELECTSEQUENCE#,APPLIEDFROMV$ARCHIVED_LOGORDERBYSEQUENCE#;查看数据库的角色selectdatabase_role,protection_mode,protection_levelfromv$database;主备库切换1 .查看主库的状态SQLSELECTSWITCHOVER_STATUSFROMV$DATABASE;2 .将主库切换至备用模式SQLALTER

15、DATABASECOMMITTOSWITCHOVERTOPHYSICALSTANDBYWITH-SESSIONSHUTDOWN;3 .关闭、装载主数据库SQLSHUTDOWNABORT;SQLSTARTUPMOUNT;4 .查看备库准备向主库模式切换SQLSELECTSWITCHOVER_STATUSFROMV$DATABASE;SWITCHOVER_STATUSTO_PRIMARY1rowselected5 .切换备库至主库模式SQLALTERDATABASECOMMITTOSWITCHOVERTOPRIMARYWITHSESSIONSHUTDOWN;6 .打开新的主数据库SQLALTERD

16、ATABASEOPEN;7 .在新的备库服务器上启动 REDOapply。SQLALTERDATABASERECOVERMANAGEDSTANDBYDATABASEUSINGCURRENTLOGFILE-DISCONNECTFROMSESSION;灾难恢复(failover)Step1FlushanyunsentredofromtheprimarydatabasetothetargetstandbydatabaseSQLALTERSYSTEMFLUSHREDOTOtarget_db_name;Step2Verifythatthestandbydatabasehasthemostrecently

17、archivedredologfileforeachprimarydatabaseredothread.SQLSELECTUNIQUETHREAD#ASTHREAD,MAX(SEQUENCE#)-OVER(PARTITIONBYthread#)ASLASTfromV$ARCHIVED_LOG;SQLALTERDATABASEREGISTERPHYSICALLOGFILEfilespec1;Step3Identifyandresolveanyarchivedredologgaps.SQLSELECTTHREAD#,LOW_SEQUENCE#,HIGH_SEQUENCE#FROMV$ARCHIVE

18、_GAP;SQLALTERDATABASEREGISTERPHYSICALLOGFILEfilespec1;Step4RepeatStep3untilallgapsareresolved.Step5StopRedoApply.IssuethefollowingSQLstatementonthetargetstandbydatabase:SQLALTERDATABASERECOVERMANAGEDSTANDBYDATABASECANCEL;Step6Finishapplyingallreceivedredodata.IssuethefollowingSQLstatementonthetarget

19、standbydatabase:SQLALTERDATABASERECOVERMANAGEDSTANDBYDATABASEFINISH;Step7Verifythatthetargetstandbydatabaseisreadytobecomeaprimarydatabase.Step8Switchthephysicalstandbydatabasetotheprimaryrole.IssuethefollowingSQLstatementonthetargetstandbydatabase:SQLALTERDATABASECOMMITTOSWITCHOVERTOPRIMARYWITHSESSIONSHUTDOWN;Step9Openthenewprimarydatabase.SQLALTERDATABASEOPEN;Step10Backup