Oracle ideas: Redo logs and Standby redo logs

clear;

select
GROUP#,THREAD#,SEQUENCE#,BYTES,BLOCKSIZE,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE#
from
v$log;

select *
from
v$logfile
order by 1;

select max_t.SEQUENCE#,max_a.SEQUENCE#,max_t.SEQUENCE#-max_a.SEQUENCE# as DIFF
from
(SELECT max(t.SEQUENCE#) as SEQUENCE#
FROM V$ARCHIVED_LOG t
WHERE t.applied='YES') max_a,
(SELECT max(t.SEQUENCE#) as SEQUENCE#
FROM V$ARCHIVED_LOG t ) max_t;

select to_char(first_time,'YYYY-MM-DD') day,
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'99') "00",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'99') "01",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'99') "02",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'99') "03",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'99') "04",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'99') "05",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'99') "06",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'99') "07",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'99') "08",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'99') "09",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'99') "10",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'99') "11",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'99') "12",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'99') "13",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'99') "14",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'99') "15",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'99') "16",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'99') "17",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'99') "18",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'99') "19",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'99') "20",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'99') "21",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'99') "22",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'99') "23"
from v$log_history
group by to_char(first_time,'YYYY-MM-DD')
order by 1;

=================================================================================

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- -------------
1 1 86212 3221225472 512 1 YES INACTIVE 8792377511
2 1 86211 3221225472 512 1 YES INACTIVE 8791884644
3 1 86213 3221225472 512 1 YES INACTIVE 8792925415
12 1 86214 3221225472 512 1 YES INACTIVE 8793446835
13 1 86215 3221225472 512 1 YES ACTIVE 8794004089
14 1 86216 3221225472 512 1 NO CURRENT 8794515232
15 1 86210 3221225472 512 1 YES INACTIVE 8791157638

7 rows selected

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
1 ONLINE /oracle/fra/NSSER_STBY/onlinelog/o1_mf_1_821kcjbr_.log YES
2 ONLINE /oracle/fra/NSSER_STBY/onlinelog/o1_mf_2_821kjbdc_.log YES
3 ONLINE /oracle/fra/NSSER_STBY/onlinelog/o1_mf_3_821n3h1g_.log YES
7 STANDBY /oracle/fra/NSSER_STBY/onlinelog/o1_mf_7_7xgknq2b_.log YES
8 STANDBY /oracle/fra/NSSER_STBY/onlinelog/o1_mf_8_7xgknvoq_.log YES
9 STANDBY /oracle/fra/NSSER_STBY/onlinelog/o1_mf_9_7xgko0fl_.log YES
10 STANDBY /oracle/fra/NSSER_STBY/onlinelog/o1_mf_10_7xgko5m5_.log YES
11 STANDBY /oracle/fra/NSSER_STBY/onlinelog/o1_mf_11_7xgkojr0_.log YES
12 ONLINE /oracle/fra/NSSER_STBY/onlinelog/o1_mf_12_821n4sz3_.log YES
13 ONLINE /oracle/fra/NSSER_STBY/onlinelog/o1_mf_13_821n5n3r_.log YES
14 ONLINE /oracle/fra/NSSER_STBY/onlinelog/o1_mf_14_821n73bo_.log YES
15 ONLINE /oracle/fra/NSSER_STBY/onlinelog/o1_mf_15_821n965r_.log YES

12 rows selected

SEQUENCE# SEQUENCE# DIFF
---------- ---------- ----------
86215 86215 0

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
---------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
2012-07-27 0 33 43 43 45 45 45 45 46 46 48 48 47 46 46 46 50 46 45 45 45 46 46 46
2012-07-28 46 45 43 45 44 45 46 45 45 45 45 45 46 45 45 44 50 46 45 46 45 46 47 46
2012-07-29 45 45 44 44 46 45 46 46 47 45 46 46 46 45 46 45 49 46 46 46 46 47 46 46
2012-07-30 45 46 43 45 45 46 45 47 47 45 46 48 48 51 49 49 52 48 47 47 47 47 47 47
2012-07-31 47 46 43 43 45 46 45 46 46 52 51 48 47 48 48 46 53 51 48 48 47 47 47 48
2012-08-01 48 47 40 44 47 47 47 47 48 51 49 49 49 49 54 51 52 50 48 47 48 47 47 48
2012-08-02 48 47 40 46 46 45 47 46 47 48 49 49 46 49 53 53 53 50 49 48 48 47 49 48
2012-08-03 49 47 46 47 47 47 48 47 40 32 30 32 32 33 33 30 34 31 32 30 32 30 31 31
2012-08-04 30 32 29 29 31 31 30 31 30 30 32 31 32 31 32 30 34 30 32 30 31 31 30 32
2012-08-05 31 31 29 30 31 31 30 32 30 30 31 31 32 30 31 31 34 31 31 31 33 31 31 30
2012-08-06 31 31 29 30 30 31 29 29 25 24 26 24 24 25 24 25 26 25 24 24 25 24 24 24
2012-08-07 24 24 23 24 24 24 24 24 25 25 26 23 15 13 13 12 14 13 13 13 13 12 13 12
2012-08-08 13 12 11 13 12 12 13 12 12 11 12 11 11 13 12 11 13 11 11 11 11 11 11 12
2012-08-09 11 11 10 10 11 11 11 11 11 12 11 12 11 3 0 0 0 0 0 0 0 0 0 0

14 rows selected

SQL>