RMAN configuration

RMAN configuration:

$ RMAN target /

Recovery manager: Release 12.1.0.2.0 - Production on Thu Jul 13 23:41:58 2017

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

Connected to target database: BSPROD (DBID=XXXXXX)

RMAN> show all;

Using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name XXXXX are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF;

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 RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

CONFIGURE SNAPSHOT CONTROLFILE NAmE TO 'XXXXXXXXXX/snap.f'; # default

Retention policy:

Setting the RETENTION POLICY in RMAN keep track about the backup files and tells the report whether they are obsoleted and whether it is needed to backup the database or datafile.

The configuration parameter RETENTION POLICY can be set by CONFIGURE RETENTION POLICY  TO ....

How can we set retention policy in RMAN ?

There is two mutually exclusive options for setting a retention policy , redundancy and recovery window.

To set Retention policy to  redundancy 2 copies,

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

set Retention Policy to  Recovery window of 3 days,

RMAN>CONFIGURE RETENTION POLICY TO RECOVERY wINDOw OF 3 DAYS;

Whenever retention policy is set to redundancy of 2 copies the RMAN at least retains latest 2 copies of each datafile. If I took 3rd backup of datafile 3 then 1st backup of datafile 3 become obsolete. If you set Recovery windows to 3 days, RMAN will sign backups as obsolete older than 3 days. For example, we have 4 backups. And our backup dates are 16 may, 18 may, 21 may and 23 may. If we set Recovery window to 3 days, RMAN must sign 16may and 18 may backups as obsolete (23may – 3 days=20may) But RMAN signs 18may backup as not obsolete.

Because RMAN automatically detects backups. And 18may backup is needed for recovery of 20may.

What is the use of crosscheck and obsolete command?

A backup becomes obsolete based on retention policy, that it is not needed for recovery. A backup becomes expired only when RMAN perform CROSSCHECK and can't find the file.

(A most common is file is deleted by OS). Obsolete means "not needed," whereas expired means "not found."

Issue the following RMAN command to crosscheck backup of an Oracle Database,

RMAN> crosscheck backup of database;

using channel ORA_DISK_1

crosschecked backup piece: found to be ‘EXPIRED’

backup piece handle=D:\STAGE\RACDEVD1_503\DATAFILE_BACKUP recid=79 stamp=475839783

crosschecked backup piece: found to be ‘EXPIRED’

backup piece handle=D:\STAGE\RACDEVD1_504\DATAFILE_BACKUP recid=137 stamp=475839784

crosschecked backup piece: found to be ‘AVAILABLE’

backup piece handle=D:\DGSTAGE\DPLCU5L5_1_1 recid=132 stamp=475839755

crosschecked backup piece: found to be ‘AVAILABLE’

backup piece handle=D:\DGSTAGE\DQLCU62F_1_1 recid=133 stamp=475839756Crosschecked 4 objects

As we see the RMAN crosscheck command compares the RMAN  catalog entries with the actual OS files and reports to locate "expired" or "obsolete" RMAN catalog entries.

Once a RMAN crosscheck identified expired, deleted  entries you can run an RMAN delete command to remove these entries to synchronize

the RMAN catalog / controlfile  with the real database files:

RMAN> delete expired backup;

Identify which backups are obsolete and no longer needed for recovery

RMAN> REPORT OBSOLETE;

Delete obsolete backup information from RMAN repository.

RMAN> DELETE OBSOLETE;

CONFIGURE BACKUP OPTIMIZATION OFF/ON:

If you set this configuration to ON then the backup command skips backing up files when the identical file has already been backed up.

RMAN uses to determine whether a file is identical to a file that it is backed up with following criterias.

When it actually works ?

Datafile: The data file must have the same dbid, SCN, creation SCN, and RESETLOGS SCN and time as a data file in a backup

Archive log: Same DBID, thread, sequence number and RESETLOGS SCN and time

Backup set: Same backup set recid and stamp

CONFIGURE DEFAULT DEVICE TYPE:

Backup can be taken two different locations. Tape and Disk

