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):
|
1 2 |
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 periodically:
|
1 2 3 |
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:
|
1 2 3 4 5 |
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.
|
1 |
ALTER SYSTEM SET DB_BLOCK_CHECKING=TRUE SCOPE=BOTH; |
8) Use the ‘check logical’ parameter to detect logical corruptions in data blocks:
|
1 |
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:
|
1 2 3 |
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:
|
1 2 3 |
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG; RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET; |
Pingback: Backups and a Clever Student - Steve Karam :: The Oracle Alchemist
kenneth