Backup and Recovery Best Practices


Here are the best practices for backup and recovery for Oracle Databases. Although most of them are basic principles and
can be applied to all Oracle versions, some of them are only valid for Oracle 10g and newer databases:
1) No need to explain: You must always run your DB in Archivelog mode!
2) Multiplex the controlfile at least 3 different locations. Be sure that they are located in different partitions (if
possible different physical disks).
3) Multiplex the online redo log groups and members, be sure that putting them in different partitions (if possible
different physical disks):
SELECT GROUP#, MEMBER FROM V$LOGFILE;
ALTER DATABASE ADD LOGFILE MEMBER ‘/u01/oradata/redo12.log’ TO GROUP 1;
4) Take regular backups and verify that they work! Use validate command of RMAN or restore them to a test environment
RMAN> RESTORE DATABASE VALIDATE;
RMAN> RESTORE ARCHIVELOG ALL VALIDATE;
RMAN> RECOVER DATABASE TEST;
5) Automatically backup your control file, it’s also recommended to set the format of your autobackup file:
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT
FOR DEVICE TYPE ‘SBT_TAPE’ TO ‘%F’;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT
FOR DEVICE TYPE DISK TO ‘%F’;
6) Use RMAN catalog. When you use a recovery catalog, RMAN can perform a wider variety of automated backup and recovery
functions than when you use the control file in the target database as the sole repository of metadata.
7) Turn on block checking to detect corrupt blocks in the database.

ALTER SYSTEM SET DB_BLOCK_CHECKING=TRUE SCOPE=BOTH;
Use the ‘check logical’ parameter to detect logical corruptions in data blocks:
RMAN> BACKUP CHECK LOGICAL DATABASE PLUS ARCHIVELOG DELETE INPUT;

9) Use Flashback Technologies. Turn on recyclebin, size undo tablespace according to your undo retention policy and if
your system has no IO and disk space problem, turn on flashback logs and set Flashback Database target retention:

ALTER SYSTEM SET RECYCLEBIN=ON SCOPE=BOTH;
ALTER DATABASE FLASHBACK ON;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=11520 SCOPE=BOTH;
10) Use compression for RMAN backups. Oracle backup compression usually improves backup and recovery time (unless your
system has CPU bottlenecks) and reduces usage of storage:

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE
TO COMPRESSED BACKUPSET;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s