最近发现数据的库存在日志频繁切换,查看了才发现redo log日志太小,进行在线调整。
1.查看当前日志组成员
SQL> select member from v$logfile;
MEMBER
------------------------------------------------------
/u01/oracle/oradata/orcl/redo03.log
/u01/oracle/oradata/orcl/redo02.log
/u01/oracle/oradata/orcl/redo01.log
2.查看当前日志组状态
SQL> select group#,members,bytes/1024/1024,status from v$log;
GROUP# MEMBERS BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
1 1 50 CURRENT
2 1 50 INACTIVE
3 1 50 INACTIVE
a. CURRENT指当前的日志文件,在进行实例恢复时是必须的;
b. ACTIVE是指活动的非当前日志,在进行实例恢复时会被用到。Active状态意味着,Checkpoint尚未完成,因此该日志文件不能被覆盖。
c. INACTIVE是非活动日志,在实例恢复时不再需要,但在介质恢复时可能需要。
d. UNUSED表示该日志从未被写入,可能是刚添加的,或RESETLOGS后被重置。
从如上信息可以看出,目前数据库有三个成员,1为CURRENT,2,3为INACTIVE,大小为50m,现在修改成100m
3.删除旧日志组,增加新的日志组
SQL>alter database drop logfile group 3;
--删除物理磁盘中的redo03.log
[oracle@centos ~]# mv /u01/oracle/oradata/orcl/redo03.log /tmp
SQL>alter database add logfile group 3 ('/u01/oracle/oradata/orcl/redo03.log') size 100M;
查看当前日志组状态
SQL> select group#,members,bytes/1024/1024,status from v$log;
GROUP# MEMBERS BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
1 1 50 CURRENT
2 1 50 INACTIVE
3 1 100 UNUSED
同样可以继续删除日志组2
SQL>alter database drop logfile group 2;
--删除物理磁盘中的redo02.log
[oracle@centos ~]# mv /u01/oracle/oradata/orcl/redo02.log /tmp
SQL>alter database add logfile group 2 ('/u01/oracle/oradata/orcl/redo02.log') size 100M;
查看当前日志组状态
SQL> select group#,members,bytes/1024/1024,status from v$log;
GROUP# MEMBERS BYTES/1024/1024 STATUS
---------- ---------- --------------- ----------------
1 1 50 CURRENT
2 1 100 UNUSED
3 1 100 UNUSED
4.此时日志组1仍为CURRENT状态,使用如下命令来使日志组1也变成INACTIVE状态
SQL> alter system switch logfile;
查看当前日志组状态
SQL> select group#,bytes/1024/1024,members,status from v$log;
GROUP# BYTES/1024/1024 MEMBERS STATUS
---------- --------------- ---------- ----------------
1 50 1 INACTIVE
2 100 1 ACTIVE
3 100 1 CURRENT
同样可以继续删除日志组1
SQL>alter database drop logfile group 1;
--删除物理磁盘中的redo01.log
[oracle@centos ~]# mv /u01/oracle/oradata/orcl/redo01.log /tmp
SQL>alter database add logfile group 1 ('/u01/oracle/oradata/orcl/redo01.log') size 100M;
查看当前日志组状态
SQL> select group#,bytes/1024/1024,members,status from v$log;
GROUP# BYTES/1024/1024 MEMBERS STATUS
---------- --------------- ---------- ----------------
1 100 1 UNUSED
2 100 1 ACTIVE
3 100 1 CURRENT
至此3个日志文件全部修改成大小100m。