Database Backup, Restore, and Recovery

Backing up Databases in Oracle

A data backup can be defined as any copy of our original information that we store so as to restore it should something happen that leads to data loss. Taking a backup is best practice for businesses or even personal computers that contain valuable information that cannot be lost; backup is key to providing secure and reliable database environments.

Check out this video on How to Become a Database Administrator:

Now, data loss can be caused due to various reasons, which are why taking backups becomes so important. Some of the reasons for data loss are:

• Accidentally misplacing or deleting data
• Virus attacks
• Hardware crashes
• Moving data from one system to another, and so on.

Now that we understood what a backup is and why do we need it, let’s see how Oracle Database, one of the biggest database management systems, helps in handling data backups.

Backup Solutions in Oracle

There are basically two ways to perform a data backup in Oracle:

• Oracle Recovery Manager:

Oracle provides a powerful utility to back up and restore databases, which is known as Oracle Recovery Manager (RMAN). We can use RMAN to back up databases as a complete backup or incremental backup. Since RMAN is a feature of the Oracle Database server, there is no need to separately install it.

• User-managed Backup and Recovery:

Other than RMAN, Oracle also supports user-managed backup and recovery, where users can implement the backup and recovery of databases using a mixture of host operating commands and SQL Plus commands, i.e., the users have to use different commands for different OS. In user-managed backup and recovery, a user will be scheduling how and when the backup has to be implemented.

When comparing both backup and recovery solutions, RMAN is most recommended since it offers one common interface for implementing backup across all different host operating systems, which makes it very user-friendly.

System Database Administrators can set up RMAN database backups, as well as schedule them, for multiple databases. In this section, we will learn how to take backups, how to recover files, databases, etc., and more in Oracle.

Let’s start off by understanding how Oracle strategizes all backups and recovery processes.

Backup Strategies

Oracle Database Configuration Assistant (DBCA) automates RMAN backup and maintenance plans using Oracle Enterprise Manager (OEM), an online set of tools used by Oracle Corporation for managing software and hardware products.

For RMAN backup of a database, we make copies and store them to disk and tape with predetermined retention policies. A backup created using RMAN is typically used as a test database before applying backup and recovery procedures on a real database, before saving RMAN backups as long-term storage solutions.

As seen in the following image, RMAN connects to the source database that needs backing up and then copies it over a network to an auxiliary instance that connects directly to RMAN, creating a duplicate database connected by RMAN.

Here is how the Recovery Manager works; however, before we can backup a database with RMAN we must first configure our RMAN server. Let’s now examine how RMAN can be configured.

RMAN Configuration for Backups

RMAN is installed with the Oracle software and placed in the ORACLE_HOME/bin/ directory. RMAN uses either a catalog to track and manage backups or the control file of the database for this purpose.

To configure RMAN, we have to create a user to be the catalog owner and grant this user a quota on the tablespace where the schema is going to live, as well as the RECOVERY_CATALOG_OWNER role.

• Open RMAN command line by typing the following command in the command prompt:

rman

• Next, create the catalog using the RMAN command line

RMAN> connect the target

Now, we will be connected to the target database, DBDEV1 (DBID=280973718), where DBDEV1 is the database that will have the catalog schema.

• If another database is to have the catalog, the connect catalog string will have the database as part of the string: rman/rmanpswdRMANCAT

RMAN> connect catalog rman/rmanpswd

• To create a recovery catalog, use the following command:

RMAN> create the catalog

Once we do that, the recovery catalog will be created.

• To register a database in the recovery catalog, we have to use the following:

RMAN> register database

This way, the database will be registered in the recovery catalog.

• Next, we will start the full resync of recovery catalog.

RMAN> RESYNC CATALOG;

The full resync is complete now.

After the configuration of RMAN, we can start backing up our databases in Oracle. We will first understand how to take RMAN backups using Oracle Enterprise Manager, and then we will understand how to do the same using commands.

Backing up Using Enterprise Manager

We can back up a database in a few steps using Oracle Enterprise Manager (OEM) without having to write RMAN scripts. OEM will create the RMAN scripts using OEM Cloud Control. This saves us a lot of time and is also cost-efficient since the whole backup approach is automated.

