Note

You can download this article as a PDF

Oracle plugin

Overview
Presentation
Using Oracle Plugin

Overview 

This user’s guide presents various techniques and strategies to backup Oracle with Bacula Enterprise.

Scope

This paper will present solutions for Bacula Enterprise 6.4 and later, which are not applicable to prior versions.

The Oracle Plugin has been tested with AIX 7.1 and 7.3 versions as well as a variety of Linux distributions.

Convention Used In This Guide

<SID> Anything between < and > should be adapted by the user, for example, <SID> should be replaced by your current ORACLE_SID. If you ORACLE_SID is TEST a file written as init<SID>.ora will become initTEST.ora.
% means that the command should be run with a normal user such as oracle
# means that the command should be run with the root account.
RMAN> means that the command should by run inside a rman session.
SQL> means that the command should by run inside a sqlplus session

The Bacula Enterprise Oracle Plugin is designed to simplify the backup and restore procedure of your Oracle Database instance, the backup administrator don’t need to learn about internals of Oracle backup techniques or write complex scripts. The Bacula Enterprise Oracle plugin supports both dump and Point In Time Recovery (PITR) with RMAN backup techniques.

In both modes, the plugin will also backup essential information which is part of Oracle DBAs best practices.

When using RMAN mode, the Bacula Enterprise Plugin is able to do incremental and differential backup of the database at a block level.

In 6.4.x and later version of the Bacula Enterprise, the Oracle Plugin implements the RMAN API SBT version 2 that can avoid writing files to local disk first. Note that the Proxy Copy feature is not implemented. The oracle-sbt Plugin requires a Director running in 6.4.x version.

This plugin is available on Linux platforms 32/64bit supported by Oracle, and supports Oracle Database versions from 11.x to 21c, including Oracle RAC.

The Oracle plugin is compatible with Copy/Migration jobs. Please read the blb:MigrationChapter for more information.

Using Oracle Plugin 

Choosing Between Dump and RMAN

Please note that Oracle, as of version 11g, does no longer support dumping and loading for backup purposes. Existing backup schemes and recovery processes may need to be updated.

Warning

Oracle 11 and later deprecate dump mode!

The following table might help you to choose between backup techniques supported by the Bacula Enterprise Oracle Plugin. Major functionnalities such as beeing able to restore your database to any point in time, or being able to filter objects during backup or restore should guide you. It is also quite common to combine Dump and RMAN PITR techniques for the same cluster.

RMAN also allows you to use advanced techniques where you can send the data through the Bacula Enterprise SBT interface to tape, and, for example, keep it on disk for fast restore and disaster recovery.

	Dump	RMAN	RMAN SBT
Can restore directly a single object (table, schema…)	Yes	No	No
Can restore directly a single file (index, db, tbs…)	No	Yes	Yes
Backup speed	Low	High	High
Restore speed	Low	High	High*
Backup size	Small	Big	Big
Size on local disk during backup	Nothing	Entire Backup	Nothing
Size on local disk during restore	Nothing	Entire Restore	Objects needed
Can restore to any point in time	No	Yes	Yes
Incremen tal/Differential support	No	Yes	Yes
Can restore in parallel	Yes	Yes	Yes
Online backup	Yes	Yes	Yes
Consistent	Yes	Yes	Yes
Can restore to previous major version of Oracle	No	No	No
Can restore to newer major version of Oracle	Yes	?	?
Can backup large database ($>$ 50GB)	No	Yes	Yes

* When using RMAN SBT interface, the restore speed depends on various elements such as the type of media (tape or disk), the network speed, the availability of the Storage Daemon device, etc… In Bacula Enterprise 6.4 and later, the Storage Daemon device is able to use the same disk volume for multiple concurrent restores, so it’s possible to use concurrent jobs to restore and backup the data.

Installation

The Oracle plugin is available as a Bacula Enterprise package for all supported Oracle platforms.

bacula-enterprise-oracle-6.4.0-1.rh5.i586.rpm

You need to install this plugin on the Client where your Oracle database resides. The Plugin assumes that your instances are listed in /etc/oratab and the Oracle Unix user “oracle” is member of the DBA Unix group (usually “dba” or “oracle”).

The package contains the following files:

/opt/bacula/scripts/bs_oracle_restore.pl
/opt/bacula/scripts/install-sbt-libobk.sh
/opt/bacula/plugins/oracle-fd.so
/opt/bacula/plugins/oracle-sbt-fd.so

On Debian system, if you plan to use the Oracle RMAN interface, you will need to install the bacula-enterprise-console package.

Plugin Configuration

As with all Bacula plugins, you must to specify the Plugin Directory directive in the FileDaemon resource of the bacula-fd.conf file.

FileDaemon {
  Name = test-fd
  ...
  Plugin Directory = /opt/bacula/plugins
}

In order to send commands to the Oracle database, the Bacula Enterprise Oracle Plugin must share files on disk with Oracle. When using packages provided by Bacula Systems, these files are located on /opt/bacula/oracle and the directory permissions should be:

% ls -ld /opt/bacula/oracle
drwxrwx--- 13 root dba 4096 Mar 28 14:04 /opt/bacula/oracle

where dba is the main group of the oracle Unix user. This permission is automatically set when installing packages, however, if your Oracle Unix user is not “oracle”, you may need to manually set permissions on this directory yourself and make sure that your changes are still in effect after each upgrade of the Bacula Enterprise Oracle Plugin package.

Some files that must be preserved between backups are located in:

/opt/bacula/working/<SID>/

If the Oracle Plugin is unable to find them during the backup, it will display a message and force a Full backup at the RMAN level.

RMAN SBT Configuration

This part of the user’s guide will describe how to install and configure properly the Bacula Enterprise RMAN interface with Oracle and RMAN.

When running a backup or a restore from RMAN, RMAN will need to contact the Bacula Enterprise Director to get information about files and volumes, or run backup and restore jobs. This communication involves shared FIFO command files, and the bconsole program.

When using the oracle-sbt-fd plugin, the Director will not be able to start a backup from Bconsole or from a Schedule. Only RMAN will be able to initiate the session and start a Backup. Note that you can still run a regular system backup of your Oracle server, and then, use a RunScript to call RMAN automatically.

