Home » RDBMS Server » Backup & Recovery » Archive logs too big in size (Oracle 9.2.0.6.0 )
Archive logs too big in size [message #444739] Tue, 23 February 2010 22:14 Go to next message
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
Re: Archive logs too big in size [message #444742 is a reply to message #444739] Tue, 23 February 2010 22:17 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
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 Go to previous messageGo to next message
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
Re: Archive logs too big in size [message #444749 is a reply to message #444747] Tue, 23 February 2010 22:57 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>We can't reduce the DML operations as per business requirement.
DML generates REDO.
Re: Archive logs too big in size [message #444751 is a reply to message #444749] Tue, 23 February 2010 23:05 Go to previous messageGo to next message
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
Re: Archive logs too big in size [message #444752 is a reply to message #444751] Tue, 23 February 2010 23:19 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
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.
Re: Archive logs too big in size [message #444855 is a reply to message #444739] Wed, 24 February 2010 09:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10671
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.
Re: Archive logs too big in size [message #444920 is a reply to message #444855] Wed, 24 February 2010 14:43 Go to previous messageGo to next message
babuknb
Messages: 1734
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
Re: Archive logs too big in size [message #444928 is a reply to message #444739] Wed, 24 February 2010 20:03 Go to previous message
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
Previous Topic: restore rman backup 10 days ago (merged 5)
Next Topic: Clone from ASM to non ASM.
Goto Forum:
  


Current Time: Thu Sep 29 07:21:34 CDT 2016

Total time taken to generate the page: 0.07178 seconds