在线扩大redo日志大小

转载

黄宝黄宝 2021-07-28 10:46:51 博主文章分类：oracle

由于ORACLE并没有提供类似RESIZE的参数来重新调整REDO LOG
FILE的大小，因此只能先把这个文件删除了，然后再重建。又由于ORACLE要求最少有两组日志文件在用，所以不能直接删除，必须要创建中间过渡的REDO
LOG日志组。

查看日志组状态

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

查看当前日志组成员

SQL> select member from v$logfile;

创建2个新的过渡日志组

SQL> alter database add logfile group 4('/data/u01/app/oracle/oradata/orcly/redo04.log') size 500M;
SQL> alter database add logfile group 5('/data/u01/app/oracle/oradata/orcly/redo05.log') size 500M;

切换日志并做检查点，确定123组日志处于inactive状态

SQL> alter system switch logfile;
SQL> alter system checkpoint;
SQL> select group#,members,bytes/1024/1024,status from v$log;


    GROUP#    MEMBERS BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
         1          1              50 INACTIVE
         2          1              50 INACTIVE
         3          1              50 INACTIVE
         4          1             500 ACTIVE
         5          1             500 CURRENT

数据库删除123组日志

SQL> alter database drop logfile group 1
SQL> alter database drop logfile group 2
SQL> alter database drop logfile group 3

OS删除文件(需要手工删除)

$rm redo01.log redo02.log redo03.log

数据库添加123组日志

SQL> alter database add logfile group 1('/data/u01/app/oracle/oradata/orcly/redo01.log') size 2048M;
SQL> alter database add logfile group 2('/data/u01/app/oracle/oradata/orcly/redo02.log') size 2048M;
SQL> alter database add logfile group 3('/data/u01/app/oracle/oradata/orcly/redo03.log') size 2048M;