archive_lag_target 手动设置归档间隔时间，强制日志切换
在Standby中，有时候我们为了减少数据损失，可以设置archive_lag_target参数来进行强制日志切换.
一般设置1800秒 ~ 7200s之间，过低会导致性能问题，过高对于强制日志切换就没有意义了(可能还没有到时间就因为online redo log满了自动切换了).
1.参数值应为整数，单位为秒。取值范围为60~7200，建议值为1800（即30分钟）；
2.参数作用：减少故障时数据损失；有效提高备库可用性
3.参数设置建议：【设置过大】起不到定时切换的作用，一旦主库出现问题，可能导致相当相当多的redo丢失；【设置过小】导致日志频繁切换，影响数据库性能；导致ARCn过于频繁，无法及时分配新的日志组。
col checkpoint_change# format 99999999999999999
select checkpoint_change# from v$database;
select checkpoint_change# from v$datafile;
[oracle@dbatest1 ~]$ ora params log_checkpoint
NAME????????????????????????????????????????? VALUE??????????????? DESCRIPTION
--------------------------------------------- -------------------- ----------------------------------------------------------------------
_log_checkpoint_recovery_check??????????????? 0??????????????????? # redo blocks to verify after checkpoint
log_checkpoint_interval?????????????????????? 0??????????????????? # redo blocks checkpoint threshold
log_checkpoint_timeout??????????????????????? 1800???????????????? Maximum time interval between checkpoints in seconds
log_checkpoints_to_alert????????????????????? FALSE??????????????? log checkpoint begin/end to alert file
[oracle@dbatest1 ~]$ ora params fast_start_???
NAME????????????????????????????????????????? VALUE??????????????? DESCRIPTION
--------------------------------------------- -------------------- ----------------------------------------------------------------------
fast_start_io_target????????????????????????? 0??????????????????? Upper bound on recovery reads
fast_start_mttr_target??????????????????????? 0??????????????????? MTTR target in seconds
fast_start_parallel_rollback????????????????? LOW????????????????? max number of parallel recovery slaves that may be used
[oracle@dbatest1 ~]$
SQL> alter system set log_checkpoint_timeout = 60;
System altered.
--alert_xxx.log:
Tue Aug 28 19:04:22 2012
ALTER SYSTEM SET log_checkpoint_timeout=60 SCOPE=BOTH;
Tue Aug 28 19:04:24 2012
Incremental checkpoint up to RBA [0x1a.2.0],current log tail at RBA [0x1a.904.0]
Tue Aug 28 19:05:24 2012
Incremental checkpoint up to RBA [0x1a.915.0],current log tail at RBA [0x1a.915.0]
Tue Aug 28 19:06:25 2012
Incremental checkpoint up to RBA [0x1a.915.0],current log tail at RBA [0x1a.982.0]
Tue Aug 28 19:07:25 2012
Incremental checkpoint up to RBA [0x1a.985.0],current log tail at RBA [0x1a.985.0]
Tue Aug 28 19:08:25 2012
Incremental checkpoint up to RBA [0x1a.986.0],current log tail at RBA [0x1a.986.0]
Tue Aug 28 19:09:25 2012
Incremental checkpoint up to RBA [0x1a.986.0],current log tail at RBA [0x1a.986.0]
---
col name format a40
select recid,name,thread#,sequence# from v$archived_log ;
$ ora params archive_lag
NAME????????????????????????????????????????? VALUE??????????????? DESCRIPTION
--------------------------------------------- -------------------- ----------------------------------------------------------------------
archive_lag_target??????????????????????????? 0??????????????????? Maximum number of seconds of redos the standby could lose
--默认值为0，表示不启用该参数.switch logfile 触发 checkpoint.
SQL> alter system set archive_lag_target = 60;
System altered.
--alert_xxx.log:
Tue Aug 28 19:40:20 2012
ALTER SYSTEM SET archive_lag_target=60 SCOPE=BOTH;
Tue Aug 28 19:40:20 2012
Beginning log switch checkpoint up to RBA [0x1b.2.10],SCN: 1657948
Thread 1 advanced to log sequence 27 (LGWR switch)
? Current log# 3 seq# 27 mem# 0: /oradg/pri/onlinelog/pri/redo03.log
Tue Aug 28 19:40:20 2012
Archived Log entry 21 added for thread 1 sequence 26 ID 0xf9978d38 dest 1:
Tue Aug 28 19:40:31 2012
Incremental checkpoint up to RBA [0x1a.9ee.0],current log tail at RBA [0x1b.2.0]
Tue Aug 28 19:41:23 2012
Beginning log switch checkpoint up to RBA [0x1c.2.10],SCN: 1657975
Thread 1 advanced to log sequence 28 (LGWR switch)
? Current log# 1 seq# 28 mem# 0: /oradg/pri/onlinelog/pri/redo01.log
Tue Aug 28 19:41:24 2012
Archived Log entry 22 added for thread 1 sequence 27 ID 0xf9978d38 dest 1:
Tue Aug 28 19:42:24 2012
Thread 1 cannot allocate new log,sequence 29
Checkpoint not complete
? Current log# 1 seq# 28 mem# 0: /oradg/pri/onlinelog/pri/redo01.log
Tue Aug 28 19:42:26 2012
Completed checkpoint up to RBA [0x1c.2.10],SCN: 1657975
Completed checkpoint up to RBA [0x1b.2.10],SCN: 1657948
Beginning log switch checkpoint up to RBA [0x1d.2.10],SCN: 1657998
Thread 1 advanced to log sequence 29 (LGWR switch)
? Current log# 2 seq# 29 mem# 0: /oradg/pri/onlinelog/pri/redo02.log
Completed checkpoint up to RBA [0x1d.2.10],SCN: 1657998
Tue Aug 28 19:42:27 2012
Archived Log entry 23 added for thread 1 sequence 28 ID 0xf9978d38 dest 1:
?
1.log_archive_dest/log_archive_duplex_dest
2.log_archive_dest_n
3.db_recovery_file_dest
1 incompatible with 3
1 incompatible with 2
log_archive_dest/log_archive_duplex_dest:
For Enterprise Edition users,this parameter has been deprecated in favor of the LOG_ARCHIVE_DEST_n parameters. If Oracle Enterprise Edition is not installed or it is installed,but you have not specified any LOG_ARCHIVE_DEST_n parameters,this parameter is valid.
LOG_ARCHIVE_DEST is applicable only if you are running the database in ARCHIVELOG mode or are recovering a database from archived redo logs. LOG_ARCHIVE_DEST is incompatible with the LOG_ARCHIVE_DEST_n parameters,and must be defined as the null string ("") or (' ') when any LOG_ARCHIVE_DEST_n parameter has a value other than a null string.
LOG_ARCHIVE_DUPLEX_DEST is similar to the initialization parameter LOG_ARCHIVE_DEST. This parameter specifies a second archive destination: the duplex archive destination. This duplex archive destination can be either a must-succeed or a best-effort archive destination,depending on how many archive destinations must succeed (as specified in the LOG_ARCHIVE_MIN_SUCCEED_DEST parameter).
$ oerr ora 16018?????????????????????????????
16018,00000,"cannot use %s with LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST"
// *Cause:? One of the following events caused an incompatibility:
//????????? 1) Parameter LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST
//????????? was in use when a LOG_ARCHIVE_DEST_n (n = 1...31) parameter
//????????? was encountered while fetching initialization parameters.
//????????? 2) An ALTER SYSTEM ARCHIVE LOG START TO command was in effect when
//????????? a LOG_ARCHIVE_DEST_n parameter was encountered while fetching
//????????? initialization parameters.
//????????? 3) A LOG_ARCHIVE_DEST_n parameter was in use when an ALTER SYSTEM
//????????? command was used to define a value for either the LOG_ARCHIVE_DEST
//????????? or LOG_ARCHIVE_DUPLEX_DEST parameter.
//????????? 4) Parameter DB_RECOVERY_FILE_DEST was in use when an attempt
//????????? was made to use an ALTER SYSTEM or ALTER SESSION command to
//????????? define a value for LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST.
// *Action: Eliminate any incompatible parameter definitions.
db_recovery_file_dest:
Specifying this parameter without also specifying the DB_RECOVERY_FILE_DEST_SIZE initialization parameter is not allowed.This parameter is conflict with log_archive_dest/log_archive_duplex_dest.
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=1g ;
System altered.
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=0;
alter system set DB_RECOVERY_FILE_DEST_SIZE=0
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-19803: Parameter DB_RECOVERY_FILE_DEST_SIZE is out of range (1 - 18446744073709551614)
SQL> alter system reset db_recovery_file_dest_size scope=spfile;
System altered
SQL> startup force
ORACLE instance started.
Total System Global Area? 684785664 bytes
Fixed Size????????????????? 2231512 bytes
Variable Size???????????? 465568552 bytes
Database Buffers????????? 209715200 bytes
Redo Buffers??????????????? 7270400 bytes
Database mounted.
Database opened.
SQL> show parameter db_recovery_file_dest_size
NAME???????????????????????????????? TYPE??????? VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size?????????? big integer 0
--flashback database
db_recovery_file_dest：指定闪回恢复区的位置
db_recovery_file_dest_size：指定闪回恢复区的可用空间大小
db_flashback_retention_target：指定数据库可以回退的时间，单位为分钟，默认1440分钟，也就是一天。当然，实际上可回退的时间还决定于闪回恢复区的大小，因为里面保存了回退所需要的 flash log。所以这个参数要和db_recovery_file_dest_size配合修改。
--查看数据库是否开闪回
select flashback_on from v$database;
--查看数据库能闪回到的最早的scn，如果数据库没有开闪回功能是没有记录返回的。
select oldest_flashback_scn from v$flashback_database_log;
select * from v$recovery_file_dest;
select * from v$flash_recovery_area_usage;
在自动调整情况下，实际的撤销信息最短保留时间可以通过查询V$UNDOSTAT视图上的TUNED_UNDORETENTION列获得。
select max(tuned_undoretention),min(tuned_undoretention) from v$undostat ;
alter system set "_undo_autotune" = false scope = both;
