当前位置: 代码迷 >> 综合 >> Oracle 11g DataGuard 主备库调整redo log 和 standby redo log 数量和大小
  详细解决方案

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

热度:23   发布时间:2024-01-16 02:17:02.0

注意事项:

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;

  相关解决方案