Archivelog Overload...

I need help figuring out a practical solution for handling big bursts of archive logs.

DB: 10.1.0.4 / ASM / OMF
OS: RedHat AS 3

From time to time we have a process that writes 20gb of archive logs in about 1/2 hour. It floods our available space (17gb reserve) for these logs and hangs the database.

Adding diskspace to the ASM diskgroup will obviously help but we'd I want to avoid the downtime of adding new raw disk devices.

I've thought of writing a pl/sql job that checks the rate of archiving and if it hits a threshold, then trigger an rman process to flush the logs, provided it's not already running.

Am I over-thinking this?

Ken

Quote:

---

Adding diskspace to the ASM diskgroup will obviously help but we'd I want to avoid the downtime of adding new raw disk devices.

---

You don't need to add raw devices for archive log file system. You need to increase the arch file system only.

Tamil

Rather than writing pl/sql, can't you just set up a threshold/notifcation (in grid - I know you use it!!) for when the file system/archive area hits x% full? If you still want to automatically kick the rman job, then set a response action up.
Either that or more space ;-)

Bazza

Quote:

---

Originally Posted by tamilselvan

You don't need to add raw devices for archive log file system. You need to increase the arch file system only.

---

Unless I choose a locally mounted file system and switch my archivelog destination to it, I believe would need to add a raw device to the ASM disgroup where my archivelogs are going now.

Ken

Quote:

---

Originally Posted by bazza

Rather than writing pl/sql, can't you just set up a threshold/notifcation (in grid - I know you use it!!) for when the file system/archive area hits x% full? If you still want to automatically kick the rman job, then set a response action up.
Either that or more space ;-)

Bazza

---

Yea, Great suggestion. I'd love to do this. I'm just not real comfortable with the alerting system 10g OEM. We've been running it side by side with other systems and they have been 100% right on, while OEM has missed some really big problems 50% of the time. That's probably me, but I think it needs help.

I also have the addition of not wanting to fire this when an rman backup (datafile or archivelog) is already running.

There's probably a sql query that could tell me if the db is currently being backed up through rman.

I'm working along the lines of a cron job that counts the rows in v$archived_log where status = 'A' ('D' is deleted), and if rman isn't already running (grep rman or something) and I've got 20 archived logs or so, then kick off the rman process.

Any thoughts/opinions are greatly appreciated.

-Ken

Instead of grepping for rman, you could check V$session instead and check for sys sessions running rman (program column is populated by rman).

Know what you mean about grid, our whole setup stopped working for targets that were discovered > 250 days ago, due to a bug. You using Release 2 yet?

Bazza

Quote:

---

Originally Posted by bazza

..check V$session .. for sys sessions running rman..

---

Great idea. Thanks!

That's a nasty bug we haven't run into. Was the root cause discovered? Was it platform specific?

I'm working on installing OEM 10.2 on SuSE-9 64 bit without much success. I've had a compilation error TAR open for a few weeks. Some TAR's just don't get good traction. :confused:

My take would be to add more disk space to the archive destination and point your archives to collect in there.

Quote:

---

Originally Posted by KenEwald

Great idea. Thanks!

That's a nasty bug we haven't run into. Was the root cause discovered? Was it platform specific?

I'm working on installing OEM 10.2 on SuSE-9 64 bit without much success. I've had a compilation error TAR open for a few weeks. Some TAR's just don't get good traction. :confused:

---

It's a known issue with R1 apparently, have a look at bug 3674467 and note 303106.1. Its not platform specific, its a bug with grid itself.

I'm going to have crack at Grid R2 on Red hat 3 next week. Just had to resolve a bug with the installer itself!!

Happy times

Bazza

Quote:

---

Originally Posted by sambavan

My take would be to add more disk space to the archive destination and point your archives to collect in there.

---

Yea, me too. Unfortunately I'm limited to the disks I have in that ASM diskgroup.

This happens once every 2 weeks. I think we can "swallow the frog" if I can flush logs before it can fill up the space. I'm also thinking about adding online redo logs so we have a little more lead time before the "archivelog dest full" alert is triggered and when the db finally halts transactions.

-Ken

I presume that you have the rman going on. On such case, if the time frame is kind of precise, when the process gets run, then invoke the rman couple of time during the process timeframe and delete the logs as you go.

I have a system, that would dump a tone of log every night. So, my policy was to delete all the logs that are older than one week. This run happens four times a day, hence keeping every other databases in good shape. Or you can delete the archive logs when you do your level 0.

This should release some space for you.

