oracle删除表redo,Oracle DataGuard环境增删REDO(最佳实践)

整理一份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.