12c: Database Backups 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 and can leverage the Advanced Data Services such as snapshots, tagging and auditing although replication and… Continue Reading →

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 and can leverage the Advanced Data Services such as snapshots, tagging and auditing although replication and encryption are not supported in conjunction with datafiles.

The ability to perform snapshots of ACFS file system is a very powerful feature. An Oracle ACFS Snapshot is a read-write or read-only, space efficient, point-in-time copy of a file system which is immediately available for use after it is created and is always online while the file system is mounted.

The snapshot copy is initially sparse as it merely references the storage allocation information maintained by the source file system. A snapshot utilizes a Copy-On-Write (COW) technology and maintains point in time view of the file system. Whenever an extent is modified by the user, the current extent is copied to the snapshot before modifying the source file extent. When a recovery is required, only the changed blocks are replaced by their “before images” stored in the snapshot.

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.

Moreover, with Oracle Database 12c, Cloud FS supports Snaps-of-Snaps feature so that snapshots of existing snapshot of the same ACFS file system may be created. Any combination of read-only and read-write snapshots is supported i.e. a read-write snapshot can be based on an existing read-only snapshot, and a read-only snapshot can be based on an existing read-write snapshot. Each ACFS file system can support a total of 63 snapshots, including Snaps-of-Snaps.

In this article, I will demonstrate the use of new Snaps-of-Snaps feature to quickly revert to the snapshot copy of a database that uses cloud FS to store all its files. Currently I am working in Oracle Database 12.1.0.2 cluster environment.

Overview of Steps:

  • Create a Cloud file system
  • Using DBCA, create a database named cfsdb, with all of its files stored on the cloud file system
  • Check that HR user and its schema exist in cfsdb database
  • Create a read-only snapshot of the cloud FS
  • Drop HR user from database cfsdb
  • Use snaps-of-snaps feature to restore HR user and its schema objects from the snapshot created earlier

Create a Cloud file system

  1. Create mountpoint
[root@host01 ~]# mkdir -p /mnt/acfs
  1. Modify the DATA diskgroup to ensure that all the new ADVM features in release 12.1 are enabled.
ASMCMD>setattr -G DATA compatible.advm 12.1.0.0.0
  1. Create a volume VOL1 in the DATA diskgroup having a size of 5G.
ASMCMD>volcreate -G DATA -s 5g --column 1 VOL1
  1. Examine the new volume and take note of the volume device associated with it.
ASMCMD> volinfo -G DATA VOL1

Diskgroup Name: DATA

	Volume Name: VOL1
	Volume Device: /dev/asm/vol1-190
	State: ENABLED
	Size (MB): 5120
	Resize Unit (MB): 8
	Redundancy: MIRROR
	Stripe Columns: 1
	Stripe Width (K): 8192
	Usage:
	Mountpath:
  1. Make a cloud file system on the newly-created volume VOL1 using the volume device identified in step 4.
[root@host01 ~]# mkfs -t acfs /dev/asm/vol1-190

mkfs.acfs: version = 12.1.0.2.0
mkfs.acfs: on-disk version = 39.0
mkfs.acfs: volume = /dev/asm/vol1-199
mkfs.acfs: volume size = 5368709120 ( 5.00 GB )
mkfs.acfs: Format complete.
  1. Mount the ACFS on the mount point created earlier.
[root@host01 ~]# mount -t acfs /dev/asm/vol1-190 /mnt/acfs
  1. Create a new cloud file system resource using the volume device identified above and the mount point created earlier.
[root@host01 ~]# srvctl add filesystem -m /mnt/acfs -d /dev/asm/vol1-190
  1. Start the new cloud file system.
[root@host01 ~]# srvctl start file system -d /dev/asm/vol1-190
  1. Confirm that the new file system is mounted.
[root@host01 ~]# srvctl status filesystem -d /dev/asm/vol1-190

ACFS file system /mnt/acfs is mounted on nodes host01

[root@host01 ~]# mount | grep acfs

/dev/asm/vol1-190 on /mnt/acfs type acfs (rw)
  1. Modify the access privileges for the new cloud file system to enable access by any user.
# chmod 777 /mnt/acfs
  1. Modify the newly created cloud FS to enable full control by members of OS group dba which corresponds to SYSDBA privilege on database instance.
[root@host01 ~]# crsctl modify resource ora.data.vol1.acfs -attr "ACL='owner:root:rwx,pgrp:dba:rwx,other::r--'" -unsupported

Create a database cfsdb with all its files on the cloud file system

  1. Using DBCA, create a new database “cfsdb”, with all its files on cloud file system and verify its configuration and status.
[oracle@host01 ~]$ srvctl config database -d cfsdb

Database unique name: cfsdb
Database name: cfsdb
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile: /mnt/acfs/oradata/cfsdb/spfilecfsdb.ora
Password file: /mnt/acfs/oradata/cfsdb/orapwcfsdb
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths: /mnt/acfs
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: cfsdb1
Configured nodes: host01
Database is administrator managed

