Home » Oracle » Oracle Database » alter system switch logfile v/s archive log current

alter system switch logfile v/s archive log current

Oracle 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.

See also  How to delete statistics from Table/index/column in Oracle

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 oracle database backup.  This will ensure all the red generate during the hot-backup  will get archived and get backed easily

Hope this short post make the point clear for alter system switch logfile and alter system archive log current

Also Reads
v$system_parameter
supplemental logging in Oracle
how to get table definition in oracle
Query to find object dependencies in oracle
how to find archive log sequence number in oracle

9 thoughts on “alter system switch logfile v/s archive log current”

  1. I simply wanted to thank you so much again. I am not sure the things that I might have gone through without the type of hints revealed by you regarding that situation.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top