Backup and Recovery (0) -- recommended setting for RMAN and init.ora

1. Settings for the database in init.ora

1.1 it is better to use flash_recovery_area for archive logs, controlfile backup and rman backup files

ALTER SYSTEM RESET log_archive_dest_1 SCOPE=SPFILE SID='*';ALTER SYSTEM RESET log_archive_format SCOPE=SPFILE SID='*';
alter system set db_recovery_file_dest = /flash scope=spfile sid='*';alter system set db_recovery_file_dest_size= 1073741824000 scope=spfile sid='*'

alter system set control_file_record_keep_time = 30 #in case not using rman catalog

1.2 it is better to enable archive log even on DEV and UAT environment. For DEV environment, the chances that the developers make a mistake are far higher. For UAT, we need to mimic production setting. Need to shutdown database and startup mount before running following command:

alter database archivelog;

archive log list;

1.3 it is better to enable flash back database feature as application patching does not have an easy rollback procedure. Startup mount then:

alter database flashback on;

1.4 check if recyclebin=on via show parameter recyclebin;

1.5 enable block change tracking in case we need to use incremental RMAN backup.

alter database enable block change tracking using file ‘/path/change_tracking.f’;

2. settings for RMAN
CONFIGURE CONTROLFILE AUTOBACKUP ON; #default OFFCONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT 'sys/syspw@PROD1';CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT 'sys/syspw@PROD1';CONFIGURE CHANNEL 3 DEVICE TYPE DISK CONNECT 'sys/syspw@PROD2';CONFIGURE CHANNEL 4 DEVICE TYPE DISK CONNECT 'sys/syspw@PROD2';CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO compressed BACKUPSET; #default parallelism 1, no compressed

It is better to turn on controlfile autobackup so that any structure changes for database are saved. Turn on compressed backupset to save disk space. Need to configure channels in RAC environment. For a single node, ignore. Also use spfile so that it got backed up too when controlfile autobacup is on.

3. rman backup script

After above configuration, rman backup script will be very simple:

RMAN> run {2> backup archivelog all delete input;3> }

RMAN> run {
2> backup database plus archivelog all delete input;
}