Friday, August 1, 2014

Why are there multiple rows in V$SESSION_LONGOPS when running an RMAN backup?

Answer:
When performing an RMAN backup, V$SESSION_LONGOPS will be populated with two types of rows:

Detailed rows: Captures the progress of individual job steps.

Aggregated rows: Captures and summarizes the progress for the individual step of the backup job, as they complete. They are updated only after each detailed step has finished.

Example:
Below I am querying the V$SESSION_LONGOPS after an incremental backup level 0 has started, using 6 channels. Notice that there are both individual rows and an aggregated row present:
SELECT SID,SERIAL#,OPNAME,TARGET_DESC,TOTALWORK,SOFAR,UNITS,START_TIME,TIME_REMAINING,ELAPSED_SECONDS,MESSAGE
FROM V$SESSION_LONGOPS 
WHERE  TOTALWORK <> 0
AND SOFAR <> TOTALWORK
ORDER BY SERIAL# ASC;

SID SERIAL# OPNAME TARGET_DESC TOTALWORK SOFAR UNITS START_TIME TIME_REMAINING ELAPSED_SECONDS MESSAGE
203
7409
RMAN: incremental datafile backup Set Count
4096000
1004926
Blocks 01.08.2014 12:28:28
557
181
RMAN: incremental datafile backup: Set Count 137085: 1004926 out of 4096000 Blocks done
398
15079
RMAN: incremental datafile backup Set Count
3018368
1122302
Blocks 01.08.2014 12:28:28
306
181
RMAN: incremental datafile backup: Set Count 137088: 1122302 out of 3018368 Blocks done
439
17451
RMAN: incremental datafile backup Set Count
2560000
1127038
Blocks 01.08.2014 12:28:29
229
180
RMAN: incremental datafile backup: Set Count 137089: 1127038 out of 2560000 Blocks done
357
31627
RMAN: incremental datafile backup Set Count
3044608
1115518
Blocks 01.08.2014 12:28:28
313
181
RMAN: incremental datafile backup: Set Count 137087: 1115518 out of 3044608 Blocks done
476
55467
RMAN: incremental datafile backup Set Count
2557440
940542
Blocks 01.08.2014 12:28:29
309
180
RMAN: incremental datafile backup: Set Count 137090: 940542 out of 2557440 Blocks done
512
55527
RMAN: aggregate input backup
76514816
4360436
Blocks 01.08.2014 12:28:28
2085
126
RMAN: aggregate input: backup 33: 4360436 out of 76514816 Blocks done
281
56085
RMAN: incremental datafile backup Set Count
3251200
969214
Blocks 01.08.2014 12:28:28
426
181
RMAN: incremental datafile backup: Set Count 137086: 969214 out of 3251200 Blocks done

Let's join V$SESSION_LONGOPS with V$SESSION and (if desirable) V$PROCESS, to view only the detailed rows, and estimate the progress in percent. This query is very useful to quickly get an overview of your backup, whether or not it is progressing according to expected speed etc:

SELECT S.CLIENT_INFO "Client Info", SL.OPNAME "Operation" ,SL.MESSAGE, SL.SID, SL.SERIAL#, P.SPID "OS Process ID", SL.SOFAR "So Far", SL.TOTALWORK "Totalwork", ROUND(SL.SOFAR/SL.TOTALWORK*100,2) "% complete"
FROM V$SESSION_LONGOPS SL INNER JOIN V$SESSION S ON SL.SID = S.SID 
                          INNER JOIN V$PROCESS P ON P.ADDR = S.PADDR
AND OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK
/
Client Info Operation MESSAGE SID SERIAL# OS Process ID So Far Totalwork % complete
rman channel=tsm_channel_0 RMAN: incremental datafile backup RMAN: incremental datafile backup: Set Count 137096: 1173118 out of 2252800 Blocks done
203
7409
42926864
1173118
2252800
52,07
rman channel=tsm_channel_1 RMAN: incremental datafile backup RMAN: incremental datafile backup: Set Count 137095: 2029054 out of 2393600 Blocks done
281
56085
52101750
2029054
2393600
84,77
rman channel=tsm_channel_2 RMAN: incremental datafile backup RMAN: incremental datafile backup: Set Count 137097: 293246 out of 2096640 Blocks done
357
31627
56361104
293246
2096640
13,99
rman channel=tsm_channel_3 RMAN: incremental datafile backup RMAN: incremental datafile backup: Set Count 137099: 174206 out of 2048000 Blocks done
398
15079
52756692
174206
2048000
8,51
rman channel=tsm_channel_4 RMAN: incremental datafile backup RMAN: incremental datafile backup: Set Count 137098: 460286 out of 2048000 Blocks done
439
17451
29032454
460286
2048000
22,47
rman channel=tsm_channel_5 RMAN: incremental datafile backup RMAN: incremental datafile backup: Set Count 137100: 196094 out of 2048000 Blocks done
476
55467
9700724
196094
2048000
9,57

Notice how the V$RMAN_STATUS only hold information on aggregated level (2 rows, one for each operation although the first spawns the other):
SELECT SID,OPERATION,STATUS,MBYTES_PROCESSED, START_TIME, END_TIME, OBJECT_TYPE, OUTPUT_DEVICE_TYPE 
FROM V$RMAN_STATUS WHERE STATUS = 'RUNNING';

SID OPERATION STATUS MBYTES_PROCESSED START_TIME OBJECT_TYPE OUTPUT_DEVICE_TYPE
512
BACKUP RUNNING
226641,953125
01.08.2014 12:28:28 DB INCR SBT_TAPE
512
RMAN RUNNING
0
01.08.2014 12:28:28    



No comments:

Post a Comment