11GR2 TUNING RMAN PART – I

11GR2 TUNING RMAN PART – I:
========================================

In this post and the next one, I will discuss various methods by which we can improve the
performance of RMAN backup and recovery.

RMAN backup/recovery performance is influenced by various parameters:
– Parallelism
– Maxpiecesize – Maximum size of each backup piece
– FIlesperset _ The number of datafiles in each backupset
– Maxopenfiles – Maximum No. of files which can be read from simultaneously
– Multiplexing level
– Asynchronous / Synchronous I/O
– Large pool Size-

- SETUP –

As part of setup I will create 4 more tablespaces . I already have 5 tablespaces in my
database. So in all I will have 9 tablespaces .

sql>conn / as sysdba
create tablespace rman1
datafile ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\rman1.dbf’ size 100m;
create tablespace rman2
datafile ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\rman2.dbf’ size 100m;
create tablespace rman3
datafile ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\rman3.dbf’ size 100m;
create tablespace rman4
datafile ‘C:\APP\ADMINISTRATOR\ORADATA\ORCL\rman4.dbf’ size 100m;

create table hr.test1 tablespace rman1 as select * from hr.employees;
create table hr.test2 tablespace rman2 as select * from hr.employees;
create table hr.test3 tablespace rman3 as select * from hr.employees;
create table hr.test4 tablespace rman4 as select * from hr.employees;

Let us discuss each parameters one by one .

– PARALLELISM –

we can parallelize the backup/recovery operation by
– setting parallelism for the device
– Allocating multiple channels and

. Let RMAN decide which file to backup/restore using which channel
. BAcking up/Restoring specified files using specified channel

Each channel used will create its own backupset.

-- SET PARALLELSM FOR DEVICE –
RMAN>delete backup;
configure device type disk parallelism 3;
backup format ‘c:\%U.bak’ tablespace rman1, rman2, rman3;
list backup of tablespace rman1, rman2, rman3;
List of Datafiles in backup set 25
6       Full 1583594    12-SEP-12 C:\APP\ADMINISTRATOR\ORADATA\ORCL\RMAN1.DBF
List of Datafiles in backup set 26
7       Full 1583595    12-SEP-12 C:\APP\ADMINISTRATOR\ORADATA\ORCL\RMAN2.DBF
List of Datafiles in backup set 27
8       Full 1583596    12-SEP-12 C:\APP\ADMINISTRATOR\ORADATA\ORCL\RMAN3.DBF
——————————————————————————————-
- Allocating multiple channels and let RMAN decide which file to backup using which channel
———————————————————————————————-
— Note that RMan decides to backup rman1 and rman3 using c1 and rman2 using c2
RMAN>configure device type disk parallelism 1;
delete backup;
RMAN>run{
allocate channel c1 device type disk format ‘c:\%U.bak';
allocate channel c2 device type disk format ‘c:\%U.bak';
backup tablespace rman1, rman2, rman3;
}
channel c1: specifying datafile(s) in backup set
input datafile file number=00006 name=C:\APP\ADMINISTRATOR\ORADATA\ORCL\RMAN1.DBF
input datafile file number=00008 name=C:\APP\ADMINISTRATOR\ORADATA\ORCL\RMAN3.DBF
channel c2: specifying datafile(s) in backup set
input datafile file number=00007 name=C:\APP\ADMINISTRATOR\ORADATA\ORCL\RMAN2.DBF
-- Check that each channel created its own backupset
RMAN>list backup;
List of Datafiles in backup set 28
6       Full 1584178    12-SEP-12 C:\APP\ADMINISTRATOR\ORADATA\ORCL\RMAN1.DBF
8       Full 1584178    12-SEP-12 C:\APP\ADMINISTRATOR\ORADATA\ORCL\RMAN3.DBF
List of Datafiles in backup set 29
7       Full 1584179    12-SEP-12 C:\APP\ADMINISTRATOR\ORADATA\ORCL\RMAN2.DBF
————————————————————————————–
- Allocating multiple channels and backing up specified files using specified channels
————————————————————————————–
— Note that RMan backs up rman1 and rman2 using c1 and rman3 using c2 as specified
RMAN>delete backup;
run{
allocate channel c1 device type disk format ‘c:\%U.bak';
allocate channel c2 device type disk format ‘c:\%U.bak';
backup (tablespace rman1, rman2 channel c1)
(tablespace rman3 channel c2);
}
channel c1: specifying datafile(s) in backup set
input datafile file number=00006 name=C:\APP\ADMINISTRATOR\ORADATA\ORCL\RMAN1.DB
input datafile file number=00007 name=C:\APP\ADMINISTRATOR\ORADATA\ORCL\RMAN2.DB
channel c2: specifying datafile(s) in backup set
input datafile file number=00008 name=C:\APP\ADMINISTRATOR\ORADATA\ORCL\RMAN3.DB
– Check that each channel created its pwn backupset

