Home > Backup and Recovery Blog > How to Backup Oracle Database with Bacula? Oracle Backup and Recovery Technical Overview.

How to Backup Oracle Database with Bacula? Oracle Backup and Recovery Technical Overview.

1 Star2 Stars3 Stars4 Stars5 Stars
(48 votes, average: 4.92 out of 5)
Loading...
Updated 20th October 2023, Rob Morrison

Oracle backup

Oracle Backup and Recovery

Typically, backup and recovery operations are regarded as one of the key pillars of data protection. This also applies equally to Oracle backup and restore of course, and in today’s world having at least one backup of your database is practically a necessity if you don’t want to eventually lose everything because of a simple error or  similar unfortunate accident.

A backup is a copy of your data that can be used to reconstruct your original database’s contents. In the context of Oracle there are two main backup types – physical backup and logical backup.

Physical backup is a copy of the physical files stored to use in case of an emergency. Physical backups typically include such file types as control files, data files, and archived redo logs.

Logical backups are a copy of logical data within the database, such as stored procedures and tables. These backups are often treated as a good supplement to the physical backups but are essentially useless without their counterpart.

Unless specified otherwise, the majority of backup and recovery documentation refers to a physical backup as the “backup” term. Creating a physical backup of a database is the act of “backing up” said database.

Data recovery and potential problems

While there are some possible exceptions, the majority of error cases that demand the data recovery process to be initiated can be attributed to one of the three error types: user errors, media failures, and app errors.

User errors represent the possibility when, due to either the manual mistake or the error in the app logic, the specific part of the database is either changed or deleted incorrectly. This single error type is considered to be the biggest cause of the majority of database downtimes all over the world.

There are also two different scopes of the potential disaster – localized and widespread. Localized damage is something relatively insignificant that can be fixed with an accurate repair process. Widespread damage, on the other hand, is the one that deletes massive amounts of data and needs to be countered as soon as possible to at least partially prevent a massive downtime for the entire database.

Media failure is another error type, this one is mostly represented by some sort of a physical problem with a storage device that causes problems with read or write requests. The appropriate measure in response to a media failure depends on the device type and the data type in question.

It’s important to mention that media errors are something that can happen to any storage device in the first place. Because of that same reason, it’s not uncommon for companies to develop some sort of a disaster recovery program in an attempt to mitigate the potentially disastrous effects of a media failure, among other things (since media failures are known for paralyzing the entire storage device at once).

App errors are the result of a software malfunction, typically represented by a corrupt data block. In the case of media corruption (also called physical corruption), it’s quite common for the entire block to not be recognized by the database at all, with the checksums that are not matching, the entire block containing zeroes, and so on. Depending on the extent of corruption, some lesser app errors can be more or less fixed using some variation of a block media recovery.

Oracle Backup Strategy

Within the capabilities of Oracle there’s two main backup strategies: RMAN and user-managed.

RMAN, or Recovery Manager, is a solution that is fully integrated within the Oracle database that can perform a variety of backup-related activities. RMAN can be accessed using either the Oracle Enterprise Manager or using the command line.

User-managed backups are basic backups that can be performed using a combination of host operating system commands and SQL*Plus recovery commands. All of the backup-related aspects must be defined manually in this case for every backup.

While there are two backup types that are supported, RMAN is the commonly preferred one, so the majority of the Oracle backup capabilities are focused within this backup type.

One major advantage of a RMAN backup is the ability to create backups while the database is open and/or mounted. The backup command itself is also relatively  easy; the prompt itself looks like this:

 

BACKUP DATABASE;

It is possible to add a number of additions or parameters to this command, for example it can look like this if we also want to include archived logs in the backup:

 

BACKUP DATABASE PLUS ARCHIVELOG;

Another option that is available with RMAN is the ability to create incremental backups. Incremental backup is a backup type that copies only specific files that were changed since the last backup of any type was performed (depending on the backup type, it can be either cumulative incremental backup or differential incremental backup).

When it comes to commands, the backup prompt is relatively similar (with possible additions in the form of various parameters):

 

