ORACLE DG 日常維護常用SQL

-- 啟用DG日誌應用
alter database start logical standby apply immediate;
-- 停止DG日誌應用
alter database stop logical standby apply; 
-- 跳過出錯的 事務
 select * from  DBA_LOGSTDBY_EVENTS t order by t.event_time desc;
 begin dbms_logstdby.skip_transaction(70,15,721998);end;

-- 指定的表不同步
BEGIN dbms_logstdby.skip(stmt => 'DML',schema_name => 'OWNER_XXX',object_name => 'TABLENAMEXXX') ;END;
-- 取消指定表的不同步
BEGIN dbms_logstdby.unskip(stmt => 'DML',schema_name => 'OWNER_XXX',object_name => 'TABLENAMEXXX') ;END;

-- 重新同步指定的表
begin dbms_logstdby.instantiate_table(schema_name => 'OWNER_XXX',table_name => 'TABLENAMEXXX',dblink => 'RACLINK');end;

--   重新同 步指定的表的整個流程 （需要先停止日誌應用）
BEGIN
   dbms_logstdby.skip(stmt => 'DML',schema_name => 'OWNER_XXX',object_name => 'MKT_KPI_INDEX_QUARTER') ;
   dbms_logstdby.instantiate_table(schema_name => 'OWNER_XXX',table_name => 'MKT_KPI_INDEX_QUARTER',dblink => 'RACLINK');
   dbms_logstdby.unskip(stmt => 'DML',schema_name => 'OWNER_XXX',object_name => 'MKT_KPI_INDEX_QUARTER') ;
end;
/

-- 常用的DG 檢視
 select * from  DBA_LOGSTDBY_EVENTS t order by t.event_time desc;
select  t.applied,t.* from   gv$archived_log t order by t.first_time desc;
 select t.applied,t.* from dba_logstdby_log t order by t.first_time desc;
 select * from V$DATAGUARD_STATS ;
 select * from V$LOGSTDBY_PROCESS ;
 select * from V$LOGSTDBY_PROGRESS ;
 select * from V$LOGSTDBY;
 select * from V$LOGSTDBY_STATE ;
 select * from V$LOGSTDBY_STATS ;

--邏輯備用資料庫中SQL應用操作的進展。
select * from dba_logstdby_progress;
 select * from dba_logstdby_skip ;
 select * from dba_logstdby_history;
 select * from dba_logstdby_unsupported;
 select * from dba_logstdby_not_unique;
 select * from dba_logstdby_skip_transaction;
 select * from dba_logmnr_log ;
 select * from dba_logmnr_purged_log;
 select * from dba_logmnr_session;


邏輯dg引數：
select * from dba_logstdby_parameters;
注意這兩個引數：LOG_AUTO_DELETE、LOG_AUTO_DEL_RETENTION_TARGET
exec DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DEL_RETENTION_TARGET', 720);

DATAGUARD狀態：
select guard_status from gv$database;
-- 改變DG狀態
alter database guard standby;
--  DG狀態說明
在standby或all狀態下，所有非sys使用者，都無法對logical standby的資料進行修改，
關於alter database guard [standby|all|none] 幾個引數的解釋如下：
ALTER DATABASE GUARD ALL - prevents users making any changes - DEFAULT
ALTER DATABASE GUARD STANDBY - prevents users making changes to data maintained by data guard sql apply，即對DBMS_LOGSTDBY.SKIP 跳過的表可進行DML操作，而其他需要SQL 

APPLY的物件都不能進行變更操作。
ALTER DATABASE GUARD NONE - normal security