OraFAQ Forum: Server Administration » Oracle 9i: Proliferating .arc Files (long)

Home » RDBMS Server » Server Administration » Oracle 9i: Proliferating .arc Files (long)

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

Oracle 9i: Proliferating .arc Files (long) [message #256419]

Sat, 04 August 2007 11:01

jpike
Messages: 4
Registered: August 2007
Location: Massachusetts

Junior Member

If I have posted in the wrong section, please move this message to the correct one.

I am a librarian and network administrator at a small public library in Massachusetts. We use software to run the library that incorporates an Oracle 9i database. I know little to nothing about using Oracle. I have a situation about which my vendor is being unhelpful, so I'm trying other sources to get an explanation. If this message board is not an appropriate place to try to find this answer, I will not be offended to be told so. Thank-you.

Library catalogs have two kinds of records, bibliographic records which attach to items in the library and authority records which contain the official versions of peoples' names, place names, and subjects, among others. So the bibliographic record for a book about John F. Kennedy, for example, would be linked to the authority record for John F. Kennedy to assure that his name in the bibliographic record appeared correctly when it came up in the library's catalog. The majority of authority records used in public libraries are created by the Library of Congress (LOC).

Last week I ran a utility that compared our local authority records with those in the LOC and marked for update any local records that were different from the LOC's. It found 9300. The next day, I ran another utility that updated all of the records. Then I ran the comparison utility once more. Finally, I ran a third utility that deleted from the database all authority records that were not linked to any bibliographic records.

Since running the utilities, Oracle has gone from generating a few dozen .arc files each day to generating hundreds of them (in the \oracle\database\archive folder). My tape backup cannot accommodate them, so the backup ends prematurely, and the creation of the files is interrupting other parts of the library software. A librarian checking out a book to a patron, for example, may have to wait several seconds after scanning a barcode for the transaction to complete. When this happens dozens of times per day, it becomes more than an annoyance.

My vendor acknowledges that my having run the utilities caused the proliferation of .arc files, but insists that the processes that are generating them cannot be stopped. My questions are, why not, and, since the processes have completed their tasks, why are they still even running? Or is my vendor just obfuscating?

This is on Windows Server 2003. I have the SQL file for the utility that deleted the unlinked records, and can post it, if that would help.

Jeffrey Pike

Report message to a moderator

Re: Oracle 9i: Proliferating .arc Files (long) [message #256421 is a reply to message #256419]

Sat, 04 August 2007 11:17

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
Check your alert<sid>.log file and post here last few lines from alert<sid>.log file.

.ARC file is offline copy of redo log files
it is used for recovery purpose.

Regards
Taj

Report message to a moderator

Re: Oracle 9i: Proliferating .arc Files (long) [message #256422 is a reply to message #256421]

Sat, 04 August 2007 11:51

jpike
Messages: 4
Registered: August 2007
Location: Massachusetts

Junior Member

Thank-you for the information, Taj. Here's a snippet from the alert file:

Sat Aug 04 12:41:02 2007
Thread 1 advanced to log sequence 592572
Current log# 7 seq# 592572 mem# 0: D:\ORACLE\DATABASE\LOG7AL3D2.ORA
Current log# 7 seq# 592572 mem# 1: D:\ORACLE\DATABASE\LOG7BL3D2.ORA
Sat Aug 04 12:41:02 2007
ARC0: Evaluating archive log 6 thread 1 sequence 592571
ARC0: Beginning to archive log 6 thread 1 sequence 592571
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\DATABASE\ARCHIVE\ARCH592571.ARC'
ARC0: Completed archiving log 6 thread 1 sequence 592571

Report message to a moderator

Re: Oracle 9i: Proliferating .arc Files (long) [message #256424 is a reply to message #256419]

Sat, 04 August 2007 12:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I suspect that Taj was looking for a slightly larger snippet in order to see how frequently the log files are switching.
Also it would be useful if you could post the size of a few of the *ARC files.

Report message to a moderator

Re: Oracle 9i: Proliferating .arc Files (long) [message #256434 is a reply to message #256422]

Sat, 04 August 2007 13:22

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Put the result of the following script (to be executed in SQL*Plus with a DBA user):

Column nop   noprint
Column jour  format a14     heading "Date"
Column total format 999G990 heading "Total"
Column h00   format a03     heading "H00" trunc
Column h01   format a03     heading "H01" trunc
Column h02   format a03     heading "H02" trunc
Column h03   format a03     heading "H03" trunc
Column h04   format a03     heading "H04" trunc
Column h05   format a03     heading "H05" trunc
Column h06   format a03     heading "H06" trunc
Column h07   format a03     heading "H07" trunc
Column h08   format a03     heading "H08" trunc
Column h09   format a03     heading "H09" trunc
Column h10   format a03     heading "H10" trunc
Column h11   format a03     heading "H11" trunc
Column h12   format a03     heading "H12" trunc
Column h13   format a03     heading "H13" trunc
Column h14   format a03     heading "H14" trunc
Column h15   format a03     heading "H15" trunc
Column h16   format a03     heading "H16" trunc
Column h17   format a03     heading "H17" trunc
Column h18   format a03     heading "H18" trunc
Column h19   format a03     heading "H19" trunc
Column h20   format a03     heading "H20" trunc
Column h21   format a03     heading "H21" trunc
Column h22   format a03     heading "H22" trunc
Column h23   format a03     heading "H23" trunc

Prompt
Spool logSwitch
Select trunc(first_time) nop, 
       to_char(first_time, 'Dy DD/MM/YYYY') jour,
       count(*) total,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'fm999'),3),'  0','   ') h00,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'fm999'),3),'  0','   ') h01,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'fm999'),3),'  0','   ') h02,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'fm999'),3),'  0','   ') h03,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'fm999'),3),'  0','   ') h04,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'fm999'),3),'  0','   ') h05,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'fm999'),3),'  0','   ') h06,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'fm999'),3),'  0','   ') h07,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'fm999'),3),'  0','   ') h08,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'fm999'),3),'  0','   ') h09,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'fm999'),3),'  0','   ') h10,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'fm999'),3),'  0','   ') h11,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'fm999'),3),'  0','   ') h12,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'fm999'),3),'  0','   ') h13,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'fm999'),3),'  0','   ') h14,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'fm999'),3),'  0','   ') h15,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'fm999'),3),'  0','   ') h16,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'fm999'),3),'  0','   ') h17,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'fm999'),3),'  0','   ') h18,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'fm999'),3),'  0','   ') h19,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'fm999'),3),'  0','   ') h20,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'fm999'),3),'  0','   ') h21,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'fm999'),3),'  0','   ') h22,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'fm999'),3),'  0','   ') h23
