Oracle 11g DataGuard 主备库调整redo log 和 standby redo log 数量和大小

注意事项：

standby log group 要比 logfile group 至少多一组。

standby logfile 和 logfile大小尽量一致。

standby logfile 和 logfile 操作逻辑基本上一样，都是要等到状态为inactive或者unused才能drop掉，所以需要结合

alter system checkpoint;alter system switch logfile;alter database clear logfile group x;等语句使用。

以下测试脚本基于主备库1,2,3组为logfile，4,5,6,7组为standby logfile，大小均为10m，目标为全部调整到12m

0.备库取消同步

alter database recover managed standby database cancel;

1.备库standby添加

alter database add standby logfile group 14 '/u01/app/oracle/oradata/db/redo14.log' size 12m reuse;
alter database add standby logfile group 15 '/u01/app/oracle/oradata/db/redo15.log' size 12m reuse;
alter database add standby logfile group 16 '/u01/app/oracle/oradata/db/redo16.log' size 12m reuse;
alter database add standby logfile group 17 '/u01/app/oracle/oradata/db/redo17.log' size 12m reuse;

2.备库standby删除

alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;

3.主库redo添加

alter database add logfile group 11 '/u01/app/oracle/oradata/db/redo11.log' size 12m reuse;
alter database add logfile group 12 '/u01/app/oracle/oradata/db/redo12.log' size 12m reuse;
alter database add logfile group 13 '/u01/app/oracle/oradata/db/redo13.log' size 12m reuse;

4.主库redo 删除

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

5.备库redo添加

alter system set standby_file_management='manual';

alter database add logfile group 11 '/u01/app/oracle/oradata/db/redo11.log' size 12m reuse;
alter database add logfile group 12 '/u01/app/oracle/oradata/db/redo12.log' size 12m reuse;
alter database add logfile group 13 '/u01/app/oracle/oradata/db/redo13.log' size 12m reuse;

6.备库redo删除

备库的 log_file_name_convert 参数要设置，否则无法运行 alter database clear logfile group xxx; 语句
如果没有设置，可以先关库，然后修改pfile，加入下面这一行，再启库

SQL> shutdown immediate
*.log_file_name_convert ='/u01/app/oracle/oradata/db/','/u01/app/oracle/oradata/db/'
用pfile 启动备库：
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdb.ora';
SQL> alter database mount standby database;
SQL> select group#,status from v$log;
SQL> show parameter log_file_name_convert

alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

7.主库standby 删除

alter database drop logfile group 4;
alter database drop logfile group 5;
alter database drop logfile group 6;
alter database drop logfile group 7;

8.主库standby 添加

alter database add standby logfile group 14 '/u01/app/oracle/oradata/db/redo14.log' size 12m reuse;
alter database add standby logfile group 15 '/u01/app/oracle/oradata/db/redo15.log' size 12m reuse;
alter database add standby logfile group 16 '/u01/app/oracle/oradata/db/redo16.log' size 12m reuse;
alter database add standby logfile group 17 '/u01/app/oracle/oradata/db/redo17.log' size 12m reuse;

9.恢复同步和备库文件自动管理

alter system set standby_file_management='AUTO';

alter database recover managed standby database using current logfile disconnect;

验证同步是否恢复

--过程中间查询验证脚本

select open_mode from v$database;
select group#,thread#,sequence#,bytes/1024/1024 mb,members,archived,status from v$log;
select group#,thread#,sequence#,bytes/1024/1024 mb,archived,status from V$STANDBY_LOG;