背景：在 Oracle 11g DG 环境下，需要在线调整主库 online redo log 的大小

停止备库 apply 日志恢复

# 在备库上执行以下操作 
alter database recover managed standby database cancel;
alter system set standby_file_management='manual';

主库操作

增加 online redo

alter database add logfile group 4 ('/u01/app/oracle/oradata/testdb/redo4.log') size 500m;
alter database add logfile group 5 ('/u01/app/oracle/oradata/testdb/redo5.log') size 500m;
alter database add logfile group 6 ('/u01/app/oracle/oradata/testdb/redo6.log') size 500m;

增加 standby log

alter database add standby logfile group 14 ('/u01/app/oracle/oradata/testdb/standby14.log') size 500m;
alter database add standby logfile group 15 ('/u01/app/oracle/oradata/testdb/standby15.log') size 500m;
alter database add standby logfile group 16 ('/u01/app/oracle/oradata/testdb/standby16.log') size 500m;alter database add standby logfile group 17 ('/u01/app/oracle/oradata/testdb/standby16.log') size 500m;

删除 online redo

1.检查当前主备的 redo 日志文件
select l.group#,l.status,l.bytes/1024/1024 mb,lf.member from v$log l,v$logfile lf where l.group#=lf.group# order by group#;
            
2.删除原来 redo
alter database drop logfile group xxxxx;
                 
如果状态不为inactive：
    alter system switch logfile;
    alter system checkpoint;
    alter database drop logfile group x;
    到os删除对应的日志文件

删除 standby log

1.检查当前主备的 standby 日志文件
select sl.group#,sl.status,sl.bytes/1024/1024 mb,lf.member from v$standby_log sl,v$logfile lf where sl.group#=lf.group# order by group#;   

2.删除原来 standbylog
alter database drop logfile group xxxxx;
到os删除对应的日志文件

备库操作

增加 redo

alter database add logfile group 4 ('/u01/app/oracle/oradata/testdb_dg/redo4.log') size 500m;
alter database add logfile group 5 ('/u01/app/oracle/oradata/testdb_dg/redo5.log') size 500m;
alter database add logfile group 6 ('/u01/app/oracle/oradata/testdb_dg/redo6.log') size 500m;

增加 standby log

alter database add standby logfile group 14 ('/u01/app/oracle/oradata/testdb_dg/standby14.log') size 500m;
alter database add standby logfile group 15 ('/u01/app/oracle/oradata/testdb_dg/standby15.log') size 500m;
alter database add standby logfile group 16 ('/u01/app/oracle/oradata/testdb_dg/standby16.log') size 500m;

删除 redo log

1.检查当前主备的 redo 日志文件
select l.group#,l.status,l.bytes/1024/1024 mb,lf.member from v$log l,v$logfile lf where l.group#=lf.group# order by group#;

2.删除原redo log
alter database drop logfile group xxxxx;

    针对current的日志，需要先在主库上切换日志，变为clearing再处理：
    alter system switch logfile;
    alter system checkpoint;

    对于clearing的日志，执行clear后再drop:
    alter database clear logfile group 3;
    alter database drop logfile group 3;

3.到os删除对应的日志文件

删除 standby log

1.检查当前主备的 standby 日志文件：
select sl.group#,sl.status,sl.bytes/1024/1024 mb,lf.member from v$standby_log sl,v$logfile lf where sl.group#=lf.group# order by group#;

2.删除原 standbylog:
alter database drop logfile group x;
	针对active的日志，需要先在主库上切换日志（alter system switch logfile;）再处理
    SQL> select group#,status from v$standby_log;
    GROUP# STATUS
    ---------- ----------
    10 UNASSIGNED
    11 ACTIVE
    12 UNASSIGNED
    13 UNASSIGNED
    alter database drop logfile group 10;
                        
3.到os删除对应的日志文件

启动 apply 日志应用

1.备库设置standby_file_management为auto
  alter system set standby_file_management='auto';
    
2.备库上启动同步
alter database recover managed standby database using current logfile disconnect;
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ ONLY WITH APPLY

3.检查状态：
主库多次执行：
alter system switch logfile;

主库：
select max(sequence#) from v$archived_log;

备库：
select process, status, thread#, sequence# from v$managed_standby where process='MRP0';
select max(sequence#) from v$archived_log where applied='YES';
SELECT name,value,time_computed FROM V$DATAGUARD_STATS WHERE NAME like '%lag%';
                 

至此，完成整个 online redo log 的扩容