Amazon Web Services Tips

  


Oracle Database Backup to Amazon Simple Storage Service (Amazon S3) on Linux

by Jeff Hunter, Sr. Database Administrator

Contents

Introduction

The traditional method of relying on tapes for off-site backups has been a widely accepted best practice for many organizations. Today, however, new disk economics and the compelling price point offered by Cloud storage vendors has presented a new opportunity to make off-site backups more reliable, accessible, and secure. With the introduction of Oracle Secure Backup Cloud Module for Amazon S3, it is now possible to perform Oracle database backups to Amazon Simple Storage Service (Amazon S3) for off-site storage purposes. This new strategy also offers the ability to stream backups directly to the Cloud. This is especially appealing when performing a backup through a low latency network connection like Amazon EC2 instances (DBs within Amazon Cloud) or from your on-premise network to Amazon VPC using Amazon AWS Direct Connect service (DBs not within Amazon Cloud).

This guide demonstrates how to install and configure the Oracle Secure Backup Cloud Module for Amazon S3 to backup an Oracle database to the Cloud using the direct streaming method. This can be performed from an Amazon EC2 instance (off-premise) or from an Oracle database server running in your own data center (on-premise).

Oracle Secure Backup Cloud Module for Amazon S3

A traditional database backup for Oracle most often included running an RMAN backup to local disk and then migrating the backup to tape which would then be moved to an off-site location (e.g. Iron Mountain). With the introduction of Oracle Secure Backup Cloud Module for Amazon S3, a new approach can be implemented where RMAN securely performs an off-site backup directly to the Cloud. This new strategy is more efficient than tapes and results in faster restores by eliminating the overhead of off-site tape management. This method is also more reliable than tapes given the fact that Amazon S3 is designed to make several redundant copies of your data.

Oracle Secure Backup Cloud Module is a new library that interfaces with RMAN and Amazon S3 to perform database backups to the Cloud using the RMAN SBT (tape) interface. The SBT interface allows external backup libraries to be seamlessly integrated with RMAN. One of the key benefits to using the Oracle module approach is that it is fully compatible with existing backup tools, custom scripts, and Oracle Enterprise Manager (OEM).

The Oracle Secure Backup Cloud Module installer is available from the Oracle Technology Network (OTN) and is also included with all Oracle provided Amazon virtual machine images (AMIs).

You can backup databases starting with Oracle Database 9i Release 2 or later. This includes Oracle Standard Edition and Oracle Enterprise Edition. At the time of this writing, operating system files cannot be backed up with RMAN or the RMAN SBT interface.

The OSB Cloud Module for Amazon S3 is supported on the following platforms:

OSB Cloud Module for Amazon S3 is a viable option for a range of database sizes including larger sized databases. For example, Oracle Corporation released the following performance and cost results for backing up several different sized on-premise databases using parallel / compressed backups.

Cloud Module Backup Performance and Cost Example
DB Size (GB) Full DB Backup Time Incremental DB Backup Time Monthly Amazon S3 Cost
500 4 hours 30 minutes $200
300 2 hours 15 minutes $125
100 40 minutes 5 minutes $50

Oracle's future road map includes:

OSB Cloud Module for Amazon S3 Licensing

OSB Cloud Module for Amazon S3 is part of the Oracle Secure Backup product family and is licensed based on the number of concurrent parallel streams (RMAN channels) to an Amazon S3 destination.

A license can be shared among multiple Oracle Database backups so long as the number of RMAN channels for backup to the Cloud does not exceed the number of OSB Cloud Module for Amazon S3 licenses. For example, if an environment has 12 OSB Cloud Module for Amazon S3 licenses, then 3 Oracle Database backups could use 4 RMAN channels concurrently or 1 Oracle Database backup could use all 12 RMAN channels at one time as long as no other RMAN channels were being used.

The costs associated with Cloud storage are independent of the OSB Cloud Module for Amazon S3 license and are set by Amazon S3. Users must first create an AWS account and sign up for the Amazon S3 service in order to use OSB Cloud Module for Amazon S3.

Please note that the licensing information in this section is only as accurate as what Oracle has published on OTN and can change without notice. Always consult with your Oracle sales representative for accurate and detailed guidance.

Oracle Provided AMIs

If you are using any of the Oracle provided Amazon virtual machine images (AMIs), the OSB Cloud Module for Amazon S3 installer is already available in the /home/oracle/scripts/osbws directory.

To find all available Oracle provided AMIs, click the [Launch Instance] button in the AWS Management Console and select the "Classic Wizard" option. Click the "Community AMIs" tab and search all images with the account 725966715235 (the Oracle Team).

    

Figure 1: Oracle provided AMIs

The Oracle provided AMIs come pre-installed with Oracle Enterprise Linux and the Oracle Database software. A script runs automatically to guide users through the database creation process. As part of the creation process, an option is provided in the script to configure the OSB Cloud Module for Amazon S3. You will need access to your Amazon Web Services Access Key ID, Amazon Web Services Secret Access Key, and your Oracle Technology Network (OTN) Username and Password.

