Friday, October 24, 2014

Restore XE database from an AWS instance

Of course there is nothing special about restoring a database from the cloud on your own down-on-earth server, but it helps making this post cool and cloudy.

When I deployed XE to an AWS instance I made a small script to backup the database once pr day (scheduled in crontab), and another to fetch the backupset to my server here on earth. Backup script looks like this:

#!/bin/sh

ORACLE_SID=XE
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID ORACLE_HOME PATH

rman target / << EOF > $HOME/script/rman.$$
backup database plus archivelog delete input;
delete noprompt obsolete;
list backup of database summary;
EOF

By default the XE is installed without archiving so you need to change that when you install your production XE database. I also turned on auto backup of the controlfile. The RMAN configuration for the XE database looks like this:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/xe/dbs/snapcf_XE.f'; # default

It is mostly default values.

A script that runs on my own box simply does a recursive secure copy (scp):

scp -r -i ssh_key.pem ec2-user@42.42.42.42:/u01/app/oracle/fast_recovery_area/XE/backupset/`date +%Y_%m_%d` /home/alone/RMAN/backupset
scp -r -i ssh_key.pem ec2-user@42.42.42.42:/u01/app/oracle/fast_recovery_area/XE/backupset/`date +%Y_%m_%d` /home/alone/RMAN/autobackup

This fetches today's backup and stores it locally.

I installed XE on my own box and executed the following in RMAN to restore the database. (If the instance is up, take it down with shutdown abort first):

rman target /
startup nomount
restore controlfile from '/home/alone/RMAN/autobackup/2014_10_21/o1_mf_s_861554053_b4dvq5pl_.bkp';
alter database mount;
catalog start with '/home/alone/RMAN/autbackup/2014_10_21';
restore database;
recover database;
alter database open resetlogs;

That's it. 

But don't trust me on this procedure without trying it yourself. Remember a DBA can make mistakes, except when it comes to backup AND recovery.