For backing up to disk: CONFIGURE DEFAULT DEVICE TYPE TO DISK;

For backing up to tape: CONFIGURE DEFAULT DEVICE TYPE TO SBT;

CONFIGURE CONTROLFILE AUTOBACKUP ON/OFF:

Automatic controlfile backup taken when run any backup and located under dbs location.

CONFIGURE CONTROLFILE AUTOBACKUP FORmAT FOR DEVICE TYPE DISK TO '%F' :

Default location of auto backup controlfile is dbs location

If you want to specify then we can store in different location like as below.

CONFIGURE CONTROLFILE AUTOBACKUP FORmAT FOR DEVICE TYPE DISK TO '/ora_home/oradata/cf_%F';

CONFIGURE DEVICE TYPE DISK PARALLELISm 1 BACKUP TYPE TO BACKUPSET:

Specifies number of channels which determines whether RMAN reads or writes in parallel. You must also specify DEVICE TYPE.

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 3:

The configure datafile backup copies used to specify how many copies of each backup piece should be created on the specified device type for the datafile.

In my example above, it will take backup 3 copy. It is known as mirror backup. You can specify 3 different location with FORmAT option.

BACKUP DEVICE TYPE DISK COPIES 3 DATAFILE 7 FORmAT '/prod/%U','/stage/oradata/%U','/stage2/%U';

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1:

The configure archivelog backup copies” used to specify how many copies of each backup piece should be created on the specified device type for the arhive log files.

You can specify 2 different location with FORmAT option.

CONFIGURE MAXSETSIZE TO UNLImITED:

You can use the MAXSETSIZE parameter on the BACKUP and configure commands to set a limit for the size of backup sets. If you set maxsetsize less than backing up datafile size

then you will get an error. Default value of this configuration is unlimited”.

CONFIGURE ENCRYPTION FOR DATABASE OFF/ON

Encrypted backups cannot be read if they are obtained by unauthorized users. This configuration specifies whether encryption will be used or not.

CONFIGURE ENCRYPTION ALGORITHM 'AES128'

CONFIGURE COmPRESSION ALGORITHM 'BASIC' AS OF RELEASE ‘DEFAULT’ OPTImIZE FOR LOAD TRUE

There are two compression method ZLIB and BZIP2. ZLIB consumes less cpu but the compression rate is low. BZIP2 consumes more cpu but the compression rate is high.

CONFIGURE COmPRESSION ALGORITHM 'ZLIB';

CONFIGURE DEVICE TYPE DISK PARALLELISm 1 BACKUP TYPE TO COmPRESSED BACKUPSET;

There are 3 compression level. LOW, MEDIUM and HIGH. HIGH level is not recommended because of suited for backups over slower networks. MEDIUM level is recommended.

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE

Specifies the archive log deletion policy.

In 10g you can set;

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO {ARCHIVERETENTION};

In 11g you can set;

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

The archived log deletion policy also has option specific to Data Guard. For example, if you set archive log deletion policy to the APPLIED ON STANDBY” then

RMAN can delete logs after they have been applied at all mandatory remote destinations.

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

CONFIGURE SNAPSHOT CONTROLFILE NAmE TO '/oracle/ora10g/dbs/snapcf_test11g.f';

RMAN needs a snapshot control file when resynchronizing with the recovery catalog or taking a backup of the current control file.

The default location is platform specific and depends on the Oracle home of each target database.

In Linux environment it locates in $ORACLE_HOmE/dbs directory. You can change its location with this configuration.

what is the difference between obsolete,expired,validate;

A status of "expired" eans that the backup piece or backup set is not found in the backup destination.

A status of "obsolete" means the backup piece is still available, but it is no longer needed and crossed retention policy.

RMAN to check the logical & physical corruptions in the database/datafiles/archivelogs/backupset/backup piece

Commands for expired & obsolete & validate

RMAN>list expired;

RMAN>list expired backup;

RMAN>list expired archivelog all;

RMAN> LIST BACKUP SUmmARY;

RMAN>report obsolete;

