RMAN TUNING

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.

In continuation with my earlier post Tuning RMAN Part-I,, in this post, I will demonstrate the usage of asynchronous i/o to improve the performance of RMAN operations.

RMAN can perform i/o in two ways : Synchronous and Asynchr nous.

We are aware that synchronous i/o is a bottleneck and we should always aim at having asynchronous i/o for better performance. Some platforms support asynchronous by default while others don’t. On the platforms which do not support asynchronous i/o, we can simulate asynch i/o by setting parameter dbwr_io_slaves.

If i/o is performed asynchronously, we will have records in v$backup_async_io

If i/o is performed synchronously, we will have records in v$backup_sync_io

 Let’s have a demonstration :

The parameter disk_asynch_io decides whether asynchronous I/O is enabled at O/S level. By default its value is true if OS supported asynch i/o.

Currently my database is runing on windows server 2008 and it supports asynchronous i/o.

Let’s verify by looking at the default value of  disk_asynch_io parameter.

SYS>sho parameter disk_asynch_io;NAME
TYPE        VALUE

———————————————– ———–

disk_asynch_io     boolean     TRUE

When asynchronous i/o is performed at the O/S level, the buffers needed by RMAN are allocated from PGA. Let’s verify this by checking the session pga memory as a backup progresses.

– Take backup using multiplexing level = 4 – this needs to allocate 16m buffers from pga

–

– While backup is going on , issue the next query repeatedly which monitors pga usage by rman session. Note that pga consumption increases as backup progresses.

rman>RUN
 {
     allocate channel c1 device type disk;
   backup datafile 1,2,3,4 filesperset 4 ;
 }

– Query the pga memory usage by rman  session .

– Note that pga consumption increases as backup progresses.

– ALso note that size of buffers allocated = 18 MB (41-23) which is slightly > 16 MB

SQL> col name for a30
     set line 500
     select s.sid, n.name , s.value/1024/1024 session_pga_mb
      from  v$statname n, v$sesstat s
      where s.sid = (select sess.SID
                                  FROM V$PROCESS p, V$SESSION sess
                                 WHERE p.ADDR = sess.PADDR
                                       AND CLIENT_INFO LIKE ‘%rman%’)
        and n.name = ‘session pga memory’
        and s.statistic# = n.statistic#;

SID NAME    SESSION_PGA_MB

—————————————- ————–

14 session pga memory     23.1208076

SQL> /

SID NAME  SESSION_PGA_MB

—————————————- ————–

14 session pga memory      41.1833076

  If OS does not support asynchronous I/O, we can simulate by setting parameter dbwr_io_slaves to a non zero value.

4 slave processes will be allocated irrespective of the value of the parameter dbwr_io_Slaves. IN this case, buffers for RMAN will be allocated from large pool. 

If large pool is sized to a value lower than the size of the buffers required, RMAN will switch to synchronous I/O and write a message to the alert log.

Let’s verify this.

– Check current values of sga_target/memory_target which will be used later to return to the original state.

- Note the values

SQL>sho parameter sga_target
          sho parameter memory_target 

– Disable AMM/ASMM

– Disable asynchronous I/O at OS level –

– SImulate async IO by configuring slave processes –

– Size large pool to a size smaller than 16M (say 8M)

SQL>alter system set “_memory_imm_mode_without_autosga”=false scope=both;

            alter system set memory_target = 0;

           alter system set sga_target = 0;

alter system set disk_asynch_io=false scope=spfile;

alter system set dbwr_io_slaves=4 scope=spfile;

alter system set large_pool_size=8m scope = spfile;

startup force;

– Check that the parameters have been set to the specified values

               

SQL> sho parameter sga_targetsho parameter memory_target
sho parameter disk_asynch_io
sho parameter dbwr_io_slaves
sho parameter large_pool_size

NAME TYPE        VALUE

———————————————– ———–

sga_target big integer 0

memory_target big integer 0

disk_asynch_io boolean     FALSE

dbwr_io_slaves integer     4

large_pool_size big integer 8M

– Take backup using multiplexing level = 4

– this needs to allocate 16m buffers from large pool –

rman>backup datafile 2,3,4,5 filesperset 4;

– Check that i/o is not asynchronous –

SQL>select filename, buffer_size, buffer_count, type, status from v$backup_async_io
where status=’IN PROGRESS';

