Protecting Oracle Database installed in EC2 instance - Part 2

In the previous blog, we saw how to protect ORACLE databases installed in the EC2 instance. In this blog, we go over how to recover the database from the EBS snapshots. We will be using Nimesa’s volume mount feature to bring the data from the EBS snapshots and recover the database.

Following steps needs to be done to restore the database

  • Identify the backup and initiate the volume mount operation. Ensure all the volumes on which the database is spread is selected for the operation.

  • Ensure the Volume Mount operation completed successfully

  • Log in to the host/VM as “root” user where ORACLE DB is running and ensure the devices are discovered for mounting. For example for XFS file system scan and mount on the host/VM machine which is running on OEL/RHEL, run/execute below commands from root user.
    • -a (To scan new devices to the machine)
    • Check for UUID is same (or) duplicated for source and destination device
    • xfs_admin -u /dev/sda (Source Device)
    • xfs_admin -u /dev/sdc (Destination Device)
    • Generate new UUID for /dev/sdc with below command: xfs_admin -U generate /dev/sdc (This step is required when the original volume is still intact and available in the system)
    • Mount the destination device id to the created mount point
      • mkdir -p /bkmount
      • mount /dev/sdc /bkmount
  • Copy the Oracle needed files from volume mount directories to the Oracle datafiles, control files, archived log files, and pfile/spfile specified locations.
  • Complete Recovery (Loss of Control, Data and Log Files):-
    • When control file exists, then start up the database and put it to mount stage executing startup mount command

SQL> startup mount;

SQL> restore database;

SQL> recover database; (Apply the archive log files to bring the DB to consistent state)

SQL> alter database open;

  • When control file does not exist, then start up the database and put it to no mount stage executing startup mount command

SQL> startup no mount;

SQL> restore control file from ‘<backup location>’;

SQL> alter database mount;

SQL> restore database;

SQL> recover database;

SQL> alter database open; (or)

SQL> alter database open reset logs; (if DB is recovered partially)

  • Restore & Recovery of Non-System Tablespace & Datafile – Loss/Damaged/Corrupt of non-system tablespace and data file (DB is “Open” & running in “Archivelog” mode)

Datafile Restore and Recovery:-

SQL> Alter database datafile <no##> offline;

SQL> Restore datafile <no##>;

SQL> Recover datafile <no##>;

SQL> Alter database datafile <no##> online;

Tablespace Restore and Recovery:

SQL> Alter tablespace <TS Name> offline immediate;

SQL> Restore tablespace <TS Name>;

SQL> Recover tablespace <TS Name>;

SQL> Alter tablespace <TS Name> online;

  • Restore & Recovery of System Tablespace – Loss/Damaged/Corrupt of the system tablespace

When you lose the SYSTEM tablespace the instance could crash or hang: anyway the instance will alert you as soon as possible.

It’s not rare the case when you cannot even shut down the instance and proceed with a kill (os level) or use “shutdown abort” oracle command from SQL prompt.

Need to restore files from the backup, but the database could not be open until the recovery process finishes. No problem for your committed transactions because your production database is always running in “ARCHIVELOG” mode and they will be available as soon as the database open.

Tablespace Restore and Recovery:-

SQL> startup mount;

SQL> Restore tablespace system;

SQL> Recover tablespace system;

SQL> Alter database open;

  • DB Snapshot Verification using Oracle DBV utility of data files after restore and recover DB.Below is a sample verification method of a oracle data file for any corrupted data blocks

[oracle@linux-gnome ~]$ dbv file=/u02/app/oracle/oradata/dummy/users01.dbf

DBVERIFY: Release – Production on Thu Feb 28 01:58:24 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY – Verification starting : FILE = /u02/app/oracle/oradata/dummy/users01.dbf

DBVERIFY – Verification complete

Total Pages Examined         : 640

Total Pages Processed (Data) : 91

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 39

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 492

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg) : 0

Total Pages Empty            : 18

Total Pages Marked Corrupt   : 0

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 986532 (0.986532)

  • Volume can be discarded using Unmount Volume operation in Nimesa when the DB is up and running

About Nimesa

Nimesa is an enterprise-class Application Aware data protection, cost management & copy data management Solution for applications running on AWS. It uses native AWS capabilities like EBS snapshots capabilities to automatically protect the environment. Nimesa provides simple policy based lifecycle management of snapshots and clones of EC2 instances