ORACLE11g redolog查看创建修改

最新推荐文章于 2025-01-08 19:10:44 发布

原创最新推荐文章于 2025-01-08 19:10:44 发布 · 1.1k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#oracle

oracle 专栏收录该内容

5 篇文章

订阅专栏

本文介绍如何处理Oracle 11g RedoLog文件大小限制导致的Checkpoint Not Complete问题，包括检查日志切换时间、查看文件大小、管理redo log group并演示了增加、切换和删除操作。通过实例解决ORACLE-01623报错，确保数据一致性与安全。

Oracle 11g redolog默认大小为50M, 在一些业务场景下会出现报错"Checkpoint not complete"。可以观察redo log file切换时间，考虑增加文件大小，如果 redo log文件太大也可能会造成数据丢失的风险。

查看redo log

查看日志文件切换时间

SELECT
	b.recid,
	to_char( b.first_time, 'yyyy-mon-dd hh24:mi:ss' ) start_time,
	a.recid,
	to_char( a.first_time, 'yyyy-mon-dd hh24:mi:ss' ) end_time,
	round( ( ( a.first_time - b.first_time ) * 25 ) * 60, 2 ) minutes 
FROM
	v$log_history a,
	v$log_history b 
WHERE
	a.recid = b.recid + 1 
ORDER BY
	a.first_time ASC;

查看日志文件大小

SELECT GROUP#,
	members,
	BYTES / 1024 / 1024 AS "SIZE M",
	STATUS,
	ARCHIVED 
FROM
	v$log;

查看redo log file

SELECT * from v$logfile

创建、切换redo log file

查看日志文件信息

SQL> col member for a45
SQL> select group#, status, type, member from v$logfile;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ---------------------------------------------
         1         ONLINE  /data/oracle/plmdb/redo01.log
         2         ONLINE  /data/oracle/plmdb/redo02.log
         3         ONLINE  /data/oracle/plmdb/redo03.log

增加日志文件

alter database add logfile group 4 ('/data/oracle/redo01.log') size 200M;
alter database add logfile group 5 ('/data/oracle/redo02.log') size 200M;

切换当前日志文件

alter system switch logfile;
alter system switch logfile;

删除旧文件

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

重新创建、切换

解决报错 ORA-01623

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance orcl (thread 1) - cannot drop
ORA-00312: online log 3 thread 1:
'/data/oracle/redo03.log'

SQL> SELECT GROUP#,
  2     members,
  3     BYTES / 1024 / 1024 AS "SIZE M",
  4     STATUS,
  5     ARCHIVED
  6  FROM
  7     v$log;

    GROUP#    MEMBERS     SIZE M STATUS           ARC
---------- ---------- ---------- ---------------- ---
         1          1        200 UNUSED           YES
         2          1        200 INACTIVE         NO
         3          1        200 CURRENT          NO


SQL> alter system checkpoint;
SQL> alter database drop logfile group 3;
Database altered.