Re: Query on v$log_history
Date: Wed, 24 Apr 2019 16:04:31 +0000
Message-ID: <DB7PR10MB209073EA8E831F2E3601A3EE853C0_at_DB7PR10MB2090.EURPRD10.PROD.OUTLOOK.COM>
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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Rakesh Ra <rakeshra.tr_at_gmail.com> 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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 24 2019 - 18:04:31 CEST