起初的时候日志组设置的是3组，日志大小为50MB，11G的时候每次25MB就切换一下。如今业务量大了，业务功能也多了，动不动就有好多UPDATE，触发很多日志。为此日志切换就非常频繁，1个小时能达到110.关键的是每3组轮完前得要做CHECKPOINT DBWR写操作。为此经常发生等待！
先添加两组日志进去，延缓一轮的时间。
目前主备都差不多这样，用满了。
alter database add logfile group 4 '/data/oradata/orcl/redo04.log' size 1024M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/database/globaldb/olinelog/std_redo05.log') size 1024m;
发现添加在线日志和在线STANDBY日志语法差不多，只是多了一些而已。
我想它们是不是分别两种类型的日志呢？所以GRUP发生重叠也没有关系吧？
结果很悲剧
SQL> alter database add logfile group 4 '/data/oradata/orcl/redo04.log' size 1024M;
alter database add logfile group 4 '/data/oradata/orcl/redo04.log' size 1024M
*
ERROR at line 1:
ORA-01184: 日志文件组 4 已存在
只好 在STANDBY GROUP 后面继续追加组号了
alter database add logfile group 8 '/data/oradata/orcl/redo08.log' size 1024M;
alter database add logfile group 9 '/data/oradata/orcl/redo09.log' size 1024M;
追加完了后，发现日志大小不一样，原先是50M 连STANDBY LOG 也是 所以接下来就是同步两个备库，一个本地，一个异地。
先把主库的其他在线日志扩大容量
使用下面三个查询语句观看：
select group#,thread#,sequence#,archived,status,members,bytes/1024/1024 as size_mb from v$log;
select group#,status,type,member from v$logfile;
select group#,thread#,sequence#,archived,status,bytes/1024/1024 from V$standby_Log;
先用这条语句查看在线日志的状态
SQL> select group#,thread#,sequence#,archived,status,members,bytes/1024/1024 as size_mb from v$log;
删除日志组 必须状态为INACTIVE，日志组1和2 都处于可删除状态下
alter database drop logfile group 1;
rm u01/app/oracle/oradata/orcl/redo01.log
alter database add logfile group 1 '/u01/app/oracle/oradata/orcl/redo01.log' size 1024M;
alter database drop logfile group 2;
/u01/app/oracle/oradata/orcl/redo02.log
alter database add logfile group 2 '/u01/app/oracle/oradata/orcl/redo02.log' size 1024M;
上面操作步骤顺序不能乱，日志组删除只是在控制文件删除，还有人工在操作系统上删除，才可以添加
alter system switch logfile;
把第3个日志组切换掉
SQL> select group#,thread#,sequence#,archived,status,members,bytes/1024/1024 as size_mb from v$log;
GROUP# THREAD# SEQUENCE# ARC STATUS MEMBERS SIZE_MB
---------- ---------- ---------- --- ---------------- ---------- ----------
1 1 24598 NO CURRENT 1 1024
2 1 0 YES UNUSED 1 1024
3 1 24597 YES ACTIVE 1 50
8 1 0 YES UNUSED 1 1024
9 1 0 YES UNUSED 1 1024
Elapsed: 00:00:00.00
现在第三日志组还是活跃中，未归档waite.....
GROUP# THREAD# SEQUENCE# ARC STATUS MEMBERS SIZE_MB
---------- ---------- ---------- --- ---------------- ---------- ----------
1 1 24598 NO CURRENT 1 1024
2 1 0 YES UNUSED 1 1024
3 1 24597 YES INACTIVE 1 50
8 1 0 YES UNUSED 1 1024
9 1 0 YES UNUSED 1 1024
alter database drop logfile group 3;
rm u01/app/oracle/oradata/orcl/redo03.log
alter database add logfile group 3 '/u01/app/oracle/oradata/orcl/redo03.log' size 1024M;
GROUP# THREAD# SEQUENCE# ARC STATUS MEMBERS SIZE_MB
---------- ---------- ---------- --- ---------------- ---------- ----------
1 1 24598 NO CURRENT 1 1024
2 1 0 YES UNUSED 1 1024
3 1 0 YES UNUSED 1 1024
8 1 0 YES UNUSED 1 1024
9 1 0 YES UNUSED 1 1024
处理主库的STANDBY LOG FILE
ALTER DATABASE DROP LOGFILE GROUP 4;
ALTER DATABASE DROP LOGFILE GROUP 5;
ALTER DATABASE DROP LOGFILE GROUP 6;
ALTER DATABASE DROP LOGFILE GROUP 7;
rm standby*.log
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/orcl/standby04.log') size 1024m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/orcl/standby05.log') size 1024m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/orcl/standby06.log') size 1024m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/orcl/standby07.log') size 1024m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('/u01/app/oracle/oradata/orcl/standby10.log') size 1024m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('/u01/app/oracle/oradata/orcl/standby11.log') size 1024m;
处理本地备库
先添加两组日志
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('/u01/ora_redo/orcl/standby10.log') size 1024m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('/u01/ora_redo/orcl/standby10.log') size 1024m
*
第 1 行出现错误:
ORA-01156: 进行中的恢复或闪回可能需要访问文件
已用时间: 00: 00: 00.01
shutdown immediate;
startup mount;
ALTER DATABASE DROP LOGFILE GROUP 4;
ALTER DATABASE DROP LOGFILE GROUP 5;
ALTER DATABASE DROP LOGFILE GROUP 6;
ALTER DATABASE DROP LOGFILE GROUP 7;
rm standby*.log
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/ora_redo/orcl/standby04.log') size 1024m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/ora_redo/orcl/standby05.log') size 1024m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/ora_redo/orcl/standby06.log') size 1024m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/ora_redo/orcl/standby07.log') size 1024m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('/u01/ora_redo/orcl/standby10.log') size 1024m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('/u01/ora_redo/orcl/standby11.log') size 1024m;
SQL> select group#,thread#,sequence#,archived,status,members,bytes/1024/1024 as size_mb from v$log;
GROUP# THREAD# SEQUENCE# ARC STATUS MEMBERS SIZE_MB
---------- ---------- ---------- --- ---------------- ---------- ----------
1 1 24598 YES CLEARING_CURRENT 1 50
2 1 24597 YES CLEARING 1 50
3 1 24598 YES CLEARING 1 50
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 1
*
第 1 行出现错误:
ORA-01275: 自动进行备用文件管理时, 不允许进行 DROP LOGFILE 操作。
郁闷备库的在线日志是不能删除的
处理异地备库
发现不需要关闭实列，只要把备库取消恢复模式，处于只读模式也可以，就能添加删除STANDBY LOG了
SELECT NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,DB_UNIQUE_NAME FROM V$DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10 ('/u01/app/oracle/oradata/orcl/standby10.log') size 1024m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('/u01/app/oracle/oradata/orcl/standby11.log') size 1024m;
alter database recover managed standby database using current logfile disconnect;
欢迎留言，欢迎分享，欢迎关注
