Backup Oracle databases to AWS S3

by Pythian Marketing on Feb 21, 2017 12:00:00 AM

There are different options for backing up Oracle databases to Cloud, but using Oracle Secure Backup module to take backups into AWS S3 is one of the most efficient methods in terms of costs and backup/restore performance. In this post I will show you how to install, configure and use Oracle Secure Backup to take your Oracle database backups to AWS S3. This method can be used for Oracle database version 9.2 or higher. In this example, database version is 12c and platform is Linux x86_64. Oracle Secure Backup module must be installed into database Oracle Home. Using installed libraries you can then take backups via RMAN into AWS S3 the same way you backup to sbt_tape.

Requirements:

1- An AWS account and an IAM user with access to S3: For setting up backups to AWS you will require an AWS account and an IAM user with full access to AWS S3. During setup Access Keys and Secret Access Key of this IAM user will be used. There is no need to have access to AWS Console. You can use AWS Free tire for test purposes. 2- Oracle Secure Backup module for AWS: You can download Oracle Secure Backup module for AWS from here 3- OTN account: During installation you need to provide an OTN account. 4- Java 1.7 or higher: Java 1.7 or higher must be installed on your server before you can proceed.

Installation:

1- Create Oracle Wallet Directory: If Oracle Wallet directory does not exist, create one. This folder will be used to store AWS Access Keys and Secret Access Key. Create this directory in $ORACLE_HOME/dbs/:


  $ cd $ORACLE_HOME/dbs/
  $ mkdir osbws_wallet

2- Download osbws_installer.zip from the link provided above and put in your installation folder, in this example /mnt/stage/osb , unzip the compressed file and you will have two files as shown below:


  $ pwd
  /mnt/stage/osb
  $ unzip osbws_installer.zip
  Archive: osbws_installer.zip
  inflating: osbws_install.jar
  inflating: osbws_readme.txt
  $ ls
  osbws_installer.zip osbws_install.jar osbws_readme.txt

3- Install OSB Cloud Module for Amazon S3 into your Oracle Home:


  $ cd /mnt/stage/osb
  $ java -jar osbws_install.jar -AWSID XxXxX -AWSKey XxXxX -walletDir $ORACLE_HOME/osbws_wallet -libDir $ORACLE_HOME/lib -location ap-southeast-2 -awsEndPoint s3-ap-southeast-2.amazonaws.com -otnUser bakhshandeh@pythian.com -otnPass

Parameters that you will need to set for installation are as below:


  -AWSID: AWS Access Key
 
  -AWSKey: AWS Secret Access Key
 
  -walletDir: Location where Backup Module will store AWS keys
 
  -libDir: Location where Backup Module libraries will be installed
 
  -location: This is AWS S3 location where you want to put your backups into. 
  Value for this parameter must be a valid Region from Amazon Regions.
  In this example "ap-southeast-2" which is region for "Asia Pacific (Sydney)" has been used
 
  -awsEndPoint: This should be valid end-point from location AWS region specified by "location" parameter
  In this example "s3-ap-southeast-2.amazonaws.com" has been used which is one of the end-points in ""Asia Pacific (Sydney)""
 
  -otnUser: OTN Account
 
  -otnPass: OTN Password

In my example I did not pass any value for -otnPass parameter and this was the only workaround I found for the error noted below during my tests:


  Downloading Oracle Secure Backup Web Service Software Library from file osbws_linux64.zip.
  Error: Library download failed. Please check your network connection to Oracle Public Cloud.

When I encountered this error I could only fix the issue by passing no value for otnPass, but it might work for you.

Running Backup using RMAN:

Installation will create a file in $ORACLE_HOME/dbs which is usually named osb<SID>.ora and you need to use full path of this file in your allocate channel command in RMAN. In my example SID is KAMRAN


  $ cd $ORACLE_HOME/dbs
  $ pwd
  /apps/oracle/product/12.1.0.2/db_1/dbs
  $ ls -al osb*.ora
  -rw-r--r-- 1 oracle oinstall 194 Jan 5 11:31 osbwsKAMRAN.ora
  $

Content of this file is as below:


  $ cat osbwsKAMRAN.ora
  OSB_WS_HOST=https://s3-ap-southeast-2.amazonaws.com
  OSB_WS_LOCATION=ap-southeast-2
  OSB_WS_WALLET='location=file:/apps/oracle/product/12.1.0.2/db_1/dbs/osbws_wallet CREDENTIAL_ALIAS=gordon-s_aws'
  $

