Home » RDBMS Server » Server Administration » Archive Logs are filling fast (11g)
Archive Logs are filling fast [message #642477] Fri, 11 September 2015 08:37 Go to next message
karthik4u
Messages: 36
Registered: September 2006
Location: CHENNAI
Member
Hi All,

Archive logs are filling fast and fills the hard disk. Need to clear .arc files periodically for smooth run of system.

On analysis .arc files using log miner found certain tables which are involved in more block changes and confirmed the same from AWR report.

So decided to set NOLOGGING option for those tables (10 to 12 tables).

Even after setting .arc files are filling with these loggings. On further analysis found that 'force_logging' is YES at Database level which overrides the NOLOGGING options at table level.

Can we set 'force_logging' to NO at Database level in Archive log mode?

Otherwise how to reduce the huge number of .arc files creations?

Kindly advise.
Re: Archive Logs are filling fast [message #642478 is a reply to message #642477] Fri, 11 September 2015 08:41 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
>Otherwise how to reduce the huge number of .arc files creations?

SHUTDOWN IMMEDIATE

above will reduce huge number of REDO files effective immediately

DML causes REDO; so reduce or eliminate DML
Re: Archive Logs are filling fast [message #642482 is a reply to message #642478] Fri, 11 September 2015 09:09 Go to previous messageGo to next message
Flyby
Messages: 185
Registered: March 2011
Location: Belgium
Senior Member
as above DML causes REDO; so reduce or eliminate DML
backup the arclogs more frequently
FORCE_LOGGING is probably on to enable restore to point in time operations.
Re: Archive Logs are filling fast [message #642485 is a reply to message #642477] Fri, 11 September 2015 09:51 Go to previous messageGo to next message
John Watson
Messages: 7262
Registered: January 2010
Location: Global Village
Senior Member
Setting NOLOGGING has no effect for normal DML, no matter what you do with the FORCE LOGGING database setting.
If your DBA has enabled FORCE LOGGING, he (presumably) has a good reason; perhaps you are using Data Guard or GoldenGate.

The BlackSwan is correct: the only way to reduce redo generation is to do less work. Failing that, just backup and remove your archivelog files more frequently. Do it every hour hour, perhaps, instead of twice a day.
Re: Archive Logs are filling fast [message #642520 is a reply to message #642485] Sun, 13 September 2015 21:51 Go to previous messageGo to next message
trantuananh24hg
Messages: 709
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
I do not agree to reduce the DML statement, because of the design in Application.

Case 1)

If the Application as good as possible, then, tuning the Database.

Tracing the redo log switch, top 5 events and can show the wait event "log sync .. commited". Now, how are the redo log members size and how many group, is there in RAC or single ...?

Case 2)

If the Application was modified and invoking the new deploying store procedure/function ... to make an complicated way to finish, then, see at the top segments, top procedure. There are some way to reduce the hot-block but can not reduce the total summary of DML every time. Why do you try to reduce the DML but not Application tuning?

Example:

You have got a big heap table contains the temporary data which do effective by DML because of changing the data from customer daily. In stead of doing the back-end DML in Database, it is very heavy and cause poor performance, considering the InMemory Database. The heap big table can be changed from heap to partitioned table but the code line may be re-written by Dev, it is very dangerous. Remember, the DML changes the data and do it because of application's purpose.
Re: Archive Logs are filling fast [message #642522 is a reply to message #642520] Sun, 13 September 2015 22:15 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
>Tracing the redo log switch, top 5 events and can show the wait event "log sync .. commited".
>Now, how are the redo log members size and how many group, is there in RAC or single ...?

Changing the number or size of the REDO log files; has ZERO impact on the total amount of REDO blocks produced.
Either you have enough disks & fast enough disks to accommodate the REDO generation,
or you don't which will have a negative impact on application performance!

>Archive logs are filling fast and fills the hard disk.
OP says above is the the problem.
Only two possible solutions exist.
1) do less DML so the REDO generation is less
2) add more data storage capacity; either add more disks or spin file archived REDO off to tape so the amount of REDO is no longer a problem

Re: Archive Logs are filling fast [message #642523 is a reply to message #642522] Sun, 13 September 2015 22:26 Go to previous messageGo to next message
trantuananh24hg
Messages: 709
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
I agree with BS:

Quote:
Changing the number or size of the REDO log files; has ZERO impact on the total amount of REDO blocks produced


Assume, I have got a big DML production, backup everyday, twice a day or even every 2 hours, example. The procedure will free the size of storage, but, we do not care about this, it is simply to do, and the really thing consideration is why DML appears more than normal?

Rgdrs!
Re: Archive Logs are filling fast [message #642551 is a reply to message #642523] Mon, 14 September 2015 06:17 Go to previous messageGo to next message
karthik4u
Messages: 36
Registered: September 2006
Location: CHENNAI
Member
Thanks for all your points.

Kindly clarify this one point.

Say for ex:

One of the frequently modified table 'Table_A' and nearly 85% of Redo log contains data of this Table_A.

Table_A size is 100GB. After resizing ( and deleting some rows ) the table its size redcued to 2 GB.

Now this will have any impact in redo log entries? Whether .arc log files generated will get reduced?

Re: Archive Logs are filling fast [message #642552 is a reply to message #642551] Mon, 14 September 2015 06:20 Go to previous messageGo to next message
John Watson
Messages: 7262
Registered: January 2010
Location: Global Village
Senior Member
What do you think the effect will be? why do you think that?
Re: Archive Logs are filling fast [message #642554 is a reply to message #642552] Mon, 14 September 2015 06:23 Go to previous messageGo to next message
karthik4u
Messages: 36
Registered: September 2006
Location: CHENNAI
Member
I know that Redo log will contain only redo/undo data. Just want to know whether the untouched data in that particular table will get recorded in redo log entry.
Re: Archive Logs are filling fast [message #642558 is a reply to message #642554] Mon, 14 September 2015 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The answer is in the question.
If you know there are only redo/undo data then not modified data are not there.

Re: Archive Logs are filling fast [message #642559 is a reply to message #642554] Mon, 14 September 2015 06:29 Go to previous messageGo to next message
John Watson
Messages: 7262
Registered: January 2010
Location: Global Village
Senior Member
Karthik, I am trying to make you think.

You say this: "I know that Redo log will contain only redo/undo data". What is the difference between redo and undo? You must be clear on this: it is fundamental to Oracle architecture since release 6, and it is what distinguishes Oracle from any other database.

You have already been told that DML generates redo. Do you think that what you call "untouched data" will generate redo?
Re: Archive Logs are filling fast [message #642564 is a reply to message #642559] Mon, 14 September 2015 07:35 Go to previous message
karthik4u
Messages: 36
Registered: September 2006
Location: CHENNAI
Member
Thanks a lot Smile
Previous Topic: Quota Grant
Next Topic: 12c Standard Edition One Installation fails and need fixes no updates from Oracle
Goto Forum:
  


Current Time: Thu Feb 22 07:31:39 CST 2018

Total time taken to generate the page: 0.06526 seconds