1、查看当前redo日志状态
SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 209715200 INACTIVE
2 209715200 CURRENT
3 209715200 UNUSED
SQL> select group#, member from v$logfile;
GROUP# ---------- MEMBER
--------------------------------------------------------------------------------
3 /data/orcl/redo03.log
2 /data/orcl/redo02.log
1 /data/orcl/redo01.log
目前,单个redo日志大小为200M
2、改变日志大小
2.1、不能删除 current状态下日志组
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance orcl (thread 1) - cannot drop
ORA-00312: online log 2 thread 1: '/data/orcl/redo02.log'
可手动切换redo日志,改变当前日志组。
2.2、不能删除active状态下日志组
手动执行检查点,因active状态日志为检查点尚未完成的状态。
2.3、添加删除日志组1,改变大小
SQL> alter database drop logfile group 1;
Database altered.
手动删除物理文件后,添加日志组1
SQL> alter database add logfile group 1 '/data/orcl/redo01.log' size 300M;
Database altered.
依次删除日志组,添加日志组;切换redo日志,改变所有组redo大小。
3、验证
SQL> select group#, bytes, status from v$log;
GROUP# BYTES STATUS
---------- ---------- ----------------
1 314572800 CURRENT
2 314572800 UNUSED
3 314572800 UNUSED
SQL> select group#, member from v$logfile;
GROUP# ---------- MEMBER
--------------------------------------------------------------------------------
3 /data/orcl/redo03.log
2 /data/orcl/redo02.log
1 /data/orcl/redo01.log