This file can be used for any other database in same Oracle Home. For this reason,I renamed it to osbwsCONFIG.ora so that name is generic and there is no dependency to any of databases. mv osbwsKAMRAN.ora osbwsCONFIG.ora I will use osbwsCONFIG.ora in RMAN channel settings. Now you just need to allocate a channel for your backup/restore commands as below using above file as below:


  allocate channel c1 device type sbt parms='SBT_LIBRARY=libosbws.so,SBT_PARMS=(OSB_WS_PFILE=/apps/oracle/product/12.1.0.2/db_1/dbs/osbwsCONFIG.ora)';

Below is a complete example of backup command which shows backup piece details and how they have been located in AWS S3 regions that you specified during installation. In this example, I have allocated only one channel and I have backed up datafile 1 only as an example. You can allocate any number of channels and backup any database component the same way you backup to disk:


  $ . oraenv
  KAMRAN
  $ rman target /
 
  Recovery Manager: Release 12.1.0.2.0 - Production on Wed Dec 28 11:21:48 2016
 
  Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
 
  connected to target database: KAMRAN (DBID=283560064)
 
  RMAN>run{
  2> allocate channel c1 device type sbt parms='SBT_LIBRARY=libosbws.so,SBT_PARMS=(OSB_WS_PFILE=/apps/oracle/product/12.1.0.2/db_1/dbs/osbwsCONFIG.ora)';
  3> backup datafile 1;
  4> }
 
  released channel: ORA_DISK_1
  allocated channel: c1
  channel c1: SID=374 instance=KAMRAN device type=SBT_TAPE
  channel c1: Oracle Secure Backup Web Services Library VER=3.16.11.11
 
  Starting backup at 28-DEC-16
  channel c1: starting full datafile backup set
  channel c1: specifying datafile(s) in backup set
  input datafile file number=00001 name=+DATA/KAMRAN/DATAFILE/system.258.887023011
  channel c1: starting piece 1 at 28-DEC-16
  channel c1: finished piece 1 at 28-DEC-16
  piece handle=09rojka7_1_1 tag=TAG20161228T112807 comment=API Version 2.0,MMS Version 3.16.11.11
  channel c1: backup set complete, elapsed time: 00:00:45
  channel c1: starting full datafile backup set
  channel c1: specifying datafile(s) in backup set
  including current control file in backup set
  including current SPFILE in backup set
  channel c1: starting piece 1 at 28-DEC-16
  channel c1: finished piece 1 at 28-DEC-16
  piece handle=0arojkbl_1_1 tag=TAG20161228T112807 comment=API Version 2.0,MMS Version 3.16.11.11
  channel c1: backup set complete, elapsed time: 00:00:07
  Finished backup at 28-DEC-16
  released channel: c1
 
  RMAN> list backup tag TAG20161228T112807;
 
 
  List of Backup Sets
  ===================
 
 
  BS Key Type LV Size Device Type Elapsed Time Completion Time
  ------- ---- -- ---------- ----------- ------------ ---------------
  9 Full 741.75M SBT_TAPE 00:00:38 28-DEC-16
  BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20161228T112807
  Handle: 09rojka7_1_1 Media: s3-ap-southeast-2.amazonaws.com/oracle-data-gordonsm-ap1
  List of Datafiles in backup set 9
  File LV Type Ckp SCN Ckp Time Name
  ---- -- ---- ---------- --------- ----
  1 Full 58915843 28-DEC-16 +DATA/KAMRAN/DATAFILE/system.258.887023011
 
  BS Key Type LV Size Device Type Elapsed Time Completion Time
  ------- ---- -- ---------- ----------- ------------ ---------------
  10 Full 22.50M SBT_TAPE 00:00:01 28-DEC-16
  BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20161228T112807
  Handle: 0arojkbl_1_1 Media: s3-ap-southeast-2.amazonaws.com/oracle-data-gordonsm-ap1
  SPFILE Included: Modification time: 26-DEC-16
  SPFILE db_unique_name: KAMRAN
  Control File Included: Ckp SCN: 58915865 Ckp time: 28-DEC-16
 
  RMAN>

Some performance statistics:

I used a 340G database for testing performance and tried full backups into AWS S3 using different number of channels. First, I allocated two channels and backup to AWS was complete in 48 minutes. I then tried four channels, and the backup to AWS was completed in 27 minutes. I predicted that by increasing the number of channels to eight, would make backup complete faster. Surprisingly, with 8 channels backup completed in 27 minutes (which was exactly the same result when I used four channels). So in my case, the optimum number of channels for taking backups to AWS S3 was four. I should mention that same database when backed up to NFS disks using four channels it completed in 22 minutes, so backup time of 27 minutes to AWS was acceptable. Restore was even faster. I tried restore without recovering the database, same 340G database full restore of databases from AWS backups completed in 22 minutes which again is acceptable.

Topics: MySQL Technical Track Open Source Xtrabackup Tar Innobackupex Xbstream Netcat Socat

No Comments Yet

Let us know what you think

Share this