Oracle Databases Enterprise Bacula Plugin Quick Guide

This quick guide presents several techniques and strategies for Oracle Databases backup with Bacula Enterprise.

The plugin currently supports the following methods: Dumps Stream to Bacula, RMAN Integrated Point In Time Recovery (PITR) and RMAN-SBT Stream.

Installation

The Oracle plug-in is available as a Bacula Enterprise package for the vast majority of platforms supported by Oracle Database. (to get them, contact us). Example:

bacula-enterprise-oracle-10.0.0-1.rh7.i586.rpm

You need to install this plug-in on the client where your Oracle database resides, along with the Bacula Enterprise Client.

The plug-in assumes that your instances are listed in /etc/oratab and that the “oracle” user of the operating system is a member of the DBA group (usually “dba” or “oracle”).

Configuration

If your Oracle user of the operating system is not “oracle”, you may need to manually set the permissions in the following directory and make sure that your changes are still in effect after each update of the Bacula Enterprise for Oracle Plugin.

ls - ld /opt/bacula/oracle
drwxrwx - - - 13 dba raiz 4096 Mar 28 14:04 /opt/bacula/oracle
# or
chown -R oracle:dba /opt/bacula/oracle/

All plugin backup methods require Oracle DB to have the Flash Recovery Area (FRA) enabled, as follows: https://docs.oracle.com/database/121/ADMQS/GUID-D6A541BF-A878-4B45-AFAC-1E921554731E.htm

Example (sqlplus):

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/mnt/fra';

The directory must exist and have permissions for the oracle operating system user.

Make sure your /etc/oratab is properly configured, as it will provide information for the Bacula plugin. It is also important Oracle listener is running:

lsnrctl start
lsnrctl status

Dumps Stream Method

For this method, differential and incremental backups are only possible through the use of Bacula Enterprise Global Deduplication. It is more used by small and medium-sized bases that do not need PITR.

