In Oracle Database operations, data file corruption, accidental deletion, or disk failure can occur at any time.
When such disasters strike, your most reliable ally is RMAN (Recovery Manager)—Oracle’s built-in backup and recovery tool.
This article explains the actual RMAN complete recovery procedure you should perform immediately after a failure, along with diagrams and key supplements like the purpose of RESETLOGS.
- ✅ Scenario: When Should You Use This?
- 📌 Prerequisites (Must Be Prepared in Advance)
- 🔧 [Disaster Recovery Procedure] Steps for RMAN Complete Recovery
- 🛠️ RMAN Commands (To Be Run Immediately After Failure)
- 📘 Supplement: What Is RESETLOGS?
- ⚠️ Pre-Recovery Checklist
- ✅ Summary: Complete Recovery Is Your Most Reliable Disaster Strategy
✅ Scenario: When Should You Use This?
- A data file has been lost or corrupted
- Important data was accidentally deleted
- Oracle cannot start due to disk failure
In these cases, you can use an RMAN backup to restore the database to the exact state it was in just before the failure occurred.
📌 Prerequisites (Must Be Prepared in Advance)
Complete recovery is only possible if the following conditions are met:
┌──────────────────────────────────────────┐
│ Prerequisites for Complete Recovery │
├──────────────────────────────────────────┤
│ ✔ ARCHIVELOG mode is enabled │
│ ✔ Full RMAN backups are taken regularly │
│ ✔ All archive logs since the backup are retained │
└──────────────────────────────────────────┘
🔍 If no backups exist, RMAN recovery is not possible. Prepare in advance.
🔧 [Disaster Recovery Procedure] Steps for RMAN Complete Recovery
The following is the typical flow for restoring the database to the latest state before failure, without specifying SCN or time.
┌───────────────────────────────────────────────┐
│ RMAN Complete Recovery Flow (Post-Failure) │
├────────────────────────┬────────────────────────┤
│ ① Connect to RMAN │ Login to the target database │
│ ② Start in MOUNT mode │ Required for restoring files │
│ ③ RESTORE DATABASE │ Restore datafiles from backup │
│ ④ RECOVER DATABASE │ Apply all archived logs and redo logs │
│ ⑤ OPEN DATABASE │ Open normally (no RESETLOGS needed) │
└────────────────────────┴────────────────────────┘
🛠️ RMAN Commands (To Be Run Immediately After Failure)
① Connect to RMAN
$ rman target /
② Start Database in MOUNT Mode
STARTUP MOUNT;
🔍 Since data files are missing or corrupt, do not use OPEN yet.
③ Restore from Backup
RESTORE DATABASE;
→ This restores all necessary files from the full RMAN backup taken earlier.
④ Perform Complete Recovery
RECOVER DATABASE;
→ All archived and redo logs are automatically applied to restore the database to the exact point of failure.
⑤ Open the Database (No RESETLOGS Required)
ALTER DATABASE OPEN;
→ In complete recovery, log consistency is maintained, so RESETLOGS is not required.
SQL> select count(*) from test1.test_tab1; ★Failure occurred
select * from test1.test_tab1
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown immediate ★An error occurred during shutdown
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.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> shutdown abort ★Stop with abort
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@orcl19c ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Aug 3 00:45:55 2025
Version 19.3.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 with mount
Oracle instance started
database mounted
Total System Global Area 1543500144 bytes
Fixed Size 8896880 bytes
Variable Size 889192448 bytes
Database Buffers 637534208 bytes
Redo Buffers 7876608 bytes
RMAN> restore database; ★Restore
Starting restore at 03-AUG-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 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/ORCL/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORCL/test_tbs.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/19.0.0/dbhome_1/dbs/024058n0_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/024058n0_1_1 tag=TAG20250803T001840
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 03-AUG-25
RMAN> recover database; ★recovery
Starting recover at 03-AUG-25
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_7_1206875490.dbf
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_8_1206875490.dbf
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_9_1206875490.dbf
archived log for thread 1 with sequence 10 is already on disk as file /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1206875490.dbf
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_7_1206875490.dbf thread=1 sequence=7
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_8_1206875490.dbf thread=1 sequence=8
media recovery complete, elapsed time: 00:00:03
Finished recover at 03-AUG-25
RMAN> alter database open; ★Open
Statement processed
RMAN> select status from v$instance;
STATUS
------------
OPEN
RMAN> select count(*) from test1.test_tab1; ★
COUNT(*)
----------
55552
📘 Supplement: What Is RESETLOGS?
RESETLOGS is a command that reinitializes the redo log history and starts a new recovery timeline.
| Recovery Type | RESETLOGS Required? | Reason |
|---|---|---|
| Complete Recovery | ❌ No | Redo log consistency is maintained |
| Incomplete Recovery | ✅ Yes | Log gap requires new starting point |
| After recreating control file | ✅ Yes | Past log continuity is broken |
✅ For complete recovery, simply use
ALTER DATABASE OPEN;.
⚠️ Pre-Recovery Checklist
- ✅ Do you have a valid RMAN backup?
- ✅ Are all archive logs since the backup retained?
- ✅ Is there enough space in FRA?
- ✅ Are archived and redo logs accessible?
✅ Summary: Complete Recovery Is Your Most Reliable Disaster Strategy
┌────────────────────────────────────────────┐
│ Why RMAN Complete Recovery Matters │
├────────────────────────────────────────────┤
│ ✔ Recover exactly to the pre-failure state │
│ ✔ Zero data loss with proper archive log retention │
│ ✔ No RESETLOGS needed—minimal operational impact │
└────────────────────────────────────────────┘
That said, this process is only effective if you are prepared ahead of time.
┌────────────────────────────────────────────┐
│ Ongoing Preparations for Disaster Readiness │
├────────────────────────────────────────────┤
│ ✔ Regularly take RMAN full backups │
│ ✔ Monitor and retain archive logs properly │
│ ✔ Share and document recovery procedures internally │
└────────────────────────────────────────────┘
In recovery, it’s not about knowing the commands—it’s about being prepared to use them.
[reference]
9.2.2 RMAN Repository
コメント