Integrating ACFS Snapshots With RMAN

In my last article, I demonstrated that 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 addition, for a database with its files stored… Continue Reading →

In my last article, I demonstrated that 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 addition, for a database with 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.

In this article, I will demonstrate that ACFS snapshots can be integrated with RMAN and hence employed to perform complete recovery using RMAN RESTORE / RECOVER commands, thereby complementing RMAN functionality.

Currently I am working in an 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:

[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

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$archived_log;

NAME
-----------------------------------------------------------------
/mnt/acfs/CFSDB/archivelog/2015_07_07/o1_mf_1_7_bsqsor96_.arc

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

CFSDB>sho parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /mnt/acfs
db_recovery_file_dest_size           big integer 3000M

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

CFSDB > archive log list;

Database log mode               Archive Mode
Automatic archival              Enabled
Archive destination             USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence      6
Next log sequence to archive    8
Current log sequence            8

Create a test table HR.EMP with 107 rows in EXAMPLE tablespace:

SQL> create table hr.emp tablespace example as select * from hr.employees;

SQL> select owner, table_name, tablespace_name
			from dba_tables
			where owner = 'HR' and table_name = 'EMP';

OWNER      TABLE_NAME      TABLESPACE_NAME
---------- --------------- ------------------------------
HR         EMP             EXAMPLE

SQL> select count(*) from hr.emp;

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

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

[root@host01 oracle]# acfsutil snap info /mnt/acfs

	number of snapshots: 0
	snapshot space usage: 0 ( 0.00 )

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

SQL> alter database begin backup;

Database altered.

[root@host01 acfs]# acfsutil snap create example_dbsnap /mnt/acfs

acfsutil snap create: Snapshot operation is complete.

[root@host01 acfs]# acfsutil snap info /mnt/acfs

snapshot name:                example_dbsnap
snapshot location:            /mnt/acfs/.ACFS/snaps/example_dbsnap
RO snapshot or RW snapshot:   RO
parent name:                  /mnt/acfs
snapshot creation time:       Tue Jul 7 20:34:18 2015

number of snapshots: 1
snapshot space usage: 9486336 ( 9.04 MB )

SQL> alter database end backup;

Database altered.

Check that all the datafiles, control file and spfile are available in snapshot:

[root@host01 acfs]# ls -l /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb

total 3038112
-rw-r----- 1 oracle oinstall   18956288 Jul  7 20:25 control01.ctl
-rw-r----- 1 oracle oinstall 1304174592 Jul  7 20:22 example01.dbf
-rw-r----- 1 oracle oinstall       7680 Jul  7 19:38 orapwcfsdb
-rw-r----- 1 oracle oinstall   52429312 Jul  7 20:03 redo01.log
-rw-r----- 1 oracle oinstall   52429312 Jul  7 20:30 redo02.log
-rw-r----- 1 oracle oinstall   52429312 Jul  7 19:44 redo03.log
-rw-r----- 1 oracle oinstall       3584 Jul  7 20:11 spfilecfsdb.ora
-rw-r----- 1 oracle oinstall  629153792 Jul  7 20:30 sysaux01.dbf
-rw-r----- 1 oracle oinstall  828383232 Jul  7 20:30 system01.dbf
-rw-r----- 1 oracle oinstall   62922752 Jul  7 20:25 temp01.dbf
-rw-r----- 1 oracle oinstall  104865792 Jul  7 20:30 undotbs01.dbf
-rw-r----- 1 oracle oinstall    5251072 Jul  7 20:22 users01.dbf

Insert records in hr.emp so that it has 214 rows:

SQL> insert into hr.emp select * from hr.emp;

107 rows created.

SQL> commit;

Commit complete

SQL> select count(*) from hr.emp;

  COUNT(*)
----------
       214

Simulate loss of datafile for EXAMPLE tablespace:

SQL> ho mv /mnt/acfs/oradata/cfsdb/example01.dbf /mnt/acfs/oradata/cfsdb/example01.bak

SQL> alter tablespace example offline;

Tablespace altered.

SQL> alter tablespace example online;
alter tablespace example online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/mnt/acfs/oradata/cfsdb/example01.dbf'.

Locate the datafile for EXAMPLE tablespace in snapshot example_dbsnap:

[root@host01 cfsdb]# ls -l /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/example01.dbf

-rw-r----- 1 oracle oinstall 1304174592 Jul 7 20:22 /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/example01.dbf

Copy datafile for EXAMPLE tablespace from snapshot and attempt to bring EXAMPLE tablespace online:

[oracle@host01 ~]$ cp /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/example01.dbf /mnt/acfs/oradata/cfsdb/example01.dbf

[oracle@host01 ~]$ ls -l /mnt/acfs/oradata/cfsdb/example01.dbf
-rw-r----- 1 oracle oinstall 1304174592 Jul 7 20:44 /mnt/acfs/oradata/cfsdb/example01.dbf

SQL> alter tablespace example online;
alter tablespace example online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/mnt/acfs/oradata/cfsdb/example01.dbf'

Employ RMAN to perform complete recovery using archived logs in FRA:

RMAN> recover tablespace example;

Starting recover at 07-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=77 instance=cfsdb1 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:06

Finished recover at 07-JUL-15

SQL> alter tablespace example online;

Tablespace altered.

SQL> select count(*) from hr.emp;

  COUNT(*)
----------
       214

Thus, we have been able to perform complete recovery by employing:

  • OS commands to copy the datafile from the snapshot example_dbsnap taken while the database was in backup mode.
  • RMAN RECOVER command to perform complete recovery using Archived logs stored in FRA (/mnt/acfs).

Now, let us explore if we can catalog the datafile copies stored in the snapshot with RMAN and hence employ RMAN for RESTORE as well as RECOVER.

Catalog all the files in the snapshot example_dbsnap in directory “/mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/”

RMAN> catalog start with '/mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/';
searching for all files that match the pattern/mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/

List of Files Unknown to the Database
=====================================
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/orapwcfsdb
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/sysaux01.dbf
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/system01.dbf
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/users01.dbf
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/undotbs01.dbf
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/control01.ctl
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/redo01.log
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/redo02.log
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/redo03.log
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/temp01.dbf
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/example01.dbf
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/spfilecfsdb.ora

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/sysaux01.dbf
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/system01.dbf
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/users01.dbf
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/undotbs01.dbf
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/temp01.dbf
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/example01.dbf

List of Files Which Were Not Cataloged
=======================================
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/orapwcfsdb
RMAN-07518: Reason: Foreign database file DBID: 0 Database Name:
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/control01.ctl
RMAN-07519: Reason: Error while cataloging. See alert.log.
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/redo01.log
RMAN-07529: Reason: catalog is not supported for this file type
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/redo02.log
RMAN-07529: Reason: catalog is not supported for this file type
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/redo03.log
RMAN-07529: Reason: catalog is not supported for this file type
File Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/spfilecfsdb.ora
RMAN-07518: Reason: Foreign database file DBID: 0 Database Name:

RMAN> list copy of database;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
2       1    A 08-JUL-15       1639217    07-JUL-15
        Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/system01.dbf

1       3    A 08-JUL-15       1639217    07-JUL-15
        Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/sysaux01.dbf

4       4    A 08-JUL-15       1639217    07-JUL-15
        Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/undotbs01.dbf

5       5    A 08-JUL-15       1639217    07-JUL-15
        Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/example01.dbf

3       6    A 08-JUL-15       1639217    07-JUL-15
        Name: /mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/users01.dbf

It can be seen that all the datafile copies have been catalogued whereas SPfile and controlfile have not been registered with RMAN.

Now we will again simulate the loss of datafile for the EXAMPLE tablespace, and then we’ll attempt to employ RMAN for RESTORE as well RECOVER.

Simulate loss of datafile for EXAMPLE tablespace:

SQL> ho rm /mnt/acfs/oradata/cfsdb/example01.dbf

	alter tablespace example offline;

Tablespace altered.

SQL> alter tablespace example online;
alter tablespace example online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/mnt/acfs/oradata/cfsdb/example01.dbf'

Restore EXAMPLE tablespace from the catalogued datafile copy in the snapshot using RMAN and attempt to bring EXAMPLE tablespace online:

RMAN> restore tablespace example;

Starting restore at 08-JUL-15
using channel ORA_DISK_1
channel ORA_DISK_1: restoring datafile 00005
input datafile copy RECID=5 STAMP=884515151 file name=/mnt/acfs/.ACFS/snaps/example_dbsnap/oradata/cfsdb/example01.dbf
destination for restore of datafile 00005: /mnt/acfs/oradata/cfsdb/example01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00005
output file name=/mnt/acfs/oradata/cfsdb/example01.dbf RECID=0 STAMP=0
Finished restore at 08-JUL-15

SQL> alter tablespace example online;
alter tablespace example online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/mnt/acfs/oradata/cfsdb/example01.dbf'

Employ RMAN to perform complete recovery using archived logs in FRA:

RMAN> recover tablespace example;

Starting recover at 08-JUL-15
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:09

Finished recover at 08-JUL-15


SQL> alter tablespace example online;

Tablespace altered.

SQL> select count(*) from hr.emp;

  COUNT(*)
----------
       214

Thus, we have been able to perform complete recovery by employing:

  • RMAN RESTORE command to RESTORE the datafile from the snapshot example_dbsnap taken while the database was in backup mode.
  • RMAN RECOVER command to perform complete recovery using Archived logs stored in FRA (/mnt/acfs).

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 complete recovery using RMAN RESTORE / RECOVER commands.