Saturday, February 25, 2017

ODA X6-2 -- using ACFS snapshots for database backup and restore

ODA X6 uses ACFS as the filesystem for database files. (ACFS is the default, but ASM can also be used as well)
So, in this blog post, I will demonstrate an ACFS snapshot based database backup & restore operation.
Note that, creating oracle database files on ACFS is allowed in RDBMS 11.2.0.4 and in later releases.
In ODA X6-2 , we have 12C or 11.2.0.4 databases, so that we can benefit from ACFS features such as the snapshot functionality.

Here I m doing a demo ->


Checking the ACFS volumes:


ASMCMD> volinfo --all
Diskgroup Name: DATA

         Volume Name: COMMONSTORE
         Volume Device: /dev/asm/commonstore-74
         State: ENABLED
         Size (MB): 5120
         Resize Unit (MB): 64
         Redundancy: MIRROR
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /opt/oracle/dcs/commonstore

         Volume Name: DATDEMO5
         Volume Device: /dev/asm/datdemo5-74
         State: ENABLED
         Size (MB): 102400
         Resize Unit (MB): 64
         Redundancy: MIRROR
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /u02/app/oracle/oradata/DEMO5

         Volume Name: DATDEMODB1
         Volume Device: /dev/asm/datdemodb1-74
         State: ENABLED
         Size (MB): 102400
         Resize Unit (MB): 64
         Redundancy: MIRROR
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /u02/app/oracle/oradata/DEMODB1

         Volume Name: DATDEMODB2
         Volume Device: /dev/asm/datdemodb2-74
         State: ENABLED
         Size (MB): 102400
         Resize Unit (MB): 64
         Redundancy: MIRROR
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /u02/app/oracle/oradata/DEMODB2

         Volume Name: DATDEMODB3
         Volume Device: /dev/asm/datdemodb3-74
         State: ENABLED
         Size (MB): 102400
         Resize Unit (MB): 64
         Redundancy: MIRROR
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /u02/app/oracle/oradata/DEMODB3

         Volume Name: DATDEMODB4
         Volume Device: /dev/asm/datdemodb4-74
         State: ENABLED
         Size (MB): 102400
         Resize Unit (MB): 64
         Redundancy: MIRROR
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /u02/app/oracle/oradata/DEMODB4

Diskgroup Name: RECO

         Volume Name: RECO
         Volume Device: /dev/asm/reco-296
         State: ENABLED
         Size (MB): 610304
         Resize Unit (MB): 64
         Redundancy: MIRROR
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /u03/app/oracle/
  • I have lots of ACFS volumes, including the one created for redologs (RECO). I choose to work with the ACFS volume named DATDEMO5, which is used by the DEMO5 database. The datafiles of the DEMO5 Database are located in DATDEMO5.
  • In order to test my ACFS snapshot based restore work, I create a table and drop it afterwards.But, jsut before dropping the table, I create an ACFS snapshot to be able to restore/rollback my database to a time before the table is dropped.


CREATING an ACFS SNAPSHOT:


[oracle@odademo ~]$ sqlplus "/as sysdba"SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 21 15:56:31 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create table erman as select * from dual;

Table created.

SQL> select * from erman;

D
-
X
  • At this point, I create my ACFS snapshot..  So my snapshot contains the table named erman.
[grid@odademo ~]$ acfsutil snap info /u02/app/oracle/oradata/DEMO5
    number of snapshots:  0
    snapshot space usage: 0  ( 0.00 )
[grid@odademo ~]$ acfsutil snap create ERMANSNAP  /u02/app/oracle/oradata/DEMO5
acfsutil snap create: Snapshot operation is complete.
[grid@odademo ~]$ acfsutil snap info /u02/app/oracle/oradata/DEMO5
snapshot name:               ERMANSNAP
snapshot location:           /u02/app/oracle/oradata/DEMO5/.ACFS/snaps/ERMANSNAP
RO snapshot or RW snapshot:  RO
parent name:                 /u02/app/oracle/oradata/DEMO5
snapshot creation time:      Tue Feb 21 15:59:10 2017

    number of snapshots:  1
    snapshot space usage: 32768  (  32.00 KB )
  • After taking my snapshot, I drop the table named erman.
