There have been many customer situations where selective data has been accidentally or maliciously deleted from a production database, without damaging the entire database. The challenge is to restore the deleted data, while allowing other parts of the application to continue operating unaffected. Using normal database restore and recovery processes involve shutting down the entire database, which is highly undesirable, especially when only a limited subset of data is unavailable.
Using PowerFlex snapshots and a test/development server, you can instantly restore a database to a previous PiT, extract the deleted data, and insert it back into the production database, without affecting the availability of the production environment.
In this example, the database resides in four storage volumes, as shown in the following figure.
In this example, the volumes are SNAPDEMO_DATA1, SNAPDEMO_DATA2, SNAPDEMO_FRA1, and SNAPDEMO_REDO1.
Note: Real-life recovery processes would make use of a scheduled snapshot group. An explicit manual step for taking the snapshot is included here for clarity. 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 creates four new snapshots volumes. This is the snapshot set that you will use for the recovery in this use case.
In this example, all records with call_ids between 3,000,000 and 6,000,000 were deleted.
In this example, an additional 5,000,000 rows were inserted into the table. The table should have a total of 15,000,000 rows, but the 3,000,001 deleted earlier are still missing.
You can either take another manual snapshot of the database or use any scheduled snapshot that was taken after the recovery point.
The second set of snapshots is displayed.
Note: When using scheduled snapshots, the DATA volumes are from a snapshot taken before the data deletion, and the second snapshot is any snapshot taken after the recovery point.
# lsblk | grep scini
Use the following script to generate the UDEV entries for the snapshot devices (inserting the device names identified in step a)
# Simple script to generate UDEV entries
# Version 1.1
# Dell Technologies
# June 2021
# EDIT THE FOLLOWING LINE AND LIST ALL DEVICE NAMES
for i in scinif scinig scinih
c=$(expr $c + 1)
dev_id=$(/opt/emc/scaleio/sdc/bin/drv_cfg --query_block_device_id --block_device /dev/$i)
echo 'KERNEL=="scini*", SUBSYSTEM=="block", PROGRAM="/opt/emc/scaleio/sdc/bin/drv_cfg --query_block_device_id --block_device /dev/%k", RESULT=="'$dev_id'", SYMLINK+="oracleasm/snapdisk'$c'", OWNER="grid", GROUP="oinstall", MODE="0660"'
Example script output:
KERNEL=="scini*", SUBSYSTEM=="block", PROGRAM="/opt/emc/scaleio/sdc/bin/drv_cfg --query_block_device_id --block_device /dev/%k", RESULT=="3ab393750b18d00f-3d262a920000001c", SYMLINK+="oracleasm/snapdisk1", OWNER="grid", GROUP="oinstall", MODE="0660"
KERNEL=="scini*", SUBSYSTEM=="block", PROGRAM="/opt/emc/scaleio/sdc/bin/drv_cfg --query_block_device_id --block_device /dev/%k", RESULT=="3ab393750b18d00f-3d262a940000001e", SYMLINK+="oracleasm/snapdisk2", OWNER="grid", GROUP="oinstall", MODE="0660"
Append the output from the above script into the PowerFlex UDEV rules file (e.g. /etc/udev/rules.d/99-oracleasm.rules) and reload the UDEV rules
# /sbin/udevadm control --reload-rules;/sbin/udevadm trigger
The control file needs to be re-created to facilitate an incomplete recovery of the database. This is because the objective is to recover the database only to the PIT just prior to the data being deleted.
This will automatically generate the archive logs file names required for the recovery and recover the database to the SCN specified.
These were the known deleted rows from the production database. Note that in a real-world scenario, the exact damage may not be known, as is the case in this scenario. However, using the reconstructed tables before the damage and the production tables after the damage allows a detailed investigation to be carried out to determine the missing data.