在DG环境中修改redo及standby redo 文件大小

主备库有3组redo，4组standby redo，每组2成员，大小100M，想改为500M。参考Doc ID：1532566.1 


检查主库
select status,instance_name,database_role from v$database,v$instance;


STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         orcl         PRIMARY


select group#,sum(bytes/1024/1024)"Size in MB" from v$log group by group#;
select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;


检查备库
 select status,instance_name,database_role from v$database,v$Instance;


STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         orcl_dg        PHYSICAL STANDBY


 select group#, sum(bytes/1024/1024)"Size in MB" from v$log group by group#;
 select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;


检查备库参数
show parameter standby_file_management
如果不是 MANUAL，则需要设置
alter system set standby_file_management=manual;
确认
show parameter standby_file_management
因该是 MANUAL




主库


看当前状态
col status for a18
select group#,status,round(bytes/1024/1024)m from v$log;  


set lin 120 pages 100
col member for a50
select group#,member from v$logfile order by 1;


alter database drop logfile group 3;
ALTER DATABASE ADD LOGFILE GROUP 3 ('/oradata/orcl/redo03a.log','/oradata/orcl/redo03b.log') SIZE 500m reuse;
alter system switch logfile;
alter system checkpoint;
切换、添加、删除其他组


处理standby log
select group#,status,round(bytes/1024/1024)m from v$standby_log;


alter database drop standby logfile group 11;
alter database drop standby logfile group 12;
alter database drop standby logfile group 13;
alter database drop standby logfile group 14;
alter database add standby logfile group 11 ('/oradata/orcl/redo11a.log','/oradata/orcl/redo11b.log')  size 500m reuse;
alter database add standby logfile group 12 ('/oradata/orcl/redo12a.log','/oradata/orcl/redo12b.log')  size 500m reuse;
alter database add standby logfile group 13 ('/oradata/orcl/redo13a.log','/oradata/orcl/redo13b.log')  size 500m reuse;
alter database add standby logfile group 14 ('/oradata/orcl/redo14a.log','/oradata/orcl/redo14b.log')  size 500m reuse;


---------------------------------------------------------------
备库
col status for a18
select group#,status,round(bytes/1024/1024)m from v$log;


set lin 120 pages 100
col member for a50
select group#,member from v$logfile;


停掉应用进程
alter database recover managed standby database cancel;


先处理非current的日志组


alter database clear logfile group 1;
alter database drop logfile group 1;
alter database add logfile group 1 ('/oradata/orcl_dg/redo01a.log','/oradata/orcl_dg/redo01b.log') size 500m reuse;


主库切换一次 alter system switch logfile;  再清理current的（主库的dest_state_2要处于enable状态）这里你可能觉得很神奇，停止mrp进程后，主库切换logfile，备库也切换？


处理standby log
col group# for 9999
select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;
select group#,status from v$standby_log;


UNASSIGNED状态的可以直接删除


alter database drop standby logfile group 11;
alter database drop standby logfile group 12;
alter database drop standby logfile group 13;
alter database drop standby logfile group 14;
alter database add standby logfile group 11 ('/oradata/orcl_dg/redo11a.log','/oradata/orcl_dg/redo11b.log')  size 500m reuse;
alter database add standby logfile group 12 ('/oradata/orcl_dg/redo12a.log','/oradata/orcl_dg/redo12b.log')  size 500m reuse;
alter database add standby logfile group 13 ('/oradata/orcl_dg/redo13a.log','/oradata/orcl_dg/redo13b.log')  size 500m reuse;
alter database add standby logfile group 14 ('/oradata/orcl_dg/redo14a.log','/oradata/orcl_dg/redo14b.log')  size 500m reuse;


全部调整完大小后，修改备库的参数
 alter system set standby_file_management=auto;
 
启动应用进程
alter database recover managed standby database disconnect from session using current logfile;


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


检查备库
select max(sequence#) from v$archived_log where applied='YES';
应该与主库不超过2个。


在主备库上确认redo个数、大小


set lin 120 pages 100
col member for a60
col status for a12
select group#,status,SEQUENCE#,round(bytes/1024/1024) Size_MB from v$log;
select group#,status,SEQUENCE#,round(bytes/1024/1024) Size_MB from v$standby_log;
select group#,status,type,member from v$logfile order by 1;




大功告成。