OraFAQ Forum: Server Administration » Archive Logs are filling fast

Home » RDBMS Server » Server Administration » Archive Logs are filling fast (11g)

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

Archive Logs are filling fast [message #642477]

Fri, 11 September 2015 08:37

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.

Report message to a moderator

Re: Archive Logs are filling fast [message #642478 is a reply to message #642477]

Fri, 11 September 2015 08:41

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: Archive Logs are filling fast [message #642482 is a reply to message #642478]

Fri, 11 September 2015 09:09

Flyby
Messages: 188
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.

Report message to a moderator

Re: Archive Logs are filling fast [message #642485 is a reply to message #642477]

Fri, 11 September 2015 09:51

John Watson
Messages: 8976
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.

Report message to a moderator

Re: Archive Logs are filling fast [message #642520 is a reply to message #642485]

Sun, 13 September 2015 21:51

trantuananh24hg
Messages: 744
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.

Report message to a moderator

Re: Archive Logs are filling fast [message #642522 is a reply to message #642520]

Sun, 13 September 2015 22:15

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: Archive Logs are filling fast [message #642523 is a reply to message #642522]

Sun, 13 September 2015 22:26

trantuananh24hg
Messages: 744
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!

Report message to a moderator

Re: Archive Logs are filling fast [message #642551 is a reply to message #642523]

Mon, 14 September 2015 06:17

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?

Report message to a moderator

Re: Archive Logs are filling fast [message #642552 is a reply to message #642551]

Mon, 14 September 2015 06:20

John Watson
Messages: 8976
Registered: January 2010
Location: Global Village

Senior Member

What do you think the effect will be? why do you think that?

Report message to a moderator

Re: Archive Logs are filling fast [message #642554 is a reply to message #642552]

Mon, 14 September 2015 06:23

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.

Report message to a moderator

Re: Archive Logs are filling fast [message #642558 is a reply to message #642554]

Mon, 14 September 2015 06:28

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Archive Logs are filling fast [message #642559 is a reply to message #642554]

Mon, 14 September 2015 06:29

John Watson
Messages: 8976
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?

Report message to a moderator

Re: Archive Logs are filling fast [message #642564 is a reply to message #642559]

Mon, 14 September 2015 07:35

karthik4u
Messages: 36
Registered: September 2006
Location: CHENNAI

Member

Thanks a lot Smile

Report message to a moderator

Previous Topic:	Quota Grant
Next Topic:	12c Standard Edition One Installation fails and need fixes no updates from Oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 18:56:01 CDT 2025