topic Re: Oracle RedoLogs are Full - Now What? in Operating System - HP-UX

Oracle RedoLogs are Full - Now What?

Scott Buckingham — Tue, 30 Oct 2001 00:21:20 GMT

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?

Re: Oracle RedoLogs are Full - Now What?

A. Clay Stephenson — Tue, 30 Oct 2001 00:28:30 GMT

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.

Re: Oracle RedoLogs are Full - Now What?

Scott Buckingham — Tue, 30 Oct 2001 00:36:31 GMT

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.

Re: Oracle RedoLogs are Full - Now What?

A. Clay Stephenson — Tue, 30 Oct 2001 00:48:43 GMT

No archive logs are regular files. Look in your init.ora file and file the entry for "log_archive_dest="

Re: Oracle RedoLogs are Full - Now What?

Printaporn_1 — Tue, 30 Oct 2001 03:46:44 GMT

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.

Re: Oracle RedoLogs are Full - Now What?

Andreas D. Skjervold — Tue, 30 Oct 2001 07:40:14 GMT

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

Re: Oracle RedoLogs are Full - Now What?

Scott Buckingham — Tue, 30 Oct 2001 14:24:32 GMT

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?

Re: Oracle RedoLogs are Full - Now What?

Thierry Poels_1 — Tue, 30 Oct 2001 14:37:14 GMT

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.

Re: Oracle RedoLogs are Full - Now What?

A. Clay Stephenson — Tue, 30 Oct 2001 14:38:11 GMT

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.

Re: Oracle RedoLogs are Full - Now What?

Scott Buckingham — Tue, 30 Oct 2001 16:08:08 GMT

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?

Re: Oracle RedoLogs are Full - Now What?

A. Clay Stephenson — Tue, 30 Oct 2001 16:12:11 GMT

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

Re: Oracle RedoLogs are Full - Now What?

Andreas D. Skjervold — Tue, 30 Oct 2001 21:11:32 GMT

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

Re: Oracle RedoLogs are Full - Now What?

Scott Buckingham — Tue, 30 Oct 2001 23:27:52 GMT

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!

Re: Oracle RedoLogs are Full - Now What?

A. Clay Stephenson — Tue, 30 Oct 2001 23:50:25 GMT

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