We help IT Professionals succeed at work.

High Volume of archive log generation

nkewale
nkewale asked
on
I am working on SAP ERP System which uses Oracle as the backend database.

Recently I am facing a problem that the is generating high amount of archive logs.

The latest development involved that we have upgraded the version of SAP. But nowhere we heard of this problem.

Can u let me know the factor respoonsible for high archive redo log generation.
Also is there any relation if we keep our print server and database server same in terms of high redo log generation.

The problem is urgent.

Navin Kewale
nkewale@yahoo.com
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
What is the volume/frequency of the log files?
How large is your database?
How many users are working on it?

The facts are simple:
The more transactions are issued to your database, the more redo log is written. The redo log is being archived as fast as log members fill up.

I am working on a backup of a system with 1 ARC file generated almost every minute, with the filesize 102 MB each! The database has a size of ~1 TB, but the most important fact is that there are all the time ~500 users on it!

Cheers
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
The first thing to check: is/are your temporary tablespace(s) defined as temporary?

Try this query:
select tablespace_name, contents, logging from dba_tablespaces;

Here are (partial) results of that query in our system:
TABLESPACE_NAME                CONTENTS  LOGGING
------------------------------ --------- ---------
SYSTEM                         PERMANENT LOGGING
TEMPORARY_DATA                 TEMPORARY NOLOGGING
ROLLBACK_DATA                  PERMANENT LOGGING
USER_DATA                      PERMANENT LOGGING
USER_INDEX                     PERMANENT LOGGING

Note the value in the "contents" column for our temporary tablespace.  If your temporary tablespace has "permanent" in this column, like the other tablespaces, that can generate a lot of unnecessary redo log activity.

Next check to make sure that all of your users have their temporary tablespace set to your system's temporary tablespace (this is *NOT* the default unfortunately).
This query will show that:

select username, temporary_tablespace, default_tablespace from dba_users;

If any users still have the default of "system" for their temporary tablespace, change that immediately.

Author

Commented:
Hi Angelllll!

Thanks for your response. But the problem at my end is having a archivelog generation without much load on system. Also the size of database is very small as compared to what scenario u r explaining.

The details at my sight are

The details at my end are

Database size : 51 GB
Redo Log size : 115MB
Frequency of log generation : 1 log every seven minute.
Concurrent users : 70

Can u focus some other area where problem can be there?

Regards
Navin Kewale

Author

Commented:
Hi Mark!

As per your advice I have checked the settings in the database and have following observations.

Result of your queries

1. select tablespace_name,contents,logging from dba_tablespaces;

The output was

Tablespace_Name             Contents            Logging
-----------------------------------------------------------
PSAPTEMP                    PERMANENT          LOGGING
-----------------------------------------------------------
PSAPTEMP is temporary tablespace in SAP Environment.

The output of second query

2. select username,temporary_tablespace,default_tablespace from dba_users;

User_name     Temporary_tablespace     default_tablespace
-----------------------------------------------------------
sys           PSAPTEMP                 SYSTEM
System        PSAPTEMP                 SYSTEM
SAPR3         PSAPTEMP                 PSAPUSER1D
OPS$P2PADM    SYSTEM                   SYSTEM
OUTLM         SYSTEM                   SYSTEM
DBSNMP        SYSTEM                   SYSTEM
-----------------------------------------------------------

BUT THESE SETTINGS ARE SAME IN OTHER INSTALLATIONS ALSO WHERE I HAVE CHECKED. THE OTHER INSTALLATIONS DOESNOT HAVE SUCH A HIGH RATE OF REDO LOG GENERATION.

Please suggest me something in this regard.

Regards


Navin
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
For me, this seems ok, as the size of the database is not that important, but the number of users... Comparing yours and mines, we are equal:
70:500 users approx 1:7 log frequency

I will look however into the information of markgeer, which seems highly interesting. I guess that all the selects that will produce some temporary working tables in the background will generate that kind of logging, which could be ignored...

Cheers
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
Just because SAP can use an Oracle database, don't assume that the folks at SAP know how to configure and tune Oracle well.  (They should, but then I haven't seen a vendor's system yet that uses Oracle that comes with good configuration and tuning recommendations for Oracle.)

First check if there are any permananent objects in your temporary tablespace:

select owner, object_name, object_type from dba_segments
where tablespace_name = 'PSAPTEMP';

That should return "no rows selected".  If there are any tables or indexes, then you need to determine if they truly are permanent objects that could/should be moved to a different tablespace, or if SAP creates some short-term objects in this tablespace.  Assuming there are no objects in that tablespace, then:
alter tablespace PSAPTEMP temporary;

This will tell Oracle to not generate redo log activity for actions that use the temporary tablespace, and this may cut down significantly on your redo log volume.

