This is a guest blog by Gur Rosen, Solutions Engineer in the Kaminario Engineering team. Gur documents his recent experience running a test for a customer to validate Oracle’s backup and restore functionality with Kaminario K2 VisionOS Snapshots.
A customer recently asked us if we support Oracle’s Backup and Restore options for Creating Backups with Third-Party Snapshot Technologies. As K2 VisionOS application-consistent snapshots are created extremely easily, we had not seen the need to test Oracle’s options for creating and restoring from a crash-consistent snapshot. Given this most recent query, I decided to take a few hours to run the procedure to find out if it actually works (Spoiler alert: It does!) In order to test this backup and restore option, a simple table was defined in our 12c database and two different scripts were created to support the test.
- The table contains just a single date column.
- The first script, “insert.sql”, is a simple SQL script that inserts a single row into the destination table, from the current Oracle database host sysdate, and commits it.
- The second script, “insert.sh”, is a simple bash script that constantly runs the insert.sql script in batches of five simultaneous calls.
In addition to the scripts and table, the Oracle database was deployed in the K2 array as described in our Oracle database best practices document. The Oracle data files, redo log files and archive files are separated to three different Volume Groups, each containing its own volumes. Oracle supports either a complete database recovery or a point-in-time recovery, and as such, it made sense to run two different tests:
- A complete restore of the database.
- Restoring the database to a specific point-in-time.
While running the insert script, we created several crash-consistent snapshots in the K2 array. After creating four different crash-consistent snapshots (named snap1 to snap4) with approximately 15 seconds between them, the insert script was halted and the actual test was started.
Test 1: A complete restore database test
The test starts with shutting down the database. To avoid adding more complexity to the test, we wanted the restore volume operation to run when the database was down. We restore the database data files volume to snap2, which results in an inconsistent database. The REDO log files and the archive files were consistent to the time of the database shutdown, while the database data files were missing all the data that came after taking the snapshot. Starting the database obviously failed and a recover was needed. At this point, we initiate SQL*Plus and ran the RECOVER DATABASE command. These operations completed quickly and running the command ALTER DATABASE OPEN resulted in a start that had no errors.
As expected, all the table data was there and the database was consistent – a successful first test without any hiccups!
Test 2: Restore the database to a point-in-time
This test is a bit more complicated. Oracle has a set of rules that must be taken into account before running the recover command (the rules can be found here – Recovery Using Storage Snapshot Optimization). The recover command should include the UNTIL TIME clause, the time at which we want the database to be recovered, and in addition, the SNAPSHOT TIME clause, the time at which the storage snapshot was created – keep in mind that this has to be consistent with the time reported by Oracle and not the K2. In our case, this wasn’t such a problem as both the storage device and the Oracle server sync from the same NTP server.
As with the previous test, we restore the datafiles to a previous snapshot, snap2 (after shutting down the database) and attempt to start the database. As expected, the startup fails. In the RECOVER… SNAPSHOT TIME command, SNAPSHOT TIME is set to a point-in-time after snap2 was taken, but before the database was stopped. The command completes successfully and a RESETLOGS clause has to be added to the ALTER DATABASE OPEN command. Checking the data on the table proves that only the expected data was recovered. Both of these tests confirm that crash-consistent snapshots can be used recover the Oracle database.
Having run these tests, it made sense to investigate another recover option that comes to mind – what would happen if a snapshot time of 30 seconds was specified in the RECOVER … SNAPSHOT TIME command after the actual point- in-time of the snapshot? Will there be 30 seconds data gap? We imagined that Oracle would restrict data from disappearing from the database and after testing, this was confirmed. Oracle has checks and balances built in to ensure that nothing was missing from the table. Feel free to drop us a line if you have questions on the testing or need validation of any other tests.
We were glad to have helped out this customer and proud to add another Oracle Solution to our extensive K2 library.