Oracle更改redo log大小 or 增加redo log组
(1)redo log的大小可以影响 DBWR 和 checkpoint ;
(2)arger redo log files provide better performance. Undersized logfiles increase checkpoint activity and reduce performance.
大的log file可以提供更好的性能,小的logfile 会增加checkpoint 和降低性能;
(3) A rough guide is to switch log files at most once every 20 minutes.(推荐日志切换的时间不要超多20分钟).
查看redolog
SQL> set linesize 200
SQL> set pagesize 200
SQL> select * from v$log ;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------ ------------ ------------------ ----------
1 1 64 209715200 512 1 YES INACTIVE 3658049 2021:10:1322:19:37 3729021 2021:10:1322:37:17 0
2 1 65 209715200 512 1 YES INACTIVE 3729021 2021:10:1322:37:17 3870943 2021:10:2020:53:55 0
3 1 66 209715200 512 1 NO CURRENT 3870943 2021:10:2020:53:55 9.2954E+18 0
status 有几个值分别是:
unused(还没有使用过);
current(正在使用);
active(Log isactive but is not the current log. It is needed for crash recovery)
inactive(Log is nolonger needed for instance recovery)
查看日志文件
SQL> col member for a40
SQL> select * from v$logfile ;
GROUP# STATUS TYPE MEMBER IS_RECOVE CON_ID
---------- --------------------- --------------------- ---------------------------------------- --------- ----------
3 ONLINE /oradata/ORCL/redo03.log NO 0
2 ONLINE /oradata/ORCL/redo02.log NO 0
1 ONLINE /oradata/ORCL/redo01.log NO 0
由于ORACLE并没有提供类似RESIZE的参数来重新调整REDO LOG FILE的大小,因此只能先把这个文件删除了,然后再重建。又由于ORACLE要求最少有两组日志文件在用,所以不能直接删除,必须要创建中间过渡的REDO LOG日志组。
1、创建3个新的日志组
ALTER DATABASE ADD LOGFILE GROUP 4('/oradata/ORCL/redo04a.log','/oradata/ORCL/redo04b.log') SIZE 2048M;
ALTER DATABASE ADD LOGFILE GROUP 5('/oradata/ORCL/redo05a.log','/oradata/ORCL/redo05b.log') SIZE 2048M;
ALTER DATABASE ADD LOGFILE GROUP 6('/oradata/ORCL/redo06a.log','/oradata/ORCL/redo06b.log') SIZE 2048M;
2、切换当前日志到新的日志组
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> select * from v$log ;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------ ------------ ------------------ ----------
1 1 64 209715200 512 1 YES INACTIVE 3658049 2021:10:1322:19:37 3729021 2021:10:1322:37:17 0
2 1 65 209715200 512 1 YES INACTIVE 3729021 2021:10:1322:37:17 3870943 2021:10:2020:53:55 0
3 1 66 209715200 512 1 YES ACTIVE 3870943 2021:10:2020:53:55 3874182 2021:10:2021:19:11 0
4 1 67 2147483648 512 2 YES ACTIVE 3874182 2021:10:2021:19:11 3874188 2021:10:2021:19:13 0
5 1 68 2147483648 512 2 YES ACTIVE 3874188 2021:10:2021:19:13 3874200 2021:10:2021:19:15 0
6 1 69 2147483648 512 2 NO CURRENT 3874200 2021:10:2021:19:15 9.2954E+18 0
3、删除旧的日志组,删除状态为INACTIVE的redo组
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
查看日志组的状态看一下哪个是当前组,哪个是inactive状态的。删除掉inactive的那个组。如果状态为current和active 在删除的时候会报错
4、操作系统下删除原日志组1、2、3中的文件
注意:每一步删除drop操作,都需要手工删除操作系统中的实体文件。
5、重建日志组1、2、3
ALTER DATABASE ADD LOGFILE GROUP 1('/oradata/ORCL/redo01a.log','/oradata/ORCL/redo01b.log') SIZE 2048M;
ALTER DATABASE ADD LOGFILE GROUP 2('/oradata/ORCL/redo02a.log','/oradata/ORCL/redo02b.log') SIZE 2048M;
ALTER DATABASE ADD LOGFILE GROUP 3('/oradata/ORCL/redo03a.log','/oradata/ORCL/redo03b.log') SIZE 2048M;
6.查看redo
SQL> select * from v$log ;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ------------------ ------------ ------------------ ----------
1 1 0 2147483648 512 2 YES UNUSED 0 0 0
2 1 0 2147483648 512 2 YES UNUSED 0 0 0
3 1 0 2147483648 512 2 YES UNUSED 0 0 0
4 1 106 2147483648 512 2 YES ACTIVE 3874983 2021:10:2021:33:51 3875029 2021:10:2021:34:33 0
5 1 107 2147483648 512 2 YES ACTIVE 3875029 2021:10:2021:34:33 3875035 2021:10:2021:34:35 0
6 1 108 2147483648 512 2 NO CURRENT 3875035 2021:10:2021:34:35 9.2954E+18 0
版权声明:本文为博主原创文章,未经博主允许不得转载。
Linux,oracle
发表评论