Configure and Use of Flash Recovery Area

Flash recovery area i.e. FRA available since oracle 10g edition and is identified as unified storage location for all recovery related files in oracle database. We will discuss more the about flash recovery area

• The flash recovery area cane reside in single file system or as an ASM diskgroup
• The following permanent items are stores in flash recovery area

Control file :

• Oracle stores one copy of the control file in flash recovery area during installation

Online redo logfile

• You can store one mirrored copy from each redo log file group in flash recovery area

 Following transient items are stored in flash recovery area

• Archived redo log files
• Flashback logs
• Controlfile automatic backup
• Datafile copies (during RMAN backup/cloning as ‘backup as copy’ command)
• RMAN backupsets
• RMAN files

There are three initialization parameters controls location of new control file, online redo logs and datafile are DB_CREATE_FILE_DEST, DB_RECOVERY_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST

• The DB_CREATE_FILE_DEST specifies the default location for oracle managed datafiles (OMF) if we do not explicitly specify destination
• The DB_CREATE_ONLINE_LOG_DEST specifies upto five location for online redo logs files, if this parameter is not specified the oracle uses DB_CREATE_FILE_DEST as destination for online redo log files
• The DB_RECOVERY_FILE_DEST specify the default location for flash recovery are
• Recommended size of flash recovery are is sum of database size , size of incremental backup and size of all archived logs that have not been moved to tape or any other location.

Setting Flash Recovery Area

Setting flash recovery area for use requires below two parameters to be set

DB_RECOVERY_FILE_DEST

DB_RECOVERY_FILE_DEST_SIZE

Make sure you enough space under server file system or ASM diskgroup

Setting parameters

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

----------------------------------- ----------- -----------

db_recovery_file_dest                string  

db_recovery_file_dest_size           big integer

set recovery location
alter system set DB_RECOVERY_FILE_DEST = '+RECO01' scope = both;

set the size of FRA
alter system set DB_RECOVERY_FILE_DEST_SIZE = 4G scope = both;

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

---------------------------------- ----------- -----------

db_recovery_file_dest                string      +RECO01

db_recovery_file_dest_size           big integer  4G

Usage of Flash Recovery Area

The database alert log will indicate, if database FRA limit is full though you have enough space on file system or diskgroup.

Usage of FRA can be determined using below queries

Check location and size using v$recovery_file_dest

SQL>  select * from  v$recovery_file_dest;

NAME  SPACE_LIMIT  SPACE_U SPACE_REC NUMBER_OF_FILES       CON_ID
----------------- -------- --------- --------------- ---------------+FLASH 107374182400  2097152       0              2              0

Check usage for areas and purpose

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE    PERCENT_S_USED PERCENT_S_RECLAIMABLE NUMBER  CON_ID
----------- -------------- ------------------- --------- ---------CONTROL FILE            0               0             0           0

REDO LOG                0               0             0           0
ARCHIVED LOG            0               0             2           0
BACKUP PIECE            0               0             0           0
IMAGE COPY              0               0             0           0
FLASHBACK LOG           0               0             0           0
FOREIGN ARCHIVED LOG    0               0             0           0
AUXILIARY DATAFILE COPY 0               0             0           0

Size of flash recovery area can be modified using alter system command. Database outage will not be required to carry out this change

alter system set db_recovery_dest_size = 6G scope=both;

Read More

Oracle RMAN : Incrementally Updated Backups

We have learned about incremental backups and how to make these incremental backups faster. Now we will discuss incrementally updated backups