Again, as you may know, running rman during the peak is also a bit resource intensive too :(

Thanx,
Sam

Here's my log flushing rman script:

-----
spool log to '/u1/app/oracle/backup/flush.log';
run {
allocate channel d1 device type disk format '/rman/logfiles/%d_%u_%p';
BACKUP ARCHIVELOG ALL DELETE INPUT;
release channel d1;
}
exit;
spool log off;
-----

I didn't know that you could fire this off more than once. Is that really possible? We had a problem when 2 of these were run on the same db. I believe it was something like control file locking. I think we may have had a "backup current controlfile" included in that script. Maybe that was the problem?

I'm going to run it when x number of logs have accumulated (and it's not already running - jury's still out on this).

The rman script removes the logs from the arch dest after they're backed up /rman/logfiles (nfs mounted fs).

Thanks for your replies and ideas,

Ken

Quote:

---

Yea, me too. Unfortunately I'm limited to the disks I have in that ASM diskgroup.

---

Another reason, not to use ASM.

Tamil

Quote:

---

Originally Posted by tamilselvan

Another reason, not to use ASM.

---

Humm. Just curious, what are your other reasons, not to use ASM?

If you have two disks then modify the script

spool log to '/u1/app/oracle/backup/flush.log';
run {
allocate channel d1 device type disk format '/rman/logfiles/%d_%u_%p';
allocate channel d2 device type disk format '/rman/logfiles/%d_%u_%p';
allocate channel d3 device type disk format '/rman/logfiles/%d_%u_%p';
BACKUP ARCHIVELOG ALL DELETE INPUT;
release channel d1;
release channel d2;
release channel d3;
}
exit;
spool log off;


This should help you to allocate multiple channels and speed up your backup process. But again, one thing be cautious, if you can spread the channels across different controllers that will give you a real gain.

When I said couple of time earlier, I ment it to be in sequential order and not in parallel. You can parallelize by opening multiple channel. If you have some knind of tape backups like Tivoli/Veritas, you can blow them to that directly rather than backing them up on to disk and then to tape.

Thanx,
Sam

Thanks Sam.

I take it (# procs + 1) is a general rule to get the most out of the machine?

This is a 4 proc server. I'm concerned about slowing down transactions if I use more than 1 processor. Maybe I should write the script to use 1 channel during peak usage and 5 channels during off-hours.

-Ken

I would say four is plenty enough. If you find the process is chewing the resouces, then you can identify the RMAN session proces and nice them out to a low priority.

Sam

OK.

Here's the script I'm going to run (via cron/sh) that determines if I need to flush. It's hard coded to 10 logs (1 gig)

set serveroutput on
set feedback off
declare
rman_channels_open number;
num_logs number;
flush_logs varchar2(10);
begin
select count(*)
into rman_channels_open
from v$session
where program like 'rman%'
and client_info like 'rman channel%';
if rman_channels_open = 0 then
begin
select count(*)
into num_logs
from v$archived_log
where status = 'A';
if num_logs > 10 then
flush_logs := 'FLUSH'; --flush
else
flush_logs := 'CYCLE'; --not enough logs, flush next cycle
end if;
end;
else
flush_logs := 'RUNNING'; --flush or backup already running
end if;
dbms_output.put_line(flush_logs);
end;
/
exit;
/

It looks promissing, if you are going for 10 archive logs, I would think it is too much of a kill. But again it depends on your archive log size. If it were to be a 5MB archive then 10 is too much of an over kill. On the other hand if it were to be 256M then I'ld think its o.k. But would make to wonder why one would set the logsize to be of 256MB :D

Good luck.
Sam

I have a shell script which runs every 15 minutes which zips the logs up, it first checks to see if they are being actively used (so you don't zip one up currently being archived)

I can send to anyone if you would like it

I used to be a big proponent of gzipping archived logs. 9i changed my opinion and compressed (archivelog) backups using rman pushed this idea further away.

When gzipping archived logs, I'd be concerned about recovery involving an unzip and the cpu gzip uses to compress/decompress (even though you can gzip -1 and sacrofice a little compression for less CPU drain.)

-Ken

We got slammed with millions of updates in a period of 1 hr 15 min again last night. It put this theory of on-demand archive log flushing via rman to the ultimate test.

And it turned out much better than expected!

At peak times we were creating a 100mb archive every 7.5 seconds.

We maintained under ~2.5 gb used by archived logs on the diskgroup at any given time. So much for needing to add more disks, this process just used them more efficiently.

The process archived & flushed a total of 43 GB during the 1 hr 15 min

I/O was spread evenly across devices. ASM worked flawlessly.

-Ken

Nice to hear some feedbacks :)

Thanx,
Sam