1200字范文 > 【DB笔试面试764】在Oracle中逻辑DG维护中常用到的SQL语句有哪些？

【DB笔试面试764】在Oracle中逻辑DG维护中常用到的SQL语句有哪些？

时间：2018-11-06 02:08:39

♣

题目部分

在Oracle中，逻辑DG维护中常用到的SQL语句有哪些？

♣

答案部分

1.日志应用的启动和关闭

1ALTERDATABASESTOPLOGICALSTANDBYAPPLY;---停止应用，等待事务完成2ALTERDATABASEABORTLOGICALSTANDBYAPPLY;--不等待事务完成就停止3ALTERDATABASESTARTLOGICALSTANDBYAPPLYIMMEDIATE;---实时4ALTERDATABASESTARTLOGICALSTANDBYAPPLY;--非实时5ALTERDATABASESTARTLOGICALSTANDBYAPPLYIMMEDIATESKIPFAILEDTRANSACTION;--实时应用并跳过失败的事务

如何知道是否开启了实时应用呢？可以查询V$LOGSTDBY_STATE视图或查询是否有lsp进程。

1SQL>SELECT*FROMV$LOGSTDBY_STATE;2PRIMARY_DBIDSESSION_IDREALTIME_APPLYSTATE3---------------------------------------------------------------------------------414807475391YAPPLYING5[oracle@rhel6_lhroraljdg]$ps-ef|grep-iora_lsp6oracle204501015:22?00:00:00ora_lsp0_oraljdg

2.查看日志文件的应用情况

1COLUMNDICT_BEGINFORMATA15;2COLUMNFILE_NAMEFORMATA50;3SETNUMF9999999;4COLFCHANGE#FORMAT9999999999999;5COLNCHANGE#FOR999999999999999999999;6SETLINESELECTFILE_NAME,SEQUENCE#ASSEQ#,FIRST_CHANGE#ASFCHANGE#,8NEXT_CHANGE#ASNCHANGE#,TIMESTAMP,DICT_BEGINASBEG,9DICT_ENDASEND,THREAD#ASTHR#,APPLIED10FROMDBA_LOGSTDBY_LOG11ORDERBYTHREAD#,SEQUENCE#;1213SETLINE9999PAGESIZE999914COLFILE_NAMEFORMATA1SELECTTHREAD#,SEQUENCE#,FILE_NAME,APPLIED,TIMESTAMP16FROMDBA_LOGSTDBY_LOGD17WHERED.SEQUENCE#>=(SELECTMAX(SEQUENCE#)-3FROMDBA_LOGSTDBY_LOGNBWHERENB.THREAD#=D.THREAD#ANDNB.APPLIED='YES')18ORDERBYTHREAD#,D.SEQUENCE#;

3.查看备库SQL Apply的进度

1SQL>SELECTLATEST_SCN,MINING_SCN,APPLIED_SCN,LATEST_TIME,MINING_TIME,APPLIED_TIMEFROMV$LOGSTDBY_PROGRESS;2LATEST_SCNMINING_SCNAPPLIED_SCNLATEST_TIMEMINING_TIMEAPPLIED_TIME3----------------------------------------------------------------------------------------4889579484688953166818895316680-05-1816:27:08-05-1816:03:54-05-1816:03:54

4.查看备库是否有任何DDL/DML语句未成功应用

1COLEVENT_TIMESTAMPFORMATA302COLEVENTFORMATA403COLEVENT_STATUSFORMATA804SELECTA.EVENT_TIME,5A.CURRENT_SCN,MIT_SCN,7XIDUSN,8XIDSLT,9XIDSQN,10TO_CHAR(EVENT)EVENT,11A.STATUS_CODE,12STATUSEVENT_STATUS13FROMDBA_LOGSTDBY_EVENTSA14WHEREA.EVENT_TIME>=SYSDATE-10/166015ORDERBYA.EVENT_TIME;

5.查看备库SQL Apply的状态

1COLREALTIME_APPLYFORMATA152COLSTATEFORMATA203SELECT*FROMV$LOGSTDBY_STATE;4PRIMARY_DBIDSESSION_IDREALTIME_APPLYSTATE5----------------------------------------------------62620890841YAPPLYING

注意STATE列，该列可能有下述的几种状态：

lINITIALIZING：LogMiner SESSION已创建并初始化

lLOADING DICTIONARY：SQL应用调用LogMiner字典

lWAITING ON GAP：SQL应用正在等待日志文件，可能有中断

lAPPLYING：SQL应用正在工作

lWAITING FOR DICTIONARY LOGS：SQL应用正在等待LogMiner字典信息

lIDLE：SQL应用工作非常出色，处于空闲状态

lSQL APPLY NOT ON：没有开启应用

6.取消部分对象或事务的同步

可以利用DBMS_LOGSTDBY.SKIP存储过程跳过特定表或特定用户的DML事务或部分DDL语句。这些跳过的对象或事务可以通过视图DBA_LOGSTDBY_SKIP和DBA_LOGSTDBY_SKIP_TRANSACTION查看。

