Query on v$log_history

From: Rakesh Ra <rakeshra.tr_at_gmail.com>
Date: Wed, 24 Apr 2019 20:22:11 +0530
Message-ID: <CAOGpvWqSQDcDo90=efwBXrZJo1j6fD+Fc-Fb_4CUtUUD5eZ01A_at_mail.gmail.com>

Hi All,

I have a simple question...

Does v$log_history include the logs that are shipped to standby locations... Or am i doing something wrong here or problem in understanding some basic thing?

v$log_history shows

select
to_char(first_time,'YY-MM-DD') day,

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999')
"00",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999')
"01",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999')
"02",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999')
"03",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999')
"04",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999')
"05",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999')
"06",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999')
"07",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999')
"08",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999')
"09",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999')
"10",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999')
"11",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999')
"12",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999')
"13",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999')
"14",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999')
"15",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999')
"16",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999')
"17",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999')
"18",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999')
"19",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999')
"20",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999')
"21",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999')
"22",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999')
"23",

COUNT(*) TOT
from v$log_history
group by to_char(first_time,'YY-MM-DD')
order by day
;

DAY      00   01   02   03   04   05   06   07   08   09   10   11   12
 13   14   15   16   17   18   19   20   21   22   23          TOT
-------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----

---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---------- 19-04-10 33 13 36 40 0 0 7 50 0 8 0 32 26 25 42 56 55 55 72 43 11 18 8 9 639 19-04-11 18 34 55 42 39 50 83 71 58 26 17 32 22 26 34 28 31 14 48 37 63 30 50 57 965 19-04-12 56 53 42 39 10 20 41 55 48 47 41 47 37 16 12 22 25 52 74 66 64 61 24 16 968 19-04-13 32 8 10 37 55 52 56 64 32 21 34 55 58 75 51 56 52 16 20 8 24 0 8 17 841 19-04-14 8 16 0 7 9 9 9 8 0 17 0 8 8 9 8 0 8 8 8 0 8 8 0 47 203 19-04-15 47 47 47 53 40 45 19 24 4 12 24 48 51 61 56 46 225 80 60 122 265 109 49 81 1615 19-04-16 47 54 174 52 45 15 31 32 22 19 16 40 68 67 61 48 39 42 23 24 32 16 24 65 1056 19-04-17 65 48 57 54 18 24 24 19 20 32 26 54 41 16 24 32 57 78 58 31 53 14 10 26 881 19-04-18 24 15 17 11 13 50 26 30 43 34 24 29 46 50 56 49 53 43 45 32 17 12 7 12 738 19-04-19 17 1 0 33 0 0 14 18 51 22 29 59 30 9 19 27 16 35 27 24 13 8 4 43 499 19-04-20 187 152 160 262 263 256 268 257 263 268 256 269 257 268 262 256 262 256 263 261 268 256 263 261

 5994<<<<<<<<<<<<<<<< switches??
19-04-21  257  263  261  244  262  262  263  256  262  263  262  262  256
266  259  262  262  256  260  263  265  256  262  245       6229
19-04-22  262  262  256  262  255  261  267  256  255  262  248  262  260
258  258  256  256  250  235  246  257  255  105   39       5783
19-04-23  259  193  154  261  239  264  299  304  305  305  302  306  309
303  308  304  291  304  261  270  307  301  307  306       6762
19-04-24  306  305  298  304  308  310   40   29   58    5    0    0    0
  0    0    0    0    0    0    0    0    0    0    0       1963

But if I see the archives generated .. On 20th only 209 GB is generated..

SELECT to_char(completion_time,'YYYY-MM-DD') DAY, to_char(sum(decode(to_char(completion_time,'HH24'),'00',(blocks*block_size)/1024/1024/1024,0)),'999')
"00",

to_char(sum(decode(to_char(completion_time,'HH24'),'01',(blocks*block_size)/1024/1024/1024,0)),'999')
"01",

to_char(sum(decode(to_char(completion_time,'HH24'),'02',(blocks*block_size)/1024/1024/1024,0)),'999')
"02",

to_char(sum(decode(to_char(completion_time,'HH24'),'03',(blocks*block_size)/1024/1024/1024,0)),'999')
"03",

