Refresh 19c physical standby database using service

 
Primary DB (19c) : TESTDB_PRIM (standby)
Standby DB (19c) : TESTDB_STD (cascade standby)
 
Standby has a GAP and waiting on logs which are deleted from primary
 
NOTE : here my primary is also a STANDBY from where I setup another standby (cascade)
 
SQL> select inst_id,process, thread#, sequence#, status from gv$managed_standby where process='MRP0';
 
   INST_ID PROCESS      THREAD#  SEQUENCE# STATUS
---------- --------- ---------- ---------- ------------
         1 MRP0               1     973244 WAIT_FOR_GAP
 
 
 
SQL> SELECT name "Database name", status, ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (select name from v$database) name, (select status from v$instance) status,(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;
 
Database  STATUS           Thread Last Sequence Received Last Sequence Applied Difference
--------- ------------ ---------- ---------------------- --------------------- ----------
TESTDB    MOUNTED               1                 975607                973243       2364
TESTDB    MOUNTED               3                 945126                942906       2220
TESTDB    MOUNTED               2                 995543                993282       2261
 
 ***** Switch some logfile on PRIMARY *****
 
SQL>   /
 
Database  STATUS           Thread Last Sequence Received Last Sequence Applied Difference
--------- ------------ ---------- ---------------------- --------------------- ----------
TESTDB    MOUNTED               1                 975607                973243       2364
TESTDB    MOUNTED               3                 945126                942906       2220
TESTDB    MOUNTED               2                 995545                993282       2263
 
 
I see logfiles are flowing to standby but MRP is waiting on GAP
 
Standby Alert log gives the GAP Message
 
2023-06-13T11:48:36.422200-05:00
PR00 (PID:397954): FAL: Failed to request gap sequence
PR00 (PID:397954):  GAP - thread 1 sequence 973244-973343
PR00 (PID:397954):  DBID 3401882687 branch 943793862
PR00 (PID:397954): FAL: All defined FAL servers have been attempted
PR00 (PID:397954): -------------------------------------------------------------------------
PR00 (PID:397954): Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
PR00 (PID:397954): parameter is defined to a value that's sufficiently large
PR00 (PID:397954): enough to maintain adequate log switch information to resolve
PR00 (PID:397954): archived redo log gaps.
PR00 (PID:397954): -------------------------------------------------------------
 
 
Solution :
 
OCI - Roll Forward A Standby Database Using Recover Database From Service (Doc ID 2931070.1)
How to Roll Forward a Standby Database Using Recover Database From Service (Doc ID 2850185.1)
 
From Oracle 18c and higher. Single command (RED) will do all the work
 
1.      STOP MRP
                     SQL>  recover managed standby database cancel;   ( or )
 
                     DGMGRL>  EDIT DATABASE '<standby>' SET STATE='APPLY-OFF';
 
 
2.      Stop all standby instances (if RAC) and mount only 1 node
3.       Run the shell script
HOSTNAME:NOTSET:/export/scripts $ cat TESTDB_INC_RECOVER_CB.sh
 
export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
rman target / trace /var/logs/RMAN_INC_recover_01jun2023.log << EOF
alter session set NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS';
select sysdate from dual;
 
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
allocate channel c8 type disk;
allocate channel c9 type disk;
allocate channel c10 type disk;
recover database from service TESTDB_PRIM section size 5g;
}
 
alter session set NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS';
select sysdate from dual;
 
EOF
 
 
4.      Once recovery is done then start MRP
 
 
 
 

Then logs will start apply normally.