Here I show, to restore and recover a database.
Hostname: node1.test.com
Database name: ORCL
Database current state:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 22
Next log sequence to archive 24
Current log sequence 24
As a part of scheduled backup, database backup was taken or take a fresh backup:
run {
allocate channel ch_01 type disk format ‘<backup_path’;
allocate channel ch_02 type disk format ‘backup_path;
backup as compressed backupset database;
backup archivelog all delete input;
}
List of Backups
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
1 B F A DISK 23-AUG-16 1 1 YES TAG20160823T165954
2 B F A DISK 23-AUG-16 1 1 YES TAG20160823T165954
3 B F A DISK 23-AUG-16 1 1 YES TAG20160823T165954
4 B F A DISK 23-AUG-16 1 1 YES TAG20160823T165954
5 B A A DISK 23-AUG-16 1 1 NO TAG20160823T170101
6 B A A DISK 23-AUG-16 1 1 NO TAG20160823T170101
Datafile missing error during dml operation:
SQL> create index dt_obj_seg_idx on tst_seg(data_object_id) tablespace DAT1;
create index dt_obj_seg_idx on tst_seg(data_object_id) tablespace DAT1
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: ‘/dsk1/oradata/ORCL/datafile/o1_mf_dat1_crwwdwd1_.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
for the above issue, we can do TSPITR or restore and recover datafile in offline mode.
But to illustrate the complete database recovery, corrupting multiple files. I use dd utility to corrupt the data dictionary files.
ALTER DATABASE OPEN
Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_10443.trc:
ORA-01157: cannot identify/lock data file 1 – see DBWR trace file
ORA-01110: data file 1: ‘/dsk1/oradata/ORCL/datafile/o1_mf_system_crwtjv2b_.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_10443.trc:
ORA-01157: cannot identify/lock data file 5 – see DBWR trace file
ORA-01110: data file 5: ‘/dsk1/oradata/ORCL/datafile/o1_mf_dat1_crwwdwd1_.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10535.trc:
ORA-01157: cannot identify/lock data file 1 – see DBWR trace file
ORA-01110: data file 1: ‘/dsk1/oradata/ORCL/datafile/o1_mf_system_crwtjv2b_.dbf’
ORA-1157 signalled during: ALTER DATABASE OPEN…
Now we know the problem, the files are either misssing or found corrupted headers. The only option is to do restore and recover.
connected to target database: ORCL (DBID=1445399087, not open)
RMAN> run {
2> allocate channel ch_01 type disk;
3> allocate channel ch_02 type disk;
4> restore datafile 1, 5;
5> recover database;
6> }
After restore, and recover it’s a straight forward step to open the database.
ORACLE instance started.
Total System Global Area 784998400 bytes
Fixed Size 2232472 bytes
Variable Size 486543208 bytes
Database Buffers 293601280 bytes
Redo Buffers 2621440 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 – see DBWR trace file
ORA-01110: data file 1:
‘/dsk1/oradata/ORCL/datafile/o1_mf_system_crwtjv2b_.dbf’
Did the recovery after this error.
SQL> alter database open;
Database altered.
Since there is no reset logs you don’t need to take immediate backup, you can still follow scheduled backups. If you go with resetlogs with an incomplete recovery then you have to take a full backup.