One spot that is very important to any database environment is backup and recovery, but too many times Pebble IT sees businesses neglecting their backups.  A perfect example of this is when backups are created, but then they are never tested.  Then one year later when an important database needs to be restored the business realizes that something isn’t working with the backups, and a full recovery is not possible.

In this post Pebble IT will provide you with the necessary steps you can follow to start using RMAN backups.   We at Pebble IT can provide further information for businesses who would like to get into the specifics of backup and recovery using RMAN, but here I will focus on a few of the basics to get you going.  If you require more information about our database services, please use the contact us link above or below.

Did you know that some businesses are scared of using RMAN?  

In older versions, when Oracle introduced RMAN, it created many problems and some businesses simply refused to use it. Instead, they chose the online backup method of placing tablespaces in backup mode, copying the datafiles to a backup location and then ending the tablespace backup mode.

Since then, 10g RMAN has vastly improved, with more businesses starting to use it.  Oracle have also done an amazing job with new features implemented as part of 11g.  So if you are using 11g, you do not have any excuses for not using this great solution.  Some have mentioned that it is too complex; but did you know that you can perform a backup of your database with the simple command below:

backup database;

Often, businesses complicate matters by writing long and elaborate scripts to perform backups when in most environments it is not required.  Just implementing a basic solution might be a good starting point, as you can always refine it and make it more elaborate as you become more familiar with RMAN the options it can provide.  With a few easy steps you can have backups in place with ease.

When performing backups we tend to prefer using local disk based storage. The disk location where the database backups are being stored should then be included as part of the database server backup cycle.  This is a quick and easy way and no special database backup software agents is required.

There are a few reasons for using disk based backups, and one of the main ones is that we do not have to wait for tapes to be loaded or returned from secure storage when a restore or recovery is required.  You can just start the restore process with the latest backups being directly available on disk.

In the example below we are using Oracle 11g on Oracle Linux.

 Step 1:  Setting the Environment

One of the first things we do when setting up new backup is to update the environment variables to include “NLS_DATE_FORMAT”.  By setting this environment variable, the dates being displayed will be in a more readable format, including the time, not just the date.  We normally add this variable to Unix account’s .bash_profile.

For example, just add the following two lines to the oracle .bash_profile:

NLS_DATE_FORMAT=”DD/MM/YYYY HH24:MI:SS”

export NLS_DATE_FORMAT

To give you an idea what effect this parameter has, if we executed a “list backup” command to show known backups, we would see the following: Without NLS_DATE_FORMAT:

..

..

List of Datafiles in backup set 5

File LV Type Ckp SCN    Ckp Time  Name

—- — —- ———- ——— —-

4       Full 1202182    18-JUN-12 /u01/app/oracle/oradata/testdb/users01.dbf

..

..

With NLS_DATE_FORMAT set:

..

..

List of Datafiles in backup set 5

File LV Type Ckp SCN    Ckp Time            Name

—- — —- ———- ——————- —-

4       Full 1202182    18/06/2012 11:57:49 /u01/app/oracle/oradata/testdb/users01.dbf

..

..

As you can see above, the dates are showing the time as well when this parameter is set.  It’s also important to have a small function setting the environment and this is one variable that should always be set.

Step 2: Default RMAN Configuration

One of the hidden “gems” of RMAN is that you can update your default configuration to make use of specific values.  To see the default values you can execute the “show all” command when connected to your database.  Below is an example:

oracle@dbvlin101[/home/oracle]: rman

Recovery Manager: Release 11.2.0.3.0 – Production on Wed Jul 4 14:08:52 2012

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

RMAN> connect target /

connected to target database: TESTDB (DBID=2569094133)

RMAN> show all;

RMAN configuration parameters for database with db_unique_name TESTDB are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM ‘AES128′; # default

CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_testdb.f’; # default

Note: do not update these values without doing a full review and understanding the implications of altering these values.  One of the most important values you need to specify is to enable auto backup of the controlfile, which by default is set to OFF.  When you update this to ON, RMAN will automatically backup the controlfile at the end of every backup.  This is the first configuration that should be updated and can be easily done with:

RMAN> configure controlfile autobackup on;

Following the above, update the controlfile autobackup location.  If you are using local disk based backups, update the configuration to create the controlfile backup in the backup location which in this example is: /backups/rman/testdb.  To do this, execute the following command:

RMAN> configure controlfile autobackup format for device type disk to ‘/backups/rman/testdb/%F’;

As mentioned above, we suggest to make backups to disk.  To configure RMAN to make use of a specific location for disk based backups you can update the default channel configuration to include the disk location where the backups should be stored.  In this example we are using /backups/rman/testdb as the backup destination.  Update the default RMAN configuration to reflect this:

RMAN> configure channel device type disk format ‘/backups/rman/testdb/%U’;

The final configuration change that needs to be made is to ensure RMAN is making use of compressed backup sets by default.  Creating compressed backups is one of the best features of RMAN (since 10g).  This is extremely useful as you can now keep multiple backups on disk. This means that you have a larger recovery window.  With Oracle Standard Edition only the “BASIC” compression algorithm can be used, for more details on this and the compression algorithm please contact Pebble IT by clicking on the contact us like above or below.  Configuring the use of compressed backup sets when using disk based backups can be done with the following command:

RMAN> configure device type disk backup type to compressed backupset;