To use sudo for the dump command, you need to comment the following line (# at the beginning) of the /etc/sudoers file with the visudo command:

Defaults requiretty

The configuration of the plugin to backup all bases in this mode (instances will be detected using information in /etc/oratab), can be done graphically by bweb (Figure 1) or via text in Bacula’s FileSet Plugins Edition:

Figure 1. Bweb Plugin Configurator.

FileSet {
  Name = oracle_dump_set
  Include {
    Options {
      Signature = MD5
    }
  Plugin = oracle
  }
}

You can also specify the ORACLE_HOME and ORACLE_SID values if necessary. Ex .: Plugin=oracle ORACLE_HOME=/ora sid=PROD.

The following example backs up two specified databases, filtered by name:

FileSet {
  Name = oracle_dump_set
  Include {
    Options {
      Signature = MD5
    }
    Plugin = "oracle: schema=heitordb"
    Plugin = "oracle: schema=xptodb"
  }
}

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

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

These are all options supported by the plugin in Dump mode:

Option	Description	Default	Example
mode	Usa o modo dump do Plugin Bacula	dump	mode=rman
dump_opt	Opções passadas para o comando export (exp)	CONSISTENT=YES GRANTS=YES	dump_opt=””
unix_user	Usuário Oracle do sistema operacional	oracle	unix_user=heitor
oracle_user	Usuário do banco Oracle para os comandos	oracle	oracle_user=”hfaria/guru”
use_sudo	Usar sudo para executar os comandos Oracle (quando não root)		use_sudo
compress	Usa compressão do comando exp do Oracle (Y/N).	Y	compress=N
schema	Fará o backup dos schemas que casem com este nome ou coringas		schema=PROD*
instance	Fará o backup dos instances (SID) que casem com este nome ou coringas		instance=PROD*
sid	SID para backup		sid=PROD
ORACLE_HOME	ORACLE_HOME para usar com o SID		ORACLE_HOME=/ora

To test the backup, the estimate command from bconsole can be used, showing lines beginning with @ (eg: /@ORACLE/) that indicate content backed up by Bacula plugin:

* estimate listing job=oracle-test
Connecting to Client 127.0.0.1-fd at 127.0.0.1:9102
-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--r--  1 oracle dba    -1 2012-06-06 21:55:22  /@ORACLE/XE/FLOWS/user.sql
...
2000 OK estimate files=25 bytes=36,643

Note: the following Oracle reserved schemas are excluded from the default backup: ‘DBSNMP’, ‘CTXSYS’, ‘DIP’, ‘XDB’, ‘MDSYS’, ‘ANONYMOUS’, ‘XS $ NULL’, ‘HR’, ‘PM ‘,’ SH ‘,’ IX ‘,’ OE ‘.

Dump Restore

To restore a backed-up backup, select the user.sql file to restore only users or user.sql and data.sql to restore a database. The where in the source location (where=/) causes Bacula to attempt to restore data directly to the Oracle service, or specifying a new name causes a renamed database to be created, preferably in uppercase. Example:

* restore where=BACULAOLD

If you specify another directory in the where, the dumps will be restored to disk file from the client machine, by default.

As shown in Figure 2, data selection can be done directly by the Bweb Graphical Interface or by bconsole.

Figure 2. bweb Oracle Selection Dumps.

RMAN-PITR Method

In this method differential and incremental backups are supported, but they take up disk space on the Oracle banking service machine. If this is a problem, the RMAN-SBT method is more appropriate.

To use the RMAN backup mode, the database must be in the 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 enable the file mode of your database, you can use the ALTER DATABASE ARCHIVELOG command in an unopened state, such as SYSDBA. Example:

Shutdown databases with SHUTDOWN
Perform databases backup
In the sqlplus console: ALTER SYSTEM SET log_archive_dest_1='LOCATION=/mnt/archive';
- OR: edit the init[SID].ora file in order to specify the archive log destinations. E.g., LOG_ARCHIVE_DEST_1='LOCATION=/mnt/archive'
Start databases without opening them with the STARTUP MOUNT command
Change the archiving mode with ALTER DATABASE ARCHIVELOG; and open databases with ALTER DATABASE OPEN;
Stop the database with the SHUTDOWN IMMEDIATE command
Back up the database again because changing ARCHIVELOG will update the control files and make old backups unusable

The Bacula Enterprise Oracle Plugin will create the RMAN backup set in a subdirectory of the archive log destination defined in the archiveinit[SID].ora.

The RMAN change control feature for incremental backups improves incremental backup performance by registering changed blocks in a trace file. If change tracking is enabled, RMAN will use the change tracking to identify changed blocks for incremental backup, thus avoiding the need to scan all blocks of data files.

The following SQL command, run as sysdba, allows you to enable change tracking. and “/path/file” as the activity log destination (the file must be in a valid directory in which the Oracle user can write).

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

After you enable change tracking, the first full backup still needs to scan the entire data file because the change tracking file does not yet reflect the status of the blocks. Subsequent incremental will already have the benefit of the control file.

When using the RMAN mode of the Bacula Enterprise Oracle Plugin, each Bacula job will run RMAN to generate a backup set. We recommend setting up RMAN to delete old files after some period of time. Although this can be done shortly after the end of the backup, we advise you to keep the data on disk a bit longer to avoid gaps in your point-in-time recovery feature. The following command will configure the Oracle retention period of 7 days, which should be sufficient as long as you do some backup at least once every 7 days. Likewise, it is recommended to also retain a couple of copies of the archive log to facilitate local restoration and ensure all means of restoration at time points. In the shell:

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

The Job and FileSet configurations can be done through the Bweb graphical interface, or in the Bacula configuration files, via text. The Accurate Job option must be enabled, and the Plugin mode=rman mode set. Example:

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

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

The following options can be modified using the RMAN-PITR method:

Option	Description	Default	Example
mode	Usa o modo PITR do Plugin Bacula	dump	mode=rman
oracle_user	Usuário Oracle do sistema operacional	oracle	oracle_user=oracle10
sid	SID do Oracle		sid=XE
ORACLE_SID	SID do Oracle		ORACLE_SID=XE
ORACLE_HOME	Oracle Home		ORARCLE_HOME=/opt/oracle/…
verbose	Mostra saída do RMAN na log do job	0	verbose=1
sbt	Usar o SBT no script RMAN		sbt
ctrlfile	Diretório dos arquivos de controle quando usando SBT	ctrlfile=/tmp/oracle

The start and scheduling of Jobs from this backup in this mode can be done directly by Bacula, and RMAN scripts can be customized on the client machine and are in /opt/bacula/etc:

oracle_before_full_backup.rman
oracle_before_incr_backup.rman
oracle_before_diff_backup.rman

Example:

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

If you want to exclude tablespaces from 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;

For information about restoring using this method, go to the topic: RMAN-PITR and RMAN-SBT Restore.

RMAN-PITR Restore

Using Bacula bweb RMAN Broker

After restoring the contents of the RMAN backup to your system (Figure 3) with the bconsole restore command or BAT/BWeb, the Oracle Plugin allows you to automate some RMAN operations through a broker called bs_oracle_restore.pl. This script is menu driven and allows you to:

Restore the original database to a particular point-in-time
Clone your database, whether or not it is available

Figure 3. RMAN broker bweb for database restoration

After you restore the contents of the backup at a certain point in time, you must run the bs_oracle_restore.pl script with the file restore_query_file.txt as the argument.

Note that you only need to restore files that are not on your system, as RMAN can 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 using where=/tmp/bacula-restores, the file restore_query_file.txt should be in /tmp/bacula-restores/u01/flash/Test.2012 -06-06_12-00-00/restore_query_file.txt.

chown oracle -R /tmp/bacula-restores/
su - oracle
/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 with special restore procedures that are currently in the test phase by Bacula systems. If you have used any of these features, we would appreciate feedback.

# /opt/bacula/bin/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

RMAN can perform the recovery of the entire database for 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 available redo information.
The restore broker bs_oracle_restore.pl will detect the restored files with Bacula Enterprise parameters that you can use during 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

Bs_oracle_restore.pl will check the backup directory and detect the files to do the restore. At the end of the restore process, the database must be in the “OPEN” state. Bs_oracle_restore.pl will perform all necessary steps to recover the database.
If you are familiar with RMAN, you can perform these steps manually.

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

Restoring Directly with RMAN

After restoring your files with Bacula, you need to check the backup directory to include files in the RMAN catalog.

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

So, 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-16       1       1       YES        TAG20120612   
459     B  A  A DISK        13-JUN-16       1       1       YES        TAG20120613
461     B  A  A DISK        14-JUN-16       1       1       YES        TAG20120614
462     B  1  A DISK        15-JUN-16       1       1       YES        TAG20120615
...

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

Archivelogs
Datafiles
Controlfile
etc.

To start the restore process, the database must not be open.

RMAN> shutdown immediate;
RMAN> startup mount;

Then you can set the 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-SBT Stream Method

This method allows you to back up Oracle databases directly to Bacula Storage.

You also need to configure the archive mode, as seen in the previous topic (RMAN-PITR).

Client Side

Once the Bacula plugin and client are installed, the libobk.so file will be present in /opt/bacula/lib.

Run the following script to automatically add a link to $ORACLE_HOM / lib for libobk.so in /opt/bacula/lib:

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

Restart the Oracle instances. Let’s try this access later.

When performing a backup or restore of RMAN, it will need to contact Bacula Enterprise Director for information about files and volumes, or to perform backup and restore jobs. This communication requires Bacula bconsole installed on the machine hosting Oracle, the backup client. Usually it is already installed together with the client package. Copy the console settings to be used by the plugin. Example:

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

You can configure the /opt/bacula/oracle/bconsole.conf file on the client, just as it is running on the Bacula server, with unrestricted anonymous administrator access (just edit and fill in the Director console name, address, and password). If you want to offer greater security, you can limit bconsole access rights only to the commands required by the Oracle plugin, using a specific user to access Bacula per console and setting a password, for example:

Director {
   Name = nome_director-dir
   DIRport = 9101
   Address = endereço_rede
   Password = "sem_senha" # propositalmente. Usuários restritos usam a senha abaixo
}

Console {
   Name = oracle
   Password = "senha_console_oracle"
}

Also, configure the RMAN settings for the Bacula job in the client, creating an sbt.conf file, and giving the client and job names EXACTLY as you want to create in the bweb:

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

A lot of different options are supported in the sbt.conf file, such as: “restoreclient”, “level” etc. They can be used to perform restores to an alternative host and to change the respective backup Job level in Bacula. In order to set these values directly in the RMAN backup script, it is possible such as in the following example.

SEND 'level=Differential';

Refer to the Bacula Systems official documentation to a full list of options.

Director Side

Passing to the Director’s machine (eg bweb), we will create the console with the Bacula oracle user, if you have opted for restricted access, tie the Client, create a FileSet and Job.

Console access example in Director (can be created by bweb) – replace the Client and Job names with yours:

Console {
  Name = oracle
  Password = "pass"
  CommandACL = .bvfs_lsfiles, .bvfs_get_volumes, use, .bvfs_get_jobids, wait, .bvfs_restore, .bvfs_cleanup, restore, run, gui, .jobs, quit, show, status
  ClientACL = oracle-fd
  JobACL = BackupOracle, RestoreJob
  CatalogACL = *all*
  StorageACL = *all*
  FileSetACL = *all*
  PoolACL = *all*
  WhereACL = /
  DirectoryAcl = *all*
  UserIdAcl = *all*
}

Tie/create a Client entry with the address and password of the Bacula client of the Oracle machine, using the same client name defined in sbt.conf.

For the FileSet configuration, specify the use of the rman-sbt mode:

FileSet {
  Name = SBT-FileSet
  Include {
    Options {
  Signature = MD5
  }
  Plugin = oracle-sbt
  }
}

The options for the plugin’s FileSet call are these:

Option	Description	Default	Example
unix_user	Usuário Oracle do sistema operacional	oracle	unix_user=heitor
ctrlfile	Caminho para o arquivo de controle do Plugin e RMAN	/opt/bacula/oracle	ctrlfile=/tmp/base

Create a new Backup Job using FileSet and Clients created with the same Job name defined in the client’s sbt.conf file. Also configure MaximumConcurrentJobs=5 or more for simultaneous backup of multiple rman channels.

Test

To test the use of the libobk library, returning to the backup client machine, run RMAN with the oracle user of the system, and there should be a similar output:

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

To test the settings made in sbt.conf, run the command in the client shell:

/opt/bacula/scripts/install-sbt-libobk.sh test
1000 OK: director-dir Version : 10.0.0
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

To run a test backup job, use the following example that will initiate 2 Bacula tasks in parallel and RMAN will send the data to them using some kind of round robbin. If RMAN can not contact Bacula for one or more channels, RMAN will automatically send the data to the available channel. This means that if your storage or director is occupied by the number of devices or the Concurrent Job Maximum Bacula setting), RMAN will automatically manage the situation.

