Chapter 11. Backup and Recovery

There are a thousand excuses for failure but never a good reason. - Mark Twain

In this chapter we will discuss the basics of Oracle Database XE backup and recovery. This chapter introduces Oracle's backup and recovery tool, Recovery Manager (RMAN). We will also discuss a few backup and recovery scenarios. The following are the topics covered in this chapter:

• A brief introduction to backup and recovery

• Recovery Manager

• Connecting to Oracle Database XE using RMAN

• The ARCHIVELOG mode

• Placing a database in the ARCHIVELOG mode

• Backing up a database (the NOARCHIVELOG mode)

• Simulating a database failure (the NOARCHIVELOG mode)

• Restoring the NOARCHIVELOG database

• Configuring the RMAN environment

• Backing up the ARCHIVELOG database

• Simulating a database failure (the ARCHIVELOG mode)

• Restoring ARCHIVELOG database

The full range of backup and recovery techniques is out of the scope of this book. Refer to Oracle documentation for more details.

Introduction to backup and recovery

In...

Introduction to backup and recovery

In information technology, a backup or the process of backing up is making copies of data, which may be used to restore the original after a data loss event (refer to the Backup definition at Wikipedia). Recovering of database is a process a reconstructing the database after data loss.

Recovery Manager

Recovery Manager (RMAN) is Oracle's command-line utility for backing up and recovering an Oracle database. RMAN is fully integrated with Oracle database and is Oracle's recommended tool for backing up and recovering Oracle databases. RMAN is installed automatically with the database.

Connecting to Oracle Database XE using RMAN

Start the RMAN executable (RMAN.exe) in the command prompt of your operating system. A RMAN prompt will appear on your screen. Now you can connect to your database using the SYSDBA privilege account as follows:

-- connect to the database
RMAN> connect target /
connected to target database: XE (DBID=2655045848)
RMAN> exit

Alternatively, you can connect to the database when you start the RMAN client session as follows:

-- start RMAN client and connect to the target database
C:\oraclexe\app\oracle\product\11.2.0\server\bin>rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Wed Apr 4 14:38:53 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: XE (DBID=2655045848)
RMAN>

The ARCHIVELOG mode

Databases can be run in one of two modes — the ARCHIVELOG mode or the NOARCHIVELOG mode. In NOARCHVIELOG mode, the redo log files are reused by the Oracle database engine without being copied to an offline location. In ARCHIVELOG mode, Oracle copies the filled online redo log files to one or more offline locations before they are reused. These redo log files, which are saved offline, are called archived redo log files. The ARCH process is responsible for archiving when automatic archiving is enabled. You use the archived redo log files to recover a database and update a standby database.

The ARCHIVELOG mode is very important for mission-critical production databases. Databases can be backed up in the open mode when running in the ARCHIVELOG mode. Also, the ARCHIVELOG mode provides point-in-time recovery. It is generally not necessary for test and development databases.

Placing a database in the ARCHIVELOG mode

To place a database in the ARCHIVELOG mode, perform the following...

Backing up a database (the NOARCHIVELOG mode)

You can manually take a backup using the BACKUP command in RMAN or use the Oracle Database XE-supplied script. In this section we will use the Oracle Database XE-supplied script to perform a full database backup. Remember, RMAN by default creates backups to disk.

Backing up a database in the NOARCHIVELOG mode requires the database to be in a MOUNT state. This backup is called a consistent backup. When a consistent backup is restored, there is no need to perform any recovery.

Start the backup by running the Oracle Database XE-supplied backup script, located under Startup | Oracle Database 11g Express Edition | Backup Database on a Windows machine, and by executing $ORACLE_HOME/config/scripts/backup.sh on a Linux environment. The backup script does the following:

• Shuts down the database

• Starts the database in MOUNT mode

• Performs backup

• Opens the database for read/write operations

The following screenshot shows RMAN backup in progress on a Windows...

Simulating a database failure (the NOARCHIVELOG mode)

Now that we have a valid backup in place, let us simulate a database failure. Let us use the hr_test table created earlier in this book for our test. We have five records in the hr_test table, as shown in the following screenshot:

For this test, we will perform the following steps to simulate a database failure:

1. 1. Take a full database backup.

2. 2. Insert a record in the hr_test table. (Any changes to the database after the backup will be lost once we restore the database. So, we will lose this record in the recovery process.)

3. 3. Insert the following line of code into hr_test:

   VALUES (6, 'test record', sysdate, 100);
   Shutdown database
   

4. 4. Rename datafiles folder— ORADATA/XE to ORADATA/XE-BACKUP.

5. 5. Create a new empty folder— XE.

6. 6. Start the database now. (Oracle instance starts up in NOMOUNT mode and fails to mount the database, as Oracle is not able to find the control file while mounting the database.)

Restoring the NOARCHIVELOG database

