OraFAQ Forum: Backup & Recovery » Archive logs too big in size

Home » RDBMS Server » Backup & Recovery » Archive logs too big in size (Oracle 9.2.0.6.0 )

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

Archive logs too big in size [message #444739]

Tue, 23 February 2010 22:14

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Can someone help in suggesting ways to reduce archive log getting written ?

We have a server that is replicating data from several servers through Mviews. 200+ Mviews are getting refreshed every 15 mins, and 100-120 GB of archive log is getting generated.

No. of rows affected will be in range of 1-2 Lakhs every time.

Is any solution to reduce this 100-120 Gb of archiving, as due to constraint of disk space DBA needs to remove these logs every 2-3 hrs.

pls help

Regards,
Rajat

Report message to a moderator

Re: Archive logs too big in size [message #444742 is a reply to message #444739]

Tue, 23 February 2010 22:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Can someone help in suggesting ways to reduce archive log getting written ?

The only way is to reduce DML (INSERT, UPDATE, & DELETE) operations

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

[Updated on: Tue, 23 February 2010 22:18]

Report message to a moderator

Re: Archive logs too big in size [message #444747 is a reply to message #444742]

Tue, 23 February 2010 22:54

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Sorry BlackSwan for the inconvenience caused.

Please find the oracle version [9.2.0.6.0]
Kindly delete the duplicate post.

As i checked i come up with this option

1) Set Mview/Tables in NOLOGGING mode.

Is there any other options available.
We can't reduce the DML operations as per business requirement.

Regards,
Rajat Ratewal

Report message to a moderator

Re: Archive logs too big in size [message #444749 is a reply to message #444747]

Tue, 23 February 2010 22:57

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>We can't reduce the DML operations as per business requirement.
DML generates REDO.

Report message to a moderator

Re: Archive logs too big in size [message #444751 is a reply to message #444749]

Tue, 23 February 2010 23:05

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

BlackSwan,

So you are trying to say that there is no way to reduce
archive log. only option is to order disk purchase.

What i am trying to find out is alternative and better option.

Anyways thanks for the help but it didn't resolve the query.

Regards,
Rajat

Report message to a moderator

Re: Archive logs too big in size [message #444752 is a reply to message #444751]

Tue, 23 February 2010 23:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> only option is to order disk purchase.
When I can buy a 2TB unit for less than $200 USDollar, I would not waste time exploring other options which place my data integrity at risk.

Oracle guarentees data integrity when no short cuts are taken.

Report message to a moderator

Re: Archive logs too big in size [message #444855 is a reply to message #444739]

Wed, 24 February 2010 09:23

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Just to add,
It depends on how you refresh.
With incremental refresh, you cannot avoid it.
With full refresh, internally Oracle will append (in 9i, not in 10g) and thus reducing the redo . Still this will have no effect on indexes.

Report message to a moderator

Re: Archive logs too big in size [message #444920 is a reply to message #444855]

Wed, 24 February 2010 14:43

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

>>Mviews are getting refreshed every 15 mins, and 100-120 GB of archive log is getting generated.

If every 15 min. huge number archive generate then try to control using ARCHIVE_LAG_TARGET parameter.

- Babu

Report message to a moderator

Re: Archive logs too big in size [message #444928 is a reply to message #444739]

Wed, 24 February 2010 20:03

hkchital
Messages: 128
Registered: September 2008
Location: Singapore

Senior Member

Are the REFRESHs COMPLETE or FAST ?

Are rows being deleted from the *source* Tables ? Oracle would have to repeat the deletes in the target MVs as well.

Hemant K Chitale

Report message to a moderator

Previous Topic:	restore rman backup 10 days ago (merged 5)
Next Topic:	Clone from ASM to non ASM.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 20:41:08 CST 2024