某就业核心数据库standby日志大小调整
原因,由于DBA优化调整了数据库redo日志大小,未对standby redo大小做调整,导致standby redo状态均为 UNASSIGNED ,间接导致主备延时,主库需要做日志切换后数据才能同步至备库。
主备库操作流程
1、主备库执行:
alter system set standby_file_management='manual';
备库取消实时应用:
alter database recover managed standby database cancel;
2、主库
select group#,status,bytes/1024/1024 from v$standby_log;
查询出来日志状态均为 UNASSIGNED 状态可以直接删除,否则需要做日志切换改变状态。
alter database drop logfile group 7;
alter database drop logfile group 8;
alter database drop logfile group 9;
alter database drop logfile group 10;
alter database add standby logfile group 7 '+DATA' size 1G;
alter database add standby logfile group 8 '+DATA' size 1G;
alter database add standby logfile group 9 '+DATA' size 1G;
alter database add standby logfile group 10 '+DATA' size 1G;
3、备库操作
alter database drop logfile group 7;
alter database drop logfile group 8;
alter database drop logfile group 9;
alter database drop logfile group 10;
alter database add standby logfile group 7 '/u01/app/oracle/oradata/jynwdbpdbsby/group_7.sby.redo' size 1G;
alter database add standby logfile group 8 '/u01/app/oracle/oradata/jynwdbpdbsby/group_8.sby.redo' size 1G;
alter database add standby logfile group 9 '/u01/app/oracle/oradata/jynwdbpdbsby/group_9.sby.redo' size 1G;
alter database add standby logfile group 10 '/u01/app/oracle/oradata/jynwdbpdbsby/group_10.sby.redo' size 1G;
4、日志大小修改完成后恢复主备库参数及实时应用日志
在主库和备库设置standby_file_management为auto
alter system set standby_file_management='auto';
备库上启动同步:
alter database recover managed standby database using current logfile disconnect from session;
5、检查状态:
主库多次执行:
alter system switch logfile;
主库:
select max(sequence#) from v$archived_log;
备库:
select max(sequence#) from v$archived_log where applied='YES';
SELECT name,value,time_computed FROM V$DATAGUARD_STATS WHERE NAME like '%lag%';