修改redo log 大小的记录:
Redo log的四种状态(CURRENT、ACTIVE、INACTIVE、UNUSED)
1. CURRENT指当前的日志文件,在进行实例恢复时是必须的;
2. ACTIVE是指活动的非当前日志,在进行实例恢复时会被用到。Active状态意味着,Checkpoint尚未完成,因此该日志文件不能被覆盖。
3. INACTIVE是非活动日志,在实例恢复时不再需要,但在介质恢复时可能需要。
4. UNUSED表示该日志从未被写入,可能是刚添加的,或RESETLOGS后被重置。
在对redo log 操作的时候,一定要让redo 处于 inactive 状态,否则会报错。
单机操作记录:
1、查询redo log 相关情况,进行记录
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 22 52428800 512 1 YES INACTIVE 2228413 11-JUL-17 2229018 18-JUL-17
2 1 23 52428800 512 1 YES INACTIVE 2229018 18-JUL-17 2229075 18-JUL-17
3 1 24 52428800 512 1 NO CURRENT 2229075 18-JUL-17 2.8147E+14
SQL> select group#,sequence#,members,status , (bytes/1024/1024)M from v$log;
GROUP# SEQUENCE# MEMBERS STATUS M
---------- ---------- ---------- ---------------- ----------
1 22 1 INACTIVE 50
2 23 1 INACTIVE 50
3 24 1 CURRENT 50
SQL> col member for a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE /oracle/app/oradata/dg1/redo01.log NO
2 ONLINE /oracle/app/oradata/dg1/redo02.log NO
3 ONLINE /oracle/app/oradata/dg1/redo03.log NO
2、创建新的 online redo 组
ALTER DATABASE ADD LOGFILE GROUP 11('/oracle/app/oradata/dg1/redo11.log') SIZE 70M;
ALTER DATABASE ADD LOGFILE GROUP 12('/oracle/app/oradata/dg1/redo12.log') SIZE 70M;
ALTER DATABASE ADD LOGFILE GROUP 13('/oracle/app/oradata/dg1/redo13.log') SIZE 70M;
3、切换
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
查看,发现已经在用 70M 大小的redo了,且 group 1、2、3 已经为 inactive 状态。
如果不是的话,可以执行 alter system checkpoint ,手动刷新
SQL> select group#,sequence#,members,status , (bytes/1024/1024)M from v$log;
GROUP# SEQUENCE# MEMBERS STATUS M
---------- ---------- ---------- ---------------- ----------
1 28 1 INACTIVE 50
2 29 1 INACTIVE 50
3 30 1 INACTIVE 50
11 31 1 ACTIVE 70
12 32 1 CURRENT 70
13 27 1 INACTIVE 70
SQL> alter system checkpoint;
System altered.
SQL> select group#,sequence#,members,status , (bytes/1024/1024)M from v$log;
GROUP# SEQUENCE# MEMBERS STATUS M
---------- ---------- ---------- ---------------- ----------
1 28 1 INACTIVE 50
2 29 1 INACTIVE 50
3 30 1 INACTIVE 50
11 31 1 INACTIVE 70
12 32 1 CURRENT 70
13 27 1 INACTIVE 70
4、删除50M 的redo 组
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
5、删除 对应的redo log 文件
[root@dg1 ~]# cd /oracle/app/oradata/dg1/
[root@dg1 dg1]# rm -rf redo01.log
[root@dg1 dg1]# rm -rf redo02.log
[root@dg1 dg1]# rm -rf redo03.log
6、切换日志组,查看是否正常
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
7、重建日志组1、2、3
ALTER DATABASE ADD LOGFILE GROUP 1('/oracle/app/oradata/dg1/redo1.log') SIZE 70M;
ALTER DATABASE ADD LOGFILE GROUP 2('/oracle/app/oradata/dg1/redo2.log') SIZE 70M;
ALTER DATABASE ADD LOGFILE GROUP 3('/oracle/app/oradata/dg1/redo3.log') SIZE 70M;
8、切换日志组,查看是否正常,将正在使用的日志组切换到 group 1或2或3 上
SQL> select group#,sequence#,members,status , (bytes/1024/1024)M from v$log;
GROUP# SEQUENCE# MEMBERS STATUS M
---------- ---------- ---------- ---------------- ----------
1 37 1 ACTIVE 70
2 38 1 ACTIVE 70
3 39 1 CURRENT 70
11 34 1 INACTIVE 70
12 35 1 INACTIVE 70
13 36 1 ACTIVE 70
注意,这里 group 13 为 active 的,所以需要执行一次 alter system checkpoint。使他的状态变为 inactive
9、删除group 11,12,13 redo 组
alter database drop logfile group 11;
alter database drop logfile group 12;
alter database drop logfile group 13;
10、删除 对应的redo log 文件
[root@dg1 dg1]# rm -rf redo11.log
[root@dg1 dg1]# rm -rf redo12.log
[root@dg1 dg1]# rm -rf redo13.log
11、保存一下控制文件
alter database backup controlfile to trace as '/oracle/control';
Redo log的四种状态(CURRENT、ACTIVE、INACTIVE、UNUSED)
1. CURRENT指当前的日志文件,在进行实例恢复时是必须的;
2. ACTIVE是指活动的非当前日志,在进行实例恢复时会被用到。Active状态意味着,Checkpoint尚未完成,因此该日志文件不能被覆盖。
3. INACTIVE是非活动日志,在实例恢复时不再需要,但在介质恢复时可能需要。
4. UNUSED表示该日志从未被写入,可能是刚添加的,或RESETLOGS后被重置。
在对redo log 操作的时候,一定要让redo 处于 inactive 状态,否则会报错。
单机操作记录:
1、查询redo log 相关情况,进行记录
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 22 52428800 512 1 YES INACTIVE 2228413 11-JUL-17 2229018 18-JUL-17
2 1 23 52428800 512 1 YES INACTIVE 2229018 18-JUL-17 2229075 18-JUL-17
3 1 24 52428800 512 1 NO CURRENT 2229075 18-JUL-17 2.8147E+14
SQL> select group#,sequence#,members,status , (bytes/1024/1024)M from v$log;
GROUP# SEQUENCE# MEMBERS STATUS M
---------- ---------- ---------- ---------------- ----------
1 22 1 INACTIVE 50
2 23 1 INACTIVE 50
3 24 1 CURRENT 50
SQL> col member for a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE /oracle/app/oradata/dg1/redo01.log NO
2 ONLINE /oracle/app/oradata/dg1/redo02.log NO
3 ONLINE /oracle/app/oradata/dg1/redo03.log NO
2、创建新的 online redo 组
ALTER DATABASE ADD LOGFILE GROUP 11('/oracle/app/oradata/dg1/redo11.log') SIZE 70M;
ALTER DATABASE ADD LOGFILE GROUP 12('/oracle/app/oradata/dg1/redo12.log') SIZE 70M;
ALTER DATABASE ADD LOGFILE GROUP 13('/oracle/app/oradata/dg1/redo13.log') SIZE 70M;
3、切换
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
查看,发现已经在用 70M 大小的redo了,且 group 1、2、3 已经为 inactive 状态。
如果不是的话,可以执行 alter system checkpoint ,手动刷新
SQL> select group#,sequence#,members,status , (bytes/1024/1024)M from v$log;
GROUP# SEQUENCE# MEMBERS STATUS M
---------- ---------- ---------- ---------------- ----------
1 28 1 INACTIVE 50
2 29 1 INACTIVE 50
3 30 1 INACTIVE 50
11 31 1 ACTIVE 70
12 32 1 CURRENT 70
13 27 1 INACTIVE 70
SQL> alter system checkpoint;
System altered.
SQL> select group#,sequence#,members,status , (bytes/1024/1024)M from v$log;
GROUP# SEQUENCE# MEMBERS STATUS M
---------- ---------- ---------- ---------------- ----------
1 28 1 INACTIVE 50
2 29 1 INACTIVE 50
3 30 1 INACTIVE 50
11 31 1 INACTIVE 70
12 32 1 CURRENT 70
13 27 1 INACTIVE 70
4、删除50M 的redo 组
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
5、删除 对应的redo log 文件
[root@dg1 ~]# cd /oracle/app/oradata/dg1/
[root@dg1 dg1]# rm -rf redo01.log
[root@dg1 dg1]# rm -rf redo02.log
[root@dg1 dg1]# rm -rf redo03.log
6、切换日志组,查看是否正常
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
7、重建日志组1、2、3
ALTER DATABASE ADD LOGFILE GROUP 1('/oracle/app/oradata/dg1/redo1.log') SIZE 70M;
ALTER DATABASE ADD LOGFILE GROUP 2('/oracle/app/oradata/dg1/redo2.log') SIZE 70M;
ALTER DATABASE ADD LOGFILE GROUP 3('/oracle/app/oradata/dg1/redo3.log') SIZE 70M;
8、切换日志组,查看是否正常,将正在使用的日志组切换到 group 1或2或3 上
SQL> select group#,sequence#,members,status , (bytes/1024/1024)M from v$log;
GROUP# SEQUENCE# MEMBERS STATUS M
---------- ---------- ---------- ---------------- ----------
1 37 1 ACTIVE 70
2 38 1 ACTIVE 70
3 39 1 CURRENT 70
11 34 1 INACTIVE 70
12 35 1 INACTIVE 70
13 36 1 ACTIVE 70
注意,这里 group 13 为 active 的,所以需要执行一次 alter system checkpoint。使他的状态变为 inactive
9、删除group 11,12,13 redo 组
alter database drop logfile group 11;
alter database drop logfile group 12;
alter database drop logfile group 13;
10、删除 对应的redo log 文件
[root@dg1 dg1]# rm -rf redo11.log
[root@dg1 dg1]# rm -rf redo12.log
[root@dg1 dg1]# rm -rf redo13.log
11、保存一下控制文件
alter database backup controlfile to trace as '/oracle/control';
online redo log 修改完成。rac 环境其实原理是一样的,存储路径修改为 asm 存储即可。
参考文档: https://www.cnblogs.com/xqzt/p/5034826.html