System Maintenance Session

Brief

This covers the most minimal actions that need to be taken should your stand by system require maintenenace and need to be offline.

Scenarios

• Just Shutdown Primary
• Just Shutdown Standby
• Post Event Checks

Scenario: Just Shutdown Primary

There is nothing that needs to be done on the Standby if the Primary is shut down for less than a day (for example, during a maintenance window).

PreChecks

-- On STANDBY
SELECT NAME, VALUE FROM V$DATAGUARD_STATS;

-- Expect:
-- transport lag  +00 00:00:00
-- apply lag      +00 00:00:00

-- On PRIMARY
SELECT DEST_ID, STATUS, ERROR
FROM V$ARCHIVE_DEST
WHERE STATUS <> 'INACTIVE';

Before Maintenance (Primary shutdown)

On PRIMARY
-- Prevent transport errors while Primary is down
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SCOPE=BOTH;

-- Optional but clean: force last redo
ALTER SYSTEM SWITCH LOGFILE;
SHUTDOWN IMMEDIATE;

On STANDBY
-- Let MRP apply everything it has
-- (Do NOT cancel yet)

-- Optional verification
SELECT PROCESS, STATUS
FROM V$MANAGED_STANDBY
WHERE PROCESS='MRP0';

After Maintenance (Primary startup)

On PRIMARY
STARTUP;

-- Re-enable redo transport
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

On STANDBY
-- If MRP stopped automatically, restart it
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Post-Checks

-- On STANDBY
SELECT NAME, VALUE FROM V$DATAGUARD_STATS;

-- transport lag should return to 0
-- apply lag should return to 0

-- On PRIMARY
SELECT DEST_ID, STATUS, ERROR
FROM V$ARCHIVE_DEST
WHERE STATUS <> 'INACTIVE';

Scenario: Just Shutdown Standby

    Pre‑Checks
       SB: SELECT NAME, VALUE FROM V$DATAGUARD_STATS;

         NAME                             VALUE
         -------------------------------- ----------------------------------------------------------------
         transport lag                    +00 00:00:00
         apply lag                        +00 00:00:00
         apply finish time                +00 00:00:00.000
         estimated startup time           10

       PRI: SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE STATUS <> 'INACTIVE';
           DEST_ID STATUS    ERROR
        ---------- --------- -----------------------------------------------------------------
                 1 VALID
                 2 VALID

     Before Maintenance 
        On STANDBY
          ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
          SHUTDOWN IMMEDIATE;
        On PRIMARY
          ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SCOPE=BOTH;
     - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
     After Maintenance 
        On STANDBY
          STARTUP MOUNT;
          ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
        On PRIMARY
          ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
    Post‑Checks
       SB:  SELECT NAME, VALUE FROM V$DATAGUARD_STATS;
       PRI: SELECT DEST_ID, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE STATUS <> 'INACTIVE';

Post Event Checks

-- MRP / RFS status (MRP0 must be APPLYING_LOG)
SET LINES 200
SELECT process, status, thread#, sequence#
FROM   v$managed_standby
ORDER  BY process;

-- Archive gap (must return no rows)
SELECT * FROM v$archive_gap;

-- Last received vs applied (should match or be very close)
SET LINES 200
SELECT thread#,
       MAX(CASE WHEN applied = 'NO'  THEN sequence# END) AS last_received,
       MAX(CASE WHEN applied = 'YES' THEN sequence# END) AS last_applied
FROM   v$archived_log
GROUP  BY thread#
ORDER  BY thread#;

-- Real-time apply check (best quick health indicator)
SELECT thread#,
       MAX(sequence#) KEEP (DENSE_RANK LAST ORDER BY first_time) AS last_seq,
       MAX(applied)   KEEP (DENSE_RANK LAST ORDER BY first_time) AS applied
FROM   v$archived_log
GROUP  BY thread#;