Once this is done, by default the disk-based backups will be compressed.  Now to show you the configuration following the above changes:

RMAN> show all;

RMAN configuration parameters for database with db_unique_name TESTDB are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/backups/rman/testdb/%F’;

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   ‘/backups/rman/testdb/%U’;

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM ‘AES128′; # default

CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_testdb.f’; # default

Step 3: Creating a backup

Now let’s assume you are following the above and have changed the default configuration as described.  With this in place, we can execute a one-line set of commands to backup the database.  Below is an example, including output.

RMAN> backup database plus archivelog;

Starting backup at 04/07/2012 16:37:59

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting compressed archived log backup set

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

input archived log thread=1 sequence=668 RECID=667 STAMP=787725660

input archived log thread=1 sequence=669 RECID=668 STAMP=787759671

input archived log thread=1 sequence=670 RECID=669 STAMP=787760426

input archived log thread=1 sequence=671 RECID=670 STAMP=787761651

input archived log thread=1 sequence=672 RECID=671 STAMP=787762876

input archived log thread=1 sequence=673 RECID=672 STAMP=787764041

input archived log thread=1 sequence=674 RECID=673 STAMP=787765267

input archived log thread=1 sequence=675 RECID=674 STAMP=787766431

input archived log thread=1 sequence=676 RECID=675 STAMP=787767657

input archived log thread=1 sequence=677 RECID=676 STAMP=787768295

input archived log thread=1 sequence=678 RECID=677 STAMP=787768373

input archived log thread=1 sequence=679 RECID=678 STAMP=787768679

channel ORA_DISK_1: starting piece 1 at 04/07/2012 16:37:59

channel ORA_DISK_1: finished piece 1 at 04/07/2012 16:38:02

piece handle=/backups/rman/testdb/0fnf8pb7_1_1 tag=TAG20120704T163759 comment=NONE

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

Finished backup at 04/07/2012 16:38:02

 

Starting backup at 04/07/2012 16:38:02

using channel ORA_DISK_1

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/testdb/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/testdb/sysaux01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/testdb/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/testdb/users01.dbf

channel ORA_DISK_1: starting piece 1 at 04/07/2012 16:38:03

channel ORA_DISK_1: finished piece 1 at 04/07/2012 16:39:08

piece handle=/backups/rman/testdb/0gnf8pbb_1_1 tag=TAG20120704T163802 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05

Finished backup at 04/07/2012 16:39:08

 

Starting backup at 04/07/2012 16:39:08

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting compressed archived log backup set

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

input archived log thread=1 sequence=680 RECID=679 STAMP=787768748

channel ORA_DISK_1: starting piece 1 at 04/07/2012 16:39:08

channel ORA_DISK_1: finished piece 1 at 04/07/2012 16:39:09

piece handle=/backups/rman/testdb/0hnf8pdc_1_1 tag=TAG20120704T163908 comment=NONE

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

Finished backup at 04/07/2012 16:39:09

 

Starting Control File and SPFILE Autobackup at 04/07/2012 16:39:09

piece handle=/backups/rman/testdb/c-2569094133-20120704-02 comment=NONE

Finished Control File and SPFILE Autobackup at 04/07/2012 16:39:10

Now let’s look at the size of the backups, bearing in mind that this example utilizes a small database, and your database backup size will be different:

[oracle@dbvlin101 ~]$ ls -al /backups/rman/testdb

total 298728

drwxr-xr-x 2 oracle oinstall      4096 Jul  4 16:39 .

drwxr-xr-x 3 oracle oinstall      4096 Jul  4 15:56 ..

-rw-r—– 1 oracle oinstall  14457856 Jul  4 16:38 0fnf8pb7_1_1

-rw-r—– 1 oracle oinstall 279937024 Jul  4 16:39 0gnf8pbb_1_1

-rw-r—– 1 oracle oinstall      3584 Jul  4 16:39 0hnf8pdc_1_1

-rw-r—– 1 oracle oinstall  11173888 Jul  4 16:39 c-2569094133-20120704-02

A quick and easy script that we use regularly, taking into account the default values set as above, is:

RMAN> run {

backup database filesperset 5;

sql ‘alter system archive log current’;

backup archivelog all filesperset 50;

}

If you did not configure the default RMAN values above (step 2), you can achieve similar results by running the following backup script:

RMAN> run {

backup as compressed backupset filesperset 5 database format ‘/backups/rman/testdb/dbf-%U’;

sql ‘alter system archive log current’;

backup as compressed backupset filesperset 50 archivelog all format ‘/backups/rman/testdb/arc-%U’;

backup current controlfile format ‘/backups/rman/testdb/cf-%U’;

}

And as we mentioned earlier, once you have the basics working, you can start looking at more complex scripts and scenarios.

Next Steps:

The above was just a quick example of how you can utilize the default RMAN configuration to get backups in place quickly.  It is important to remember that when you are looking at implementing a backup solution for a production environment that you implement a backup retention period that fits in with your business’ backup and recovery policies.  We did not cover retention period in this post, but please contact Pebble IT if you have further questions.

The most important recommendation we can give you when implementing backups, is to TEST it.  Always test your backups and recovery procedures and document them.  To test your backups, you can easily perform backups on your production system, then copy the backups to another server and perform a restore there.  It is crucial that your production backups are tested on a regular basis.

This post courtesy of DBVisit Support