↓ 跳过正文
Greatfinish Blog

Oracle 单实例在线 Redo Log 日志文件大小调整实战

·432 字·3 分钟
Oracle Oracle Redo Log Online Redo 日志组 数据库运维 DBA
Greatfinish
作者
Greatfinish
记录 Oracle、PostgreSQL、达梦、Linux、存储与生产环境故障处理经验。
目录

Oracle单实例调整在线重做日志文件大小的步骤如下：

查看当前日志的大小
#

sqlplus / as sysdba
select group#, bytes, status from v$log;

操作如下

[oracle@ora-test ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 6 14:56:01 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select group#, bytes, status from v$log;

    GROUP#      BYTES STATUS
---------- ---------- --------------------------------
         1   52428800 CURRENT
         2   52428800 INACTIVE
         3   52428800 INACTIVE

SQL>

上面的在线重做日志为 50MB，我们将其大小调整为 1GB
#

查看在线重做日志位置

select group#, member from v$logfile;

操作日志

SQL> select group#, member from v$logfile;

    GROUP# MEMBER
---------- ------------------------------------------------------------
         1 /u01/app/oracle/oradata/orcl/redo01.log
         2 /u01/app/oracle/oradata/orcl/redo02.log
         3 /u01/app/oracle/oradata/orcl/redo03.log

SQL>

创建 3 个新的日志组
#

它们命名为组 4、5 和 6，每个大小为 1GB

alter database add logfile group 4 '/u01/app/oracle/oradata/orcl/redo04.log' size 1G;
alter database add logfile group 5 '/u01/app/oracle/oradata/orcl/redo05.log' size 1G;
alter database add logfile group 6 '/u01/app/oracle/oradata/orcl/redo06.log' size 1G;

操作如下

SQL> alter database add logfile group 4 '/u01/app/oracle/oradata/orcl/redo04.log' size 1G;

Database altered.

SQL> alter database add logfile group 5 '/u01/app/oracle/oradata/orcl/redo05.log' size 1G;

Database altered.

SQL> alter database add logfile group 6 '/u01/app/oracle/oradata/orcl/redo06.log' size 1G;

Database altered.

SQL>

查询v$log查看在线重做日志状态
#

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

操作如下

SQL> select group#, bytes, status from v$log;

    GROUP#      BYTES STATUS
---------- ---------- --------------------------------
         1   52428800 CURRENT
         2   52428800 INACTIVE
         3   52428800 INACTIVE
         4 1073741824 UNUSED
         5 1073741824 UNUSED
         6 1073741824 UNUSED

6 rows selected.

SQL>

切换在线重做日志文件到第4组
#

优先删除状态是INACTIVE,如果是ACTIVE的状态的话，手动发起检查点

alter system switch logfile;
select group#, status from v$log;
alter system checkpoint;

操作如下

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- --------------------------------
         1 ACTIVE
         2 INACTIVE
         3 INACTIVE
         4 ACTIVE
         5 ACTIVE
         6 CURRENT

6 rows selected.

SQL> alter system checkpoint;

System altered.

SQL> select group#, status from v$log;

    GROUP# STATUS
---------- --------------------------------
         1 INACTIVE
         2 INACTIVE
         3 INACTIVE
         4 INACTIVE
         5 INACTIVE
         6 CURRENT

6 rows selected.

SQL>

删除原在线重做日志文件1~3组
#

注意状态是INACTIVE可删除

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

操作如下

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL>

检查调整后的在线重做日志
#

select group#, bytes, status from v$log;
select group#, member from v$logfile;

操作如下

SQL> select group#, bytes, status from v$log;

    GROUP#      BYTES STATUS
---------- ---------- --------------------------------
         4 1073741824 INACTIVE
         5 1073741824 INACTIVE
         6 1073741824 CURRENT

SQL> select group#, member from v$logfile;

    GROUP# MEMBER
---------- ------------------------------------------------------------
         4 /u01/app/oracle/oradata/orcl/redo04.log
         5 /u01/app/oracle/oradata/orcl/redo05.log
         6 /u01/app/oracle/oradata/orcl/redo06.log

SQL>