set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
j.session_recid, j.session_stamp,
to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
(j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
3, 'Tuesday', 4, 'Wednesday',
5, 'Thursday', 6, 'Friday',
7, 'Saturday') dow,
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l,
ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
left outer join (select
d.session_recid, d.session_stamp,
sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
sum(case when d.controlfile_included = 'NO'
and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
from
V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where s.input_file_scan_only = 'NO'
group by d.session_recid, d.session_stamp) x
on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
from GV$RMAN_OUTPUT o
group by o.session_recid, o.session_stamp)
ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;
===================================
set linesize 126
column Pct_Complete format 99.99
column client_info format a25
column sid format 999
column MB_PER_S format 999.99
select s.client_info,
l.sid,
l.serial#,
l.sofar,
l.totalwork,
round (l.sofar / l.totalwork*100,2) "Pct_Complete",
aio.MB_PER_S,
aio.LONG_WAIT_PCT
from v$session_longops l,
v$session s,
(select sid,
serial,
100* sum (long_waits) / sum (io_count) as "LONG_WAIT_PCT",
sum (effective_bytes_per_second)/1024/1024 as "MB_PER_S"
from v$backup_async_io
group by sid, serial) aio
where aio.sid = s.sid
and aio.serial = s.serial#
and l.opname like 'RMAN%'
and l.opname not like '%aggregate%'
and l.totalwork != 0
and l.sofar <> l.totalwork
and s.sid = l.sid
and s.serial# = l.serial#
order by 1;
=====================================
Courtesy:
http://oracleinaction.com/tune-rman-ii/
http://hardworkingdba.blogspot.co.uk/2011/04/how-to-measure-throughput-of-rman.html --> INteresting
allocate channel c1 device type disk maxopenfiles 1; --> Max open files defines the no. of files it can read simultaneously. Default value=8.
This also defines the multiplexing. Above multiplexing = 1. By default, it's 8
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.
Backup runs faster in multiplexing, however restore takes time as it has to read a single file from intermigled files.
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.
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
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
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.
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’;
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
j.session_recid, j.session_stamp,
to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
(j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
3, 'Tuesday', 4, 'Wednesday',
5, 'Thursday', 6, 'Friday',
7, 'Saturday') dow,
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l,
ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
left outer join (select
d.session_recid, d.session_stamp,
sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
sum(case when d.controlfile_included = 'NO'
and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
from
V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where s.input_file_scan_only = 'NO'
group by d.session_recid, d.session_stamp) x
on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
from GV$RMAN_OUTPUT o
group by o.session_recid, o.session_stamp)
ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;
===================================
set linesize 126
column Pct_Complete format 99.99
column client_info format a25
column sid format 999
column MB_PER_S format 999.99
select s.client_info,
l.sid,
l.serial#,
l.sofar,
l.totalwork,
round (l.sofar / l.totalwork*100,2) "Pct_Complete",
aio.MB_PER_S,
aio.LONG_WAIT_PCT
from v$session_longops l,
v$session s,
(select sid,
serial,
100* sum (long_waits) / sum (io_count) as "LONG_WAIT_PCT",
sum (effective_bytes_per_second)/1024/1024 as "MB_PER_S"
from v$backup_async_io
group by sid, serial) aio
where aio.sid = s.sid
and aio.serial = s.serial#
and l.opname like 'RMAN%'
and l.opname not like '%aggregate%'
and l.totalwork != 0
and l.sofar <> l.totalwork
and s.sid = l.sid
and s.serial# = l.serial#
order by 1;
=====================================
Courtesy:
http://oracleinaction.com/tune-rman-ii/
http://hardworkingdba.blogspot.co.uk/2011/04/how-to-measure-throughput-of-rman.html --> INteresting
allocate channel c1 device type disk maxopenfiles 1; --> Max open files defines the no. of files it can read simultaneously. Default value=8.
This also defines the multiplexing. Above multiplexing = 1. By default, it's 8
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.
Backup runs faster in multiplexing, however restore takes time as it has to read a single file from intermigled files.
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.
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
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
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.
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’;
No comments:
Post a Comment