For more Oracle DB advanced versions (e.g. Enterprise) you can define multiple channels for multiplexing:

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

Full backup example:

RUN {
  ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
  ALLOCATE CHANNEL c2 DEVICE TYPE sbt;
  BACKUP DATABASE;
}

For the Standard version of Oracle it is only possible to start one backup or restore channel at a time. For example:

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

You can also use tags during backup to make restoration easier later:

RUN {
 ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
 BACKUP DATABASE tag BACULA filesperset 5 KEEP UNTIL TIME '(SYSDATE+130)'  LOGS;
 release channel c1;
}

The data goes directly into Bacula’s storage, and backup can be accompanied by bweb, bconsole, and so on interfaces.

Schedule

The start and scheduling of backup jobs in this mode can be done by ORM, dbms_scheduler or a RMUL script initiated by Bacula. Ref.: http://www.dba-oracle.com/t_rman_scheduling_backup.htm

An RMAN script called by a Bacula Job Admin can also be used. For sample scripts, please contact us.

RMAN Local Disk Backup Copy for Bacula-SBT

Instead of setting up a new backup, you can also simply copy backups to Disk already made by RMAN to the SBT driver. Command example:

RMAN> BACKUP DEVICE TYPE sbt COPY OF DATABASE;

More examples: https://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmbackp.htm#CEGBCHJA