RMAN>list backup;
List of Datafiles in backup set 30
6       Full 1584550    12-SEP-12 C:\APP\ADMINISTRATOR\ORADATA\ORCL\RMAN1.DBF
7       Full 1584550    12-SEP-12 C:\APP\ADMINISTRATOR\ORADATA\ORCL\RMAN2.DBF
List of Datafiles in backup set 31
8       Full 1584551    12-SEP-12 C:\APP\ADMINISTRATOR\ORADATA\ORCL\RMAN3.DBF
———————
-— FILESPERSET –
-———————
– This parameter decides how many files will be included in one backupset.
– If > 1 files are backed up and filesperset = 1, one backupset will be created for each file
rman>delete backup;
rman>backup datafile 7,8 filesperset 1 format ‘c:\%U.bak';
list backup ;
— Note that two backupsets are created with one datafile each
List of Backup Sets
===================
List of Datafiles in backup set 17
7       Full 1529096    11-SEP-12 C:\APP\ADMINISTRATOR\ORADATA\ORCL\RMAN2.DBF
List of Datafiles in backup set 18
8       Full 1529098    11-SEP-12 C:\APP\ADMINISTRATOR\ORADATA\ORCL\RMAN3.DBF
Using this parameter we can reduce the recovery time by limiting the no. of files per
backupset, since to restore a particular datafile we will have to restore a smaller file.
This is also beneficial in case the backup fails in the middle. Next time, we can backup
only those datafiles which could not be successfully backed up.
————————–
— MAXPIECESIZE –
————————–
– This parameter controls the maximum size a backup piece can have
– If we backup only one file, it will split into multiple pieces
- Check the size of datafiles.
– Note that datafile datafile for tablespace sysaux is 570m in size
rman>report schema;
File Size(MB) Tablespace           RB segs Datafile Name
—- ——– ——————– ——- ————————
1    690      SYSTEM               ***     C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF
2    570      SYSAUX               ***     C:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
– Backup datafile for sysaux tablespace so that each piece size <= 200M
— Note that backup is spread over 3 backup pieces
rman>delete backup;
rman>run{
allocate channel c1 device type disk maxpiecesize 200m format ‘c:\%U.bak';
backup tablespace sysaux ;
}
list backup of tablespace sysaux;
List of Backup Pieces for backup set 21 Copy #1
BP Key Pc# Status      Piece Name
——- — ———– ———-
21      1   AVAILABLE   C:\0NNKUKP0_1_1.BAK
22      2   AVAILABLE   C:\0NNKUKP0_2_1.BAK
23      3   AVAILABLE   C:\0NNKUKP0_3_1.BAK
This option can be used
– to exercise the operating system limit on the file size.
– to split the backup of a datafile into multiple pieces if the backup does not fit in one
tape
– if backup is to be transferred over network.
————————–
— MAXOPENFILES -
–————————-
— This parameter decides how many files can be backed up simultaneously
i.e. data from how many files can be read at a time.
— Default value = 8
– Take backup of 2 datafiles with maxopenfiles = 1 so that level of multiplexing = 1 i.e.
. datafile 1 will be backed up first
. datafile 2 will be backed up next
so that data from 2 files will not be intermingled.
Rather first all data from datafile 1 will be written and
then data from datafile 2 will be written.
– While the backup is still going on issue the next sql query
RMAN> run{
allocate channel c1 device type disk maxopenfiles 1;
backup datafile 1,2;
}
— Note the change in filename from system01.dbf to sysaux01.dbf
— Note that buffers are allocated to datafile 1 first,
when that has been backed up, then buffers are allocated
to datafile 2
— Since multiplexing level = 1, total of 16 buffers of 1M each are
all allocated to each file one by one
sql>col filename for a25 word_wrapped
select type, status, filename, buffer_size, buffer_count
from v$backup_async_io
where type <> ‘AGGREGATE’
and status = ‘IN PROGRESS';
TYPE      STATUS      FILENAME                  BUFFER_SIZE BUFFER_COUNT
——— ———– ————————- ———– ————
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD     1048576           16
ATA\ORCL\SYSTEM01.DBF
OUTPUT    IN PROGRESS C:\APP\ADMINISTRATOR\FLAS     1048576            4
H_RECOVERY_AREA\ORCL\BACK
UPSET\2012_09_11\O1_MF_NN
NDF_TAG20120911T152958_84
Y2WZ7B_.BKP
SQL> /
TYPE      STATUS      FILENAME                  BUFFER_SIZE BUFFER_COUNT
——— ———– ————————- ———– ————
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD     1048576           16
ATA\ORCL\SYSAUX01.DBF
OUTPUT    IN PROGRESS C:\APP\ADMINISTRATOR\FLAS     1048576            4
H_RECOVERY_AREA\ORCL\BACK
UPSET\2012_09_11\O1_MF_NN
NDF_TAG20120911T152958_84
Y2WZ7B_.BKP
– Take backup of 2 datafiles with maxopenfiles = 2 so that level of multiplexing = 2 i.e.
. datafile 1 and 2 will be backed up simultaneously so that data from 2 files will be intermingled.
– While the backup is still going on issue the next sql query
RMAN> run{
allocate channel c1 device type disk maxopenfiles 2;
backup datafile 1,2;
}
— Note that buffers are allocated simultaneously to datafile 1 and 2
— Since multiplexing level = 2 (< 4), total of 16 buffers of 1M each are allocated.
— Each file gets 8 buffers each of the size of 1M
sql>col filename for a25 word_wrapped
select type, status, filename, buffer_size, buffer_count
from v$backup_async_io
where type <> ‘AGGREGATE’
and status = ‘IN PROGRESS';
TYPE      STATUS      FILENAME                  BUFFER_SIZE BUFFER_COUNT
——— ———– ————————- ———– ————
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD    1048576            8
ATA\ORCL\SYSTEM01.DBF
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD     1048576            8
ATA\ORCL\SYSAUX01.DBF
OUTPUT    IN PROGRESS C:\APP\ADMINISTRATOR\FLAS     1048576            4
H_RECOVERY_AREA\ORCL\BACK
UPSET\2012_09_11\O1_MF_NN
NDF_TAG20120911T153330_84
Y33M40_.BKP
——————–
— MULTIPLEXING
——————–
Level of multiplexing means the no. of files whose data is intermingled
in the backup file. It depends on the following:
– # of files being backed up using one channel
– FIlesperset
– Maxopenfiles
Level of multiplexing = Minimum of the above 3.
We increase level of multiplexing so that data can be simultaneously read from multiple
files so that even if a process to wait to read from one of the files being backed up due to
contention, it can still read from the other files so that output tape drives gets
continuous input. Otherwise, due to lack of the input data, tape drive might overshoot
before stopping and will again have to come back to its earlier position when it gets next
stream of data.
Thus, increasing the level of multiplexing may increase the speed of backup. On the other
hand , it can reduce the restore speed as data for a file has to be extracted from the
intermingled data.
Thus , one has to decide the level of multiplexing based on whether performance of the
backup or recovery is more critial.
In case files being backed up are ASM files, level of multiplexing may be reduced as ASM
automatically takes care of the contention by striping the data.
Input buffers are allocated for the backup. The no. of buffers allocated per file depends
upon the level of multiplexing.
Multiplexing level        Allocation rule
Level <= 4           1 MB buffers are allocated so that the total buffer size for all
input files is 16 MB.
4 < Level <= 8       512 KB are allocated so that the total buffer size for all files is
less than 16 MB.
Level > 8            RMAN allocates four 128 KB disk buffers per channel for each file,
so that the total size is 512 KB per channel for each file.
For multiplexing level 1 and 2 we have already seen that 1 MB buffers are allocated so that
the total buffer size for all input files is 16 MB.
—————————————–
Let’s verify for multiplexing level of 5 .
—————————————–
RMAN>delete backup;
=- Note that 512 KB are allocated so that the total buffer size for all files is less than
16 MB.
— AS backup progresses the files which have been backed up, buffers allocated to them gets
freed
RMAN> run{
allocate channel c1 device type disk maxopenfiles 5;
backup datafile 1,2,3,4,5;
}
sql>/
TYPE      STATUS      FILENAME                  BUFFER_SIZE BUFFER_COUNT
——— ———– ————————- ———– ————
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      524288            6
ATA\ORCL\SYSTEM01.DBF
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      524288            6
ATA\ORCL\SYSAUX01.DBF
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      524288            6
ATA\ORCL\UNDOTBS01.DBF
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      524288            6
ATA\ORCL\EXAMPLE01.DBF
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      524288            6
ATA\ORCL\USERS01.DBF
sql>/
TYPE      STATUS      FILENAME                  BUFFER_SIZE BUFFER_COUNT
——— ———– ————————- ———– ————
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      524288            6
ATA\ORCL\SYSTEM01.DBF
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      524288            6
ATA\ORCL\SYSAUX01.DBF
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      524288            6
ATA\ORCL\UNDOTBS01.DBF
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      524288            6
ATA\ORCL\EXAMPLE01.DBF
sql>/
TYPE      STATUS      FILENAME                  BUFFER_SIZE BUFFER_COUNT
——— ———– ————————- ———– ————
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD     524288            6
ATA\ORCL\SYSTEM01.DBF
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      524288            6
ATA\ORCL\SYSAUX01.DBF
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      524288            6
ATA\ORCL\EXAMPLE01.DBF
sql>/
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      524288            6
ATA\ORCL\SYSTEM01.DBF
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      524288            6
ATA\ORCL\SYSAUX01.DBF
sql>/
TYPE      STATUS      FILENAME                  BUFFER_SIZE BUFFER_COUNT
——— ———– ————————- ———– ————
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      524288            6
ATA\ORCL\SYSTEM01.DBF
—————————————–
Let’s verify for multiplexing level of 9 (> 8) .
—————————————–
RMAN>delete backup;
RMAN> run{
allocate channel c1 device type disk maxopenfiles 9;
backup datafile 1,2,3,4,5,6,7,8,9;
}
— Note that buffers are allocated simultaneously to all the 9 datafiles so that data from all the files will be intermingled in the output file.
— Since multiplexing level = 9 ( Level > 8), RMAN allocates four 128 KB disk buffers per channel for each file, so that the total size is 512 KB per channel for each file.
sql>col filename for a25 word_wrapped
select type, status, filename, buffer_size, buffer_count
from v$backup_async_io
where type <> ‘AGGREGATE’
and status = ‘IN PROGRESS';
TYPE      STATUS      FILENAME                  BUFFER_SIZE BUFFER_COUNT
——— ———– ————————- ———– ————
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD     131072            4
ATA\ORCL\SYSTEM01.DBF
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      131072            4
ATA\ORCL\SYSAUX01.DBF
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      131072            4
ATA\ORCL\UNDOTBS01.DBF
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      131072            4
ATA\ORCL\EXAMPLE01.DBF
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      131072            4
ATA\ORCL\RMAN1.DBF
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      131072            4
ATA\ORCL\RMAN2.DBF
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      131072            4
ATA\ORCL\RMAN3.DBF
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      131072            4
ATA\ORCL\RMAN4.DBF
INPUT     IN PROGRESS C:\APP\ADMINISTRATOR\ORAD      131072            4
ATA\ORCL\USERS01.DBF
Conclusion :

