During Restore we normally have to see progress for big database . Posting script below used by me to check progress .
SELECT sid, serial#, context, sofar, totalwork,
round(sofar/totalwork*100,2) "% Complete"
FROM v$session_longops
WHERE opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND totalwork != 0
AND sofar != totalwork;
select device_type "Device", type, filename, to_char(open_time, 'mm/dd/yyyy hh24:mi:ss') open,
to_char(close_time,'mm/dd/yyyy hh24:mi:ss') close,elapsed_time ET, effective_bytes_per_second EPS
from v$backup_async_io;
TTITLE LEFT '% Completed. Aggregate is the overall progress:'
SET LINE 132
SELECT opname, round(sofar/totalwork*100) "% Complete"
FROM gv$session_longops
WHERE opname LIKE 'RMAN%'
AND totalwork != 0
AND sofar <> totalwork
ORDER BY 1;
TTITLE LEFT 'Channels waiting:'
COL client_info FORMAT A15 TRUNC
COL event FORMAT A20 TRUNC
COL state FORMAT A7
COL wait FORMAT 999.90 HEAD "Min waiting"
SELECT s.sid, p.spid, s.client_info, status, event, state, seconds_in_wait/60 wait
FROM gv$process p, gv$session s
WHERE p.addr = s.paddr
AND client_info LIKE 'rman%';
TTITLE LEFT 'Files currently being written to:'
COL filename FORMAT a50
SELECT filename, bytes, io_count
FROM v$backup_async_io
WHERE status='IN PROGRESS'
/
TTITLE OFF
SET HEAD OFF
SELECT 'Throughput: '||
ROUND(SUM(v.value/1024/1024),1) || ' Meg so far @ ' ||
ROUND(SUM(v.value /1024/1024)/NVL((SELECT MIN(elapsed_seconds)
FROM v$session_longops
WHERE opname LIKE 'RMAN: aggregate input'
AND sofar != TOTALWORK
AND elapsed_seconds IS NOT NULL
),SUM(v.value /1024/1024)),2) || ' Meg/sec'
FROM gv$sesstat v, v$statname n, gv$session s
WHERE v.statistic# = n.statistic#
AND n.name = 'physical write total bytes'
AND v.sid = s.sid
AND v.inst_id = s.inst_id
AND s.program LIKE 'rman@%'
GROUP BY n.name
/
SET HEAD ON
##### To check Restore Speed
TTITLE OFF
SET HEAD OFF
SELECT 'Throughput: '||
ROUND(SUM(v.value/1024/1024/1024),1) || ' Gig so far @ ' ||
ROUND(SUM(v.value /1024/1024)/NVL((SELECT MIN(elapsed_seconds)
FROM v$session_longops
WHERE opname LIKE 'RMAN: aggregate input'
AND sofar != TOTALWORK
AND elapsed_seconds IS NOT NULL
),SUM(v.value /1024/1024)),2) || ' Meg/sec'
FROM gv$sesstat v, v$statname n, gv$session s
WHERE v.statistic# = n.statistic#
AND n.name = 'physical write total bytes'
AND v.sid = s.sid
AND v.inst_id = s.inst_id
AND s.program LIKE 'rman@%'
GROUP BY n.name
/
Tracing Rman :
For analyzing any issues we can debug rman
$ rman target / catalog rman/rman debug trace trace.log
Speeding Up Rman :
RMAN Multiplexing
RMAN uses two different types of buffers for I/O: disk and tape.RMAN multiplexing determineshow RMAN allocates disk buffers.
RMAN multiplexing is the number of files in a backup readsimultaneously and then written to the same backup piece. The degree of multiplexing depends onthe
FILESPERSET parameter of the BACKUP command as well as the MAXOPENFILES parameterof the CONFIGURECHANNEL command or ALLOCATECHANNELcommand.
Note: RMANmultiplexing is set at the channel level
Allocating Tape Buffers
•From SGA (large pool) with BACKUP_TAPE_IO_SLAVES is TRUE
.•From PGA with BACKUP_TAPE_IO_SLAVES is FALSE
RMAN allocates the tape buffers in the System Global Area (SGA) or the Program Global Area(PGA), depending on whether I/O slaves are used.
If the BACKUP_TAPE_IO_SLAVES initialization parameter is set to TRUE , RMAN allocates tape buffers from the shared pool or the large pool if the LARGE_POOL_SIZE
initialization parameter is set.
If you set the parameter to FALSE , RMAN allocates the buffers from the PGA.
If you use I/O slaves, set the LARGE_POOL_SIZE initialization parameter to set aside SGA memory that is dedicated to holding these large memory allocations. By doing this, the RMAN I/O buffers do not compete with thelibrary cache for shared pool memory.
Oracle recommends that you set the BACKUP_TAPE_IO_SLAVES initialization parameter to TRUE
In most circumstances, this will provide the best performance of backups to tape. Also, thissetting is required in order to perform duplexed backups. Duplexed backups are covered in the lessontitled “Using RMAN to Create Backups.”
Comparing Synchronous and Asynchronous I/O
When RMAN reads or writes data, the I/O is either synchronous orasynchronous. When the I/O is synchronous, a server process can perform only one task at a time. When it is asynchronous, a server process can begin an I/O and then perform other tasks while waiting for the I/O to complete. It can also begin multiple I/O operations before waiting for the first to complete.You can set initialization parameters that determine the type of I/O.
If you set BACKUP_TAPE_IO_SLAVES to TRUE , the tape I/O is asynchronous. Otherwise, the I/O is synchronous
Tuning the BACKUP Command
The MAXPIECESIZE parameter specifies the maximum size of each backup piece created on thechannel.
The FILESPERSET parameter specifies the maximum number of files to place in a backup set. If you allocate only one channel, then you can use this parameter to make RMAN create multiple backup sets. For example, if you have 50 input data files and two channels, you can set FILESPERSET=5 to create 10 backup sets. This strategy can prevent you from splitting a backupset among multiple tapes.
The MAXOPENFILES parameter setting depends on your disk subsystem characteristics. If you useASM, then set it to 1 or 2. Otherwise, if your data is not striped, then you may want to set this higher.To gain performance, increase either the number of files per backup set, or this parameter. If you arenot using ASM or striping of any kind, then try increasing MAXOPENFILES
.
Channel Tuning
If you configure multiple channels for an SBT device, then you can specifically spread data filesacross those channels.
Here is an example:
RUN
{ ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
ALLOCATE CHANNEL c2 DEVICE TYPE sbt;
ALLOCATE CHANNEL c3 DEVICE TYPE sbt;
BACKUP (DATAFILE 1,2,5 CHANNEL c1) (DATAFILE 4,6 CHANNEL c2) (DATAFILE 3,7,8 CHANNEL c3);BACKUP DATABASE NOT BACKED UP;}
Monitor rman restore throughput:
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;
########## Shell Script to Check backup or Restore Status #############
After setting db environment run below shell
#!/bin/bash
#Set the environment
_env(){
RESTORE_PROGRESS=/tmp/restore_progress.log
touch dfsize
export SIZELOG=dfsize
}
#RMAN restore progress monitor in percentage from database
_restore_pct(){
while sleep 0.5;do
date_is=$(date "+%F-%H-%M-%S")
#ela_s=$(date +%s)
#echo "============================================================"+
#echo " ----->$ORACLE_SID<----- |"|tr 'a-z' 'A-Z';echo " Restore progress ($date_is) |"
#echo "============================================================"+
$ORACLE_HOME/bin/sqlplus -S "/ as sysdba" << EOF > dbsz.txt
set feedback off
set lines 200
set pages 1000
set termout off
col INPUT_BYTES/1024/1024 format 9999999
col OUTPUT_BYTES/1024/1024 format 9999999
col OBJECT_TYPE format a10
set serveroutput off
spool dbsz.out
variable s_num number;
BEGIN
select sum((datafile_blocks)*8/1024) into :s_num from v\$BACKUP_DATAFILE;
dbms_output.put_line(:s_num);
END;
/
set feedback on
select INPUT_BYTES/1024/1024 as inp_byte,OUTPUT_BYTES/1024/1024 as out_byte,OBJECT_TYPE,100*(MBYTES_PROCESSED/:s_num) as pctdone from v\$rman_status where status like '%RUNNING%';
spool off
EOF
#Realtime monitoring of RMAN restore which shows date and percentage of completion
pct="$(cat dbsz.txt|grep -v 'row'|tail -3|grep -v '^$'|awk '{print $5}')"
clear;
echo "$date_is|Current restore progress for $ORACLE_SID:[$pct]"
#cat $SIZELOG|grep -v 'PL'
#cat /dev/null > $SIZELOG
done
}
#ela_e=$(date +%s)
#echo "elapsed_time: $($ela_e - $ela_s)
_env
_restore_pct
No comments:
Post a Comment