[Oracle OCP 052-1-6]Redo日志切换

核心SQL
select * from v$log;
alter system switch logfile;
alter system checkpoint;

核心SQL解释

1. select * from v$log;
   v$log中status指的是重做日志文件组的状态，各状态含义：
   CURRENT：当前的，正在被使用的日志文件，同一时刻只有一个日志文件会处于CURRENT状态，处于CURRENT的日志文件被LGWR进程实时的写入
   ACTIVE：活动的，未被LGWR写，刚切换完成，尚未发生检查点，实例恢复时需要的重做日志文件为ACTIVE（处于ACTIVE的日志文件组无法删除）
   INACTIVE：不活动的，实例恢复时不需要的重做日志文件为INACTIVE
   UNUSED：还没有被写入的空白日志文件
   CLEARING：正在被清除的日志文件，日志清除后状态变为UNUSED
   CLEARING_CURRENT：正在被清除的CURRENT日志文件

2. alter system switch logfile;
   手动切换日志

3. alter system checkpoint;
   checkpoint由ckpt进程触发oracle进行checkpoint动作,将data buffer中的脏块(已经写在
   redo里记录但是没有写到datafile里的)的内容写入到data file里并释放占用的空间,由
   dbw后台进程完成,并修改controlfile和datafile的scn.
   一般手工执行是由于要删除某个日志但是该日志里还有没有同步到data file里的内容,就
   需要手工check point来同步数据,然后就可以drop logfile group n.

实验详细

idle>set linesize 150;
idle>select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1         19   52428800        512          1 YES INACTIVE               1412792 10-JUN-19      1435515 15-JUN-19
         2          1         20   52428800        512          1 YES INACTIVE               1435515 15-JUN-19      1474749 16-JUN-19
         3          1         21   52428800        512          1 NO  CURRENT                1474749 16-JUN-19   2.8147E+14

idle>
idle>
idle>
idle>
idle>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

idle>set linesize 150;
idle>select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------
NEXT_TIME
-------------------
         1          1         19   52428800        512          1 YES INACTIVE               1412792 2019-06-10 22:00:17      1435515
2019-06-15 09:54:35

         2          1         20   52428800        512          1 YES INACTIVE               1435515 2019-06-15 09:54:35      1474749
2019-06-16 10:53:46

         3          1         21   52428800        512          1 NO  CURRENT                1474749 2019-06-16 10:53:46   2.8147E+14



idle>
idle>
idle>
idle>
idle>Alter system switch logfile;

System altered.

idle>set linesize 150;
idle>select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------
NEXT_TIME
-------------------
         1          1         22   52428800        512          1 NO  CURRENT                1483267 2019-06-16 12:32:10   2.8147E+14


         2          1         20   52428800        512          1 YES INACTIVE               1435515 2019-06-15 09:54:35      1474749
2019-06-16 10:53:46

         3          1         21   52428800        512          1 NO  ACTIVE                 1474749 2019-06-16 10:53:46      1483267
2019-06-16 12:32:10


idle>
idle>
idle>Alter system checkpoint;

System altered.

idle>
idle>
idle>set linesize 150;
idle>select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------
NEXT_TIME
-------------------
         1          1         22   52428800        512          1 NO  CURRENT                1483267 2019-06-16 12:32:10   2.8147E+14


         2          1         20   52428800        512          1 YES INACTIVE               1435515 2019-06-15 09:54:35      1474749
2019-06-16 10:53:46

         3          1         21   52428800        512          1 NO  INACTIVE               1474749 2019-06-16 10:53:46      1483267
2019-06-16 12:32:10


idle>

Reference
https://blog.csdn.net/songyundong1993/article/details/53765943
https://blog.csdn.net/xuemeilu/article/details/52576826