This guide assumes that the target machine is not an Oracle provided AMI and that the OSB Cloud Module for Amazon S3 will be manually installed and configured.

Requirements

Using the Oracle Secure Backup Cloud Module for Amazon S3 requires an account on OTN, an Amazon Web Services account, and the OSB Cloud Module for Amazon S3 software library and associated files (which get installed using osbws_install.jar).

Before running the OSB Cloud Module for Amazon S3 installer, verify that the following prerequisites are met.

  1. The OSB Cloud Module for Amazon S3 installer requires Java 1.5 or higher to run.


    $ java -version java version "1.6.0_20" OpenJDK Runtime Environment (IcedTea6 1.9.7) (rhel-1.39.1.9.7.el6-x86_64) OpenJDK 64-Bit Server VM (build 19.0-b09, mixed mode)

  2. Download the OSB Cloud Module for Amazon S3 installer (osbws_installer.zip) from OTN to the database server.

  3. Copy and unzip the OSB Cloud Module for Amazon S3 installer archive to a staging directory as the Oracle software owner (typically oracle).


    $ id uid=501(oracle) gid=501(oinstall) groups=501(oinstall),502(dba),503(oper) $ mkdir -p ~/software/osbws $ cp osbws_installer.zip ~/software/osbws $ cd ~/software/osbws $ unzip osbws_installer.zip Archive: osbws_installer.zip inflating: osbws_install.jar inflating: osbws_readme.txt

  4. Create a directory for the secure Oracle wallet. The Oracle wallet will be created by the installer and used to store your AWS S3 credentials.


    $ mkdir -p $ORACLE_HOME/dbs/osbws_wallet

  5. The authentication method used by Amazon S3 relies on the client's system time being similar to Amazon S3's time. In this case, the client is the machine where you run the OSB Web Services library. Amazon S3 time is Coordinated Universal Time (UTC), so you must ensure that the system time on your client is within a few minutes of UTC.

Install Oracle Secure Backup Cloud Module for Amazon S3

