Your Browser is Out of Date

Nytro.ai uses technology that works best in other browsers.
For a full experience use one of the browsers below
Dell EMC PowerFlex – Exploiting Snapshots for Oracle Database Backup and Recovery
Executive Summary Introduction Dell EMC PowerFlex appliance Oracle support for storage snapshots Oracle snapshot use cases
About the use cases
About the use case system architecture
Prerequisites
Use Case 1: Full recovery from corrupted database, with zero data loss
Use Case 2: Surgical recovery after an accidental or malicious data deletion
Use Case 3: Offloading Database Backups
Conclusion References

Use Case 1: Full recovery from corrupted database, with zero data loss

Use Case 1: Full recovery from corrupted database, with zero data loss

  • While the exact process may vary from database to database, for a typical database operation, a full or incremental backup is taken nightly, with regular backups of the archive logs about every four hours throughout the day. The recovery process requires restoring the last full backup followed by the required incremental backups. You then apply the incremental backups and archived logs to recover the database back to the current point-in-time (PiT). This is a time-consuming process and can extend the period that the database is unavailable. 

    By doing regular database snapshots using PowerFlex Storage Snapshot functionality, you can facilitate fast recovery from certain database failure or corruption scenarios. The recovery time depends on the regularity of the snapshots and the database change rate. However, a lengthy restore from a remote backup location is not required because the snapshots can be restored instantaneously. You only need to apply the archive logs and online redo logs generated since the last snapshot. This results in a simplified and rapid recovery process with minimal steps required.

    Use Case 1: Set up the scenario 

    1. In the Oracle SQL Command Line, create a test table, insert some rows, and check the table.

    1. To create a snapshot of the database, in PowerFlex, under Configuration > Volumes, select all the volumes that the database resides in, including DATA, REDO, and FRA disk groups. 

    Simultaneously taking snapshots of all contributing PowerFlex volumes automatically creates a Snapshot Consistency Group.

    In this example, the volumes are SNAPDEMO_DATA1, SNAPDEMO_DATA2, SNAPDEMO_FRA1, and SNAPDEMO_REDO1.

    1. To create a snapshot group, select all the volumes and select Create Snapshot from the More menu.

    You can use the default snapshot name and suffix or create a custom name format. For snapshots that are only required for recovery purposes, we recommend that you ensure the Read Only option is selected (default).

    The following dialog displays, and the snapshots are created with the names and suffixes specified.

    The system instantaneously creates four new snapshots volumes. This is the snapshot set that you will use for doing recovery in this use case. 

    1. After creating the recovery snapshots, return to the Oracle SQL Command Line and insert more rows into the table. 

    The table DEMO_TAB now has 20,000,000 committed rows.

    You can simulate a corruption or media fault in the database by deleting one of the databases data files. Subsequently, a database restart will show an error as shown in the following figure:

    Use Case 1: Run the recovery

    1. In PowerFlex, under Configuration > Volumes, identify the latest snapshot volumes that are associated with the database. 

    1. Shut down the database instance.
    2. In Oracle SQL Command Line, identify the ASM disk groups that hold the data files for the affected database. 

    In the current use case, the data files are stored in the SNAPDEMO_DATA disk group.

    1. Unmount the ASM disk groups comprising the data files, and then confirm that the state is DISMOUNTED.

    1. In PowerFlex, restore only the snapshot volumes that comprise the affected ASM disk group containing the damaged data files. 

    Note: While all the data disk groups and volumes can be restored from their respective snapshots, this requires more media recovery and takes longer. Our recommendation is that you restore only the snapshots that are required. Do not restore the REDO and FRA disk groups.

    1. From PowerFlex, check the required volumes one at a time and then select Overwrite Content from the More menu to restore volumes from a snapshot.

    The following verification dialog is displayed:

    1. Select the latest snapshot, and then click Next.

    A final verification dialog is displayed. 

    1. To immediately overwrite the source volume, click Overwrite Content.
    2. Repeat the previous steps for all volumes comprising any ASM data disk group that must be restored. 

    Note: All the above steps from the PowerFlex GUI can also be performed using the PowerFlex CLI. See Dell EMC PowerFlex v3.5.x CLI Reference Guide. 

    1. In Oracle SQL Command Line, mount the disk group and verify that the ASM disk group state is set to MOUNTED. 

    1. Restart the database in MOUNT mode and run automatic database recovery.

    1. Open the database and check the contents of the demo_tab table. 

    This confirms that the snapshot (taken at 10,000,000 rows) has been restored, and that the database was also rolled forward to the latest committed transaction in the test database.

    The process described here takes only a few minutes, but the exact amount of time depends on the number of archived logs that need to be applied, which is a function of the frequency of the snapshot intervals and the redo rate of the database.