TSPITR Using ACFS Snapshots

Starting with Oracle Grid Infrastructure 12c (12.1), Oracle Cloud File system (ASM Cluster File System in cluster environment) supports database files (database version 11.2.0.4 and up) in addition to general-purpose files so that entire Oracle databases can be stored inside Oracle Cloud FS. In my earlier articles I demonstrated that: For a database having its files stored on Oracle Cloud

Starting with Oracle Grid Infrastructure 12c (12.1), Oracle Cloud File system (ASM Cluster File System in cluster environment) supports database files (database version 11.2.0.4 and up) in addition to general-purpose files so that entire Oracle databases can be stored inside Oracle Cloud FS. In my earlier articles I demonstrated that:

  • For a database having its files stored on Oracle Cloud file system, Oracle ACFS Snapshots may serve as point-in-time backups of the database which can be used for online recovery of database files.
  • ACFS snapshots taken while the database is in backup mode can be integrated with RMAN and employed to perform complete recovery using RMAN RESTORE / RECOVER commands.

In this article, I will demonstrate that ACFS snapshots taken while the database is in backup mode can be integrated with RMAN and employed to perform Tablespace Point In Time Recovery (TSPITR) as well.

Currently I am working in Oracle Database 12.1.0.2 cluster environment and have created a database named cfsdb, with all of its files stored on the cloud file system as shown below:

Let’s confirm that the database cfsdb is in archivelog mode:

Create a new tablespace named TEST with its data file stored on Oracle Cloud file system:

Create a table named HR.EMP with 107 rows in the TEST tablespace:

Take a backup of the control file:

Verify that currently there are no snapshots of the cloud file system hosting the database files:

Take a snapshot (example_snap) of the cloud file system while the database is in backup mode:

Check that all the datafiles are available in the snapshot:

Catalog all the data files in the snapshot example_snap in directory “/mnt/acfs/.ACFS/snaps/example_snap/oradata/cfsdb/“:

It can be seen that all the data file copies have been catalogued whereas redo log files, SPfile and control file have not been registered with RMAN.

Check the current table has 107 rows:

Create a restore point EMP_107:

Insert records into HR.EMP so that there are now 214 records:

Perform TSPITR for TEST tablespace until restore point EMP_107 when there were 107 records in HR.EMP:

Verify that TSPITR to restore point EMP_107 is successful and that there are 107 records in HR.EMP now:

Thus, we have been able to perform TSPITR by employing the RMAN “RECOVER TABLESPACE UNTIL…” command which utilized:

  • Backup of control file to restore the control file
  • A snapshot taken while the database was in backup mode to restore the datafiles for the “TEST”, “SYSTEM”, “UNDOTBS1”, and “SYSAUX” tablespaces.

Conclusion:

  • Starting with Oracle Grid Infrastructure 12c (12.1), ACFS supports database files in cluster environment so that entire Oracle databases can be stored inside a cloud file system.
  • ACFS snapshots taken while the database is in backup mode can be integrated with RMAN and employed to perform TSPITR using RMAN “RECOVER TABLESPACE UNTIL…” command.