Bacula Configuration

When using the RMAN interface, Bacula console bconsole should be installed, and the console should be able to connect to your Director and have access to the local Client, the backup Job and other Pool specifications.

The access to the Director via bconsole, a restricted console should be properly configured on the Client:

# cat /opt/bacula/oracle/bconsole.conf

# Bacula User Agent (or Console) Configuration File
#

Director {
  Name = "oracle11-dir"
  DirPort = 9101
  Address = 192.168.0.46
  Password = "NotUsed"
}

Console {
  Name = "oracle-fd"
  Password = "pass"
}

To use a restricted console, you may use the following Console definition:

Client {
  Name = oracle-fd
  Maximum Concurrent Jobs = 10
  ...
}
Console {
  Name = oracle-fd
  Password = "pass"

  CommandACL = .bvfs_lsfiles, .bvfs_get_volumes, use, .bvfs_get_jobids, wait
  CommandACL = .bvfs_restore, .bvfs_cleanup, restore, run, gui, .jobs, quit

  # These commands are used only by the install-sbt-libobk.sh test
  # procedure and can be commented out after the installation
  CommandACL = show, status

  ClientACL  = oracle-fd
  JobACL     = SBT-Backup, RestoreJob

  CatalogACL = *all*
  StorageACL = *all*
  FileSetACL = *all*
  PoolACL    = *all*
  WhereACL   = /

  # Available with Bacula 8.8
  DirectoryAcl = *all*
  UserIdAcl = *all*
}

Job {
  Name    = SBT-Backup
  FileSet = SBT-FileSet
  Client  = oracle-fd
  Maximum Concurrent Jobs = 10

  # Adapt the following resources
  # to your settings
  Messages = Standard
  Pool     = Default
  Storage  = File
}
FileSet {
  Name = SBT-FileSet
  Include {
    Options {
       Signature = MD5
    }
    Plugin = oracle-sbt
  }
}

The unix “oracle” user should be able to execute bconsole and read the associated configuration file bconsole.conf, which is not the default configuration. You can copy the configuration file to the /opt/bacula/oracle directory with the following unix commands:

cp /opt/bacula/etc/bconsole.conf /opt/bacula/oracle
chown oracle:dba  /opt/bacula/oracle/bconsole.conf
chmod go-rxw /opt/bacula/oracle/bconsole.conf

Running Parallel Jobs

In order to run Backup or Restore using multiple channels, you need to ensure that all required resources in Bacula are properly configured using Maximum Concurrent Jobs directive to allow concurrent jobs.

Director: Director (ex: 100)
Director: Client (ex: 10)
Director: Job (ex: 10)
Director: Storage (ex: 10)
Storage: Storage (ex: 100)
Storage: Device (ex: 10 or 10 devices grouped in a Virtual Changer)
Client: FileDaemon (ex: 10)

To allow concurrent Backup and Restore jobs using the same Director Storage resource, the configuration must use a Virtual Changer disk device. See Disk Backup whitepaper about this specific configuration.

libobk Installation

Once packages are installed, the libobk.so file will be present in /opt/bacula/lib directory.

# ls -l /opt/bacula/lib/libobk*
-r--r--r-- 1 root root 95654 2013-03-02 01:00 libobk.so

# ln -s /opt/bacula/lib/libobk.so $ORACLE_HOME/lib/
# ls -l $ORACLE_HOME/lib/libobk.so
lrwxrwxrwx 1 root root 34 2013-03-22 01:01 libobk.so -> /opt/bacula/lib/libobk.so

You basically need to add a link in $ORACLE_HOME/lib/ to libobk.so in /opt/bacula/lib/.

It can be done using the install-sbt-libobk.sh script available under /opt/bacula/scripts

# /opt/bacula/scripts/install-sbt-libobk.sh install

Once done, we advise you to shutdown all oracle instances on this client. Sometimes Oracle will take a shared library (such as ours) and place it into its shared memory area. Then each time RMAN runs it will use the library it has in shared memory, not the one on disk. So even though this upgrade will replace the file on disk, Oracle may ignore the newly upgraded library.

When the library is installed and configured, it’s time to test if the linking has been done properly.

As oracle unix user, run RMAN and allocate a tape channel.

RMAN> allocate channel for maintenance type 'SBT_TAPE';

using target database control file instead of recovery catalog
allocated channel: ORA_MAINT_SBT_TAPE_1
channel ORA_MAINT_SBT_TAPE_1: SID=42 device type=SBT_TAPE
channel ORA_MAINT_SBT_TAPE_1: Bacula Enterprise Oracle SBT Plugin 1.0.0.2

Storage Consideration

Oracle imposes to the Media Manager, Bacula Enterprise, to not multiplex data streams from two concurrent API sessions onto the same sequential device. It means that if you are using tape based storage for your Oracle backup, you must use different tape devices for each concurrent backup jobs. This restriction doesn’t apply to disk based storage. This limitation implies specially longer restore time.

Bacula SBT Configuration

The libobk can be configured with $BACULA_CONFIGDIR/sbt.conf (available in 16.0.6), or /opt/bacula/oracle/sbt.conf or the /opt/bacula/etc/sbt.conf file, or using the RMAN SEND command. The keywords presented Table OptionsSBT are accepted.

SBT libobk Configuration
Option	Comment	Example
client	Bacula Client name	client=oracle-fd
restoreclient	Bacula Client name used for restore	restoreclient=oracle-fd
job	Bacula Backup Job name	job=SBT-Backup
bconsole	Bconsole command with arguments	bconsole=”/tmp/bconsole -n”
restorejob	Bacula Restore Job name. If multiple restore jobs are defined in your configuration and this option is not used, the SBT plugin will choose automatically the first restore Job defined.	restorejob=RestoreFiles
waitjobcompletion	Wait for Job completion at the end of the SBT session. The default is to finish the SBT session as soon as possible. Note that this option can be used only when starting the Backup from RMAN.	waitjobcompletion
update	Type of update (local/catalog). When the filename is present in the local catalog, the Plugin will reply directly to RMAN without contacting Bacula Director. Use update=force to force Bacula Director check.	update=force
jobopt	Extra Job options	jobopt=”spooldata=no”
backupdir	Directory for the local catalog	backupdir= /opt/bacula/oracle
ctrlfile	Base path of control files	ctrlfile=/tmp/oracle
ctrltimeout	Timeout when connecting with Bacula	ctrltimeout=300
retry	Number of retry when connecting to Bacula	retry=30
localdir	Local data file directory where the SBT plugin will check first before calling a Bacula restore.	localdir=/tmp/@ORACLE/sbt
level	Bacula Job level	level=full
catalog	Bacula Catalog name.	catalog=”MyCatalog2”
trace	Path to the trace file.	trace=/tmp/log.txt
debug	Debug level.	debug=50
comment	Job comment.	comment=”Initiated by me”

