Tuesday, April 1, 2025

Recover Standby using service

   No comments
  • Introduction: From 12c onwards, Oracle provides  a new commands RECOVER FROM SERVICE for make the database sync if standby database is out of sync by reducing the steps.

  • Prerequisites:
    • Oracle Data Guard should be configured.
    • Disable DG broker if any as it will start MRP automatically.
    • Oracle version should be 12c or later.
  • Environment:

Server Primary Standby
Hostname Source Target
IP 192.168.80.51 192.168.80.111
OS OEL 9 OEL 9
SID ORCLDC ORCLDR
Service Name ORCLDC ORCLDR

  • Now proceed for recovery activity step by step:

    • 1. Check difference of SCN at DB and file level between Primary and Standby
    At Primary:
    SQL> select name,db_unique_name,database_role,open_mode from v$database;

    NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
    --------- ------------------------------ ---------------- --------------------
    ORCL ORCLDC PRIMARY READ WRITE

    SQL>
    SQL> set lines 200 pages 300
    SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
    SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF
    FROM
    (select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a,
    (select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
    (select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;
    Session altered.
    SQL>
    DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP ARC_DIFF
    ------------------------------ ---------------- ---------- ---------- ----------- -------------------- ----------
    ORCLDC PRIMARY 1 210 104 08-FEB-2025 20:39:23 106
    SQL>
    SQL> select current_scn from v$database;
    CURRENT_SCN
    -----------
    4123407
    SQL>
    SQL> set lines 333
    SQL> col file for a60
    SQL> select HXFIL File_num,substr(HXFNM,1,60) "file", fhscn from x$kcvfh;
    FILE_NUM file FHSCN
    ---------- ------------------------------------------------------------ --------------------
    1 /u01/app/oracle/oradata/ORCL/system01.dbf 4122798
    3 /u01/app/oracle/oradata/ORCL/sysaux01.dbf 4122798
    4 /u01/app/oracle/oradata/ORCL/undotbs01.dbf 4122798
    5 /u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf 2345308
    6 /u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf 2345308
    7 /u01/app/oracle/oradata/ORCL/users01.dbf 4122798
    8 /u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf 2345308
    9 /u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf 4122847
    10 /u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf 4123033
    11 /u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf 4123159
    12 /u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf 4123189
    13 /u01/app/oracle/oradata/ORCL/orclpdb/test01.dbf 4123195
    12 rows selected.
    SQL>
    At Standby:
    SQL> select name,db_unique_name,database_role,open_mode from v$database;
    NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
    --------- ------------------------------ ---------------- --------------------
    ORCL ORCLDR PHYSICAL STANDBY READ ONLY WITH APPLY
    SQL>
    SQL> set lines 200 pages 300
    SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
    SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp,
    round((sysdate - a.last_app_timestamp)*24*60,2) Gap_in_Mins, round((sysdate - a.last_app_timestamp)*24*60*60,2) Gap_in_Seconds
    FROM
    (select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where REGISTRAR='RFS' and applied='YES' group by thread#) a,
    (select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
    (select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;
    Session altered.
    SQL>
    DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ ARC_DIFF LAST_APP_TIMESTAMP GAP_IN_MINS GAP_IN_SECONDS
    ------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- --------------
    ORCLDR PHYSICAL STANDBY 1 210 104 106 08-FEB-2025 20:39:23 71667.78 4300067

    SQL> select current_scn from v$database;
    CURRENT_SCN
    -----------
    2950967
    SQL> set lines 333
    SQL> col file for a60
    SQL> select HXFIL File_num,substr(HXFNM,1,60) "file", fhscn from x$kcvfh;
    FILE_NUM file FHSCN
    ---------- ------------------------------------------------------------ --------------------
    1 /u01/app/oracle/oradata/ORCLDR/system01.dbf 2950968
    3 /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf 2950968
    4 /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf 2950968
    5 /u01/app/oracle/oradata/ORCLDR/pdbseed/system01.dbf 2345308
    6 /u01/app/oracle/oradata/ORCLDR/pdbseed/sysaux01.dbf 2345308
    7 /u01/app/oracle/oradata/ORCLDR/users01.dbf 2950968
    8 /u01/app/oracle/oradata/ORCLDR/pdbseed/undotbs01.dbf 2345308
    9 /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf 2950968
    10 /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf 2950968
    11 /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf 2950968
    12 /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf 2950968
    13 /u01/app/oracle/oradata/ORCLDR/orclpdb/test01.dbf 2950968
    12 rows selected.
    SQL>
    As per above output we can see the gap between Primary & Standy database. Also we have observed the difference in SCN.
    2. Now stop the MRP process at Standby. Also shutdown the DB & start in nomount.
    SQL> alter database recover managed standby database cancel;
    Database altered.
    SQL>
    SQL> shut immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL>
    SQL> startup nomount
    ORACLE instance started.
    Total System Global Area 1459616616 bytes
    Fixed Size 9177960 bytes
    Variable Size 1224736768 bytes
    Database Buffers 218103808 bytes
    Redo Buffers 7598080 bytes
    SQL>
    3. Now on Standby login in RMAN & restore the controlfile using service.
    [oracle@target ~]$ rman target sys
    Recovery Manager: Release 19.0.0.0.0 - Production on Sun Mar 30 15:39:31 2025
    Version 19.24.0.0.0
    Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

    target database Password:
    connected to target database: ORCL (not mounted)
    RMAN> restore standby controlfile from service orcldc;
    Starting restore at 30-MAR-25
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=255 device type=DISK
    channel ORA_DISK_1: starting datafile backup set restore
    channel ORA_DISK_1: using network backup set from service orcldc
    channel ORA_DISK_1: restoring control file
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
    output file name=/u01/app/oracle/oradata/ORCLDR/control01.ctl
    output file name=/u01/app/oracle/fast_recovery_area/ORCLDR/control02.ctl
    Finished restore at 30-MAR-25
    RMAN>
    RMAN> alter database mount;
    released channel: ORA_DISK_1
    Statement processed
    RMAN> report schema;
    Starting implicit crosscheck backup at 30-MAR-25
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=39 device type=DISK
    Crosschecked 15 objects
    Finished implicit crosscheck backup at 30-MAR-25
    Starting implicit crosscheck copy at 30-MAR-25
    using channel ORA_DISK_1
    Finished implicit crosscheck copy at 30-MAR-25
    searching for all files in the recovery area
    cataloging files...
    cataloging done
    List of Cataloged Files
    =======================
    File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_03_30/o1_mf_1_208_myl27nc7_.arc
    File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_03_30/o1_mf_1_209_myl33owm_.arc
    File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_03_30/o1_mf_1_210_myl3zxlm_.arc
    File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_03_30/o1_mf_1_211_myl4vyvz_.arc
    File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/autobackup/2025_01_23/o1_mf_s_1191149732_ms3npt02_.bkp
    File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/autobackup/2025_01_24/o1_mf_s_1191234421_ms67n42x_.bkp
    File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/autobackup/2025_01_24/o1_mf_s_1191235588_ms68kdwf_.bkp
    File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/autobackup/2025_01_24/o1_mf_s_1191236506_ms69n194_.bkp
    RMAN-06139: warning: control file is not current for REPORT SCHEMA
    Report of database schema for database with db_unique_name ORCLDR
    List of Permanent Datafiles
    =======================
    File Size(MB) Tablespace RB segs Datafile Name
    ---- -------- -------------------- ------- ------------------------
    1 1100 SYSTEM *** /u01/app/oracle/oradata/ORCLDR/system01.dbf
    3 890 SYSAUX *** /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf
    4 710 UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf
    5 450 PDB$SEED:SYSTEM *** /u01/app/oracle/oradata/ORCLDR/pdbseed/system01.dbf
    6 430 PDB$SEED:SYSAUX *** /u01/app/oracle/oradata/ORCLDR/pdbseed/sysaux01.dbf
    7 5 USERS *** /u01/app/oracle/oradata/ORCLDR/users01.dbf
    8 230 PDB$SEED:UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/pdbseed/undotbs01.dbf
    9 460 ORCLPDB:SYSTEM *** /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf
    10 490 ORCLPDB:SYSAUX *** /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf
    11 230 ORCLPDB:UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf
    12 5 ORCLPDB:USERS *** /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf
    13 1024 ORCLPDB:TEST *** /u01/app/oracle/oradata/ORCLDR/orclpdb/test01.dbf
    List of Temporary Files
    =======================
    File Size(MB) Tablespace Maxsize(MB) Tempfile Name
    ---- -------- -------------------- ----------- --------------------
    1 32 TEMP 32767 /u01/app/oracle/oradata/ORCLDR/temp01.dbf
    2 36 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/ORCLDR/pdbseed/temp012025-01-22_00-17-43-687-AM.dbf
    3 174 ORCLPDB:TEMP 32767 /u01/app/oracle/oradata/ORCLDR/orclpdb/temp01.dbf
    RMAN>
    4. Now recover the standby database using scn.
    At new Primary:
    RMAN> recover database from service orcldc noredo using compressed backupset;
    Starting recover at 30-MAR-25
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=38 device type=DISK
    skipping datafile 5; already restored to SCN 2345308
    skipping datafile 6; already restored to SCN 2345308
    skipping datafile 8; already restored to SCN 2345308
    channel ORA_DISK_1: starting incremental datafile backup set restore
    channel ORA_DISK_1: using compressed network backup set from service orcldc
    destination for restore of datafile 00001: /u01/app/oracle/oradata/ORCLDR/system01.dbf
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
    channel ORA_DISK_1: starting incremental datafile backup set restore
    channel ORA_DISK_1: using compressed network backup set from service orcldc
    destination for restore of datafile 00003: /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf
    channel ORA_DISK_1: restore complete, elapsed time: 00:02:15
    channel ORA_DISK_1: starting incremental datafile backup set restore
    channel ORA_DISK_1: using compressed network backup set from service orcldc
    destination for restore of datafile 00004: /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
    channel ORA_DISK_1: starting incremental datafile backup set restore
    channel ORA_DISK_1: using compressed network backup set from service orcldc
    destination for restore of datafile 00007: /u01/app/oracle/oradata/ORCLDR/users01.dbf
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
    channel ORA_DISK_1: starting incremental datafile backup set restore
    channel ORA_DISK_1: using compressed network backup set from service orcldc
    destination for restore of datafile 00009: /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf
    channel ORA_DISK_1: restore complete, elapsed time: 00:02:25
    channel ORA_DISK_1: starting incremental datafile backup set restore
    channel ORA_DISK_1: using compressed network backup set from service orcldc
    destination for restore of datafile 00010: /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf
    channel ORA_DISK_1: restore complete, elapsed time: 00:01:27
    channel ORA_DISK_1: starting incremental datafile backup set restore
    channel ORA_DISK_1: using compressed network backup set from service orcldc
    destination for restore of datafile 00011: /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
    channel ORA_DISK_1: starting incremental datafile backup set restore
    channel ORA_DISK_1: using compressed network backup set from service orcldc
    destination for restore of datafile 00012: /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
    channel ORA_DISK_1: starting incremental datafile backup set restore
    channel ORA_DISK_1: using compressed network backup set from service orcldc
    destination for restore of datafile 00013: /u01/app/oracle/oradata/ORCLDR/orclpdb/test01.dbf
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
    Finished recover at 30-MAR-25
    RMAN>
    RMAN> exit
    Recovery Manager complete.
    5. Now check the SCN at standby and start the MRP. Also do some log switches on Primary and check the sync.
    SQL> select name,db_unique_name,database_role,open_mode from v$database;
    NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
    --------- ------------------------------ ---------------- --------------------
    ORCL ORCLDR PHYSICAL STANDBY MOUNTED
    SQL> select current_scn from v$database;
    CURRENT_SCN
    -----------
    4121015
    SQL>
    SQL> set lines 333
    col file for a60
    select HXFIL File_num,substr(HXFNM,1,60) "file", fhscn from x$kcvfh;
    FILE_NUM file FHSCN
    ---------- ------------------------------------------------------------ --------------------
    1 /u01/app/oracle/oradata/ORCLDR/system01.dbf 4122274
    3 /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf 4122323
    4 /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf 4122531
    5 /u01/app/oracle/oradata/ORCLDR/pdbseed/system01.dbf 2345308
    6 /u01/app/oracle/oradata/ORCLDR/pdbseed/sysaux01.dbf 2345308
    7 /u01/app/oracle/oradata/ORCLDR/users01.dbf 4122616
    8 /u01/app/oracle/oradata/ORCLDR/pdbseed/undotbs01.dbf 2345308
    9 /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf 4122847
    10 /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf 4123033
    11 /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf 4123159
    12 /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf 4123189
    13 /u01/app/oracle/oradata/ORCLDR/orclpdb/test01.dbf 4123195
    12 rows selected.
    SQL>
    SQL> alter database recover managed standby database disconnect from session;
    Database altered.
    SQL> set lines 200 pages 300
    alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
    select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp,
    round((sysdate - a.last_app_timestamp)*24*60,2) Gap_in_Mins, round((sysdate - a.last_app_timestamp)*24*60*60,2) Gap_in_Seconds
    FROM
    (select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where REGISTRAR='RFS' and applied='YES' group by thread#) a,
    (select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
    (select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;
    SQL> Session altered.
    DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ ARC_DIFF LAST_APP_TIMESTAMP GAP_IN_MINS GAP_IN_SECONDS
    ------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- --------------
    ORCLDR PHYSICAL STANDBY 1 217 217 0 30-MAR-2025 15:59:50 5.1 306
    SQL>


    Thanks for visiting!!

    0 comments:

    Post a Comment