to_char(sum(decode(to_char(completion_time,'HH24'),'04',(blocks*block_size)/1024/1024/1024,0)),'999')
"04",

to_char(sum(decode(to_char(completion_time,'HH24'),'05',(blocks*block_size)/1024/1024/1024,0)),'999')
"05",

to_char(sum(decode(to_char(completion_time,'HH24'),'06',(blocks*block_size)/1024/1024/1024,0)),'999')
"06",

to_char(sum(decode(to_char(completion_time,'HH24'),'07',(blocks*block_size)/1024/1024/1024,0)),'999')
"07",

to_char(sum(decode(to_char(completion_time,'HH24'),'08',(blocks*block_size)/1024/1024/1024,0)),'999')
"08",

to_char(sum(decode(to_char(completion_time,'HH24'),'09',(blocks*block_size)/1024/1024/1024,0)),'999')
"09",

to_char(sum(decode(to_char(completion_time,'HH24'),'10',(blocks*block_size)/1024/1024/1024,0)),'999')
"10",

to_char(sum(decode(to_char(completion_time,'HH24'),'11',(blocks*block_size)/1024/1024/1024,0)),'999')
"11",

to_char(sum(decode(to_char(completion_time,'HH24'),'12',(blocks*block_size)/1024/1024/1024,0)),'999')
"12",

to_char(sum(decode(to_char(completion_time,'HH24'),'13',(blocks*block_size)/1024/1024/1024,0)),'999')
"13",

to_char(sum(decode(to_char(completion_time,'HH24'),'14',(blocks*block_size)/1024/1024/1024,0)),'999')
"14",

to_char(sum(decode(to_char(completion_time,'HH24'),'15',(blocks*block_size)/1024/1024/1024,0)),'999')
"15",

to_char(sum(decode(to_char(completion_time,'HH24'),'16',(blocks*block_size)/1024/1024/1024,0)),'999')
"16",

to_char(sum(decode(to_char(completion_time,'HH24'),'17',(blocks*block_size)/1024/1024/1024,0)),'999')
"17",

to_char(sum(decode(to_char(completion_time,'HH24'),'18',(blocks*block_size)/1024/1024/1024,0)),'999')
"18",

to_char(sum(decode(to_char(completion_time,'HH24'),'19',(blocks*block_size)/1024/1024/1024,0)),'999')
"19",

to_char(sum(decode(to_char(completion_time,'HH24'),'20',(blocks*block_size)/1024/1024/1024,0)),'999')
"20",

to_char(sum(decode(to_char(completion_time,'HH24'),'21',(blocks*block_size)/1024/1024/1024,0)),'999')
"21",

to_char(sum(decode(to_char(completion_time,'HH24'),'22',(blocks*block_size)/1024/1024/1024,0)),'999')
"22",

to_char(sum(decode(to_char(completion_time,'HH24'),'23',(blocks*block_size)/1024/1024/1024,0)),'999')
"23",

sum(blocks*block_size)/1024/1024/1024 Tot_GB from v$ARCHIVED_LOG
where to_date(completion_time) > sysdate -30

and dest_id=1<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Just

primary dest..
GROUP by to_char(completion_time,'YYYY-MM-DD') order by 1 /

DAY        00   01   02   03   04   05   06   07   08   09   10   11   12
 13   14   15   16   17   18   19   20   21   22   23      TOT_GB
---------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----

---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---------

2019-03-26  644  224  225  878  673  615  994 #### #### #### #### ####
556  211  291 #### #### #### #### ####  675 ####  587  497  25174.93
2019-03-27  605  224  896 ####  672  448  200 #### #### #### #### ####  895
#### #### ####  532  564  903  673 #### #### #### ####  27198.25
2019-03-28  563  504  986  672  731  420 #### #### #### #### #### ####  447
#### ####  701 #### ####  896  901  449 #### #### ####  26371.02
2019-03-29 #### ####  676  682 #### ####  556  672  112  220  672  768  897
#### #### #### #### #### #### ####  474  447  699 ####  23890.43
2019-03-30 ####  280  168  345  839  672  715 #### #### #### #### ####  728
####  256  674  671 ####  580  895  971 #### #### ####  24624.87
2019-03-31 #### #### ####  843  898  561  623 #### #### #### #### ####
####  556  483 #### #### #### #### ####  368 #### #### ####  31484.23
2019-04-01 #### #### #### ####  669  898 #### #### #### #### ####  733  887
#### #### #### #### #### #### ####  712 #### #### ####  31551.92