The minimal configuration file will require the client, job and bconsole options to be set. Note that if the configuration item contains spaces (such as bconsole), you need to use double quotes to enclose it.

# cat /opt/bacula/etc/sbt.conf
client=oracle-fd
job=OracleBackup
bconsole="/opt/bacula/oracle/bconsole -n -c /opt/bacula/oracle/bconsole.conf"

It is possible to overwrite these settings using the RMAN SEND command.

RUN {
 ALLOCATE CHANNEL c1 DEVICE TYPE sbt;

 SEND 'job=OtherBackup jobopt="spooldata=yes storage=File1"';
 SEND 'retry=10';
}

The SEND command is limited to 512 bytes, so this is possible to use multiple SEND commands to set all options. In general, this is a good idea to avoid long path names when using the RMAN interface.

In the following example, we allocate multiple channels and we can send different parameters to each of them:

RUN {
 ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
 ALLOCATE CHANNEL c2 DEVICE TYPE sbt;
 SEND CHANNEL c1 'job=OtherBackup jobopt="spooldata=yes storage=File1"';
 SEND CHANNEL c2 'retry=10';
}

Some other RMAN driver providers are using the environment variables to pass parameters to the RMAN driver (ENV=(waitjobcompletion)). Bacula Enterprise Oracle RMAN driver uses only the SEND command.

FileSet Configuration

The Oracle RMAN plugin (oracle-sbt) is accepting parameters in the Job FileSet described in Table OptionsPluginSBT.

Oracle SBT Plugin Options
Option	Default	Comment	Example
unix_user	oracle	Unix user to use for Oracle commands	unix_user=rob
ctrlfile	/opt/bacula/oracle	Path to the control file shared between the Plugin and RMAN.	ctrlfile=/tmp/base

Testing sbt.conf Configuration

To test the Bacula Enterprise Oracle SBT Plugin configuration, the following command can be used as root user:

oracle# /opt/bacula/scripts/install-sbt-libobk.sh test
1000 OK: oracle11-dir Version: 6.4.1 (24 May 2013)
INFO: Connection to the Director OK
INFO: Connection from the Director to the Client OK
INFO: Plugin installed correctly
INFO: Job found on the Director
INFO: FileSet configured on the Director

If a connection error is detected, a message will be displayed. It is useless to run any RMAN backup until the connection is properly configured.

Internal Bacula SBT Catalog

The Bacula Enterprise libobk will use a local catalog to store information about all files. These information may be outdated, so you can use the update=force in the sbt.conf file or in the SEND command, to force lookup Bacula catalog.

RUN {
 ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
 ALLOCATE CHANNEL c2 DEVICE TYPE disk;
 SEND 'update=force client=oracle-fd';

 CROSSCHECK  ARCHIVELOG ALL;
 DELETE EXPIRED ARCHIVELOG ALL;
 CROSSCHECK BACKUP;
 DELETE NOPROMPT FORCE OBSOLETE;
}

The catalog is stored by default in /opt/bacula/oracle/bacula-sbt.cat and can be a part of the regular system backup.

RMAN Backup Retention

When using RMAN SBT plugin of the Bacula Enterprise, the backup retention defined in RMAN should match the Bacula volume or job retention. When RMAN will send commands to delete backup files, Bacula will not try to purge or delete anything.

Backup Examples

The following example will start 3 Bacula backup jobs in parallel and RMAN will send data into them using some kind of round robbin. If RMAN is not able to contact Bacula for one or more channel, RMAN will automatically send the data to the available channel. It means that if your Storage or the Director is busy (limited by the number of devices or the Maximum Concurrent Jobs setting), RMAN will manage the situation automatically.

RUN {
 ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
 ALLOCATE CHANNEL c2 DEVICE TYPE sbt;
 ALLOCATE CHANNEL c3 DEVICE TYPE sbt;
 BACKUP INCREMENTAL LEVEL 0 DATABASE  plus archivelog;
}

In this example, RMAN will use 3 Bacula backup jobs to backup 3 datafiles.

RUN {
 ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
 ALLOCATE CHANNEL c2 DEVICE TYPE sbt;
 ALLOCATE CHANNEL c3 DEVICE TYPE sbt;
 BACKUP DATAFILE 1,2,3;
}

RMAN Mode Configuration

This part of the user’s guide will describe how to configure RMAN to work properly with the non-SBT part of the Bacula Enterprise Oracle Plugin.

The current version of the Bacula Enterprise Oracle Plugin currently supports only databases running with ARCHIVELOG mode enabled.

ARCHIVELOG Oracle Configuration

In order to use the RMAN backup mode, the database must be in ARCHIVELOG mode. To verify how your database is configured you can use the following SQL command.

oracle$ sqlplus / as sysdba

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
-----------
ARCHIVELOG

To activate the archive mode of your database, you can use the ALTER DATABASE ARCHIVELOG command on a non-open state as SYSDBA.

Stop the database with SHUTDOWN
Backup the database
Edit your init<SID>.ora file to configure the archive log destination
Start your database without opening it with STARTUP MOUNT
Change the archive mode with ALTER DATABASE ARCHIVELOG; and open it with ALTER DATABASE OPEN;
Stop the database with SHUTDOWN IMMEDIATE
Backup the database again, because changing the ARCHIVELOG will update control files and will make old backups unusable.

Bacula Enterprise Oracle Plugin will create RMAN backup set into a sub directory of the archive log destination defined in the init<SID>.ora file.

Optimize Incremental Backup

