Monday, September 22, 2008

Logical Standby Management

# Override Guard protection
ALTER SESSION DISABLE GUARD


# ALTER DATABASE GUARD
ALL - everything
STANDBY - replicated from primary
NONE - nothing


# Change the commit order (faster performance, no read consistency)

exec dbms_logstdby.apply_set('PRESERVE_COMMIT_ORDER', 'FALSE');

# Skip DML on a table

ALTER DATABASE STOP LOGICAL STANDBY APPLY;
EXECUTE DBMS_LOGSTDBY.SKIP ('DML', 'ROBERT', 'TABLE1', null);
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

EXECUTE DBMS_LOGSTDBY.UNSKIP ('DML', 'ROBERT', 'TABLE1');
# Skip a transaction

SELECT XIDUSN, XIDSLT, XIDSQN FROM DBA_LOGSTDBY_EVENTS WHERE EVENT_TIME = (SELECT MAX(EVENT_TIME) FROM DBA_LOGSTDBY_EVENTS);
exec dbms_logstdby.skip_transaction(XIDUSN => 1, XIDSLT => 10, XIDSQN => 1000);

# Instantiate a table using a database link

EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE (SCHEMA_NAME => 'ROBERT', TABLE_NAME => 'TABLE2', DBLINK => 'A_LINK');

# Tables for Diagnostics

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# FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS ORDER BY EVENT_TIMESTAMP, COMMIT_SCN;

SELECT APPLIED_SCN, LATEST_SCN, MINING_SCN, RESTART_SCN FROM V$LOGSTDBY_PROGRESS;

SELECT SID, SERIAL#, LOGSTDBY_ID AS LID, SPID, TYPE, HIGH_SCN FROM V$LOGSTDBY_PROCESS;

SELECT TYPE, STATUS_CODE, STATUS FROM V$LOGSTDBY_PROCESS;

SELECT * FROM V$LOGSTDBY_STATE;

SELECT NAME, VALUE, TIME_COMPUTED FROM V$LOGSTDBY_STATS;

v$LOGSTDBY

# What Sql is being applied ?

SELECT SAS.SERVER_ID, SS.OWNER, SS.OBJECT_NAME, SS.STATISTIC_NAME, SS.VALUE
FROM V$SEGMENT_STATISTICS SS
, V$LOCK L
, V$STREAMS_APPLY_SERVER SAS
WHERE SAS.SERVER_ID = &SLAVE_ID
AND L.SID = SAS.SID
AND L.TYPE = 'TM'
AND SS.OBJ# = L.ID1;