逻辑备库运维相关的脚本

前几天逻辑备库有问题了，在网上搜了一下，就没找到几个逻辑备库相关的运维的各种脚本，现在整理一个出来

##############################       常用信息的查询      ##############################--涉及SQL应用的不同进程的当前状态的相关信息select * from v$logstdby_process;select * from v$logstdby_progress;select * from v$logstdby;select * from v$logstdby_transaction;select sid ,serial#, spid , 'kill -9'||spid ||';' from v$logstdby_process;查看逻辑standby的相关参数select name param_name, value param_value from dba_logstdby_parameters;ALTER DATABASE STOP LOGICAL STANDBY APPLY;EXECUTE DBMS_LOGSTDBY.APPLY_SET('APPLY_SERVERS',20);ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;--SQL应用的当前状态的概要select * from v$logstdby_state;--在SQL应用操作期间发生的感兴趣的事件(如果SQL 应用应该未预料地停止，问题的原因也记录在该视图中)select * from dba_logstdby_events order by event_time desc;--默认列出最近的100个事件，可以改成保留10000个事件--execute dbms_logstdby.apply_set ('MAX_EVENTS_RECORDED','10000');--被SQL应用处理的归档日志的动态相关信息select * from dba_logstdby_log;--逻辑备数据库的故障转移特性的相关信息，包括：--到故障转移的时间（apply finish time）--在逻辑备数据库中已提交的数据有多新（lag time）--在灾难发生的情况下可能会丢失什么数据（potential data loss）select * from v$logstdby_stats;--查看默认不同步的用户下的对象select owner,name from DBA_LOGSTDBY_SKIP;--skipselect owner,NAME  from dba_logstdby_skip WHERE statement_opt='SCHEMA_DDL'  ORDER BY 1,2;select owner,NAME  from dba_logstdby_skip WHERE statement_opt='DML'  ORDER BY 1,2;select * from dba_logstdby_skip_transaction;##############################                skip and unskip               ##############################--skip事务--A.通过包--1.通过dba_logstdby_events视图找到事务的3个SN(XIDUSN,XIDSLT,XIDSQN)select * from dba_logstdby_events order by event_time desc;--2.停止sql apply(可能已经自动停掉了)alter database stop logical standby apply;--3.通过DBMS_LOGSTDBY.SKIP_TRANSACTION包skip掉该事务--exec dbms_logstdby.skip_transaction (XIDUSN =>139 , XIDSLT =>22 , XIDSQN =>32374 );exec dbms_logstdby.skip_transaction (8 , 14 , 3049627 ); --要写成这样EXEC dbms_logstdby.skip_transaction (--4.开启sql applyalter database start logical standby apply immediate;--恢复ddl或者dml--查看skip的相关信息select owner,NAME  from dba_logstdby_skip WHERE statement_opt='SCHEMA_DDL'  ORDER BY 1,2;select owner,NAME  from dba_logstdby_skip WHERE statement_opt='DML'  ORDER BY 1,2;--1.停止sql apply(可能已经自动停掉了)alter database stop logical standby apply;--2.通过DBMS_LOGSTDBY.SKIP_TRANSACTION包skip和unskipexec dbms_logstdby.unskip (stmt => 'SCHEMA_DDL',schema_name => 'CPS',object_name => 'T_TMC_AT_DATA_REQUEST'); --多执行三遍exec dbms_logstdby.unskip (stmt => 'DML',schema_name => 'CPS',object_name => 'T_TMC_AT_DATA_REQUEST'); --多执行三遍exec dbms_logstdby.skip (stmt => 'SCHEMA_DDL',schema_name => 'CPS',object_name => 'T_TMC_AT_DATA_REQUEST'); --多执行三遍exec dbms_logstdby.skip (stmt => 'DML',schema_name => 'CPS',object_name => 'T_TMC_AT_DATA_REQUEST'); --多执行三遍--3.开启sql applyalter database start logical standby apply immediate;DML： update 、insert 、delete SCHEMA_DDL：create、alter、drop、truncate、rename 、COMMENT  表名一定要大写！！--其他视图select * from dba_logstdby_progress;select * from dba_logstdby_history;select * from dba_logstdby_not_unique;select * from dba_logstdby_parameters;select * from dba_logstdby_unsupported;##############################    sql应用的启动和关闭    ##############################--开始sql应用alter database start logical standby apply immediate;--停止sql应用alter database stop logical standby apply;--alter database abort logical standby apply--等待 "kksfbc child completion" 导致复制异常等待处理，Oracle之BugALTER DATABASE ABORT LOGICAL STANDBY APPLY  --停掉复制进程alter database start logical standby apply immediate; --开启复制进程##############################       常见问题处理        ##############################--日志卡着不动--1.查看RFS进程有没有问题select * from v$managed_standby--2.检查日志传输是否打开，若没有打开，修改ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE scope = both;--3.查看卡在哪个日志上了select * from v$logstdby_process;--4.从主库上往备库上推日志scp /u02/archive/billdb/Arc_3_29152_592235202.arc oracle@192.168.1.102:/u02/stdlog/--5.注册日志文件alter database register or replace logical logfile '/u02/stdlog/vposdb/Arc_1_76561_676043623.arc';--ORA-01291: missing logfile--1.在主库上重新生成一份数据字典EXECUTE DBMS_LOGSTDBY.BUILD;--2.切换日志alter system switch logfile;--一切正常但延时严重select a.sid, a.status, c.sql_text  from v$logstdby_process a, v$session b, v$sqlarea c where a.sid = b.sid   and b.sql_id = c.sql_id;--在DATAGUARD中手工处理日志GAP的方法1.在备库检查是否有日志缺失select * from V$ARCHIVE_GAP;2.在主库中查询缺失的日志的所在路径和名称SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 99 AND 109;3.把日志拷贝到备库上4.在备库上手工注册上一步中从主库拷贝来的日志alter database register or replace logical logfile '/u02/stdlog/Arc_1_31085_592235202.arc';5.稍等片刻，观察备库的alert日志信息6.检查备库是否还有日志GAP，如果有记录重复以上步骤，直到没有结果显示。select * from V$ARCHIVE_GAP