Install and configure Oracle Secure Backup Cloud Module for Amazon S3 using the steps described in this section.

  1. The OSB Cloud Module for Amazon S3 Installer

    Verify the environment by listing the valid set of input parameters for the OSB Cloud Module for Amazon S3 installer along with their descriptions.


    $ cd ~/software/osbws $ java -jar osbws_install.jar

    You can also review the osbws_readme.txt file for more details.

  2. Create OSB Cloud Module for Amazon S3 Installer Script

    Create a script that will invoke the OSB Cloud Module for Amazon S3 installer with the required input parameter values.


    $ vi osbws_install.sh java -jar osbws_install.jar \ -AWSID nnnnnnnnnnnnnn \ -AWSKey nnnnnnnnnnnnnnnnnnn \ -otnUser eric.cartman@southpark.com \ -otnPass xxxxxxxx \ -walletDir $ORACLE_HOME/dbs/osbws_wallet \ -libDir $ORACLE_HOME/lib \ -configFile $ORACLE_HOME/dbs/osbws<ORACLE_SID>.ora $ chmod +x osbws_install.sh

    [-AWSID] and [-AWSKey]

    Supply your AWS Access Key and Secret Key which serve the purpose of ID and Password to access Amazon S3. To obtain your AWS Access Key and Secret Key from the AWS website, navigate to Security Credentials, click on the Access Keys tab under Access Credentials to create or view your Access Key ID and Secret Access Key.

    (Mandatory)

    [-otnUser]

    Your OTN username which the installer uses to identify the customer.

    (Mandatory)

    [-otnPass]

    Your OTN password.

    (Mandatory)

    [-walletDir]

    Directory where you want the installer to create a secure wallet containing your AWS S3 credentials and proxy information (if a proxy server is specified).

    (Mandatory)

    [-libDir]

    Directory where you want the installer to download the OSB Cloud Module for Amazon S3 software library. The installer will automatically detect the underlying platform and download the appropriate port of the software library. Although an optional parameter, if it is omitted, the installer will not download the library. Usually you would want to download the library. The only reason to omit downloading the library is if you are using the install tool to regenerate the wallet and configuration file in an Oracle Home where the OSB Cloud Module for Amazon S3 has previously been installed.

    (Optional)

    [-configFile]

    The name of the initialization parameter file that will be created by the install tool. This parameter file will be referenced during your RMAN jobs for a particular database. If this parameter is not specified then the initialization parameter file will be created in a system-dependent default location and filename based on the ORACLE_SID. For example: $ORACLE_HOME/dbs/osbws<ORACLE_SID>.ora.

    (Optional)

  3. Run OSB Cloud Module for Amazon S3 Installer Script

    Run the OSB Cloud Module for Amazon S3 installer script created in the previous step. The installer will download the software library and configure the environment for running Oracle database backups to the Cloud (Amazon S3).


    $ ./osbws_install.sh Oracle Secure Backup Database Web-Service Install Tool, build 2011-02-04.0001 AWS credentials are valid. Creating new registration for this S3 user. Created new log bucket. Registration ID: 5b2c25ef-74cd-4b1d-a6d2-792e78c759c3 S3 Logging Bucket: oracle-log-idevjhun-1 Validating log bucket location ... Validating license file ... Create credential oracle.security.client.connect_string1 OSB web-services wallet created in directory /u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbws_wallet. OSB web-services initialization file /u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwstestdb1.ora created. Downloading OSB Web Services Software Library from file osbws_linux64.zip. Downloaded 20744341 bytes in 10 seconds. Transfer rate was 2074434 bytes/second. Download complete. Extracted file /u01/app/oracle/product/11.2.0/dbhome_1/lib/libosbws12.so

  4. Fix Media Management Library Loading Error

    The installer does not create the default media management library symbolic link for the OSB Cloud Module for Amazon S3 media management library. This results in the following RMAN error when attempting to allocate a channel of type sbt:


    RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of allocate command on c1 channel at 07/05/2012 15:07:25 ORA-19554: error allocating device, device type: SBT_TAPE, device name: ORA-27211: Failed to load Media Management Library Additional information: 2

    Manually create the following symbolic link for the default media management library before performing backups using the SBT interface.


    $ ln -s $ORACLE_HOME/lib/libosbws12.so $ORACLE_HOME/lib/libobk.so

    If you provided a non-default name and location for the OSB Cloud Module for Amazon S3 configuration file (-configFile), also create the following symbolic link.


    $ ln -s <configFile> $ORACLE_HOME/dbs/osbws<ORACLE_SID>.ora

  5. Modify Oracle Recovery Manager's Media Management Configuration

    The next step is to change the Oracle Recovery Manager (RMAN) media management configuration to redirect traditional tape backups to the Cloud (Amazon S3) in the RMAN repository. This avoids having to provide the configuration information each time a backup is invoked.

    The following example is for an Oracle 11g Release 2 database.

    Make certain to add a space after entering each line of the configure channel device type sbt parms ... command. I needed to specify the command on multiple lines to make it easier to read. Optionally change the default device type to SBT_TAPE and modify any retention settings.


    $ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jul 5 15:59:18 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: TESTDB1 (DBID=527025534) RMAN> configure channel device type sbt parms 2> 'SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libosbws12.so 3> SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwstestdb1.ora)'; using target database control file instead of recovery catalog new RMAN configuration parameters: CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libosbws12.so SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwstestdb1.ora)'; new RMAN configuration parameters are successfully stored RMAN> configure default device type to 'sbt_tape'; using target database control file instead of recovery catalog new RMAN configuration parameters: CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE'; new RMAN configuration parameters are successfully stored RMAN> configure archivelog deletion policy to backed up 1 times to 'tape'; new RMAN configuration parameters: CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'TAPE'; new RMAN configuration parameters are successfully stored RMAN> exit

     

    Note: For Oracle Database 11g Release 2 and later, you must use the SBT_PARMS parameter for specifying environment variables. For pre-Oracle Database 11g Release 2, you can still use the ENV parameter of the PARMS option to specify environment variables.

  6. Test the Media Management Software

    Use the Oracle diagnostic tool called sbttest to perform a simple test of the media management software. This utility will attempt to communicate with the media manager just as the Oracle database server would using RMAN using an example file.


    $ sbttest osbws_readme.txt -dbname testdb1 The sbt function pointers are loaded from libobk.so library. -- sbtinit succeeded -- sbtinit (2nd time) succeeded sbtinit: vendor description string=Oracle Secure Backup sbtinit: Media manager is version 3.0.0.0 sbtinit: Media manager supports SBT API version 2.0 sbtinit: allocated sbt context area of 1080 bytes -- sbtinit2 succeeded -- regular_backup_restore starts ................................ -- sbtbackup succeeded write 100 blocks -- sbtwrite2 succeeded -- sbtclose2 succeeded sbtinfo2: SBTBFINFO_NAME=osbws_readme.txt sbtinfo2: SBTBFINFO_COMMENT=Oracle Secure Backup Web Services Library sbtinfo2: SBTBFINFO_METHOD=stream sbtinfo2: SBTBFINFO_ORDER=random access sbtinfo2: SBTBFINFO_SHARE=multiple users sbtinfo2: SBTBFINFO_LABEL=s3.amazonaws.com/oracle-data-idevjhun-1 -- sbtinfo2 succeeded -- sbtrestore succeeded file was created by this program: seed=431357526, blk_size=16384, blk_count=100 read 100 buffers -- sbtread2 succeeded -- sbtclose2 succeeded -- sbtremove2 succeeded -- regular_backup_restore ends ................................ -- sbtcommand succeeded proxy copy is not supported -- sbtend succeeded *** The SBT API test was successful ***

  7. Additional Configuration

    With the basic install and configuration out of the way, you can further customize the software by modify parameters found in the OSB Cloud Module for Amazon S3 configuration file ($ORACLE_HOME/dbs/osbws<ORACLE_SID>.ora).

    To obtain a list of OSB_ related parameters, perform a strings on the OSB Cloud Module for Amazon S3 media management library (libosbws12.so).


    $ strings $ORACLE_HOME/lib/libosbws12.so | grep '^OSB_' | sort OSB_WS_BUCKET OSB_WS_CHUNK_SIZE OSB_WS_HOST OSB_WS_LICENSE_ID OSB_WS_LICENSE_MAX_SESSIONS OSB_WS_LOCATION OSB_WS_PFILE OSB_WS_PROXY OSB_WS_WALLET

    In addition to the basic configuration parameters, OSB Cloud Module for Amazon S3 also includes several hidden (underscored) parameters.


    $ strings $ORACLE_HOME/lib/libosbws12.so | grep '^_OSB_' | sort _OSB_WS_100_CONTINUE _OSB_WS_ACCESSLOG _OSB_WS_ACCESSLOG_CLEANER _OSB_WS_ACCESSLOG_RETENTION_TIME _OSB_WS_ALLOCBUF_DISABLE _OSB_WS_BASIC_AUTH _OSB_WS_BUFFER_READ _OSB_WS_BUFFER_WRITE _OSB_WS_CHECKER_RETENTION_TIME _OSB_WS_CLEANER _OSB_WS_CLEANER_RETENTION_TIME _OSB_WS_DEFERRED_DELETE _OSB_WS_EVENT _OSB_WS_IP_REFRESH_TIMEOUT _OSB_WS_LOAD_BALANCE _OSB_WS_LOG_BUCKET _OSB_WS_PURGE_LIMIT _OSB_WS_RECV_BUF_SIZE _OSB_WS_REDIRECT _OSB_WS_RESPONSE_TIMEOUT _OSB_WS_RETRY_WAIT_TIME _OSB_WS_REUSE_CONNECTION _OSB_WS_RUN_CLEANER _OSB_WS_SDU_SIZE _OSB_WS_SEND_BUF_SIZE _OSB_WS_SESSION_RETENTION_TIME _OSB_WS_TDU_SIZE _OSB_WS_TRACE_LEVEL _OSB_WS_UPLOAD_DELAY _OSB_WS_USE_IPV6 _OSB_WS_VALIDATE_CHUNK