RMAN’s change tracking feature for incremental backups improves incremental backup performance by recording changed blocks in each data file in a change tracking file. If change tracking is enabled, RMAN uses the change tracking file to identify changed blocks for incremental backup, thus avoiding the need to scan every block in the data file.

After enabling change tracking, the first Full backup still has to scan the entire data file, as the change tracking file does not yet reflect the status of the blocks. Any subsequent incremental backups that use this Full backup as parent will take advantage of the change tracking file.

The following SQL command, run as sysdba, permits activating the change tracking feature and use the file “/path/to/file” as destination of the activity log. (Note that the file must be in a valid directory where the Oracle user can write)

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE  '/path/to/file';
SQL> ALTER DATABASE OPEN;

RMAN Backup Retention

When using RMAN mode of the Bacula Enterprise Oracle Plugin, each Bacula job will execute RMAN to generate a backup set. We advise you to configure RMAN in order to delete old files after some time period. Although, this can be done just after the end of the backup, we advise you to keep data on disk a bit longer to avoid any gaps in your point-in-time recovery capability. The following command will configure the Oracle retention period of 7 days, which should be sufficient providing you do some sort of backup at least once every 7 days.

RMAN>  CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

See the Oracle RMAN manual for more information http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmconfb.htm#i1019318

Oracle Plugin Configuration for RMAN

With the RMAN point-in-time recovery (PITR) option, the Bacula Oracle plugin requires Accurate mode information to correctly handle incremental and differential backups, thus you must enable the Accurate option in your Job resource. Note that when combined with the plugin, the Accurate option is used to ensure that all new files are saved by Bacula, but will not mark old files as deleted from previous backups as they most likely will still be useful.

Job {
 Name = "Oracle-RMAN"
 Client = laptop1-fd
 FileSet = FS_oracle
 Accurate = yes
 ...
}

FileSet {
 Name = FS_oracle
 Include {
   Options {
     Signature = MD5
   }
   Plugin = "oracle: mode=rman"
 }
}

In the RMAN mode, the Oracle plugin also accepts additional options on the Plugin command line listed in the table below:

Oracle Plugin Options in RMAN Mode
Option	Comment	Default	Example
mode	Needed to enable RMAN PITR backup	dump	mode=rman
oracle_user	Oracle Unix super user	oracle	oracle_user=oracle10
sid	Oracle SID		sid=XE
ORACLE_SID	Oracle SID		ORACLE_SID=XE
ORACLE_HOME	Oracle HOME		ORACLE_HOME =/opt/oracle/..
verbose	Display RMAN output in the Job	0	verbose=1
sbt	Use SBT in RMAN script , available since version 6.4.3		sbt
ctrlfile	Base path of control files when using SBT	ctrlfile=/tmp/oracle

Schedule Consideration for RMAN

If you wish to be able to restore to any point in time between the last Incremental and the next Full or Differential backup you must arrange your backups carefully so as not to leave a gap. One way is to schedule an Incremental backup immediately before your next Full or Differential.

Another way is to configure the RMAN Retention Policy to include previous archivelogs. For example:

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
or
RMAN> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 2;

Either of above options, the Full backup will contain previous archivelog files required to restore to a point between the last Incremental and the Full backup. To restore to a point between both jobs, you need to restore files from both groups of backup. Note, if you have implemented the above policy, there is no need to do an Incremental backup immediately before your Full since the Full will find the old logs and save them.

Customize RMAN Scripts

The Bacula Enterprise Oracle Plugin allows you to customize the RMAN backup script by creating special files in /opt/bacula/etc:

oracle_before_full_backup.rman
oracle_before_incr_backup.rman
oracle_before_diff_backup.rman

You may want to add special actions to these files such as:

% cat /opt/bacula/etc/oracle_before_full_backup.rman
BACKUP ARCHIVELOG FROM TIME 'SYSDATE-2';

In this example, the Bacula Enterprise Oracle Plugin will include a backup of all Archivelogs generated for the last two days. It will allow you to cover the gap between the last Incremental backup and the current Full backup as described in 2.5.5. Note that if you configure the Retention Policy the Full backup will automatically include Archivelogs generated between the two backups that have not been backed up.

If you want to exclude tablespaces from the backup, you can use the following RMAN script.

% cat /opt/bacula/etc/oracle_before_full_backup.rman
CONFIGURE EXCLUDE FOR TABLESPACE cwmlite;
CONFIGURE EXCLUDE FOR TABLESPACE example;

In this example, the RMAN backup will exclude the two tablespaces “cwmlite” and “example”. Note that this settting is saved in RMAN configuration and will stay accross RMAN sessions.

Tip

Use CONFIGURE EXCLUDE FOR TABLESPACE cwmlite CLEAR; to remove this exception.

Important, you can’t change the configuration of the disk device or it will reset previous configuration made by Bacula and break the job.

Dump Configuration

Warning

Oracle 11 and later deprecate dump mode!

With the Dump option, Bacula cannot perform Incremental or Differential backups, but the procedure to backup and restore is very simple, and this method is suitable for small or medium databases that don’t need Point-In-Time recovery capabilities. Also, please be aware that Oracle database 11g and newer no longer support dump for backup purposes.

Job {
 Name = "Oracle-dump"
 Client = laptop1-fd
 FileSet = FS_oracle_dump
 ...
}

FileSet {
 Name = FS_oracle_dump
 Include {
   Options {
     Signature = MD5
     Compression = GZIP
   }
   Plugin = oracle
 }
}

In this example, the plugin will detect and backup all databases on your server. Instances will be detected using information in /etc/oratab. You can also specify ORACLE_HOME and ORACLE_SID in Plugin command line.

FileSet {
 Name = FS_oracle
 Include {
   Options {
     Signature = MD5
     Compression = GZIP
   }
   Plugin = "oracle: schema=bacula"
   Plugin = "oracle: schema=master"
 }
}

In this example, the plugin will backup databases bacula and master.

In the Dump mode, the Oracle plugin also accepts additional options on the Plugin command line listed in table 2.4.

