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