Full Recovery of Standby Database over the network

Say that your Standby database is lagging behind the Primary database.

You could
a.  Fetch and apply all the ArchiveLogs required to cover the lag
b.  Take an Incremental Backup from the Primary and apply it to the Standby (the syntax being "backup as compressed backupset incremental from SCN=xxxx format '......' ) and then restore it on the Standby
c.  Since 12c, do a complete Refresh of the Standby over the network
(at the end of this post, I have added a link to a 19c demo of Restore+Recovery of a single datafile from the Primary to the Standby)


My Primary reports :

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            79

SQL> 

While my Standby reports :

SQL> select high_Sequence# from v$archive_gap;

HIGH_SEQUENCE#
--------------
            67

SQL> 

So, I attempt to refresh the Standby with :

$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jun 17 22:55:22 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     419430400 bytes

Fixed Size                     8793496 bytes
Variable Size                167772776 bytes
Database Buffers             234881024 bytes
Redo Buffers                   7983104 bytes

RMAN> restore database from service ORCL12C;

Starting restore at 17-JUN-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=66 device type=DISK

skipping datafile 5; already restored to SCN 1443131
skipping datafile 6; already restored to SCN 1443131
skipping datafile 8; already restored to SCN 1443131
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /STANDBY/database/STDB/datafile/o1_mf_system_2gude3k1_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /STANDBY/database/STDB/datafile/o1_mf_sysaux_2hude3l5_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /STANDBY/database/STDB/datafile/o1_mf_users_2qude3pc_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_system_2jude3nb_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00010 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_sysaux_2fude3iu_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_undotbs1_2iude3mi_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00012 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_users_2mude3op_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00013 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_apex_199_2pude3pb_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00014 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_apex_199_2rude3pe_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00015 to /STANDBY/database/STDB/datafile/o1_mf_undotbs2_2oude3p4_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 17-JUN-20

RMAN> 
RMAN> exit


Recovery Manager complete.
$sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 17 22:58:55 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> 

The alert log on the Standby even shows the new PDB that I had created on the Primary while the Standby was down.  (See my previous BlogPost where I had created NEWPDB)

2020-06-17T22:59:13.347236+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_67_hgncbh2m_.arc
2020-06-17T22:59:13.497650+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_68_hgnbvsy2_.arc
2020-06-17T22:59:13.566410+08:00
Completed: alter database recover managed standby database disconnect from session
2020-06-17T22:59:13.659512+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_69_hgncbj0v_.arc
2020-06-17T22:59:13.759502+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_70_hgncbj5z_.arc
2020-06-17T22:59:13.981225+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_71_hgncbjf6_.arc
Recovery created pluggable database NEWPDB
2020-06-17T22:59:21.246470+08:00
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file /STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_system_hgnbd696_.dbf from /STANDBY/database/STDB/49BFE9E2D73E2038E0530100007F846C/datafile/o1_mf_system_2lude3oa_.dbf
NEWPDB(4):Successfully added datafile 41 to media recovery
NEWPDB(4):Datafile #41: '/STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_system_hgnbd696_.dbf'
2020-06-17T22:59:30.184914+08:00
Recovery copied files for tablespace SYSAUX
Recovery successfully copied file /STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_sysaux_hgnbd6c1_.dbf from /STANDBY/database/STDB/49BFE9E2D73E2038E0530100007F846C/datafile/o1_mf_sysaux_2kude3nr_.dbf
NEWPDB(4):Successfully added datafile 42 to media recovery
NEWPDB(4):Datafile #42: '/STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_sysaux_hgnbd6c1_.dbf'
2020-06-17T22:59:32.983486+08:00
Recovery copied files for tablespace UNDOTBS1
Recovery successfully copied file /STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_undotbs1_hgnbd6c2_.dbf from /STANDBY/database/STDB/49BFE9E2D73E2038E0530100007F846C/datafile/o1_mf_undotbs1_2nude3p1_.dbf
NEWPDB(4):Successfully added datafile 43 to media recovery
NEWPDB(4):Datafile #43: '/STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_undotbs1_hgnbd6c2_.dbf'
2020-06-17T22:59:36.310405+08:00
(4):Successfully added datafile 44 to media recovery
(4):Datafile #44: '/STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_my_user__hgncypmz_.dbf'
(4):Resize operation completed for file# 42, old size 337920K, new size 348160K
(4):Resize operation completed for file# 42, old size 348160K, new size 368640K
2020-06-17T22:59:39.083144+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_72_hgncbjf0_.arc
2020-06-17T22:59:39.235507+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_73_hgnc90l0_.arc
2020-06-17T22:59:39.547295+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_74_hgncbdhw_.arc
2020-06-17T22:59:39.734181+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_75_hgncbdn2_.arc
2020-06-17T22:59:39.856485+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_76_hgncbgvb_.arc
2020-06-17T22:59:40.007157+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_77_hgncbgr5_.arc
2020-06-17T22:59:40.129214+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_78_hgncbmo4_.arc
2020-06-17T22:59:40.266298+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_79_hgncbmmz_.arc
Media Recovery Waiting for thread 1 sequence 80 (in transit)
2020-06-17T23:02:19.174414+08:00
Archived Log entry 18 added for thread 1 sequence 80 rlc 937554761 ID 0x2dc76487 LAD2 :
2020-06-17T23:02:19.575536+08:00
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Assigned to RFS process (PID:6137)
RFS[2]: No standby redo logfiles created
2020-06-17T23:02:19.597702+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_80_hgncrmr3_.arc
RFS[2]: Opened log for T-1.S-81 dbid 768045447 branch 937554761
2020-06-17T23:02:20.948511+08:00
Media Recovery Waiting for thread 1 sequence 81 (in transit)
2020-06-17T23:02:31.317814+08:00
Archived Log entry 19 added for thread 1 sequence 81 rlc 937554761 ID 0x2dc76487 LAD2 :
RFS[2]: No standby redo logfiles created
RFS[2]: Opened log for T-1.S-82 dbid 768045447 branch 937554761
2020-06-17T23:02:31.710951+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_81_hgnd3vlo_.arc
Media Recovery Waiting for thread 1 sequence 82 (in transit)

And I can check whether the datafiles are being updated at the Standby

SQL> select current_scn, database_role from v$database;

CURRENT_SCN    DATABASE_ROLE
----------- ----------------
    3035674 PHYSICAL STANDBY

SQL> select file#, checkpoint_change# from v$datafile order by 1;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            3035675
         3            3035675
         5            1443131
         6            1443131
         7            3035675
         8            1443131
         9            3035675
        10            3035675
        11            3035675
        12            3035675
        13            3035675
        14            3035675
        15            3035675
        41            3035675
        42            3035675
        43            3035675
        44            3035675

17 rows selected.

SQL> 

What are datafiles 5, 6 and 8 ? Even the RMAN RESTORE command had shown them to "already restored to SCN 1443131"  -- which is a much lower SCN ?

SQL> select p.con_id, p.name, p.open_mode, d.file#, d.checkpoint_change#
  2  from v$pdbs p, v$datafile d
  3  where p.con_id=d.con_id
  4  order by d.file#
  5  /

    CON_ID NAME         OPEN_MODE       FILE# CHECKPOINT_CHANGE#
---------- ------------ ---------- ---------- ------------------
         2 PDB$SEED     MOUNTED             5            1443131
         2 PDB$SEED     MOUNTED             6            1443131
         2 PDB$SEED     MOUNTED             8            1443131
         3 ORCL         MOUNTED             9            3035675
         3 ORCL         MOUNTED            10            3035675
         3 ORCL         MOUNTED            11            3035675
         3 ORCL         MOUNTED            12            3035675
         3 ORCL         MOUNTED            13            3035675
         3 ORCL         MOUNTED            14            3035675
         4 NEWPDB       MOUNTED            41            3035675
         4 NEWPDB       MOUNTED            42            3035675
         4 NEWPDB       MOUNTED            43            3035675
         4 NEWPDB       MOUNTED            44            3035675
 
13 rows selected.

SQL> 

Those 3 datafiles are of the SEED PDB which does not get updated as it is not opened READ-WRITE.


Note : Here is a 19c demo of Restore+Recovery of a single datafile from Primary to the Standby