Oracle Standby Databases are crucial components in a Data Guard environment, providing disaster recovery and high availability. In this guide, I'll show you how to manually recover a Physical Standby Database when the automated recovery process needs intervention.
Scenario Overview
We are working on a Physical Standby Database named BCP. Initially, it is in MOUNTED mode, as shown by the following query:
SQL> SELECT name, database_role, open_mode, log_mode FROM v$database;
NAME DATABASE_ROLE OPEN_MODE LOG_MODE --------- ---------------- -------------------- ------------ BCP PHYSICAL STANDBY MOUNTED ARCHIVELOG
To proceed with the recovery, we first cancel the managed recovery process:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
Once the managed recovery is canceled, we initiate the manual recovery process.
Manual Recovery Process
Start the manual recovery by issuing the following command:
SQL> RECOVER STANDBY DATABASE;
You'll see Oracle prompting for the next archived log file required for recovery:
ORA-00279: change 43321385282 generated at 03/21/2025 07:44:48 needed for thread 1 ORA-00289: suggestion : /oracle/BCP/arch/BCParch1_370887_1077509460.dbf ORA-00280: change 43321385282 for thread 1 is in sequence #370887
Applying Archived Redo Logs
At this point, Oracle suggests the next archived log needed for recovery. You can either:
- Press
<ENTER>to apply the suggested log. - Specify a different filename if you want to apply a different log.
- Use
AUTOto allow Oracle to automatically apply subsequent logs. - Use
CANCELto stop the recovery process.
We select AUTO to automatically apply the necessary logs:
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} AUTO
The recovery continues with the next logs:
ORA-00279: change 43321421211 generated at 03/21/2025 07:50:15 needed for thread 1 ORA-00289: suggestion : /oracle/BCP/arch/BCParch1_370888_1077509460.dbf ORA-00280: change 43321421211 for thread 1 is in sequence #370888 ORA-00278: log file '/oracle/BCP/arch/BCParch1_370887_1077509460.dbf' no longer needed for this recovery
Oracle automatically applies the next required archived logs in sequence:
ORA-00279: change 43321463359 generated at 03/21/2025 07:54:15 needed for thread 1 ORA-00289: suggestion : /oracle/BCP/arch/BCParch1_370889_1077509460.dbf ORA-00280: change 43321463359 for thread 1 is in sequence #370889 ORA-00278: log file '/oracle/ECP/oraarch/ECParch1_370888_1077509460.dbf' no longer needed for this recovery
Key Takeaways
Manual recovery is useful when you need to apply archived logs incrementally or troubleshoot recovery issues.
The AUTO option allows Oracle to automatically apply the required logs without manual intervention.
Using ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL stops the automatic recovery process and enables manual recovery mode.
Best Practices
Automate log shipping and application in a production Data Guard setup to minimize the need for manual recovery.
Monitor the lag between primary and standby databases using v$archive_gap to ensure the standby is in sync.
Archive log management: Ensure that archived logs are not deleted before they are applied to the standby database.
Useful Queries
Check the current status of the standby database:
SQL> SELECT name, database_role, open_mode, log_mode FROM v$database;
Verify the last applied log sequence:
SQL> SELECT sequence#, applied FROM v$archived_log WHERE applied = 'YES' ORDER BY sequence# DESC;
Note: Recovering a physical standby database is a fundamental skill for Oracle DBAs. By understanding how to cancel the managed recovery process and perform manual recovery, you can effectively handle recovery scenarios and troubleshoot Data Guard issues.
Toufique Khan