Then do an "alter user" for each user that does not have PSAPTEMP as their temporary tablespace.  The syntax is:
alter user OPS$P2PADM temporary tablespace PSAPTEMP;

I would also recommend that you change the default tablespace too for each user other than SYS, who has their default tablespace = 'SYSTEM'.  The syntax is:
alter user OPS$P2PADM default tablespace PSAPUSER1D;
(Instead of "PSAPUSER1D", you could use any tablespace you like other than "SYSTEM" or "PSAPTEMP".)

Basically in an Oracle database, the only user who needs their default tablespace set to "SYSTEM" is "SYS".  All other users (including "SYSTEM") should have their default tablespace set to some other tablespace.  Why?  This is to avoid fragmentation of the system tablespace, because if/when that happens, the only fix is a complete database export, drop and rebuild.  Fragmentation in all other tablespaces is much easier to manage.  You can just move the objects to another tablespace.  With the SYS-owned objects though in the SYSTEM tablespace, you cannot drop or move them.
CERTIFIED EXPERT

Commented:
Navin,

Previously I was asked to help with a silimar situation of excessive redo generation of an 8.1.5 database on an NT server --- the size of redo log is 30M, even without system load, the frequency of redo switching was few minutes.  With the assistance of Oracle Support, it turned out that a co-existing Oracle 7.3 client (installed AFTER 8.1.5 installtion) on the same box was the cause of the 8.1.5 database wrong doing.

Your platform maybe different. However borrowed from this example, there could be a possibility that some process(es) on the server side may interfere with the Oracle processes, for example, as you are concerned about the co-existence of the print and database server. If you can efford temporarily shutdown/suppend the suspected process(es), and meanwhile monitoring the redo generation, it may help to isolate the problem.

Another thing it may be worth to try --- pick up a redo logfile and logminer it;  examine the V$LOGMNR_CONTENTS table by grouping such as username or seg_name; find the percentage of hits by sys/system or application users,  any specific table/segment recieving high hits, any pattern or trend appearing abnormal, etc.

If you have the previous archived redo log before the upgrade, it will be most helpful to logminer one of them and make a comparison with the current findings.

Regards,

Wayne

Author

Commented:
Hi Mark!

I have checked in my system and there are no permanent objects in my temporary tablespace.

I have done the changes in my system by running the following SQL Commands:

1.  alter tablespace PSAPTEMP temporary;

and after that also the system has generated 6 redo logs of size 117 MB in 30 minutes time.

Also I did changes to all users by sspecifying the temporary tablespace and default tablespace as PSAPTEMP and PSAPUSER1D.

but still it is generating high amount of redo log.

Can you suggest some other solution.

Regards


Navin

Author

Commented:
High Mark!  

I am enclosing the trend of redo log generation for your reference. the generation is sometimes every minute a redo log of 120MB. In exceptional cases the log size is also 78 MB and 59 MB.

Redo No.   date         Time          
---------------------------------                          
  36372   2001.09.24 16:20:18
  36371   2001.09.24 16:17:34
  36370   2001.09.24 16:16:12
  36369   2001.09.24 15:17:56
  36368   2001.09.24 15:15:00
  36367   2001.09.24 15:12:12
  36366   2001.09.24 15:10:57
  36365   2001.09.24 15:09:33
  36364   2001.09.24 15:08:17
  36363   2001.09.24 14:06:58
  36362   2001.09.24 14:03:39
  36361   2001.09.24 14:00:37
  36360   2001.09.24 13:59:42
  36359   2001.09.24 13:58:16
  36358   2001.09.24 13:56:38
  36357   2001.09.24 13:46:09
  36356   2001.09.24 13:33:53 (Log Size 59 MB)
  36355   2001.09.24 13:31:01
  36354   2001.09.24 13:28:04
  36353   2001.09.24 13:26:42
  36352   2001.09.24 13:25:24
  36351   2001.09.24 13:24:00
  36350   2001.09.24 12:28:01
  36349   2001.09.24 11:25:40 (Log Size - 78 MB)
  36348   2001.09.24 11:22:28
  36347   2001.09.24 11:19:05
  36346   2001.09.24 11:17:10
----------------------------------------------------
This info may help you in assessing the current circumstances.

Navin
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
We recently have seen an unusually high volume of redo logs generated in our system at the same time that the following two entries show up in our alert log:

ORA-01595: error freeing extent (2) of rollback segment (2))
ORA-01594: attempt to wrap into rollback segment (2) extent (2) which is being freed

Apparently Oracle's internal housekeeping procedures can generate lots of redo in this situation.  I haven't figured out yet what causes this problem, nor what can be done to prevent it.

Have you tried LogMiner to see what kind of activity is happening in your redo logs, and which Oracle account is causing it? LogMiner is a utility that is available with Oracle8.1.5 and higher to allow DBA's to browse the archived redo logs to see who did what and when.


Author

Commented:
Hi Mark!

