When a failure occurs in an Oracle Database, a “Complete Recovery”—restoring the database to the state immediately preceding the failure—is possible by utilizing appropriate backups and archived logs. This article explains the procedures for complete recovery using Oracle Recovery Manager (RMAN) in Oracle Database 19c in detail.
1. What is Complete Recovery?
Complete Recovery is a method that applies backup files and archived logs (a history of REDO logs) to restore the database to the state immediately before the failure without any loss of transactions.
Main Application Scenarios:
- Loss of data files due to disk failure.
- Accidental deletion or corruption of data files.
- Database crash due to system failure.
2. Prerequisites and Verification
Before executing recovery, ensure the following environment is configured:
Verify ARCHIVELOG Mode
Operation in ARCHIVELOG mode is mandatory for complete recovery.
SELECT LOG_MODE FROM V$DATABASE;
Note: There is no issue if ARCHIVELOG is displayed.
Verify Backups
Check if restorable backups exist.
RMAN> LIST BACKUP;
3. Complete Recovery Procedures Using RMAN
The following is the flow for restoring a database using RMAN upon the occurrence of a failure.
Step 1: Check Database Status and Shutdown
Safely shut down the database when a failure occurs.
-- Check status
SELECT STATUS FROM V$INSTANCE;
-- Force shutdown if it cannot be opened due to failure
SHUTDOWN ABORT
Step 2: Start in Mount Mode
Start RMAN and mount the database.
[oracle@v19single ~]$ rman target /
RMAN> STARTUP MOUNT
Step 3: Restore Data Files
Restore the corrupted data files to their original locations using backup files.
RMAN> RESTORE DATABASE;
Step 4: Apply Archived Logs (Media Recovery)
Apply the archived logs to the restored data files to roll forward to the point of failure.
RMAN> RECOVER DATABASE;
[Execution Example]
SQL> shu immediate
ORA-01116: error in opening database file 7 ★Failure Occurred
ORA-01110: data file 7: '/u01/app/oracle/oradata/V19/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> select status from v$instance;
STATUS
------------------------------------
OPEN
SQL> shu abort ★Force shutdown
ORACLE instance shut down.
SQL> exit
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0 connection disconnected.
[oracle@v19single ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Mar 10 23:31:33 2025
Version 19.21.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount ★Start in mount mode
Oracle instance started
database mounted.
Total System Global Area: 1543500120 bytes
Fixed Size 8925528 bytes
Variable Size 889192448 bytes
Database Buffers 637534208 bytes
Redo Buffers 7847936 bytes
RMAN> restore database; ★Restore
Starting restore at 25-03-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/V19/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/V19/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/V19/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/V19/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/19.0.0/dbhome_1/dbs/013k1ijc_1_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/013k1ijc_1_1_1 tag=TAG20250310T232740
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 25-03-10
RMAN> recover database; ★Recover
Starting recover at 25-03-10
using channel ORA_DISK_1
Starting media recovery
archive log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_12_1153872185.dbf
archive log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_13_1153872185.dbf
archive log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_14_1153872185.dbf
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-03-10
RMAN> alter database open;
Statement processed
RMAN> select status from v$instance;
STATUS
------------
OPEN ★Database opened
Step 5: Open the Database
Once recovery is complete, open the database.
RMAN> ALTER DATABASE OPEN;
-- Verification
RMAN> SELECT STATUS FROM V$INSTANCE;
4. Manual Recovery Using SQL*Plus (Reference)
In environments where RMAN cannot be used, recovery procedures after physically copying files are as follows:
- Mount:
STARTUP MOUNT - Restore Files: Place data files from backup using OS commands (e.g.,
cp). - Execute Recovery:
RECOVER DATABASE; - Open DB:
ALTER DATABASE OPEN;
5. Troubleshooting
Measures to take if errors occur during recovery:
- Backup Not Found: Execute
CROSSCHECK BACKUP;to re-verify the integrity of the backup sets. - Archived Log Missing: If archived logs have been moved to another location, use
CATALOG START WITH '/path/to/archive/';to make them recognizable. - OPEN RESETLOGS Required: Since the incarnation is changed, execute
ALTER DATABASE OPEN RESETLOGS;. Immediately take a full backup after execution.
6. Summary
The points to ensure success in a complete recovery are as follows:
- ARCHIVELOG Mode Operation: The prerequisite for recovery.
- Utilization of RMAN: Reduces manual operations and enables reliable recovery.
- Prior Testing: Master the procedures to be taken in the event of a failure.
It is strongly recommended that database administrators take routine backups and perform regular verification of recovery procedures.
This article targets Oracle Database 19c (screens and default values may differ in other versions).
[reference]
Oracle Database Backup and Recovery Reference, 19c
コメント