Home » RDBMS Server » Server Administration » Excessive Archive logs creation (Oracle DB 11.2.0.3.0 - AIX 6.1)
Excessive Archive logs creation [message #614515] Thu, 22 May 2014 14:14 Go to next message
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
Re: Excessive Archive logs creation [message #614516 is a reply to message #614515] Thu, 22 May 2014 14:29 Go to previous messageGo to next message
BlackSwan
Messages: 26324
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.
Re: Excessive Archive logs creation [message #614517 is a reply to message #614516] Thu, 22 May 2014 14:47 Go to previous messageGo to next message
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.
Re: Excessive Archive logs creation [message #614518 is a reply to message #614517] Thu, 22 May 2014 14:54 Go to previous messageGo to next message
BlackSwan
Messages: 26324
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;
Re: Excessive Archive logs creation [message #614519 is a reply to message #614518] Thu, 22 May 2014 14:58 Go to previous messageGo to next message
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

Re: Excessive Archive logs creation [message #614520 is a reply to message #614519] Thu, 22 May 2014 15:04 Go to previous messageGo to next message
BlackSwan
Messages: 26324
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.
Re: Excessive Archive logs creation [message #614522 is a reply to message #614520] Thu, 22 May 2014 15:14 Go to previous messageGo to next message
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


Re: Excessive Archive logs creation [message #614523 is a reply to message #614522] Thu, 22 May 2014 15:18 Go to previous messageGo to next message
BlackSwan
Messages: 26324
Registered: January 2009
Location: SoCal
Senior Member
post SQL & EXPLAIN PLAN of the culprit
Re: Excessive Archive logs creation [message #614527 is a reply to message #614523] Thu, 22 May 2014 16:14 Go to previous messageGo to next message
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
Re: Excessive Archive logs creation [message #614532 is a reply to message #614527] Thu, 22 May 2014 17:15 Go to previous messageGo to next message
BlackSwan
Messages: 26324
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.
Re: Excessive Archive logs creation [message #614534 is a reply to message #614532] Thu, 22 May 2014 20:30 Go to previous messageGo to next message
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.


Re: Excessive Archive logs creation [message #614535 is a reply to message #614534] Thu, 22 May 2014 20:33 Go to previous messageGo to next message
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/
Re: Excessive Archive logs creation [message #614536 is a reply to message #614535] Thu, 22 May 2014 20:54 Go to previous messageGo to next message
BlackSwan
Messages: 26324
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.
Re: Excessive Archive logs creation [message #614537 is a reply to message #614536] Thu, 22 May 2014 22:12 Go to previous messageGo to next message
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.

Re: Excessive Archive logs creation [message #614538 is a reply to message #614537] Thu, 22 May 2014 22:36 Go to previous messageGo to next message
BlackSwan
Messages: 26324
Registered: January 2009
Location: SoCal
Senior Member
please post actual code & results where DBMS_LOGMNR was actually invoked.
Re: Excessive Archive logs creation [message #614581 is a reply to message #614538] Fri, 23 May 2014 08:34 Go to previous messageGo to next message
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; 





Re: Excessive Archive logs creation [message #614582 is a reply to message #614581] Fri, 23 May 2014 08:42 Go to previous messageGo to next message
BlackSwan
Messages: 26324
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?
Re: Excessive Archive logs creation [message #614584 is a reply to message #614582] Fri, 23 May 2014 09:06 Go to previous messageGo to next message
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.




Re: Excessive Archive logs creation [message #614586 is a reply to message #614584] Fri, 23 May 2014 09:18 Go to previous messageGo to next message
BlackSwan
Messages: 26324
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.
Re: Excessive Archive logs creation [message #614589 is a reply to message #614586] Fri, 23 May 2014 09:39 Go to previous message
nitek
Messages: 11
Registered: May 2014
Junior Member
Wrong answer but thanks for trying.
Previous Topic: Remote diagnostic agent
Next Topic: ALL DATAFILES ARE FUZZY
Goto Forum:
  


Current Time: Sun Dec 16 00:08:11 CST 2018