Perform an Oracle Database Backup to the Cloud

At this point, you can issue your usual RMAN backup and restore commands.


$ vi rman_testdb1.cmd run { allocate channel ch1 type sbt; allocate channel ch2 type sbt; allocate channel ch3 type sbt; allocate channel ch4 type sbt; backup as compressed backupset database; sql 'alter system archive log current'; backup as compressed backupset archivelog all not backed up; backup current controlfile; release channel ch1; release channel ch2; release channel ch3; release channel ch4; }


$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Sun Jul 8 11:05:29 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: TESTDB1 (DBID=527025534) RMAN> @rman_testdb1.cmd RMAN> run { 2> allocate channel ch1 type sbt; 3> allocate channel ch2 type sbt; 4> allocate channel ch3 type sbt; 5> allocate channel ch4 type sbt; 6> 7> backup as compressed backupset database; 8> sql 'alter system archive log current'; 9> backup as compressed backupset archivelog all not backed up; 10> backup current controlfile; 11> 12> release channel ch1; 13> release channel ch2; 14> release channel ch3; 15> release channel ch4; 16> } using target database control file instead of recovery catalog allocated channel: ch1 channel ch1: SID=25 device type=SBT_TAPE channel ch1: Oracle Secure Backup Web Services Library allocated channel: ch2 channel ch2: SID=150 device type=SBT_TAPE channel ch2: Oracle Secure Backup Web Services Library allocated channel: ch3 channel ch3: SID=161 device type=SBT_TAPE channel ch3: Oracle Secure Backup Web Services Library allocated channel: ch4 channel ch4: SID=42 device type=SBT_TAPE channel ch4: Oracle Secure Backup Web Services Library Starting backup at 08-JUL-2012 11:06:06 channel ch1: starting compressed full datafile backup set channel ch1: specifying datafile(s) in backup set input datafile file number=00001 name=/u02/app/oracle/oradata/TESTDB1/datafile/o1_mf_system_7mckjxwx_.dbf input datafile file number=00005 name=/u02/app/oracle/oradata/TESTDB1/datafile/o1_mf_users_7mcknsn6_.dbf channel ch1: starting piece 1 at 08-JUL-2012 11:06:07 channel ch2: starting compressed full datafile backup set channel ch2: specifying datafile(s) in backup set input datafile file number=00002 name=/u02/app/oracle/oradata/TESTDB1/datafile/o1_mf_sysaux_7mckky96_.dbf channel ch2: starting piece 1 at 08-JUL-2012 11:06:08 channel ch3: starting compressed full datafile backup set channel ch3: specifying datafile(s) in backup set input datafile file number=00003 name=/u02/app/oracle/oradata/TESTDB1/datafile/o1_mf_undotbs1_7mcklxhg_.dbf input datafile file number=00004 name=/u02/app/oracle/oradata/TESTDB1/datafile/o1_mf_example_7zmyzfmf_.dbf channel ch3: starting piece 1 at 08-JUL-2012 11:06:08 channel ch4: starting compressed full datafile backup set channel ch4: specifying datafile(s) in backup set including current control file in backup set channel ch4: starting piece 1 at 08-JUL-2012 11:06:16 channel ch4: finished piece 1 at 08-JUL-2012 11:06:23 piece handle=76nflbp0_1_1 tag=TAG20120709T110607 comment=API Version 2.0,MMS Version 3.0.0.0 channel ch4: backup set complete, elapsed time: 00:00:07 channel ch4: starting compressed full datafile backup set channel ch4: specifying datafile(s) in backup set including current SPFILE in backup set channel ch4: starting piece 1 at 08-JUL-2012 11:06:24 channel ch4: finished piece 1 at 08-JUL-2012 11:06:31 piece handle=77nflbpg_1_1 tag=TAG20120709T110607 comment=API Version 2.0,MMS Version 3.0.0.0 channel ch4: backup set complete, elapsed time: 00:00:07 channel ch3: finished piece 1 at 08-JUL-2012 11:08:59 piece handle=75nflbp0_1_1 tag=TAG20120709T110607 comment=API Version 2.0,MMS Version 3.0.0.0 channel ch3: backup set complete, elapsed time: 00:02:51 channel ch2: finished piece 1 at 08-JUL-2012 11:18:59 piece handle=74nflbp0_1_1 tag=TAG20120709T110607 comment=API Version 2.0,MMS Version 3.0.0.0 channel ch2: backup set complete, elapsed time: 00:12:51 channel ch1: finished piece 1 at 08-JUL-2012 11:20:09 piece handle=73nflbov_1_1 tag=TAG20120709T110607 comment=API Version 2.0,MMS Version 3.0.0.0 channel ch1: backup set complete, elapsed time: 00:14:02 Finished backup at 08-JUL-2012 11:20:09 sql statement: alter system archive log current Starting backup at 08-JUL-2012 11:20:14 current log archived channel ch1: starting compressed archived log backup set channel ch1: specifying archived log(s) in backup set input archived log thread=1 sequence=770 RECID=104 STAMP=788128762 input archived log thread=1 sequence=771 RECID=105 STAMP=788140813 channel ch1: starting piece 1 at 08-JUL-2012 11:20:16 channel ch2: starting compressed archived log backup set channel ch2: specifying archived log(s) in backup set input archived log thread=1 sequence=772 RECID=106 STAMP=788145391 input archived log thread=1 sequence=773 RECID=107 STAMP=788161153 channel ch2: starting piece 1 at 08-JUL-2012 11:20:16 channel ch3: starting compressed archived log backup set channel ch3: specifying archived log(s) in backup set input archived log thread=1 sequence=774 RECID=108 STAMP=788181614 input archived log thread=1 sequence=775 RECID=109 STAMP=788181615 channel ch3: starting piece 1 at 08-JUL-2012 11:20:17 channel ch3: finished piece 1 at 08-JUL-2012 11:21:22 piece handle=7anflcjh_1_1 tag=TAG20120709T112015 comment=API Version 2.0,MMS Version 3.0.0.0 channel ch3: backup set complete, elapsed time: 00:01:05 channel ch1: finished piece 1 at 08-JUL-2012 11:22:52 piece handle=78nflcjg_1_1 tag=TAG20120709T112015 comment=API Version 2.0,MMS Version 3.0.0.0 channel ch1: backup set complete, elapsed time: 00:02:36 channel ch2: finished piece 1 at 08-JUL-2012 11:23:02 piece handle=79nflcjg_1_1 tag=TAG20120709T112015 comment=API Version 2.0,MMS Version 3.0.0.0 channel ch2: backup set complete, elapsed time: 00:02:46 Finished backup at 08-JUL-2012 11:23:02 Starting backup at 08-JUL-2012 11:23:04 channel ch1: starting full datafile backup set channel ch1: specifying datafile(s) in backup set including current control file in backup set channel ch1: starting piece 1 at 08-JUL-2012 11:23:07 channel ch1: finished piece 1 at 08-JUL-2012 11:23:52 piece handle=7bnflcoo_1_1 tag=TAG20120709T112304 comment=API Version 2.0,MMS Version 3.0.0.0 channel ch1: backup set complete, elapsed time: 00:00:45 Finished backup at 08-JUL-2012 11:23:52 released channel: ch1 released channel: ch2 released channel: ch3 released channel: ch4 RMAN> **end-of-file** RMAN> list backup summary; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- -------------------- ------- ------- ---------- --- 108 B F A SBT_TAPE 08-JUL-2012 11:06:19 1 1 YES TAG20120709T110607 109 B F A SBT_TAPE 08-JUL-2012 11:06:27 1 1 YES TAG20120709T110607 110 B F A SBT_TAPE 08-JUL-2012 11:08:52 1 1 YES TAG20120709T110607 111 B F A SBT_TAPE 08-JUL-2012 11:18:59 1 1 YES TAG20120709T110607 112 B F A SBT_TAPE 08-JUL-2012 11:20:03 1 1 YES TAG20120709T110607 113 B A A SBT_TAPE 08-JUL-2012 11:21:20 1 1 YES TAG20120709T112015 114 B A A SBT_TAPE 08-JUL-2012 11:22:51 1 1 YES TAG20120709T112015 115 B A A SBT_TAPE 08-JUL-2012 11:23:01 1 1 YES TAG20120709T112015 116 B F A SBT_TAPE 08-JUL-2012 11:23:43 1 1 NO TAG20120709T112304 Note: This is one of my test systems. Do not perform the following test on a mission critical production system. You've been warned. RMAN> report schema; Report of database schema for database with db_unique_name TESTDB1 List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 800 SYSTEM *** /u02/app/oracle/oradata/TESTDB1/datafile/o1_mf_system_7mckjxwx_.dbf 2 900 SYSAUX *** /u02/app/oracle/oradata/TESTDB1/datafile/o1_mf_sysaux_7mckky96_.dbf 3 500 UNDOTBS1 *** /u02/app/oracle/oradata/TESTDB1/datafile/o1_mf_undotbs1_7mcklxhg_.dbf 4 350 EXAMPLE *** /u02/app/oracle/oradata/TESTDB1/datafile/o1_mf_example_7zmyzfmf_.dbf 5 100 USERS *** /u02/app/oracle/oradata/TESTDB1/datafile/o1_mf_users_7mcknsn6_.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 500 TEMP 8192 /u02/app/oracle/oradata/TESTDB1/datafile/o1_mf_temp_7mckmk6d_.tmp RMAN> host 'rm /u02/app/oracle/oradata/TESTDB1/datafile/o1_mf_example_7zmyzfmf_.dbf'; host command complete ===================================================== # User error SQL> select count(*) from hr.employees; select count(*) from hr.employees * ERROR at line 1: ORA-01116: error in opening database file 4 ORA-01110: data file 4: '/u02/app/oracle/oradata/TESTDB1/datafile/o1_mf_example_7zmyzfmf_.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ===================================================== RMAN> sql 'alter database datafile 4 offline'; sql statement: alter database datafile 4 offline RMAN> restore datafile 4; Starting restore at 08-JUL-2012 11:30:12 allocated channel: ORA_SBT_TAPE_1 channel ORA_SBT_TAPE_1: SID=25 device type=SBT_TAPE channel ORA_SBT_TAPE_1: Oracle Secure Backup Web Services Library allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=150 device type=DISK channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00004 to /u02/app/oracle/oradata/TESTDB1/datafile/o1_mf_example_7zmyzfmf_.dbf channel ORA_SBT_TAPE_1: reading from backup piece 75nflbp0_1_1 channel ORA_SBT_TAPE_1: piece handle=75nflbp0_1_1 tag=TAG20120709T110607 channel ORA_SBT_TAPE_1: restored backup piece 1 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:35 Finished restore at 08-JUL-2012 11:30:50 RMAN> recover datafile 4; Starting recover at 08-JUL-2012 11:31:31 using channel ORA_SBT_TAPE_1 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:02 Finished recover at 08-JUL-2012 11:31:34 RMAN> sql 'alter database datafile 4 online'; sql statement: alter database datafile 4 online ===================================================== # Datafile restored SQL> select count(*) from hr.employees; COUNT(*) ---------- 107 =====================================================