BACKUP INCREMENTAL;

And there’s also the fact that shadow copies/snapshot files can also be created with the RMAN’s incremental backups. For example, the command to create an incremental backup in the fast recovery area can be initiated with the following command:

 

BACKUP INCREMENTAL LEVEL 1 … FROM SCN

As you can see, there’s plenty of capabilities that RMAN as a native Oracle backup solution can offer. At the same time, it does have its own limitations, and this is where third-party backup solutions come in.

Oracle Backup and Recovery with Bacula Enterprise

Essentially a copy of your important data that you’re keeping separately from the original to restore it in case of all kinds of data loss is called a data backup. Any business has some sort of data that they need to protect and don’t want to lose – that includes Oracle database users, as well. Data loss is the main reason why you need to have a backup to keep your Oracle database environment reliable and secure.

Most of the companies using Oracle prefer to have a separate person to manage their Oracle backup operations – this position is called a “backup administrator”. Typically, this person would be tasked with a number of duties, including:

  • Working out a proper backup schedule;
  • Being ready to solve problems that can arise in relation to the whole backup and recovery process;
  • Both thinking through and testing different situations with different kinds of possible hardware or software failures related to data loss;
  • Not directly related to the backup process but still possible tasks are data preservation and data transfer;
  • Being ready to recover in case of data loss of any scale. 

Speaking of Oracle database loss, the wide variety of possible reasons for loss of the database further illustrates that doing Oracle backups is a good thing in general! For example, some of the various causes of data loss could be:

  • Hardware crash;
  • Accidents with mishandling data;
  • Data corruption because of a virus;
  • Errors in the process of data migration from one device or system to another, etc.

There are many ways to backup Oracle databases. Clustered modes provide resilience to hardware issues, and highly available, cloud, and converged infrastructures offer new options for data assurance and freedom along with redundancy. Regardless of these options, Oracle backup remains critical in order to ensure that a small mistake, corruption, or hack doesn’t destroy the critical data residing on even the best infrastructure. Database servers remain a key component of most organizations, and often contain the most critical information for the continuity of operations. The guide that follows will show you how to execute Oracle backup and recovery using RMAN’s SBT functionality, which allows data to stream directly to Bacula Enterprise.

Possible Types of Oracle Backup and Recovery

There are two methods to backup Oracle database:

  • Bacula managed backup using Oracle dump mode. This is quick and easy to set up, but is limited in scope to smaller databases and does not support point-in-time recovery or incremental backups.
  • RMAN managed backup (Oracle SBT mode). Also known as Oracle Recovery Manager (RMAN), it is an original Oracle database server feature, meaning there’s no need to install it manually – it’s already included on the server side. This mode uses the excellent RMAN backup tool and APIs to allow Bacula to initiate more advanced backup modes that support PITR, incremental and differential database backups, and can take advantage of RMAN’s change tracking feature to improve incremental backup performance. It’s also the most user-friendly since RMAN uses one interface for all of the operating systems which makes it a lot less complicated.

Since RMAN-based backup type is the preferred solution, here are some of the most noticeable advantages of using it:

  • Binary compression. This type of compression mechanism is integrated in the Oracle database as a system and its main purpose is to reduce the overall size of an average backup.
  • Automated database duplication. A number of features implemented in Oracle allows for easy creation of your database’s copy using a large number of storage configurations.
  • Incremental backups. This type of backup is keeping and backing up only those blocks which were changed in some way since the last full backup. This backup approach requires far less storage space than the traditional one and adds more flexibility to the restoration process in case of some sort of disaster.
  • Encrypted backups. RMAN can easily encrypt your database using the integrated backup encryption capability. There’s one distinction between creating such backup on a disk and creating that same backup directly on a tape. For disk – the database in question much enable the Advanced Security Option. For tape – RMAN has to use Oracle SBT interface but there’s no need for Advanced Security Option to be enabled.
  • Block media recovery. If the amount of corrupt data is relatively small, you don’t really need to restore the entire backup to fix it – this feature is called block media recovery and can be used without taking the file itself offline, as well.

