Skip to content
Home » Oracle » When Several Datafiles Are Missing - Restore From Local Backups

When Several Datafiles Are Missing - Restore From Local Backups

When several datafiles are accidentally deleted, we can use RMAN to restore and recover the datafiles individually.

  1. List the backup we have now.
  2. [oracle@primary01 ~]$ rman target /
    ...
    RMAN> list backup;


    List of Backup Sets
    ===================

    ...
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    5       Full    1.14G      DISK        00:02:35     12-DEC-12
            BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20121212T085811
            Piece Name: +DATA/primdb/backupset/2012_12_12/nnndf0_tag20121212t085811_0.1079.801824293
      List of Datafiles in backup set 5
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      1       Full 2937050    12-DEC-12 +DATA/primdb/datafile/system.897.797943475
      2       Full 2937050    12-DEC-12 +DATA/primdb/datafile/sysaux.898.797943569
      3       Full 2937050    12-DEC-12 +DATA/primdb/datafile/undotbs1.899.797943687
      4       Full 2937050    12-DEC-12 +DATA/primdb/datafile/users.902.797943753
      5       Full 2937050    12-DEC-12 +DATA/primdb/datafile/example.900.797943711
      6       Full 2937050    12-DEC-12 +DATA/primdb/datafile/undotbs2.901.797943737

    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    6       Full    18.64M     DISK        00:00:09     12-DEC-12
            BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20121212T085811
            Piece Name: +DATA/primdb/backupset/2012_12_12/ncsnf0_tag20121212t085811_0.1080.801824455
      SPFILE Included: Modification time: 12-DEC-12
      SPFILE db_unique_name: PRIMDB
      Control File Included: Ckp SCN: 2937050      Ckp time: 12-DEC-12
  3. Shutdown all instances of the primary RAC, and make sure the standby database cannot be reached by anyone beside sysdba.
  4. [oracle@primary01 ~]$ srvctl stop database -d compdb
  5. Remove datafiles to simulate the missing of datafiles.
  6. [oracle@primary02 ~]$ asmcmd
    ASMCMD> cd data/primdb/datafile
    ASMCMD> ls -l
    Type      Redund  Striped  Time             Sys  Name
    DATAFILE  MIRROR  COARSE   DEC 12 10:00:00  Y    EXAMPLE.897.801828179
    DATAFILE  MIRROR  COARSE   DEC 12 10:00:00  Y    SYSAUX.898.797943569
    DATAFILE  MIRROR  COARSE   DEC 12 10:00:00  Y    SYSTEM.902.801827981
    DATAFILE  MIRROR  COARSE   DEC 12 10:00:00  Y    UNDOTBS1.899.797943687
    DATAFILE  MIRROR  COARSE   DEC 12 10:00:00  Y    UNDOTBS2.901.797943737
    DATAFILE  MIRROR  COARSE   DEC 12 10:00:00  Y    USERS.900.801828179
    ASMCMD> rm EXAMPLE.897.801828179
    ASMCMD> rm SYSTEM.902.801827981
    ASMCMD> rm USERS.900.801828179
    ASMCMD> ls -l
    Type      Redund  Striped  Time             Sys  Name
    DATAFILE  MIRROR  COARSE   DEC 12 10:00:00  Y    SYSAUX.898.797943569
    DATAFILE  MIRROR  COARSE   DEC 12 10:00:00  Y    UNDOTBS1.899.797943687
    DATAFILE  MIRROR  COARSE   DEC 12 10:00:00  Y    UNDOTBS2.901.797943737
  7. Open only one instance of the primary RAC.
  8. [oracle@primary01 ~]$ sqlplus / as sysdba
    ...
    SQL> startup open;
    ORACLE instance started.

    Total System Global Area  839282688 bytes
    Fixed Size                  2217992 bytes
    Variable Size             792725496 bytes
    Database Buffers           41943040 bytes
    Redo Buffers                2396160 bytes
    Database mounted.
    ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
    ORA-01110: data file 1: '+DATA/primdb/datafile/system.902.801827981'


    SQL> select inst_id, open_mode, database_role, switchover_status from gv$database;

       INST_ID OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
    ---------- -------------------- ---------------- --------------------
             1 MOUNTED              PRIMARY          NOT ALLOWED

    Yes, we both know the database will fail to open, and the database detects at least one of the datafile is missing. We need to know which datafiles are missing before restoring datafiles.

  9. Validate the database by RMAN.
  10. We need to know which datafiles are missing.

    RMAN> validate database;

    Starting validate at 12-DEC-12
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=41 instance=primdb1 device type=DISK
    RMAN-06169: could not read file header for datafile 1 error reason 4
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of validate command at 12/12/2012 10:11:42
    RMAN-06056: could not access datafile 1

    RMAN> list failure;

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of list command at 12/12/2012 10:11:52
    RMAN-05533: LIST FAILURE is not supported on RAC database
  11. Restore and recover datafile 1.
  12. RMAN> run {
    2> restore datafile 1;
    3> recover datafile 1;
    4> }

    Starting restore at 12-DEC-12
    using channel ORA_DISK_1

    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 +DATA/primdb/datafile/system.902.801827981
    channel ORA_DISK_1: reading from backup piece +DATA/primdb/backupset/2012_12_12/nnndf0_tag20121212t085811_0.1079.801824293
    channel ORA_DISK_1: piece handle=+DATA/primdb/backupset/2012_12_12/nnndf0_tag20121212t085811_0.1079.801824293 tag=TAG20121212T085811
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:01:16
    Finished restore at 12-DEC-12

    Starting recover at 12-DEC-12
    using channel ORA_DISK_1

    starting media recovery

    archived log for thread 1 with sequence 71 is already on disk as file +DATA/primdb/archivelog/2012_12_12/thread_1_seq_71.1081.801824977
    archived log for thread 1 with sequence 72 is already on disk as file +DATA/primdb/archivelog/2012_12_12/thread_1_seq_72.1082.801824981
    archived log for thread 1 with sequence 73 is already on disk as file +DATA/primdb/archivelog/2012_12_12/thread_1_seq_73.1083.801826851
    archived log for thread 1 with sequence 74 is already on disk as file +DATA/primdb/archivelog/2012_12_12/thread_1_seq_74.1084.801826855
    archived log for thread 1 with sequence 75 is already on disk as file +DATA/primdb/archivelog/2012_12_12/thread_1_seq_75.1085.801827543
    archived log for thread 1 with sequence 76 is already on disk as file +DATA/primdb/archivelog/2012_12_12/thread_1_seq_76.1086.801827547
    archived log file name=+DATA/primdb/archivelog/2012_12_12/thread_1_seq_71.1081.801824977 thread=1 sequence=71
    archived log file name=+DATA/primdb/archivelog/2012_12_12/thread_1_seq_72.1082.801824981 thread=1 sequence=72
    archived log file name=+DATA/primdb/archivelog/2012_12_12/thread_1_seq_73.1083.801826851 thread=1 sequence=73
    archived log file name=+DATA/primdb/archivelog/2012_12_12/thread_1_seq_74.1084.801826855 thread=1 sequence=74
    archived log file name=+DATA/primdb/archivelog/2012_12_12/thread_1_seq_75.1085.801827543 thread=1 sequence=75
    media recovery complete, elapsed time: 00:00:01
    Finished recover at 12-DEC-12
  13. Validate the database again.
  14. To check whether any other datafile is missing.

    RMAN> validate database;

    Starting validate at 12-DEC-12
    using channel ORA_DISK_1
    RMAN-06169: could not read file header for datafile 4 error reason 4
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of validate command at 12/12/2012 10:14:34
    RMAN-06056: could not access datafile 4
  15. Restore and recovery datafile 4,5.
  16. Since we know datafile 4, 5 are missing, so we restore the two datafiles at the same time as below.

    RMAN> run {
    2> restore datafile 4,5;
    3> recover datafile 4,5;
    4> }

    Starting restore at 12-DEC-12
    using channel ORA_DISK_1

    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 00004 to +DATA/primdb/datafile/users.900.801828179
    channel ORA_DISK_1: restoring datafile 00005 to +DATA/primdb/datafile/example.897.801828179
    channel ORA_DISK_1: reading from backup piece +DATA/primdb/backupset/2012_12_12/nnndf0_tag20121212t085811_0.1079.801824293
    channel ORA_DISK_1: piece handle=+DATA/primdb/backupset/2012_12_12/nnndf0_tag20121212t085811_0.1079.801824293 tag=TAG20121212T085811
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
    Finished restore at 12-DEC-12

    Starting recover at 12-DEC-12
    using channel ORA_DISK_1

    starting media recovery

    archived log for thread 1 with sequence 71 is already on disk as file +DATA/primdb/archivelog/2012_12_12/thread_1_seq_71.1081.801824977
    archived log for thread 1 with sequence 72 is already on disk as file +DATA/primdb/archivelog/2012_12_12/thread_1_seq_72.1082.801824981
    archived log for thread 1 with sequence 73 is already on disk as file +DATA/primdb/archivelog/2012_12_12/thread_1_seq_73.1083.801826851
    archived log for thread 1 with sequence 74 is already on disk as file +DATA/primdb/archivelog/2012_12_12/thread_1_seq_74.1084.801826855
    archived log for thread 1 with sequence 75 is already on disk as file +DATA/primdb/archivelog/2012_12_12/thread_1_seq_75.1085.801827543
    archived log for thread 1 with sequence 76 is already on disk as file +DATA/primdb/archivelog/2012_12_12/thread_1_seq_76.1086.801827547
    archived log file name=+DATA/primdb/archivelog/2012_12_12/thread_1_seq_71.1081.801824977 thread=1 sequence=71
    archived log file name=+DATA/primdb/archivelog/2012_12_12/thread_1_seq_72.1082.801824981 thread=1 sequence=72
    archived log file name=+DATA/primdb/archivelog/2012_12_12/thread_1_seq_73.1083.801826851 thread=1 sequence=73
    archived log file name=+DATA/primdb/archivelog/2012_12_12/thread_1_seq_74.1084.801826855 thread=1 sequence=74
    archived log file name=+DATA/primdb/archivelog/2012_12_12/thread_1_seq_75.1085.801827543 thread=1 sequence=75
    media recovery complete, elapsed time: 00:00:01
    Finished recover at 12-DEC-12
  17. Validate the database again.
  18. You must make sure the database is normal to be opened.

    RMAN> validate database;

    Starting validate at 12-DEC-12
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting validation of datafile
    channel ORA_DISK_1: specifying datafile(s) for validation
    input datafile file number=00001 name=+DATA/primdb/datafile/system.900.801828739
    input datafile file number=00002 name=+DATA/primdb/datafile/sysaux.898.797943569
    input datafile file number=00003 name=+DATA/primdb/datafile/undotbs1.899.797943687
    input datafile file number=00005 name=+DATA/primdb/datafile/example.902.801828959
    input datafile file number=00006 name=+DATA/primdb/datafile/undotbs2.901.797943737
    input datafile file number=00004 name=+DATA/primdb/datafile/users.897.801828959
    channel ORA_DISK_1: validation complete, elapsed time: 00:01:25
    List of Datafiles
    =================
    File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
    ---- ------ -------------- ------------ --------------- ----------
    1    OK     0              12289        88329           2940748
      File Name: +DATA/primdb/datafile/system.900.801828739
      Block Type Blocks Failing Blocks Processed
      ---------- -------------- ----------------
      Data       0              59414
      Index      0              12356
      Other      0              4261

    File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
    ---- ------ -------------- ------------ --------------- ----------
    2    OK     0              16262        83227           2940390
      File Name: +DATA/primdb/datafile/sysaux.898.797943569
      Block Type Blocks Failing Blocks Processed
      ---------- -------------- ----------------
      Data       0              18219
      Index      0              13461
      Other      0              35258

    File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
    ---- ------ -------------- ------------ --------------- ----------
    3    OK     0              2            14083           2940746
      File Name: +DATA/primdb/datafile/undotbs1.899.797943687
      Block Type Blocks Failing Blocks Processed
      ---------- -------------- ----------------
      Data       0              0
      Index      0              0
      Other      0              14078

    File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
    ---- ------ -------------- ------------ --------------- ----------
    4    OK     0              255          640             1153034
      File Name: +DATA/primdb/datafile/users.897.801828959
      Block Type Blocks Failing Blocks Processed
      ---------- -------------- ----------------
      Data       0              91
      Index      0              39
      Other      0              255

    File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
    ---- ------ -------------- ------------ --------------- ----------
    5    OK     0              1689         13204           2136990
      File Name: +DATA/primdb/datafile/example.902.801828959
      Block Type Blocks Failing Blocks Processed
      ---------- -------------- ----------------
      Data       0              4486
      Index      0              1261
      Other      0              5764

    File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
    ---- ------ -------------- ------------ --------------- ----------
    6    OK     0              1            6405            2936714
      File Name: +DATA/primdb/datafile/undotbs2.901.797943737
      Block Type Blocks Failing Blocks Processed
      ---------- -------------- ----------------
      Data       0              0
      Index      0              0
      Other      0              6399

    channel ORA_DISK_1: starting validation of datafile
    channel ORA_DISK_1: specifying datafile(s) for validation
    including current control file for validation
    including current SPFILE in backup set
    channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
    List of Control File and SPFILE
    ===============================
    File Type    Status Blocks Failing Blocks Examined
    ------------ ------ -------------- ---------------
    SPFILE       OK     0              2
    Control File OK     0              1188
    Finished validate at 12-DEC-12

    RMAN>
  19. Make sure the datafiles are restored physically.
  20. ASMCMD> ls -l
    Type      Redund  Striped  Time             Sys  Name
    DATAFILE  MIRROR  COARSE   DEC 12 10:00:00  Y    EXAMPLE.902.801828959
    DATAFILE  MIRROR  COARSE   DEC 12 10:00:00  Y    SYSAUX.898.797943569
    DATAFILE  MIRROR  COARSE   DEC 12 10:00:00  Y    SYSTEM.900.801828739
    DATAFILE  MIRROR  COARSE   DEC 12 10:00:00  Y    UNDOTBS1.899.797943687
    DATAFILE  MIRROR  COARSE   DEC 12 10:00:00  Y    UNDOTBS2.901.797943737
    DATAFILE  MIRROR  COARSE   DEC 12 10:00:00  Y    USERS.897.801828959
  21. Open the database again.
  22. SQL> alter database open;

    Database altered.

    SQL> select inst_id, open_mode, database_role, switchover_status from gv$database;

       INST_ID OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
    ---------- -------------------- ---------------- --------------------
             1 READ WRITE           PRIMARY          FAILED DESTINATION

    The database is opened normally without errors.

Please notice that, if most of the datafiles are damaged or missing, it's a safer way to restore and recover the whole database directly to avoid any human ignorance.

More related posts that you may be interested in:

Leave a Reply

Your email address will not be published. Required fields are marked *