ITPub博客

首页 > 数据库 > Oracle > Oracle DataGuard环境主备库日志组数和大小调整

Oracle DataGuard环境主备库日志组数和大小调整

原创 Oracle 作者:db_wjw 时间:2016-04-19 11:24:35 0 删除 编辑
Oracle DataGuard主备库日志组数和大小调整:

检查当前主备的redo日志文件和standby日志文件:
redo log:
select l.group#,l.bytes/1024/1024 mb,lf.member from v$log l,v$logfile lf where l.group#=lf.group# order by group#;
select group#,status from v$log;

standby log:
select sl.group#,sl.bytes/1024/1024 mb,lf.member from v$standby_log sl,v$logfile lf where sl.group#=lf.group# order by group#;
select group#,status from v$standby_log;


一、增减日志文件:

1、在主库和备库都设置standby_file_management为manual
alter system set standby_file_management='manual';

2、备库上停止恢复:
alter database recover managed standby database cancel;

3、在主库和备库上增加或者减少online和standby日志:
增加:
alter database add logfile group 2 ('/u02/app/oracle/oradata/ncf/redo02a.log','/u02/app/oracle/oradata/ncf/redo02b.log') size 200m;
alter database add standby logfile group 13 ('/u02/app/oracle/oradata/ncf/standby13_1.log','/u02/app/oracle/oradata/ncf/standby13_2.log') size 200m;
减少:
如果显示状态为clearing,则先clear,再删除日志:
alter database clear logfile group 8;
alter database drop logfile group 8;
host rm -rf /u02/app/oracle/oradata/ncf/redo08a.log
host rm -rf /u02/app/oracle/oradata/ncf/redo08b.log

4、在主库和备库设置standby_file_management为auto
alter system set standby_file_management='auto';

5、备库上启动同步:
alter database recover managed standby database using current logfile disconnect;

