Databases@Powered by Passion

Delete Orphaned Datafile Copies

Oracle Incrementally Updated Backups : Detect and Delete Orphaned (Expired) Datafile Copies

—

by

Osman DİNÇ
, ,

What is Oracle Recommended Disk Backup Strategy?

Oracle recommends adopting a merged incremental disk backup strategy to ensure efficient database protection and faster recovery times. This approach involves maintaining an up-to-date image copy of the database as the base backup while continuously applying incremental backups to it on the fly.

Key Considerations:

When using the Oracle recommended disk backup strategy, the retention policy should be set to REDUNDANCY 1, and the image copy should ideally be created within the Fast Recovery Area (FRA). FRA provides a centralized and managed location for backup and recovery files. Block Change Tracking (BCT) is highly recommended as it enhances incremental backup efficiency by recording changes to database blocks since the last backup, eliminating the need to scan the entire database during backup operations. Cumulative incremental backups should be taken and applied daily to roll forward the base backup.

We use a script similar to provided below.

connect catalog /@catdb
connect target /
RUN
{
  SQL 'alter system checkpoint global';
  ALLOCATE CHANNEL cblt1 device type DISK;
  ALLOCATE CHANNEL cblt2 device type DISK;
  BACKUP CHECK LOGICAL
      INCREMENTAL LEVEL 1
      FOR RECOVER OF COPY WITH TAG 'v'
      DATABASE;
   RECOVER COPY OF DATABASE
      WITH TAG 'bltdb_update';
   RELEASE CHANNEL cblt1;
   RELEASE CHANNEL cblt2;
}
allocate channel for maintenance type DISK;
run
{
  # delete incremental backups taken to apply on image copy
  delete noprompt backup tag 'bltdb_update';
}
release channel;
exit

This strategy enables quick failover using the SWITCH DATABASE TO COPY command, allowing the database to point directly to the updated image copy without requiring a restore operation. Fewer archive logs are needed for recovery since the image copy is consistently rolled forward. For instance, only logs from the last 24 hours might be necessary if incremental updates are applied daily.

However, maintaining a full image copy of the database requires additional disk space, which may be a limitation for larger databases. Another limitation is that point-in-time recovery is restricted to dates after the last incremental backup was applied to the image copy. For example, if the image copy is updated daily, recovery to a point earlier than 24 hours may require alternative backup strategies.

This strategy works best when the retention policy is set to REDUNDANCY 1. If a different retention policy, such as RECOVERY WINDOW, is used, RMAN may not satisfy the policy with only one up-to-date image copy. To address this, DBAs need to create custom scripts to retain older versions of the image copy. For example, if a recovery window of seven days is required, the image copy must be retained for at least eight days using the UNTIL clause.

The database copy cannot be compressed or encrypted.

I want to share another important point I recently encountered, which I believe is often forgotten.

When you drop a tablespace or delete a datafile in Oracle, the datafiles associated with the base image copy are not automatically removed from the file system or ASM (Automatic Storage Management).

While the original datafile is dropped during the operation, its corresponding backup or image copy remains intact in the backup location. This behavior ensures that any backups, particularly those created for recovery purposes, are preserved unless explicitly removed. Since backup policies are typically written once and not revisited, these datafile copies are often forgotten in fast recovery area

To handle these leftover image copies, you must either manually delete the files from the file system or use RMAN commands to manage them. In our case, there were 20 datafiles leftover in the Fast Recovery Area. You can also detect the anomaly by comparing the count from v$datafile with the number_of_files value in v$recovery_area_usage.

SQL> select count(*) as datafile_count from v$datafile

DATAFILE_COUNT

————————————

298

SQL> select number_of_files from v$recovery_area_usage where file_type = ‘IMAGE COPY’;

NUMBER_OF_FILES

—————————————-

318

After identifying discrepancies in the number of datafiles between the database and the image copy, you can compare the outputs of v$datafile and v$datafile_copy, or use the RMAN command LIST DATAFILECOPY ALL.

You may delete the current database copy and take a new base backup. However, this method will require backing up all datafiles again, without utilizing incremental backups, which may result in a longer backup duration, especially for large databases. Alternatively, you can manually remove the leftover datafiles from the file system or ASM using the rm command. Afterward, perform a crosscheck in RMAN to detect the absence of these datafile copies. Then, you can run the DELETE EXPIRED command to remove the metadata of these backups from the catalog or control file.

