调整Dataguard环境的Online和Standby Redo Log

创建好Dataguard好，可能需要调整online或者standby的redo日志的大小，或者是添加和修改，具体步骤如下：

1、Primary主库上的操作
1.1、检查redo日志文件的信息
col member for a50
col status for a10
col type for a10
select f.group#,l.bytes,l.status,f.type,f.member from v$logfile f,v$log l where f.group#=l.group#
union all
select f.group#,s.bytes,s.status,f.type,f.member from v$logfile f,v$standby_log s where f.group#=s.group#;
    GROUP#      BYTES STATUS     TYPE       MEMBER
---------- ---------- ---------- ---------- --------------------------------------------------
         1  134217728 CURRENT    ONLINE     /soft/oracle/oradata/utf11g/redo01.log
         2  134217728 INACTIVE   ONLINE     /soft/oracle/oradata/utf11g/redo02.log
         3  134217728 INACTIVE   ONLINE     /soft/oracle/oradata/utf11g/redo03.log
         4  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo01.log
         5  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo02.log
         6  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo03.log
         7  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo04.log

7 rows selected.

1.2、修改Standby Redo Log
utf11g@SYS> alter database drop logfile group 4;

Database altered.

utf11g@SYS> alter database drop logfile group 5;

Database altered.

utf11g@SYS> alter database drop logfile group 6;

Database altered.

utf11g@SYS> alter database drop logfile group 7;

Database altered.

再次查看，只剩下online redo log了
select f.group#,l.bytes,l.status,f.type,f.member from v$logfile f,v$log l where f.group#=l.group#
union all
select f.group#,s.bytes,s.status,f.type,f.member from v$logfile f,v$standby_log s where f.group#=s.group#;

    GROUP#      BYTES STATUS     TYPE       MEMBER
---------- ---------- ---------- ---------- --------------------------------------------------
         1  134217728 CURRENT    ONLINE     /soft/oracle/oradata/utf11g/redo01.log
         2  134217728 INACTIVE   ONLINE     /soft/oracle/oradata/utf11g/redo02.log
         3  134217728 INACTIVE   ONLINE     /soft/oracle/oradata/utf11g/redo03.log

1.3、创建新的Standby redo log
alter database add standby logfile group 4 '/soft/oracle/oradata/utf11g/stb.redo01.log' size 128M reuse;
alter database add standby logfile group 5 '/soft/oracle/oradata/utf11g/stb.redo02.log' size 128M reuse;
alter database add standby logfile group 6 '/soft/oracle/oradata/utf11g/stb.redo03.log' size 128M reuse;
alter database add standby logfile group 7 '/soft/oracle/oradata/utf11g/stb.redo04.log' size 128M reuse;
再次检查：
select f.group#,l.bytes,l.status,f.type,f.member from v$logfile f,v$log l where f.group#=l.group#
union all
select f.group#,s.bytes,s.status,f.type,f.member from v$logfile f,v$standby_log s where f.group#=s.group#;
    GROUP#      BYTES STATUS     TYPE       MEMBER
---------- ---------- ---------- ---------- --------------------------------------------------
         1  134217728 UNUSED     ONLINE     /soft/oracle/oradata/utf11g/redo01.log
         2  134217728 INACTIVE   ONLINE     /soft/oracle/oradata/utf11g/redo02.log
         3  134217728 INACTIVE   ONLINE     /soft/oracle/oradata/utf11g/redo03.log
         4  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo01.log
         5  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo02.log
         6  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo03.log
         7  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo04.log

7 rows selected.

1.4、修改Online redo log，先处理状态为inactive的，它表示已经完成归档，可以删除。
utf11g@SYS> alter database drop logfile group 2;

Database altered.

utf11g@SYS> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01567: dropping log 3 would leave less than 2 log files for instance utf11g (thread 1)
ORA-00312: online log 3 thread 1: '/soft/oracle/oradata/utf11g/redo03.log'

