Home » RDBMS Server » Server Administration » ARC file size too small
ARC file size too small [message #567507] Tue, 02 October 2012 18:35 Go to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
I noticed my DB is generating a lot of "small" .arc files and I am usure why. As you can see from the v$log query my log file size is set to 50MB. But yet BLOCKS*BLOCK_SIZE never adds up to 50MB.

Is there anything else I can look into to see how to make the .arc files larger?



SQL> select group#, thread#, bytes from v$log;

    GROUP#    THREAD#      BYTES
---------- ---------- ----------
         1          1   52428800
         2          1   52428800
         3          2   52428800
         4          2   52428800


select blocks, block_size, blocks*block_size from v$archived_log
  where sequence# between 63876 and 72851 and thread# = 1

  BLOCKS BLOCK_SIZE BLOCKS*BLOCK_SIZE
---------- ---------- -----------------
        28        512             14336
        28        512             14336
        28        512             14336
        55        512             28160
        55        512             28160
        55        512             28160
        43        512             22016
        43        512             22016
        43        512             22016
       130        512             66560
       130        512             66560

    BLOCKS BLOCK_SIZE BLOCKS*BLOCK_SIZE
---------- ---------- -----------------
       130        512             66560
        85        512             43520
        85        512             43520
        85        512             43520


show parameter db_block_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

SQL> show parameter archive_lag_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0

Re: ARC file size too small [message #567515 is a reply to message #567507] Wed, 03 October 2012 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many other parameters that influence the archived log size.
What is your actual problem?

Regards
Michel
ARC file size too small [message #567568 is a reply to message #567507] Wed, 03 October 2012 06:20 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
I noticed I had a huge amount of log switches over the past few days. I would like understand why this is happening and see if I can reduce the switches to a reasonable number.

I thought this was controlled by archive_lag_target but that is set to 0 and disabled. Can somebody provide me with other parameters I should check? If needed, I can upload all the parms for review.

Re: ARC file size too small [message #567574 is a reply to message #567568] Wed, 03 October 2012 06:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
noticed I had a huge amount of log switches over the past few days. I would like understand why this is happening and see if I can reduce the switches to a reasonable number.


Enlarge your ONLINE redo logs.
The problem has nothing to do with the size of ARCHIVED logs.

Regards
Michel
Re: ARC file size too small [message #567580 is a reply to message #567574] Wed, 03 October 2012 07:15 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
As you can see I posted the size of my online redo logs and they are 50MB, but the query I ran does not show any of the .arc files to be that size.

Is there something else missing?
Re: ARC file size too small [message #567584 is a reply to message #567580] Wed, 03 October 2012 07:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It does NOT matter what is the size of the archived logs for your problem.
Your problem is the size of the ONLINE redo logs.

Regards
Michel
Re: ARC file size too small [message #567591 is a reply to message #567584] Wed, 03 October 2012 07:52 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
I would expect to see the majority of the .arc files close to or the same size as the redlogs (50MB). That does not seem to be the case. Can you explain why this is happening and how making the online logs bigger will solve this issue.
Re: ARC file size too small [message #567596 is a reply to message #567591] Wed, 03 October 2012 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again it does NOT matter. I have NEVER had a look at archived logs size in 20 years. I don't care. Nobody care. It does not matter.
Fix your REAL problem.

Regards
Michel
Re: ARC file size too small [message #567622 is a reply to message #567596] Wed, 03 October 2012 10:51 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
thanks for your help. I will look some where else to try to get the answer to my question.
Re: ARC file size too small [message #567623 is a reply to message #567622] Wed, 03 October 2012 10:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But could you explain why you need an answer to this question (I already answered in my first reply by the way)?

Regards
Michel
Re: ARC file size too small [message #567624 is a reply to message #567623] Wed, 03 October 2012 11:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> I will look some where else to try to get the answer to my question.

You appear to suffer from Compulsive Tuning Disorder
Re: ARC file size too small [message #567625 is a reply to message #567622] Wed, 03 October 2012 11:15 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
OK, I'll have a go. I think you need to provide more information.

First, your query should be selecting all archive logfiles generated by about 9000 log switches. But you have provided the results for only five. Are these actually typical of the other 9000?

Second, can you adjust the query to include the first_time column, and to exclude the multiple copies of each archive?

Third, can you run this (or something similar) to calculate the amount of redo being archived per hour over the last few days (the script assumes three archive log destinations, which seems to be what you have):
alter session set nls_date_format='dd-mm-yy hh24:mi:ss';
select trunc(first_time,'HH'),round(sum(blocks*block_size/1024/1024/3)) redoMB
from v$archived_log
where first_time > sysdate - &how_many_days
group by trunc(first_time,'HH') order by trunc(first_time,'HH');



Re: ARC file size too small [message #567632 is a reply to message #567625] Wed, 03 October 2012 14:41 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
See what is writing the most to the database with the following query. Maybe you can drop some indexes or get the developers to not write to some log tables.
ENWEBP1P > select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",a.object_name,
  2  sum(b.PHYSICAL_WRITES_DELTA) physical_writes_delta
  3  from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
  4  where b.SNAP_ID >
  5  (select max(SNAP_ID)-24*2 from sys.wRM$_SNAPSHOT) and a.object_id=b.OBJ#
  6  and b.PHYSICAL_WRITES_DELTA>1000
  7  and c.instance_number=(select instance_number from v$instance)
  8  and c.snap_id=b.snap_id
  9  group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day'),a.object_name
 10  order by 1,3;

DATE                 OBJECT_NAME                     PHYSICAL_WRITES_DELTA
-------------------- ------------------------------- ---------------------
2012-10-02 Tuesday   VIDEO_WEEKLY_VIEWS                               1049
2012-10-02 Tuesday   I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST                   1360
2012-10-02 Tuesday   WRH$_ACTIVE_SESSION_HISTORY                      1867
2012-10-02 Tuesday   NFL_PERSON                                      10712
2012-10-02 Tuesday   VIDEO                                           40543
2012-10-02 Tuesday   REGISTRATIONS                                  339744

DATE                 OBJECT_NAME                     PHYSICAL_WRITES_DELTA
-------------------- ------------------------------- ---------------------
2012-10-03 Wednesday NFL_PERSON                                       1030
2012-10-03 Wednesday I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST                   1062
2012-10-03 Wednesday VIDEO_WEEKLY_VIEWS                               1213
2012-10-03 Wednesday VIDEO                                           21552
2012-10-03 Wednesday REGISTRATIONS                                  123941
Re: ARC file size too small [message #567635 is a reply to message #567632] Wed, 03 October 2012 15:48 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
Results from Alan's query


DATE                 OBJECT_NAME                 PHYSICAL_WRITES_DELTA
2012-10-01 Monday    WRH$_ACTIVE_SESSION_HISTORY   1060
2012-10-01 Monday    I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 2021
2012-10-02 Tuesday   I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST  1023



Results from John's query.. I modified it just a bit as there was a syntax erorr when I ran what was posted.

To answer Michel my DBW process decided to terminate the instance. I think this is a result of too many log swirches. I found this online

"GEN0 process seems to get too many messages during the instance startup by DBW process and GEN0 is not able respond in time." (note ID 1404964.1)

I am hoping if I can get the full 50MB .arc file this will give me less log switches.



SQL> column  Start_Date format a10
SQL> column  Start_Time format a5

SQL> SELECT Start_Date,
  2         Start_Time,
  3         Num_Logs,
  4         Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),
  5               2) AS Mbytes,
  6         Vdb.NAME AS Dbname
  7    FROM (SELECT To_Char(Vlh.First_Time,
  8                         'YYYY-MM-DD') AS Start_Date,
  9                 To_Char(Vlh.First_Time,
 10                         'HH24') || ':00' AS Start_Time,
 11                 COUNT(Vlh.Thread#) Num_Logs
 12            FROM V$log_History Vlh
 13           GROUP BY To_Char(Vlh.First_Time,
 14                            'YYYY-MM-DD'),
 15                    To_Char(Vlh.First_Time,
 16                            'HH24') || ':00') Log_Hist,
 17         V$log Vl,
 18         V$database Vdb
 19   WHERE Vl.Group# = 1
 20   ORDER BY Log_Hist.Start_Date,
 21            Log_Hist.Start_Time;

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-13 13:00        180       9000 ORARZULP
2012-09-13 14:00        279      13950 ORARZULP
2012-09-13 15:00        263      13150 ORARZULP
2012-09-13 16:00        263      13150 ORARZULP
2012-09-13 17:00        268      13400 ORARZULP
2012-09-13 18:00        268      13400 ORARZULP
2012-09-13 19:00        259      12950 ORARZULP
2012-09-13 20:00        273      13650 ORARZULP
2012-09-13 21:00        275      13750 ORARZULP
2012-09-13 22:00        263      13150 ORARZULP
2012-09-13 23:00        253      12650 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-14 00:00        282      14100 ORARZULP
2012-09-14 01:00        283      14150 ORARZULP
2012-09-14 02:00        275      13750 ORARZULP
2012-09-14 03:00        224      11200 ORARZULP
2012-09-14 04:00        158       7900 ORARZULP
2012-09-14 05:00        172       8600 ORARZULP
2012-09-14 06:00        170       8500 ORARZULP
2012-09-14 07:00        165       8250 ORARZULP
2012-09-14 08:00        170       8500 ORARZULP
2012-09-14 09:00        181       9050 ORARZULP
2012-09-14 10:00        173       8650 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-14 11:00        143       7150 ORARZULP
2012-09-14 12:00         86       4300 ORARZULP
2012-09-14 15:00          2        100 ORARZULP
2012-09-14 18:00         11        550 ORARZULP
2012-09-14 19:00         82       4100 ORARZULP
2012-09-14 20:00        106       5300 ORARZULP
2012-09-14 21:00        120       6000 ORARZULP
2012-09-14 22:00        121       6050 ORARZULP
2012-09-14 23:00        118       5900 ORARZULP
2012-09-15 00:00        127       6350 ORARZULP
2012-09-15 01:00        120       6000 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-15 02:00        120       6000 ORARZULP
2012-09-15 03:00        122       6100 ORARZULP
2012-09-15 04:00        114       5700 ORARZULP
2012-09-15 05:00        120       6000 ORARZULP
2012-09-15 06:00        120       6000 ORARZULP
2012-09-15 07:00        120       6000 ORARZULP
2012-09-15 08:00        120       6000 ORARZULP
2012-09-15 09:00        116       5800 ORARZULP
2012-09-15 10:00        120       6000 ORARZULP
2012-09-15 11:00        117       5850 ORARZULP
2012-09-15 12:00        119       5950 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-15 13:00        120       6000 ORARZULP
2012-09-15 14:00        118       5900 ORARZULP
2012-09-15 15:00        120       6000 ORARZULP
2012-09-15 16:00        118       5900 ORARZULP
2012-09-15 17:00        120       6000 ORARZULP
2012-09-15 18:00        118       5900 ORARZULP
2012-09-15 19:00        120       6000 ORARZULP
2012-09-15 20:00        118       5900 ORARZULP
2012-09-15 21:00        120       6000 ORARZULP
2012-09-15 22:00        120       6000 ORARZULP
2012-09-15 23:00        116       5800 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-16 00:00        127       6350 ORARZULP
2012-09-16 01:00        118       5900 ORARZULP
2012-09-16 02:00        122       6100 ORARZULP
2012-09-16 03:00        120       6000 ORARZULP
2012-09-16 04:00        116       5800 ORARZULP
2012-09-16 05:00        120       6000 ORARZULP
2012-09-16 06:00        119       5950 ORARZULP
2012-09-16 07:00        120       6000 ORARZULP
2012-09-16 08:00        118       5900 ORARZULP
2012-09-16 09:00        120       6000 ORARZULP
2012-09-16 10:00        120       6000 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-16 11:00        120       6000 ORARZULP
2012-09-16 12:00        119       5950 ORARZULP
2012-09-16 13:00        119       5950 ORARZULP
2012-09-16 14:00        119       5950 ORARZULP
2012-09-16 15:00        119       5950 ORARZULP
2012-09-16 16:00        126       6300 ORARZULP
2012-09-16 17:00        125       6250 ORARZULP
2012-09-16 18:00        117       5850 ORARZULP
2012-09-16 19:00        120       6000 ORARZULP
2012-09-16 20:00        120       6000 ORARZULP
2012-09-16 21:00        120       6000 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-16 22:00        118       5900 ORARZULP
2012-09-16 23:00        120       6000 ORARZULP
2012-09-17 00:00        127       6350 ORARZULP
2012-09-17 01:00        118       5900 ORARZULP
2012-09-17 02:00        122       6100 ORARZULP
2012-09-17 03:00        120       6000 ORARZULP
2012-09-17 04:00        120       6000 ORARZULP
2012-09-17 05:00        120       6000 ORARZULP
2012-09-17 06:00        119       5950 ORARZULP
2012-09-17 07:00        121       6050 ORARZULP
2012-09-17 08:00        118       5900 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-17 09:00        120       6000 ORARZULP
2012-09-17 10:00        119       5950 ORARZULP
2012-09-17 11:00        120       6000 ORARZULP
2012-09-17 12:00        117       5850 ORARZULP
2012-09-17 13:00        120       6000 ORARZULP
2012-09-17 14:00        120       6000 ORARZULP
2012-09-17 15:00        118       5900 ORARZULP
2012-09-17 16:00        120       6000 ORARZULP
2012-09-17 17:00        116       5800 ORARZULP
2012-09-17 18:00        121       6050 ORARZULP
2012-09-17 19:00        120       6000 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-17 20:00        118       5900 ORARZULP
2012-09-17 21:00        120       6000 ORARZULP
2012-09-17 22:00        120       6000 ORARZULP
2012-09-17 23:00        120       6000 ORARZULP
2012-09-18 00:00        122       6100 ORARZULP
2012-09-18 01:00        120       6000 ORARZULP
2012-09-18 02:00        123       6150 ORARZULP
2012-09-18 03:00        122       6100 ORARZULP
2012-09-18 04:00        120       6000 ORARZULP
2012-09-18 05:00        115       5750 ORARZULP
2012-09-18 06:00        119       5950 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-18 07:00        118       5900 ORARZULP
2012-09-18 08:00        119       5950 ORARZULP
2012-09-18 09:00        120       6000 ORARZULP
2012-09-18 10:00        114       5700 ORARZULP
2012-09-18 11:00        120       6000 ORARZULP
2012-09-18 12:00        117       5850 ORARZULP
2012-09-18 13:00        121       6050 ORARZULP
2012-09-18 14:00        120       6000 ORARZULP
2012-09-18 15:00        120       6000 ORARZULP
2012-09-18 16:00        116       5800 ORARZULP
2012-09-18 17:00        120       6000 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-18 18:00        120       6000 ORARZULP
2012-09-18 19:00        117       5850 ORARZULP
2012-09-18 20:00        121       6050 ORARZULP
2012-09-18 21:00        117       5850 ORARZULP
2012-09-18 22:00        121       6050 ORARZULP
2012-09-18 23:00        120       6000 ORARZULP
2012-09-19 00:00        125       6250 ORARZULP
2012-09-19 01:00        121       6050 ORARZULP
2012-09-19 02:00        121       6050 ORARZULP
2012-09-19 03:00        122       6100 ORARZULP
2012-09-19 04:00        118       5900 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-19 05:00        120       6000 ORARZULP
2012-09-19 06:00        121       6050 ORARZULP
2012-09-19 07:00        118       5900 ORARZULP
2012-09-19 08:00        122       6100 ORARZULP
2012-09-19 09:00        120       6000 ORARZULP
2012-09-19 10:00        120       6000 ORARZULP
2012-09-19 11:00        117       5850 ORARZULP
2012-09-19 12:00        123       6150 ORARZULP
2012-09-19 13:00        117       5850 ORARZULP
2012-09-19 14:00        120       6000 ORARZULP
2012-09-19 15:00        120       6000 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-19 16:00        116       5800 ORARZULP
2012-09-19 17:00        120       6000 ORARZULP
2012-09-19 18:00        118       5900 ORARZULP
2012-09-19 19:00        121       6050 ORARZULP
2012-09-19 20:00        121       6050 ORARZULP
2012-09-19 21:00        121       6050 ORARZULP
2012-09-19 22:00        117       5850 ORARZULP
2012-09-19 23:00        120       6000 ORARZULP
2012-09-20 00:00        127       6350 ORARZULP
2012-09-20 01:00        120       6000 ORARZULP
2012-09-20 02:00        123       6150 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-20 03:00        121       6050 ORARZULP
2012-09-20 04:00        120       6000 ORARZULP
2012-09-20 05:00        116       5800 ORARZULP
2012-09-20 06:00        121       6050 ORARZULP
2012-09-20 07:00        120       6000 ORARZULP
2012-09-20 08:00        116       5800 ORARZULP
2012-09-20 09:00        120       6000 ORARZULP
2012-09-20 10:00        120       6000 ORARZULP
2012-09-20 11:00        120       6000 ORARZULP
2012-09-20 12:00        120       6000 ORARZULP
2012-09-20 13:00        121       6050 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-20 14:00        119       5950 ORARZULP
2012-09-20 15:00        118       5900 ORARZULP
2012-09-20 16:00        121       6050 ORARZULP
2012-09-20 17:00        119       5950 ORARZULP
2012-09-20 18:00        117       5850 ORARZULP
2012-09-20 19:00        120       6000 ORARZULP
2012-09-20 20:00        119       5950 ORARZULP
2012-09-20 21:00        121       6050 ORARZULP
2012-09-20 22:00        120       6000 ORARZULP
2012-09-20 23:00        121       6050 ORARZULP
2012-09-21 00:00        127       6350 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-21 01:00        117       5850 ORARZULP
2012-09-21 02:00        125       6250 ORARZULP
2012-09-21 03:00        120       6000 ORARZULP
2012-09-21 04:00        120       6000 ORARZULP
2012-09-21 05:00        120       6000 ORARZULP
2012-09-21 06:00        119       5950 ORARZULP
2012-09-21 07:00        121       6050 ORARZULP
2012-09-21 08:00        118       5900 ORARZULP
2012-09-21 09:00        122       6100 ORARZULP
2012-09-21 10:00        116       5800 ORARZULP
2012-09-21 11:00        120       6000 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-21 12:00        120       6000 ORARZULP
2012-09-21 13:00        118       5900 ORARZULP
2012-09-21 14:00        121       6050 ORARZULP
2012-09-21 15:00        116       5800 ORARZULP
2012-09-21 16:00        121       6050 ORARZULP
2012-09-21 17:00        117       5850 ORARZULP
2012-09-21 18:00        120       6000 ORARZULP
2012-09-21 19:00        120       6000 ORARZULP
2012-09-21 20:00        120       6000 ORARZULP
2012-09-21 21:00        119       5950 ORARZULP
2012-09-21 22:00        122       6100 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-21 23:00        121       6050 ORARZULP
2012-09-22 00:00        123       6150 ORARZULP
2012-09-22 01:00        121       6050 ORARZULP
2012-09-22 02:00        118       5900 ORARZULP
2012-09-22 03:00        123       6150 ORARZULP
2012-09-22 04:00        120       6000 ORARZULP
2012-09-22 05:00        118       5900 ORARZULP
2012-09-22 06:00        123       6150 ORARZULP
2012-09-22 07:00        120       6000 ORARZULP
2012-09-22 08:00        118       5900 ORARZULP
2012-09-22 09:00        120       6000 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-22 10:00        113       5650 ORARZULP
2012-09-22 11:00        119       5950 ORARZULP
2012-09-22 12:00        121       6050 ORARZULP
2012-09-22 13:00        118       5900 ORARZULP
2012-09-22 14:00        120       6000 ORARZULP
2012-09-22 15:00        119       5950 ORARZULP
2012-09-22 16:00        120       6000 ORARZULP
2012-09-22 17:00        118       5900 ORARZULP
2012-09-22 18:00        120       6000 ORARZULP
2012-09-22 19:00        120       6000 ORARZULP
2012-09-22 20:00        118       5900 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-22 21:00        120       6000 ORARZULP
2012-09-22 22:00        116       5800 ORARZULP
2012-09-22 23:00        121       6050 ORARZULP
2012-09-23 00:00        122       6100 ORARZULP
2012-09-23 01:00        121       6050 ORARZULP
2012-09-23 02:00        121       6050 ORARZULP
2012-09-23 03:00        121       6050 ORARZULP
2012-09-23 04:00        119       5950 ORARZULP
2012-09-23 05:00        120       6000 ORARZULP
2012-09-23 06:00        123       6150 ORARZULP
2012-09-23 07:00        118       5900 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-23 08:00        120       6000 ORARZULP
2012-09-23 09:00        118       5900 ORARZULP
2012-09-23 10:00        120       6000 ORARZULP
2012-09-23 11:00        118       5900 ORARZULP
2012-09-23 12:00        120       6000 ORARZULP
2012-09-23 13:00        121       6050 ORARZULP
2012-09-23 14:00        120       6000 ORARZULP
2012-09-23 15:00        116       5800 ORARZULP
2012-09-23 16:00        120       6000 ORARZULP
2012-09-23 17:00        120       6000 ORARZULP
2012-09-23 18:00        116       5800 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-23 19:00        122       6100 ORARZULP
2012-09-23 20:00        116       5800 ORARZULP
2012-09-23 21:00        120       6000 ORARZULP
2012-09-23 22:00        119       5950 ORARZULP
2012-09-23 23:00        120       6000 ORARZULP
2012-09-24 00:00        123       6150 ORARZULP
2012-09-24 01:00        120       6000 ORARZULP
2012-09-24 02:00        123       6150 ORARZULP
2012-09-24 03:00        119       5950 ORARZULP
2012-09-24 04:00        121       6050 ORARZULP
2012-09-24 05:00        118       5900 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-24 06:00        120       6000 ORARZULP
2012-09-24 07:00        118       5900 ORARZULP
2012-09-24 08:00        120       6000 ORARZULP
2012-09-24 09:00        118       5900 ORARZULP
2012-09-24 10:00        120       6000 ORARZULP
2012-09-24 11:00        116       5800 ORARZULP
2012-09-24 12:00        121       6050 ORARZULP
2012-09-24 13:00        120       6000 ORARZULP
2012-09-24 14:00        120       6000 ORARZULP
2012-09-24 15:00        120       6000 ORARZULP
2012-09-24 16:00         31       1550 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-24 17:00         42       2100 ORARZULP
2012-09-24 18:00        116       5800 ORARZULP
2012-09-24 19:00        120       6000 ORARZULP
2012-09-24 20:00        118       5900 ORARZULP
2012-09-24 21:00        121       6050 ORARZULP
2012-09-24 22:00        122       6100 ORARZULP
2012-09-24 23:00        116       5800 ORARZULP
2012-09-25 00:00        128       6400 ORARZULP
2012-09-25 01:00        121       6050 ORARZULP
2012-09-25 02:00        122       6100 ORARZULP
2012-09-25 03:00        122       6100 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-25 04:00        118       5900 ORARZULP
2012-09-25 05:00        120       6000 ORARZULP
2012-09-25 06:00        119       5950 ORARZULP
2012-09-25 07:00        120       6000 ORARZULP
2012-09-25 08:00        116       5800 ORARZULP
2012-09-25 09:00        120       6000 ORARZULP
2012-09-25 10:00        120       6000 ORARZULP
2012-09-25 11:00        116       5800 ORARZULP
2012-09-25 12:00        121       6050 ORARZULP
2012-09-25 13:00        117       5850 ORARZULP
2012-09-25 14:00        120       6000 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-25 15:00        120       6000 ORARZULP
2012-09-25 16:00        118       5900 ORARZULP
2012-09-25 17:00        120       6000 ORARZULP
2012-09-25 18:00        116       5800 ORARZULP
2012-09-25 19:00        121       6050 ORARZULP
2012-09-25 20:00        121       6050 ORARZULP
2012-09-25 21:00        116       5800 ORARZULP
2012-09-25 22:00        123       6150 ORARZULP
2012-09-25 23:00        115       5750 ORARZULP
2012-09-26 00:00        127       6350 ORARZULP
2012-09-26 01:00        121       6050 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-26 02:00        123       6150 ORARZULP
2012-09-26 03:00        120       6000 ORARZULP
2012-09-26 04:00        116       5800 ORARZULP
2012-09-26 05:00        120       6000 ORARZULP
2012-09-26 06:00        118       5900 ORARZULP
2012-09-26 07:00        120       6000 ORARZULP
2012-09-26 08:00        121       6050 ORARZULP
2012-09-26 09:00        118       5900 ORARZULP
2012-09-26 10:00        120       6000 ORARZULP
2012-09-26 11:00        118       5900 ORARZULP
2012-09-26 12:00        120       6000 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-26 13:00        118       5900 ORARZULP
2012-09-26 14:00        120       6000 ORARZULP
2012-09-26 15:00        118       5900 ORARZULP
2012-09-26 16:00        120       6000 ORARZULP
2012-09-26 17:00        120       6000 ORARZULP
2012-09-26 18:00        120       6000 ORARZULP
2012-09-26 19:00        120       6000 ORARZULP
2012-09-26 20:00        119       5950 ORARZULP
2012-09-26 21:00        120       6000 ORARZULP
2012-09-26 22:00        121       6050 ORARZULP
2012-09-26 23:00        118       5900 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-27 00:00        128       6400 ORARZULP
2012-09-27 01:00        118       5900 ORARZULP
2012-09-27 02:00        122       6100 ORARZULP
2012-09-27 03:00        119       5950 ORARZULP
2012-09-27 04:00        121       6050 ORARZULP
2012-09-27 05:00        114       5700 ORARZULP
2012-09-27 06:00        120       6000 ORARZULP
2012-09-27 07:00        119       5950 ORARZULP
2012-09-27 08:00        120       6000 ORARZULP
2012-09-27 09:00        120       6000 ORARZULP
2012-09-27 10:00        119       5950 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-27 11:00        120       6000 ORARZULP
2012-09-27 12:00        118       5900 ORARZULP
2012-09-27 13:00        120       6000 ORARZULP
2012-09-27 14:00        116       5800 ORARZULP
2012-09-27 15:00        120       6000 ORARZULP
2012-09-27 16:00        120       6000 ORARZULP
2012-09-27 17:00        116       5800 ORARZULP
2012-09-27 18:00        120       6000 ORARZULP
2012-09-27 19:00        116       5800 ORARZULP
2012-09-27 20:00        120       6000 ORARZULP
2012-09-27 21:00        120       6000 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-27 22:00        118       5900 ORARZULP
2012-09-27 23:00        121       6050 ORARZULP
2012-09-28 00:00        126       6300 ORARZULP
2012-09-28 01:00        121       6050 ORARZULP
2012-09-28 02:00        120       6000 ORARZULP
2012-09-28 03:00        122       6100 ORARZULP
2012-09-28 04:00        118       5900 ORARZULP
2012-09-28 05:00        118       5900 ORARZULP
2012-09-28 06:00        120       6000 ORARZULP
2012-09-28 07:00        116       5800 ORARZULP
2012-09-28 08:00        120       6000 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-28 09:00        116       5800 ORARZULP
2012-09-28 10:00        120       6000 ORARZULP
2012-09-28 11:00        116       5800 ORARZULP
2012-09-28 12:00        121       6050 ORARZULP
2012-09-28 13:00        120       6000 ORARZULP
2012-09-28 14:00        118       5900 ORARZULP
2012-09-28 15:00        120       6000 ORARZULP
2012-09-28 16:00        120       6000 ORARZULP
2012-09-28 17:00        116       5800 ORARZULP
2012-09-28 18:00        121       6050 ORARZULP
2012-09-28 19:00        118       5900 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-28 20:00        121       6050 ORARZULP
2012-09-28 21:00        120       6000 ORARZULP
2012-09-28 22:00        121       6050 ORARZULP
2012-09-28 23:00        120       6000 ORARZULP
2012-09-29 00:00        123       6150 ORARZULP
2012-09-29 01:00        121       6050 ORARZULP
2012-09-29 02:00        122       6100 ORARZULP
2012-09-29 03:00        121       6050 ORARZULP
2012-09-29 04:00        121       6050 ORARZULP
2012-09-29 05:00        118       5900 ORARZULP
2012-09-29 06:00        123       6150 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-29 07:00        118       5900 ORARZULP
2012-09-29 08:00        118       5900 ORARZULP
2012-09-29 09:00        120       6000 ORARZULP
2012-09-29 10:00        115       5750 ORARZULP
2012-09-29 11:00        120       6000 ORARZULP
2012-09-29 12:00        121       6050 ORARZULP
2012-09-29 13:00        118       5900 ORARZULP
2012-09-29 14:00        120       6000 ORARZULP
2012-09-29 15:00        119       5950 ORARZULP
2012-09-29 16:00        247      12350 ORARZULP
2012-09-29 17:00        308      15400 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-29 18:00        330      16500 ORARZULP
2012-09-29 19:00        317      15850 ORARZULP
2012-09-29 20:00        309      15450 ORARZULP
2012-09-29 21:00        304      15200 ORARZULP
2012-09-29 22:00        322      16100 ORARZULP
2012-09-29 23:00        309      15450 ORARZULP
2012-09-30 00:00        302      15100 ORARZULP
2012-09-30 01:00        342      17100 ORARZULP
2012-09-30 02:00        313      15650 ORARZULP
2012-09-30 03:00        295      14750 ORARZULP
2012-09-30 04:00        317      15850 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-30 05:00        344      17200 ORARZULP
2012-09-30 06:00        304      15200 ORARZULP
2012-09-30 07:00        304      15200 ORARZULP
2012-09-30 08:00        323      16150 ORARZULP
2012-09-30 09:00        303      15150 ORARZULP
2012-09-30 10:00        300      15000 ORARZULP
2012-09-30 11:00        281      14050 ORARZULP
2012-09-30 12:00        347      17350 ORARZULP
2012-09-30 13:00        342      17100 ORARZULP
2012-09-30 14:00        338      16900 ORARZULP
2012-09-30 15:00        344      17200 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-09-30 16:00        323      16150 ORARZULP
2012-09-30 17:00        426      21300 ORARZULP
2012-09-30 18:00        477      23850 ORARZULP
2012-09-30 19:00        463      23150 ORARZULP
2012-09-30 20:00        461      23050 ORARZULP
2012-09-30 21:00        463      23150 ORARZULP
2012-09-30 22:00        473      23650 ORARZULP
2012-09-30 23:00        472      23600 ORARZULP
2012-10-01 00:00        491      24550 ORARZULP
2012-10-01 01:00        468      23400 ORARZULP
2012-10-01 02:00        427      21350 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-10-01 03:00        360      18000 ORARZULP
2012-10-01 04:00        314      15700 ORARZULP
2012-10-01 05:00        308      15400 ORARZULP
2012-10-01 06:00        321      16050 ORARZULP
2012-10-01 07:00        328      16400 ORARZULP
2012-10-01 08:00        317      15850 ORARZULP
2012-10-01 09:00        307      15350 ORARZULP
2012-10-01 10:00        331      16550 ORARZULP
2012-10-01 11:00        314      15700 ORARZULP
2012-10-01 12:00        341      17050 ORARZULP
2012-10-01 13:00        303      15150 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-10-01 14:00        309      15450 ORARZULP
2012-10-01 15:00        371      18550 ORARZULP
2012-10-01 16:00        475      23750 ORARZULP
2012-10-01 17:00        432      21600 ORARZULP
2012-10-01 18:00        410      20500 ORARZULP
2012-10-01 19:00        327      16350 ORARZULP
2012-10-01 20:00        293      14650 ORARZULP
2012-10-01 21:00        325      16250 ORARZULP
2012-10-01 22:00        320      16000 ORARZULP
2012-10-01 23:00        367      18350 ORARZULP
2012-10-02 00:00        311      15550 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-10-02 01:00        324      16200 ORARZULP
2012-10-02 02:00        339      16950 ORARZULP
2012-10-02 03:00        305      15250 ORARZULP
2012-10-02 04:00        304      15200 ORARZULP
2012-10-02 05:00        335      16750 ORARZULP
2012-10-02 06:00        350      17500 ORARZULP
2012-10-02 07:00        236      11800 ORARZULP
2012-10-02 08:00        234      11700 ORARZULP
2012-10-02 09:00        250      12500 ORARZULP
2012-10-02 10:00        230      11500 ORARZULP
2012-10-02 11:00        230      11500 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-10-02 12:00        237      11850 ORARZULP
2012-10-02 13:00        226      11300 ORARZULP
2012-10-02 14:00        142       7100 ORARZULP
2012-10-02 15:00        120       6000 ORARZULP
2012-10-02 16:00        116       5800 ORARZULP
2012-10-02 17:00        120       6000 ORARZULP
2012-10-02 18:00        118       5900 ORARZULP
2012-10-02 19:00        120       6000 ORARZULP
2012-10-02 20:00        120       6000 ORARZULP
2012-10-02 21:00        118       5900 ORARZULP
2012-10-02 22:00        121       6050 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-10-02 23:00        117       5850 ORARZULP
2012-10-03 00:00        129       6450 ORARZULP
2012-10-03 01:00        120       6000 ORARZULP
2012-10-03 02:00        120       6000 ORARZULP
2012-10-03 03:00        120       6000 ORARZULP
2012-10-03 04:00        118       5900 ORARZULP
2012-10-03 05:00        157       7850 ORARZULP
2012-10-03 06:00        165       8250 ORARZULP
2012-10-03 07:00        162       8100 ORARZULP
2012-10-03 08:00        118       5900 ORARZULP
2012-10-03 09:00        118       5900 ORARZULP

START_DATE START   NUM_LOGS     MBYTES DBNAME
---------- ----- ---------- ---------- ---------
2012-10-03 10:00        120       6000 ORARZULP
2012-10-03 11:00        147       7350 ORARZULP
2012-10-03 12:00        120       6000 ORARZULP
2012-10-03 13:00        118       5900 ORARZULP
2012-10-03 14:00        116       5800 ORARZULP
2012-10-03 15:00        120       6000 ORARZULP


Re: ARC file size too small [message #567646 is a reply to message #567635] Wed, 03 October 2012 23:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
my DBW process decided to terminate the instance. I think this is a result of too many log swirches.


DBWR generates a trace file when it terminates, have a look at it.

I already told you how to fix your problem, but, for the third time, increase the size of you redo logs and from your output I should say to something between 500MB and 1GB.

Regards
Michel

Re: ARC file size too small [message #567650 is a reply to message #567635] Thu, 04 October 2012 00:14 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You appear to be several G of redo an hour. I'm not sure your query is reporting what you tihnk it is, considering that you appear to be running a RAC (two threads?) and that your archive logs are, presumably, multiplexed.

But as Michel says, your online logs are too small. I would size them at 2G each instead of 50M, with 4 groups per thread not 2, and set archive_lag_target to 1200.
Re: ARC file size too small [message #567728 is a reply to message #567650] Thu, 04 October 2012 10:43 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
Thanks for your input. I found this online

http://divakarmehta.wordpress.com/2012/07/13/archive-redo-log-generated-half-the-size-redo-logs-in-11-2-database/

This is how I interpted it. Each thread saves 19mb space under no load so since my redo logs are only 50mb, it just continually does log switch cuz it's saying it's out of space
4(grps if 16 CPU's) x 19mb are larger than my 50MB of redo space.

My next step will be to follow the advice above and start slow by resizing each log to 500M instead of 50M, with 4 groups per thread not 2, and set archive_lag_target to 1200.

I will be certain to update this thread to show my results. Thank you all for you valuable input.

ARC file size too small [message #567743 is a reply to message #567728] Thu, 04 October 2012 18:55 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
I changed my online redo logs from 50MB to 250MB and created 6 groups and set archive_lag_target to 1200 than to 1800. This had no affect as my .arc files are still being created with very small sizes such as 50, 60, 100K.

My next step is to open an SR with Oracle to see if they can provide me with information.

ARC file size too small [message #567817 is a reply to message #567743] Fri, 05 October 2012 16:20 Go to previous message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
Excessive Redo log Switch In Rac Node [ID 1153582.1]

Following up with Oracle as this should be fixed in my version of the server.
Previous Topic: Redo log switches in every 3 mins
Next Topic: Temporary tablespace not seen on user_tablespaces
Goto Forum:
  


Current Time: Fri Mar 29 01:17:31 CDT 2024