Oracle Plugin Options in Dump Mode
Option	Default	Comment	Example
mode	dump	Configure the Plugin backup method	mode=dump
dump_opt	CONSISTENT=Y GRANTS=y	This string will be passed to the `exp` command	dump_opt=””
unix_user	oracle	Unix user to use for Oracle commands	unix_user=rob
oracle_user	/ as sysdba	Oracle user to use for Oracle commands	oracle_user=”scott/tiger”
use_sudo		Use sudo instead to run Oracle commands (when not root)	use_sudo
compress	Y	Use exp compression. Y/N	compress=N
schema		Will backup schema matching this string	schema=PROD*
instance		Will backup instances (SID) matching this string	instance=PROD*
sid		Instance (SID) to backup	sid=PROD
ORACLE_HOME		ORACLE_HOME to use with sid	ORACLE_HOME=/ora

FileSet {
 Name = FS_oracle_dump
 Include {
   Options {
     Signature = MD5
   }
   Plugin = "oracle: unix_user=rob dump_opt=\"TABLES=temp\""
 }
}

In this example, the Oracle plugin will use Unix account “rob” to perform a dump backup of table named “temp”. The Oracle Plugin expects the “rob” account to be a member of the dba Unix group in order to directly access Oracle using “/ as sydba”.

Testing Database Access

You can use the estimate command to verify that the Oracle plugin is correctly configured.

* estimate listing job=oracle-test
Connecting to Client 127.0.0.1-fd at 127.0.0.1:8102
-rw-r--r--  1 oracle dba   1949 2012-06-06 21:55:20  /@ORACLE/XE/users.sql
-rw-r--r--  1 oracle dba   5240 2012-06-06 21:55:22  /@ORACLE/XE/FLOWS/user.sql
-rw-r-----  1 oracle dba     -1 2012-06-06 21:55:22  /@ORACLE/XE/FLOWS/data.dmp
drwxr-x---  2 oracle dba   4096 2012-06-06 21:55:22  /@ORACLE/XE/FLOWS
-rw-r--r--  1 oracle dba   1028 2012-06-06 21:55:23  /@ORACLE/XE/HR/user.sql
-rw-r-----  1 oracle dba     -1 2012-06-06 21:55:23  /@ORACLE/XE/HR/data.dmp
drwxr-x---  2 oracle dba   4096 2012-06-06 21:55:23  /@ORACLE/XE/HR
-rw-r--r--  1 oracle dba    360 2012-06-06 21:55:23  /@ORACLE/XE/OUTLN/user.sql
-rw-r-----  1 oracle dba     -1 2012-06-06 21:55:23  /@ORACLE/XE/OUTLN/data.dmp
drwxr-x---  2 oracle dba   4096 2012-06-06 21:55:23  /@ORACLE/XE/OUTLN
-rw-r--r--  1 oracle dba   2941 2012-06-06 21:55:24  /@ORACLE/XE/SYS/user.sql
-rw-r-----  1 oracle dba     -1 2012-06-06 21:55:24  /@ORACLE/XE/SYS/data.dmp
drwxr-x---  2 oracle dba   4096 2012-06-06 21:55:24  /@ORACLE/XE/SYS
-rw-r--r--  1 oracle dba   2857 2012-06-06 21:55:24  /@ORACLE/XE/SYSTEM/user.sql
-rw-r-----  1 oracle dba     -1 2012-06-06 21:55:24  /@ORACLE/XE/SYSTEM/data.dmp
drwxr-x---  2 oracle dba   4096 2012-06-06 21:55:24  /@ORACLE/XE/SYSTEM
-rw-r--r--  1 oracle dba    233 2012-06-06 21:55:25  /@ORACLE/XE/TSMSYS/user.sql
-rw-r-----  1 oracle dba     -1 2012-06-06 21:55:25  /@ORACLE/XE/TSMSYS/data.dmp
drwxr-x---  2 oracle dba   4096 2012-06-06 21:55:25  /@ORACLE/XE/TSMSYS
2000 OK estimate files=25 bytes=36,643

Notice that all the files backed up by the Bacula Oracle plugin are under a pseudo-directory named /@ORACLE/.

In order to use the sudo wrapper, you need to comment out:

Defaults requiretty

