Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help! Archiver failed! Need guidelines please.

Re: Help! Archiver failed! Need guidelines please.

From: Paul Drake <drak0nian_at_yahoo.com>
Date: 1 Jan 2004 13:02:23 -0800
Message-ID: <1ac7c7b3.0401011302.409dcf0e@posting.google.com>


klist_at_adnohr.net (Al) wrote in message news:<740109f4.0312301047.3df7f1a9_at_posting.google.com>...
> Gurus,
>
> Are there guidelines to determine the necessary size to allocate to a
> disk
> device that contains my log archive?
>
> My Unix sysadmin got the message "Critical Disk Near Full!"...for the
> 3rd time this week.
>
> Select archiver from v$instance came back with FAILED. So I did
> "alter
> system archive log stop;". This is a production datamart for which I
> have
> not had any hot/cold backup for 3 weeks. I am worried.
>
> The overall size of my production datamart is 190GB. My archive log is
> about 29 GB in size at the moment.
>
> I am running v9.2 on HP-UX B.11.00 U 9000/800.
>
> TIA for any help you can provide.
>
> kl.

sounds like a pair of 73 GB disks (mirrored) would solve this problem. I installed a set of 7 x 73 GB drives last week from Dell for $210 apiece, including trays. storage is so cheap, this should be a non-issue.

Ok, so assuming that simply adding storage is not an option ...

Who made the decision to configure this database in ARCHIVELOG mode?

It may not have been the correct decision, given the hardware available.
If you can't allocate sufficient hardware (storage) for housing the archived redo logs, one option is to run the database in NOARCHIVELOG mode.

I expect to be flamed for posting this, but understand that I am not recommending it, I am merely stating the NOARCHIVELOG is an option.

IF

you can afford the downtime to perform either a - physical cold backup (database closed) - RMAN full backup (database mounted)

AND you need not recover any transactions that have occurred since the last data load and can reload all such data (words like "LOSS OF DATA" tend to scare people that have purchasing authority)

AND you (or the database's users) can afford the downtime encurred by restoring the last cold backup and reloading data since the last cold backup (words like "LOSS OF DATA AVAILABILITY" tend to scare people that have purchasing authority)

THEN NOARCHIVELOG mode could be right for this database.

Its not likely that this is the case, though. (You will also need to know exactly how long the restore and reloads would take to execute).

Were you given the data availability requirements?

If this database is not accessed over the weekend (for example) then cold full backups of all read/write tablespaces may be the way to go (and set all the tablespaces to read only that can be).

Another option may be to simply process the archived redo logs frequently enough that the generation rate does not cause the destintation to become full.
This could mean scheduling an OS task to run a shell script to compress and move the archived redo logs to network storage or tape, or to call an RMAN script to do the same. The trick here is to know what your peak redo log generation rate is, and schedule the job to run accordingly.

The downside of near-time processing of the logs is that the resource utilization required to process the archived redo logs will contend with the oracle and user processes on the server and storage units.

Contention.
Increased response time in processing. Users tend not to like this.

Better to have enough storage for the day's archived redo logs and process them when the users' response time would not be affected.

Another option is to determine what is causing the generation of redo, and attempt to reduce the amount of redo generated, or to schedule tasks that generate large amounts of redo to times when it can be handled.

You may find that there are statements (loads, inserts) running as LOGGING when they could be configured as NOLOGGING. Examing v$sesstat would provide info as to what user is generating the most redo.

You might actually determine what users are causing the most redo, and get them to sign off on the new disks, or determine that the statement that is being processed has no business requirement - and that they should stop running the task.

Check out Cary Milsap's new book, a sample chapter is available in .pdf:
http://www.oreilly.com/catalog/optoraclep/chapter/index.html

hth.

Pd Received on Thu Jan 01 2004 - 15:02:23 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US