要求至少有两组online redo log，所以，只能先重建group 2
alter database add logfile group 2 '/soft/oracle/oradata/utf11g/redo02.log' size 128M reuse;
然后drop group 3 并重建group 3：
alter database drop logfile group 3;
alter database add logfile group 3 '/soft/oracle/oradata/utf11g/redo03.log' size 128M reuse;

现在需要切换一下归档后才能drop group 1：
alter system archive log current;
切换归档后可能一时半会儿redo日志的状态还是active的，所以最快的方法是做个checkpoint
alter system checkpoint;

select f.group#,l.bytes,l.status,f.type,f.member from v$logfile f,v$log l where f.group#=l.group#
union all
select f.group#,s.bytes,s.status,f.type,f.member from v$logfile f,v$standby_log s where f.group#=s.group#;
    GROUP#      BYTES STATUS     TYPE       MEMBER
---------- ---------- ---------- ---------- --------------------------------------------------
         1  134217728 CURRENT    ONLINE     /soft/oracle/oradata/utf11g/redo01.log
         2  134217728 INACTIVE   ONLINE     /soft/oracle/oradata/utf11g/redo02.log
         3  134217728 INACTIVE   ONLINE     /soft/oracle/oradata/utf11g/redo03.log
         4  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo01.log
         5  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo02.log
         6  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo03.log
         7  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo04.log

7 rows selected.


现在可以drop group 1并重建group 1：
alter database drop logfile group 1;
alter database add logfile group 1 '/soft/oracle/oradata/utf11g/redo01.log' size 128M reuse;

至此，Primary数据库上的Online和Standby Redo日志重建完成了。


2、Standby备库上的操作
2.1、同样的应该先查看一下备库上的Redo日志信息
col member for a50
col status for a10
col type for a10
select f.group#,l.bytes,l.status,f.type,f.member from v$logfile f,v$log l where f.group#=l.group#
union all
select f.group#,s.bytes,s.status,f.type,f.member from v$logfile f,v$standby_log s where f.group#=s.group#;

    GROUP#      BYTES STATUS     TYPE       MEMBER
---------- ---------- ---------- ---------- --------------------------------------------------
         1  134217728 CLEARING   ONLINE     /soft/oracle/oradata/utf11g/redo01.log
         2  134217728 CLEARING   ONLINE     /soft/oracle/oradata/utf11g/redo02.log
         3  134217728 CURRENT    ONLINE     /soft/oracle/oradata/utf11g/redo03.log
         4  134217728 ACTIVE     STANDBY    /soft/oracle/oradata/utf11g/stb.redo01.log
         5  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo02.log
         6  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo03.log
         7  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo04.log

7 rows selected.


2.2、处理Standby备库上的Standby Redo日志
处理Standby备库上的Redo日志之前应该先取消redo日志的应用：
alter database recover managed standby database cancel;
否则会出现如下错误：
standby@SYS> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

先处理状态为UNASSINGED的Standby日志，如果Standby Redo Log的状态为ACTIVE，则Drop时会遇到如下错误：
standby@SYS> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00261: log 4 of thread 1 is being archived or modified
ORA-00312: online log 4 thread 1: '/soft/oracle/oradata/utf11g/stb.redo01.log'

此时只需要Clear掉该日志组即可：
standby@SYS> ALTER DATABASE CLEAR LOGFILE GROUP 4;

Database altered.
Clear后日志组的状态会变为UNASSIGNED：
standby@SYS> select f.group#,l.bytes,l.status,f.type,f.member from v$logfile f,v$log l where f.group#=l.group#
  2  union all
  3  select f.group#,s.bytes,s.status,f.type,f.member from v$logfile f,v$standby_log s where f.group#=s.group#;

    GROUP#      BYTES STATUS     TYPE       MEMBER
---------- ---------- ---------- ---------- --------------------------------------------------
         1  134217728 CLEARING   ONLINE     /soft/oracle/oradata/utf11g/redo01.log
         2  134217728 CURRENT    ONLINE     /soft/oracle/oradata/utf11g/redo02.log
         3  134217728 CLEARING   ONLINE     /soft/oracle/oradata/utf11g/redo03.log
         4  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo01.log
         5  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo02.log
         6  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo03.log
         7  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo04.log

