原文地址:http://blog.itpub.net/26194851/viewspace-2083912/
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%';