背景：在 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 的扩容
