Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted March 12, 2018


Full Text Search: The Key to Better Natural Language Queries for NoSQL in Node.js

Backup to the Oracle Cloud

By David Fitzjarrell

A number of tools and utilities are available for database backup and recovery, utilizing various storage technologies. Some shops backup directly to disk, to make the database backups immediately available should they be needed. This, of course, requires a plan to archive older backups so that local disk storage can be used to its best advantage. Others use tape devices, to provide the option of off-site storage to protect the database backups should the local facility suffer extreme or irreparible damage. Lately the 'cloud' has been a popular location for databases and backups, to reduce the cost by eliminating (hopefully) the need for on-site hardware. A number of cloud vendors provide such services, Oracle included. The good thing regarding Oracle's cloud backup and recovery services is that they utilize an access method many shops are already using or are, at least, familiar with. Let's look at the basics of using Oracle's cloud backup and recovery services.

Like all cloud vendors it's required that a Cloud Service account be created to access such services. Trial accounts are available to give cloud backup and recovery a test run; to see what needs to be done to establish an account, Oracle's instructions are found here. [Note that to backup to the cloud service, encryption needs to be used. If the database is using TDE then the backup module will use it to encrypt the backups. If not, a password will need to be set to enable encryption.] Once an account has been set up the next step is to install the Oracle Database Cloud Backup Module into each ORACLE_HOME location where cloud backups are to be run.

Installing the module requires that the installation archive be downloaded; this is available from Oracle. Full instructions for the installation are found here. It's a lengthy document; the information in it won't be repeated here so visit the URL to download the Backup Module and install it.

In some shops each Oracle database has its own ORACLE_HOME; in others multiple databases run from a single ORACLE_HOME. The initial installation is the same for either configuration; running multiple databases from a single home makes configuring those databases easier as they will all use the same library location. The installer will create two files, the shared object library used to access the cloud backup service ( and a configuration file for the database that will be using the service (opcSID.ora). The opcSID.ora file contains information specific to the installation; an example is shown below:

OPC_WALLET='LOCATION=file:/u01/oracle/tde/wallet CREDENTIAL_ALIAS=alias_opc'

To configure multiple databases under the same ORACLE_HOME the procedure is simple: copy the opcSID.ora file with the next database SID in the name, such as copying opcmydb1.ora to opcmydb2.ora. For databases with dedicated homes the installation procedure needs to be executed for each database to use the backup service. Once the Cloud Backup Module is installed the next step is to configure an SBT device to access the resource in RMAN. Sample instructions, issued from the RMAN> prompt, are shown below:

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/u01/app/oracle/lib/, SBT_PARMS=(OPC_PFILE=/u01/app/oracle/dbs/opcmydb1.ora)';

The PARMS parameter provides the cloud configuration information so that the backup will go to the cloud. For multiple databases using the same ORACLE_HOME these settings must be submitted through RMAN to the connected database (and yes, if there are 16 databases using /u01/app/oracle then all 16 need to be connected to go through RMAN so they can be configured for the cloud backup service). Once configured RMAN will use these settings and send all backups to the cloud. It's now time to test the configuration; from the RMAN> prompt the following will be executed, and in this example TDE is configured:


If RMAN is version 12 or later SQL commands can be directly executed without the 'sql' precursor so a 12.x backup script for a cold backup could look like this:

shutdown immediate;
startup mount;

# SBT_TAPE is the default device type so it doesn't
# need to be specified here

# If one is brave and fearless the database can be
# shutdown, started/mounted (before taking the backup)
# and opened (after the backup completes) from RMAN
# For safety such activities should be completed from
# SQL*Plus to prevent any mishaps
alter database open;

# The backup is complete so exit

Obviously, the DBA should verify that the database being backed up is the 'correct' database, which won't be an issue if this is manually executed; safeguards should be in place to verify that the desired database is being used for scheduled, unattended backups (and such scripts should be rigorously tested before using them in production) to prevent nasty emails from users whose work has been abruptly interrupted.

Recovery is just as simple once the groundwork has been done:


It's also possible to restore the spfile and controlfiles prior to a restore and recover operation. Start the database in NOMOUNT mode and if necessary, open the keystore. Then a script similar to that shown below can be used to recover the spfile:

set controlfile autobackup format for device type sbt to '%F';
run {
  allocate channel c1 device type sbt  PARMS  'SBT_LIBRARY=/u01/app/oracle/lib/, SBT_PARMS=(OPC_PFILE=/u01/app/oracle/dbs/opcmydb1.ora)';
  restore spfile from autobackup;
  shutdown immediate
  startup nomount

It may be necessary to open the keystore if there isn't an autologin wallet in use. Once the spfile is recovered and in use a similar script will restore the controlfiles, restore and then recover the database:

set controlfile autobackup format for device type sbt to '%F';
run {
  allocate channel c1 device type sbt  PARMS  'SBT_LIBRARY=/u01/app/oracle/lib/, SBT_PARMS=(OPC_PFILE=/u01/app/oracle/dbs/opcmydb1.ora)';
  restore controlfile from autobackup;
  alter database mount;

# Restore and recover the database


# Open the recovered database
alter database open resetlogs;

# Exit gracefully

Backup to and recovery from the Oracle cloud are now possible. The cloud dashboard will provide access to account activity and estimated costs, so expenses can be anticipated.

Hopefully this has provided a starting point for using Oracle Cloud Services for database backups; it is not an exhaustive treatise on the subject so read the documentation supplied by Oracle to understand the process, the backup availability and the limitations. It is a reliable method for backups and using cloud services may, in the long run, save money on hardware acquisition and maintenance and lower storage costs (for those backup services that charge by the gigabyte for storage used).

See all articles by David Fitzjarrell

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM