整理一份DG环境下增删主备库REDO的最佳实践步骤
1. 主库,备库都增加redo步骤
说明:
主库可正常添加 ORLS（REDO LOG)或SRLS（STANDBY REDO LOG）
主库的STANDBY_FILE_MANAGEMENT无论是否auto 或Manuals都不会传播到备库, 但是为了避免主备切换后，redo不足问题，所以建议备库也相应的添加redo .
1.1. 主库填加redo:
ALTER DATABASE ADD LOGFILE GROUP ...
1.2. 主库填加standby redo
ALTER DATABASE ADD STANDBY LOGFILE GROUP ...
1.3. 备库停复制
RECOVER MANAGED STANDBY DATABASE CANCEL;
1.4. 备库改手动
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';
1.5. 备库填加redo
ALTER DATABASE ADD LOGFILE GROUP ...
1.6. 备库填加standby redo
ALTER DATABASE ADD STANDBY LOGFILE GROUP ...
1.7. 备库改自动
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
1.8. 备库恢复复制
FOR ADG:
RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
FOR DG:
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
2. 只是备库添加REDO步骤(主库不添加)
2.1. 备库停复制:
RECOVER MANAGED STANDBY DATABASE CANCEL;
2.2. 备库改手动
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';
2.3. 备库填加redo
ALTER DATABASE ADD LOGFILE GROUP ...
2.4. 备库填加standby redo
ALTER DATABASE ADD STANDBY LOGFILE GROUP ...
RAC环境示例:
ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 27 '+DG_DATA' SIZE 1024M,
GROUP 28 '+DG_DATA' SIZE 1024M,
GROUP 29 '+DG_DATA' SIZE 1024M;
ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 33 '+DG_DATA' SIZE 1024M,
GROUP 34 '+DG_DATA' SIZE 1024M,
GROUP 35 '+DG_DATA' SIZE 1024M,
GROUP 36 '+DG_DATA' SIZE 1024M;
2.5. 备库改自动
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
2.6. 恢复复制
FOR ADG:
RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
FOR DG:
RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
3. 删除redo(视需要)
注：
- 无论主备，current ,CLEARING_CURRENT不能删
- 备库要cancel 后才能删；
- 删除REDO和STANDBY_FILE_MANAGEMENT参数无关;
3.1. 备库停复制
RECOVER MANAGED STANDBY DATABASE CANCEL;
3.2. 主库删除redo
alter system switch logfile;
selecct * from v$Log;
alter database clear logfile group 1;
alter database drop logfile group 1;
3.3. 主库删除stadnby redo
selecct * from v$Log;
alter database clear logfile group 21;
alter database drop standby logfile group 21;
3.3. 备库删除redo
select * from v$Log;
--注:
只有为CLEARING, UNUSED, INACTIVE 的才可继续执行如下删除.
如果是其他状态如current 状态，可在主库先进行switch logfile操作,再查看状态 是否改变;
alter database clear logfile group 1;
alter database drop logfile group 1;
3.4. 备库删除stanby redo
alter database clear logfile group 21;
alter database drop standby logfile group 21;
3.5. 备库恢复复制
....
FYI:
MOS Note 740675.1 provides information on how to add ORLs.
