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.

List the backup we have now.


[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

Shutdown all instances of the primary RAC, and make sure the standby database cannot be reached by anyone beside sysdba.


[oracle@primary01 ~]$ srvctl stop database -d compdb

Remove datafiles to simulate the missing of datafiles.


[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

Open only one instance of the primary RAC.


[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.

Validate the database by RMAN.

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

Restore and recover datafile 1.


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

Validate the database again.

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

Restore and recovery datafile 4,5.

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

Validate the database again.

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>

Make sure the datafiles are restored physically.


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

Open the database again.


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:

When Several Datafiles Are Missing - Restore From Local Backups

Leave a Reply Cancel reply