For this how-to, we’ll be setting up Oracle SBT backups.

Part 1: Configure the Oracle backup and recovery plugin in Bweb

Step 1. In Bweb, configure a new fileset for the job. In the ‘Plugin’ tab on the fileset, select Oracle SBT.

backup oracle database

Step 2. The Oracle backup and recovery plugin is configured primarily on the client side, so in most cases no further configuration is required on in Bweb. The new fileset can be committed.

oracle backup and recovery

Step 2a: Please note the different options to backup Oracle database available if the Oracle plugin (non-SBT) is chosen. The Oracle Plugin Whitepaper covers these alternate methods in depth.

oracle backup

Part 2: Configure the Oracle Database Backup Plugin on the Oracle server

As with other database plugins, the Bacula Enterprise File Daemon and the relevant database plugin component (Oracle SBT in this case) must first be installed on the database server. This places the necessary tools for Bacula backup onto the database server. Please review the Oracle whitepaper or contact Bacula Systems support if you need help with this step.

Step 1: Install the Bacula File Daemon and the Oracle backup plugin packages

Step 2: Install the sbt library into Oracle


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

Step 3: Restart oracle

Step 4: Copy bconsole and make sure Oracle can read it:


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*

Step 5: Edit /opt/bacula/etc/sbt.conf to indicate the job name, bconsole path and configuration, and client name:


client=oracle-fd
job=OracleBackup
bconsole=”/opt/bacula/oracle/bconsole -n -c /opt/bacula/oracle/bconsole.conf”

Fileset and Job examples:

Below are examples of the simple fileset configured in BWeb in Part 1, and a sample job that uses this fileset. For in depth Bweb tutorials, please see the Bacula Systems video documentation:


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

Job {
Name = SBT-Backup
FileSet = SBT-FileSet
Client = oracle-fd
Maximum Concurrent Jobs = 10
Messages = Standard
Pool = Default
Storage = File
}

Part 3: Test Plugin Connectivity and Run Oracle Backup:

Step 1: Test the plugin


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

Step 2: Manually run a backup through RMAN:


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;
}

A typical successful RMAN backup output looks like below, and is an indication that the SBT plugin is installed, correctly configured, and ready to run backups:


