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

[Michael Schulte zur Sur]

Hi Stuart,

what Volker does, is establshing an alternating logging by adding group by group and forcing Oracle to use it by switch log. Since you would want to use at least two members per group for data safety you need four disks for logging. Alternating the archive destination is in my opinion not necessary, since you have only one log to archive and it is sufficient to separate log writing and log archival.

I hope, that makes sense,

Michael

[SteveKirby]

Howdy All,

The two disk rotating Redologs are something I do at two client sites.

I am afraid I was laxy when I set them up and just use Oracle Enterprise Manager.

I can give you details if you need them, but off the top of my head the way I did it (with no down time) is:

Create 4 NEW redo logs. When you create them have set to different locations (e.g. Disk group 5 = Disk array 1, Disk group 6 = disk array 2, Data group 7 = Disk group 1, etc.)

Then force log swithces until up are on Disk group 5. You can then DROP the previous 4 log groups. And .. ta-da ... you now have alternating redo groups.

If you REALLY want to clean it up you can now RECREATE the first 4 log groups on alternatin drives and DROP data groups 5-8.

There is some performance improvement. I am curious how you discovered that you redo log bound, this is not a usual condition unless you have redo logs that are too small or you have an OBSCENE amount of transaction ALL the time. The redo logs are typically switched as needed and are normally a low priority so they are not normally going to hold the system up ... plus you have BUFFERS in RAM to hold stuff...

[Volker Borowski]

Hi,

Michael has got it right, that is the reason for the switch.
Oracle does not care about the LOG# to decide which Group is next.
Been there, done that (and learned it by doing mistakes :-)
- Created group 11 / 13 / 15 / 17 on "A"s
- Created group 12 / 14 / 16 / 18 on "B"s
- Switched all the logfiles to make sure it works.
- Checked alertlog, and found it was switching
"A","A","A","A","B","B","B","B"

Had to do it again :-)
Volker

[Jakes Louw]

An alternative that we have for this would be to assign the filesystems to alternate disks:
-create your HP volume group as normal
-do the lvcreate with a null (ZERO) size
-lvextend the lvol onto the appropriate disk
-create your redo-log space as normal in Oracle DB.

In this way, you can make sure that log_1a and log_2a are on seperate disks.
Repeat for the process for the "B" redo log group.

[Stuart Abramson_2]

We're not going to have multiple archive areas.

We decided that it's not necessary to have more than one Archive Log area. You probably only write to one archive log time at a time, so there is no problem with disk contention. You do want to have multiple redo log disks, and ensure that when you are reading from redo 1 to Archive, that you write redo 2 on a different disk, etc.

[Jean-Luc Oudart]

Hi,

May be the question is related to the performance of dumping the redo onto the archive destination with no or little impact on the rest of the application.
1) How often do you have a redo switch ?
2) how long does it take ?
3) do you use same controler(s)/set of disks

Any clue from the perfstat report ?

Regards,
Jean-Luc