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,
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 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
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
It’s interesting that many of the bloggers to helped clarify a few things for me as well as giving.
Most of ideas can be nice content.The people to give them a good shake to get your point and across the command.
Shivaji Bade says
Thanks for this Post
yes, definitely this type of blogs are really help for people great job.
Nice blog. Thank you for sharing your experiences with us and keep going on.
Thanks for the feedback
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.
Mohd Sharique says
Thanks for sharing information to our knowledge, it helps me plenty keep sharing…
Amlida James says
Thank for sharing excellent post on your blog keep it up and share more.
Prince Arora says
This is most informative and also this post most user-friendly and super navigation to all posts.