High Volume of archive log generation

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.

ASKER

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

ASKER

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

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

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.

waynezhu

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

ASKER

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

ASKER

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

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.

ASKER

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

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

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.

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

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.