lamp2lapo

One of the primary reasons for choosing Oracle over MySQL is Oracle’s rich set of enterprise features. This article looks at the builtin backup features of Oracle provided by the Recovery Manager (RMAN).

Since we are migrating from MySQL to Oracle, let’s compare RMAN to MySQL’s backup offerings. The two main programs for backing up MySQL databases are mysqldump and mysqlhotcopy. The mysqldump program performs a logical backup and the mysqlhotcopy program performs a physical backup.

Oracle databases (even XE!) come with a robust backup and recover tool called RMAN. I could try to summarise RMAN myself but Oracle has done such a great job already that I’ve decided to just quote them.

A complete high availability and disaster recovery strategy requires dependable data backup, restore, and recovery procedures. Oracle Recovery Manager (RMAN), a command-line and Enterprise Manager-based tool, is the Oracle-preferred method for efficiently backing up and recovering your Oracle database. RMAN is designed to work intimately with the server, providing block-level corruption detection during backup and restore. RMAN optimizes performance and space consumption during backup with file multiplexing and backup set compression, and integrates with leading tape and storage media products via the supplied Media Management Library (MML) API.

RMAN takes care of all underlying database procedures before and after backup or restore, freeing dependency on OS and SQL*Plus scripts. It provides a common interface for backup tasks across different host operating systems, and offers features not available through user-managed methods, such as parallelization of backup/recovery data streams, backup files retention policy, and detailed history of all backups.

Put in simpler terms, Oracle comes with an enterprise-quality backup and recovery tool builtin. Once you have configured Oracle and RMAN to perform your backup plan then you simply let it run. Sounds easy, right? Like most things in Oracle there is the simple way to get started and then about 15,000 ways to customise and configure it. We’ll start with the simple way first.

Oracle XE ships with a script that does a full backup every time it is run. It also instructs RMAN to keep two copies so if you run it every day, you can recover back to two days ago. My experience with RMAN started because we wanted to change the default behaviour from two days of redundancy to seven and found out that it wasn’t as easy as changing a ‘2′ to a ‘7′.

If you want to do live database backups then you should turn on ARCHIVELOG mode. You will also need to increase the size of your flash recovery area from the default 10 Gigabytes because the archive logs could be quite large, depending on how much activity your database sees. More information can be found in the Oracle XE 2 day DBA online course provided by Oracle.
The script can be found at “$ORACLE_HOME/config/scripts/backup.sh”. The script performs some useful configuration checks and writes the ouput of the backup to a current and previous log file in the oracle user’s home directory. The specific part that interests us is the rman command:

rman target / >> $rman_backup << EOF
set echo on;
configure retention policy to redundancy 2;
configure controlfile autobackup format for device type disk clear;
configure controlfile autobackup on;
sql "create pfile=''$rman_spfile2init'' from spfile";
backup as backupset device type disk database;
configure controlfile autobackup off;
delete noprompt obsolete;
EOF

Let’s look at it line by line to understand what is happening.

1. rman target / >> $rman_backup << EOF - Launches rman, connects it to the root of all databases and writes it to a backup log file. The following lines are piped in as configuration.
2. set echo on; - Produce verbose output
3. configure retention policy to redundancy 2; - Only consider the two latest backups as current. All others are obsolete.
4. configure controlfile autobackup format for device type disk clear; - Reset the controlfile autobackup format for device type disk option to its default value. You can reset any option to its default using the “configure option… clear;” syntax.
5. configure controlfile autobackup on; - Automatically backup the server parameter and control files whenever a change is made. Enabling this essentially allows RMAN to start up after most failures and start to recover data.
6. sql “create pfile='’$rman_spfile2init'’ from spfile”; - Create an init.ora backup file from the running server’s system configuration. Oracle now maintains persistent settings in a server parameter file (spfile) so that “ALTER SYSTEM …” commands change the running instance and stay in effect between shutdowns and restarts. Before, you had to alter the system AND remember to add the new configuration to init.ora.
7. backup as backupset device type disk database; - Backs up the entire database as a backupset in RMAN.
8. configure controlfile autobackup off; - Turn off autobackup of the spfile. I’m not sure why this is done here.
9. delete noprompt obsolete; - Delete all the backups that RMAN considers to be obsolete. If you run this every day then you will only have two backupsets in your repository at any time.

Now you are probably thinking to yourself “It seems to me that he could have just changed a 2 to a 7 and been done with it.” I would agree with you except for the fact that this particular server doesn’t have enough disk space available to store seven full backups. Which is why I went looking for ways to configure RMAN to do incremental backups. It took me a long time to figure out but you get the benefit of my learning and it involves commenting out one line and adding two more like this:

/* backup as backupset device type disk database; */
configure retention policy to recovery window of 7 days;
recover copy of database with tag “whole_db_copy” until time ‘SYSDATE-7′;
backup incremental level 1 for recover of copy with tag “whole_db_copy” database;

Once again, a line by line approach seems appropriate.

1. /* backup as backupset device type disk database; */ - SQL comment to remove our old full-backup method
2. configure retention policy to recovery window of 7 days; - Ensure that we retain adequate backups to recover data from the last seven days.
3. recover copy of database with tag “whole_db_copy” until time ‘SYSDATE-7′; - Rolls forward our incremental backup image copy of the database to the database state of seven days ago. Read below for how this works in detail.
4. backup incremental level 1 for recover of copy with tag “whole_db_copy” database; - Make an incremental backup against our full backup file tagged whole_db_copy.

If you’re like me, your first thought is “where is the full backup actually created?” The process is neatly explained by Jim Czuprynski in his Backup and Recovery
Improvements article on databasejournal.com and quoted here with only the tag name changed to match our configuration.

Though this appears a bit counter-intuitive at first, here is an explanation of what happens during the initial run of this script:

• The RECOVER command actually has no effect, because it cannot find any incremental backups with a tag of whole_db_copy.
• However, the BACKUP command will create a new Incremental Level 0 backup that is labeled with a tag of whole_db_copy because no backups have been created yet with this tag.

And during the second run of this script:

• The RECOVER command still will have no effect, because it cannot find any Level 1 incremental backups with a tag of whole_db_copy.
• The BACKUP command will create its first Incremental Level 1 backup that is labeled with a tag of whole_db_copy.

But during the third and subsequent runs of this script:

• The RECOVER command finds the incremental level 1 image copy backups from the previous night’s run tagged as whole_db_copy, and applies them to the existing datafile image copies.
• The BACKUP command will create the next Incremental Level 1 backup that is labeled with a tag of whole_db_copy.

After the third run of this script, RMAN would then choose the following files during a media recovery scenario: the image copy of the database for tag whole_db_copy from the previous night, the most recent incremental level 1 backup, and all archived redo logs since the image copy was taken. This strategy offers a potentially quick and flexible recovery, since the datafile image copies will be relatively quick to restore, and the incremental level 1 backup plus all archived redo logs can be used to perform either a point-in-time or a complete recovery.

At this point, you might also want to read Oracle’s article on Backup and Recovery, especially if you’re only experience in backing up databases is with MySQL. Hopefully, my explanation of getting started with backups in Oracle XE for MySQL users has been helpful! Comments are always welcome.