• Assume we have very huge size database say around 50-60TB, there are few challenges we may face while performing backup like making backup faster and keeping recovery time as less as possible.
• Generally recovery is done after restore and restore takes all the time, so we need to concentrate on reducing this restore time.
• For backup minimization time we have enough option of incremental backups but in order to reduce restore time, we go new Incrementally updated backups
• Using incrementally updated backup method, RMAN rolls forward the backup taken of an image copy with subsequent incremental backups. With incremental backups updated with all the changes since last incremental level backup
• Using RMAN, database can be just switched over this updated image copy, rather than restoring them back to original files location
• Since we are not restoring files, restore will be very very minimum and to recover the same, redo logs will be less and it would be only last incremental backup
• Thus within few minutes, we will be able to restore and recover the database
• One of the major condition while using this method is use of large enough FRA (Fast Recovery Area) in order to take full image copy database backup. RMAN will look for updated image at FRA only

Now we will discuss how exactly this works

We can use RUN block to execute set of RMAN commands

RUN

{

RECOVER copy of database

with tag 'tech_db_incr';

BACKUP

incremental level 1

for recover of copy with tag 'tech_db_incr' database;

}

Assuming you running this RUN block daily, here is what happens every day

Day 1 : On first day RECOVER command does nothing , to apply incremental backups a level backup is needed, since there isn't one existing yet BACKUP command will create level 0 image copy of database backup

Day 2 : On second day, as level 0 backup exists the BACKUP command will create level 1 incremental backup. There wont be any work for RECOVER command on second day as well

Day 3 Onward : from third day onward RECOVER command will update the image copy with previous days level 1 incremental backup daily 

To do point in time recovery of this image copy, the available time window is only until level 0 backup, so if we created level 0 backup of database on Aug 6, we can not recover it using incrementally updated backups till Aug 4 or so. 

Read More

Oracle RMAN: Fast Incremental Backups

There are two ways to achieve fast incremental backups as mentioned in last section of this post 

1. Block change tracking (BCT) file
2. Multi-Section incremental backups using section size (12c)

We will discuss these two methods in details

Block change tracking (BCT) file

• While performing incremental backup RMAN search for modified blocks whose system change number (SCN) is higher than last incremental level backups incremental start SCN
• In normal incremental backup strategy, in order to identify modified blocks it would require to read an entire datafile
• If incremental backups needs to be fast enough we need to skip the scan of entire datafile to find modified blocks, oracle 10g introduced a more refined way of using block change tracking (BCT) file.
• This file keeps entry of those blocks that are modified since last full backup
• At the time of next incremental backup, RMAN would read details from this file only and avoid looking for whole datafile
• BCT file uses bitmap structures to update and maintain the information from changed blocks
• BCT file is neither default nor it gets generated at the time of database creation.
• It can be viewed using database view v$block_change_tracking

SQL> select filename, status from v$block_change_tracking;

FILENAME   STATUS

---------- ----------

          DISABLED

SQL>

We can enable block change tracking file using below command

SQL >alter database enable block change tracking using file '/u01/oracle/bct/config.f';

Database altered

SQL> select filename,status from v$block_change_tracking;

FILENAME                               STATUS

-------------------------------------- ----------

/u01/oracle/bct/config.f                ENABLED

• Default location for BCT file is $ORACLE_HOME/dbs
• Default size would be 10M (1/30,000) of total database sizes at the time of database creation.
• When BCT file is enabled, oracle database use the background process change tracking writer (CTWR) for recording the bitmaps of blocks being modified for datafile
• The CTWR background process uses memory are CTWR dba buffer allocated from large pool

Current size is viewed using  v$sgastat view

SQL> select pool,name, bytes from v$sgastat where name like 'CTWR%';

POOL         NAME                            BYTES

------------ -------------------------- ----------

large pool   CTWR dba buffer               1525808

BCT file speeds up the already faster incremental backups even more, this file does not need an additional administration by DBA.

 Multi Section Incremental backups

• As we have discussed here, SECTION SIZE clause helps to improve the performance backups of huge size datafiles and databases, same can be used in Incremental backup strategy as well.
• To make use of SECTION SIZE  clause in level 1 incremental backups, the compatible parameter must be set to 12.0, for level 0 incremental backups compatible can be 11.0
• Using below command we can complete multi section incremental backups

RMAN > backup incremental level 1 section 500m database;