7 rows selected.


删除Standby Redo Log：
standby@SYS> alter database drop logfile group 4;

Database altered.

standby@SYS> alter database drop logfile group 5;

Database altered.

standby@SYS> alter database drop logfile group 6;

Database altered.

standby@SYS> alter database drop logfile group 7;

Database altered.

重建Standby Redo Log Group ：
alter database add standby logfile group 4 '/soft/oracle/oradata/utf11g/stb.redo01.log' size 128M reuse;
alter database add standby logfile group 5 '/soft/oracle/oradata/utf11g/stb.redo02.log' size 128M reuse;
alter database add standby logfile group 6 '/soft/oracle/oradata/utf11g/stb.redo03.log' size 128M reuse;
alter database add standby logfile group 7 '/soft/oracle/oradata/utf11g/stb.redo04.log' size 128M reuse;
查看一下状态：
select f.group#,l.bytes,l.status,f.type,f.member from v$logfile f,v$log l where f.group#=l.group#
union all
select f.group#,s.bytes,s.status,f.type,f.member from v$logfile f,v$standby_log s where f.group#=s.group#;

    GROUP#      BYTES STATUS     TYPE       MEMBER
---------- ---------- ---------- ---------- --------------------------------------------------
         1  134217728 CURRENT    ONLINE     /soft/oracle/oradata/utf11g/redo01.log
         2  134217728 CLEARING   ONLINE     /soft/oracle/oradata/utf11g/redo02.log
         3  134217728 CLEARING   ONLINE     /soft/oracle/oradata/utf11g/redo03.log
         4  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo01.log
         5  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo02.log
         6  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo03.log
         7  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo04.log

2.3、处理Standby备库上的Online Redo Log：
先将standby_file_management设为手动：
alter system set standby_file_management=manual;
不然会得到如下错误：
standby@SYS> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01275: Operation DROP LOGFILE is not allowed if standby file management is automatic.

