Roll Forward Standby with 1 Command

I have a testbed that I use for research purposes. This testbed consists of a 2-node RAC primary database (RESP) and a 2-node RAC physical standby database (RESS). Both Oracle 19c on Oracle Linux. I also use the DG Broker in this configuration.

I am planning using this testbed to work on a new Oracle feature. Normally when I let this testbed sit unattended for a long period of time, the physical standby has stopped receiving its redo stream from the primary. Today I checked on it and sure enough, the standby has not been keeping up.

Transport lag of 233 days. Yikes! Obviously I have neglected this database for too long.

In the past, I would use the information in MOS Note 836986.1 to use RMAN incremental backups to roll-forward the physical standby database. The basic procedure is to determine the SCN of the physical standby and then create an RMAN backup set containing all blocks with a higher SCN. Then apply the backup set to the standby and get it back in business.

I’ve always thought that it would be nice if RMAN could look up this SCN and then figure this all out on its own. Apparently Oracle agrees with me. When I was looking up Note 836986.1 this morning, I came across Note 2431311.1 which shows me how to roll forward my physical standby database using just a single RMAN command. This feature is available to anyone with Oracle 18c and higher.

Before I can get started, I need to shutdown both instances of the standby and then start just one instance with SQL*Plus. I then stop managed recovery with the DG Broker.

SQL> startup mount

DGMGRL> edit database ress set state=apply-off;

Now that I have the standby ready, I’m ready to use the one RMAN command: recover standby database from service resp where resp is the TNS alias pointing to the primary. As you can see from the RMAN output below, snipped for brevity, RMAN figures it all out for me.

RMAN> connect target /

RMAN> recover standby database from service resp;

Starting recover at 22-APR-23
using target database control file instead of recovery catalog
Executing: alter database flashback off
Oracle instance started

Total System Global Area 3053449792 bytes

Fixed Size 8929856 bytes
Variable Size 637534208 bytes
Database Buffers 2399141888 bytes
Redo Buffers 7843840 bytes

contents of Memory Script:
{
restore standby controlfile from service ‘resp’;
alter database mount standby database;
}
executing Memory Script

Starting restore at 22-APR-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=265 instance=ress1 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service resp
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:10
output file name=/u01/app/oracle/oradata/RESP/control01.ctl
output file name=/u01/app/oracle/oradata/RESP/control02.ctl
Finished restore at 22-APR-23

released channel: ORA_DISK_1
Statement processed

<snip>

starting media recovery

media recovery complete, elapsed time: 00:00:00
Finished recover at 22-APR-23
flashback needs to be reenabled on standby open
Finished recover at 22-APR-23

Once done, I start all instances and use the DG Broker to resume redo apply. After a few minutes, I checked the status of my standby:

DGMGRL> show database ress

Database – ress

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 10.20 MByte/s
Real Time Query: OFF
Instance(s):
ress1 (apply instance)
ress2

Database Status:
SUCCESS

That looks much better and so easy to do!