1EXECUTEDBMS_LOGSTDBY.SKIP(STMT=>'VIEW');2EXECUTEDBMS_LOGSTDBY.SKIP(STMT=>'PROFILE');3EXECUTEDBMS_LOGSTDBY.SKIP(STMT=>'DATABASELINK');4EXECUTEDBMS_LOGSTDBY.SKIP(STMT=>'CREATEVIEW');5EXECUTEDBMS_LOGSTDBY.SKIP(STMT=>'DROPVIEW');6EXECUTEDBMS_LOGSTDBY.SKIP(STMT=>'SCHEMA_DDL',SCHEMA_NAME=>'%',OBJECT_NAME=>'%',PROC_NAME=>NULL);7EXECUTEDBMS_LOGSTDBY.SKIP(STMT=>'SCHEMA_DDL',SCHEMA_NAME=>'LHR',OBJECT_NAME=>'%',PROC_NAME=>NULL);8EXECUTEDBMS_LOGSTDBY.SKIP(STMT=>'SCHEMA_DDL',SCHEMA_NAME=>'MDSYS',OBJECT_NAME=>'%',PROC_NAME=>NULL);910EXECDBMS_LOGSTDBY.SKIP_TRANSACTION(3,3,827);--(XIDUSN=3,XIDSLT=3,XIDSQN=827)11SELECTEVENT,STATUS,'EXECDBMS_LOGSTDBY.SKIP_TRANSACTION('||XIDUSN||','||XIDSLT||','||XIDSQN||');'FROMDBA_LOGSTDBY_EVENTSAWHEREXIDUSNISNOTNULLANDA.EVENT_TIME>=SYSDATE-60/1660;12SELECT'EXECDBMS_LOGSTDBY.SKIP_TRANSACTION('||XIDUSN||','||XIDSLT||','||XIDSQN||');'FROMDBA_LOGSTDBY_EVENTSAWHEREXIDUSNISNOTNULLANDA.EVENT_TIME>=SYSDATE-10/1660;1314SELECT*FROMDBA_LOGSTDBY_SKIP;15SELECT*FROMDBA_LOGSTDBY_SKIP_TRANSACTION;

7.增加apply进程个数

如果Apply进程过于繁忙，那么可以增加Apply进程个数。以下命令调整为20，默认为5个:

1SQL>ALTERDATABASESTOPLOGICALSTANDBYAPPLY;2SQL>EXECUTEDBMS_LOGSTDBY.APPLY_SET('APPLY_SERVERS',20);3SQL>ALTERDATABASESTARTLOGICALSTANDBYAPPLYIMMEDIATE;

8.处理从主库接收到的归档文件

逻辑DG在应用完归档日志后会自动删除该归档文件，这一特性是由逻辑DG中的2个参数控制的，它们分别为LOG_AUTO_DELETE和LOG_AUTO_DEL_RETENTION_TARGET。

LOG_AUTO_DELETE的值默认为TRUE，表示逻辑DG在应用完归档日志后会自动删除该归档文件，默认24小时之后删除（由参数LOG_AUTO_DEL_RETENTION_TARGET控制）。如果希望禁用自动删除的功能，那么可以执行下列语句：

1EXECUTEDBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE',FALSE);

在告警日志中会有类似如下的记录：

1FriJul2713:48:532LOGMINER:LogAutoDelete-deleting:/u01/app/oracle/flash_recovery_area/ORADGLG/1_202_886695024.dbf3Deletedfile/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小时，可以通过以下命令修改该值的大小：

1execDBMS_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来决定了）的文件进行标记，例如：

1EXECUTEDBMS_LOGSTDBY.PURGE_SESSION;

然后，查询数据字典DBA_LOGMNR_PURGED_LOG，所有被DBMS_LOGSTDBY. PURGE_SESSION标记不再需要的日志都会记录在这里，例如：

1SELECT*FROMDBA_LOGMNR_PURGED_LOG;

该字典只有一列，即归档文件的实际路径。最后根据显示的路径找到这些文件，然后在操作系统中删除即可。

9.调整PREPARER（调制机）的进程数

如果备库上有很多事务在等待Apply，但是还有空闲的Applier进程，且已经没有idle状态的PREPARER（调制机）进程，这时需要增加PREPARER的进程数。以下命令调整为4个，默认为1个:

1SQL>ALTERDATABASESTOPLOGICALSTANDBYAPPLY;2SQL>EXECUTEDBMS_LOGSTDBY.APPLY_SET('PREPARE_SERVERS',4);3SQL>ALTERDATABASESTARTLOGICALSTANDBYAPPLYIMMEDIATE;

10.调整MAX_SGA，防止Paged out

通过以下SQL可以查询到是否发生了Paged out：

1SQL>selectvaluebytesfromv$logstdby_statswherename='bytespagedout';

如果以上查询结果在增长，那么查看当前MAX_SGA的大小:

1SQL>selectvaluefromv$logstdby_statswherenamelike'maximumSGAforLCRcache%';2VALUE3---------------------------------------------------------------430

可以增大MAX_SGA:

1SQL>alterdatabasestoplogicalstandbyapply;2SQL>executedbms_logstdby.apply_set('MAX_SGA',1000);3SQL>alterdatabasestartlogicalstandbyapplyimmediate;

逻辑备库需要将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应用:

1SQL>ALTERDATABASESTOPLOGICALSTANDBYAPPLY;

②允许事务不按照Primary的提交顺序应用：

1SQL>EXECUTEDBMS_LOGSTDBY.APPLY_SET('PRESERVE_COMMIT_ORDER','FALSE');

③重新启动SQL应用

1SQL>ALTERDATABASESTARTLOGICALSTANDBYAPPLYIMMEDIATE;

恢复逻辑Standby按照事务提交顺序应用的话，按照下列步骤：

①还是先停止SQL应用：

1SQL>ALTERDATABASESTOPLOGICALSTANDBYAPPLY;

②重置参数PRESERVE_COMMIT_ORDER的初始值：