[oracle@host01 ~]$ srvctl status database -d cfsdb
Instance cfsdb1 is running on node host01
  1. Verify that controlfiles, redo logfiles and datafiles are created on cloud file system.
CFSDB>select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/mnt/acfs/oradata/cfsdb/control01.ctl
/mnt/acfs/cfsdb/control02.ctl

CFSDB>select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/mnt/acfs/oradata/cfsdb/redo03.log
/mnt/acfs/oradata/cfsdb/redo02.log
/mnt/acfs/oradata/cfsdb/redo01.log

CFSDB>select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/mnt/acfs/oradata/cfsdb/system01.dbf
/mnt/acfs/oradata/cfsdb/sysaux01.dbf
/mnt/acfs/oradata/cfsdb/undotbs01.dbf
/mnt/acfs/oradata/cfsdb/example01.dbf
/mnt/acfs/oradata/cfsdb/users01.dbf

Check that HR user and its schema exist in database cfsdb

CFSDB>select username from dba_users where username = 'HR';

USERNAME
--------------------------------------------------------------------------------
HR

CFSDB>select count(*) from hr.employees;

COUNT(*)
----------
	107

Create a read-only snapshot of the cloud File System

[root@host01 .ACFS]# acfsutil snap create ro_dbsnap /mnt/acfs

acfsutil snap create: Snapshot operation is complete.
  1. Verify that the snapshot created above exists and is a read-only snapshot.
[root@host01 .ACFS]# acfsutil snap info /mnt/acfs

snapshot name: ro_dbsnap
snapshot location: /mnt/acfs/.ACFS/snaps/ro_dbsnap
RO snapshot or RW snapshot: RO
parent name: /mnt/acfs
snapshot creation time: Tue Jun 16 11:14:20 2015
number of snapshots: 1
snapshot space usage: 25313280 ( 24.14 MB )

Drop HR user from database cfsdb

CFSDB>drop user HR cascade;

User dropped.

CFSDB>select username from dba_users where username = 'HR';

no rows selected

CFSDB>select count(*) from hr.employees;

select count(*) from hr.employees
	*
ERROR at line 1:
ORA-00942: table or view does not exist

Use snaps-of-snaps feature to restore HR user and its schema objects from the snapshot created earlier

  1. List the database files and multiplexed control file in the snapshot ro_dbsnap which was taken when HR user existed in the database
[root@host01 cfsdb]# ls -l /mnt/acfs/.ACFS/snaps/ro_dbsnap/oradata/cfsdb

total 3043232
-rw-r----- 1 oracle oinstall 18956288 Jun 16 11:07 control01.ctl
-rw-r----- 1 oracle oinstall 1304174592 Jun 15 20:34 example01.dbf
-rw-r----- 1 oracle oinstall 7680 Jun 15 19:44 orapwcfsdb
-rw-r----- 1 oracle oinstall 52429312 Jun 16 11:07 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jun 15 20:01 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jun 15 20:00 redo03.log
-rw-r----- 1 oracle oinstall 3584 Jun 15 20:39 spfilecfsdb.ora
-rw-r----- 1 oracle oinstall 639639552 Jun 16 11:07 sysaux01.dbf
-rw-r----- 1 oracle oinstall 828383232 Jun 16 11:05 system01.dbf
-rw-r----- 1 oracle oinstall 62922752 Jun 15 20:36 temp01.dbf
-rw-r----- 1 oracle oinstall 99622912 Jun 16 10:58 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Jun 15 19:56 users01.dbf

[root@host01 ~]# ls -l /mnt/acfs/.ACFS/snaps/ro_dbsnap/cfsdb
total 18512
-rw-r----- 1 oracle oinstall 18956288 Jun 16 11:07 control02.ctl
  1. Stop database CFSDB
[oracle@host01 ~]$ srvctl stop database -d cfsdb
  1. List current database files and multiplexed control file belonging to cfsdb on cloud file system in which HR schema is missing.
[oracle@host01 ~]$ ls -l /mnt/acfs/oradata/cfsdb/
total 3043232
-rw-r----- 1 oracle oinstall 18956288 Jun 16 11:25 control01.ctl
-rw-r----- 1 oracle oinstall 1304174592 Jun 16 11:25 example01.dbf
-rw-r----- 1 oracle oinstall 7680 Jun 15 19:44 orapwcfsdb
-rw-r----- 1 oracle oinstall 52429312 Jun 16 11:25 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jun 15 20:01 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jun 15 20:00 redo03.log
-rw-r----- 1 oracle oinstall 3584 Jun 15 20:39 spfilecfsdb.ora
-rw-r----- 1 oracle oinstall 639639552 Jun 16 11:12 sysaux01.dbf
-rw-r----- 1 oracle oinstall 828383232 Jun 16 11:10 system01.dbf
-rw-r----- 1 oracle oinstall 62922752 Jun 15 20:36 temp01.dbf
-rw-r----- 1 oracle oinstall 99622912 Jun 16 11:12 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Jun 15 19:56 users01.dbf

[oracle@host01 ~]$ ls -l /mnt/acfs/cfsdb/

