【DB笔试面试764】在Oracle中，逻辑DG维护中常用到的SQL语句有哪些？
【DB笔试面试764】在Oracle中，逻辑DG维护中常用到的SQL语句有哪些？
♣
题目部分
在Oracle中，逻辑DG维护中常用到的SQL语句有哪些？
♣
答案部分
1.日志应用的启动和关闭
ALTER DATABASE STOP LOGICAL STANDBY APPLY; ---停止应用，等待事务完成
ALTER DATABASE ABORT LOGICAL STANDBY APPLY;--不等待事务完成就停止
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; ---实时
ALTER DATABASE START LOGICAL STANDBY APPLY; --非实时
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE SKIP FAILED TRANSACTION; --实时应用并跳过失败的事务
如何知道是否开启了实时应用呢？可以查询V$LOGSTDBY_STATE视图或查询是否有lsp进程。
SQL> SELECT * FROM V$LOGSTDBY_STATE;
PRIMARY_DBID SESSION_ID REALTIME_APPLY STATE
------------ ---------- ------------------ -----------------------------------------
1480747539 1 Y APPLYING
[oracle@rhel6_lhr oraljdg]$ ps -ef|grep -i ora_lsp
oracle 20450 1 0 15:22 ? 00:00:00 ora_lsp0_oraljdg
2.查看日志文件的应用情况
COLUMN DICT_BEGIN FORMAT A15;
COLUMN FILE_NAME FORMAT A50;
SET NUMF 9999999;
COL FCHANGE# FORMAT 9999999999999;
COL NCHANGE# FOR 999999999999999999999;
SET LINE 200
SELECT FILE_NAME, SEQUENCE# AS SEQ#, FIRST_CHANGE# AS FCHANGE#,
NEXT_CHANGE# AS NCHANGE#, TIMESTAMP, DICT_BEGIN AS BEG,
DICT_END AS END, THREAD# AS THR#, APPLIED
FROM DBA_LOGSTDBY_LOG
ORDER BY THREAD#,SEQUENCE#;
SET LINE 9999 PAGESIZE 9999
COL FILE_NAME FORMAT A120
SELECT THREAD#,SEQUENCE#, FILE_NAME, APPLIED, TIMESTAMP
FROM DBA_LOGSTDBY_LOG D
WHERE D.SEQUENCE# >=(SELECT MAX(SEQUENCE#)-3 FROM DBA_LOGSTDBY_LOG NB WHERE NB.THREAD#=D.THREAD# AND NB.APPLIED='YES' )
ORDER BY THREAD#,D.SEQUENCE#;
3.查看备库SQL Apply的进度
SQL> SELECT LATEST_SCN,MINING_SCN,APPLIED_SCN,LATEST_TIME,MINING_TIME,APPLIED_TIME FROM V$LOGSTDBY_PROGRESS;
LATEST_SCN MINING_SCN APPLIED_SCN LATEST_TIME MINING_TIME APPLIED_TIME
---------- ---------- ----------- ------------------- ------------------- -------------------
8895794846 8895316681 8895316680 2010-05-18 16:27:08 2010-05-18 16:03:54 2010-05-18 16:03:54
4.查看备库是否有任何DDL/DML语句未成功应用
COL EVENT_TIMESTAMP FORMAT A30
COL EVENT FORMAT A40
COL EVENT_STATUS FORMAT A80
SELECT A.EVENT_TIME,
A.CURRENT_SCN,
A.COMMIT_SCN,
XIDUSN,
XIDSLT,
XIDSQN,
TO_CHAR(EVENT) EVENT,
A.STATUS_CODE,
STATUS EVENT_STATUS
FROM DBA_LOGSTDBY_EVENTS A
WHERE A.EVENT_TIME >= SYSDATE - 10 / 1660
ORDER BY A.EVENT_TIME ;
5.查看备库SQL Apply的状态
COL REALTIME_APPLY FORMAT A15
COL STATE FORMAT A20
SELECT * FROM V$LOGSTDBY_STATE;
PRIMARY_DBID SESSION_ID REALTIME_APPLY STATE
------------ ---------- --------------- ---------------
262089084 1 Y APPLYING
注意STATE列，该列可能有下述的几种状态：
l INITIALIZING：LogMiner SESSION已创建并初始化
l LOADING DICTIONARY：SQL应用调用LogMiner字典
l WAITING ON GAP：SQL应用正在等待日志文件，可能有中断
l APPLYING：SQL应用正在工作
l WAITING FOR DICTIONARY LOGS：SQL应用正在等待LogMiner字典信息
l IDLE：SQL应用工作非常出色，处于空闲状态
l SQL APPLY NOT ON：没有开启应用
6.取消部分对象或事务的同步
可以利用DBMS_LOGSTDBY.SKIP存储过程跳过特定表或特定用户的DML事务或部分DDL语句。这些跳过的对象或事务可以通过视图DBA_LOGSTDBY_SKIP和DBA_LOGSTDBY_SKIP_TRANSACTION查看。
EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'VIEW');
EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'PROFILE');
EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'DATABASE LINK');
EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'CREATE VIEW');
EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'DROP VIEW');
EXECUTE DBMS_LOGSTDBY.SKIP(STMT=>'SCHEMA_DDL', SCHEMA_NAME=>'%', OBJECT_NAME=>'%', PROC_NAME=>NULL);
EXECUTE DBMS_LOGSTDBY.SKIP(STMT=>'SCHEMA_DDL', SCHEMA_NAME=>'LHR', OBJECT_NAME=>'%', PROC_NAME=>NULL);
EXECUTE DBMS_LOGSTDBY.SKIP(STMT=>'SCHEMA_DDL', SCHEMA_NAME=>'MDSYS', OBJECT_NAME=>'%', PROC_NAME=>NULL);
EXEC DBMS_LOGSTDBY.SKIP_TRANSACTION (3, 3, 827); --(XIDUSN = 3, XIDSLT = 3, XIDSQN = 827)
SELECT EVENT, STATUS,'EXEC DBMS_LOGSTDBY.SKIP_TRANSACTION ('||XIDUSN||', '||XIDSLT||', '||XIDSQN||');' FROM DBA_LOGSTDBY_EVENTS A WHERE XIDUSN IS NOT NULL AND A.EVENT_TIME >= SYSDATE - 60 / 1660;
SELECT 'EXEC DBMS_LOGSTDBY.SKIP_TRANSACTION ('||XIDUSN||', '||XIDSLT||', '||XIDSQN||');' FROM DBA_LOGSTDBY_EVENTS A WHERE XIDUSN IS NOT NULL AND A.EVENT_TIME >= SYSDATE - 10 / 1660;
SELECT * FROM DBA_LOGSTDBY_SKIP;
SELECT * FROM DBA_LOGSTDBY_SKIP_TRANSACTION;
7.增加apply进程个数
如果Apply进程过于繁忙，那么可以增加Apply进程个数。以下命令调整为20，默认为5个:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('APPLY_SERVERS',20);
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
8.处理从主库接收到的归档文件
逻辑DG在应用完归档日志后会自动删除该归档文件，这一特性是由逻辑DG中的2个参数控制的，它们分别为LOG_AUTO_DELETE和LOG_AUTO_DEL_RETENTION_TARGET。
LOG_AUTO_DELETE的值默认为TRUE，表示逻辑DG在应用完归档日志后会自动删除该归档文件，默认24小时之后删除（由参数LOG_AUTO_DEL_RETENTION_TARGET控制）。如果希望禁用自动删除的功能，那么可以执行下列语句：
EXECUTE DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', FALSE);
在告警日志中会有类似如下的记录：
Fri Jul 27 13:48:53 2018
LOGMINER: Log Auto Delete - deleting: /u01/app/oracle/flash_recovery_area/ORADGLG/1_202_886695024.dbf
Deleted file /u01/app/oracle/flash_recovery_area/ORADGLG/1_202_886695024.dbf
在某些情况下确实需要禁用归档文件的自动删除功能，例如逻辑DG需要执行Flashback Database操作，如果你想恢复到之前的某个时间点，然后再接着应用，那么就必须要有该时间点后对应的归档。假如LOG_AUTO_DELETE为TRUE的话，应用过的归档已经被删除，想回都回不去。
参数LOG_AUTO_DEL_RETENTION_TARGET表示逻辑DG在应用完归档日志后的多长时间之后再自动删除该归档文件。该参数仅在LOG_AUTO_DELETE设置为TRUE之后才起作用，默认值为1440分钟，即24小时，可以通过以下命令修改该值的大小：
exec DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DEL_RETENTION_TARGET', 1);
以上命令表示归档日志被应用完之后，再过1分钟才会自动删除该归档日志。需要注意的是，这些设置仅适用于从主库传递过来的归档文件归档到的位置不是闪回恢复区。如果正在使用闪回恢复区，那么这些从主库传递过来的归档文件将不再根据参数LOG_AUTO_DELETE和LOG_AUTO_DEL_RETENTION_TARGET的值做处理。
如果禁止了逻辑DG归档文件的自动删除功能，那么一定要有相应的其他解决方案，不能说取消了自动删除功能，之后逻辑Standby数据库接收到的Standby归档文件就不再管它，这肯定会产生问题，最起码要考虑到逻辑Standby数据库的存储空间是有限的。
逻辑Standby数据库接收到的归档文件并不会显示在V$ARCHIVED_LOG视图中，因此以为通过RMAN中的配置自动删除这些文件的希望也是会落空的。对于这类文件的删除，正确的删除方法通常会按照如下步骤操作：
首先执行DBMS_LOGSTDBY.PURGE_SESSION，该过程会检查当前所有接收到的归档日志文件，对于那些已经应用过，不再需要（这里是当前不再需求，未来是否有可能需要就得由DBA来决定了）的文件进行标记，例如：
EXECUTE DBMS_LOGSTDBY.PURGE_SESSION;
然后，查询数据字典DBA_LOGMNR_PURGED_LOG，所有被DBMS_LOGSTDBY. PURGE_SESSION标记不再需要的日志都会记录在这里，例如：
SELECT * FROM DBA_LOGMNR_PURGED_LOG;
该字典只有一列，即归档文件的实际路径。最后根据显示的路径找到这些文件，然后在操作系统中删除即可。
9.调整PREPARER（调制机）的进程数
如果备库上有很多事务在等待Apply，但是还有空闲的Applier进程，且已经没有idle状态的PREPARER（调制机）进程，这时需要增加PREPARER的进程数。以下命令调整为4个，默认为1个:
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('PREPARE_SERVERS',4);
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
10.调整MAX_SGA，防止Paged out
通过以下SQL可以查询到是否发生了Paged out：
SQL>select value bytes from v$logstdby_stats where name='bytes paged out';
如果以上查询结果在增长，那么查看当前MAX_SGA的大小:
SQL>select value from v$logstdby_stats where name like 'maximum SGA for LCR cache%';
VALUE
---------------------------------------------------------------
30
可以增大MAX_SGA:
SQL>alter database stop logical standby apply;
SQL>execute dbms_logstdby.apply_set('MAX_SGA',1000);
SQL>alter database start logical standby apply immediate;
逻辑备库需要将Redo记录解析成LCR（Logical Change Records），会在Shared Pool里分配一部分空间来作为LCR Cache，如果Cache太小，就会像OS的虚拟内存管理一样，需要做page out，这会严重影响应用日志的性能。在默认情况下，LCR Cache为Shared Pool的四分之一，最少不少于30M（默认为30M，最大可以设置到4096M），否则SQL Apply不能启动。如果机器的内存足够，建议将LCR Cache尽量设大一点，当然，同时Share Pool也要足够大。如果机器内存有限，那么可以考虑将Buffer Cache减少一点来给LCR Cache腾出空间。
11.调整事务应用方式
默认情况下逻辑Standby端事务应用顺序与Primary端提交顺序相同。如果希望逻辑Standby端的事务应用不要按照顺序的话，那么可以按照下列的步骤操作：
①停止SQL应用:
SQL>ALTER DATABASE STOP LOGICAL STANDBYAPPLY;
②允许事务不按照Primary的提交顺序应用：
SQL>EXECUTE DBMS_LOGSTDBY.APPLY_SET('PRESERVE_COMMIT_ORDER','FALSE');
③重新启动SQL应用
SQL>ALTER DATABASE START LOGICAL STANDBYAPPLY IMMEDIATE;
恢复逻辑Standby按照事务提交顺序应用的话，按照下列步骤：
①还是先停止SQL应用：
SQL>ALTER DATABASE STOP LOGICAL STANDBYAPPLY;
②重置参数PRESERVE_COMMIT_ORDER的初始值：
SQL>EXECUTE DBMS_LOGSTDBY.APPLY_UNSET('PRESERVE_COMMIT_ORDER');
③重新启动SQL应用：
SQL>ALTER DATABASE START LOGICAL STANDBYAPPLY IMMEDIATE;
逻辑备库还有很多其它非常实用的SQL语句，这里就不列举了，读者可以关注作者的微信公众号，作者每天会推送一个非常实用的SQL语句。
& 说明：
有关DBMS_LOGSTDBY包的使用可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2136495/
本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗
About Me：小麦苗
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步