Summary: in this tutorial, you will learn how to restore and recover the database if the user datafile or corrupted or deleted Oracle Database instance.
Eg: You will get the below error.
1)ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
2)ORA-01110: data file 6:'/u01/app/oracle/oradata/PROD/datafile/o1_mf_users_l1vn7ngo_.dbf'
Step 1: we need a backup of that user data file or full database backup.
we are going to take a full database backup.:
RMAN> backup database;
Starting backup at 14-AUG-23
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
using channel ORA_DISK_9
using channel ORA_DISK_10
using channel ORA_DISK_11
using channel ORA_DISK_12
using channel ORA_DISK_13
using channel ORA_DISK_14
using channel ORA_DISK_15
using channel ORA_DISK_16
using channel ORA_DISK_17
using channel ORA_DISK_18
using channel ORA_DISK_19
using channel ORA_DISK_20
using channel ORA_DISK_21
using channel ORA_DISK_22
using channel ORA_DISK_23
using channel ORA_DISK_24
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_l1vn6k9o_.dbf
channel ORA_DISK_1: starting piece 1 at 14-AUG-23
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_l1vn5r5n_.dbf
channel ORA_DISK_2: starting piece 1 at 14-AUG-23
channel ORA_DISK_3: starting full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD/datafile/o1_mf_undotbs1_l1vn7omh_.dbf
channel ORA_DISK_3: starting piece 1 at 14-AUG-23
channel ORA_DISK_4: starting full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/PROD/datafile/o1_mf_omf_l2mbfk4s_.dbf
channel ORA_DISK_4: starting piece 1 at 14-AUG-23
channel ORA_DISK_5: starting full datafile backup set
channel ORA_DISK_5: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/PROD/datafile/o1_mf_omf_l2mbhvwo_.dbf
channel ORA_DISK_5: starting piece 1 at 14-AUG-23
channel ORA_DISK_6: starting full datafile backup set
channel ORA_DISK_6: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/PROD/datafile/o1_mf_users02_l4pv5odp_.dbf
channel ORA_DISK_6: starting piece 1 at 14-AUG-23
channel ORA_DISK_7: starting full datafile backup set
channel ORA_DISK_7: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/bigtbs.dbf
channel ORA_DISK_7: starting piece 1 at 14-AUG-23
channel ORA_DISK_8: starting full datafile backup set
channel ORA_DISK_8: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/tbs1.dbf
channel ORA_DISK_8: starting piece 1 at 14-AUG-23
channel ORA_DISK_9: starting full datafile backup set
channel ORA_DISK_9: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/auto.dbf
channel ORA_DISK_9: starting piece 1 at 14-AUG-23
channel ORA_DISK_10: starting full datafile backup set
channel ORA_DISK_10: specifying datafile(s) in backup set
channel ORA_DISK_11: starting full datafile backup set
channel ORA_DISK_11: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/PROD/datafile/o1_mf_users_l1vn7ngo_.dbf
channel ORA_DISK_11: starting piece 1 at 14-AUG-23
channel ORA_DISK_12: starting full datafile backup set
channel ORA_DISK_12: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_12: starting piece 1 at 14-AUG-23
channel ORA_DISK_4: finished piece 1 at 14-AUG-23
piece handle=/u01/full01_8c23qkma_1_1 tag=TAG20230814T191937 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:04
channel ORA_DISK_5: finished piece 1 at 14-AUG-23
piece handle=/u01/full01_8d23qkma_1_1 tag=TAG20230814T191937 comment=NONE
channel ORA_DISK_5: backup set complete, elapsed time: 00:00:04
channel ORA_DISK_6: finished piece 1 at 14-AUG-23
piece handle=/u01/full01_8e23qkmb_1_1 tag=TAG20230814T191937 comment=NONE
channel ORA_DISK_6: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_3: finished piece 1 at 14-AUG-23
piece handle=/u01/full01_8b23qkma_1_1 tag=TAG20230814T191937 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:05
channel ORA_DISK_7: finished piece 1 at 14-AUG-23
piece handle=/u01/full01_8f23qkmb_1_1 tag=TAG20230814T191937 comment=NONE
channel ORA_DISK_7: backup set complete, elapsed time: 00:00:04
channel ORA_DISK_8: finished piece 1 at 14-AUG-23
piece handle=/u01/full01_8g23qkmb_1_1 tag=TAG20230814T191937 comment=NONE
channel ORA_DISK_8: backup set complete, elapsed time: 00:00:04
channel ORA_DISK_9: finished piece 1 at 14-AUG-23
piece handle=/u01/full01_8h23qkmb_1_1 tag=TAG20230814T191937 comment=NONE
channel ORA_DISK_9: backup set complete, elapsed time: 00:00:03
including current control file in backup set
channel ORA_DISK_10: starting piece 1 at 14-AUG-23
channel ORA_DISK_12: finished piece 1 at 14-AUG-23
piece handle=/u01/full01_8k23qkmd_1_1 tag=TAG20230814T191937 comment=NONE
channel ORA_DISK_12: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_11: finished piece 1 at 14-AUG-23
piece handle=/u01/full01_8j23qkmd_1_1 tag=TAG20230814T191937 comment=NONE
channel ORA_DISK_11: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_10: finished piece 1 at 14-AUG-23
piece handle=/u01/full01_8i23qkmc_1_1 tag=TAG20230814T191937 comment=NONE
channel ORA_DISK_10: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: finished piece 1 at 14-AUG-23
piece handle=/u01/full01_8923qkma_1_1 tag=TAG20230814T191937 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:30
channel ORA_DISK_2: finished piece 1 at 14-AUG-23
piece handle=/u01/full01_8a23qkma_1_1 tag=TAG20230814T191937 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:30
Finished backup at 14-AUG-23
RMAN> exit
Step 2:
Now we are going to remove the user data file.
[oracle@oracle backup]$
[oracle@oracle backup]$ cd /u01/app/oracle/oradata/PROD/datafile
[oracle@oracle datafile]$
[oracle@oracle datafile]$
[oracle@oracle datafile]$ ll
total 2101244
-rw-r-----. 1 oracle oinstall 104865792 Aug 14 19:19 o1_mf_omf_l2mbfk4s_.dbf
-rw-r-----. 1 oracle oinstall 104865792 Aug 14 19:19 o1_mf_omf_l2mbhvwo_.dbf
-rw-r-----. 1 oracle oinstall 713039872 Aug 14 19:19 o1_mf_sysaux_l1vn5r5n_.dbf
-rw-r-----. 1 oracle oinstall 859840512 Aug 14 19:19 o1_mf_system_l1vn6k9o_.dbf
-rw-r-----. 1 oracle oinstall 62922752 Aug 14 19:17 o1_mf_temp_l1vn8mxl_.tmp
-rw-r-----. 1 oracle oinstall 251666432 Aug 14 19:19 o1_mf_undotbs1_l1vn7omh_.dbf
-rw-r-----. 1 oracle oinstall 104865792 Aug 14 19:19 o1_mf_users02_l4pv5odp_.dbf
-rw-r-----. 1 oracle oinstall 5251072 Aug 14 19:19 o1_mf_users_l1vn7ngo_.dbf
[oracle@oracle datafile]$
[oracle@oracle datafile]$
[oracle@oracle datafile]$ rm -rf o1_mf_users_l1vn7ngo_.dbf
[oracle@oracle datafile]$
Step 3: Restart the database hope you will get the below error,
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6:
'/u01/app/oracle/oradata/PROD/datafile/o1_mf_users_l1vn7ngo_.dbf'
Step 4: You can check the datafile number you should put on offline mode.
SQL> alter database datafile 6 offline;
Database altered.
Step5:Now try to open the database.
SQL> alter database open;
Database altered.
Check the datafile status using the below cmd,
SQL> select file#,name,status from v$datafile where file#='6';
FILE# NAME STATUS
---------------- ---------------------------------------------------------- ----------------
6 /u01/app/oracle/oradata/PROD/datafile/o1_mf_users_l1vn7ngo_.dbf RECOVER
Step6:Recover means we should do the recovery operation like below.
Connect the RMAN:
[oracle@oracle datafile]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Aug 14 19:26:27 2023
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=544189800)
Fire the lost backup cmd:
RMAN> list backup;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
75 Full 80.00K DISK 00:00:00 04-AUG-23
BP Key: 75 Status: AVAILABLE Compressed: NO Tag: TAG20230804T214127
Piece Name: /u01/full01_87230h87_1_1
SPFILE Included: Modification time: 04-AUG-23
SPFILE db_unique_name: PROD
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
76 Full 9.61M DISK 00:00:02 04-AUG-23
BP Key: 76 Status: AVAILABLE Compressed: NO Tag: TAG20230804T214127
Piece Name: /u01/full01_86230h87_1_1
Control File Included: Ckp SCN: 6141869 Ckp time: 04-AUG-23
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
77 Full 704.84M DISK 00:00:13 04-AUG-23
BP Key: 77 Status: AVAILABLE Compressed: NO Tag: TAG20230804T214127
Piece Name: /u01/full01_85230h87_1_1
List of Datafiles in backup set 77
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 6141869 04-AUG-23 /u01/app/oracle/oradata/PROD/datafile/o1_mf_system_l1vn6k9o_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
78 Full 9.61M DISK 00:00:01 04-AUG-23
BP Key: 78 Status: AVAILABLE Compressed: NO Tag: TAG20230804T220848
Piece Name: /u01/full01_88230irg_1_1
Control File Included: Ckp SCN: 6149933 Ckp time: 04-AUG-23
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
79 Full 1.03M DISK 00:00:01 14-AUG-23
BP Key: 79 Status: AVAILABLE Compressed: NO Tag: TAG20230814T191937
Piece Name: /u01/full01_8c23qkma_1_1
List of Datafiles in backup set 79
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
8 Full 6357360 14-AUG-23 /u01/app/oracle/oradata/PROD/datafile/o1_mf_omf_l2mbfk4s_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
80 Full 1.03M DISK 00:00:01 14-AUG-23
BP Key: 80 Status: AVAILABLE Compressed: NO Tag: TAG20230814T191937
Piece Name: /u01/full01_8d23qkma_1_1
List of Datafiles in backup set 80
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
9 Full 6357362 14-AUG-23 /u01/app/oracle/oradata/PROD/datafile/o1_mf_omf_l2mbhvwo_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
81 Full 1.03M DISK 00:00:01 14-AUG-23
BP Key: 81 Status: AVAILABLE Compressed: NO Tag: TAG20230814T191937
Piece Name: /u01/full01_8e23qkmb_1_1
List of Datafiles in backup set 81
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
10 Full 6357363 14-AUG-23 /u01/app/oracle/oradata/PROD/datafile/o1_mf_users02_l4pv5odp_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
82 Full 1.03M DISK 00:00:02 14-AUG-23
BP Key: 82 Status: AVAILABLE Compressed: NO Tag: TAG20230814T191937
Piece Name: /u01/full01_8h23qkmb_1_1
List of Datafiles in backup set 82
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
7 Full 6357366 14-AUG-23 /u01/app/oracle/oradata/auto.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
83 Full 1.03M DISK 00:00:01 14-AUG-23
BP Key: 83 Status: AVAILABLE Compressed: NO Tag: TAG20230814T191937
Piece Name: /u01/full01_8g23qkmb_1_1
List of Datafiles in backup set 83
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 6357365 14-AUG-23 /u01/app/oracle/oradata/orcl/tbs1.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
84 Full 6.05M DISK 00:00:03 14-AUG-23
BP Key: 84 Status: AVAILABLE Compressed: NO Tag: TAG20230814T191937
Piece Name: /u01/full01_8f23qkmb_1_1
List of Datafiles in backup set 84
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 6357364 14-AUG-23 /u01/app/oracle/oradata/orcl/bigtbs.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
85 Full 80.00K DISK 00:00:00 14-AUG-23
BP Key: 85 Status: AVAILABLE Compressed: NO Tag: TAG20230814T191937
Piece Name: /u01/full01_8k23qkmd_1_1
SPFILE Included: Modification time: 14-AUG-23
SPFILE db_unique_name: PROD
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
86 Full 3.49M DISK 00:00:04 14-AUG-23
BP Key: 86 Status: AVAILABLE Compressed: NO Tag: TAG20230814T191937
Piece Name: /u01/full01_8b23qkma_1_1
List of Datafiles in backup set 86
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 6357359 14-AUG-23 /u01/app/oracle/oradata/PROD/datafile/o1_mf_undotbs1_l1vn7omh_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
87 Full 2.25M DISK 00:00:02 14-AUG-23
BP Key: 87 Status: AVAILABLE Compressed: NO Tag: TAG20230814T191937
Piece Name: /u01/full01_8j23qkmd_1_1
List of Datafiles in backup set 87
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 6357378 14-AUG-23 /u01/app/oracle/oradata/PROD/datafile/o1_mf_users_l1vn7ngo_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
88 Full 9.61M DISK 00:00:03 14-AUG-23
BP Key: 88 Status: AVAILABLE Compressed: NO Tag: TAG20230814T191937
Piece Name: /u01/full01_8i23qkmc_1_1
Control File Included: Ckp SCN: 6357369 Ckp time: 14-AUG-23
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
89 Full 520.45M DISK 00:00:23 14-AUG-23
BP Key: 89 Status: AVAILABLE Compressed: NO Tag: TAG20230814T191937
Piece Name: /u01/full01_8a23qkma_1_1
List of Datafiles in backup set 89
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 6357358 14-AUG-23 /u01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_l1vn5r5n_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
90 Full 704.84M DISK 00:00:24 14-AUG-23
BP Key: 90 Status: AVAILABLE Compressed: NO Tag: TAG20230814T191937
Piece Name: /u01/full01_8923qkma_1_1
List of Datafiles in backup set 90
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 6357357 14-AUG-23 /u01/app/oracle/oradata/PROD/datafile/o1_mf_system_l1vn6k9o_.dbf
Start to Restore the user data file.
RMAN> restore datafile 6;
Starting restore at 14-AUG-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=250 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=367 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=13 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=132 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=251 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=368 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=14 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=133 device type=DISK
allocated channel: ORA_DISK_9
channel ORA_DISK_9: SID=252 device type=DISK
allocated channel: ORA_DISK_10
channel ORA_DISK_10: SID=369 device type=DISK
allocated channel: ORA_DISK_11
channel ORA_DISK_11: SID=15 device type=DISK
allocated channel: ORA_DISK_12
channel ORA_DISK_12: SID=134 device type=DISK
allocated channel: ORA_DISK_13
channel ORA_DISK_13: SID=253 device type=DISK
allocated channel: ORA_DISK_14
channel ORA_DISK_14: SID=370 device type=DISK
allocated channel: ORA_DISK_15
channel ORA_DISK_15: SID=16 device type=DISK
allocated channel: ORA_DISK_16
channel ORA_DISK_16: SID=135 device type=DISK
allocated channel: ORA_DISK_17
channel ORA_DISK_17: SID=254 device type=DISK
allocated channel: ORA_DISK_18
channel ORA_DISK_18: SID=371 device type=DISK
allocated channel: ORA_DISK_19
channel ORA_DISK_19: SID=17 device type=DISK
allocated channel: ORA_DISK_20
channel ORA_DISK_20: SID=136 device type=DISK
allocated channel: ORA_DISK_21
channel ORA_DISK_21: SID=255 device type=DISK
allocated channel: ORA_DISK_22
channel ORA_DISK_22: SID=372 device type=DISK
allocated channel: ORA_DISK_23
channel ORA_DISK_23: SID=18 device type=DISK
allocated channel: ORA_DISK_24
channel ORA_DISK_24: SID=137 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 00006 to /u01/app/oracle/oradata/PROD/datafile/o1_mf_users_l1vn7ngo_.dbf
channel ORA_DISK_1: reading from backup piece /u01/full01_8j23qkmd_1_1
channel ORA_DISK_1: piece handle=/u01/full01_8j23qkmd_1_1 tag=TAG20230814T191937
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 14-AUG-23
RMAN> recover datafile 6;
Starting recover at 14-AUG-23
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
using channel ORA_DISK_9
using channel ORA_DISK_10
using channel ORA_DISK_11
using channel ORA_DISK_12
using channel ORA_DISK_13
using channel ORA_DISK_14
using channel ORA_DISK_15
using channel ORA_DISK_16
using channel ORA_DISK_17
using channel ORA_DISK_18
using channel ORA_DISK_19
using channel ORA_DISK_20
using channel ORA_DISK_21
using channel ORA_DISK_22
using channel ORA_DISK_23
using channel ORA_DISK_24
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 14-AUG-23
RMAN> alter database datafile 6 online;
Statement processed
RMAN>
Summary: in this tutorial, you will learn how to use the Oracle STARTUP command to start an Oracle Database instance.
RMAN> select file#,name,status from v$datafile where file#='6';
FILE# NAME STATUS
------------- ----------------------------------------------------------- -------------
6 /u01/app/oracle/oradata/PROD/datafile/o1_mf_users_lfndop9v_.dbf ONLINE
Thanks for your time!!!!!
0 Comments