You may feel comfortable deleting them once you’re certain that you’re in the fast_recovery_area and not in the db_create_file_dest location.

Step 1: Manually remove copy of datafiles from file system or ASM.

ASMCMD> rm -f +RECO/BLTDB/DATAFILE/AI_APP.8889.1062728795
ASMCMD> rm -f +RECO/BLTDB/DATAFILE/AI_APP.28091.1070850253
ASMCMD> rm -f +RECO/BLTDB/DATAFILE/AI_APP.11430.1065147933
ASMCMD> rm -f +RECO/BLTDB/DATAFILE/AI_DOC.10223.1062123865
ASMCMD> rm -f +RECO/BLTDB/DATAFILE/AI_DOC.28060.1070850307
ASMCMD> rm -f +RECO/BLTDB/DATAFILE/AI_DOC.21758.1067221723
ASMCMD> rm -f +RECO/BLTDB/DATAFILE/AI_APP.12171.1070158731
ASMCMD> rm -f +RECO/BLTDB/DATAFILE/AI_APP.1518.1070763355
ASMCMD> rm -f +RECO/BLTDB/DATAFILE/AI_APP.3629.1070158867
ASMCMD> rm -f +RECO/BLTDB/DATAFILE/AI_APP.8275.1070763527
ASMCMD> rm -f +RECO/BLTDB/DATAFILE/AI_APP.27009.1067221785
ASMCMD> rm -f +RECO/BLTDB/DATAFILE/AI_APP.28036.1070850403
ASMCMD> rm -f +RECO/BLTDB/DATAFILE/AI_APP.10100.1062729049
ASMCMD> rm -f +RECO/BLTDB/DATAFILE/AI_APP.27032.1067221815
ASMCMD> rm -f +RECO/BLTDB/DATAFILE/AI_BI.15964.1060913539
ASMCMD> rm -f +RECO/BLTDB/DATAFILE/AI_ARCHIVE.17004.1121941201
ASMCMD> rm -f +RECO/BLTDB/DATAFILE/AI_ARCHIVE.7277.1121941227
ASMCMD> rm -f +RECO/BLTDB/DATAFILE/AI_APP.28002.1070850463
ASMCMD> rm -f +RECO/BLTDB/DATAFILE/AI_BI.25410.1062728149
ASMCMD> rm -f +RECO/BLTDB/DATAFILE/AI_BI.9995.1062728529

Step 2: Crosscheck Copies of Datafiles in RMAN to Detect Their Absence

RMAN>  crosscheck datafilecopy all;

    released channel: ORA_DISK_1
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=1011 instance=BLTDB1 device type=DISK
    ...
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.8889.1062728795 RECID=787905 STAMP=1117246444
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.28091.1070850253 RECID=787914 STAMP=1117246667
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.11430.1065147933 RECID=787909 STAMP=1117246552
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_nsn.10824.1065060539 RECID=794004 STAMP=1118628343
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_nsn.28084.1070850253 RECID=794005 STAMP=1118628370
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_nsn.19463.1070158163 RECID=794006 STAMP=1118628392
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_nsn.22174.1070762791 RECID=794007 STAMP=1118628395
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_nsn.11939.1070762839 RECID=794002 STAMP=1118628281
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_DOC.10223.1062123865 RECID=794009 STAMP=1116122711
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_DOC.28060.1070850307 RECID=794008 STAMP=1116122705
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_DOC.21758.1067221723 RECID=794010 STAMP=1116122799
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.12171.1070158731 RECID=787908 STAMP=1117246548
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.1518.1070763355 RECID=787911 STAMP=1117246615
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.3629.1070158867 RECID=787912 STAMP=1117246616
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.8275.1070763527 RECID=787907 STAMP=1117246506
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.27009.1067221785 RECID=787913 STAMP=1117246623
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.28036.1070850403 RECID=787903 STAMP=1117246412
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.10100.1062729049 RECID=787906 STAMP=1117246445
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.27032.1067221815 RECID=787904 STAMP=1117246419
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_BI.15964.1060913539 RECID=787924 STAMP=1117419412
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_ARCHIVE.17004.1121941201 RECID=794062 STAMP=1121997882
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_ARCHIVE.7277.1121941227 RECID=794099 STAMP=1121997943
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.28002.1070850463 RECID=787910 STAMP=1117246589
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_BI.25410.1062728149 RECID=787919 STAMP=1117419302
    validation failed for DATAFILE copy
    DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_BI.9995.1062728529 RECID=787916 STAMP=1117419158
    validation failed for DATAFILE copy
    Crosschecked .... objects