Cloud Backup Best Practices

Securing Data in the Cloud

It is highly recommended to encrypt your Cloud backups. Encrypting backups ensures that your data remains secure and protected against unauthorized access. Please refer to the Oracle Backup and Recovery User's Guide to learn more about the RMAN commands used to configure backup encryption. Encryption can also be enabled while scheduling backups through Oracle Enterprise Manager (OEM).

For example, to use Password Encryption of Backups (as apposed to Transparent Encryption of Backups) include the SET ENCRYPTION ON IDENTIFIED BY password ONLY command in your RMAN scripts. Password encryption cannot be persistently configured. Password Encryption of Backups requires the DBA to provide a password when creating and restoring encrypted backup. Restoring a password-encrypted backup requires the same password that was used to create the backup. Note that while this example uses password encryption, Oracle strongly recommends using the transparent encryption method (master key and wallet) to best secure your sensitive data.

 

If you forget or lose the password that you used to encrypt a password-encrypted backup, then you cannot restore the backup.


RMAN> set encryption on for all tablespaces algorithm 'AES128' identified by myPassword only; executing command: SET encryption using target database control file instead of recovery catalog RMAN> backup as compressed backupset datafile 4; Starting backup at 08-JUL-2012 11:36:16 allocated channel: ORA_SBT_TAPE_1 channel ORA_SBT_TAPE_1: SID=30 device type=SBT_TAPE channel ORA_SBT_TAPE_1: Oracle Secure Backup Web Services Library channel ORA_SBT_TAPE_1: starting compressed full datafile backup set channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set input datafile file number=00004 name=/u02/app/oracle/oradata/TESTDB1/datafile/o1_mf_example_7zoy8odq_.dbf channel ORA_SBT_TAPE_1: starting piece 1 at 08-JUL-2012 11:36:18 channel ORA_SBT_TAPE_1: finished piece 1 at 08-JUL-2012 11:37:33 piece handle=7cnfldhi_1_1 tag=TAG20120709T113617 comment=API Version 2.0,MMS Version 3.0.0.0 channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:01:15 Finished backup at 08-JUL-2012 11:37:33 RMAN> host 'rm /u02/app/oracle/oradata/TESTDB1/datafile/o1_mf_example_7zoy8odq_.dbf'; host command complete RMAN> sql 'alter database datafile 4 offline'; sql statement: alter database datafile 4 offline RMAN> restore datafile 4; Starting restore at 08-JUL-2012 11:39:38 using channel ORA_SBT_TAPE_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=161 device type=DISK channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00004 to /u02/app/oracle/oradata/TESTDB1/datafile/o1_mf_example_7zoy8odq_.dbf channel ORA_SBT_TAPE_1: reading from backup piece 7cnfldhi_1_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 07/08/2012 11:39:55 ORA-19870: error while restoring backup piece 7cnfldhi_1_1 ORA-19913: unable to decrypt backup ORA-28365: wallet is not open RMAN> set decryption identified by myPassword; executing command: SET decryption RMAN> restore datafile 4; Starting restore at 08-JUL-2012 11:42:07 using channel ORA_SBT_TAPE_1 using channel ORA_DISK_1 channel ORA_SBT_TAPE_1: starting datafile backup set restore channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set channel ORA_SBT_TAPE_1: restoring datafile 00004 to /u02/app/oracle/oradata/TESTDB1/datafile/o1_mf_example_7zoy8odq_.dbf channel ORA_SBT_TAPE_1: reading from backup piece 7cnfldhi_1_1 channel ORA_SBT_TAPE_1: piece handle=7cnfldhi_1_1 tag=TAG20120709T113617 channel ORA_SBT_TAPE_1: restored backup piece 1 channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:25 Finished restore at 08-JUL-2012 11:42:34 RMAN> recover datafile 4; Starting recover at 08-JUL-2012 11:43:16 using channel ORA_SBT_TAPE_1 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 08-JUL-2012 11:43:19 RMAN> sql 'alter database datafile 4 online'; sql statement: alter database datafile 4 online