Let’s understand how we can use OEM to backup a database.

Step 1: From our database home page, we will go to Availability, then select Backup & Recovery, and then move on to Backup Settings

• The following page will appear. In the Device tab, we will start filling the fields as shown below:

• If we want our backups to be created on disk, we will fill out the Disk Settings section. We will retain the value of parallelism as 1 (the default value). Keeping the backup type as a Compressed Backup Set will help us make the best use of backup space. We can also use an image copy for this backup type. It will create an exact copy of all the datafiles, which is useful for fast recovery. And, then, we will click on Test Disk Backup to ensure whether the disk backup setup is appropriate.
• Similarly, if we want our backups to be created on tape, we can fill the Tape Settings section

Step 2: Next, we will move to the Backup Set tab as shown below:

• In this tab, we have to specify the size of the Maximum Backup Piece in MB, and then we have to set the Compression Algorithm fields. The release field can be filled with a specific version or can be left with the default value

Step 3: Now, we will move on to the Policy tab as shown in the following screenshot:

• Here, in the Policy tab, we will choose the automatic backup of Database Control File and Server Parameter File with every database backup

Note: It is strongly recommended to specify the automatic backup as Database Control File and Server Parameter File are very important files.

• Scrolling down in the Policy tab shows the retention policy, Archived Redo Log Deletion Policy, as in the below screenshot:

Note: Selecting to retain full backups for each data file as 1 (the default value) will mark the older backups as obsolete whenever a new backup is taken, i.e., only one backup will be retained at a time. Later, we can delete the obsolete backups using the following command:

DELETE NOPROMPT OBSOLETE DEVICE TYPE DISK;

• Finally, we will click on OK to save the backup settings in the control file of the database

Backup and Restore Commands

The basic commands for backing up and restoring Oracle databases should look familiar to SQL Server DBAs.

• Let’s start with the SQL Server command to back up a database:

backup database customer_db to
disk ='E:\mssql\bkups\customer_db_02022010.bak'
backup log customer_db to
disk='E:\mssql\bkups\customer_db_log_02022010.trn'

• In Oracle, we use a Recovery Manager (RMAN) command, like this:

RMAN> run {
allocate channel disk1 device type disk;
backup database plus archivelog;
}

This command will back up the database with the archive logs and write the backup file to the disk.

Backup Options

Some common backup types and how to run them in SQL Server and RMAN are mentioned in the below table:

OEM Backup Jobs

With OEM, we can configure RMAN backups and schedule them for later as jobs in the database. OEM will generate the RMAN script and display it for our review. This provides a good way to gain a better understanding of the backup options and RMAN commands.

Restoring and Recovering Databases

Till now, we were talking about how to take backups in Oracle. Now, let’s assume that we have already taken a backup of our database in Oracle, and later, due to some reason, we lost our original data.

In this scenario, even if we have a backup, it won’t do any good to us until we know how to recover and restore the backup. So, let’s understand how we can recover and restore databases in Oracle.

Before we dive into restoring and recovering databases, let’s first understand under what circumstances we might need to recover a database, and what are the different ways in which a database can fail.

Hardware failures/firmware issues:

• User errors
• Bad code
• Loss of a file, control file, redo log, or datafile
• Corrupt blocks
• Upgrade issues
• Bad changes
• Disasters

Oracle provides various options for recovery, such as rolling back a query or returning to a point before a change.

Restore and Recover Options

To use RMAN to restore or recover a database, we must first connect to the recovery catalog and then allocate channels to the tape or disk. The catalog has information about the database backup and backup set. A control file can be used for the same information.

The restore database command restores the database files, and the recover database command applies any of the changes that are in the archive logs.

• Recovering to a Specific Point

In SQL Server, we have the option to restore with recovery or with no recovery. With Oracle, we can just restore the database, and then use the recover database command with options to define at which point to recover.

Along with this, Oracle RMAN recover database command can bring the database to a point in time, to a change number, or to a specific archive log.

We can recover to a system change number (SCN). The current SCN can be seen here:

v$database view (select current_scn from v$database;)

We can also recover to an ‘until time’ or a sequence from archive logs. If a restore point, such as before_upgrade, has been set for the backup, we can recover the database to that point.