[oracle@centos07 ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 – Production on Thu Mar 23 11:02:22 2017

Copyright (c) 1982, 2015, Oracle and/or its affiliates. All rights reserved.

connected to target database: CENTOS07 (DBID=2213460080)

RMAN> run {

2> allocate channel c1 type sbt;

3> backup database plus archivelog;

4>}

using target database control file instead of recovery catalog

allocated channel: c1

channel c1: SID=44 device type=SBT_TAPE

channel c1: Bacula Enterprise Oracle SBT Plugin 1.0.0.7

Starting backup at 23-MAR-17

current log archived

channel c1: starting archived log backup set

channel c1: specifying archived log(s) in backup set

input archived log thread=1 sequence=23 RECID=1 STAMP=894837644
input archived log thread=1 sequence=24 RECID=2 STAMP=894882191
input archived log thread=1 sequence=25 RECID=3 STAMP=894882226
input archived log thread=1 sequence=26 RECID=4 STAMP=894924027
input archived log thread=1 sequence=27 RECID=5 STAMP=912953744
input archived log thread=1 sequence=28 RECID=6 STAMP=912955548
input archived log thread=1 sequence=29 RECID=7 STAMP=912955554
input archived log thread=1 sequence=30 RECID=8 STAMP=912955561
input archived log thread=1 sequence=31 RECID=9 STAMP=912955564
input archived log thread=1 sequence=32 RECID=10 STAMP=912964429
input archived log thread=1 sequence=33 RECID=11 STAMP=939375680
input archived log thread=1 sequence=34 RECID=12 STAMP=939380476
input archived log thread=1 sequence=35 RECID=13 STAMP=939380575

channel c1: starting piece 1 at 23-MAR-17

channel c1: finished piece 1 at 23-MAR-17

piece handle=07rvrjqv_1_1 tag=TAG20170323T110255 comment=API Version 2.0,MMS Version 1.0.0.7

channel c1: backup set complete, elapsed time: 00:00:25

Finished backup at 23-MAR-17

Starting backup at 23-MAR-17

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

input datafile file number=00001
name=/u01/app/oracle/oradata/CENTOS07/datafile/o1_mf_system_c2kyrs39_.dbf

input datafile file number=00003
name=/u01/app/oracle/oradata/CENTOS07/datafile/o1_mf_sysaux_c2kyqoql_.dbf

input datafile file number=00004
name=/u01/app/oracle/oradata/CENTOS07/datafile/o1_mf_undotbs1_c2kyt7s9_.dbf

input datafile file number=00006
name=/u01/app/oracle/oradata/CENTOS07/datafile/o1_mf_users_c2kyt6j4_.dbf

channel c1: starting piece 1 at 23-MAR-17

channel c1: finished piece 1 at 23-MAR-17

piece handle=08rvrjrp_1_1 tag=TAG20170323T110321 comment=API Version 2.0,MMS Version 1.0.0.7

channel c1: backup set complete, elapsed time: 00:00:56

channel c1: starting full datafile backup set

channel c1: specifying datafile(s) in backup set

including current control file in backup set

channel c1: starting piece 1 at 23-MAR-17

channel c1: finished piece 1 at 23-MAR-17

piece handle=09rvrjth_1_1 tag=TAG20170323T110321

comment=API Version 2.0,MMS Version 1.0.0.7

channel c1: backup set complete, elapsed time: 00:00:03

Finished backup at 23-MAR-17

Starting backup at 23-MAR-17

current log archived

channel c1: starting archived log backup set

channel c1: specifying archived log(s) in backup set

input archived log thread=1 sequence=36 RECID=14 STAMP=939380664

channel c1: starting piece 1 at 23-MAR-17 channel c1: finished piece 1 at 23-MAR-17

piece handle=0arvrjto_1_1 tag=TAG20170323T110424 comment=API Version 2.0,MMS Version 1.0.0.7

channel c1: backup set complete, elapsed time: 00:00:01

Finished backup at 23-MAR-17 released channel: c1

Oracle Database Recovery

While a backup on its own is incredibly important – it’s also worth mentioning that the  recovery functions are, of course, a vital part of the whole process in the Oracle database backup and recovery cycle. Just having a backup with no means to restore is of no use to anyone.

But first we have to cover some basics. As we said earlier, there are a large number of possible circumstances that could leave you needing to recover your database. For example, these could be a number of various hardware failures and firmware issues:

  • Block corruption;
  • Data loss;
  • User error;
  • Issues with upgrading;
  • Natural or unnatural disaster.

You’ll have to connect to the recovery catalog first if you want to either restore or recover a database using RMAN. The next step is to allocate channels to either tape or disk. The purpose of recovery catalog is to provide all kinds of information about database backup or backups. You can also configure a separate control file for similar purposes. The difference between restore and recover commands is that “restore” does exactly what you’d expect – restores database files, but “recover” is somewhat different since it applies all of the changes recorded in the archived data logs.

Oracle database backup and recovery as a process is able to provide several recovery options, including:

  • Specific Point Recovery

When you’re using Oracle, the recovery process is not so complicated. You can easily restore the entire database and after that use “recover” commands to choose the specific point to which you want your database to be recovered. There are also several other options included aside from the basic complete recovery if you’re using RMAN – you can bring the state of your database to a specific point in time or you can match its condition to specific archive log.

  • Tablespaces/Datafiles/Blocks Restoration

If your database includes a lot of tablespaces – you can choose this recovery mode to limit the database downtime only to tablespace users that were using now damaged files. In general, restoring tablespaces is a bit more complicated than the usual restore database function since you can’t recover specific tablespace after the restore. That’s why a backup is advised straight after restoring those tablespaces. One more specific when using RMAN is that you’ll be able to provide both block number and datafile number, thus making the recovery process easier.

  • Data Recovery Advisor

Oracle’s service also includes a feature called Data Recovery Advisor which you can bring up if you’re having problems with one or several of your database files. This advisor is able to provide you with both the repair script based on your database and can show you how much you can recover in your current situation, as well. You can also quite easily enable the provided script right after launching the advisor.

However, Oracle backup management is not just about keeping up your retention policy, it can also be used to keep track of which backups you can use for restore operations at a given moment. There are several approaches to this task:

The first approach is strictly for retrieving a list of available backups, and it can be done with little to no effort with a LIST command that RMAN has. This particular command retrieves a table of backup sets, with additional information about each of those, such as the creation date, the backup type, the specific parts of a database that were copied, and more.

The second approach to Oracle backup management is centered around getting rid of obsolete files – it is mostly controlled by only two parameters (RECOVERY WINDOW and REDUNDANCY, for number of days for a retention policy and number of backups, respectively), and consists of three possible options:

  • Expire. Controlled by REDUNDANCY and RECOVERY WINDOW,  usually a part of a backup script or backup job with an EXPIREDATE parameter.
  • Delete from catalog. Backup history gets deleted with a chance for recovery if it was accidental or if the data in question is needed.
  • Delete expired. A routine cleanup task that deletes obsolete backups and archive logs.

While the majority of Oracle backup operations deal with backups of the entire database, the option to restore specific objects is also on the table, so that any object within your system can be backed up or recovered separately from the rest at any given moment. As with the previous topic, there are several ways to approach the subject:

  • Oracle Data Pump. Data Pump is an Oracle utility that can provide both export and import of specific objects or tables. It is worth noting that the DDL (Data Definition Language) is also included, meaning that the entire structure of the table or procedure in question would be recreated with the object or table that is being restored. This can also be used to restore just the structure with zero data in it to be used as a template or a backup measure.
  • Using Table/Schema levels to copy specific objects. Singular tables can be recovered within Oracle using the CREATE command, it will include tablespaces with no logging – meaning that you’ll only be able to restore data, without any of the structure such as indexes, triggers, constants, etc.

Final Thoughts on Oracle Backup and Recovery

Because the Bacula Enterprise Oracle SBT plugin leverages RMAN for advanced backup features, some additional configuration must be done in Oracle.

Retention period:

When using RMAN SBT plugin, the backup retention defined in RMAN should match the Bacula volume or job retention.

Archive log:

In order to use the RMAN backup mode, the database must be in ARCHIVELOG mode.

Please consult Bacula’s Oracle Backup whitepaper and Bacula Systems Support if you need more information, assistance, or have questions after reading this Oracle backup and recovery guide.

About the author
Rob Morrison
Rob Morrison is the marketing director at Bacula Systems. He started his IT marketing career with Silicon Graphics in Switzerland, performing strongly in various marketing management roles for almost 10 years. In the next 10 years Rob also held various marketing management positions in JBoss, Red Hat and Pentaho ensuring market share growth for these well-known companies. He is a graduate of Plymouth University and holds an Honours Digital Media and Communications degree, and completed an Overseas Studies Program.
4 Comments
  1. Adam
    Adam

    I used to think that it’s hard to backup oracle database. But with Bacula plugin it’s easier that ever to do oracle backup and recovery. Also you can look through Bacula oracle backup whitepaper to learn more details.

  2. George
    George

    That’s a great step-by-step oracle backup and recovery tutorial with the definition of oracle backup types.
    If you want to backup oracle database you should check this post. Just follow the steps or contact support if necessary.

  3. Gary
    Gary

    Oracle backup is a surprisingly complicated process that might seem too sophisticated for some people. This article did a great job in describing all of the necessary steps with a lot of details.

  4. Kaylee
    Kaylee

    With an Oracle backup and recovery process this complicated – we’re lucky to have RMAN with its unified interface capability. It’s hard to imagine an Oracle backup process with several different interfaces that you need to get through every time.

Leave a comment

Your email address will not be published. Required fields are marked *