Optimizing Cloud Backup Performance

The following recommendations should be considered in order to optimize backup performance to the Cloud. This is especially true when working over a slower network. Many of the performance results discussed in this section were the result of internal tests performed by Oracle Corporation and provided in a white paper titled "Cloud-Stored Offsite Database Backups" (published May 2010).

Cloud-Stored Database Backups over the Public Internet

When performing Cloud based backups over the public Internet, performance will depend on your Internet network throughput which often times can be less than 1 MB/sec (8 Mbits/sec) per connection. In addition, Cloud vendors like Amazon may throttle sessions to prevent individual users from consuming disproportionate amounts of resources. According to internal tests conducted at Oracle, Amazon S3 limits an individual session's read/write throughput to around 2-3 MB/sec. This makes backing up databases larger than a couple hundred Gigabytes a challenge (if not impossible) when relying on the public Internet and slow data transfer rates.

Amazon AWS Direct Connect service is an option which can significantly enhance network throughput. The Amazon AWS Direct Connect service allows you to establish a direct connection from your on-premise network to Amazon VPC using one or more 1 Gbps and 10 Gbps connections. There is no charge for IN data transfers, which makes it ideally suited for backup purposes. For more information including pricing, consult the following documents:

Compression

Compression helps overcome network bandwidth limitations. For an on-premise database at Oracle HQ, the use of compression resulted in a 4X gain in backup speed. Using the Oracle Database 11g Advanced Compression option is significantly faster and more efficient (in terms of CPU overhead) than pre-11g compression.


