OraFAQ Forum: Server Administration » Excessive Archive logs creation

Home » RDBMS Server » Server Administration » Excessive Archive logs creation (Oracle DB 11.2.0.3.0 - AIX 6.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Excessive Archive logs creation [message #614515]

Thu, 22 May 2014 14:14

nitek
Messages: 11
Registered: May 2014

Junior Member

I have issue with our 11.2.3.0 DB on AIX 6.1 where archive logfiles created excessively.
We know what job causing it but it is a small job about 12K rows insert and update.
Using logminer, we only see about 400~500 ops but the logfile is 200M in size.
Here is the sample of the count of one logfile.

OPERATION,COUNT(*)
COMMIT,107
DELETE,15
INSERT,70
INTERNAL,31
START,107
UNSUPPORTED,88
UPDATE,18

Tables have no blob,long datatype. Just number or varchar2.

Anyone has similar issue or have any idea on how/where to check what the problem is ?
TIA,
Nick

Report message to a moderator

Re: Excessive Archive logs creation [message #614516 is a reply to message #614515]

Thu, 22 May 2014 14:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

a single INSERT could move gigabytes of data.

Report message to a moderator

Re: Excessive Archive logs creation [message #614517 is a reply to message #614516]

Thu, 22 May 2014 14:47

nitek
Messages: 11
Registered: May 2014

Junior Member

Thanks, BlackSwan. I will try to format the information.
Regarding your response "a single INSERT could move gigabytes of data", I did apply the sql_undo and it was definitely not moving gigabytes of data.

Report message to a moderator

Re: Excessive Archive logs creation [message #614518 is a reply to message #614517]

Thu, 22 May 2014 14:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post results from SQL below

SELECT to_char(first_time , 'YYYY-MM-DD HH24:MI')
FROM v$loghist
WHERE first_time > SYSDATE - 2
ORDER BY 1;

Report message to a moderator

Re: Excessive Archive logs creation [message #614519 is a reply to message #614518]

Thu, 22 May 2014 14:58

nitek
Messages: 11
Registered: May 2014

Junior Member

Here we go

TO_CHAR(FIRST_TIME,'YYYY-MM-DDHH24:MI')
2014-05-20 14:56
2014-05-20 14:58
2014-05-20 14:59
2014-05-20 15:00
2014-05-20 15:01
2014-05-20 15:02
2014-05-20 15:03
2014-05-20 15:04
2014-05-20 15:06
2014-05-20 15:07
2014-05-20 15:08
2014-05-20 15:09
2014-05-20 15:10
2014-05-20 15:11
2014-05-20 15:13
2014-05-20 15:14
2014-05-20 15:15
2014-05-20 15:16
2014-05-20 15:17
2014-05-20 15:18
2014-05-20 15:19
2014-05-20 15:21
2014-05-20 15:22
2014-05-20 15:23
2014-05-20 15:24
2014-05-20 15:25
2014-05-20 15:26
2014-05-20 15:28
2014-05-20 15:29
2014-05-20 15:30
2014-05-20 15:31
2014-05-20 15:32
2014-05-20 15:33
2014-05-20 15:34
2014-05-20 15:36
2014-05-20 15:37
2014-05-20 15:38
2014-05-20 15:39
2014-05-20 15:40
2014-05-20 15:41
2014-05-20 15:43
2014-05-20 15:44
2014-05-20 15:45
2014-05-20 15:46
2014-05-20 15:47
2014-05-20 15:48
2014-05-20 15:50
2014-05-20 15:51
2014-05-20 15:52
2014-05-20 15:53
2014-05-20 15:54
2014-05-20 15:55
2014-05-20 15:57
2014-05-20 15:58
2014-05-20 15:59
2014-05-20 16:00
2014-05-20 16:01
2014-05-20 16:02
2014-05-20 16:03
2014-05-20 16:05
2014-05-20 16:06
2014-05-20 16:07
2014-05-20 16:08
2014-05-20 16:09
2014-05-20 16:10
2014-05-20 16:11
2014-05-20 16:13
2014-05-20 16:14
2014-05-20 16:15
2014-05-20 16:16
2014-05-20 16:17
2014-05-20 16:18
2014-05-20 16:19
2014-05-20 16:24
2014-05-20 16:41
2014-05-20 17:17
2014-05-20 22:08
2014-05-21 04:00
2014-05-21 07:46
2014-05-21 09:09
2014-05-21 09:40
2014-05-21 10:20
2014-05-21 10:21
2014-05-21 10:25
2014-05-21 10:29
2014-05-21 10:29
2014-05-21 10:29
2014-05-21 10:30
2014-05-21 10:30
2014-05-21 10:30
2014-05-21 10:31
2014-05-21 10:41
2014-05-21 10:46
2014-05-21 10:46
2014-05-21 11:13
2014-05-21 11:13
2014-05-21 11:13
2014-05-21 11:13
2014-05-21 11:13
2014-05-21 11:14
2014-05-21 11:14
2014-05-21 11:14
2014-05-21 11:14
2014-05-21 11:14
2014-05-21 11:14
2014-05-21 11:14
2014-05-21 11:14
2014-05-21 11:14
2014-05-21 12:40
2014-05-21 12:40
2014-05-21 12:43
2014-05-21 12:43
2014-05-21 12:44
2014-05-21 12:44
2014-05-21 15:00
2014-05-21 15:01
2014-05-21 16:13
2014-05-21 16:18
2014-05-21 23:25
2014-05-22 07:00
2014-05-22 08:57

Report message to a moderator

Re: Excessive Archive logs creation [message #614520 is a reply to message #614519]

Thu, 22 May 2014 15:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It appears that the REDO log files are way too under sized.
It appears that this database processes bursts of heavy DML activity.
DBMS_LOGMNR should be able to identify the tables & SQL that are the culprits.

Report message to a moderator

Re: Excessive Archive logs creation [message #614522 is a reply to message #614520]

Thu, 22 May 2014 15:14

nitek
Messages: 11
Registered: May 2014

Junior Member

As I said, we know the query and the job. But we can't explain why it generated many log files.
We did use log miner and as I showed below, there are not many ops but each file is around 200M.

select operation,count(*) from v$logmnr_contents group by operation;

OPERATION   COUNT(*)
COMMIT      107
DELETE      15
INSERT      70
INTERNAL    31
START       107
UNSUPPORTED 88
UPDATE      18

Report message to a moderator

Re: Excessive Archive logs creation [message #614523 is a reply to message #614522]

Thu, 22 May 2014 15:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post SQL & EXPLAIN PLAN of the culprit

Report message to a moderator

Re: Excessive Archive logs creation [message #614527 is a reply to message #614523]

Thu, 22 May 2014 16:14

nitek
Messages: 11
Registered: May 2014

Junior Member


INSERT INTO param("APPR_NO","PROGRAM_ID","PARAM_NAME")
SELECT '-','NEW_24',rpt_doc_id from ts_spec 
WHERE rpt_doc_id = 'SPEC_ID_1234'

PLAN_TABLE_OUTPUT
 
-------------------------------------------------------------------------
| Id  | Operation                | Name         | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |              |   473 |  6149 |     7 |
|   1 |  LOAD TABLE CONVENTIONAL | PARAM        |       |       |       |
|   2 |   INDEX RANGE SCAN       | TS_SPEC_IDX1 |   473 |  6149 |     7 |
-------------------------------------------------------------------------
 
Note
-----
   - 'PLAN_TABLE' is old version

Report message to a moderator

Re: Excessive Archive logs creation [message #614532 is a reply to message #614527]

Thu, 22 May 2014 17:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

forgive me, but there is a serious disconnect between what you report in this thread.
INSERT of 473 rows won't generate multiple REDO log switches in less than 1 minute; so I assume that you have identified the wrong statement.

I suggest that since this is a recurring problem, that you enable SQL_TRACE=TRUE to see what really occurs within the database.

> - 'PLAN_TABLE' is old version
you should be using the "new" plan table which provides more detailed results.

Report message to a moderator

Re: Excessive Archive logs creation [message #614534 is a reply to message #614532]

Thu, 22 May 2014 20:30

nitek
Messages: 11
Registered: May 2014

Junior Member

Don't misunderstand, the explain plan didn't give you exact # of rows. The query returned over 4K rows.
At the time when we ran the job, it was 12K rows.
We have tools to collect data every second. Even though we can run this query and it is done in a few seconds in sqlplus, it was hours and there were a lot of archive logs created.
I was not asking about the performance of the query or such. I just wonder if anyone could have any idea why there is a big gap between the logminer data and archive logfile size.

Report message to a moderator

Re: Excessive Archive logs creation [message #614535 is a reply to message #614534]

Thu, 22 May 2014 20:33

nitek
Messages: 11
Registered: May 2014

Junior Member

By the way, the tool we use is called Maxgauge. You can read for some information about what it does here:
http://dioncho.wordpress.com/maxgauge-the-most-efficient-oracle-performance-monitoring-tool-ever-existed/

Report message to a moderator

Re: Excessive Archive logs creation [message #614536 is a reply to message #614535]

Thu, 22 May 2014 20:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> I just wonder if anyone could have any idea why there is a big gap between the logminer data and archive logfile size.
There is no human intervention involved with archive logfile size.
with DBMS_LOGMNR it is only as good as the person using it. It could be a case of Garbage In, Garbage Out.

please post actual code & results where DBMS_LOGMNR was actually invoked.

Problem Exists Between Keyboard And Chair.

Report message to a moderator

Re: Excessive Archive logs creation [message #614537 is a reply to message #614536]

Thu, 22 May 2014 22:12

nitek
Messages: 11
Registered: May 2014

Junior Member

Quote:

There is no human intervention involved with archive logfile size.

I understand this. I didn't ask about intervention. I am asking for ideas, possible explanation. Doesn't have to be a solution.

Quote:

Problem Exists Between Keyboard And Chair.

Totally agree.

I was hoping there is someone has some experience in using log miner to discuss about this.

Report message to a moderator

Re: Excessive Archive logs creation [message #614538 is a reply to message #614537]

Thu, 22 May 2014 22:36

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

please post actual code & results where DBMS_LOGMNR was actually invoked.

Report message to a moderator

Re: Excessive Archive logs creation [message #614581 is a reply to message #614538]

Fri, 23 May 2014 08:34

nitek
Messages: 11
Registered: May 2014

Junior Member

look like we are heading the right direction.
Here we go

1) Look for the logfile that we are interested in

SELECT sequence#,name,first_time 
FROM v$archived_log
WHERE 1=1 
AND first_time BETWEEN TO_DATE('20140520142900','YYYYMMDDHH24MISS') AND TO_DATE('20140520143022','YYYYMMDDHH24MISS')
AND deleted='NO'
AND registrar='RMAN'
ORDER BY sequence#;


SEQUENCE#           NAME                                                  FIRST_TIME
4880                /tmprestore/SPEC_1_4880_831555569.dbf                5/20/2014 2:29:14 PM

2) Check for the file on server to make sure it is there

spec> ls -ltr *4880*
-rw-r-----    1 oracle   dba       181404672 May 21 15:36 SPEC_1_4880_831555569.dbf

3) Use DBMS_LOGMNR

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';


EXECUTE DBMS_LOGMNR.START_LOGMNR( STARTTIME => '20-May-2014 14:29:15', ENDTIME => '20-May-2014 14:30:22', 
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);

4) Make sure we load the right file

SELECT log_id,filename,low_time,high_time 
FROM v$logmnr_logs ;

LOG_ID  FILENAME                                  LOW_TIME              HIGH_TIME
4880    /tmprestore/SPEC_1_4880_831555569.dbf    5/20/2014 2:29:14 PM  5/20/2014 2:30:23 PM

5) Let see how many ops

SELECT operation,count(*) FROM v$logmnr_contents 
GROUP BY operation ;

OPERATION      COUNT(*)
COMMIT         107
DELETE         15
INSERT         70
INTERNAL       31
START          107
UNSUPPORTED    88
UPDATE         18

6) Let's stop DBMS_LOGMNR

EXEC DBMS_LOGMNR.END_LOGMNR;

Report message to a moderator

Re: Excessive Archive logs creation [message #614582 is a reply to message #614581]

Fri, 23 May 2014 08:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Why are you obsessed with Operation counts?
Seeing COUNT values won't tell which DML produced all the REDO!

If a single DML starts at 14:56 May 20 & terminates 16:24 May 20, which archived redo log file contains the DML statement that resulted in over 70 redo switches?

What is/are the DML statements that generated 70+ archived files?

Report message to a moderator

Re: Excessive Archive logs creation [message #614584 is a reply to message #614582]

Fri, 23 May 2014 09:06

nitek
Messages: 11
Registered: May 2014

Junior Member

The purpose of using DBMS_LOGMNR to find out what transactions (changes) are in the archive log.
The count would tell you how many transactions are in that archive log. If you want to restore a certain transaction, you can use these information.

If a single DML starts at 14:56 May 20 & terminates 16:24 May 20, which archived redo log file contains the DML statement that resulted in over 70 redo switches?

The archive log that I just did a count on.

What is/are the DML statements that generated 70+ archived files?

The statement should be in the archive log that we mined.

But my question is not about the statement. I've never asked to find the statement to start off with.

Report message to a moderator

Re: Excessive Archive logs creation [message #614586 is a reply to message #614584]

Fri, 23 May 2014 09:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Anyone has similar issue or have any idea on how/where to check what the problem is ?
There is no problem.
DML generates REDO.
If you do not want many archived REDO file, then reduce DML activity.

Report message to a moderator

Re: Excessive Archive logs creation [message #614589 is a reply to message #614586]

Fri, 23 May 2014 09:39

nitek
Messages: 11
Registered: May 2014

Junior Member

Wrong answer but thanks for trying.

Report message to a moderator

Previous Topic:	Remote diagnostic agent
Next Topic:	ALL DATAFILES ARE FUZZY

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 01 23:53:52 CST 2025