Kindly comment below if any additional information is required

Read 

• RMAN Introduction
• Oracle RMAN Commands
• Incremental Backups

Read More

ORA-19804, ORA-19809: limit exceeded for recovery files

I was taking backup of database as image copy of size 200G, I faced ORA errors like ORA-19809, ORA-19804 as shown below

RMAN> run

{

allocate channel ch1 type disk;

backup as copy SECTION SIZE 800M database;

release channel ch1;

}2> 3> 4> 5> 6>

using target database control file instead of recovery catalog

allocated channel: ch1

channel ch1: SID=2330 device type=DISK

Starting backup at 07-JAN-19

channel ch1: starting datafile copy

input datafile file number=00013 name=+DATA/TECH_DB/DATAFILE/tech_tbs_data.277.996526534

backing up blocks 1 through 153600

released channel: ch1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ch1 channel at 01/07/2019 05:52:11

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 322123595776 bytes disk space from 107374182400 limit

RMAN> 

Below is the solution for the same

I found database size is 200GB and FRA size is set 100GB only, which caused this error

commands to check size of FRA

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      +FLASH

db_recovery_file_dest_size           big integer 100G

SQL>

Command to check size of database and usage of FRA

SQL > select sum(bytes)/1024/1024/1024 as "Size Of DB" from dba_data_files;

              Size Of DB

-------------------------

                      200

SQL> col NAME for a10

SQL> SELECT NAME, round(space_limit/1024/1024/1024,2) TOTAL_GB, round(space_used/1024/1024/1024,2) USED_GB, round((space_limit-space_used+space_reclaimable)/1024/1024/1024,2) AVAILABLE_GB, ROUND((space_used-space_reclaimable)/space_limit * 100,1) PERCENT_FULL FROM v$recovery_file_dest;

NAME         TOTAL_GB    USED_GB AVAILABLE_GB PERCENT_FULL

---------- ---------- ---------- ------------ ------------

+FLASH            100       1.09        99.42           .6

I have changed size of the FRA using DB_RECOVERY_FILE_DEST_SIZE to 250GB, then it worked without any issue.

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE =250G SCOPE=BOTH

Once this is done fire RMAN backup command, it will initiate the backup and will not through this error.

Read More

Oracle RMAN : File Section for backup of large datafiles

After learning RMAN introduction and taking first backup of database, we will go through file section for RMAN backups of large datafiles and databases.

• With introduction of BigFile tablespaces in oracle, large sized datafiles are command these days. For such large datafiles, it takes enormous amount of time to get backed up.
• By using multiple channels, we can make it faster but issue remains same for single large sized datafiles, as channels support inter-file parallelism not intra-file parallelism
• To resolve this we need to logically divide large data file into small file chunks using the option “SECTION SIZE” in backup command
• When large data file is broken into many smaller chunks, each chunk will be treated as separate file. Every single chunk will be backed up by individual channels i.e. intra parallelism.
• SECTION SIZE can be mentioned in KB, MB, GB and backup will be completed in backup set format
• SECTION SIZE clause can be used in either full database backup or partial database backup such as tablespace or datafile
• In below example, we will take backup of one of tablespace SYSTEM using SECTION SIZE

RMAN> run

{

allocate channel ch1 type disk;

BACKUP SECTION SIZE 800M TABLESPACE SYSTEM;

release channel ch1;

}2> 3> 4> 5> 6>

allocated channel: ch1

channel ch1: SID=2330 device type=DISK

Starting backup at 07-JAN-19

channel ch1: starting full datafile backup set

channel ch1: specifying datafile(s) in backup set

input datafile file number=00001 name=+DATA/TECH_DB/DATAFILE/system.285.996743219

backing up blocks 1 through 102400

channel ch1: starting piece 1 at 07-JAN-19

channel ch1: finished piece 1 at 07-JAN-19

piece handle=+FLASH/TECH_DB/BACKUPSET/2019_01_07/nnndf0_tag20190107t054719_0.328.996904041 tag=TAG20190107T054719 comment=NONE

