Excessive Archives

[cravi]

Hi Friends

I have a Oracle 734 database on windows Nt.Usually only 10 mb of archives are generating per day.But Now it is Around 75mb of archives per day since 10 days.Is it a serious concern.What should be the problem and how to resolve it.Please let me know ASAP.It's a Production database.

Thanks
Ravi

[pss]

Do you have activity increase in your database? Any new batch processes, new users, new applications?
F.e. I have 1.5Tb production DB and 10Gb-15Gb archive logs daily.

Sergey.

[cravi]

Hi

The activity on Database is not Increased.Also no batch process.Only one user was added and no new applications.

Thanks
Ravi

[rsuri]

look into v$session and try to figure out if any sessions are unusual or very active.

- Rajeev

[mbaliga]

Hi,

Check if you have too many rollbacks. If some transactions run for a period of time and then do a rollback, you could be generating some extra redo entries as the aborted transactions will be executed again & again.

Baliga

[cravi]

Hi Rajeev

Could you please help me how to figure out the problematic session in
v$session.

Thanks
Ravi

[rsuri]

The best thing will be if you can get hold of "Top Sessions" tool of Oracle Enterprise Manager. This has a very good GUI that shows you detailed information about the current sessions and their activities.
It actually uses the following objects to gather the info --

V$SESSION
V$STATNAME
V$SESSTAT
ALL_TAB_COLUMNS
V$OPEN_CURSOR
V$SQLTEXT
V$LOCK
SYS.DBA_OBJECTS
V$ROLLNAME
PLAN_TABLE
ALL_CATALOG
AUDIT_ACTIONS

Also make sure that you didn't create any Management Server's repository on this database. MAnagement server pings the database at a set interval to check the status of the services you are monitoring (this causes transaction information).

- Rajeev

[sln81]

To track the current transactions, check in the V$TRANSACTION view, in this you can find the session address and you have the timestamp also. Using this and V$SESSION you can find out the username and kill that session, if required.