Step 3: Delete All Datafile Copies with Expired Status


RMAN>  delete expired datafilecopy all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1011 instance=BLTDB1 device type=DISK
List of DATAFILE Copies
=======================

Key     File S Completion Time     Ckp SCN    Ckp Time            Sparse
------- ---- - ------------------- ---------- ------------------- ------
787905  14   X 05/10/2022 02:14:04 646467676565 05/10/2022 02:01:24 NO
        Name: +RECO/BLTDB/DATAFILE/AI_APP.8889.1062728795
        Tag: BLTDB_UPDATE

787914  15   X 05/10/2022 02:17:47 646471245743 05/10/2022 02:06:21 NO
        Name: +RECO/BLTDB/DATAFILE/AI_APP.28091.1070850253
        Tag: BLTDB_UPDATE

787909  16   X 05/10/2022 02:15:52 646469312722 05/10/2022 02:03:39 NO
        Name: +RECO/BLTDB/DATAFILE/AI_APP.11430.1065147933
        Tag: BLTDB_UPDATE

794009  25   X 23/09/2022 02:05:11 635420336207 23/09/2022 02:01:24 NO
        Name: +RECO/BLTDB/DATAFILE/AI_DOC.10223.1062123865
        Tag: BLTDB_UPDATE

794008  26   X 23/09/2022 02:05:05 635420356485 23/09/2022 02:01:26 NO
        Name: +RECO/BLTDB/DATAFILE/AI_DOC.28060.1070850307
        Tag: BLTDB_UPDATE

794010  27   X 23/09/2022 02:06:39 635421430800 23/09/2022 02:02:51 NO
        Name: +RECO/BLTDB/DATAFILE/AI_DOC.21758.1067221723
        Tag: BLTDB_UPDATE

787908  72   X 05/10/2022 02:15:48 646469312722 05/10/2022 02:03:39 NO
        Name: +RECO/BLTDB/DATAFILE/AI_APP.12171.1070158731
        Tag: BLTDB_UPDATE

787911  76   X 05/10/2022 02:16:55 646471245743 05/10/2022 02:06:21 NO
        Name: +RECO/BLTDB/DATAFILE/AI_APP.1518.1070763355
        Tag: BLTDB_UPDATE

787912  83   X 05/10/2022 02:16:56 646471245743 05/10/2022 02:06:21 NO
        Name: +RECO/BLTDB/DATAFILE/AI_APP.3629.1070158867
        Tag: BLTDB_UPDATE

787907  91   X 05/10/2022 02:15:06 646469312722 05/10/2022 02:03:39 NO
        Name: +RECO/BLTDB/DATAFILE/AI_APP.8275.1070763527
        Tag: BLTDB_UPDATE

787913  107  X 05/10/2022 02:17:03 646471245743 05/10/2022 02:06:21 NO
        Name: +RECO/BLTDB/DATAFILE/AI_APP.27009.1067221785
        Tag: BLTDB_UPDATE

787903  116  X 05/10/2022 02:13:32 646467697821 05/10/2022 02:01:26 NO
        Name: +RECO/BLTDB/DATAFILE/AI_APP.28036.1070850403
        Tag: BLTDB_UPDATE

787906  155  X 05/10/2022 02:14:05 646467697821 05/10/2022 02:01:26 NO
        Name: +RECO/BLTDB/DATAFILE/AI_APP.10100.1062729049
        Tag: BLTDB_UPDATE

787904  158  X 05/10/2022 02:13:39 646467676565 05/10/2022 02:01:24 NO
        Name: +RECO/BLTDB/DATAFILE/AI_APP.27032.1067221815
        Tag: BLTDB_UPDATE