no rows selected

– check that synchronous i/o is taking place although async i/o was simulated –

SQL> col filename for a30 word_wrapped

               select FILENAME, BUFFER_SIZE, BUFFER_COUNT, TYPE,
STATUS from v$backup_sync_io

where status = ‘IN PROGRESS';

FILENAME BUFFER_SIZE BUFFER_COUNT TYPE      STATUS

—————————————– ———— ——— ———–

C:\APP\ADMINISTRATOR\ORADATA\O   1048576            4    INPUT     IN PROGRESS

RCL\SYSAUX01.DBF

C:\APP\ADMINISTRATOR\ORADATA\O  1048576            4  INPUT     IN PROGRESS

RCL\UNDOTBS01.DBF

C:\APP\ADMINISTRATOR\ORADATA\O  1048576            4  INPUT     IN PROGRESS

RCL\EXAMPLE01.DBF

C:\APP\ADMINISTRATOR\ORADATA\O  1048576            4  INPUT     IN PROGRESS

RCL\USERS01.DBF

– check that alert log indicates that sync i/o will be used –

                                                

ORA-04031: unable to allocate 1056812 bytes of shared memory (“large pool”,”unknown

object”,”large  pool”,”KSFQ Buffers”)

ksfqxcre: failure to allocate shared memory means sync I/O will be used

Now let us increase the size of large pool to a value > 16M (say 20M) and the issue the

same backup. This time asynchronous i/o should be used. we will verify this by checking

that records appear in  v$backup_async_io

SQL>alter system set large_pool_size=20m scope=spfile;

startup force;

sho parameter large_pool_size;

NAME   TYPE        VALUE

———————————————– ————-

large_pool_size  big integer 20M

– Take backup using  multiplexing level = 4

- this needs to allocate 16m buffers from large   pool –

rman>backup datafile 2,3,4,5 filesperset 4;

– Check that i/o is not asynchronous –

SQL> select filename, buffer_size, buffer_count, type, status from v$backup_async_io

              where status=’IN PROGRESS';

FILENAME  BUFFER_SIZE BUFFER_COUNT TYPE      STATUS

—————————————– ———— ——— ———–

C:\APP\ADMINISTRATOR\ORADATA\O  1048576            4  INPUT     IN PROGRESS

RCL\SYSAUX01.DBF

C:\APP\ADMINISTRATOR\ORADATA\O  1048576            4  INPUT     IN PROGRESS

RCL\UNDOTBS01.DBF

C:\APP\ADMINISTRATOR\FLASH_REC  1048576            4  OUTPUT    IN PROGRESS

OVERY_AREA\ORCL\BACKUPSET\2012

_09_14\O1_MF_NNNDF_TAG20120914

T100407_855DY50Z_.BKP

Conclusion:

- The parameter disk_asynch_io decides whether asynchronous I/O is enabled at O/S level. By default its value is true if OS supported asynch i/o.

- When asynchronous i/o is performed at the O/S level, the buffers needed by RMAN are allocated from PGA.

- If OS does not support asynchronous I/O, we can simulate it by setting parameter dbwr_io_slaves to a non zero value. 4 slave processes will be allocated irrespective of the value of the parameter dbwr_io_Slaves.

- In the case  of simulated asynchronous i/o, , buffers for RMAN will be allocated from large pool. 

  If large pool is sized to a value more than the required buffers, asynchronous i/o will be performed.

  If large pool is sized to a value lower than the size of the buffers required, RMAN will switch to synchronous I/O and write a message to the alert log.

-  The no. of buffers allocated by RMAN 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 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

- If asynchronous i/o is supported by the O/s, we should size PGA to a value more than the size of the buffers required by RMAN.

- If asynchronous i/o is not supported by O/S and we are simulating it, we should size large pool to a value more than the size of the buffers required by RMAN.

— CLEANUP —

SQL>drop tablespace rman1 including contents and datafiles;

    drop tablespace rman2 including contents and datafiles;

    drop tablespace rman3 including contents and datafiles;

    drop tablespace rman4 including contents and datafiles;

    alter system set disk_asynch_io=true scope=spfile;

    alter system set memory_target = <> scope=spfile;

    alter system set sga_target = <> scope=spfile;

    startup force;