RMAN> backup as compressed backupset database;

You can also make the compressed backupset option persistent for the SBT interface.


RMAN> configure device type sbt backup type to compressed backupset;

Parallel Streams

Using parallel streams (RMAN channels) can also speed up Cloud backups - particularly for on-premise databases. Use multiple RMAN channels for higher parallelism which results in full utilization of the network. For example, Oracle was able to achieve peak performance of 40 MB/sec (320 Mbits/sec) for an on-premise database at Oracle HQ using 64 channels (and compression).


run { allocate channel ch1 type sbt; ... allocate channel ch[n] type sbt; backup as compressed backupset database; release channel ch1; ... release channel ch[n]; }

You can also make the parallelism option persistent for the SBT interface.


RMAN> configure device type sbt parallelism 8;

Multisection Backups

To further improve efficiency and performance, consider using multisection backups. Oracle Database 11g allows multiple RMAN channels to backup a contiguous block range of a single datafile in parallel. This contiguous block range is known as a file section. Multisection backups increase parallelism beyond the number of datafiles to be backed up which was the lowest unit of RMAN parallelism prior to Oracle Database 11g. Up to 256 RMAN channels can be allocated per datafile.

File sections enable RMAN to create multiple steps for the backup of a single large data file. RMAN channels can process each step independently and in parallel, with each channel producing one section of a multisection backup set. The size of the file section is controlled by the SECTION SIZE parameter of the BACKUP command which tells RMAN to create a backup set where each backup piece contains blocks from one file section. A backup piece is produced for each block range and once complete, the channel is allocated the next block range until such time that the entire data file is backed up.