- We can parallelize the backup/recovery operation by

– setting parallelism for the device

– Allocating multiple channels and

. Let RMAN decide which file to backup/restore using which channel

. BAcking up/Restoring specified files using specified channels

- Filesperset

. Using this parameter we can reduce the recovery time by limiting the no. of files per

backupset, since to restore a particular datafile we will have to restore a smaller file.

. This is also beneficial in case the backup fails in the middle. Next time, we can backup

only those datafiles which could not be successfully backed up.

- Maxpiecesize

This option can be used

– to exercise the operating system limit on the file size.

– to split the backup of a datafile into multiple pieces if the backup does not fit in one tape

– if backup is to be transferred over network.

- Maxopenfiles

This parameter decides how many files can be backed up simultaneously i.e. data from how many files can be read at a time.

- Multiplexing

Level of multiplexing means the no. of files whose data is intermingled in the backup file. It depends on the following:

- # of files being backed up using one channel

- FIlesperset

- Maxopenfiles

Level of multiplexing = Minimum of the above 3.

– Increasing the level of multiplexing can effectively keep the tape streaming.

– Increasing the level of multiplexing may increase the speed of backup. On the other

hand , it can reduce the restore speed as data for a file has to be extracted from the

intermingled data.

– Thus , one has to decide the level of multiplexing based on whether performance of the

backup or recovery is more critial.

– In case files being backed up are ASM files, level of multiplexing may be reduced as ASM automatically takes care of the contention by striping the data.

- Input buffers are allocated for the backup. The no. of buffers allocated per file depends

upon the level of multiplexing.

Multiplexing level Allocation rule

Level <= 4 1 MB buffers are allocated so that the total buffer size for all

input files is 16 MB.

4 < Level <= 8 512 KB are allocated so that the total buffer size for all files is

less than 16 MB.

Level > 8 RMAN allocates four 128 KB disk buffers per channel for each file,

so that the total size is 512 KB per channel for each file.

- We can calculate the size of the buffers required by RMAN as follows:

Multiply the total buffer bytes for each data file by the number of data files that are being concurrently accessed by the channel, and then multiply this number by the number of channels.

Assume that you use one channel to back up four data files, and use the settings that are shown above. In this case, multiply as follows to obtain the total size of the buffers that are allocated for the backup:

4 MB per data file Î 1 channel Î 4 data files per channel = 16 MB

In my next post

Tuning RMAN Part-II ,I will discuss the use of the asynchronous i/i to improve the performance of RMAN backup/recovery.

Thanx for your time. Happy Tuning !!

ORACLE CHECKPOINTS:

======================

In this post, I will explain about checkpoints – their purpose and different types of checkpoints.

PURPOSE OF CHECKPOINTS

