Last updated on May 29th, 2019 at 05:39 pm
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
from v$log l,
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
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