Question: I understand that there are times
that you need to force a log switch for backups, but I have noted
that Oracle has two ways to force a logfile switch, ALTER SYSTEM
SWITCH LOGFILE and ALTER SYSTEM ARCHIVE LOG CURRENT.
What is the difference between
ALTER SYSTEM SWITCH LOGFILE and ALTER SYSTEM ARCHIVE LOG CURRENT,
and when do I use each?
Answer: Yes, both
ALTER SYSTEM SWITCH LOGFILE and ALTER SYSTEM ARCHIVE LOG CURRENT
will force a log switch, but they do it in different ways!
Both the SWITCH LOGFILE and ARCHIVE LOG CURRENT write a quiesce
checkpoint, a firm place whereby that last redo log is a part of the
hot backup, but ARCHIVE LOG CURRENT waits for the writing to
complete. This can take several minutes for multi-gigabyte redo
logs.
Conversely, the ALTER SYSTEM SWITCH LOGFILE command is very fast
and returns control to the caller in less than a second while ALTER
SYSTEM ARCHIVE LOG CURRENT pauses.
As we see below, the ALTER SYSTEM SWITCH LOGFILE is fast because
it does not wait for the archiver process (ARCH) to complete writing
the online redo log to the archivelog log filesystem:
- It issues database checkpoint
- It immediately starts writing to the next redo log
- In the background, the "switch logfile" command tells
the ARCH background process to copy the "old" redo log file to
the redo log filesystem.
Here are the important differences between
ALTER SYSTEM SWITCH LOGFILE and ALTER SYSTEM ARCHIVE LOG CURRENT:
- RAC: If you are running RAC, the
ALTER SYSTEM ARCHIVE LOG CURRENTwill switch the logs on
all RAC nodes (instances), whereas
ALTER SYSTEM SWITCH LOGFILE will only switch he logfile
on the instance where you issue the switch command. Hence,
ALTER SYSTEM ARCHIVE LOG CURRENT is a best practice for
RAC systems.
- ALTER SYSTEM SWITCH LOGFILE is
asynchronous: This command is fast to return to the invoking
program because the writing of the redo log to the OS filesystem
is done in the background. There is a very small risk in cases
where the ARCH process cannot complete writing the redo log,
such as cases where the OS archivelog file directory is out of
space. It is also risky because the calling script may move on
to a subsequent step, assuming that the redo has been written.
Some scripts will place a SLEEP 60 command in their backup
script to allow time for the redo to complete writing, but this
is not a best practice.
- ALTER SYSTEM ARCHIVE LOG CURRENT is
synchronous: This is faster to return because this command
waits until the online redo log has completed the writing of the
redo log file to the filesystem. This command is safer because
it waits for the OS to acknowledge (ACK) that the redo log has
been successfully written. Hence,
ALTER SYSTEM ARCHIVE LOG CURRENT is the best practice for
production backup scripts with RMAN.
- Argument required: The
ALTER SYSTEM ARCHIVE LOG CURRENT allows you to specify the
thread to archive while the
ALTER SYSTEM SWITCH LOGFILE archives only the current
thread. If you do not pass the thread parameter, Oracle will
archive all full online redo logs.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