Database blocks are temporarily stored in Database buffer cache. As blocks are read, they are stored in DB buffer cache so that if any user accesses them later, they are available in memory and need not be read from the disk. When we update any row, the buffer in DB buffer cache corresponding to the block containing that row is updated in memory. Record of the change made is kept in redo log buffer . On commit, the changes we made are written to the disk thereby making them permanent. But where are those changes written? To the datafiles containing data blocks? No !!! The changes are recorded in online redo log files by flushing the contents of redo log buffer to them.This is called write ahead logging. If the instance crashed right now, the DB buffer cache will be wiped out but on restarting the database, Oracle will apply the changes recorded in redo log files to the datafiles.

Why doesn’t Oracle write the changes to datafiles right away when we commit the transaction? The reason is simple. If it chose to write directly to the datafiles, it will have to physically locate the data block in the datafile first and then update it which means that after committing, user has to wait until DBWR searches for the block and then writes it before he can issue next command. This will bring down the performance drastically. That is where the role of redo logs comes in. The writes to the redo logs are sequential writes – LGWR just dumps the info in redologs to log files sequentially and synchronously so that the user does not have to wait for long. Moreover, DBWR will always write in units of Oracle blocks whereas LGWR will write only the changes made. Hence, write ahead logging also improves performance by reducing the amount of data written synchronously. When will the changes be applied to the datablocks in datafiles? The data blocks in the datafiles will be updated by the DBWR asynchronously in response to certain triggers. These triggers are called checkpoints.

Checkpoint is a synchronization event at a specific point in time which causes some / all dirty blocks to be written to disk thereby guaranteeing that blocks dirtied prior to that point in time get written.

Whenever dirty blocks are written to datafiles, it allows oracle

- to reuse a redo log : A redo log can’t be reused until DBWR writes all the dirty blocks protected by that logfile to disk. If we attempt to reuse it before DBWR has finished its checkpoint, we get the following message in alert log : Checkpoint not complete.

- to reduce instance recovery time : As the memory available to a database instance increases, it is possible to have database buffer caches as large as several million buffers. It requires that the database checkpoint advance frequently to limit recovery time, since infrequent checkpoints and large buffer caches can exacerbate crash recovery times significantly.

- to free buffers for reads : Dirtied blocks can’t be used to read new data into them until they are written to disk. Thus DBWrR writes dirty blocks from the buffer cache, to make room in the cache.

Various types of checkpoints in Oracle :

– Full checkpoint
– Thread checkpoint

- File checkpoint

- Parallel Query checkpoint

- Object checkpoint

- Log switch checkpoint

_ Incremental checkpoint

Whenever a checkpoint is triggered :

- DBWR writes some /all dirty blocks to datafiles

- CKPT process updates the control file and datafile headers

FULL CHECKPOINT

- Writes block images to the database for all dirty buffers from all instances.

- Statistics updated

. DBWR checkpoints

. DBWR checkpoint buffers written

. DBWR thread checkpoint buffers written

- Caused by :

. Alter system checkpoint [global]

. ALter database begin backup

. ALter database close

. Shutdown [immediate]

- Controlfile and datafile headers are updated

. Checkpoint_change#

THREAD CHECKPOINT

- Writes block images to the database for all dirty buffers from one instance

- Statistics updated

. DBWR checkpoints

. DBWR checkpoint buffers written

. DBWR thread checkpoint buffers written

- Caused by :

. Alter system checkpoint local

- Controlfile and datafile headers are updated

. Checkpoint_change#

FILE CHECKPOINT

When a tablespace is put into backup mode or take it offline, Oracle writes all the dirty blocks from the tablespace to disk before changing the state of the tablespace.

- Writes block images to the database for all dirty buffers for all files of a tablespace from all instances

- Statistics updated

. DBWR checkpoints

. DBWR tablespace checkpoint buffers written

. DBWR checkpoint buffers written

- Caused by :

. Alter tablespace xxx offline

. Alter tablespace xxx begin backup

. Alter tablespace xxx read only

- Controlfile and datafile headers are updated

. Checkpoint_change#

PARALLEL QUERY CHECKPOINT

Parallel query often results in direct path reads (Full tablescan or index fast full scan). This means that blocks are read straight into the session’s PGA, bypassing the data cache; but that means if there are dirty buffers in the data cache, the session won’t see the most recent versions of the blocks unless they are copied to disk before the query starts – so parallel queries start with a checkpoint.

- Writes block images to the database for all dirty buffers belonging to objects accessed by the query from all instances.

- Statistics updated

. DBWR checkpoints

. DBWR checkpoint buffers written

- Caused by :

. Parallel Query

. Parallel Query component of Parallel DML (PDML) or Parallel DDL (PDDL)

- Mandatory for consistency

- Controlfile and datafile headers are updated

. Checkpoint_change#

OBJECT CHECKPOINT

When an object is dropped/truncated, the session initiates an object checkpoint telling DBWR to copy any dirty buffers for that object to disk and the state of those buffers is changed to free.

- Writes block images to the database for all dirty buffers belonging to an object from all instances.

- Statistics updated

. DBWR checkpoints

. DBWR object drop buffers written

- Caused by dropping or truncating a segment:

. Drop table XXX

. Drop table XXX Purge

. Truncate table xxx

. Drop index xxx

- Mandatory for media recovery purposes

- Controlfile and datafile headers are updated

. Checkpoint_change#

LOG SWITCH CHECKPOINT

- Writes the contents of the dirty buffers whose information is protected by a redo log to the database .

- Statistics updated

. DBWR checkpoints

. DBWR checkpoint buffers written

. background checkpoints started

. background checkpoints completed

- Caused by log switch

- Controlfile and datafile headers are updated

. Checkpoint_change#

INCREMENTAL CHECKPOINT

Prior to Oracle 8i, only well known checkpoint was log switch checkpoint. Whenever LGWR filled an online logfile, DBWR would go into a frenzy writing data blocks to disks, and when it had finished, Oracle would update each data file header block with the SCN to show that file was updated up to that point in time.

Oracle 8i introduced incremental checkpointing which triggered DBWR to write some dirty blocks from time to time so as to advance the checkpoint and reduce the instance recovery time.

Incremental checkpointing has been implemented using two algorithms :

- Ageing algorithm

- LRU/TCH algorithm

AGEING ALGORITHM

This strategy involves writing changed blocks that have been dirty for the longest time and is called aging writes. This algorithm relies on the CKPT Q running thru the cache and buffers being linked to the end of this list the first time they are made dirty.

