添加日志组成员oracle,Oracle数据库添加日志组在主备模式下

起初的时候日志组设置的是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;

欢迎留言，欢迎分享，欢迎关注