Oracle redo log管理
redo log一般有4种状态，
- ACTIVE：日志组记录的缓存里的脏数据已经写回磁盘后的状态
- INACTIVE：日志组记录的缓存里的脏数据还未写回磁盘后的状态
- CURRENT：当前正在使用的日志组
- UNUSED：没有使用的，一般为新建的日志组才会出现这种状态
查看日志组
select * from v$log;
查看日志文件
select * from v$logfile;
select a.group#,a.thread#,b.member,a.bytes/1024/1024 size_MB,a.members,a.archived,a.status from v$log a,v$logfile b where a.group#=b.group#;
列出日志切换的线程号和切换时间
col 00 for '999'
col 01 for '999'
col 02 for '999'
col 03 for '999'
col 04 for '999'
col 05 for '999'
col 06 for '999'
col 07 for '999'
col 08 for '999'
col 09 for '999'
col 10 for '999'
col 11 for '999'
col 12 for '999'
col 13 for '999'
col 14 for '999'
col 15 for '999'
col 16 for '999'
col 17 for '999'
col 18 for '999'
col 19 for '999'
col 20 for '999'
col 21 for '999'
col 22 for '999'
col 23 for '999'
SELECT thread#, a.ttime, SUM (c8) "08", SUM (c9) "09", SUM (c10) "10",
SUM (c11) "11", SUM (c12) "12", SUM (c13) "13", SUM (c14) "14",
SUM (c15) "15", SUM (c16) "16", SUM (c17) "17", SUM (c18) "18",
SUM (c0) "00", SUM (c1) "01", SUM (c2) "02", SUM (c3) "03",
SUM (c4) "04", SUM (c5) "05", SUM (c6) "06", SUM (c7) "07",
SUM (c19) "19", SUM (c20) "20", SUM (c21) "21", SUM (c22) "22",
SUM (c23) "23"
FROM (SELECT thread#, ttime, DECODE (tthour, '00', 1, 0) c0,
DECODE (tthour, '01', 1, 0) c1,
DECODE (tthour, '02', 1, 0) c2,
DECODE (tthour, '03', 1, 0) c3,
DECODE (tthour, '04', 1, 0) c4,
DECODE (tthour, '05', 1, 0) c5,
DECODE (tthour, '06', 1, 0) c6,
DECODE (tthour, '07', 1, 0) c7,
DECODE (tthour, '08', 1, 0) c8,
DECODE (tthour, '09', 1, 0) c9,
DECODE (tthour, '10', 1, 0) c10,
DECODE (tthour, '11', 1, 0) c11,
DECODE (tthour, '12', 1, 0) c12,
DECODE (tthour, '13', 1, 0) c13,
DECODE (tthour, '14', 1, 0) c14,
DECODE (tthour, '15', 1, 0) c15,
DECODE (tthour, '16', 1, 0) c16,
DECODE (tthour, '17', 1, 0) c17,
DECODE (tthour, '18', 1, 0) c18,
DECODE (tthour, '19', 1, 0) c19,
DECODE (tthour, '20', 1, 0) c20,
DECODE (tthour, '21', 1, 0) c21,
DECODE (tthour, '22', 1, 0) c22,
DECODE (tthour, '23', 1, 0) c23
FROM (SELECT thread#, TO_CHAR (first_time, 'yyyy-mm-dd') ttime,
TO_CHAR (first_time, 'hh24') tthour
FROM v$log_history
WHERE (SYSDATE - first_time < 30))) a
GROUP BY thread#, ttime
order by ttime;
THREAD# TTIME 08 09 10 11 12 13 14 15 16 17 18 00 01 02 03 04 05 06 07 19 20 21 22 23
---------- ---------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
1 2018-04-29 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4 0 0
1 2018-05-05 1 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1 2018-05-07 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0
1 2018-05-08 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 1 0
1 2018-05-11 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0
1 2018-05-12 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0
1 2018-05-13 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 1 0
1 2018-05-14 0 0 0 0 0 0 0 0 0 11 2 0 0 0 0 0 0 0 0 5 1 8 0 0
1 2018-05-15 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 4 6 0 1 0
1 2018-05-16 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0
1 2018-05-17 0 0 0 0 0 0 0 0 0 0 5 0 0 0 0 0 0 0 0 2 0 0 0 0
1 2018-05-21 0 0 3 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
12 rows selected
新增日志组
sys@MYSQL_MA> alter database add logfile group 4 ('/oracle/11g/oradata/mysql_master_1003306/redo04a.log','/oracle/11g/oradata/mysql_master_1003306/redo04b.log') size 50M;
Database altered.
给已有的日志组添加成员
sys@MYSQL_MA> alter database add logfile member '/oracle/11g/oradata/mysql_master_1003306/redo04c.log' to group 4;
Database altered.
删除日志组成员，并不会删除物理数据文件
sys@MYSQL_MA> alter database drop logfile member '/oracle/11g/oradata/mysql_master_1003306/redo04a.log';
Database altered.
删除日志组，并不会删除物理数据文件
sys@MYSQL_MA> alter database drop logfile group 4;
Database altered.
redo log恢复
如果inactive的日志组成员丢失，不会丢失数据，数据库没有关闭的情况下，只需要把该日志组删掉，再新建日志组即可，如果数据库关闭了，数据库是打不开的，使用以下方法恢复：
sys@MYSQL_MA> ! rm -rf /oracle/11g/oradata/mysql_master_1003306/redo01.log
sys@MYSQL_MA>
sys@MYSQL_MA> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@MYSQL_MA> startup;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2213856 bytes
Variable Size 335546400 bytes
Database Buffers 67108864 bytes
Redo Buffers 4325376 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 6188
Session ID: 1 Serial number: 5
alert 日志如下
Errors in file /oracle/11g/diag/rdbms/mysql_ma/orcl/trace/orcl_lgwr_6154.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/oracle/11g/oradata/mysql_master_1003306/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /oracle/11g/diag/rdbms/mysql_ma/orcl/trace/orcl_lgwr_6154.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/oracle/11g/oradata/mysql_master_1003306/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /oracle/11g/diag/rdbms/mysql_ma/orcl/trace/orcl_ora_6188.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/oracle/11g/oradata/mysql_master_1003306/redo01.log'
USER (ospid: 6188): terminating the instance due to error 313
Instance terminated by USER, pid = 6188
恢复redolog
##启动到mount状态
idle> start mount;
SP2-0310: unable to open file "mount.sql"
idle> startup mount;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2213856 bytes
Variable Size 335546400 bytes
Database Buffers 67108864 bytes
Redo Buffers 4325376 bytes
Database mounted.
idle>
idle> select group#,thread#,sequence#,bytes/1024/1024 size_MB,members,archived,status,first_change#,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') first_time from v$log;
GROUP# THREAD# SEQUENCE# SIZE_MB MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
------ ------- --------- ---------- ------- --- ---------------- ------------- -------------------
1 1 12 50 1 YES INACTIVE 1580033 2018-05-21 19:42:52
3 1 10 50 1 YES INACTIVE 1580027 2018-05-21 19:42:47
2 1 13 50 1 NO CURRENT 1580036 2018-05-21 19:42:53
##重建group 1
idle> alter database clear logfile group 1;
Database altered.
idle> alter database open;
Database altered.
idle> !ls -l /oracle/11g/oradata/mysql_master_1003306/redo01.log
-rw-r-----. 1 oracle oinstall 52429312 May 21 20:06 /oracle/11g/oradata/mysql_master_1003306/redo01.log
如果active日志组成员丢失，如果数据库关闭了，则会丢失数据，需要进行不完全恢复。在数据库尚未关闭的情况下，需要将active日志组转化为inactive，再按照inactive日志组丢失的方法恢复
使用alter system checkpoint可以将active日志组转化为inactive
current日志组成员丢失，在数据库尚未关闭的前提下，需要切换日志将current转化为active状态，再通过检查点转化为inactive状态，最后按照inactive日志组丢失的情况进行恢复。如果数据库已经关闭了，则需要进行不完全恢复
转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 289211569@qq.com