channel ch1: backup set complete, elapsed time: 00:00:07

channel ch1: starting full datafile backup set

channel ch1: specifying datafile(s) in backup set

input datafile file number=00001 name=+DATA/TECH_DB/DATAFILE/system.285.996743219

backing up blocks 102401 through 204800

channel ch1: starting piece 2 at 07-JAN-19

channel ch1: finished piece 2 at 07-JAN-19

piece handle=+FLASH/TECH_DB/BACKUPSET/2019_01_07/nnndf0_tag20190107t054719_0.327.996904049 tag=TAG20190107T054719 comment=NONE

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

channel ch1: starting full datafile backup set

channel ch1: specifying datafile(s) in backup set

input datafile file number=00001 name=+DATA/TECH_DB/DATAFILE/system.285.996743219

backing up blocks 204801 through 262144

channel ch1: starting piece 3 at 07-JAN-19

channel ch1: finished piece 3 at 07-JAN-19

piece handle=+FLASH/TECH_DB/BACKUPSET/2019_01_07/nnndf0_tag20190107t054719_0.326.996904051 tag=TAG20190107T054719 comment=NONE

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

channel ch1: starting full datafile backup set

channel ch1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ch1: starting piece 1 at 07-JAN-19

channel ch1: finished piece 1 at 07-JAN-19

piece handle=+FLASH/TECH_DB/BACKUPSET/2019_01_07/ncsnf0_tag20190107t054719_0.325.996904055 tag=TAG20190107T054719 comment=NONE

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

Finished backup at 07-JAN-19

released channel: ch1

RMAN>

• For Oracle 12c onward, SECTION SIZE clause can be used for image copy format backup as well, command will be like below

RMAN > backup as copy SECTION SIZE 500M database;

• Using SECTION SIZE method, it helps a lot in case database is huge to reduce elapsed time.

Feel free to comment  here and share if you like it.

[Also read : RMAN disk backup ]

Read More

Oracle RMAN : Incremental Backups

In Previous articles we have discussed, RMAN Introduction  and started with RMAN commands and first backup. Now we will discuss more about incremental backups.

• For large databases taking full backup is not possible every time as it will consume lot of space, time and resources and may cause performance degradation while database is running.
• Incremental backups are designed to for such scenarios and are among best options.
• As we already discussed here incremental backups are backups which includes only modified blocks since last full incremental backup taken.
• There is base backup level designated with level 0 and subsequent backups after it are labeled level 1.
• For level 1 there another two options
• Differential incremental backups
• Cumulative incremental backups
• Theoretically these are already discussed in detail in RMAN introduction

• Now will discuss commands to take these type of backups

Differential backup at LEVEL 0 for the database

RMAN> backup incremental level 0 database;

Starting backup at 09-JAN-19

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=23 devtype=DISK

channel ORA_DISK_1: starting incremental level 0 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00003 name=C:\ORACLE\ORADATA\TECH_DB\SYSAUX.DBF

input datafile fno=00001 name=C:\ORACLE\ORADATA\TECH_DB\SYSTEM.DBF

input datafile fno=00004 name=C:\ORACLE\ORADATA\TECH_DB\USERS.DBF

input datafile fno=00005 name=C:\ORACLE\ORADATA\TECH_DB\TECHON_TBS.DBF

input datafile fno=00002 name=C:\ORACLE\ORADATA\TECH_DB\UNDO.DBF

channel ORA_DISK_1: starting piece 1 at 09-JAN-19

channel ORA_DISK_1: finished piece 1 at 09-JAN-19

piece handle=C:\ORACLE\APP\ORACLE\FLASH_RECOVERY_AREA\TECH_DB\BACKUPSET\2019_01_09\O1_MF_NNND0_TAG20190

109T205403_G3D4JQTM_.BKP tag=TAG20190109T205403 comment=NONE

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