total 18512
-rw-r----- 1 oracle oinstall 18956288 Jun 16 11:24 control02.ctl
  1. Remove the current copy of all the database files and multiplexed control file of database cfsdb
[oracle@host01 ~]$ rm -rf /mnt/acfs/oradata

[oracle@host01 ~]$ ls -l /mnt/acfs/oradata/cfsdb/

ls: /mnt/acfs/oradata/cfsdb/: No such file or directory

[oracle@host01 ~]$ rm -rf /mnt/acfs/cfsdb

[oracle@host01 ~]$ ls -l /mnt/acfs/cfsdb/

ls: /mnt/acfs/cfsdb/: No such file or directory

At this stage, we can recover the database to the point in time when HR schema existed by copying the snapshot copies of all the database files into the original locations and restarting the database.

However, we will employ snaps-of-snaps, a 12c new feature, to create a read write snapshot based on the read only snapshot taken earlier and implement a quicker and more flexible solution which would obviate the need to copy the database files.

Let’s see how…

  1. Create a new read write snapshot called rw_dbsnap based on the first read-only snapshot ro_dbsnap
[root@host01 ~]# acfsutil snap create -w -p ro_dbsnap rw_dbsnap /mnt/acfs

acfsutil snap create: Snapshot operation is complete.
  1. Verify that now we have two snapshots of the file system
[root@host01 ~]# acfsutil snap info /mnt/acfs

snapshot name: ro_dbsnap
snapshot location: /mnt/acfs/.ACFS/snaps/ro_dbsnap
RO snapshot or RW snapshot: RO
parent name: /mnt/acfs
snapshot creation time: Tue Jun 16 11:14:20 2015

snapshot name: rw_dbsnap
snapshot location: /mnt/acfs/.ACFS/snaps/rw_dbsnap
RO snapshot or RW snapshot: RW
parent name: ro_dbsnap
snapshot creation time: Tue Jun 16 11:31:05 2015

number of snapshots: 2
snapshot space usage: 3133186048 ( 2.91 GB )
  1. Create a link that exposes the writable snapshot database files and multiplexed control file in their original locations.
[root@host01 ~]# ln -s /mnt/acfs/.ACFS/snaps/rw_dbsnap/oradata /mnt/acfs
[root@host01 ~]# ln -s /mnt/acfs/.ACFS/snaps/rw_dbsnap/cfsdb/ /mnt/acfs
  1. Verify that database files and multiplexed control file residing inside the writable snapshot rw_dbsnap are exposed in their original locations using the links that we just created.
[oracle@host01 ~]$ ls -l /mnt/acfs/oradata/cfsdb/

total 3043232
-rw-r----- 1 oracle oinstall 18956288 Jun 16 11:07 control01.ctl
-rw-r----- 1 oracle oinstall 1304174592 Jun 15 20:34 example01.dbf
-rw-r----- 1 oracle oinstall 7680 Jun 15 19:44 orapwcfsdb
-rw-r----- 1 oracle oinstall 52429312 Jun 16 11:07 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jun 15 20:01 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jun 15 20:00 redo03.log
-rw-r----- 1 oracle oinstall 3584 Jun 15 20:39 spfilecfsdb.ora
-rw-r----- 1 oracle oinstall 639639552 Jun 16 11:07 sysaux01.dbf
-rw-r----- 1 oracle oinstall 828383232 Jun 16 11:05 system01.dbf
-rw-r----- 1 oracle oinstall 62922752 Jun 15 20:36 temp01.dbf
-rw-r----- 1 oracle oinstall 99622912 Jun 16 10:58 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Jun 15 19:56 users01.dbf

[oracle@host01 ~]$ ls -l /mnt/acfs/cfsdb/

total 18512
-rw-r----- 1 oracle oinstall 18956288 Jun 16 11:07 control02.ctl
  1. Restart the cfsdb database.
[oracle@host01 ~]$ srvctl starts database -d cfsdb
[oracle@host01 ~]$ srvctl status database -d cfsdb

Instance cfsdb1 is running on node host01
  1. Check that HR user and objects owned by it have been restored.
CFSDB>select username from dba_users where username = 'HR';

USERNAME
--------------------------------------------------------------------------------
HR

CFSDB>select count(*) from hr.employees;

COUNT(*)
----------
107

At this point, the database is available for full read and write operations. Thus, using snaps-of-snaps feature, we have been able to revert to an earlier point in time without copying database files from the read only snapshot ro_dbsnap.

Extending this functionality further, we can take read only snapshots of the database at various points in time while the database is in use. These snapshots will serve as point in time backups of the database. We can revert to an earlier point in time time by simply taking a read write snapshot of the relevant read-only snapshot and exposing the database files inside the read write snapshot in the original location.

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, although replication and encryption are not supported in conjunction with datafiles. Oracle ACFS Snapshots may serve as the point-in-time backup of the database to be used for online recovery of database files. Using the 12c new snaps-of-snaps feature, we can create read write snapshot based on the read only snapshot and recover the database to an earlier point in time without copying the database files. Here, It is worth mentioning that snapshot is not a replacement for RMAN.