-- Enable Managed recovery mode at STANDBY side and check archive logs.
-- STANDBY:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
-- CHECK ROLE:
-- PRIMARY:
SELECT name,database_role,controlfile_type,open_mode FROM V$DATABASE;
NAME DATABASE_ROLE CONTROLFILE_TYPE OPEN_MODE
--------------------- ------------------ -------------------- ----------
ORCL PRIMARY CURRENT READ WRITE
-- STANDBY:
SELECT name,database_role,controlfile_type,open_mode FROM V$DATABASE;
NAME DATABASE_ROLE CONTROLFILE_TYPE OPEN_MODE
--------------------- ------------------ -------------------- ----------
ORCL PHYSICAL STANDBY STANDBY MOUNTED
-- Check max archive log sequence
-- PRIMARY:
SELECT max(sequence#) FROM V$thread;
MAX(SEQUENCE#)
--------------
93
-- STANDBY:
SELECT max(sequence#) FROM V$thread;
MAX(SEQUENCE#)
--------------
93
-- Switch logfile in PRIMARY database :
ALTER SYSTEM SWITCH LOGFILE;
-- Check sequence from STANDBY database :
SELECT max(sequence#) FROM V$thread;
MAX(SEQUENCE#)
--------------
94
-- Check archive log files
-- PRIMARY:
ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oradata/production/arch
Oldest online log sequence 251
Next log sequence to archive 252
Current log sequence 252
-- PRIMARY:
SELECT sequence#, first_time, next_time, applied FROM V$archived_log ORDER BY next_time;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
____________ _____________ ____________ __________
01 06-SEP-23 06-SEP-23 NO
02 06-SEP-23 08-SEP-23 NO
03 08-SEP-23 09-SEP-23 NO
04 09-SEP-23 10-SEP-23 NO
05 10-SEP-23 11-SEP-23 NO
06 11-SEP-23 11-SEP-23 NO
07 11-SEP-23 11-SEP-23 NO
08 11-SEP-23 11-SEP-23 NO
09 11-SEP-23 11-SEP-23 NO
10 11-SEP-23 11-SEP-23 NO
11 11-SEP-23 11-SEP-23 NO
-- STANDBY:
SELECT sequence#, first_time, next_time, applied FROM V$archived_log WHERE applied='YES' ORDER BY next_time;
SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
____________ ______________________ ______________________ __________
8 2023-09-11 15:39:46 2023-09-11 17:17:30 YES
9 2023-09-11 17:17:30 2023-09-11 18:54:40 YES
-- STANDBY:
SELECT name,applied FROM V$archived_log;
NAME APPLIED
---------------------------------------- ---------
/u01/oradata/production/arch/1_250_976404635.dbf YES
/u01/oradata/production/arch/1_249_976404635.dbf YES
/u01/oradata/production/arch/1_251_976404635.dbf YES
-- PRIMARY: verifica ultimo archive destinado ao STANDBY e que foi aplicado
SELECT thread#, max(completion_time), max(sequence#)
FROM v$archived_log
WHERE STANDBY_dest = 'YES' AND applied = 'YES'
GROUP BY thread#;
-- STANDBY:
SELECT thread#,max(sequence#) FROM V$archived_log WHERE applied='YES' GROUP BY thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 9
-- STANDBY:
SELECT registrar, creator, thread#, sequence#, first_change#, next_change# FROM V$archived_log;
REGISTRAR CREATOR THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
____________ __________ __________ ____________ ________________ ________________
RFS ARCH 1 8 1977707952752 1977707977407
RFS LGWR 1 9 1977707977407 1977707996615
-- Check the current log sequence on the PRIMARY database:
-- STANDBY:
SELECT thread#, max(sequence#) "Last PRIMARY Seq Generated" FROM v$archived_log val, v$database vdb WHERE val.resetlogs_change# = vdb.resetlogs_change# GROUP BY thread# ORDER BY 1;
THREAD# Last PRIMARY Seq Generated
__________ _____________________________
1 11
OR
ARCHIVE LOG LIST;
--PRIMARY | STANDBY:
SELECT dest_id,error FROM V$ARCHIVE_DEST;
-- VERIFICAR: Atraso, Transporte, Aplicacao, Tempo estimado em caso de transicao para PRIMARY
-- STANDBY:
SELECT * FROM V$DATAGUARD_STATS;
SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS WHERE name like '%lag';
NAME VALUE DATUM_TIME TIME_COMPUTED
________________ _______________ ______________________ ______________________
transport lag +00 00:00:00 09/12/2023 17:06:53 09/12/2023 17:07:01
apply lag +00 00:00:00 09/12/2023 17:06:53 09/12/2023 17:07:01
-- PRIMARY | STANDBY: Detalhes das operacoes de transporte e aplicacao de redo
SELECT * FROM v$dataguard_status order by timestamp desc;
-- STANDBY:Convert physical STANDBY into active STANDBY database:
-- STANDBY:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN;
SELECT name,database_role,controlfile_type,open_mode FROM V$DATABASE.
NAME DATABASE_ROLE CONTROLFILE_TYPE OPEN_MODE
-------------- ----------------- ----------------- -------------------------------
ORCL PHYSICAL STANDBY STANDBY READ ONLY WITH APPLY
-- TO VERIFY RECOVER PROCESS ON STANDBY:
-- PRIMARY
SELECT thread#, sequence# FROM V$LOG WHERE status='CURRENT';
THREAD# SEQUENCE#
__________ ____________
1 12
-- STANDBY
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;
SELECT thread#, sequence#, status FROM V$MANAGED_STANDBY WHERE process='MRP0';
THREAD# SEQUENCE# STATUS
__________ ____________ _______________
1 12 APPLYING_LOG
-- Check applied log on STANDBY
-- STANDBY
SELECT thread#, max(sequence#) "Last STANDBY Seq Applied"
FROM v$archived_log val, v$database vdb
WHERE val.resetlogs_change# = vdb.resetlogs_change# AND val.applied IN ('YES','IN-MEMORY')
GROUP BY thread# ORDER BY 1;
THREAD# Last STANDBY Seq Applied
__________ ___________________________
1 9
-- Identify the missing archive log file - archive gap
-- PRIMARY | STANDBY
SELECT * FROM v$archive_gap;
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
-- Check redo RECEIVED AND APPLIED ON STANDBY.
-- STANDBY
SELECT ARCH.THREAD# "Thread",
ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
_________ _________________________ ________________________ _____________
1 9 11 -2
-- PRIMARY
SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;
Thread Last Sequence Generated
_________ __________________________
1 11
-- PRIMARY
SELECT distinct SEQUENCE# "Last Sequence Generated", THREAD# "Thread"
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;
-- PRIMARY
SELECT thread#, max(sequence#) "Last PRIMARY Seq Generated"
FROM v$archived_log val, v$database vdb
WHERE val.resetlogs_change# = vdb.resetlogs_change#
GROUP BY thread# ORDER BY 1;
-- STANDBY
SELECT thread#, max(sequence#) "Last STANDBY Seq Applied"
FROM v$archived_log val, v$database vdb
WHERE val.resetlogs_change# = vdb.resetlogs_change#
AND val.applied IN ('YES','IN-MEMORY')
GROUP BY thread# ORDER BY 1;
THREAD# Last STANDBY Seq Applied
__________ ___________________________
1 9
-- PRIMARY: Ultimo sequence# aplicado para cada thread
SELECT max(sequence#) as last_sequence, thread#
FROM v$archived_log
WHERE resetlogs_change# = (SELECT max(resetlogs_change#) FROM v$archived_log)
GROUP BY thread#;
-- PRIMARY: Verifica se algum archived log esta faltando em um destino especifico (Assumindo que local archive esta em dest_id=1 e STANDBY esta em dest_id=2)
SELECT thread#, sequence#
FROM v$archived_log prim
WHERE
resetlogs_change# = (SELECT resetlogs_change# FROM v$database)
AND dest_id = 1
AND sequence# not IN (SELECT sequence# FROM v$archived_log WHERE dest_id = 2 AND thread# = prim.thread# AND resetlogs_change# = prim.resetlogs_change#)
AND sequence# >= (SELECT max(sequence#) FROM v$archived_log WHERE dest_id = 2 AND thread# = prim.thread# AND resetlogs_change# = prim.resetlogs_change#);
-- PRIMARY: verifica se archives destinados ao STANDBY ja foram aplicados
SELECT thread#, sequence#, completion_time, STANDBY_dest, archived, applied
FROM v$archived_log WHERE STANDBY_dest = 'YES'
ORDER BY completion_time desc;
-- STANDBY
SELECT LOG_ARCHIVED-LOG_APPLIED "LOG_GAP"
FROM (SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'),(SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES');
-- Check received log on STANDBY
-- STANDBY
SELECT thread#, max(sequence#) "Last STANDBY Seq Received" FROM v$archived_log val, v$database vdb WHERE val.resetlogs_change# = vdb.resetlogs_change# GROUP BY thread# ORDER BY 1;
THREAD# Last STANDBY Seq Received
__________ ____________________________
1 9
-- Check for GAP on STANDBY
-- STANDBY
SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;
Thread Last Sequence Generated
_________ __________________________
1 9
-- Copy missing archive log file
-- PRIMARY
SELECT name FROM v$archived_log WHERE thread# = 1 AND dest_id = 1 AND sequence# BETWEEN XXX AND YYY;
NAME
_______________________________________
/oraarch/ORCL_1_1_1146845631.arc
-- Register archive logfile with STANDBY.
-- STANDBY
ALTER DATABASE REGISTER LOGFILE ‘/oraarch/ORCL_1_1_1146845631.arc’;
-- Restart the managed recovery operations.
-- STANDBY
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
-- Displays runtime and configuration information for the archived redo log destinations.
-- PRIMARY | STANDBY
SELECT archived_thread#, archived_seq#, applied_thread#, applied_seq# FROM V$archive_dest_status;
-- PRIMARY: verifica e estado do transporte para o STANDBY
SELECT d.dest_id,d.dest_name,d.status,d.transmit_mode,d.affirm,d.reopen_secs,d.delay_mins,d.net_timeout,s.type,s.recovery_mode,s.synchronized,s.protection_mode,s.synchronization_status
FROM v$archive_dest d
inner join v$archive_dest_status s on d.dest_id = s.dest_id
WHERE d.dest_name = 'LOG_ARCHIVE_DEST_2';
-- PRIMARY: Sequence# de archive mais recente em cada destino (Sequence# deve ser o mesmo em todos os destinos, caso contrario um deles possivelmente estara com algum erro)
SELECT destination, status, archived_thread#, archived_seq#
FROM v$archive_dest_status
WHERE status <> 'DEFERRED' AND status <> 'INACTIVE';
-- PRIMARY | STANDBY: configuracoes e Status
SELECT * FROM v$dataguard_config;
-- PRIMARY
SELECT current_scn FROM v$database;
CURRENT_SCN
________________
1977708627918
-- STANDBY
SELECT current_scn FROM v$database;
CURRENT_SCN
________________
1977708627919
-- Displays messages recently written to the alert log
-- STANDBY
SELECT message,timestamp FROM V$DATAGUARD_STATUS WHERE timestamp > sysdate - 1/6;
SELECT message FROM V$DATAGUARD_STATUS WHERE dest_id = 2;
MESSAGE TIMESTAMP
------------------------------------------------------------------------------------------
RFS[48]: No STANDBY redo logfiles created 05-AUG-15
Media Recovery Log /uv1010/arch/MRSX/arch_MRSX_779539386_1_49482.log 05-AUG-15
Media Recovery Waiting for thread 1 sequence 49483 (in transit) 05-AUG-15
-- =======================================================================
-- Find LAG in DATA GUARD with ORACLE RAC :
-- =======================================================================
set echo on feed on term on
set linesize 120
col PRIMARY_TIME format a20
col STANDBY_COMPLETION_TIME format a23
SELECT
prim.thread# thread,
prim.seq PRIMARY_seq,
to_char(prim.tm, 'DD-MON-YYYY HH24:MI:SS') PRIMARY_time,
tgt.thread# STANDBY_thread,
tgt.seq STANDBY_seq,
to_char(tgt.tm, 'DD-MON-YYYY HH24:MI:SS') STANDBY_completion_time,
prim.seq - tgt.seq seq_gap,
( prim.tm - tgt.tm ) * 24 * 60 lag_minutes
FROM
(SELECT thread#, MAX(sequence#) seq, MAX(completion_time) tm FROM v$archived_log GROUP BY thread# ) prim,
(SELECT thread#, MAX(sequence#) seq, MAX(completion_time) tm FROM v$archived_log WHERE dest_id IN (SELECT dest_id FROM v$archive_dest WHERE target = 'STANDBY' )
AND applied = 'YES' GROUP BY thread# ) tgt
WHERE prim.thread# = tgt.thread#;
