需求,由于一套生产环境归档日志切换频率过低,建议修改参数,使其间隔一定时间周期自动切换生成归档日志; SQL>select thread#,sequence#,to_char(completion_time,'yyyy-mm-dd hh24:mi') from v$archived_log where completion_time>sysdate-10; THREAD# SEQUENCE# TO_CHAR(COMPLETION_TIME,'YYYY-MM ---------- ---------- -------------------------------- 1 1366 2019-08-13 23:55 1 1367 2019-08-14 02:43 1 1368 2019-08-18 11:02 1 1369 2019-08-19 03:37 测试环境归档日志切换频率非常低,生产环境类似，如果生产环境online redo出现问题，将丢失数十小时的数据。 SQL>select a.ksppinm,b.ksppstvl,a.ksppdesc from x$ksppi a, x$ksppcv b where (a.indx=b.indx) and a.ksppinm like '%archive_lag_target%'; -------------------------------------------------------------------------------- archive_lag_target 0 Maximum number of seconds of redos the standby could lose 秒为单位,重做日志切换时间
生产环境建议配置1800s,即建议30分钟日志切换一次。
|
Setup of archive_lag_target anywhere between [60, 7200] seconds: use of setting archive_lag_target in non-standby envrionment ARCHIVE_LAG_TARGET limits the amount of data that can be lost and effectively increases the availability of the standby database by forcing a log switch after the specified amount of time elapses. A 0 value disables the time-based thread advance feature; otherwise, the value represents the number of seconds. Values larger than 7200 seconds are not of much use in maintaining a reasonable lag in the standby database. The typical, or recommended value is 1800 (30 minutes). Extremely low values can result in frequent log switches, which could degrade performance; such values can also make the archiver process too busy to archive the continuously generated logs. |
SQL> alter system set archive_lag_target=900; --15分钟 SQL> select sysdate from dual; SYSDATE ------------------- 2019-08-19 03:41:07 测试环境没有数据,按照参数,应该是再03:52分,alert应该存在日志切换记录信息。 Alert日志信息 Mon Aug 19 03:52:04 2019 Thread 1 advanced to log sequence 1371 (LGWR switch) Current log# 3 seq# 1371 mem# 0: /11.2.0.4/app/oracle/oradata/tt11204/redo03.log Mon Aug 19 03:52:04 2019 Archived Log entry 1597 added for thread 1 sequence 1370 ID 0x756770e0 dest 1: Mon Aug 19 03:52:07 2019