004、日志文件和SCN
日志文件
- 记录数据库的变化；
- 顺序写，循环写；
- 按照组的模式组织到一起；
- 组是逻辑的概念，通过v$log来查看；
- 组内的文件叫做成员；
- 成员是物理文件，同组内的成员是镜像关系；
- 一组写完，写下一组的过程叫做切换；
- 我们随时可以手工的切换：alter system switch log file;
- 日志的切换触发检查点的发生；
- 数据库做任何事情，都伴随一个号码，这个号是自动的发生，是连续的，顺序的：SCN=system change number;
若实例崩溃，查看当前需要进行介质恢复的scn号：
SQL> select current_scn,checkpoint_change#,current_scn - checkpoint_change# from v$database;
CURRENT_SCN CHECKPOINT_CHANGE# CURRENT_SCN-CHECKPOINT_CHANGE#
----------- ------------------ ------------------------------
1222920 1221374 1546SCN号的上限是16进制，12个f，可以从v$log中查看：
SQL> select * from v$log; --oracle 10G的v$log中，没有这些字段：BLOCKSIZE，NEXT_CHANGE，NEXT_TIME
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 15 52428800 512 1 NO CURRENT 251358 21-JUN-21 2.8147E+14
2 1 14 52428800 512 1 NO INACTIVE 187270 13-JUN-21 251358 21-JUN-21
3 2 1 52428800 512 1 NO INACTIVE 197263 13-JUN-21 226735 13-JUN-21
4 2 2 52428800 512 1 NO CURRENT 226735 13-JUN-21 2.8147E+14
SQL> select to_number('ffffffffffff','xxxxxxxxxxxxxxxx') from dual;
TO_NUMBER('FFFFFFFFFFFF','XXXXXXXXXXXXXXXX')
--------------------------------------------
2.8147E+14查看纪录被修改时的SCN号：
SQL> select ora_rowscn,ename,empno from scott.emp where rownum <3;
ORA_ROWSCN ENAME EMPNO
---------- ---------- ----------
200263 SMITH 7369
200263 ALLEN 7499
SQL> update scott.emp set sal=sal+1 where empno=7369;
1 row updated.
SQL> select ora_rowscn,ename,empno from scott.emp where rownum <3;
ORA_ROWSCN ENAME EMPNO
---------- ---------- ----------
200263 SMITH 7369
200263 ALLEN 7499
SQL> commit;
Commit complete.
SQL> select ora_rowscn,ename,empno from scott.emp where rownum <3;
ORA_ROWSCN ENAME EMPNO
---------- ---------- ----------
252689 SMITH 7369
252689 ALLEN 7499查看日志组状态，注意观察status和first_change#：
SQL> select * from v$log where thread#=1;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 15 52428800 512 1 NO CURRENT 251358 21-JUN-21 2.8147E+14
2 1 14 52428800 512 1 NO INACTIVE 187270 13-JUN-21 251358 21-JUN-21
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log where thread#=1;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 15 52428800 512 1 NO ACTIVE 251358 21-JUN-21 254244 21-JUN-21
2 1 16 52428800 512 1 NO CURRENT 254244 21-JUN-21 2.8147E+14
--注意观察日志的状态
--active状态为检查点未完成，当检查点完成后，状态由active变为inactive；
--active状态的日志组，若实例崩溃，恢复时需要使用到
--current为当前正在使用的日志
SQL> select current_scn,checkpoint_change#,current_scn - checkpoint_change# from v$database;
CURRENT_SCN CHECKPOINT_CHANGE# CURRENT_SCN-CHECKPOINT_CHANGE#
----------- ------------------ ------------------------------
254256 251359 2897
--可以手动存盘，使active状态的日志变为inactive
SQL> alter system checkpoint;
System altered.
SQL> select * from v$log where thread#=1;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 15 52428800 512 1 NO INACTIVE 251358 21-JUN-21 254244 21-JUN-21
2 1 16 52428800 512 1 NO CURRENT 254244 21-JUN-21 2.8147E+14检查点
完全检查点：
- 一致性停止数据库；
- 手工存盘：alter system checkpoint;
所有的脏数据，统一的写。
- alter system switch logfile;
- 自动切换日志组；
- 数据库内部触发的检查点。
检查点列表，写的是部分数据块。
查看数据库内部触发检查点的时间间隔：
SQL> show parameter log_checkpoint_timeout
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_checkpoint_timeout integer 1800日志的切换过程，有两个条件：
- 在使用下一个日志之前，下一个日志的状态是inactive的，若下一个日志组的状态是active的，则日志切换会卡住；
- 初始化下一个将要使用的日志文件，初始化的时候，日志组的状态变为unused；
例如，一个会话疯狂切换日志，另一个会话查看日志组的状态：
SQL> alter system switch logfile; --会话1疯狂切换日志
System altered.
SQL> select GROUP#,SEQUENCE#,STATUS from v$log where THREAD#=1;--会话2监控日志组状态
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 17 CURRENT
2 16 INACTIVE
SQL> /
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 87 CURRENT
2 0 UNUSED --监控到日志组状态为unused日志文件的维护
1、增加日志组
可以指定日志组的号，若不指定，按顺序增加。新增加的日志文件状态是unused.
SQL> alter database add logfile group 8 '/u01/scripts/redo8.log' size 5m; --增加日志组，并指定组号和日志文件路径，大小
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- --------------------------------------------- ---
1 ONLINE +DATADG/o11g/onlinelog/group_1.257.1075132159 NO
2 ONLINE +DATADG/o11g/onlinelog/group_2.258.1075132159 NO
3 ONLINE +DATADG/o11g/onlinelog/group_3.265.1075133329 NO
4 ONLINE +DATADG/o11g/onlinelog/group_4.266.1075133329 NO
8 ONLINE /u01/scripts/redo8.log NO
SQL> select GROUP#,THREAD#,SEQUENCE#,STATUS from v$log where THREAD#=1;
GROUP# THREAD# SEQUENCE# STATUS
---------- ---------- ---------- ----------------
1 1 93 CURRENT
2 1 92 INACTIVE
8 1 0 UNUSED --状态为unused
SQL> select GROUP#,THREAD#,SEQUENCE#,STATUS from v$log where THREAD#=1; --另一个会话切换日志，再查看日志组的状态
GROUP# THREAD# SEQUENCE# STATUS
---------- ---------- ---------- ----------------
1 1 93 INACTIVE
2 1 92 INACTIVE
8 1 94 CURRENT2、日志组添加成员
日志组添加成员，不需要指定大小，路径可以不同，同一组内的日志文件是镜像关系，每组文件数最多5个：
SQL> alter database add logfile member '+DATADG' to group 8; --日志组添加成员
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- --------------------------------------------- ---
1 ONLINE +DATADG/o11g/onlinelog/group_1.257.1075132159 NO
2 ONLINE +DATADG/o11g/onlinelog/group_2.258.1075132159 NO
3 ONLINE +DATADG/o11g/onlinelog/group_3.265.1075133329 NO
4 ONLINE +DATADG/o11g/onlinelog/group_4.266.1075133329 NO
8 ONLINE /u01/scripts/redo8.log NO
8 INVALID ONLINE +DATADG/o11g/onlinelog/group_8.268.1075930615 NO
6 rows selected.
--一直添加，直到报错为止
SQL> alter database add logfile member '+DATADG' to group 8;
alter database add logfile member '+DATADG' to group 8
*
ERROR at line 1:
ORA-00357: too many members specified for log file, the maximum is 5
--查看日志文件，状态为invalid，到下一次日志切换之后，变为正常
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- --------------------------------------------- ---
1 ONLINE +DATADG/o11g/onlinelog/group_1.257.1075132159 NO
2 ONLINE +DATADG/o11g/onlinelog/group_2.258.1075132159 NO
3 ONLINE +DATADG/o11g/onlinelog/group_3.265.1075133329 NO
4 ONLINE +DATADG/o11g/onlinelog/group_4.266.1075133329 NO
8 ONLINE /u01/scripts/redo8.log NO
8 INVALID ONLINE +DATADG/o11g/onlinelog/group_8.268.1075930615 NO
8 INVALID ONLINE +DATADG/o11g/onlinelog/group_8.269.1075931021 NO
8 INVALID ONLINE +DATADG/o11g/onlinelog/group_8.270.1075931023 NO
8 INVALID ONLINE +DATADG/o11g/onlinelog/group_8.271.1075931023 NO
9 rows selected.3、日志组删除成员
删除日志组成员需要满足两个条件：
- 要删除成员的日志组当前状态不能是current；
SQL> select GROUP#,THREAD#,SEQUENCE#,STATUS from v$log where THREAD#=1; --查看当前日志状态，日志组8是current GROUP# THREAD# SEQUENCE# STATUS ---------- ---------- ---------- ---------------- 1 1 99 ACTIVE 2 1 98 ACTIVE 8 1 100 CURRENT SQL> alter database drop logfile member '+DATADG/o11g/onlinelog/group_8.271.1075932327'; --删除当前状态是current的日志组成员，无法删除 alter database drop logfile member '+DATADG/o11g/onlinelog/group_8.271.1075932327' * ERROR at line 1: ORA-01609: log 8 is the current log for thread 1 - cannot drop members ORA-00312: online log 8 thread 1: '/u01/scripts/redo8.log' ORA-00312: online log 8 thread 1: '+DATADG/o11g/onlinelog/group_8.268.1075930615' ORA-00312: online log 8 thread 1: '+DATADG/o11g/onlinelog/group_8.269.1075931021' ORA-00312: online log 8 thread 1: '+DATADG/o11g/onlinelog/group_8.270.1075931023' ORA-00312: online log 8 thread 1: '+DATADG/o11g/onlinelog/group_8.271.1075932327' --手工切换后，再次删除： SQL> alter system switch logfile; System altered. SQL> select GROUP#,THREAD#,SEQUENCE#,STATUS from v$log where THREAD#=1; GROUP# THREAD# SEQUENCE# STATUS ---------- ---------- ---------- ---------------- 1 1 99 ACTIVE 2 1 101 CURRENT 8 1 100 ACTIVE --删除日志组成员 SQL> alter database drop logfile member '+DATADG/o11g/onlinelog/group_8.271.1075932327'; Database altered. - 要删除的成员不能是该组最后一个成员，即日志组仅剩一个成员时，该成员不能删除；
--删除日志组成员，直到最后一个，并查看报错 …… SQL> alter database drop logfile member '+DATADG/o11g/onlinelog/group_8.270.1075931023'; Database altered. SQL> alter database drop logfile member '/u01/scripts/redo8.log'; --日志组的最后一个成员无法删除 alter database drop logfile member '/u01/scripts/redo8.log' * ERROR at line 1: ORA-00361: cannot remove last log member /u01/scripts/redo8.log for group 8
4、日志文件重命名
日志文件重命名，需要当前的日志组状态为非current的，分为两步：
- 使用操作系统命令复制日志文件到目标路径
--使用命令复制日志文件到指定路径并且修改文件名 SQL> host cp '/u01/scripts/redo8.log' '/u01/scripts/redo08.log' - 修改控制文件指针，指向最新的日志文件
--报错，不能修改当前日志组(current)的成员名称 SQL> alter database rename file '/u01/scripts/redo8.log' to '/u01/scripts/redo08.log'; alter database rename file '/u01/scripts/redo8.log' to '/u01/scripts/redo08.log' * ERROR at line 1: ORA-01511: error in renaming log/data files ORA-01621: cannot rename member of current log if database is open ORA-00312: online log 8 thread 1: '/u01/scripts/redo8.log' --切换日志，再重新rename SQL> alter system switch logfile; System altered. SQL> alter database rename file '/u01/scripts/redo8.log' to '/u01/scripts/redo08.log'; Database altered. SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- --------------------------------------------- --- 1 ONLINE +DATADG/o11g/onlinelog/group_1.257.1075132159 NO 2 ONLINE +DATADG/o11g/onlinelog/group_2.258.1075132159 NO 3 ONLINE +DATADG/o11g/onlinelog/group_3.265.1075133329 NO 4 ONLINE +DATADG/o11g/onlinelog/group_4.266.1075133329 NO 8 ONLINE /u01/scripts/redo08.log NO --文件重命名后，并不会自动删除，需要手动删除： [oracle@rac1 scripts]$ ls -l 总用量 10248 -rw-r----- 1 oracle oinstall 5243392 6月 22 22:25 redo08.log -rw-r----- 1 oracle asmadmin 5243392 6月 22 22:27 redo8.log
5、清除日志组
清除组以后，日志状态为unused：
SQL> alter database clear logfile group 8;
Database altered.
SQL> select GROUP#,THREAD#,SEQUENCE#,STATUS from v$log where THREAD#=1;
GROUP# THREAD# SEQUENCE# STATUS
---------- ---------- ---------- ----------------
1 1 102 INACTIVE
2 1 104 CURRENT
8 1 0 UNUSED清除日志组，等于删除组，再增加组：
--手动删除日志组文件
[oracle@rac1 scripts]$ rm /u01/scripts/redo08.log
SQL> alter database clear logfile group 8;--清除日志组
Database altered.
--查看日志是否存在
[oracle@rac1 scripts]$ ls -l /u01/scripts/redo08.log
-rw-r----- 1 oracle asmadmin 5243392 6月 22 22:40 /u01/scripts/redo08.log清除日志组，需要日志组的状态不能是current：
SQL> select GROUP#,THREAD#,SEQUENCE#,STATUS from v$log where THREAD#=1;
GROUP# THREAD# SEQUENCE# STATUS
---------- ---------- ---------- ----------------
1 1 102 INACTIVE
2 1 104 CURRENT
8 1 0 UNUSED
SQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance o11g1 (thread 1)
ORA-00312: online log 2 thread 1: '+DATADG/o11g/onlinelog/group_2.258.1075132159'6、删除日志组
删除日志组需要满足：
- 要删除的日志组状态不能是active的
SQL> select GROUP#,THREAD#,SEQUENCE#,STATUS from v$log where THREAD#=1; GROUP# THREAD# SEQUENCE# STATUS ---------- ---------- ---------- ---------------- 1 1 106 CURRENT 2 1 104 ACTIVE 8 1 105 ACTIVE SQL> alter database drop logfile group 8; alter database drop logfile group 8 * ERROR at line 1: ORA-01624: log 8 needed for crash recovery of instance o11g1 (thread 1) ORA-00312: online log 8 thread 1: '/u01/scripts/redo08.log' - 要删除的日志组状态不能是current的
SQL> select GROUP#,THREAD#,SEQUENCE#,STATUS from v$log where THREAD#=1; GROUP# THREAD# SEQUENCE# STATUS ---------- ---------- ---------- ---------------- 1 1 106 INACTIVE 2 1 107 INACTIVE 8 1 108 CURRENT SQL> alter database drop logfile group 8; alter database drop logfile group 8 * ERROR at line 1: ORA-01623: log 8 is current log for instance o11g1 (thread 1) - cannot drop ORA-00312: online log 8 thread 1: '/u01/scripts/redo08.log' - 若碰见以上两种情况，可以手动切换，手动存盘，将要删除的日志组状态改为inactive
SQL> alter system switch logfile; System altered. SQL> alter system checkpoint; System altered. SQL> select GROUP#,THREAD#,SEQUENCE#,STATUS from v$log where THREAD#=1; GROUP# THREAD# SEQUENCE# STATUS ---------- ---------- ---------- ---------------- 1 1 109 CURRENT 2 1 107 INACTIVE 8 1 108 INACTIVE SQL> alter database drop logfile group 8; Database altered. --最后还需要手动删除残留的文件 [oracle@rac1 scripts]$ ls -l 总用量 5124 -rw-r----- 1 oracle asmadmin 5243392 6月 22 22:50 redo08.log
浙公网安备 33010602011771号