Rakesh,
I assume 11.2? A redo can and will switch before it is full so comparing
grouped output like this is not equivalent.
If a log was manually switched, or more likely someone set
ARCHIVE_LAG_TARGET=20 you'd see lots of switches - every 20 seconds - without
any corresponding increase in size.
regards
Neil Chandler
Database Guy. Knows Things.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Rakesh Ra <rakeshra.tr@xxxxxxxxx>
Sent: 24 April 2019 15:52
To: Oracle-L Freelists
Cc: Rakesh RA
Subject: Query on v$log_history
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
