Oracle Redo Logs All Active-CodePudding

First, I know very little next to nothing about being an OracleDba. I am responsible for managing an application using Oracle 11g written by a third party. Our application/database has been stable for the last 7 years. All of the sudden, we've been experiencing database slowness/locks/crashing. The developer for the application seems to think it's related our redo logs. I can't say he's right or wrong, but based on what I've been reading so far, all of our log files should not be active.

It does seem that when these logs fill up, we run into all kind of issues. Cleanly stopping and restarting the database does clear out these log files and the application becomes usable again, but it takes a LONG time and adversely affects our ability to do business.

Any thoughts on why these log files aren't switching and going inactive? How do we find what's causing it? Can we force a switch by using ALTER SYSTEM SWITCH LOGFILE;?

Again, I know very little about being an Oracle Dba, but our software developer hasn't been able to resolve the issue since Friday and I'm desperate for help. Looking forward to learning more about this.

Thanks in Advance.

CodePudding user response：

If the destination to where your writing your logs are filling up that can freeze your system.

I would check to see if there are abd unusual spikes on a hourly and daily basis.


Hourly Archive Log Generation :

set pages 1000
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

select trunc(COMPLETION_TIME,'HH') Hour,thread# , 
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives from v$archived_log 
group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;


Daily Archive Log Generation :

SQL> select trunc(COMPLETION_TIME,'DD') Day, thread#, 
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives_Generated from v$archived_log 
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

second you may want to consider running rman commands to free up room such as

DELETE expired;
DELETE obsolete;

Lastly, upload an alert log to Oracle support. Being in 11g this maybe problematic as that version has been desupported for years

CodePudding user response：

Assuming that you don't have space, blocking sessions or any other issues, just a problem against active redo logs.

Status ACTIVE means that some data changes in that redo log have not yet written into data files in this moment. You can perform a checkpoint to force all redos to be written to data files.

SQL> alter system checkpoint;

System altered.

Once the checkpoint is complete, you can check statuses of all redo logs again. They should be INACTIVE now.