状态为CLEARING或者是CLEARING_CURRENT的日志组都需要手工执行一次CLEAR。
直接DROP状态为CLEARING或者CLEARING_CURRENT的日志组会得到如下错误：
standby@SYS> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance utf11g (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/soft/oracle/oradata/utf11g/redo02.log'

先执行CLEAR：
alter database clear logfile group 2;
alter database clear logfile group 3;
查看一下状态：
select f.group#,l.bytes,l.status,f.type,f.member from v$logfile f,v$log l where f.group#=l.group#
union all
select f.group#,s.bytes,s.status,f.type,f.member from v$logfile f,v$standby_log s where f.group#=s.group#;

    GROUP#      BYTES STATUS     TYPE       MEMBER
---------- ---------- ---------- ---------- --------------------------------------------------
         1  134217728 CURRENT    ONLINE     /soft/oracle/oradata/utf11g/redo01.log
         2  134217728 UNUSED     ONLINE     /soft/oracle/oradata/utf11g/redo02.log
         3  134217728 UNUSED     ONLINE     /soft/oracle/oradata/utf11g/redo03.log
         4  134217728 ACTIVE     STANDBY    /soft/oracle/oradata/utf11g/stb.redo01.log
         5  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo02.log
         6  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo03.log
         7  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo04.log

7 rows selected.

现在可以DROP状态为UNUSED的日志组了，同样的至少需要两个日志组，所以应该是DROP后立即CREATE新的：
alter database drop logfile group 2;
alter database add logfile group 2 '/soft/oracle/oradata/utf11g/redo02.log' size 128M reuse;
alter database drop logfile group 3;
alter database add logfile group 3 '/soft/oracle/oradata/utf11g/redo03.log' size 128M reuse;

到此就剩group 1不是clear状态了，那怎么让它变为clear状态呢？
其实简单，就是现在Standby库上启用日志应用
alter database recover managed standby database using current logfile disconnect from session;
然后在Primary上切换日志，多切换几次：
alter system archive log current;
同时在Standby上检查，直到其状态为CLEAR即可：
select f.group#,l.bytes,l.status,f.type,f.member from v$logfile f,v$log l where f.group#=l.group#
union all
select f.group#,s.bytes,s.status,f.type,f.member from v$logfile f,v$standby_log s where f.group#=s.group#;

    GROUP#      BYTES STATUS     TYPE       MEMBER
---------- ---------- ---------- ---------- --------------------------------------------------
         1  134217728 CLEARING   ONLINE     /soft/oracle/oradata/utf11g/redo01.log
         2  134217728 CURRENT    ONLINE     /soft/oracle/oradata/utf11g/redo02.log
         3  134217728 UNUSED     ONLINE     /soft/oracle/oradata/utf11g/redo03.log
         4  134217728 ACTIVE     STANDBY    /soft/oracle/oradata/utf11g/stb.redo01.log
         5  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo02.log
         6  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo03.log
         7  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo04.log

7 rows selected.

最后再次取消日志应用：
alter database recover managed standby database cancel;

现在就可以CLEAR了：
select f.group#,l.bytes,l.status,f.type,f.member from v$logfile f,v$log l where f.group#=l.group#
union all
select f.group#,s.bytes,s.status,f.type,f.member from v$logfile f,v$standby_log s where f.group#=s.group#;

    GROUP#      BYTES STATUS     TYPE       MEMBER
---------- ---------- ---------- ---------- --------------------------------------------------
         1  134217728 UNUSED     ONLINE     /soft/oracle/oradata/utf11g/redo01.log
         2  134217728 CLEARING   ONLINE     /soft/oracle/oradata/utf11g/redo02.log
         3  134217728 CURRENT    ONLINE     /soft/oracle/oradata/utf11g/redo03.log
         4  134217728 ACTIVE     STANDBY    /soft/oracle/oradata/utf11g/stb.redo01.log
         5  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo02.log
         6  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo03.log
         7  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo04.log

7 rows selected.

现在终于可以重建group 1了：
alter database drop logfile group 1;
alter database add logfile group 1 '/soft/oracle/oradata/utf11g/redo01.log' size 128M reuse;
检查调整的最终状态：
select f.group#,l.bytes,l.status,f.type,f.member from v$logfile f,v$log l where f.group#=l.group#
union all
select f.group#,s.bytes,s.status,f.type,f.member from v$logfile f,v$standby_log s where f.group#=s.group#;

    GROUP#      BYTES STATUS     TYPE       MEMBER
---------- ---------- ---------- ---------- --------------------------------------------------
         1  134217728 UNUSED     ONLINE     /soft/oracle/oradata/utf11g/redo01.log
         2  134217728 CLEARING   ONLINE     /soft/oracle/oradata/utf11g/redo02.log
         3  134217728 CURRENT    ONLINE     /soft/oracle/oradata/utf11g/redo03.log
         4  134217728 ACTIVE     STANDBY    /soft/oracle/oradata/utf11g/stb.redo01.log
         5  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo02.log
         6  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo03.log
         7  134217728 UNASSIGNED STANDBY    /soft/oracle/oradata/utf11g/stb.redo04.log

7 rows selected.


3、主库和备库的Online和Standby Redo Log Group都已经重建调整完了，别忘了启用Standby库上的日志应用：
alter database recover managed standby database using current logfile disconnect from session;
恢复Standby库上的STANDBY_FILE_MANAGEMENT参数为AUTO自动管理：
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';

然后在Primary库上多切换几次日志，并检查日志的同步应用状态：
primary@SYS> alter system archive log current;

System altered.

primary@SYS> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
           958

primary@SYS>
standby@SYS> select sequence#,applied from v$archived_log;

 SEQUENCE# APPLIED
---------- ---------------------------
       946 YES
       947 YES
       948 YES
       949 YES
       950 YES
       951 NO
       952 NO
       953 NO
       954 NO
       951 YES
       952 YES
       953 YES
       954 YES
       955 YES
       956 YES
       957 YES
       958 IN-MEMORY

17 rows selected.

同步正常，一切都OK。