.The LRU list contains all the buffers – free / pinned / dirty. Whenever a buffer in LRU list is dirtied, it is placed in CKPT Q as well i.e. a buffer can simultaneously have pointers in both LRU list and CKPT Q but the buffers in CKPT Q are arranged in the order in which they were dirtied.Thus, checkpoint queue contains dirty blocks in the order of SCN# in which they were dirtied

Every 3 secs DBWR wakes up and checks if there are those many dirty buffers in CKPT Q which need to br written so as to satisfy instance recovery requirement..

If those many or more dirty buffers are not found,

DBWR goes to sleep

else (dirty buffers found)

.CKPT target RBA is calculated based on

– The most recent RBA

– log_checkpoint_interval

– log_checkpoint_timeout

– fast_start_mttr_target

– fast_start_io_target

– 90% of the size of the smallest redo log file

. DBWR walks the CKPT Q from the low end (dirtied earliest) of the redo log file collecting buffers for writing to disk until it reaches the buffer that is more recent than the target RBA. These buffers are placed in write list-main.

. DBWR walks the write list-main and checks all the buffers

– If changes made to the buffer have already been written to redo log files

. Move those buffers to write-aux list

else

. Trigger LGWR to write changes to those buffers to redo logs

. Move those buffers to write-aux list

. Write buffers from write-aux list to disk

. Update checkpoint RBA in SGA

. Delink those buffers from CKPT Q

. Delink those buffers from write-aux list

- Statistics Updated :

. DBWR checkpoint buffers written

- Controlfile updated every 3 secs by CKPT

. Checkpoint progress record

As sessions link buffers to one end of the list, DBWR can effectively unlink buffers from the other end and copy them to disk. To reduce contention between DBWR and foreground sessions, there are two linked lists in each working set so that foreground sessions can link buffers to one while DBWR is unlinking them from the other.

LRU/TCH ALGORITHM

LRU/TCH algorithm writes the cold dirty blocks to disk that are on the point of being pushed out of cache.

As per ageing algorithm, DBWR will wake up every 3 seconds to flush dirty blocks to disk. But if blocks get dirtied at a fast pace during those 3 seconds and a server process needs some free buffers, some buffers need to be flushed to the disk to make room. That’s when LRU/TCH algorithm is used to write those dirty buffers which are on the cold end of the LRU list.

Whenever a server process needs some free buffers to read data, it scans the LRU list from its cold end to look for free buffers.

While searching

If unused buffers found

Read blocks from disk into the buffers and link them to the corresponding hash bucket

if it finds some clean buffers (contain data but not dirtied or dirtied and have been flushed to disk),

if they are the candidates to be aged out (low touch count)

Read blocks from disk into the buffers and link them to the corresponding hash bucket

else (have been accessed recently and should not be aged out)

Move them to MRU end depending upon its touch count.

If it finds dirty buffers (they are already in CKPT Q),

Delink them from LRU list

Link them to the write-main list (Now these buffers are in CKPT Q and write-main list)

The server process scans a threshold no. of buffers (_db_block_max_scan_pct = 40(default)). If it does not find required no. of free buffers,

It triggers DBWR to dirty blocks in write-mainlist to disk

. DBWR walks the write list-main and checks all the buffers

– If changes made to the buffer have already been written to redo log files

. Move those buffers to write-aux list

else

. Trigger LGWR to write changes to those buffers to redo logs

. Move those buffers to write-aux list

. Write buffers from write-aux list to disk

. Delink those buffers from CKPT Q and w rite-aux list

. Link those buffers to LRU list as free buffers

Note that

- In this algorithm, the dirty blocks are delinked from LRU list before linking them to write-main list in contrast to ageing algorithm where the blocks can be simultaneously be in both CKPT Q and LRU list.

- In this algorithm, checkpoint is not advanced because it may be possible that the dirty blocks on the LRU end may actually not be the ones which were dirtied earliest. They may be there because the server process did not move them to the MRU end earlier. There might be blocks present in CKPT Q which were dirtied earlier than the blocks in question.

I hope the information was useful. Thanks for your time.

Keep visiting the blog…

References:
Oracle Core : Essential Internals for DBA’s and Developers By Jonathan Lewis

http://www.dbafree.net/wp-content/uploads/2011/05/CheckPoints.pdf
http://jonathanlewis.wordpress.com/2007/04/12/log-file-switch/

11g R2 RAC : LOAD BALANCING

11g R2 RAC : LOAD BALANCING:

============================

In this post, I will discuss about various types of load balancing techniques that can be configured in 11g R2 RAC for workload management.

In RAC we have multiple instances of a database running on multiple servers.

Workload management involves :

– Failover : If connection to an instance fails, client should automatically connect to another instance

– Load balancing : Workload should spread across various instances to obtain maximum performance/throughput

Failover can be

– Connect Time Connection Failover (CTCF) while making initial connection ()

- Run time connection failover (RTCF) after connection has been established or (transparent application failover (TAF)

FAILOVER_METHOD = NONE, BASIC, PRECONNECT

FAILOVER TYPE = SESSION, SELECT

Load balancing can be :

– Connect time load balancing (CTLB)

. On client side

. On server side

– Run time load balancing (RTLB)

TO configure failover/load balancing, tnsnames.ora should contain multiple listener addresses to connect to multiple instances e.g.

Multiple Listener addresses within a description: i.e. User is trying to connect to a service which is supported by multiple instances.

RAC=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS= (PROTOCOL=TCP) (HOST=node1-vip) (PORT=1521))

(ADDRESS= (PROTOCOL=TCP) (HOST=node2-vip) (PORT=1521))

(ADDRESS= (PROTOCOL=TCP) (HOST=node3-vip) (PORT=1521))

(ADDRESS= (PROTOCOL=TCP) (HOST=node4-vip) (PORT=1521))

)

(CONNECT_DATA= (SERVICE_NAME= RAC))

)

In case SCAN is used, SCAN name is used in the address which resolves to 3 SCAN listeners.

e.g.

RAC=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS= (PROTOCOL=TCP) (HOST=cluster01-scan) (PORT=1521))

)

(CONNECT_DATA= (SERVICE_NAME= RAC))

)

In this post, I will discuss in detail about Load Balancing. To know more about failover , please click here.

LOAD BALANCING

————–