787924  163  X 07/10/2022 02:16:52 648202203475 07/10/2022 02:07:07 NO
        Name: +RECO/BLTDB/DATAFILE/AI_BI.15964.1060913539
        Tag: BLTDB_UPDATE

794062  181  X 29/11/2022 02:04:42 675849807582 29/11/2022 02:01:28 NO
        Name: +RECO/BLTDB/DATAFILE/AI_ARCHIVE.17004.1121941201
        Tag: BLTDB_UPDATE

794099  182  X 29/11/2022 02:05:43 675849931505 29/11/2022 02:02:16 NO
        Name: +RECO/BLTDB/DATAFILE/AI_ARCHIVE.7277.1121941227
        Tag: BLTDB_UPDATE

787910  195  X 05/10/2022 02:16:29 646470113997 05/10/2022 02:04:45 NO
        Name: +RECO/BLTDB/DATAFILE/AI_APP.28002.1070850463
        Tag: BLTDB_UPDATE

787919  221  X 07/10/2022 02:15:02 648200233319 07/10/2022 02:04:22 NO
        Name: +RECO/BLTDB/DATAFILE/AI_BI.25410.1062728149
        Tag: BLTDB_UPDATE

787916  263  X 07/10/2022 02:12:38 648198459434 07/10/2022 02:01:33 NO
        Name: +RECO/BLTDB/DATAFILE/AI_BI.9995.1062728529
        Tag: BLTDB_UPDATE
    
Do you really want to delete the above objects (enter YES or NO)? YES
deleted DATAFILE copy
DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.8889.1062728795 RECID=787905 STAMP=1117246444
deleted DATAFILE copy
DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.28091.1070850253 RECID=787914 STAMP=1117246667
deleted DATAFILE copy
DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.11430.1065147933 RECID=787909 STAMP=1117246552
deleted DATAFILE copy
DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_DOC.10223.1062123865 RECID=794009 STAMP=1116122711
deleted DATAFILE copy
DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_DOC.28060.1070850307 RECID=794008 STAMP=1116122705
deleted DATAFILE copy
DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_DOC.21758.1067221723 RECID=794010 STAMP=1116122799
deleted DATAFILE copy
DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.12171.1070158731 RECID=787908 STAMP=1117246548
deleted DATAFILE copy
DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.1518.1070763355 RECID=787911 STAMP=1117246615
deleted DATAFILE copy
DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.3629.1070158867 RECID=787912 STAMP=1117246616
deleted DATAFILE copy
DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.8275.1070763527 RECID=787907 STAMP=1117246506
deleted DATAFILE copy
DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.27009.1067221785 RECID=787913 STAMP=1117246623
deleted DATAFILE copy
DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.28036.1070850403 RECID=787903 STAMP=1117246412
deleted DATAFILE copy
DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.10100.1062729049 RECID=787906 STAMP=1117246445
deleted DATAFILE copy
DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.27032.1067221815 RECID=787904 STAMP=1117246419
deleted DATAFILE copy
DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_BI.15964.1060913539 RECID=787924 STAMP=1117419412
deleted DATAFILE copy
DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_ARCHIVE.17004.1121941201 RECID=794062 STAMP=1121997882
deleted DATAFILE copy
DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_ARCHIVE.7277.1121941227 RECID=794099 STAMP=1121997943
deleted DATAFILE copy
DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_APP.28002.1070850463 RECID=787910 STAMP=1117246589
deleted DATAFILE copy
DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_BI.25410.1062728149 RECID=787919 STAMP=1117419302
deleted DATAFILE copy
DATAFILE copy file name=+RECO/BLTDB/DATAFILE/AI_BI.9995.1062728529 RECID=787916 STAMP=1117419158
deleted DATAFILE copy
Deleted 20 EXPIRED objects

Conclusion

Implementing a merged incremental disk backup strategy ensures efficient database protection and faster recovery by maintaining an up-to-date image copy as the foundation for incremental updates. Additionally, DBAs must remain proactive in managing orphaned datafiles after tablespace deletions to keep the base image copy synchronized with the database and prevent unnecessary storage consumption.

Hope it helps.

Discover More from Osman DİNÇ

Comments

Leave your comment