Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle RedoLogs are Full - Now What?

SOLVED
Go to solution
Scott Buckingham
Regular Advisor

Oracle RedoLogs are Full - Now What?

We found out earlier today that our production database was no longer accessible due to our redo logs being full. Now I don't know what to do to get back up and running! I have shutdown the instance, but it will not restart until the logs get cleared (or can I add more space to them?) Am I completely hosed or is there a way out of this mess?
Long time dabbler, first time Admin / DBA
13 REPLIES
A. Clay Stephenson
Acclaimed Contributor

Re: Oracle RedoLogs are Full - Now What?

Hi Scott:

I suspect what has happened is notr that your redo logs have filled (that is normal) but rather you are out of space in your archivelog directory. Let's say that you have four redo logs. When redo1 fills up you switch to redo2; when it fills you switch to redo3; when it fills you switch to redo4; when it fills you first copy redo 1 to an archivelog and then switch to redo1. You need to either remove old archivelogs (after making sure that they are not needed or they are backed up) or create more room in this logical volume.
If it ain't broke, I can fix that.
Scott Buckingham
Regular Advisor

Re: Oracle RedoLogs are Full - Now What?

Thanks for the quick response, Clay. After doing a bdf on our HP_UX box, the logs that Oracle is complaining about are on logical drives that are less than half full. Or are you referring to extending tablespaces? If so, can I do this through SQLPLUS or the the SVRMGRL utility? I can't access the instance from the Oracle Enterprise Manager. For some reason, I get a message that the instance is still be shutdown.
Long time dabbler, first time Admin / DBA
A. Clay Stephenson
Acclaimed Contributor

Re: Oracle RedoLogs are Full - Now What?

No archive logs are regular files. Look in your init.ora file and file the entry for "log_archive_dest="
If it ain't broke, I can fix that.
Printaporn_1
Esteemed Contributor

Re: Oracle RedoLogs are Full - Now What?

Hi,
what is exact ORA error ?
if it's in that you explain just delete very old archive file (may be previous month) in
"log_archive_dest=" directory as Clay suggest.
enjoy any little thing in my life
Andreas D. Skjervold
Honored Contributor

Re: Oracle RedoLogs are Full - Now What?

Hi

I want to second Clays solution and come with some more information.
On a production database you most certain have set up archiving of the redologs.
When your database became inaccesible, there was no need of shuting it down (infact shuting down should be the last solution when you experience problems, as data might be consistent in memory but due to some error not on disk yet.)

What happens when the archive filesystem gets full is that the archiver backround process stops copying redologs out, and thereby effectively preventing redologs not archived to be reused.
If / when this situation occure, the thing is just to move/delete the archived logs to free up space or to extend archive volume if you have the possibility. The database would then start running all by it self as the archiver starts up and the redologs are freed.

If running with archiving you should implement some routine to atomatically backup and delete the archived redologs, to prevent archive filesystem to filling up.

If using RMAN to do backups you also can use this to backup and delete the archived redologs.

Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Scott Buckingham
Regular Advisor

Re: Oracle RedoLogs are Full - Now What?

Can you tell I'm an acting DBA and not an official one?

Thanks to all for your responses and I understand what your telling me. However, after moving the archived logs from one filesystem to another (for backup purposes), leaving our /u06 filesystem empty, I still receive the following when trying to start the database instance:

SVRMGR> startup
ORACLE instance started.
Total System Global Area 80409504 bytes
Fixed Size 38984 bytes
Variable Size 49486680 bytes
Database Buffers 30720000 bytes
Redo Buffers 163840 bytes
Database mounted.
ORA-00255: error archiving log 5 of thread 1, sequence # 83945
ORA-00312: online log 5 thread 1: '/u07/oradata/PRD0/redo51_PRD0.log'
ORA-00312: online log 5 thread 1: '/u09/oradata/PRD0/redo52_PRD0.log'
ORA-00270: error creating archive log
ORA-00334: archived log: '/u06/oradata/PRD0/arch/arch.log'LOG83945_1.ARC''
ORA-07352: sfccf: create error, unable to create file.
HP-UX Error: 5: I/O error

So, is my problem still with /u07 and/or /u09?
Long time dabbler, first time Admin / DBA
Thierry Poels_1
Honored Contributor

Re: Oracle RedoLogs are Full - Now What?

hi Scott,

'/u06/oradata/PRD0/arch/arch.log'LOG83945_1.ARC''

mind the quotes within the filename. Please check log_archive_dest_1 and/or log_archive_format for excessive quotes.

Also make sure Oracle has write privilege on these directories.

good luck,
Thierry.
All unix flavours are exactly the same . . . . . . . . . . for end users anyway.
A. Clay Stephenson
Acclaimed Contributor
Solution

Re: Oracle RedoLogs are Full - Now What?

Hi Scott:

I think your real problem is with /u06. Unfortunately, errno 5 (EIO) generally indicates a failure reading or writing to a device - possibly a disk failure. You need to unmount /u06 and run fsck on it (and probably not just a log replay). I that is ok try creating a file in the archive directory.

If it ain't broke, I can fix that.
Scott Buckingham
Regular Advisor

Re: Oracle RedoLogs are Full - Now What?

It appears that you nailed it, Clay. I haven't run the fsck command yet as we have some demos going on. I'll do that later. However, I tried to create a file in the /u06 filesystem and I received an I-O error (definite red flag here!) In the meantime, while I try to address this issue, how can I direct my archive logging to go somewhere else?
Long time dabbler, first time Admin / DBA
A. Clay Stephenson
Acclaimed Contributor

Re: Oracle RedoLogs are Full - Now What?

Hi Scott:

The easist thing to do (and avoid any Oracle stuff altogether) is to do some basic unix stuff. How abour this:

1) Unmount /u06
2) comment the entry in /etc/fstab so that you do not try to mount it later
3) Set up a symbolic link from some other directory/filesystem to /u06
If it ain't broke, I can fix that.
Andreas D. Skjervold
Honored Contributor

Re: Oracle RedoLogs are Full - Now What?

Hi

To change your archive destination, just change your init.ora setting: "log_archive_dest=/new_path/" and startup once more.

If you're not familiar with Oracle, your init.ora is located in $ORACLE_HOME/dbs as init.ora (either as file or link to $ORACLE_BASE/admin/SID/pfile/init.ora)

Andreas

Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
Scott Buckingham
Regular Advisor

Re: Oracle RedoLogs are Full - Now What?

Thanks, Clay, for the great advice! Your last suggestion worked like a champ! I ran fsck on the drive in question and it came up with all kinds of errors so until we get a new one, this solution will take precedence.

Andreas, I likde your suggestion too, but I tend to do things in order.

Thanks to all for your responses!
Long time dabbler, first time Admin / DBA
A. Clay Stephenson
Acclaimed Contributor

Re: Oracle RedoLogs are Full - Now What?

Hi Scott:

Glad I was able to help. I intentionally gave you a 'pure' UNIX workaround since you seemed to be more comfortable in that world.

Regards, Clay
If it ain't broke, I can fix that.