I have analysed the Log Files which are getting created so  fast.

The details are as follows:

In one of the logs the following query:

1. Select operation,sql_redo,sql_undo from V$logmnr_contents;

OUTPUT :

OPERATION     SQL_UNDO                          SQL_REDO
------------------------------------------  --------------
INSERT       INSERT INTO "SAPR3"."DDLOG"           val
             ("S_ID","SEQ_NR","WR_TS",
             "TS2","CLNT","NOTEBOOK")    
-----------------------------------------------------------
This step is executing 231987 times out of total operation which amount for 243998.

Also I fired one more query which gives insight to operations happening in database:

2. select seg_owner,seg_name,count(*) as HITS from V$logmnr_contents where seg_name NOT LIKE '%$' group by seg_owner, seg_name;

The outcome is as follows:
-----------------------------------------------------------
SEG_OWNER  SEG_NAME         HITS
-----------------------------------------------------------
SAPR3      TLTSTLOD           12                    
SAPR3      BTCCTL            129
SAPR3      DDLOG          231987
SAPR3      SORAMON            88
SAPR3      SORAMON~0          16
SAPR3      TBTCO              20
SAPR3      TBTCO____0          4
SAPR3      TBTCO____1          4
SAPR3      TBTCO____3         28
SAPR3      TBTCO____5          4
SAPR3      TBTCO____7         36
SAPR3      TBTCP               4
SAPR3      TBTCP____0          4
SAPR3      TBTCP~1             4  
SAPR3      TBTCP~2             4
SAPR3      TBTCS              20
SAPR3      TBTCS____0          8
SAPR3      TBTCS____1          8
SAPR3      TSOPTIONS          16
SAPR3      TST01              16
SAPR3      TST01____0          4
SAPR3      TUCON              32
SAPR3      TUCON~0            20
-----------------------------------------------------------

This shows huge usage of table DDLOG. I dont know what process is firing the extensive use of table. Can u tell me how to find out which operation is firing this table usagw and how it can be stopped?

Navin
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
I guess that the SAP application is logging it's actions to this table, seeing this high number of actions to only this table. I think you have found the reason for the high amount of transactions created...

Now you might either find out if you need this logging, and how to turn it off or tune it...
Maybe you could place this table into NO_LOGGING state, but you need to know the risks when you have to restore the database..

Cheers
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
It looks to me like you need to ask the folks at SAP why their application generates this large volume of transactions.

My understanding of "no_logging" is that this only applies to SQL*Loader and "insert into... select ..." statements, not standard "insert... values..." statements, so I don't expect that this approach will do anything for you, but if it does help, please let us know.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
markgeer, in oracle you can put a single table into NOLOGGING mode, which means that there is no redo log generated for this table.

ALTER TABLE SAPR3.DDLOG NOLOGGING;

Be aware that this table is not recoverable with the redo logs, only full backup (online or cold) backup these tables.

CHeers
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
To angellll:

Here is an excerpt from the Oracle8.1.7 documentation regarding "nologging".  Note that it does not mention updates, or regular "insert into... values..." statements.  Have you tested this to see if redo log generation is actually surpressed for updates and standard inserts?

LOGGING | NOLOGGING
 Specify whether subsequent Direct Loader (SQL*Loader) and direct-load INSERT operations against a nonpartitioned table, table partition, all partitions of a partitioned table, or all subpartitions of a partition will be logged (LOGGING) or not logged (NOLOGGING) in the redo log file.
 
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
ja, you are right...
it's time to continue my oracle studies!
Sorry for any confusion(s)

CHeers
Hi,
I am also working on a similar kind of ERP system , SAP on Oracle/Unix .

Did you check whether the online back was on during the high amount if Redo logging as SAP brings all the tablespaces to backup mode in one shot and backsup the datafiles .Do check the Backup logs in DB13 .

Please also check the parameters :
log_checkpoint_interval
log_checkpoint_timeout

Regards
Amar
Hi,
I am also working on a similar kind of ERP system , SAP on Oracle/Unix .

Did you check whether the online back was on during the high amount if Redo logging as SAP brings all the tablespaces to backup mode in one shot and backsup the datafiles .Do check the Backup logs in DB12 .

Please also check the parameters :
log_checkpoint_interval
log_checkpoint_timeout

Regards
Amar
Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** Mindphaser - Community Support Moderator **

P.S.  Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
CERTIFIED EXPERT
Author of the Year 2009

Commented:
Recommended disposition:

    Split points between: angelIII and markgeer and waynezhu

DanRollins -- EE database cleanup volunteer
Thanks, Dan. :)

Points have been split.
Points for markgeer -> http://www.experts-exchange.com/jsp/qShow.jsp?qid=20308559
Points for waynezhu -> http://www.experts-exchange.com/jsp/qShow.jsp?qid=20308560

Moondancer - EE Moderator