[oracle@odademo ~]$ . oraenv
ORACLE_SID = [oracle] ? DEMO5
The Oracle base has been set to /u01/app/oracle
[oracle@odademo ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 21 16:01:18 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> drop table erman;

Table dropped.

SQL> select * from erman;
select * from erman
              *
ERROR at line 1:
ORA-00942: table or view does not exist
[grid@odademo ~]$ . oraenv
ORACLE_SID = [+ASM1] ? +ASM1
The Oracle base has been set to /u01/app/grid
  • At this point, I have no table named erman in my DEMO5 database.

RESTORING THE DATABASE FROM THE SNAPSHOT:

  • As seen above, the table named erman is dropped and now I restore my database using the ACFS snapshot that I took before dropping the table.
  • I first create a read-write snapshot from the read-only snapshot that I took earlier.
[grid@odademo ~]$ acfsutil snap create -w -p ERMANSNAP RW_ERMANSNAP /u02/app/oracle/oradata/DEMO5
acfsutil snap create: Snapshot operation is complete.
[grid@odademo ~]$ acfsutil snap info /u02/app/oracle/oradata/DEMO5
snapshot name:               ERMANSNAP
snapshot location:           /u02/app/oracle/oradata/DEMO5/.ACFS/snaps/ERMANSNAP
RO snapshot or RW snapshot:  RO
parent name:                 /u02/app/oracle/oradata/DEMO5
snapshot creation time:      Tue Feb 21 15:59:10 2017

snapshot name:               RW_ERMANSNAP
snapshot location:           /u02/app/oracle/oradata/DEMO5/.ACFS/snaps/RW_ERMANSNAP
RO snapshot or RW snapshot:  RW
parent name:                 ERMANSNAP
snapshot creation time:      Tue Feb 21 16:02:33 2017

    number of snapshots:  2
    snapshot space usage: 475136  ( 464.00 KB )
  • Then, I relink the DEMO5 directory residing in my related ACFS volume to the relevant snapshot path.
[root@odademo DEMO5]# cd /u02/app/oracle/oradata/DEMO5/DEMO5
[root@odademo DEMO5]# mv datafile datafile_old
[root@odademo DEMO5]# ln -s /u02/app/oracle/oradata/DEMO5/.ACFS/snaps/RW_ERMANSNAP/DEMO5/datafile /u02/app/oracle/oradata/DEMO5/DEMO5

[root@odademo DEMO5]# ls -al
total 40
drwxr-x--- 3 oracle asmadmin 8192 Feb 21 16:10 .
drwxrwxr-x 6 oracle oinstall 4096 Feb 21 16:08 ..
lrwxrwxrwx 1 root   root       66 Feb 21 16:10 datafile -> /u02/app/oracle/oradata/DEMO5/.ACFS/snaps/RW_ERMANSNAP/DEMO5/datafile
drwxr-x--- 2 oracle asmadmin 8192 Feb 20 16:48 datafile_old

[root@odademo DEMO5]# ls -al datafile
lrwxrwxrwx 1 root root 66 Feb 21 16:10 datafile -> /u02/app/oracle/oradata/DEMO5/.ACFS/snaps/RW_ERMANSNAP/DEMO5/datafile

[root@odademo DEMO5]# ls -al /u02/app/oracle/oradata/DEMO5/.ACFS/snaps/RW_ERMANSNAP/DEMO5/datafile
total 1773176
drwxr-x--- 2 oracle asmadmin      8192 Feb 20 16:48 .
drwxr-x--- 3 oracle asmadmin      8192 Feb 20 16:43 ..
-rw-r----- 1 oracle asmadmin 734011392 Feb 21 15:45 o1_mf_sysaux_dbowmlrw_.dbf
-rw-r----- 1 oracle asmadmin 754982912 Feb 21 15:52 o1_mf_system_dbownowh_.dbf
-rw-r----- 1 oracle asmadmin  72359936 Feb 20 22:48 o1_mf_temp_dbowpgm7_.tmp
-rw-r----- 1 oracle asmadmin 319823872 Feb 21 15:54 o1_mf_undotbs1_dbowoh07_.dbf
-rw-r----- 1 oracle asmadmin   5251072 Feb 20 16:48 o1_mf_users_dbowy2g2_.dbf
  • As seen in the above ls command outputs, the directory that my database file suppose to reside, is linked to the ACFS snapshot that I took earlier.
  • So, now I start my database to see if it can be started and see if the table that I dropped is now available.
[oracle@odademo ~]$ . oraenv
ORACLE_SID = [oracle] ? DEMO5
The Oracle base has been set to /u01/app/oracle
[oracle@odademo ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 21 16:15:21 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                  2932632 bytes
Variable Size             889192552 bytes
Database Buffers         3372220416 bytes
Redo Buffers               30621696 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:
'/u02/app/oracle/oradata/DEMO5/DEMO5/datafile/o1_mf_system_dbownowh_.dbf'
  • My database requires recovery!!? Why? It is because I only created an ACFS snapshot for the datafile volume, I didn't create an ACFS snapshot for the RECO volume, in which my redologs reside.
  • Anyways, let's recover the database to show you that it can be opened, and the media recovery is working :)
SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.
  • So , at this point, I have restored and recovered my database.  So I can say that Oracle database can be restored from an ACFS snapshot.
SQL> select * from erman;
select * from erman
              *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> exit

  • However, the table named erman is not there. As explained earlier, this is expected, because we did not restore the redolog files.. I mean the redologs where in a different ACFS volume, so our snapshot restore didn't restore the redologs, and during the recovery, our table is dropped..:) interesting, isnt it? 

CONCLUSION:


Anyways, this blog post proves not only 1 thing, but 2, actually 3 :) ->
1)"The ACFS snapshot based restore mechanism" works as expected
2)"Oracle's media recovery mechanism" works as expected
3)We can get benefit from ACFS snapshot features even for the databases running on ODA platforms.

No comments :

Post a Comment