The following multisection backup example will backup a tablespace (APPDATA) containing a single 1 GB datafile to be backed up in 4x250 GB sections.


configure device type sbt parallelism 4; run { backup as compressed backupset section size 250m tablespace appdata; }

Consider the following points related to multisection backups when trying to decide if this option would be useful in enhancing backup performance to the Cloud.

The following is an RMAN script that uses multisection backup to the Cloud with 8 RMAN channels. This is an example of a database that contains unevenly sized tablespaces and datafiles. For example, I want to use a section size that best optimizes all files in the APPDATA tablespace (since they are all similarly sized) while specifying a unique section size for unevenly sized datafiles in the APPLOB tablespace. Finding the right balance can significantly improve performance when backing up large and unevenly sized tablespaces to the Cloud.


$ vi rman_testdb1_multisection.cmd run { allocate channel ch1 type sbt; allocate channel ch2 type sbt; allocate channel ch3 type sbt; allocate channel ch4 type sbt; allocate channel ch5 type sbt; allocate channel ch6 type sbt; allocate channel ch7 type sbt; allocate channel ch8 type sbt; configure exclude for tablespace 'APPDATA'; configure exclude for tablespace 'APPLOB'; backup as compressed backupset database; configure exclude for tablespace 'APPDATA' clear; configure exclude for tablespace 'APPLOB' clear; backup as compressed backupset section size 250g tablespace APPDATA; backup as compressed backupset section size 250g datafile '/u02/app/oracle/oradata/TESTDB1/datafile/o1_mf_applob_7zpkj1v2_.dbf'; backup as compressed backupset section size 500g datafile '/u02/app/oracle/oradata/TESTDB1/datafile/o1_mf_applob_7zpkp1w1_.dbf'; sql 'alter system archive log current'; backup as compressed backupset archivelog all not backed up; backup current controlfile; release channel ch1; release channel ch2; release channel ch3; release channel ch4; release channel ch5; release channel ch6; release channel ch7; release channel ch8; }

Incremental Backups

Consider making full database backups once a week and performing incremental backups during the weekdays. This will results in faster backups and may help save significant amount of network bandwidth. Use the RMAN Block Change Tracking feature to further optimize the performance of your daily incremental backups.

Conclusion

At the end of their Cloud backup testing, Oracle was able to conclude that when using the right combination of parallelism and compression, backup speeds of up to 40-50 MB/sec (320-400 Mbits/sec) could be attained provided sufficient network bandwidth.

Cloud Backup Performance (performed by Oracle)
Test Environment Uncompressed Backup Speed
(Network Throughput)
Compressed Backup Speed
(Network Throughput)
Full DB Backup Time
(250 GB)
Incremental Backup Time
(10% delta)
DB at Oracle HQ
(8 x 2 GHz CPU, 16 GB RAM)
10 MB/sec
(64 RMAN Channels)
40 MB/sec
(64 RMAN Channels)
2-6 hours 30 - 60 minutes
DB within Amazon Cloud
(Extra Large EC2 Instance)
32 MB/sec
(16 RMAN Channels)
50 MB/sec +
(32 RMAN Channels / Constrained by CPU)
2 hours < 20 minutes

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX / Linux server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 20 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science and Mathematics.



Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Friday, 11-Apr-2014 17:59:15 EDT
Page Count: 7012