Load balancing in RAC implies distributing the workload over multiple instances accessing the same physical database. Two kinds load balancing can be configured

– Connect time load balancing (CTLB)

. On client side

. On server side

– On server side – Run time load balancing (RTLB)

Connect time load balancing (CTLB) : This enables user to connect to one of the instances supporting the service. The connection stays with the same instance until the user disconnects orthe session is killed. It can be configured on the client side and/or server side.

- Connect time load balancing on client side: When a user session attempts to connect to the database, Oracle Net chooses an address specified in tnsnames.ora to connect to in a random order rather than sequential order. This has the effect of clients connecting through addresses which are picked up at random and no one address is overloaded. Its configuration is quite simple. You just need to set the parameter LOAD_BALANCE=ON in the client connection definition in tnsnames.ora. For example :

RAC=

(DESCRIPTION=

(ADDRESS_LIST=

(LOAD_BALANCE=ON)

(ADDRESS= (PROTOCOL=TCP) (HOST=node1-vip) (PORT=1521))

(ADDRESS= (PROTOCOL=TCP) (HOST=node2-vip) (PORT=1521))

(ADDRESS= (PROTOCOL=TCP) (HOST=node3-vip) (PORT=1521))

(ADDRESS= (PROTOCOL=TCP) (HOST=node4-vip) (PORT=1521))

)

(CONNECT_DATA= (SERVICE_NAME= RAC)

)

LOAD_BALANCE parameter is set to ON by default. When this parameter is set to ON, Oracle Net Services progresses through the list of listener addresses in a random sequence, balancing the load on several listeners. When it is set to OFF, the addresses are tried out sequentially until one succeeds.

When using SCAN in the connection definition, Oracle database randomly connects to one of the available SCAN listeners in a round robin fashion and balances the connections on the three scan listeners. Here is a sample tnsnames.ora for a RAC database using SCAN.

RAC=

(DESCRIPTION=

(LOAD_BALANCE=ON)

(ADDRESS= (PROTOCOL=TCP) (HOST=SCAN-HOSTNAME) (PORT=1521))

)

(CONNECT_DATA= (SERVICE_NAME= RAC)

)

Limitation of connect time load balancing : The connection stays with the same instance for the life of a session. If connection lasts a long time, it might be possible that load of current instance increases and some other less loaded instance might be preferable. In that case we would like the connection to switch the other more appropriate instance. This can be achieved by using Run time Load Balancing (RTLB).

- Connect time load balancing on server side: After a listener receives the connection request, it can forward the request to another instance based on the connect time load balancing goal (CLB_GOAL)specified for the service. CLB_GOAL can be :

LONG(Default) - used for application connections that are connected for a long period such as third party connection pools and SQL*Forms applications. In this case,

. the listener will load balance on number of sessions

. Run time load balancing goal will not be used in this case

SHORT - used for application connections that are short in duration. This should be used with connection pools integrated with the load balancing advisory. In this case, listener uses Load Balancing Advisory (LBA) to make the connection based on CPU utilization on the node.

Limitation of Connect time load balancing on client side : The listener has no idea if the session has been assigned to an endpoint whose corresponding database server is already overloaded. Hence, timeouts can occur if the node is heavily loaded and unable to respond quickly.Hence, to overcome this problem, server side connect time load balancing needs to be configured. It is useful to spread initial connection load among all listeners inthe cluster. Client may then be redirected based on server side load balancing.

Run Time (Server side) load Balancing (RTLB): In this case, the listener routes incoming client connections according to policies and based on the current service level provided by the database instances. The listener determines the connection distribution depending upon profile statistics that are dynamically updated by PMON. The higher the load on the node, the more frequently PMON updates the load profile.Thus connections may be switched depending upon changes in cluster configuration, application wrokload overworked nodes or hangs.

The core of server side laod balancing id Dynamic service registration so that a services are registered with all the listeners. Since PMON on each node sends load profile to all the listeners with which the service is registered, all the listeners come to know about load profile of all the instances and hence the connection is forwarded to the most appropriate listener depending upon the goal of the run time load balancing.

Run time load balancing is achieved using connection pools. Work requests are automatically balanced across the pool of connections.The connection allocation is based on the current performance level provided by the database instances as indicated by the LBA FAN events. This provides load balancing at the transaction level instead of load balancing at the time of initial connection.

With server-side load balancing, the listener directs a connection request to the best instance currently providing the service by using the load balancing advisory.

Load Balancing Advisory

- is an advisory for balancing work across RAC instances

- Monitors workload activity for a service across all instances in the cluster

- Analyzes the service level for each instance based on defined metric goal

Metric: service time (GOAL_SERVICE_TIME)

Metric: throughput (GOAL_THROUGHPUT)

- Publishes FAN events recommending amount of work to be sent to each instance and data quality flag

- Default is Off.

- Directs work to where services are executing well and resources are available

- Adjusts distribution for different power nodes, different priority and shape workloads, changing demand

- Stops sending work to slow, hung, failed nodes early

How to configure server side load balancing:

1. set parameters LOCAL_LISTENER and REMOTE_LISTENER

2. set CLB_GOAL = SHORT for the service

3. set RTLB_GOAL for the service

1. set parameters LOCAL_LISTENER and REMOTE_LISTENER

LOCAL_LISTENER parameter should be set to name of the listener defined in the same node

REMOTE_LISTENER should be set to names of the listeners running on other nodes

For example in a 3 node setup

Host01 running instance orcl1

Host02 running instance orcl2

Host03 running instance orcl3

For host01,

local_listener – (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) HOST=host01-vip)(PORT=1521))))

remote_listener – (DESCRIPTION=(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=TCP) HOST=host02-vip)(PORT=1521)

(ADDRESS=(PROTOCOL=TCP) HOST=host03-vip)(PORT=1521))))

For host02,

local_listener – (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) HOST=host02-vip)(PORT=1521))))

remote_listener – (DESCRIPTION=(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=TCP) HOST=host01-vip)(PORT=1521)

(ADDRESS=(PROTOCOL=TCP) HOST=host03-vip)(PORT=1521))))

For host03,

local_listener – (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) HOST=host03-vip)(PORT=1521))))

remote_listener – (DESCRIPTION=(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=TCP) HOST=host02-vip)(PORT=1521)

(ADDRESS=(PROTOCOL=TCP) HOST=host01-vip)(PORT=1521))))

