This is not a difficult idea for someone who work as DBA, I just wish more idea for discussion and sharing . Some Idea using V$LOG.BYTES and V$ARCHIVED_LOG.*
but I believe V$ARCHIVED_LOG view be able to help:
Archivelog size each day:
SQL> select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME) order by 1;Archivelog size each hour:
TIME SIZE_MB
---------- ----------
2011-02-12 71797.87
2011-02-13 75880.52
2011-02-14 73569.37
2011-02-15 76776.81
2011-02-16 73959.86
2011-02-17 69969.71
2011-02-18 74677.10
2011-02-19 75474.95
2011-02-20 77967.07
2011-02-21 67802.70
SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24';That is just my samples ^^
Session altered.
SQL> select trunc(COMPLETION_TIME,'HH24') TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME,'HH24') order by 1;
TIME SIZE_MB
------------- ----------
2011-02-21 00 6396.65
2011-02-21 01 2797.31
2011-02-21 02 2010.45
2011-02-21 03 1871.77
2011-02-21 04 1481.5
2011-02-21 05 2868.20
2011-02-21 06 2363.89
2011-02-21 07 4269.26
2011-02-21 08 2469.08
2011-02-21 09 3007.06
2011-02-21 10 3561.97
2011-02-21 11 2530.57
2011-02-21 12 3509.08
2011-02-21 13 3022.5
2011-02-21 14 3514.97
2011-02-21 15 4057.45
2011-02-21 16 3021.27
2011-02-21 17 4014.31
2011-02-21 18 4011.66
2011-02-21 19 4008.10
2011-02-21 20 3015.46
How about archive log size each of day/hour on RAC?- Using GV$ARCHIVED_LOG ???
SQL> select INST_ID, RECID, NAME, to_char(COMPLETION_TIME,'YYYY-MM-DD HH24:MI:SS'), ARCHIVAL_THREAD# , blocks * block_size from GV$ARCHIVED_LOG order by COMPLETION_TIME;
INST_ID RECID NAME TO_CHAR(COMPLETION_ ARCHIVAL_THREAD#
---------- ---------- -------------------------------------------------- ------------------- ----------------
4 258572 +ARCH/DB/4_73214_617849235.dbf 2011-02-21 20:29:52 4
2 258572 +ARCH/DB/4_73214_617849235.dbf 2011-02-21 20:29:52 4
1 258572 +ARCH/DB/4_73214_617849235.dbf 2011-02-21 20:29:52 4
3 258572 +ARCH/DB/4_73214_617849235.dbf 2011-02-21 20:29:52 4
1 258573 +ARCH/DB/1_55739_617849235.dbf 2011-02-21 20:44:22 1
3 258573 +ARCH/DB/1_55739_617849235.dbf 2011-02-21 20:44:22 1
4 258573 +ARCH/DB/1_55739_617849235.dbf 2011-02-21 20:44:22 1
2 258573 +ARCH/DB/1_55739_617849235.dbf 2011-02-21 20:44:22 1
NO!!! - What do we see? - When we want to calculate archivelog size on RAC, we just use V$ARCHIVED_LOG view. So, on RAC:
Archivelog size each day:
SQL> select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME) order by 1;Archivelog size each hour:
TIME SIZE_MB
---------- ----------
2011-02-12 71797.87
2011-02-13 75880.52
2011-02-14 73569.37
2011-02-15 76776.81
2011-02-16 73959.86
2011-02-17 69969.71
2011-02-18 74677.10
2011-02-19 75474.95
2011-02-20 77967.07
2011-02-21 67802.70
SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24';How about your idea?
Session altered.
SQL> select trunc(COMPLETION_TIME,'HH24') TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc (COMPLETION_TIME,'HH24') order by 1;
TIME SIZE_MB
------------- ----------
2011-02-21 00 6396.65
2011-02-21 01 2797.31
2011-02-21 02 2010.45
2011-02-21 03 1871.77
2011-02-21 04 1481.5
2011-02-21 05 2868.20
2011-02-21 06 2363.89
2011-02-21 07 4269.26
2011-02-21 08 2469.08
2011-02-21 09 3007.06
2011-02-21 10 3561.97
2011-02-21 11 2530.57
2011-02-21 12 3509.08
2011-02-21 13 3022.5
2011-02-21 14 3514.97
2011-02-21 15 4057.45
2011-02-21 16 3021.27
2011-02-21 17 4014.31
2011-02-21 18 4011.66
2011-02-21 19 4008.10
2011-02-21 20 3015.46
3 comments:
Hi
I need to setup a three instance rac database.The datafiles and flash recovery area will be stored in ASM diskgroups called +data and +fra.
Which is the best location options for archivelogs so that they can be accessed during recovery without dba intervention ?
a-)
cluster file system with each instance writing to a shared location
or
b-)
cluster file system with each instance writing to a seperate location as long as all the locations are in directories under the same mount point
I don't get the question more. and this > cluster file system
If you use +data, +fra... that mean you use ASM = shares Disk
If archivelog files shares and you recovery database .. you don't do anything.
If archivelog files separate as choice b) You have to change something to access files from all nodes
need to filter by dest_id further, if there is multiple archive log destinations.
Post a Comment