- 需求：日志切换太频繁，导致出现log file switch (checkpoint incomplete)
- 参考: Handling ORL and SRL (Resize) on Primary and Physical Standby in Data Guard Environment (Doc ID 1532566.1)
http://blog.itpub.net/26194851/viewspace-2083912/ - log file switch (checkpoint incomplete)等待时间描述:
Waiting for a log switch because the session cannot wrap into the next log. Wrapping cannot be performed because the checkpoint for that log has not completed.
当你的日志组都写完以后，LGWR 试图写第一个log file，如果这时数据库没有完成写出记录在第一个log file 中的dirty 块时(例如第一个检查点未完成)，该等待事件出现。此时online log状态全部是active状态
该等待事件通常表示你的DBWR 写出速度太慢或者IO 存在问题或者正在执行批量插入作业 解决办法:
调整参数db_writer_processes增加额外的DBWR 增加你的日志组或日志文件大小
===========================================================================================================
一、备库上停止apply日志恢复：
alter database recover managed standby database cancel;
备库设置standby_file_management为manual：
alter system set standby_file_management='manual';
二、主库：
1,主库增加redo，standbylog,每组大小200m
--redo
alter database add logfile group 20 ('/u01/app/oracle/oradata/testdb/redo20.log') size 200m;
alter database add logfile group 21 ('/u01/app/oracle/oradata/testdb/redo21.log') size 200m;
alter database add logfile group 22 ('/u01/app/oracle/oradata/testdb/redo22.log') size 200m;
--standbylog
alter database add standby logfile group 23 ('/u01/app/oracle/oradata/testdb/standby23.log') size 200m;
alter database add standby logfile group 24 ('/u01/app/oracle/oradata/testdb/standby24.log') size 200m;
alter database add standby logfile group 25 ('/u01/app/oracle/oradata/testdb/standby25.log') size 200m;
alter database add standby logfile group 26 ('/u01/app/oracle/oradata/testdb/standby26.log') size 200m;
2,主库删除原来的redo,和standbylog
a).检查当前主备的redo日志文件和standby日志文件：
redo log:
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#;
standby log:
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#;
b).drop redo:
alter database drop logfile group x;
如果状态不为inactive：
alter system switch logfile;
alter system checkpoint;
alter database drop logfile group x;
到os删除对应的日志文件
c).drop standbylog:
alter database drop logfile group x;
到os删除对应的日志文件
三、备库：
1,备库增加redo，standbylog,每组大小200m
--redo
alter database add logfile group 20 ('/u01/app/oracle/oradata/testdb_dg/redo20.log') size 200m;
alter database add logfile group 21 ('/u01/app/oracle/oradata/testdb_dg/redo21.log') size 200m;
alter database add logfile group 22 ('/u01/app/oracle/oradata/testdb_dg/redo22.log') size 200m;
--standbylog
alter database add standby logfile group 23 ('/u01/app/oracle/oradata/testdb_dg/standby23.log') size 200m;
alter database add standby logfile group 24 ('/u01/app/oracle/oradata/testdb_dg/standby24.log') size 200m;
alter database add standby logfile group 25 ('/u01/app/oracle/oradata/testdb_dg/standby25.log') size 200m;
alter database add standby logfile group 26 ('/u01/app/oracle/oradata/testdb_dg/standby26.log') size 200m;
2,删除原来的redo,和standbylog
a).检查当前主备的redo日志文件和standby日志文件：
redo log:
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#;
standby log:
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#;
b).drop redo:
alter database drop logfile group x;
针对current的日志，需要先在主库上切换日志，变为clearing再处理：
alter system switch logfile;
alter system checkpoint;
对于clearing的日志，执行clear后再drop:
alter database clear logfile group 3;
alter database drop logfile group 3;
到os删除对应的日志文件
c).drop 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;
到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%';
五 调整日志出现的问题 ：standby 的redo log无法删除
SQL> alter database drop logfile group 4
*
ERROR at line 1:
ORA-01623: log 4 is current log for instance PROD (thread 1) - cannot drop
ORA-00312: online log 4 thread 1: '/redoa/oracle/oradata/PROD/redo04a.log'
解决方法：重新从库控制文件
参考：
Online Redo Logs on Physical Standby (Doc ID 740675.1)
Steps to recreate a Physical Standby Controlfile (Doc ID 459411.1)
1、停同步（备库）
SQL> alter database recover managed standby database cancel;
2、停库（备库）
SQL> shutdown immediate;
3、从主库生成控制文件，copy到从库（主库）
SQL> connect sys/@ as sysdba
SQL> alter database create standby controlfile as '/home/controlfil0412.ctl';
scp controlfil0412.ctl to 备库的主机上
4、备份从库原控制文件，用新的控制文件覆盖旧的控制文件
mv control01.ctl control01.ctl.bak
mv control02.ctl control01.ct2.bak
--用新的控制文件覆盖旧的
cp controlfil0412.ctl controlfil01.ctl
cp controlfil0412.ctl controlfil02.ctl
5、把原来的redo重命名
mv redo01.log redo01.log.bak
mv redo02.log redo02.log.bak
mv redo03.log redo03.log.bak
6、启动备库到mount状态（当启动时，新的控制文件会生成和主库一样的redo和standby log文件）
SQL> startup mount
7、清理redo
Clear all Redolog Groups on Standby (only when using Oracle Database <10.2.0.1).
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
8、启动同步
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
9、停同步，open库
alter database recover managed standby database cancel;
alter database open;
10、开启real实时同步
alter database recover managed standby database using current logfile disconnect from session;
11、验证日志问题和同步是否正常