by putting a pound sign (#) in front of it in the /etc/sudoers file.

Estimate Information

The estimate command will display all discovered information by the Oracle plugin. Note that with the dump mode, Bacula can’t compute dump size for databases and will display -1 instead. On RMAN PITR mode, Bacula will not use RMAN to generate backup set, so it will display only system files and the flash recovery area.

Backup Files in RMAN Mode

In RMAN mode, Bacula Enterprise Oracle Plugin will not be cataloged under the pseudo-directory named /@ORACLE/ but will be under their original location. For example:

/etc/oratab
/ora10/flash_recovery_area/<Jobname>/<SID>/last_control_file
/ora10/flash_recovery_area/<Jobname>/<SID>/restore_query_file.txt
/ora10/product/10.2.0/server/network/admin/tnsnames.ora
/ora10/product/10.2.0/server/network/admin/listener.ora
/ora10/product/10.2.0/server/dbs/orapw<SID>

The Plugin will also generate some extra files in order to help you in case of a disaster situation, such as datafiles.txt or logfiles.txt as shown in Table 2.5.

Files Generated During RMAN Backup
File	Comment
dbid.txt	DBID of the current instance
tnsname.ora	Backup of ORACLE_HOME/network/admin/tnsname.ora
control<SID>.txt	Last control file generated after the backup
init<SID>.ora	Parameter file for the current instance
last_control_file	File that contains the name of the last control file backup
datafiles.txt	List of all data files
logfiles.txt	List of all log files
tempfiles.txt	List of all temp files
restore_query_file.txt	Special file needed for automatic restore
orpw<SID>	Password file for this instance
c-0000-YYYYMMDD-00	Control file
9tncsu2b_1_1	Backup Set

RMAN Backup Level

In Bacula Enterprise terminology, jobs may have the following Level

Full A backup that includes all files, it corresponds to the INCREMENTAL LEVEL 0 RMAN level. This level will allow RMAN to do subsequent incremental backups.
Incremental A backup that includes all files changed since the last Full, Differential, or Incremental backup started. It corresponds to the INCREMENTAL LEVEL 1 or INCREMENTAL FROM SCN RMAN level. Depending on the current state of the RMAN catalog, Bacula will choose one level or the other, depending on the SCN sequence.
Differential A backup that includes all files changed since the last Full save started. It corresponds to the INCREMENTAL LEVEL 1 CUMULATIVE RMAN level.

Example of Job Output

The Bacula Enterprise Oracle Plugin will display in the Job output the full result of the RMAN command executed.

dir: Start Backup JobId 7, Job=Oracle.2012-06-11_20.00.07_10
dir: Using Device "FileStorage"
sd: Recycled volume "TestVolume001" on device "FileStorage" (/storage), all previous data lost.
fd: Calling RMAN for orcl
fd: connected to target database: ORCL (DBID=1229390655)
fd: using target database control file instead of recovery catalog
fd: RMAN> RUN {
fd: 2>  CROSSCHECK  ARCHIVELOG ALL;
fd: 3>  DELETE EXPIRED ARCHIVELOG ALL;
fd: 4>  CROSSCHECK BACKUP;
fd: 5>  DELETE NOPROMPT FORCE OBSOLETE;
fd: 6>  CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK
        TO '/app/oracle/flash_area/Oracle.2012-06-11_20.00.07_10/orcl/%F';
fd: 7>  CONFIGURE CONTROLFILE AUTOBACKUP ON;
fd: 8>  CONFIGURE BACKUP OPTIMIZATION OFF;
fd: 9>  CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2G
        FORMAT '/app/oracle/flash_area/Oracle.2012-06-11_20.00.07_10/orcl/%U';
fd: 10> # @/tmp/oracle_before_full_backup.rman
fd: 11>  BACKUP INCREMENTAL LEVEL 0 DATABASE  PLUS ARCHIVELOG;
fd: 12>  SQL "ALTER DATABASE BACKUP CONTROLFILE TO TRACE
         AS ''/app/oracle/flash_area/Oracle.2012-06-11_20.00.07_10/orcl/controlorcl.txt'' REUSE";
fd: 13>  SQL "CREATE pfile=''/app/oracle/flash_area/Oracle.2012-06-11_20.00.07_10/orcl/initorcl.ora''
         FROM spfile";
fd: 14> }
fd: 15>
fd: allocated channel: ORA_DISK_1
fd: channel ORA_DISK_1: SID=33 device type=DISK
fd: validation succeeded for archived log
fd: Crosschecked 28 objects
fd: released channel: ORA_DISK_1
fd: allocated channel: ORA_DISK_1
fd: channel ORA_DISK_1: SID=33 device type=DISK
fd: specification does not match any archived log in the repository
fd: using channel ORA_DISK_1
fd: crosschecked backup piece: found to be 'AVAILABLE'
fd: backup piece handle=/app/oracle/flash_area/Oracle2.2012-06-08_15.33.06_28/orcl/1pnd0fqi_1_1 RECID=96 STAMP=785..
...
fd: Crosschecked 3 objects
fd: RMAN retention policy will be applied to the command
fd: RMAN retention policy is set to redundancy 1
fd: using channel ORA_DISK_1
fd: no obsolete backups found
fd: CONFIGURE BACKUP OPTIMIZATION OFF;
fd: released channel: ORA_DISK_1
fd: Starting backup at 2012-06-11_11:00:22
fd: current log archived
fd: allocated channel: ORA_DISK_1
fd: channel ORA_DISK_1: SID=33 device type=DISK
fd: channel ORA_DISK_1: starting archived log backup set
fd: channel ORA_DISK_1: specifying archived log(s) in backup set
fd: input archived log thread=1 sequence=556 RECID=6 STAMP=785300484
...
fd: channel ORA_DISK_1: starting piece 1 at 2012-06-11_11:00:23
fd: channel ORA_DISK_1: finished piece 1 at 2012-06-11_11:00:24
fd: piece handle=/app/oracle/flash_area/Oracle.2012-06-11_20.00.07_10/orcl/1rnd8si7_1_1 tag=TAG20120611T110023
fd: channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
fd: Finished backup at 2012-06-11_11:00:24
fd: Starting backup at 2012-06-11_11:00:24
fd: using channel ORA_DISK_1
fd: channel ORA_DISK_1: starting incremental level 0 datafile backup set
fd: channel ORA_DISK_1: specifying datafile(s) in backup set
fd: input datafile file number=00002 name=/app/oracle/oradata/orcl/sysaux01.dbf
...
fd: channel ORA_DISK_1: starting piece 1 at 2012-06-11_11:00:24
fd: channel ORA_DISK_1: finished piece 1 at 2012-06-11_11:01:19
fd: piece handle=/app/oracle/flash_area/Oracle.2012-06-11_20.00.07_10/orcl/1snd8si8_1_1 tag=TAG20120611T110024
fd: channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
fd: Finished backup at 2012-06-11_11:01:19
fd: Starting backup at 2012-06-11_11:01:19
fd: current log archived
fd: using channel ORA_DISK_1
fd: channel ORA_DISK_1: starting archived log backup set
fd: channel ORA_DISK_1: specifying archived log(s) in backup set
fd: input archived log thread=1 sequence=585 RECID=35 STAMP=785674879
fd: channel ORA_DISK_1: starting piece 1 at 2012-06-11_11:01:19
fd: channel ORA_DISK_1: finished piece 1 at 2012-06-11_11:01:20
fd: piece handle=/app/oracle/flash_area/Oracle.2012-06-11_20.00.07_10/orcl/1tnd8sjv_1_1 tag=TAG20120611T110119
fd: channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
fd: Finished backup at 2012-06-11_11:01:20
fd: Starting Control File and SPFILE Autobackup at 2012-06-11_11:01:20
fd: piece handle=/app/oracle/flash_area/Oracle.2012-06-11_20.00.07_10/orcl/c-1229390655-20120611-00
fd: Finished Control File and SPFILE Autobackup at 2012-06-11_11:01:21
fd: sql statement: ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS
    ''/app/oracle/flash_area/Oracle.2012-06-11_20.00.07_10/orcl/controlorcl.txt'' REUSE
fd: sql statement: CREATE pfile=''/app/oracle/flash_area/Oracle.2012-06-11_20.00.07_10/orcl/initorcl.ora''
    FROM spfile
fd: Recovery Manager complete.
sd: Job write elapsed time = 00:02:29, Transfer rate = 5.577 M Bytes/second
sd: Sending spooled attrs to the Director. Despooling 7,972 bytes ...
dir: Bacula dir 6.0.2 (01May12):
  Build OS:               i686-pc-linux-gnu redhat
  JobId:                  7
  Job:                    Oracle.2012-06-11_20.00.07_10
  Backup Level:           Full (upgraded from Incremental)
  Client:                 "127.0.0.2-fd" 6.0.2 (01May12) i686-pc-linux-gnu,redhat,
  FileSet:                "OracleSQLRMAN" 2012-06-11 14:26:50
  Pool:                   "Default" (From Job resource)
  Catalog:                "MyCatalog" (From Client resource)
  Storage:                "File" (From Job resource)
  Scheduled time:         11-juin-2012 20:00:07
  Start time:             11-juin-2012 20:00:09
  End time:               11-juin-2012 20:02:41
  Elapsed time:           2 mins 32 secs
  Priority:               10
  FD Files Written:       25
  SD Files Written:       25
  FD Bytes Written:       831,041,382 (831.0 MB)
  SD Bytes Written:       831,045,562 (831.0 MB)
  Rate:                   5467.4 KB/s
  Software Compression:   60.4 %
  VSS:                    no
  Encryption:             no
  Accurate:               yes
  Volume name(s):         TestVolume001
  Volume Session Id:      1
  Volume Session Time:    1339432273
  Last Volume Bytes:      831,895,725 (831.8 MB)
  Non-fatal FD errors:    0
  SD Errors:              0
  FD termination status:  OK
  SD termination status:  OK
  Termination:            Backup OK

Backup Information in Dump Mode

The Oracle plugin will generate the following files for a MAIN instance having a single database “test”.

/@ORACLE/MAIN/TEST/user.sql
/@ORACLE/MAIN/TEST/tables.sql
/@ORACLE/MAIN/TEST/data.dmp

/@ORACLE/MAIN/users.sql
/@ORACLE/MAIN/tnsnames.ora
/@ORACLE/MAIN/listener.ora
/@ORACLE/MAIN/orapwMAIN
/@ORACLE/MAIN/datafiles.txt
/@ORACLE/MAIN/logfiles.txt
/@ORACLE/MAIN/init.ora

/@ORACLE/oratab

Files Generated During Dump Backup
File	Context	Comment
users.sql	global	List of all users, their password and specific options
datafiles.txt	global	List of all data files, for information only
tempfiles.txt	global	List of all temp files, for information only
logfiles.txt	global	List of all log files, for information only
tnsnames.ora	global	Content of the tnsnames.ora
listener.ora	global	Content of the listener configuration file
orapw<SID>	global	Password file of the current instance
init.ora	global	Oracle configuration file
user.sql	schema	Schema definition with all information about GRANT, PASSWORD, …
data.dmp	schema	Database data in exp format, contains everything needed to restore
tables.sql	schema	Tables and indexes definition in SQL format

Oracle RAC Configuration

Since the RAC determines which node will provide the data, the plugin must be installed on each node. It also needs to be configured with the same password in its bacula-fd.conf. On the director at least one client needs to be added pointing to the cluster ip address and again, the same password set in the bacula-fd.conf of every node. The parameter update=force will be used to avoid the local catalog becoming outdated.

Restore Scenarios

Before starting a restore or recovery procedure, we strongly advise you to run a backup of you database.

Restoring using RMAN SBT Interface

Like when doing a Backup job, to restore objects with RMAN, the connection between RMAN and Bacula should be functional. If resouces are available, everything will be managed by RMAN automatically.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

RUN {
 ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
 ALLOCATE CHANNEL c2 DEVICE TYPE sbt;
 SET UNTIL TIME "to_date('2013-05-31_10:20:00','YYYY-MM-DD_HH24:MI:SS')";
 RESTORE DATABASE;
 RECOVER DATABASE;
}
ALTER DATABASE OPEN RESETLOGS;

In this example, RMAN will restore the database at a certain point in time defined by the UNTIL command. More information can be found on Oracle RMAN documentation. http://docs.oracle.com/cd/B28359_01/server.111/b28294/rman.htm#i1024051

If you restore RMAN files into a local directory and the localdir option is defined in sbt.conf, the RMAN plugin will look the localdir before starting a Bacula restore session. If the requested file is present, the RMAN Plugin will use it directly.

Restoring using RMAN with bs_oracle_restore.pl

Once you have restored the contents of the RMAN backup to your system (Fig 2.2) with the bconsole restore command or with BAT/BWeb, the Bacula Enterprise Oracle Plugin allows you to automate some RMAN operations through a wrapper called bs_oracle_restore.pl. This script is menu driven and allows you to:

Restore the original database to a certain Point-In-Time
Clone your database whether or not it is still available (currently in beta testing)

RMAN file selection when restoring using BWeb

Once you have restored the contents of the backup to a given point in time, you should run the bs_oracle_restore.pl script with restore_query_file.txt file as argument.

Note that you only need to restore files that are not on your system, RMAN will use files that are still in the flash recovery area to perform the restore.

In the next example, you will find the file named
restore_query_file.txt in the directory where you restored the
files with Bacula. If your backup was in
/u01/flash/Test.2012-06-06_12-00-00, and you restored it using
where=/tmp, the restore_query_file.txt should be in
/tmp/u01/flash/Test.2012-06-06_12-00-00/restore_query_file.txt

# /opt/bacula/scripts/bs_oracle_restore.pl /path/to/restore_query_file.txt
Bacula Enterprise Oracle Restore Tool 0.9

Do you want to:
 1- restore the original database

The Bacula Enterprise Oracle restore script can be called with the –testing option to have access to restore procedures that are currently in testing phase by Bacula Systems.

# /opt/bacula/scripts/bs_oracle_restore.pl --testing /path/to/restore_query_file.txt
Bacula Enterprise Oracle Restore Tool 0.9

Do you want to:
  1- restore the original database

The following restore modes are available but still being beta tested
  2- restore the database into a clone
  3- restore the database to a different location

Performing Database Point-In-Time Recovery

RMAN can perform recovery of the whole database to a specified past time, SCN, or log sequence number. This type of recovery is sometimes called incomplete recovery because it does not completely use all of the available redo information.

The restore wrapper bs_oracle_restore.pl will detect from files restored with Bacula Enterprise parameters that you can use during the restore.

Do you want to:
         1- restore to a certain point-in-time
         2- restore to a certain scn

Choose restore mode (1-2): 1

Getting the range of recoverable backups
Please input the time to which you want to restore
between 2012-06-05_15:17:16 and 2012-06-05_15:36:09

(YYYY-MM-DD_HH24:MI:SS): 2012-06-05_15:35:00

INFO: Mounting database in mount state
The database is in open state,
do you really want to shutdown the database now (y/N): y
INFO: Call RMAN to restore the database
Opening database

BE CAREFUL, we are about to open the database in RESETLOGS mode.

Do you want to continue ? (no will exit) (y/N): y
Opening database resetlogs

The bs_oracle_restore.pl will scan the backup directory, then detect the right Incarnation in order to restore files as expected.

At the end of the restore process, the database should be in “OPEN” state. The bs_oracle_restore.pl will perform all the step necessary to recover the database. If you are familiar with RMAN, you may want to do these steps manually.

Using bs_oracle_restore.pl Script Without Restoring First

In some case, if RMAN backup sets are still present on disk, you may want to skip the Bacula restore and run directly the bs_oracle_restore.pl script. For that, just use the -D option and point to the flash back recovery area where are located files generated during the last backup.

# /opt/bacula/scripts/bs_oracle_restore.pl -D /path/to/flash/job/SID

Restoring Directly with RMAN

Once you restored your files with Bacula, you need to scan the backup directory to include files in the RMAN catalog.

RMAN>  CATALOG START WITH '/path/to/restore' NOPROMPT;

Then, you should have all backups registered and you can list them with:

RMAN>  LIST BACKUP SUMMARY;

List of Backups
===============
Key   TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
----- -- -- - ----------- --------------- ------- ------- ---------- ---
458   B  0  A DISK        12-JUN-12       1       1       YES        TAG20120612
459   B  A  A DISK        13-JUN-12       1       1       YES        TAG20120613
461   B  A  A DISK        14-JUN-12       1       1       YES        TAG20120614
462   B  1  A DISK        15-JUN-12       1       1       YES        TAG20120615

...

Note that the LIST BACKUP can display information about specific objects such as:

Archivelogs
Datafiles
Controlfile
…

To start the restore process, the database should not be in open state.

RMAN> shutdown immediate;
RMAN> startup mount;

Then, you can set a UNTIL clause and start your recovery

RMAN> RUN {
2> RESTORE DATAFILE 1;
3> RECOVER DATAFILE 1;
3> }

Starting restore at 15-JUN-12
using channel ORA_DISK_1
...

RMAN is a very powerful tool with many options, for more information, see the RMAN documentation available on http://docs.oracle.com

Sometime, RMAN may ask to restore files and finaly find out that some of these files are not required to perform the restore. In this case, the communication link with Bacula is stopped by RMAN without specific notification. In this situation, the RMAN job output is correct, but the Bacula restore job report has an error such as:

2017-03-20 13:28:12 oracle-fd JobId 1: Error: restore.c:1388 Write error
   at byte=0 block=0 write_len=65536 lerror=0
   on /@ORACLE/sbt/c-3461191666-20170321-02: ERR=Success

If the RMAN job report is OK, then the Bacula error can be ignored.

Restoring using Dump

Restoring Users

To restore users/schema to your Oracle instance, you just select the users.sql file located in /@ORACLE/<SID>/users.sql

Then, using where=/ or where= the plugin will load this SQL file to your database. If some roles already exist, errors will be printed in the Job log. It is also possible to restore the users.sql file to a local directory, edit the file and load it with sqlplus to restore any selected part of the file.

% sqlplus / as sysdba @users.sql

Restoring a Single Database

To restore a single schema with the Bacula Enterprise Oracle plugin, you must select the schema directory during the restore command, the selection should contain the data file (data.dmp) and the schema creation script (user.sql).

When the database directory is selected, you can use the where parameter to restore the schema to a new schema, with a different name. In order to create a new schema name, you must set where to a single word that contains only A..Z, 0-9, and _, Bacula will then create the specified schema and restore data into it.

* restore where=BACULAOLD

We advise you to always use schema names in capital letters, Bacula Enterprise Oracle Plugin will recreate the new schema using exactly the same name that you provided in the where= parameter. If you mix upper and lowercase characters in the name, it can lead to a situation where you will need to enclose the schema name with quotes to access it.

Once restored, you may need to reset the password of the schema that you just created using the same parameters as the original schema. To do so, use:

SQL> ALTER USER BACULAOLD IDENTIFIED BY APASSWORD;

If you set the replace parameter to never, Bacula will check the schema list, and will abort the Job if the schema currently restored already exists.

Using replace=always is not recommended, because it can overwrite existing files.

If the where parameter is a directory (containing /), Bacula will restore all files into this directory. Doing so, you will be able to use :term”IMP directly and restore only triggers, tables, indexes, etc…

Restoring a Single Table

To restore a single object such as a table from your dump, you must first restore the dump file to a local directory, then use the :term”IMP tool to import the needed object. See the Oracle :term”IMP documentation for more information.

Restoring Dump Files to a Directory

To restore SQL dumps to a directory, you can set the where parameter to any valid directory.

* restore where=/tmp

The Bacula restore process will create the following directories when restoring the schema SYS in the Oracle SID XE, and will restore selected files into it.

/tmp/@ORACLE/MAIN/XE

Restoring the Entire Database

To restore the all databases and the database configuration, just all files located in /@ORACLE/<service>, use replace=always and where=/.

Note

Oracle documentation:

Oracle plugin

Overview

Scope

Convention Used In This Guide

Presentation

Using Oracle Plugin

Choosing Between Dump and RMAN

Installation

Plugin Configuration

RMAN SBT Configuration

RMAN Mode Configuration

Dump Configuration

Estimate Information

Backup Files in RMAN Mode

Backup Information in Dump Mode

Oracle RAC Configuration

Restore Scenarios

Overview 

Presentation 

Using Oracle Plugin 