• Restoring Tablespaces, Datafiles, and Blocks

If there are more tablespaces in the database, there is a way to recover with downtime for only the applications or users in the damaged tablespace. To recover a tablespace to a point in time, before an error occurred or it was corrupted, an auxiliary database/files can be used. After restoring a tablespace, we should run a backup, because recovering the tablespace after the restore is not possible.

If a block of data is corrupt, DBVERIFY will throw an error and provide some details about the datafile number and block number. The system view v$database_block_corruption will confirm the block number. Using RMAN, we can supply the datafile number and block number to recover the blocks.

• Data Recovery Advisor

If there is an issue with one of the database files, we can use the LIST FAILURE command and the ADVISE FAILURE command to help figure out what to do. The advisor provides a repair script, and the recovery is possible without data loss. The script can be run after running the advisor, with REPAIR FAILURE. REPAIR FAILURE PREVIEW will show us the script first.

Managing Backups

Managing backups is not just about purging and maintaining the retention policy, but also about knowing which backups are available for restores. Oracle provides several ways to get information about backup sets.

• Viewing Backups

RMAN has a LIST command that will return the backup sets that are present in the catalog or control file. This would show the different backup pieces and details, including the checkpointed SCN, the date, full or incremental, and tablespaces that were backed up.

Data dictionary views and recovery catalog tables also provide views into the backup sets to help manage backups and to know which backups are available for restoring.

• Purging Obsolete Files

In Oracle, the parameters, REDUNDANCY and RECOVERY WINDOW, set the number of backups and the number of days for retention policies.

Backing Up and Restoring Objects

With Oracle, restoring the database is normally to restore the full system, but there are utilities available to pull out just the objects by schema, or even at the table level. This allows us to secure backups for these objects, copy them to another system, or perhaps to refresh a test environment with just the needed schema or tables.

• Copying Objects at the Table and Schema Level

A backup table can be defined (such as CREATE table TAB_BACKUP AS select * from TAB_PROD) with tablespaces, no logging (to avoid some of the logging in the redo logs), and with some of the other table options.

The table will not include any of the indexes, constraints, or triggers that might be on the ‘real’ table, but it will have the same datatypes and the same data. A WHERE clause can also be defined in the CREATE table AS statement to capture data.

• Using Data Pump

Oracle has the Data Pump utility that handles both exports and imports, as well as older EXP and IMP utilities. We can set up a Data Pump export job to recover just a table or another object, such as a view or stored procedure. The exports include Data Definition Language (DDL), which creates the structures of the tables, procedures, triggers, indexes, views, and other objects. Exports can also be done without data, to provide just the structures, which we can then copy to another schema or save as a backup.

Recycle Bin

The Oracle recycle bin works with the tables that have been dropped. For example, if we are refreshing a couple of tables and we realized that we dropped the wrong tables, we can retrieve those objects from the recycle bin. The recycle bin has been available since Oracle Database 10g and is on by default. Users have their own recycle bins.

The user_recyclebin and dba_recyclebin views show information about the contents of the recycle bin. The dba_recyclebin view has an owner column, which lists who owns an object.

What is Flashback in Oracle?

Flashback in Oracle is a set of tools that allow System Administrators and the users to view and even manipulate the past state of data without having to recover to a fixed point in time. Using the flashback command, we can pull a table out of the recycle bin.

• Restoring Tables from the Recycle Bin

To ‘undrop’ a table in the recycle bin, we flashback the table.

SQLPLUS> flashback table TAB_CUST to before drop;

The Flashback is complete; this way, we restore the table.

• Purging the Recycle Bin

The objects in the recycle bin can be cleared out with the purge command. Users can clear their own recycle bins using the below purge command:

SQLPLUS> purge recyclebin;

• Purging the DBA recycle bin clears out all users’ recycle bins:

SQLPLUS> conn / as sysdba
SQLPLUS> purge dba_recyclebin;

Flashback in Oracle is a set of tools that allow System Administrators and the users to view and even manipulate the past state of data without having to recover to a fixed point in time. Using the flashback command, we can pull a table out of the recycle bin.