Abnormal Log Switching - URGENT

[alison]

Hi there,

Could anyone shed any light on why the logs in my Oracle7.3.3 database are switching far more than normal.

We are running Peoplesoft against this database so all transactions are controlled through this, I have checked the processes scheduler and no abnormal jobs are running.

The logs are 7 groups 2 members each 40 M, they are switching roughly every 10 to 30 seconds (this is very unusual).

Is there something I should be looking at to discover what's going on.

We are running in archive log mode and this switching is eating up all the space on the server (so much so that the archiver process is stopping when we run out of space).

Any help would be greatly appreciated.

Alison

[marist89]

Are the logs full when they are being switched? If so, you have activity on your server. Investigate through v$session and v$sqlarea to find the culprit...

[alison]

Hi there marist89,

The logs are full on each switch.

Could I check, select's won't be written to the logs will they, V$SQLAREA is only showing selects.

As I said my users can only access the database through Peoplesoft therefor any job that they are running should show up in the process schedular.

Could anything else be causeing this abnormal switching.

Cheers

Alison

[rsuri]

Alison,

1. When exactly did it start happening ?
2. Was a new schema added to the database ?
3. Was any new repository created on this database (like the one for OEM) ?


In 8i you can use Log mining to see what exactly is in the Logs but not in 7.3.3 !

- Rajeev

[alison]

Hi Rajeev

1. On a Saturday morning the archive logs are purged from the server, ever since then I have seen this abnormal switching.

2. No new schema's have been adder, this is a very business critical environment so all changes are approved through change control processes.

3. No new repository either, the system has been stable and unchanged for a while now.

I have Oracle8i databases, could I use there log mining tool to look into these logs??

Thanks for you help

Alison

[bmycroft]

Am I right in thinking that selects will get written to logs - after all, selects cause writes to rollback segments, which get written to logs?

Also, how can you tell if the log is full when it was written away?

cheers,
Bob

[rsuri]

Alison,
I have never tried this but I guess there is no harm in trying to Log a 7.3.3 log by using 8i Log Miner. 40M logfile may take a while a create the output file though.

- Rajeev

[marist89]

Originally posted by bmycroft
Am I right in thinking that selects will get written to logs - after all, selects cause writes to rollback segments, which get written to logs?

Also, how can you tell if the log is full when it was written away?

cheers,
Bob

Sure, selects cause changes to the rollback segments which causes redo to be generated.

You can tell if the log was full when the switch occurred because of it's size. If your redo logs are defined as 40M and your archived redo log is 40M, it was full.

[marist89]

Originally posted by alison
Hi there marist89,

As I said my users can only access the database through Peoplesoft therefor any job that they are running should show up in the process schedular.

Could anything else be causeing this abnormal switching.

Cheers

Alison

Maybe its not a user process that is hosing your db. What does the OS say is your top process in terms of CPU utilization? The fact that the logs are full when they switch indicates that Oracle is doing his job correctly. The only other possibility is a bug of some kind, but that is unlikely.

[rsuri]

bmycroft,
If the Archived Log size is equal to the size of the Redo log then the Log was full otherwise not.

The log may not be full and you can do a manual Log Switch and that will result in a smaller size of Archived Log.

For the first part (SELECTs cause Rollbacks and so Redo information).. it is an interesting aspect...I never saw it from that angle... I will let others reply to that and be an observer :-)

- Rajeev