RMAN-SBT Restore

To restore objects with RMAN, the connection between RMAN and Bacula must be functional. If resources are available, everything will be managed by RMAN automatically. You can list all backups with:

list backup summary;

You can list all backups with:

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 specific time defined by the UNTIL command. More information can be found in the Oracle RMAN documentation. http://docs.oracle.com/cd/B28359_01/server.111/b28294/rman.htm#i1024051

Example of restoration using tags:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
RUN{ 
 ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
 restore tablespace users from tag 'BACULA'; 
}
ALTER DATABASE OPEN RESETLOGS;

If you want to perform the restore of one or more dropped table spaces, you must also restore the controlfile:

startup nomount;
RUN {
 ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
 SET UNTIL TIME "to_date('2018-12-26_17:05:00','YYYY-MM-DD_HH24:MI:SS')";
 restore controlfile from autobackup;
}

In case of problems like “RMAN-06026: some targets not found – aborting restore” and “RMAN-06023: in the backup or copy of datafile x found to restore”, you can force an update of the RMAN catalog:

RUN {
 ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
 send "update=force";
}

Other examples of RMAN restore: https://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmrecov.htm#i1006746

If you restore RMAN files to a local directory and the localdir option is set to sbt.conf, the RMAN plug-in will search for the location before starting a Bacula.

References

Oracle Backup Using Bacula Enterprise Edition Plugin Whitepaper. http://baculaystems.com

Disponível em: Português (Portuguese (Brazil))EnglishEspañol (Spanish)