Restoring a backup is a process of bringing the database back to a state before crash. As our database is in the NOARCHIVELOG mode, all changes made after the backup will be lost. We will be able to restore the database to the last backup.

We can restore the database either by using the Oracle Database XE-supplied restore script or by manually entering the restore commands in RMAN command prompt. In this section we will use the Oracle Database XE-supplied script to restore the database.

Start the restore by running the Oracle Database XE-supplied restore script, located under Startup | Oracle Database 11g Express Edition | Restore Database on the Window environment, and by executing $ORACLE_HOME/config/scripts/restore.sh on Linux. The restore script does the following:

• Restores spfile and controlfile from autobackup

• Restarts the database in MOUNT mode

• Restores the database

• Opens the database with the RESETLOGS option

The following screenshot shows the RMAN...

Configuring the RMAN environment

We can use the SHOW ALL command to display the current values of RMAN-configured settings for our target database. The following are a few of the values we can configure:

• Database retention policy

• Default device type for backup

• Control file automatic backup

• Enable/disable database encryption

Let us configure automatic backup of controlfile as follows:

This setting will enable the database to take an autobackup of controlfile whenever a database backup occurs or the database structure metadata (add/drop of datafiles/tablespaces) in controlfile changes.

For more information on other parameters, refer to Oracle Database Backup and Recovery User's Guide.

Backing up the ARCHIVELOG database

Let us first place the database in the ARCHIVELOG mode before backing up the database. We have already gone through the procedure of placing the database in the ARCHIVELOG mode earlier in this chapter.

Switch a few log files after opening the database using ALTER SYSTEM SWITCH LOGFILE and look for new archived redo log files in the C:\oraclexe\app\oracle\fast_recovery_area\XE\ARCHIVELOG\ folder.

Now our database is ready for the backup. Let us now take a manual backup instead of using the Oracle Database XE-supplied backup script. Log in to the RMAN session, connect to the target database, and run the backup command.

The following is the simple backup command that we will use to back up our database:

-- RMAN back command to backup database and archive logs
RMAN> backup database plus archivelog;

Alternatively, you can specify the backup location using the FORMAT clause. %U generates unique filenames for the backup pieces as follows:

RMAN> backup database...

Simulating a database failure (the ARCHIVELOG mode)

Now it's time to start playing with our database. We have recovered our database (NOARCHIVELOG) from a simulated database failure earlier in this chapter. We will repeat a similar approach with our database in the ARCHIVELOG mode.

For this test we will be performing the following steps to simulate a database failure:

1. 1. Take a full database backup.

2. 2. Insert a record in the hr_test table:

   SQL>INSERT into hr_test VALUES (6, 'test record', sysdate, 100);
   SQL> COMMIT;
   

3. 3. Switch the archive logs:

   SQL> ALTER SYSTEM SWITCH LOGFILE;
   

4. 4. Back up the archive logs:

   RMAN> backup archivelog all;
   

5. 5. Shut down the database.

6. 6. Rename the datafiles folder— ORADATA/XE to ORADATA/XE-ARCH-BACKUP.

7. 7. Create a new empty folder— XE.

8. 8. Start the database now. (Oracle instance starts up in NOMOUNT mode and fails to mount the database, as Oracle is not able to find controlfile while mounting the database.)

Restoring the ARCHIVELOG database

The steps involved in recovering an ARCHIVELOG mode database differ from that of the NOARCHIVELOG mode database. The steps involved are as follows:

1. 1. Start the database instance in the NOMOUNT mode.

2. 2. Restore the controlfile.

3. 3. Mount the database.

4. 4. Restore the database.

5. 5. Recover the database.

6. 6. Open the database.

7. 7. Query the hr_test table to confirm the change.

Start the database instance in the NOMOUNT mode using either SQL*Plus or the RMAN client. The next step in the process is to restore controlfile and mount the database. Restore controlfile from the autobackup, as shown in the following screenshot:

RMAN searches for a backup of controlfile in the default location, and once located, RMAN restores controlfile to its default location. Now mount the database using the ALTER DATABASE MOUNT command.

The database is now mounted and it is the time to restore the database. The control file has all the information of the backup and when we restore the database...

Summary

After completing this chapter, the reader should be able to perform basic Oracle database backups, configure the RMAN environment, change the ARCHIVELOG mode of a database, and recover the database from any media failures.

In this chapter we have used both manual RMAN commands and Oracle-supplied scripts for backup and recovery. At this point, we strongly suggest the reader of this book to refer to Oracle documentation for more details on database backup and recovery.

The next chapter will focus on the performance tuning of the database and database applications. It will cover the best practices in designing efficient database applications and more.

References

• Oracle Database Backup and Recovery User's Guide (http://docs.oracle.com/cd/E11882_01/backup.112/e10642/toc.htm)