channel ORA_DISK_1: starting incremental level 0 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 09-JAN-19

channel ORA_DISK_1: finished piece 1 at 09-JAN-19

piece handle=C:\ORACLE\APP\ORACLE\FLASH_RECOVERY_AREA\TECH_DB\BACKUPSET\2019_01_09\O1_MF_NCSN0_TAG20190

109T205403_G3D4M4X5_.BKP tag=TAG20190109T205403 comment=NONE

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

Finished backup at 09-JAN-19

RMAN>

For LEVEL 1

RMAN> backup incremental level 1 database;

Starting backup at 09-JAN-19

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 1 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00003 name=C:\ORACLE\ORADATA\TECH_DB\SYSAUX.DBF

input datafile fno=00001 name=C:\ORACLE\ORADATA\TECH_DB\SYSTEM.DBF

input datafile fno=00004 name=C:\ORACLE\ORADATA\TECH_DB\USERS.DBF

input datafile fno=00005 name=C:\ORACLE\ORADATA\TECH_DB\TECHON_TBS.DBF

input datafile fno=00002 name=C:\ORACLE\ORADATA\TECH_DB\UNDO.DBF

channel ORA_DISK_1: starting piece 1 at 09-JAN-19

channel ORA_DISK_1: finished piece 1 at 09-JAN-19

piece handle=C:\ORACLE\APP\ORACLE\FLASH_RECOVERY_AREA\TECH_DB\BACKUPSET\2019_01_09\O1_MF_NNND1_TAG20190

109T205803_G3D4R3OL_.BKP tag=TAG20190109T205803 comment=NONE

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

channel ORA_DISK_1: starting incremental level 1 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 09-JAN-19

channel ORA_DISK_1: finished piece 1 at 09-JAN-19

piece handle=C:\ORACLE\APP\ORACLE\FLASH_RECOVERY_AREA\TECH_DB\BACKUPSET\2019_01_09\O1_MF_NCSN1_TAG20190

109T205803_G3D4S8LC_.BKP tag=TAG20190109T205803 comment=NONE

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

Finished backup at 09-JAN-19

RMAN>

For taking cumulative incremental backup

RMAN> backup incremental level 1 cumulative database;

Starting backup at 09-JAN-19

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 1 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00003 name=C:\ORACLE\ORADATA\TECH_DB\SYSAUX.DBF

input datafile fno=00001 name=C:\ORACLE\ORADATA\TECH_DB\SYSTEM.DBF

input datafile fno=00004 name=C:\ORACLE\ORADATA\TECH_DB\USERS.DBF

input datafile fno=00005 name=C:\ORACLE\ORADATA\TECH_DB\TECHON_TBS.DBF

input datafile fno=00002 name=C:\ORACLE\ORADATA\TECH_DB\UNDO.DBF

channel ORA_DISK_1: starting piece 1 at 09-JAN-19

channel ORA_DISK_1: finished piece 1 at 09-JAN-19

piece handle=C:\ORACLE\APP\ORACLE\FLASH_RECOVERY_AREA\TECH_DB\BACKUPSET\2019_01_09\O1_MF_NNND1_TAG20190

109T210011_G3D4W49C_.BKP tag=TAG20190109T210011 comment=NONE

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

channel ORA_DISK_1: starting incremental level 1 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 09-JAN-19

channel ORA_DISK_1: finished piece 1 at 09-JAN-19

piece handle=C:\ORACLE\APP\ORACLE\FLASH_RECOVERY_AREA\TECH_DB\BACKUPSET\2019_01_09\O1_MF_NCSN1_TAG20190

109T210011_G3D4X96M_.BKP tag=TAG20190109T210011 comment=NONE

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

Finished backup at 09-JAN-19

RMAN>

To make most of incremental backups, you can use different combined approaches

• Taking full level 0 backup on tape drives as it will be once in a week.
• Taking incremental level 1 backup on disk
• Use of compression

Comment below if any additional information is required.

Read More