2019-04-02 #### #### 447 #### 370 #### 828 776 #### #### #### ####

####  964  767  595 #### #### #### #### #### ####  747  721  26902.12
2019-04-03  959  692  560  773  287  530  649 #### #### #### ####  529
504  791  189 ####  532  699 #### #### #### #### #### ####  24300.38
2019-04-04 #### #### #### #### #### ####  728 #### #### #### #### ####

#### 935 266 261 166 868 588 222 692 874 419 186 23634.50 2019-04-05 59 291 261 452 900 784 559 643 643 684 448 448 #### 677 342 #### #### #### #### 420 28 140 363 153 15263.23

2019-04-06  508  168  224  265  346  517  654 #### #### #### #### ####
####  719  834  335  617  815  521  901  886 #### #### ####  23105.63
2019-04-07 #### ####  335 ####  197  447  844 #### #### #### #### ####
932  950 #### #### #### ####  925  222  565  695 #### ####  25048.79

2019-04-08 #### #### #### #### 310 489 698 800 464 351 982 #### #### #### #### #### 766 393 648 273 969 #### 447 420 24117.64 2019-04-09 422 371 200 707 #### #### #### #### #### 765 704 755 757 460 341 266 740 536 703 224 0 28 0 0 14862.42 2019-04-10 0 1 0 5 0 0 0 1 0 0 0 710

316  277 #### #### #### #### #### ####  280  562  196  252  11722.66
2019-04-11  448  840 #### ####  899 #### #### ####  922  807  531  718
703  671 ####  812  818  420 #### #### ####  931 #### ####  24394.43
2019-04-12 #### #### #### ####  335  560 #### #### #### #### #### ####

#### 448 448 613 440 #### #### #### #### #### 672 475 26867.89 2019-04-13 745 223 224 927 #### #### #### #### #### 527 692 #### #### #### #### #### #### 533 467 224 330 168 112 460 22345.71 2019-04-14 160 252 196 196 251 251 188 224 0 442 0 170 224 216 223 0 224 224 206 0 109 224 0 #### 5118.71

2019-04-15 #### #### #### #### #### ####  344  906   84  314  645 #### ####
#### #### #### ####  953 #### #### #### #### #### ####  28494.94
2019-04-16 #### #### #### #### ####  361  665  929  531  534  449  934 ####
#### #### #### #### ####  670  643  859  447  452 ####  24111.37
2019-04-17 #### #### #### ####  672  646  540  264    6  752  638 ####

977 643 699 756 #### #### #### 873 #### 531 169 870 23220.41 2019-04-18 674 281 532 335 291 #### 755 672 #### 863 731 852 #### #### #### #### #### #### 819 276 0 0 0 0 17549.16 2019-04-19 1 0 0 2 0 0 0 225 #### 599 521 #### 850 286 393 755 468 935 624 560 54 8 3 13 8954.66 2019-04-20 14 20 23 30 23 26 12 4 5 10 5 1 5 3 4 4 2 1 4 3 3 3 2 2

209.19<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Just 209 gigs of data for 20th
2019-04-21    4    1    1    4    1    1    2    1    1    2    2    2
2    2    2    2    2    2    3    3    2    2    3    2     51.01

2019-04-22 3 3 2 4 2 2 3 3 2 2 5 433 958 507 90 649 176 401 #### 219 681 391 217 151 5934.00 2019-04-23 382 130 67 168 262 473 353 248 431 16 20 37 17 26 277 931 273 610 #### #### #### 137 299 11 8828.12 2019-04-24 118 484 389 #### 669 465 616 311 761 466 0 0 0 0 0 0 0 0 0 0 0 0 0 0 5400.55

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 24 2019 - 16:52:11 CEST

This message: [ Message body ]
Next message: Neil Chandler: "Re: Query on v$log_history"
Previous message: Jonathan Lewis: "Re: Query Performance Issue"
Next in thread: Neil Chandler: "Re: Query on v$log_history"
Reply: Neil Chandler: "Re: Query on v$log_history"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message