逻辑Data Guard维护篇.doc

逻辑DataGuard维护1、 主备库的转换之switchoverSQL show parameter FAL;NAME TYPE VALUE- - -fal_client string orcl_p.1_tnsfal_server string orcl_s.2_tnsSQL show parameter log_archive_dest;NAME TYPE VALUE- - -log_archive_dest stringlog_archive_dest_1 string location=/u01/archlog_archive_dest_10 stringlog_archive_dest_2 string service=orcl_s.2_tns arch vali d_for=(online_logfiles, primar y_role) db_unique_name=orcl2log_archive_dest_3 stringlog_archive_dest_4 stringlog_archive_dest_5 stringlog_archive_dest_6 stringlog_archive_dest_7 stringNAME TYPE VALUE- - -log_archive_dest_8 stringlog_archive_dest_9 stringlog_archive_dest_state_1 string enablelog_archive_dest_state_10 string enablelog_archive_dest_state_2 string ENABLElog_archive_dest_state_3 string enablelog_archive_dest_state_4 string enablelog_archive_dest_state_5 string enablelog_archive_dest_state_6 string enablelog_archive_dest_state_7 string enablelog_archive_dest_state_8 string enableNAME TYPE VALUE- - -log_archive_dest_state_9 string enableSQL alter system set log_archive_dest_1=location=/u01/arch1 valid_for=(online_logfiles, all_roles) db_unique_name=orcl1 ;System altered.SQL alter system set log_archive_dest_3=location=/u01/arch2 valid_for=(standby_logfiles, standby_role) db_unique_name=orcl1 ;System altered.SQL show parameter log_archive_dest;NAME TYPE VALUE- - -log_archive_dest stringlog_archive_dest_1 string location=/u01/archlog_archive_dest_10 stringlog_archive_dest_2 string service=orcl_s.2_tns arch vali d_for=(online_logfiles, primar y_role) db_unique_name=orcl2log_archive_dest_3 string location=/u01/arch2 valid_for= (standby_logfiles, standby_rol e) db_unique_name=orcl1log_archive_dest_4 stringlog_archive_dest_5 string。创建三组standby 的 redolog (至少也要两组)SQL alter database add standby logfile group 4 /u01/app/oracle/oradata/orcl/stdredo01.log size 50m;Database altered.SQL alter database add standby logfile group 5 /u01/app/oracle/oradata/orcl/stdredo02.log size 50m;Database altered.SQL alter database add standby logfile group 6 /u01/app/oracle/oradata/orcl/stdredo03.log size 50m;Database altered.查看一下primary是switchover状态，结果为TO STANDBY或者SESSIONS ACTIVE视为正常SQL select switchover_status from v$database;SWITCHOVER_STATUS-SESSIONS ACTIVE将primary数据库置为将要装换的状态SQL alter database prepare to switchover to logical standby;Database altered.查看当前数据库状态SQL select switchover_status from v$database;SWITCHOVER_STATUS-PREPARING SWITCHOVER将逻辑standby数据库转换为primarySTANDBY alter database prepare to switchover to primary;Database altered.查看备份数据库的状态STANDBY select switchover_status from v$database;SWITCHOVER_STATUS-PREPARING SWITCHOVER此时查看primary数据库的转换状态PRIMARY select switchover_status from v$database;SWITCHOVER_STATUS-TO LOGICAL STANDBY将primary数据库转换为逻辑的standbyPRIMARY alter database commit to switchover to logical standby;Database altered.返回再去看看原standby数据库的状态，很神奇STANDBY select switchover_status from v$database;SWITCHOVER_STATUS-TO PRIMARY将该standby数据库转换为primarySTANDBY alter database commit to switchover to primary;Database altered.逻辑standby转换完成，注意将新转换来的standby启动SQL应用PRIMARY set sqlprompt NEW_STANDBY NEW_STANDBY alter database start logical standby apply immediate;Database altered.在心的primary端添加数据进行检测：STANDBY set sqlprompt NEW_PRIMARY NEW_PRIMARY select * from scott.dg_test;ID-DG_TEST_1DG_TEST_2DG_TEST_3DG_TEST_4NEW_PRIMARY insert into scott.DG_TEST 2 values(DG_TEST_5) 3 /1 row created.NEW_PRIMARY select * from scott.DG_TEST;ID-DG_TEST_1DG_TEST_2DG_TEST_3DG_TEST_4DG_TEST_5NEW_PRIMARY commit;Commit complete.NEW_PRIMARY alter system switch logfile;System altered.在新的standby端查看数据是否接收NEW_STANDBY select * from scott.dg_test;ID-DG_TEST_1DG_TEST_2DG_TEST_3DG_TEST_4DG_TEST_5小结：1、 在进行转换前，要确定备库的设置健全，比如几个不同日志存放目录的设置2、 在将数据库置为开始转换状态时，由于，或因某些原因另一数据库无法自动到TO STANDBY或TO PRIMAYR状态，可用下列语句取消转换状态:alter database prepare to switchover cancel;2、 逻辑DG主备库转换之failover既然是failover，那么就认为PRIMARY数据库已经崩溃，需要马上将其中的一个逻辑STANDBY数据库置为PRIMAYD虽然primary数据库已经崩溃，如果能打开数据到mount状态，可以先查看一下日志情况，这样可以对日志进行一些操作，尽量的来减少数据的丢失，当然，也可以在数据库不能mount的时候去归档路径下查看归档信息。SQL select max(sequence#) from v$archived_log;MAX(SEQUENCE#)- 15STANDBY select sequence#,applied from dba_logstdby_log; SEQUENCE# APPLIED- - 15 CURRENT此处日志已经完全被应用，如果发现有日志未被发送到STANDBY，可以手工的将日志复制过去，并执行alter database register logical logfile filename语句手动的注册查看逻辑standby的日志应用情况STANDBY select applied_scn,latest_scn from v$logstdby_progress;APPLIED_SCN LATEST_SCN- - 540775 540775如果两结果一致则证明日志已经应用完毕，如果不一致，则需要启动SQL应用配置一下日志的归档路径STANDBY alter system set log_archive_dest_1=location=/u01/log/arch_online valid_for=(online_logfiles, all_roles) db_unique_name=orcl_std;System altered.STANDBY alter system set log_archive_dest_3=location=/u01/log/arch_pri_log valid_for=(standby_logfiles, standby_roles) db_unique_name=orcl_std;System altered.STANDBY show parameter archive_dest;NAME TYPE VALUE- - -log_archive_dest stringlog_archive_dest_1 string location=/u01/log/arch_online valid_for=(online_logfiles, al l_roles) db_unique_name=orcl_s tdlog_archive_dest_10 stringlog_archive_dest_2 string service=orcl_p_tns arch valid_ for=(online_logfiles,primary_r ole) db_unique_name=orcl_prilog_archive_dest_3 string location=/u01/log/arch_pri_log valid_for=(standby_logfiles, standby_roles) db_unique_name= orcl_std转换之前先看一下数据库的角色STANDBY select database_role,force_logging from v$database;DATABASE_ROLE FOR- -LOGICAL STANDBY YES开始转换STANDBY alter database activate logical standby database finish apply;Database altered.该语句会听着该standby数据库的RFS服务，并应用当前已经接收但并未应用的日志，并执行完毕手关闭SQL应用再次查看数据库的角色STANDBY select database_role,force_logging from v$database;DATABASE_ROLE FOR- -PRIMARY YES将该新的primary数据库加入到原来的Data Guard环境中在standby数据库执行：SQL alter session disable guard;Session altered.SQL create database link dg_recover connect to scott identified by oracle using orcl_p_tns;Database link created.注意连接用户必须有select_catalog_role的权限，可以在想要的standby数据库端赋予权限SQL grant select_catalog_role to scott;Grant succeeded.SQL alter session enable guard;Session altered.SQL select sysdate from dualdg_recover;SYSDATE-09-MAR-14将该standby数据库加入新的DG环境中去SQL alter database start logical standby apply new primary dg_recover;Database altered.要是有多个standby只需将它们添加到新的primary即可。三：逻辑DG的相关视图DBA_LOGSTDBY_EVENTS视图记录了逻辑STANDBY的操作日志，该视图默认的只记录100条信息SQL select event_time,status from dba_logstdby_events;EVENT_TIM STATUS- -08-MAR-14 ORA-16111: log mining and apply setting up08-MAR-14 ORA-16128: User initiated stop apply successfully completed08-MAR-14 ORA-16111: log mining and apply setting up08-MAR-14 ORA-16128: User initiated stop apply successfully completed08-MAR-14 ORA-16111: log mining and apply setting up08-MAR-14 ORA-16128: User initiated stop apply successfully completed08-MAR-14 ORA-16111: log mining and apply setting up08-MAR-14 ORA-16128: User initiated stop apply successfully completed08-MAR-14 ORA-16111: log mining and apply setting up08-MAR-14 ORA-16128: User initiated stop apply successfully completed08-MAR-14 ORA-16111: log mining and apply setting up修改该视图保留时间的最大条数修改前需关闭SQL应用STANDBY alter database stop logical standby apply;Database altered.STANDBY EXEC DBMS_LOGSTDBY.APPLY_SET(MAX_EVENTS_RECORDED,200);PL/SQL procedure successfully completed.启动SQL应用并查看保留条数STANDBY alter database start logical standby apply immediate;Database altered.STANDBY select * from v$logstdby_stats where name like %recorded% ;NAME VALUE- -maximum events recorded 200DBA_LOGSTDBY_LOG视图显示归档日志的SQL应用情况SQL select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,APPLIED from dba_logstdby_log; SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APPLIED- - - - 14 494521 494573 CURRENT 15 494573 545758 NO 16 545758 572067 NO上面的结果中出现应用结果为NO的情况，是因为在数据库重启期间SQL应用未开启，开启SQL应用即可SQL alter database start logical standby apply immediate;Database altered.稍等片刻之后SQL select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,APPLIED from dba_logstdby_log; SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APPLIED- - - - 14 494521 494573 CURRENT 15 494573 545758 CURRENT 16 545758 572067 NOSQL select SEQUENCE#,FIRST_CHANGE#,NEXT_CHANGE#,APPLIED from dba_logstdby_log; SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# APPLIED- - - - 16 545758 572067 CURRENT最后完成SQL应用完全完成后发现少了几组重做日志信息，是因为默认情况下stanbyd数据库SQL应用之后自动将废弃的归档文件删除，当然，也可以通过设置相关参数使之保留取消自动删除归档文件设置：SQL exec DBMS_LOGSTDBY.APPLY_SET(LOG_AUTO_DELETE, FALSE);PL/SQL procedure successfully completed.查看一下修改后的standby参数SQL select * from dba_logstdby_parameters;NAME VALUE- -PREP_DICT_RECEIVEDLMNR_SID 1GUARD_STANDBY READYFIRST_SCN