How does the Oracle DBA get redo -> archive to alternate archive log filesystems?

[Stuart Abramson_2]

At my last job the DBAs had it set up so that the redo logs and archive log file systems were "grouped": i.e. - redo logs 1 and 3 were on one disk, and would always archive to archive file system A, and redo logs 2 and 4 were on another disks and would archive to archive file system B.

This had the effect of ensuring that when you switched from redo log 1 to 2 redo log writing would switch disks, and archive logging from redo log 1 to disk A wouldn't interfere with writing to redo log 2.

Does this sound possible?

How does an Oracle DBA set this up? (Our DBAs had never heard of this.) Can you give me a clue to tell the DBAs here?

[Tim Sanko]

It jest has to be set up that way.
Jethro says that it just is so simple to do it that way...

Now that we get the Bumpkin out of the Bumpkin(grin), The term they will understand is "Archive destination". They can look it up at Oracle's website (One Real ....).

It really is only necessary when you are running multiple archivers, in a high volume, interactive transactional environment.

If that is the case, I can explain it to the DBA's in a memo Just send an e-mail to
tjsankoATbabcockdotcom (fix the syntax there to be sure.)

Tim

[Claudio Cilloni]

You can obtain this simply specifing the location of the logfiles on different filesystems that are on different disks:

SQL> alter database add logfile group 1 ('/diskA/redologs/redo01.rdo') size 10M;

SQL> alter database add logfile group 2 ('/diskB/redologs/redo02.rdo') size 10M;

SQL> alter database add logfile group 3 ('/diskA/redologs/redo03.rdo') size 10M;

SQL> alter database add logfile group 4 ('/diskB/redologs/redo04.rdo') size 10M;

logfiles 1 and 3 are on /diskA, logfiles 2 and 4 are on /diskB.

hth,
Claudio

[Stuart Abramson_2]

Claudio:

Thanks.

That doesn't explain how I get redo log 1 to archive to Disk A, and redo 2 to archive to Disk B, etc.

Stuart

[Volker Borowski · Accepted solution]

Hi,

this is standard setup in SAP installations, although you usually do no spend 73GB disks to hold some online redologs only today.

Put redolog group 1 to
..../origlogA (one member)
..../mirrlogA (if mirrored by oracle)

Put redolog group 2 to
..../origlogB (one member)
..../mirrlogB (if mirrored by oracle)

for group 3 start over on the "A" filesystems.

Watch it, oracle does not care about group-numbers for sequence ! So do it this way:

drop your group 1
create your new group 1
switch logfile and make sure that group 1 is current
drop 2
create new group 2
switch logfile and make sure that group 2 is current
...

Check your alertlog after you finished.

The idea is to switch from A to B and from B to A, so whenever the logwriter is writing to on disk (A), that the archiver can read from the other (B) and vice versa.

Again, you need to execute the correct sequence. If you do all "A"s first and the "B"s afterwards, you will have the wrong sequence, but your alertlog will tell you.

And be sure not to mix up your mirrors with "A"s and "B"s !

It is not possible to alternate the odd and even Lognumbers to diffrent archive destinations, so the archiver will always write on disk "C" (when online logs not mirrored) or on disk "E" (assuming "C" and "D" will be mirrA and mirrB in this case).

Hope this helps
Volker

[Stuart Abramson_2]

Volker:

My last job was at an SAP/Oracle site.

What does "drop group 1" mean?

Stuart

[Claudio Cilloni]

Sorry... I didn't understand well your question. You want to put the archive log files generated from the redo logs 1 & 3 on /diskA, and the archive logs files that come from redo 2 & 4 on /diskB. Right?

If it is so... I don't know how to do that :-(

My answer tries to do this: when the LGWR process fills one logfile on /diskA, it starts writing a logfile on /diskB; then, the ARCn process reads the filled logfile from /diskA, without interfere with LGWR that is using /diskB. It is a good idea to use a diskC as destination for the archived log
files.

Ciao
Claudio

[Volker Borowski]

Hi Stuart,

this only refers to the oracle lognumber.
You may need to check how yours are numbered in table V$LOG and v$LOGFILE.

In SAP you may have lognumbers 11 / 12 / 13 /14, but they may be numbered 1 / 2 / 3 /4 as well.

The action goes i.e. like this:

alter database drop logfile group 1;
alter database add logfile group 1
('/oracle/QAS/origlogA/log_g1m1.dbf',
'/oracle/QAS/mirrlogA/log_g1m2.dbf') size 900M;
alter system switch logfile;

alter database drop logfile group 2;
alter database add logfile group 2
('/oracle/QAS/origlogB/log_g2m1.dbf',
'/oracle/QAS/mirrlogB/log_g2m2.dbf') size 900M;
alter system switch logfile;


Volker

[Brian Crabtree]

Stuart,

Oracle doesn't set things up this way. Normally, archivelogs are all archived into a single directory.

Now, how you could do it. If you deactivated automatic archivelogging (NOT RECOMMENDED), you could run a process that could go into the database, check to see for archivelog groups that are not archived, change the log_archive_dest, and manually run an archive for them. However, you would definately increase your chances of hanging the database if the job doesn't run, the process hangs, or too much data is going through for it to keep up.

I wouldn't recommend doing this at all, due to the problems. I would just recommend allocating a large amount of space to the archive directory, and look into the log_archive_max_processes parameter. This won't resolve I/O problems going to the disk, but should increase the speed that logs can be archived over.

Thanks,

Brian

[Sanjay Kumar Suri]

Although forum members have already answered the post, my say is:

1. Redo activity is highly I/O bound so it is recommended to keep alter redo log groups on alternate disks so that when redo log is being written in diskA, archiver is busy reading completed redo from diskB and archiving to diskC.

2. All archive file must be on one destination as specified by log_archive_dest parameter. It is possible to specify the alternate location by log_archive_duplex_dest or log_archive_dest_n parameters.

3. Sufficient disk space should be allocated in the archive location to avoid "Archiver Stuck" kind of problems.


sks