DG的一些查询和操作

miloluo

1. Check the gap

-- on physical standby, checking:
SELECT * FROM V$ARCHIVE_GAP;

-- on logical standby, checking:
-- If there are no gaps, the query will show only one file for each thread.
COLUMN FILE_NAME FORMAT a55
SELECT THREAD#, SEQUENCE#, FILE_NAME FROM DBA_LOGSTDBY_LOG L
WHERE NEXT_CHANGE# NOT IN
(SELECT FIRST_CHANGE# FROM DBA_LOGSTDBY_LOG WHERE L.THREAD# = THREAD#)
ORDER BY THREAD#,SEQUENCE#;


select sequence#,first_change#,next_change#,timestamp,applied from dba_logstdby_log;



2. Copy the missing archive log file from primary and register this log:

-- physical standby:
alter database register logfile '/physical_standby/xxx.arc';

-- logical standby:
alter database register logical logfile '/logical_standby/xxx.arc';

3. Restart the redo apply or sql apply:

-- restart redo apply, issue on physical standby database:
-- stop redo apply
alter database recover managed standby database cancel;

-- start redo apply
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


-- restart sql apply, issue on the logical standby database:
-- stop sql apply
ALTER DATABASE STOP LOGICAL STANDBY APPLY;


-- start sql apply
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;