6、检查状态:
主库多次执行:alter system switch logfile;
主库:select max(sequence#) from v$archived_log;
备库:select max(sequence#) from v$archived_log where applied='YES';
     SELECT name,value,time_computed FROM V$DATAGUARD_STATS WHERE NAME like '%lag%';


二、修改日志文件大小:

1、在主库和备库都设置standby_file_management为manual
alter system set standby_file_management='manual';

2、备库上停止恢复:
alter database recover managed standby database cancel;

3、主库操作:
处理主库的redo log:
select l.group#,l.bytes/1024/1024 mb,lf.member from v$log l,v$logfile lf where l.group#=lf.group# order by group#;

    GROUP#       MB MEMBER
---------- ---------- --------------------------------------------------
     2      50 /u02/app/oracle/oradata/ncf/redo02a.log
     2      50 /u02/app/oracle/oradata/ncf/redo02b.log
     3      50 /u02/app/oracle/oradata/ncf/redo03a.log
     3      50 /u02/app/oracle/oradata/ncf/redo03b.log
     4      50 /u02/app/oracle/oradata/ncf/redo04a.log
     4      50 /u02/app/oracle/oradata/ncf/redo04b.log

select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
     2 CURRENT
     3 INACTIVE
     4 INACTIVE


alter database drop logfile group 4;
host rm -rf /u02/app/oracle/oradata/ncf/redo04a.log
host rm -rf /u02/app/oracle/oradata/ncf/redo04b.log
alter database add logfile group 4 ('/u02/app/oracle/oradata/ncf/redo04a.log','/u02/app/oracle/oradata/ncf/redo04b.log') size 100m;
alter database drop logfile group 3;
host rm -rf /u02/app/oracle/oradata/ncf/redo03a.log
host rm -rf /u02/app/oracle/oradata/ncf/redo03b.log
alter database add logfile group 3 ('/u02/app/oracle/oradata/ncf/redo03a.log','/u02/app/oracle/oradata/ncf/redo03b.log') size 100m;

alter system switch logfile;

select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
     2 ACTIVE
     3 CURRENT
     4 UNUSED
alter system checkpoint;

select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
     2 INACTIVE
     3 CURRENT
     4 UNUSED
alter database drop logfile group 2;
host rm -rf /u02/app/oracle/oradata/ncf/redo02a.log
host rm -rf /u02/app/oracle/oradata/ncf/redo02b.log
alter database add logfile group 2 ('/u02/app/oracle/oradata/ncf/redo02a.log','/u02/app/oracle/oradata/ncf/redo02b.log') size 100m;

处理主库的standby log:
SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ----------
    10 UNASSIGNED
    11 UNASSIGNED
    12 UNASSIGNED
    13 UNASSIGNED

alter database drop logfile group 10;
alter database drop logfile group 11;
alter database drop logfile group 12;
alter database drop logfile group 13;
host rm -rf /u02/app/oracle/oradata/ncf/standby10a.log
host rm -rf /u02/app/oracle/oradata/ncf/standby10b.log
host rm -rf /u02/app/oracle/oradata/ncf/standby11a.log
host rm -rf /u02/app/oracle/oradata/ncf/standby11b.log
host rm -rf /u02/app/oracle/oradata/ncf/standby12a.log
host rm -rf /u02/app/oracle/oradata/ncf/standby12b.log
host rm -rf /u02/app/oracle/oradata/ncf/standby13a.log
host rm -rf /u02/app/oracle/oradata/ncf/standby13b.log

alter database add standby logfile group 10 ('/u02/app/oracle/oradata/ncf/standby10a.log','/u02/app/oracle/oradata/ncf/standby10b.log') size 100m;
alter database add standby logfile group 11 ('/u02/app/oracle/oradata/ncf/standby11a.log','/u02/app/oracle/oradata/ncf/standby11b.log') size 100m;
alter database add standby logfile group 12 ('/u02/app/oracle/oradata/ncf/standby12a.log','/u02/app/oracle/oradata/ncf/standby12b.log') size 100m;
alter database add standby logfile group 13 ('/u02/app/oracle/oradata/ncf/standby13a.log','/u02/app/oracle/oradata/ncf/standby13b.log') size 100m;

4、备库操作:
处理备库的redo log:
select l.group#,l.bytes/1024/1024 mb,lf.member from v$log l,v$logfile lf where l.group#=lf.group# order by group#;
    GROUP#       MB MEMBER
---------- ---------- --------------------------------------------------
     2      50 /u02/app/oracle/oradata/ncf/redo02a.log
     2      50 /u02/app/oracle/oradata/ncf/redo02b.log
     3      50 /u02/app/oracle/oradata/ncf/redo03a.log
     3      50 /u02/app/oracle/oradata/ncf/redo03b.log
     4      50 /u02/app/oracle/oradata/ncf/redo04a.log
     4      50 /u02/app/oracle/oradata/ncf/redo04b.log

select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
     2 CLEARING
     3 CURRENT
     4 CLEARING

alter database clear logfile group 4;
alter database drop logfile group 4;
host rm -rf /u02/app/oracle/oradata/ncf/redo04a.log
host rm -rf /u02/app/oracle/oradata/ncf/redo04b.log
alter database add logfile group 4 ('/u02/app/oracle/oradata/ncf/redo04a.log','/u02/app/oracle/oradata/ncf/redo04b.log') size 100m;

alter database clear logfile group 2;
alter database drop logfile group 2;
host rm -rf /u02/app/oracle/oradata/ncf/redo02a.log
host rm -rf /u02/app/oracle/oradata/ncf/redo02b.log
alter database add logfile group 2 ('/u02/app/oracle/oradata/ncf/redo02a.log','/u02/app/oracle/oradata/ncf/redo02b.log') size 100m;

针对current的日志,需要先在主库上切换日志,变为clearing再处理:
select group#,status from v$log;
    GROUP# STATUS
---------- ----------------
     2 CURRENT
     3 CLEARING
     4 UNUSED

alter database clear logfile group 3;
alter database drop logfile group 3;
host rm -rf /u02/app/oracle/oradata/ncf/redo03a.log
host rm -rf /u02/app/oracle/oradata/ncf/redo03b.log
alter database add logfile group 3 ('/u02/app/oracle/oradata/ncf/redo03a.log','/u02/app/oracle/oradata/ncf/redo03b.log') size 100m;

处理备库的standby log:
select sl.group#,sl.bytes/1024/1024 mb,lf.member from v$standby_log sl,v$logfile lf where sl.group#=lf.group# order by group#;

    GROUP#       MB MEMBER
---------- ---------- --------------------------------------------------
    10      50 /u02/app/oracle/oradata/ncf/standby10a.log
    10      50 /u02/app/oracle/oradata/ncf/standby10b.log
    11      50 /u02/app/oracle/oradata/ncf/standby11a.log
    11      50 /u02/app/oracle/oradata/ncf/standby11b.log
    12      50 /u02/app/oracle/oradata/ncf/standby12a.log
    12      50 /u02/app/oracle/oradata/ncf/standby12b.log
    13      50 /u02/app/oracle/oradata/ncf/standby13a.log
    13      50 /u02/app/oracle/oradata/ncf/standby13b.log

select group#,status from v$standby_log;

    GROUP# STATUS
---------- ----------
    10 ACTIVE
    11 UNASSIGNED
    12 UNASSIGNED
    13 UNASSIGNED



alter database drop logfile group 11;
alter database drop logfile group 12;
alter database drop logfile group 13;
host rm -rf /u02/app/oracle/oradata/ncf/standby11a.log
host rm -rf /u02/app/oracle/oradata/ncf/standby11b.log
host rm -rf /u02/app/oracle/oradata/ncf/standby12a.log
host rm -rf /u02/app/oracle/oradata/ncf/standby12b.log
host rm -rf /u02/app/oracle/oradata/ncf/standby13a.log
host rm -rf /u02/app/oracle/oradata/ncf/standby13b.log
alter database add standby logfile group 11 ('/u02/app/oracle/oradata/ncf/standby11a.log','/u02/app/oracle/oradata/ncf/standby11b.log') size 100m;
alter database add standby logfile group 12 ('/u02/app/oracle/oradata/ncf/standby12a.log','/u02/app/oracle/oradata/ncf/standby12b.log') size 100m;
alter database add standby logfile group 13 ('/u02/app/oracle/oradata/ncf/standby13a.log','/u02/app/oracle/oradata/ncf/standby13b.log') size 100m;


针对active的日志,需要先在主库上切换日志再处理
SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ----------
    10 UNASSIGNED
    11 ACTIVE
    12 UNASSIGNED
    13 UNASSIGNED
alter database drop logfile group 10;
host rm -rf /u02/app/oracle/oradata/ncf/standby10a.log
host rm -rf /u02/app/oracle/oradata/ncf/standby10b.log
alter database add standby logfile group 10 ('/u02/app/oracle/oradata/ncf/standby10a.log','/u02/app/oracle/oradata/ncf/standby10b.log') size 100m;


5、在主库和备库设置standby_file_management为auto
alter system set standby_file_management='auto';

6、备库上启动同步:
alter database recover managed standby database using current logfile disconnect;

7、检查状态:
主库多次执行:alter system switch logfile;
主库:select max(sequence#) from v$archived_log;
备库:select max(sequence#) from v$archived_log where applied='YES';
     SELECT name,value,time_computed FROM V$DATAGUARD_STATS WHERE NAME like '%lag%';

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26194851/viewspace-2083912/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-08-21

  • 博文量
    96
  • 访问量
    452142