from v$log_history
where first_time between to_date('&first_date','DD/MM/YYYY')
                 and to_date('&last_date','DD/MM/YYYY')
group by trunc(first_time), to_char(first_time, 'Dy DD/MM/YYYY')
order by 1
/

It ask for first_date and last_date in format 'DD/MM/YYYY', give him as first date a day before the beginning of the increase of arc generation and today as last day.

Regards
Michel

Report message to a moderator

Re: Oracle 9i: Proliferating .arc Files (long) [message #256435 is a reply to message #256419]

Sat, 04 August 2007 13:24

jpike
Messages: 4
Registered: August 2007
Location: Massachusetts

Junior Member

OK, thank-you for the tip. The .arc files are always more or less the same size each day, but differ in size from day to day. Today, they are 5.1 Mb. A couple of days ago, they were about 1.5 Mb. I haven't tracked whether the trend is bigger each day.

Here are the last two pages of the alert file:

Thread 1 advanced to log sequence 592602
Current log# 1 seq# 592602 mem# 0: D:\ORACLE\DATABASE\LOG1AL3D2.ORA
Current log# 1 seq# 592602 mem# 1: D:\ORACLE\DATABASE\LOG1BL3D2.ORA
Sat Aug 04 13:50:59 2007
ARC0: Evaluating archive log 10 thread 1 sequence 592601
ARC0: Beginning to archive log 10 thread 1 sequence 592601
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\DATABASE\ARCHIVE\ARCH592601.ARC'
ARC0: Completed archiving log 10 thread 1 sequence 592601
Sat Aug 04 13:56:49 2007
Thread 1 advanced to log sequence 592603
Current log# 2 seq# 592603 mem# 0: D:\ORACLE\DATABASE\LOG2AL3D2.ORA
Sat Aug 04 13:56:49 2007
Current log# 2 seq# 592603 mem# 1: D:\ORACLE\DATABASE\LOG2BL3D2.ORA
Sat Aug 04 13:56:49 2007
ARC0: Evaluating archive log 1 thread 1 sequence 592602
Sat Aug 04 13:56:49 2007
ARC0: Beginning to archive log 1 thread 1 sequence 592602
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\DATABASE\ARCHIVE\ARCH592602.ARC'
ARC0: Completed archiving log 1 thread 1 sequence 592602
Sat Aug 04 14:00:21 2007
Thread 1 advanced to log sequence 592604
Current log# 3 seq# 592604 mem# 0: D:\ORACLE\DATABASE\LOG3AL3D2.ORA
Current log# 3 seq# 592604 mem# 1: D:\ORACLE\DATABASE\LOG3BL3D2.ORA
Sat Aug 04 14:00:21 2007
ARC0: Evaluating archive log 2 thread 1 sequence 592603
ARC0: Beginning to archive log 2 thread 1 sequence 592603
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\DATABASE\ARCHIVE\ARCH592603.ARC'
ARC0: Completed archiving log 2 thread 1 sequence 592603
Sat Aug 04 14:00:36 2007
Thread 1 advanced to log sequence 592605
Current log# 4 seq# 592605 mem# 0: D:\ORACLE\DATABASE\LOG4AL3D2.ORA
Sat Aug 04 14:00:36 2007
ARC0: Evaluating archive log 3 thread 1 sequence 592604
Sat Aug 04 14:00:36 2007
Current log# 4 seq# 592605 mem# 1: D:\ORACLE\DATABASE\LOG4BL3D2.ORA
Sat Aug 04 14:00:36 2007
ARC0: Beginning to archive log 3 thread 1 sequence 592604
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\DATABASE\ARCHIVE\ARCH592604.ARC'
Sat Aug 04 14:00:37 2007
ARC1: Evaluating archive log 3 thread 1 sequence 592604
ARC1: Unable to archive log 3 thread 1 sequence 592604
Log actively being archived by another process
Sat Aug 04 14:00:38 2007
ARC0: Completed archiving log 3 thread 1 sequence 592604
Sat Aug 04 14:00:50 2007
Thread 1 advanced to log sequence 592606
Current log# 5 seq# 592606 mem# 0: D:\ORACLE\DATABASE\LOG5AL3D2.ORA
Current log# 5 seq# 592606 mem# 1: D:\ORACLE\DATABASE\LOG5BL3D2.ORA
Sat Aug 04 14:00:50 2007
ARC0: Evaluating archive log 4 thread 1 sequence 592605
ARC0: Beginning to archive log 4 thread 1 sequence 592605
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\DATABASE\ARCHIVE\ARCH592605.ARC'
ARC0: Completed archiving log 4 thread 1 sequence 592605
Sat Aug 04 14:01:01 2007
Thread 1 advanced to log sequence 592607
Current log# 6 seq# 592607 mem# 0: D:\ORACLE\DATABASE\LOG6AL3D2.ORA
Current log# 6 seq# 592607 mem# 1: D:\ORACLE\DATABASE\LOG6BL3D2.ORA
Sat Aug 04 14:01:03 2007
ARC0: Evaluating archive log 5 thread 1 sequence 592606
ARC0: Beginning to archive log 5 thread 1 sequence 592606
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\DATABASE\ARCHIVE\ARCH592606.ARC'
ARC0: Completed archiving log 5 thread 1 sequence 592606
Sat Aug 04 14:10:11 2007
Thread 1 advanced to log sequence 592608
Current log# 7 seq# 592608 mem# 0: D:\ORACLE\DATABASE\LOG7AL3D2.ORA
Current log# 7 seq# 592608 mem# 1: D:\ORACLE\DATABASE\LOG7BL3D2.ORA
Sat Aug 04 14:10:11 2007
ARC0: Evaluating archive log 6 thread 1 sequence 592607
ARC0: Beginning to archive log 6 thread 1 sequence 592607
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\DATABASE\ARCHIVE\ARCH592607.ARC'
ARC0: Completed archiving log 6 thread 1 sequence 592607
Sat Aug 04 14:10:27 2007
Thread 1 advanced to log sequence 592609
Current log# 8 seq# 592609 mem# 0: D:\ORACLE\DATABASE\LOG8AL3D2.ORA
Current log# 8 seq# 592609 mem# 1: D:\ORACLE\DATABASE\LOG8BL3D2.ORA
Sat Aug 04 14:10:29 2007
ARC0: Evaluating archive log 7 thread 1 sequence 592608
ARC0: Beginning to archive log 7 thread 1 sequence 592608
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\DATABASE\ARCHIVE\ARCH592608.ARC'
ARC0: Completed archiving log 7 thread 1 sequence 592608
Sat Aug 04 14:10:38 2007
Thread 1 advanced to log sequence 592610
Current log# 11 seq# 592610 mem# 0: D:\ORACLE\DATABASE\LOG11AL3D2.ORA
Current log# 11 seq# 592610 mem# 1: D:\ORACLE\DATABASE\LOG11BL3D2.ORA
Sat Aug 04 14:10:38 2007
ARC0: Evaluating archive log 8 thread 1 sequence 592609
ARC0: Beginning to archive log 8 thread 1 sequence 592609
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\DATABASE\ARCHIVE\ARCH592609.ARC'
Sat Aug 04 14:10:38 2007
ARC1: Evaluating archive log 8 thread 1 sequence 592609
ARC1: Unable to archive log 8 thread 1 sequence 592609
Log actively being archived by another process
Sat Aug 04 14:10:42 2007
ARC0: Completed archiving log 8 thread 1 sequence 592609
Sat Aug 04 14:10:55 2007
Thread 1 advanced to log sequence 592611
Current log# 12 seq# 592611 mem# 0: D:\ORACLE\DATABASE\LOG12AL3D2.ORA
Current log# 12 seq# 592611 mem# 1: D:\ORACLE\DATABASE\LOG12BL3D2.ORA
Sat Aug 04 14:10:55 2007
ARC0: Evaluating archive log 11 thread 1 sequence 592610
ARC0: Beginning to archive log 11 thread 1 sequence 592610
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\DATABASE\ARCHIVE\ARCH592610.ARC'
ARC0: Completed archiving log 11 thread 1 sequence 592610

Report message to a moderator

Re: Oracle 9i: Proliferating .arc Files (long) [message #256437 is a reply to message #256435]

Sat, 04 August 2007 13:27

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Post the result of "show parameter checkpoint" and "show parameter archive_lag".

Regards
Michel

Report message to a moderator

Re: Oracle 9i: Proliferating .arc Files (long) [message #256468 is a reply to message #256419]

Sat, 04 August 2007 23:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Sat Aug 04 13:50:59 2007
>ARC0: Evaluating archive log 10 thread 1 sequence 592601
>ARC0: Beginning to archive log 10 thread 1 sequence 592601
>Creating archive destination >LOG_ARCHIVE_DEST_1: 'D:\ORACLE\DATABASE\ARCHIVE\ARCH592601.ARC'
....
>Sat Aug 04 14:10:55 2007
>ARC0: Evaluating archive log 11 thread 1 sequence 592610
>ARC0: Beginning to archive log 11 thread 1 sequence 592610
>Creating archive destination >LOG_ARCHIVE_DEST_1: 'D:\ORACLE\DATABASE\ARCHIVE\ARCH592610.ARC'

My eyeball sees 10 log switches in about 20 minutes.
IMO, a single log switch should occur no quicker than 1 every 15 minutes (or more).
If 1 log switch every 15+ minutes is a reasonable metric, then your log file size needs to be increased by about a factor of 10.
I doubt increasing REDO log file size by 10 times will solve all your problems, but it can't/won't make things worse.

Report message to a moderator

Re: Oracle 9i: Proliferating .arc Files (long) [message #256806 is a reply to message #256434]

Mon, 06 August 2007 13:46

jpike
Messages: 4
Registered: August 2007
Location: Massachusetts

Junior Member

Michel, thank-you for the script. I will have to see if my vendor will give me login name and password for SQL *Plus, and then I will give it a try.

And anacedent I will ask my vendor about increasing the log file size. THanks for the tip.

Jeffrey Pike

Report message to a moderator

Previous Topic:	Oracle product information
Next Topic:	database account is locked

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 14:02:46 CDT 2024