| 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.
|
|
|
|
|
|
|
|
| Re: Archive Logs are filling fast [message #642485 is a reply to message #642477] |
Fri, 11 September 2015 09:51  |
John Watson
Messages: 8964
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 |
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.
|
|
|
|
|
|
| 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!
|
|
|
|
| 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?
|
|
|
|
|
|
|
|
|
|
| Re: Archive Logs are filling fast [message #642559 is a reply to message #642554] |
Mon, 14 September 2015 06:29 |
John Watson
Messages: 8964
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?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