When we start the three instances the corresponding PMON processes get dynamically registered with all the listeners and start feeding listeners with load profile information. Now all the listeners come to know about load profile of all the instances and hence the connection is forwarded to the listener of the least loaded node.

When SCAN is used, remote_listener parameter should be set to SCAN name on all the nodes i.e.

remote_listener – myrac-cluster-scan:1521

So that PMON process of each instance registers the database services with the default listener on the local node and with each SCAN listener, which is specified by the REMOTE_LISTENER database parameter.

2. set CLB_GOAL = SHORT for the service

EXECUTE DBMS_SERVICE.MODIFY_SERVICE

(service_name => ‘sjob’ -

, clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT);

srvctl modify service

-s orcl_serv database -d orcl

-j SHORT // connection load balancing goal {long|short}

3. set RTLB_GOAL for the service

A request for a connection is serviced by selecting a connection based on the service goal as determined by the Load Balancing Advisory. The service goal determines whether the connection provides best service quality, that is, how efficiently a single transaction completes, or best throughput, that is, how efficiently an entire job or long-running query completes.

For RTLB, we can define service level goal which will be used only if CLB_GOAL= SHORT

There are 3 options available

NONE – Default setting, you are not taking advantage of this feature

THROUGHPUT – Work requests are directed based on throughput. THROUGHPUT should be used when the work in a service completes at homogenous rates. An example is a trading system where work requests are similar lengths. Attempts to direct work requests according to throughput. The load balancing advisory analyzes the service level for each instance based on the service time and is based on the rate that work is completed in the service plus available bandwidth to the service. For example for the use of THROUGHPUT is for workloads such as batch processes,trading system work requests have similar lengths and next job starts when the last job completes:

EXECUTE DBMS_SERVICE.MODIFY_SERVICE

(service_name => ‘sjob’ -

, goal => DBMS_SERVICE.GOAL_THROUGHPUT );

srvctl modify service

-s orcl_serv database -d orcl

-B throughput // runtime connection load balancing goal { service_time|throughput | none}

SERVICE_TIME – Work requests are directed based on response time. SERVICE_TIME should be used when the work in a service completes at various rates. In this case, Load balancing advisory data is based on elapsed time for work done in the service plus available bandwidth to the service. An example for the use of SERVICE_TIME is for workloads such as internet shopping where the rate of demand changes and work requests are of differing various lengths.:

EXECUTE DBMS_SERVICE.MODIFY_SERVICE

(service_name => ‘OE’ -

, goal => DBMS_SERVICE.GOAL_SERVICE_TIME -

);

srvctl modify service

-s orcl_serv database -d orcl

-B service_time // runtime connection load balancing goal { service_time|throughput | none}

You can see the goal settings for a service in the data dictionary and in the DBA_SERVICES, V$SERVICES, and V$ACTIVE_SERVICES views.

SUMMARY:

Workload management involves :

– Failover : If connection to an instance fails, client should automatically connect to another instance

– Load balancing : Workload should spread across various instances to obtain maximum performance/throughput

Load balancing can be :

– Connect time load balancing (CTLB)

. On client side

. On server side

– Run time load balancing (RTLB)

TO configure failover/load balancing, tnsnames.ora should contain multiple listener addresses to connect to multiple instances

- Connect time load balancing on client side: When a user session attempts to connect to the database, Oracle Net chooses an address specified in tnsnames.ora to connect to in a random order rather than sequential order. This has the effect of clients connecting through addresses which are picked up at random and no one address is overloaded. Its configuration is quite simple. You just need to set the parameter LOAD_BALANCE=ON in the client connection definition in tnsnames.ora.

Limitation of Connect time load balancing (CTLB) on client side : The listener has no idea if the session has been assigned to an endpoint whose corresponding database server is already overloaded.

- Connect time load balancing (CTLB) on server side: After a listener receives the connection request, it can forward the request to another instance based on the connect time load balancing goal (CLB_GOAL)specified for the service. CLB_GOAL can be : LONG or SHORT

LONG(Default) – In this case,

. the listener will load balance on number of sessions

. Run time load balancing goal will not be used in this case

Run Time (Server side) load Balancing (RTLB): In this case, the listener routes incoming client connections according to policies and based on the current service level provided by the database instances. With server-side load balancing, the listener directs a connection request to the best instance currently providing the service by using the load balancing advisory.

To configure server side load balancing:

1. set parameters LOCAL_LISTENER and REMOTE_LISTENER

2. set CLB_GOAL = SHORT for the service

3. set RTLB_GOAL for the service

references: http://docs.oracle.com/cd/B28359_01/java.111/b31224/rlb.htm

Using RMAN Incremental Backups to Refresh Standby Database:

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

In this post , I will demonstrate synchronization of a standby DB from an incremental RMAN backup. It might be needed in following scenarios:

1-The standby database is considerably lagging behind the primary . The copying and applying of archive logs from primary will be time consuming as it will apply both the COMMITED and the NON COMMITED transactions then will ROLLBACK the non committed transactions. Incremental backup will recover the standby database much faster than applying the archives as it will apply only the COMMITED transactions on the standby database .

2-Some archivelogs on the primary which have not been applied to the standby have been lost. In such cases, you can create an incremental backup of the primary database containing changes since the standby database was last refreshed. This incremental backup can be applied to the standby database to synchronize it with a primary database.

Overview:

Stop redo transport on primary
Switch logs on primary – results in some archived logs on primary which have not been sent to standby
Rename newly generated archived logs on primary to simulate their loss
Restart redo transport – gives error as gap cannot be resolved due to missing logs
Create a control file for standby database on primary
Take incremental backup on primary starting from the SCN# of standby database
Copy the incremental backup to the standby host and catalog it with RMAN
Mount the standby database with newly created standby control file
Cancel managed recovery of standby database and apply incremental backup to the standby database
Start managed recovery of standby database

Implementation:
– Check current log sequence on primary

PRI>archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     40
Next log sequence to archive   42
Current log sequence           42

- check that all the archived logs prior to the current log have been sent to standby

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
41

– stop redo transport from primary (dg01)

DGMGRL> show database dg01

Database - dg01

Role:            PRIMARY
Intended State:  TRANSPORT-ON
Instance(s):
dg01

Database Status:
SUCCESS

DGMGRL> edit database dg01 set state='Transport-off';
Succeeded.