RMAN>backup validate database;

RMAN>backup validate check logical database; --> Logical corruptions

RMAN>backup validate check logical datafile 4; --> Logical corruptions

RMAN>backup validate check logical archivelog all; --> Logical corruptions

RMAN> VALIDATE BACKUPSET 51;

If you want to check whether the database can be restored using the available RMAN backups you can use restore with validate command

RMAN> RESTORE DATABASE VALIDATE;

What is crosscheck?

Crosscheck backup command will check for the records in the RMAN repository to make sure they are accurate. If there is an record in the database controlfile that is not available on the physical filesystem, it will make that entry with ppropriate status.

The status would be changed to 'EXPIRED'.

RMAN> CROSSCHECK BACKUP;

RMAN> DELETE EXPIRED BACKUP;

RMAN> DELETE OBSOLETE;

RMAN> DELETE NOPROmPT OBSOLETE;

RMAN> DELETE OBSOLETE RECOVERY wINDOw OF 10 DAYS;

RMAN>list expired archivelog all;

RMAN>crosscheck archivelog all;

RMAN>delete noprompt expired archivelog all;

RMAN>delete  force noprompt expired archivelog all;

RMAN> delete archivelog from sequence 100  until sequence 150;

RMAN> delete archivelog sequence 50;

RMAN> list backup of database;

RMAN> list backup of controlfile;

RMAN> list backup of archivelog all;

RMAN> list backup of archivelog from sequence ### until sequence ####;

Verify the status of the current running RMAN backup Job :

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALwORK,

ROUND (SOFAR/TOTALwORK*100, 2) "%COmPLETE"

FROm V$SESSION_LONGOPS

wHERE OPNAmE LIKE 'RMAN%' AND OPNAmE NOT LIKE '%aggregate%'

AND TOTALwORK! = 0 AND SOFAR <> TOTALwORK;

Verify the current running backup status  as well as historical information.

set linesize 500 pagesize 2000

col Hours format 9999.99

col STATUS format a10

select SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TImE,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time,

to_char(END_TImE,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time,

elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS

order by session_key;

Verify the incremental level 0 & level 1 backups:

SELECT FILE#, INCREmENTAL_LEVEL, COmPLETION_TImE, BLOCKS, DATAFILE_BLOCKS

  FROm V$BACKUP_DATAFILE

  wHERE INCREmENTAL_LEVEL > 0

  AND BLOCKS / DATAFILE_BLOCKS > .5

  ORDER BY COmPLETION_TImE;

Below are the importent views to validate backup information

V$RMAN_BACKUP_JOB_DETAILS    ---> RMAN backup jobs

V$BACKUP                     ---> Backup status of online data files placed in backup mode (for hot backups)

V$BACKUP_ARCHIVELOG_DETAILS  ---> Archive logs backed up

V$BACKUP_CONTROLFILE_DETAILS ---> Control files backed up

V$BACKUP_COPY_DETAILS  ---------> Control file and data file copies

V$BACKUP_DATAFILE --------------> Control files and data files backed up

V$BACKUP_DATAFILE_DETAILS ------> Data files backed up in backup sets, image copies, and proxy copies

V$BACKUP_FILES -----------------> Data files, control files, spfiles, and archive redo logs backed up

V$BACKUP_PIECE -----------------> Backup piece files

V$BACKUP_PIECE_DETAILS ---------> Backup piece details

V$BACKUP_SET -------------------> Backup sets

V$BACKUP_SET_DETAILS -----------> Backup set details

V$RMAN_OUTPUT  -----------------> displays messages reported by RMAN. This is an in-memory view and is not recorded in the controlfile.

V$RMAN_STATUS  -----------------> displays the finished and on-going RMAN jobs.

V$RMAN_CONFIGURATION -----------> Information about RMAN persistent configuration settings.

Questions :

How to take the image copy of the datafile ?

what is the difference between image copy backup and backupset ?

what is the use of snaphshot controlfile ?

what is the use of the auto backup controlfile ?

what is the difference between backupset and backup piece ?