alter system switch logfile v/s archive log current

Redo and archive log files are two important pieces in Oracle database environment. They both are used for recovering the database from failure.I would be explaining two important command alter system switch logfile and alter system archive log current about them ,their difference and what they command actually performs in the oracle database

ALTER SYSTEM SWITCH LOGFILE

This command switch the redo log in the instance where it is executed. So log sequence move to another number by executing the command.

Also The SQL statement ALTER SYSTEM SWITCH LOGFILE forces the current instance to begin writing to a new redo log file, regardless of whether the current redo log file is full.

Forcing a log switch also forces a checkpoint. Oracle returns control to you immediately after beginning the log switch, rather than waiting until the checkpoint is finished.

This SQL statement does not ensure the redo log is archived. So log switch happened only, archiving of the redo log might happen at latter point

Also this perform the Log switch in the current instance only. If we have Oracle RAC environment ,the command need to executed desperately for each instance

You can use below query to check the status of redo log files after executing the command

select l.thread#, l.group#, l.archived, l.status, bytes/1024/1024 MB
from v$log l,
v$instance i
where l.thread# = i.thread#
order by 1, 2

ALTER SYSTEM ARCHIVE LOG CURRENT

This statement forces all the redo log files to get archived before control is returned to it.So it perform the switch and archiving both.

Also in case of Oracle RAC environment, it forces the global switch in all the instance  and archiving also

In case you want to execute this for particular thread ,you need to specify the thread number

ALTER SYSTEM ARCHIVE LOG CURRENT THREAD 1 ;

In-case we are taking Hot-backup of the oracle database , it is recommended to use the alter system archive log current command at the beginning of the backup and at the end of the backup.  This will ensure all the red generate during the hot-backup  will get archived and get backed easily

One thought on “alter system switch logfile v/s archive log current”

Leave a Reply