Network-Based Restore and Recovery Overview

In Oracle Data Guard environments, archivelog gaps can occur when primary archives are deleted before the standby database receives them, halting the redo apply process. Traditionally, resolving this required taking an incremental backup on the primary based on the standby's current SCN, transferring it manually, and applying it. Starting with Oracle 12c, the FROM SERVICE clause in RMAN allows restore and recover operations to be executed directly over the network, streamlining the synchronization process.

RMAN supports network-based restoration for various database structures:

• Database: RESTORE DATABASE FROM SERVICE <service_alias>
• Tablespace: RESTORE TABLESPACE FROM SERVICE <service_alias>
• Control File: RESTORE CONTROLFILE TO '<path>' FROM SERVICE <service_alias>
• SPFILE: RESTORE SPFILE FROM SERVICE <service_alias>

Network Restore Mechanics

When using the FROM SERVICE clause, RMAN creates a backup set on the source database containing the required files and transfers it over Oracle Net to the target. To optimize the operation, you can use multisection backups with SECTION SIZE, encrypt the network transfer using SET ENCRYPTION ALGORITHM, and compress the backup set with USING COMPRESSED BACKUPSET.

Network Recovery Mechanics

For rolling forward a physical standby, RMAN connects to the standby as the target and fetches an incremental backup from the primary. Only modified data blocks are recovered. The RECOVER STANDBY DATABASE FROM SERVICE command refreshes the standby data files and rolls them forward to the primary's timestamp. Because the standby control file retains older SCN values after the data files are updated, it must also be refreshed, and data file paths must be updated accordingly.

Prerequisites for network recovery include:

• Configured Oracle Net connectivity between primary and standby.
• Identical password files on both databases.
• Compatible parameter set to 12.0 or higher.

Practical Scenario: Cascading Standby Gap Recovery in 19c RAC

Environment and Gap Identification

Consider a configuration with a two-node 19c RAC primary database (PROD_RAC), a physical standby (DR1), and a newly built cascading standby (DR2). The cascading standby was not initially monitored and accumulated an apply lag of 15 days. Since the primary's archivelog deletion policy removed logs older than 15 days, the missing archives could not be shipped.

Checking the Data Guard stats revealed the significant lag:

SELECT name, value, datum_time FROM v$dataguard_stats WHERE name IN ('transport lag', 'apply lag');

NAME          VALUE                DATUM_TIME
------------- -------------------- ------------------------------
transport lag +00 00:00:56         07/08/2024 14:54:20
apply lag     +15 07:41:28         07/08/2024 14:54:20

Attempting to restart the Managed Recovery Process (MRP) resulted in errors indicating missing data files:

ORA-01111: name for data file 181 is unknown - rename to correct file
ORA-01110: data file 181: '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00181'

Diagnosing Parameter Misconfiguration

The UNNAMED file issue and missing data files were traced to incorrect initialization parameters on the cascading standby. The db_file_name_convert parameter was empty, and standby_file_management was set to MANUAL, preventing automatic data file creation when new tablespaces were added to the primary.

The parameters were corrected as follows:

ALTER SYSTEM SET db_file_name_convert='+DATA/proddb/datafile','/data/proddb/datafile','+DATA/proddb/tempfile','/data/proddb/tempfile' SCOPE=SPFILE SID='*';
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;
SHUTDOWN IMMEDIATE;
STARTUP;

Executing the Network Incremental Recovery

Because the environment runs on 19c, the FROM SERVICE feature can be used for an incremental recovery. For large databases, it is advisable to run this in the background using compression to minimize network traffic.

Create an RMAN command script (sync_dr2.rman):

SET COMPRESSION ALGORITHM 'BASIC';
RECOVER STANDBY DATABASE FROM SERVICE 'DR1_TNS' USING COMPRESSED BACKUPSET;

Execute the script via a shell script (run_sync.sh):

rman target / cmdfile=/home/oracle/scripts/sync_dr2.rman log=/home/oracle/scripts/sync_dr2.log append

nohup sh /home/oracle/scripts/run_sync.sh &

Crucial Note on OMF: If the primary database uses Oracle Managed Files (OMF) with db_create_file_dest set (e.g., to +DATA), but the standby uses a standard file system, the standby's db_create_file_dest parameter must be explicitly unset (null). If the standby retains the OMF parameter, the FROM SERVICE recovery will ignore the incremental SCN and perform a full database backup over the network instead.

Reconstructing Standby Redo Logs

After the incremental recovery completes, the alert log may repeatedly show errors regarding missing standby redo log files, as the recovery process might reference paths from the source database. It is necessary to drop and recreate the standby redo log groups.

Identify the current standby redo logs:

SELECT a.thread#, a.group#, b.member, a.bytes/1024/1024 MB FROM v$standby_log a, v$logfile b WHERE a.group# = b.group#;

Drop the invalid or active log groups. For groups stuck in an ACTIVE state, clear them first:

ALTER DATABASE CLEAR LOGFILE GROUP 15;
ALTER DATABASE CLEAR LOGFILE GROUP 24;
ALTER DATABASE DROP LOGFILE GROUP 34;

Add the new standby redo log groups pointing to the correct local paths:

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 34 ('/data/proddb/onlinelog/srl_34_a.log', '/data/proddb/fra/srl_34_b.log') SIZE 500M;

Resuming Redo Apply

Once the logs are reconstructed, open the database and start the MRP:

ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL 32 USING CURRENT LOGFILE DISCONNECT;

Verify the apply lag has been eliminated and MRP is running correctly:

SELECT name, value FROM v$dataguard_stats WHERE name = 'apply lag';
SELECT PROCESS, STATUS, SEQUENCE# FROM V$MANAGED_STANDBY WHERE PROCESS = 'MRP0';

RMAN Recovery Internals

When the RECOVER STANDBY DATABASE FROM SERVICE command is executed, RMAN orchestrates the following automated sequence:

1. Restarts the standby in NOMOUNT and restores the control file from the primary service.
2. Mounts the standby database and sets standby_file_management to MANUAL.
3. Uses SET NEWNAME to map the missing data files (e.g., 181-186) to local paths and restores them directly from the primary service.
4. Catalogs the restored data file copies and switches the database to use them.
5. Performs an incremental network recovery starting from the data file header SCN, skipping data files that are already synchronized.
6. Applies the necessary archived logs to achieve consistency.
7. Resets standby_file_management back to AUTO.