Protecting Oracle Database deployed on EC2 Instance- Part I

ORACLE server is one of the key components in the data center. It is critical to protecting the database on a periodic basis. Backup and recovery refer to the process of backing up data in case of a loss and setting up systems that allow that data recovery due to data loss. Backing up data requires copying and archiving computer data so that it is accessible in case of data deletion or corruption. Below are the different types of backups of ORACLE supports.

Oracle – Types of backup:-

  • Cold Backup – A cold backup is done when there is no user activity going on with the system. Also called as an offline backup, is taken when the database is not running and no users are logged in. all files of the database are copied and no changes during the copy are made.
  • Hot Backup – A hot backup is taken when the database needs to run all the time. It is an online backup. All files of the database are copied and there may be changes to the database during the copy.

The benefit of taking a hot backup is that the database is available for use while the backup is occurring and one can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. For cold backups, the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

Difference between Restore and Recovery:-

Restoring involves copying backup files from secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location. Recovery is the process of applying redo logs to the database to roll it forward.

Media recovery applies archived redo logs and online redo logs to recover the datafiles. Whenever a change is made to a data file, the change is first recorded in the online redo logs. Media recovery selectively applies the changes recorded in the online and archived redo logs to the restored datafile to roll it forward.

Unlike media recovery, Oracle performs crash recovery and instance recovery automatically after an instance failure. Crash and instance recovery recover a database to its transaction-consistent state just before instance failure. By definition, crash recovery is the recovery of a database in a single-instance configuration or an Oracle Real Application Clusters configuration in which all instances have crashed.

Basic Recovery steps (Rolling forward and Rolling back):-

 

For managing production database is it mandatory to put the database in archive log mode.

How to take “App Aware” Oracle DB backup files using Nimesa?

Nimesa takes the snapshot of EBS volumes. Below steps describes how to take application consistent snapshot of Oracle databases using Nimesa pre and post scripts.

The first step is to create an Oracle App aware policy through “Application Policy” screen.

  • Update below fields with appropriate data based on your solution deployment needs
    • Retention Copies – Number of retention copies required
    • Enable the pre & post DB script
    • Check Abort Backup id Pre/Post Script Fails
    • When enabling Pre/Post Scripts,  attach the respective DB scripts present in your local file system.

Upload the pre and post scripts.

“pre-db.sh” file Usage

Description:- Script is designed for DB to make it run on a hot backup mode. It is a pre-backup shell script which connects to the database, Quiesce and puts the database into “begin backup” mode.

Pre-req:-

    • Ensure your database is running in archivelog mode
    • Connect to DB and run the query  ARCHIVE LOG LIST; to check if DB is in archive log mode.
    • Run Alter database archivelog; command in the SQL prompt to enable the DB into archive log mode.
    • Better to have dbf file and log file destinations configured in a different location

“pre-db.sh” script modification:-

  • Modify Oracle User, Oracle Instance ID and Oracle Home in the script (marked as bold)
  • Run the script as “root” user after logging in to Oracle DB host server

###Connecting to the bash shell prompt

#!/bin/bash

###Connecting to oracle user, Oracle instance ID from root user

orauser=”oracle”

orasid=”dummy”

###Setting the Oracle home path, getting it as input from the user

orahome=”/u02/app/oracle/product/11.2.0/db_1″

###Swtich User to Oracle and set ORACLE_SID and ORACLE_HOME based on orasid and orahome parameters

su – ${orauser}   << BOF 2>&1

export ORACLE_SID=$orasid

export ORACLE_HOME=$orahome

###Connect to SQLPLUS as sysdba and execute oracle commands needed for backup the DB

sqlplus “/ as sysdba” << EOF 2>&1

alter system archive log current;

alter database begin backup;

exit;

EOF

BOF

“post-db.sh” file Usage

Description:- Script is designed for DB to end its hot backup mode. It is a post-backup shell script which connects to the database and puts the database into “end backup” mode.

“post-db.sh” script modification:-

  • Modify Oracle User, Oracle Instance ID and Oracle Home in the script (marked as bold)
  • Run the script as “root” user after login to Oracle DB host server

#!/bin/bash

orauser=”oracle”

orasid=”dummy”

orahome=”/u02/app/oracle/product/11.2.0/db_1″

#source /home/$orauser/$orasid.env

source /home/$orauser/.bash_profile

su – ${orauser}   << BOF 2>&1

export ORACLE_SID=$orasid

export ORACLE_HOME=$orahome

###Connecting to Oracle as sysdba and execute Oracle commands needs to end the DB backup

sqlplus “/ as sysdba” << EOF 2>&1

alter system archive log current;

alter database end backup;

alter system archive log current;

alter system switch logfile;

alter system switch logfile;

exit;

EOF

BOF

Create a backup group in Nimesa add the EC2 instance and assign the newly created policy. Trigger a scheduled or on demand backup as part of job progress the script excecution output will be seen.

Pre-Script Logs:

Post-Script Logs:

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