7 Replies Latest reply on Feb 21, 2013 7:19 AM by Dude!

    Delete a datafile in 11gR2 ASM

    568397
      Here is the current datafiles of the database 11gR2, 11.2.0.1 on the ASM file system.

      SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;

      FILE_NAME TABLESPACE_NAME
      ------------------------------------------------------- ------------------------------
      +DATA01/heat/datafile/testtbs.274.807968419             TESTTBS
      +DATA01/heat/datafile/testtbs.266.807968493             TESTTBS
      +DATA01/heat/datafile/testtbs.258.807968511             TESTTBS

      1/ Use RMAN to backup the whole db onto the disk
      2/ In RMAN, check the backup: List backup; See all the backup including the above data files;
      3/ Run this cmd to drop 1 datafile, In SQL> ALTER TABLESPACE TESTTBS drop datafile '+DATA01/heat/datafile/testtbs.258.807968511';
      4/ Verify it, whether that data file deleted, yes it was deleted.

      SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;

      FILE_NAME TABLESPACE_NAME
      ------------------------------------------------------- ------------------------------
      +DATA01/heat/datafile/testtbs.274.807968419             TESTTBS
      +DATA01/heat/datafile/testtbs.266.807968493             TESTTBS

      5/ In RMAN, check the backup again: List backup; It's not there in the backup. Why's that ?????????????????????
      BS Key Type LV Size Device Type Elapsed Time Completion Time
      ------- ---- -- ---------- ----------- ------------ --------------------
      6 Full 935.09M DISK 00:01:15 21-FEB-2013 11:43:44
      BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20130221T114228
      Piece Name: +FRA01/heat/backupset/2013_02_21/nnndf0_tag20130221t114228_0.344.807968549
      List of Datafiles in backup set 6
      File LV Type Ckp SCN Ckp Time Name
      ---- -- ---- ---------- -------------------- ----
      1 Full 999944 21-FEB-2013 11:42:29 +DATA01/heat/datafile/system.256.807898985
      2 Full 999944 21-FEB-2013 11:42:29 +DATA01/heat/datafile/sysaux.261.807898985
      3 Full 999944 21-FEB-2013 11:42:29 +DATA01/heat/datafile/undotbs1.260.807898985
      4 Full 999944 21-FEB-2013 11:42:29 +DATA01/heat/datafile/users.268.807898985
      5 Full 999944 21-FEB-2013 11:42:29 +DATA01/heat/datafile/testtbs.274.807968419
      6 Full 999944 21-FEB-2013 11:42:29 +DATA01/heat/datafile/testtbs.266.807968493
      7 Full 999944 21-FEB-2013 11:42:29

      6/ Try restore the deleted data file, it's not available in the backup ????? I do not understand this ? Really appreciate someone could explain this ?????
        • 1. Re: Delete a datafile in 11gR2 ASM
          Hemant K Chitale
          RMAN's "LIST BACKUP" command maps the backup to the actual datafile in the database.
          Since the datafile no longer exists in the database (the DROP DATAFILE command having removed it's entry from the controlfile), there is no datafile to map to. Oracle wouldn't restore that backup either because there is no "target" to restore it to.


          Note : Also remember that datafile FILE_IDs can be reused after datafiles and tablespaces are dropped and new tablespaces and datafiles are added to a database. It would be incorrect to restore the wrong (dropped) file instead of the new datafile that may have been added with the same FILE_ID.


          Hemant K Chitale
          • 2. Re: Delete a datafile in 11gR2 ASM
            568397
            I have a backup which shows the data file was included in the backup before I deleted the data file.
            My question is how do I get it back ?
            • 3. Re: Delete a datafile in 11gR2 ASM
              Hemant K Chitale
              It is no longer part of the database. It does not belong to the database. It cannot be "restored" to the database.

              Hemant K Chitale
              • 4. Re: Delete a datafile in 11gR2 ASM
                568397
                As a DBA, I MUST be able to restore from the backup, right ????
                • 5. Re: Delete a datafile in 11gR2 ASM
                  Hemant K Chitale
                  If you want to restore that file, you must restore the whole database to the point in time just before the datafile was lost.


                  Oracle always tries to keep a database consistent. If the current database image and controlfile say that "file 7 is not part of the database", it will not allow file 7 to be restored to the current database.
                  You would have to restore the controlfile and the whole database to the point before file 7 was dropped -- only then will the database be consistent. (Note that this means that any changes to the database after the drop of file 7 are lost !)


                  Hemant K Chitale
                  1 person found this helpful
                  • 6. Re: Delete a datafile in 11gR2 ASM
                    568397
                    Thanks. It's getting clearer now but it's not good.

                    Because I want to restore only the datafile. This can be done before in Oracle without using ASM.
                    • 7. Re: Delete a datafile in 11gR2 ASM
                      Dude!
                      When you drop a datafile, the database instance updates controlfiles, redologs and SCN numbers. The datafile you have in your backup will no longer match the the rest of the database environment, so it cannot be restored without restoring the complete database prior to the time the datafile was dropped. Similarly you cannot mount or restore the datafile of a different database. The database does not maintain recovery information for a datafile that does not exist. If you loose the datafile, e.g. delete it from the filesystem, you can restore the datafile and recover the information using the existing controlfile and redologs. That is all normal and is not influenced by ASM.