DGMGRL> show database dg01

Database - dg01

Role:            PRIMARY
Intended State:  TRANSPORT-OFF
Instance(s):
dg01

Database Status:
SUCCESS

– switch log on primary

PRI>alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

archive log list;

Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     44
Next log sequence to archive   45
Current log sequence           46

– check that logs after sequence# 42 are not being transported to sby

SBY> select max(sequence#) from v$archived_log
MAX(SEQUENCE#)
--------------
42

– Find out names of archived logs generated on primary which have not been transported to standby

PRI>set line 500
col name for a40
select sequence#, name from v$archived_log where sequence# > 42;

SEQUENCE# NAME
---------- ----------------------------------------
43 /u01/app/oracle/flash_recovery_area/DG01
/archivelog/2013_11_02/o1_mf_1_43_9780s1
ch_.arc

44 /u01/app/oracle/flash_recovery_area/DG01
/archivelog/2013_11_02/o1_mf_1_44_9780tl
t0_.arc

45 /u01/app/oracle/flash_recovery_area/DG01
/archivelog/2013_11_02/o1_mf_1_45_9780tl
vo_.arc

– To simulate loss of archived logs on primary, rename them

PRI>ho mv /u01/app/oracle/flash_recovery_area/DG01/archivelog/2013_11_02/o1_mf_1_43_9780s1ch_.arc  /home/oracle/arch_43.arc

ho mv /u01/app/oracle/flash_recovery_area/DG01/archivelog/2013_11_02/o1_mf_1_44_9780tlt0_.arc /home/oracle/arch_44.arc

ho mv /u01/app/oracle/flash_recovery_area/DG01/archivelog/2013_11_02/o1_mf_1_45_9780tlvo_.arc /home/oracle/arch_45.arc

– Restart redo transport

DGMGRL>  edit database dg01 set state='Transport-on';

– check that gap in redo logs on standby cannot be resolved as some logs are missing on primary

DGMGRL> show database dg01

Database - dg01

Role:            PRIMARY
Intended State:  TRANSPORT-ON
Instance(s):
dg01

Database Error(s):
 ORA-16783: cannot resolve gap for database dg02

Database Status:
ERROR

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
dg01 - Primary database
Error: ORA-16724: cannot resolve gap for one or more standby databases

dg02 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

– check current scn# of standby

SBY>select current_scn from v$database;

CURRENT_SCN
-----------
998647

– Create a standby control file:

PRI>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/standby_control.ctl';

– copy the standby control file to standby host

PRI> !scp /home/oracle/standby_control.ctl node2:/home/oracle/standby_control.ctl

– Take incremental backup on primary starting from scn# of standby database
RMAN uses the selected SCN as the basis for this incremental backup. For all files being backed up, RMAN includes all data blocks that were changed at SCNs greater than or equal to the FROM SCN in the incremental backup.
Note:
• RMAN does not consider the incremental backup as part of a backup strategy at the source database. The backup is not suitable for use in a normal RECOVER DATABASE operation at the source database.

RMAN> Backup incremental from SCN 987005 database tag='FOR_STANDBY' format '/home/oracle/%d_%t_%s_%p';

Starting backup at 02-NOV-13

channel ORA_DISK_1: starting full datafile backup set
...
channel ORA_DISK_1: starting piece 1 at 02-NOV-13
channel ORA_DISK_1: finished piece 1 at 02-NOV-13
piece handle=/home/oracle/DG01_830395300_22_1 tag=FOR_STANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

– Move the backup pieces to the standby host

PRI> !scp /home/oracle/DG01_830395300_22_1 node2:/home/oracle/DG01_830395300_22_1

– Catalog the Incremental Backup Files at the Standby Database

RMAN>  catalog backuppiece  '/home/oracle/DG01_830395300_22_1';

list backup tag FOR_STANDBY;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9       Incr    9.38M      DISK        00:00:00     02-NOV-13
BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: FOR_STANDBY
Piece Name: /home/oracle/DG01_830395300_22_1
Keep: NOLOGS             Until: 09-NOV-13

List of Datafiles in backup set 9

– Find out names of current control files

SQL>  col value for a50
col name for a15
select name, value   from v$parameter where upper(name)= 'CONTROL_FILES';

NAME            VALUE
--------------- --------------------------------------------------
control_files   /u01/app/oracle/oradata/dg02/control01.ctl, /u01/a
pp/oracle/flash_recovery_area/dg02/control02.ctl

- Shutdown the standby database and rename the original control file of the standby database:

SBY> Shu immediate;
!mv /u01/app/oracle/oradata/dg02/control01.ctl /u01/app/oracle/oradata/dg02/control01.bak
!mv /u01/app/oracle/flash_recovery_area/dg02/control02.ctl /u01/app/oracle/flash_recovery_area/dg02/control02.bak

– Restore the standby control file we just copied from the primary

SBY>    ! cp /home/oracle/standby_control.ctl /u01/app/oracle/oradata/dg02/control01.ctl
! cp /home/oracle/standby_control.ctl /u01/app/oracle/flash_recovery_area/dg02/control02.ctl

– Startup the Standby database with the new controlfile:

SBY> startup mount;

– Apply the Incremental Backup to the Standby Database
Use the RMAN RECOVER command with the NOREDO option to apply the incremental backup to the standby database. All changed blocks captured in the incremental backup are updated at the standby database, bringing it up to date with the primary database.

SBY> recover managed standby database cancel;
RMAN> RECOVER DATABASE from tag for_standby NOREDO;

Starting recover at 02-NOV-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/dg02/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/dg02/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/dg02/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/dg02/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/dg02/example01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/DG01_830395300_22_1
channel ORA_DISK_1: piece handle=/home/oracle/DG01_830395300_22_1 tag=FOR_STANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished recover at 02-NOV-13

Now your standby database is refreshed from the incremental backup.
You can start the Managed Recovery process on the standby DB:

SBY>recover managed standby database disconnect;
Media recovery complete.

You can now resume managed recovery at the standby. Any redo logs required at the standby with changes since those contained in the incremental are automatically requested from the primary and applied.
— check that SCN# of the standby database has advanced.

SBY>select current_scn from v$database;

CURRENT_SCN
-----------
1005729

DGMGRL> edit database dg01 set state='transport-on';
DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
dg01 - Primary database
dg02 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Note:
This technique cannot be used to update a duplicate database.