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

MongoDB 4.0 New Features

MongoDB 4.0 has been released on Aug 6, 2018, with tremendous new features specially ACID transactions.

I have listed the MongoDB 4.0 new features below

Read concern snapshot

• MongoDB 4.0 introduces a new concern level snapshot for the multi-document transactions.
• Read concern helps in targeting consistency and isolation properties of data
• This new feature ensures that a consistent view of data is returned to the client, whether data is being modified simultaneously

Data Type Conversions

• This is new in MongoDB 4.0 to convert data types, this comes under the aggregation framework and can be used with the help of the $convert expression

New String Operators

Below are the string operators added in the new version 4.0

• $ltrim : Removes white spaces or specified characters from beginning of string
• $rtrim : Removes white spaces or specified characters from end of the string
• $trim : Removes white spaces or specified characters from beginning and end of the string

Read Preference

• In the previous release MongoDB blocked secondary reads while oplog entries were applied.
• Now there is improved read latency and increased throughput from the replica set which helps in maintaining a consistent ordering of data

Sharding Operations and Migration throughput

• Sharded migrations are now up 40% faster helping for better distribution of data
• Operations can list and kill queries running in shared cluster.

Locking System

• By default, multi-document transactions wait 5 milliseconds to acquire locks required by operations in transactions
• If transactions can not acquire their required locks within 5 milliseconds the transactions abort

The latest Minor release is 4.0.5 (Dec 20, 2018)

Below will be featured in the upcoming MongoDB 4.2

• Removed MMAPv1 storage engine 
• Removed a few commands and methods like group, eval, copydb etc.
• Security improvements like adding TLS and depreciating SSL
• Aggregation improvements
• Transaction Manager

[Also read: Introduction to MongoDB MongoDB storage Engines]

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

Oracle : Starting with RMAN Commands

In previous article we have discussed introduction to RMAN, Now will learn RMAN commands and taking backup of database and understanding its output.

RMAN is very easy tool to interact with, it understand simple English language. If we want to take backup then just type backup in RMAN prompt along with whether backup of database or tablespace, so we want to take backup of database then backup database will do the needful.

RUN Block
In order to execute number of RMAN commands together like restore, recover and open then we need to use RUN block. This will run commands sequentially.
for example

RMAN> run

2> {

3> restore database;

4> recover database;

5> alter database open;

 6> }

This run block first restore the database, then recover database and at last it will open database.

Taking First Full Backup
Now will take full database backup and explain each line in details. Backup of database will get started on disk at FRA (Fast Recovery Area) location. Those who do not know FRA, I will write another post but for here understand it is defined with bellow two parameters
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
Now invoke RMAN client and execute below command to start backup

RMAN> backup database plus archivelog;

This is command to start database backup with archive log

Starting backup at 03-JAN-19

This indicates, backup starting at mentioned date

current log archived

This line shows current log is archived

using target database control file instead of recovery catalog

This line indicates, RMAN is using target control file and not recovery catalog

allocated channel: ORA_DISK_1

This indicates, we are using disk channel 

channel ORA_DISK_1: sid=21 devtype=DISK

channel ORA_DISK_1: starting archive log backupset

This indicates starting of archived log backup set, its list and sequence is shown in subsequent below lines

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=2 recid=1 stamp=992908371

input archive log thread=1 sequence=36 recid=2 stamp=996615449

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

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

piece handle=C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\BACKUPSET\2019_01_03\O1_MF_ANNNN_TAG20190

103T213732_G2WDT5NH_.BKP tag=TAG20190103T213732 comment=NONE

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

Finished backup at 03-JAN-19

This indicates backup of archived log is finished, it automatically generates tag, format for tag is TAGYYYYMMDDTHHMMSS

Starting backup at 03-JAN-19

using channel ORA_DISK_1

This is start of full database backup, its take single disk channel as we have not mentioned any. 

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00003 name=C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF

input datafile fno=00001 name=C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF

input datafile fno=00004 name=C:\ORACLEXE\ORADATA\XE\USERS.DBF

input datafile fno=00005 name=C:\ORACLEXE\ORADATA\XE\TECHON_TBS.DBF

input datafile fno=00002 name=C:\ORACLEXE\ORADATA\XE\UNDO.DBF

This are the datafiles which are getting backup 

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

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

piece handle=C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\BACKUPSET\2019_01_03\O1_MF_NNNDF_TAG20190

103T213734_G2WDT7CR_.BKP tag=TAG20190103T213734 comment=NONE

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

At this point creation of backup set for full database is complete

channel ORA_DISK_1: starting full 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 03-JAN-19

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

piece handle=C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\BACKUPSET\2019_01_03\O1_MF_NCSNF_TAG20190

103T213734_G2WDVOWG_.BKP tag=TAG20190103T213734 comment=NONE

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

Finished backup at 03-JAN-19

control file and spfile are being backed up here. This will automatically gets backed up.

Starting backup at 03-JAN-19

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=37 recid=3 stamp=996615503

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

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

piece handle=C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\BACKUPSET\2019_01_03\O1_MF_ANNNN_TAG20190

103T213823_G2WDVRL6_.BKP tag=TAG20190103T213823 comment=NONE

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

Finished backup at 03-JAN-19

Current redo log is archived and backed up it as well.

This is finish of complete database backup
[Also read : RMAN introduction, User Managed Backups, RMAN Disk backup]

Read More