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.

Use Case 2: Set up the scenario

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

2. In PowerFlex, under Configuration > Volumes, identify the storage volumes where the database resides.

In this example, the database resides in four storage volumes, as shown in the following figure.

3. To create a snapshot of the database, select all volumes that the database resides in, including DATA, REDO, and FRA disk groups. 
4. 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.

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.

5. After you take the snapshot, insert additional rows into the table and check the row count.

6. Record the time. You will use this as the recovery point.

7. Simulate the deletion of rows from the tables.

In this example, all records with call_ids between 3,000,000 and 6,000,000 were deleted.

8. To simulate the application continuing to operate, insert more rows into the table.

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. 

9. 
   Create a control file script in preparation of the recovery steps

Use Case 2: Run the recovery 

You can either take another manual snapshot of the database or use any scheduled snapshot that was taken after the recovery point.

1. Take a second snapshot of the database. Make sure you select the root (or source) volumes for the database, not other snapshots. 

2. For the second snapshot, under Index, select a different suffix. 

The second set of snapshots is displayed.

3. Map the DATA volumes from the first snapshot and map the FRA and REDO volumes from the second snapshot to a test or development server. This allows rolling the database forward to the desired PiT, after the initial snapshot but before the deletion of data.

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.

4. On the test/development server, we will identify the newly presented snapshot volumes, update the UDEV rules file, and reload the rules. Note: If using ASMLib, follow the steps in the Oracle documentation to create the ASMLib devicesIdentify the newly presented PowerFlex Snapshot devices

# lsblk | grep scini 

Use the following script to generate the UDEV entries for the snapshot devices (inserting the device names identified in step a)

#!/bin/ksh

#

# 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 

do

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"'

done

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

5. On the test/development server, mount the ASM disk groups using the grid user. 

6. From the Oracle software owner, start the database in mount mode and recreate the control file.

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.

7. On the production environment, identify the System Change Number (SCN) for the recovery point in time. 

8. On the test/development server, recover the database to the SCN identified in  step 7.

9. When prompted, specify AUTO for the logfile suggestions.

This will automatically generate the archive logs file names required for the recovery and recover the database to the SCN specified.

10. Open the database and reset the logs, which are mandatory after an incomplete recovery.

11. At this point the deleted rows should be in the reconstructed databases.

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.

12. Export the required rows from the test system.

13. Transfer the dump file to the production system and import back into the database. 

14. Check that the deleted rows have been reinstated in the production database.