[20200114]关于log_archive_dest_1设置.txt

来源：这里教程网时间：2026-03-03 14:46:51 作者：

[20200114]关于log_archive_dest_1设置.txt --//别人问的问题,如果不设置log_archive_dest_1，实际上归档目的设置在log_archive_dest_10并且等于USE_DB_RECOVERY_FILE_DEST. --//我个人建议最好不要这样设置，因为这样如果产生大事务或者异常事务，会导致fast_recovery_area满了，归档日志无法归档，导致 --//系统挂起，维护管理非常被动。 --//启动数据库时可以发现如下信息： Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST。 --//另外的问题就是如果设置log_archive_dest_1等于db_recovery_file_dest的值，为什么归档日志占用很大的磁盘空间，oracle不会出现 --//无法归档，系统挂起的情况。例子： 1.环境： xxxxx> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production xxxxx> show parameter recovery NAME TYPE VALUE -------------------------- ----------- ------ db_recovery_file_dest string +DATA ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ db_recovery_file_dest_size big integer 10G recovery_parallelism integer 0 --//db_recovery_file_dest_size设置太小。 xxxxx> show parameter log_archive_dest_1 NAME TYPE VALUE ------------------ ------- -------------- log_archive_dest_1 string LOCATION=+DATA --//log_archive_dest_1 值等于 db_recovery_file_dest的设置。 2.查询： xxxxx> select * from v$flash_recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES -------------------- ------------------ ------------------------- --------------- CONTROL FILE .06 0 1 REDO LOG 0 0 0 ARCHIVED LOG 0 0 0 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ BACKUP PIECE 0 0 0 IMAGE COPY 0 0 0 FLASHBACK LOG 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 7 rows selected. --//注意下划线，可以发现FILE_TYPE='ARCHIVED LOG'. --//如果查询select view_definition from V$FIXED_VIEW_DEFINITION where view_name='V$RECOVERY_AREA_USAGE';。 --//注意显示是被截断的，view_definition仅仅显示4000字节。 --//你可以查询v$sql,查询包含fusg.file_type,字串的sql语句，发现如下 /* Formatted on 2020/1/14 17:41:47 (QP5 v5.269.14213.34769) */ SELECT fusg.file_type ,DECODE ( NVL2 (ra.name, ra.space_limit, 0) ,0, 0 ,ROUND (NVL (fusg.space_used, 0) / ra.space_limit, 4) * 100 ) ,DECODE ( NVL2 (ra.name, ra.space_limit, 0) ,0, 0 ,ROUND (NVL (fusg.space_reclaimable, 0) / ra.space_limit, 4) * 100 ) ,NVL2 (ra.name, fusg.number_of_files, 0) FROM v$recovery_file_dest ra , (SELECT 'CONTROL FILE' file_type ,SUM ( CASE WHEN ceilasm = 1 AND name LIKE '+%' THEN CEIL ( ( (block_size * file_size_blks) + 1) / 1048576 ) * 1048576 ELSE block_size * file_size_blks END ) space_used ,0 space_reclaimable ,COUNT (*) number_of_files FROM v$controlfile, (SELECT /*+ no_merge */ ceilasm FROM x$krasga) WHERE is_recovery_dest_file = 'YES' UNION ALL SELECT 'REDO LOG' file_type ,SUM ( CASE WHEN ceilasm = 1 AND MEMBER LIKE '+%' THEN CEIL ( (l.bytes + 1) / 1048576) * 1048576 ELSE l.bytes END ) space_used ,0 space_reclaimable ,COUNT (*) number_of_files FROM (SELECT group#, bytes FROM v$log UNION SELECT group#, bytes FROM v$standby_log) l ,v$logfile lf ,(SELECT /*+ no_merge */ ceilasm FROM x$krasga) WHERE l.group# = lf.group# AND lf.is_recovery_dest_file = 'YES' UNION ALL SELECT 'ARCHIVED LOG' file_type ,SUM (al.file_size) space_used ,SUM (CASE WHEN dl.rectype = 11 THEN al.file_size ELSE 0 END) space_reclaimable ,COUNT (*) number_of_files FROM (SELECT recid ,CASE WHEN ceilasm = 1 AND name LIKE '+%' THEN CEIL ( ( (blocks * block_size) + 1) / 1048576 ) * 1048576 ELSE blocks * block_size END file_size FROM v$archived_log, (SELECT /*+ no_merge */ ceilasm FROM x$krasga) WHERE is_recovery_dest_file = 'YES' AND name IS NOT NULL) al ,x$kccagf dl WHERE al.recid = dl.recid(+) AND dl.rectype(+) = 11 UNION ALL SELECT 'BACKUP PIECE' file_type ,SUM (bp.file_size) space_used ,SUM (CASE WHEN dl.rectype = 13 THEN bp.file_size ELSE 0 END) space_reclaimable ,COUNT (*) number_of_files FROM (SELECT recid ,CASE WHEN ceilasm = 1 AND handle LIKE '+%' THEN CEIL ( (bytes + 1) / 1048576) * 1048576 ELSE bytes END file_size FROM v$backup_piece, (SELECT /*+ no_merge */ ceilasm FROM x$krasga) WHERE is_recovery_dest_file = 'YES' AND handle IS NOT NULL) bp ,x$kccagf dl WHERE bp.recid = dl.recid(+) AND dl.rectype(+) = 13 UNION ALL SELECT 'IMAGE COPY' file_type ,SUM (dc.file_size) space_used ,SUM (CASE WHEN dl.rectype = 16 THEN dc.file_size ELSE 0 END) space_reclaimable ,COUNT (*) number_of_files FROM (SELECT recid ,CASE WHEN ceilasm = 1 AND name LIKE '+%' THEN CEIL ( ( (blocks * block_size) + 1) / 1048576 ) * 1048576 ELSE blocks * block_size END file_size FROM v$datafile_copy, (SELECT /*+ no_merge */ ceilasm FROM x$krasga) WHERE is_recovery_dest_file = 'YES' AND name IS NOT NULL) dc ,x$kccagf dl WHERE dc.recid = dl.recid(+) AND dl.rectype(+) = 16 UNION ALL SELECT 'FLASHBACK LOG' file_type ,NVL (fl.space_used, 0) space_used ,NVL (fb.reclsiz, 0) space_reclaimable ,NVL (fl.number_of_files, 0) number_of_files FROM (SELECT SUM ( CASE WHEN ceilasm = 1 AND name LIKE '+%' THEN CEIL ( (fl.bytes + 1) / 1048576) * 1048576 ELSE bytes END ) space_used ,COUNT (*) number_of_files FROM v$flashback_database_logfile fl ,(SELECT /*+ no_merge */ ceilasm FROM x$krasga)) fl , (SELECT SUM (TO_NUMBER (fblogreclsiz)) reclsiz FROM x$krfblog) fb UNION ALL SELECT 'FOREIGN ARCHIVED LOG' file_type ,SUM (rlr.file_size) space_used ,SUM ( CASE WHEN rlr.purgable = 1 THEN rlr.file_size ELSE 0 END ) space_reclaimable ,COUNT (*) number_of_files FROM (SELECT CASE WHEN ceilasm = 1 AND rlnam LIKE '+%' THEN CEIL ( ( (rlbct * rlbsz) + 1) / 1048576) * 1048576 ELSE rlbct * rlbsz END file_size ,CASE WHEN BITAND (rlfl2, 4096) = 4096 THEN 1 WHEN BITAND (rlfl2, 8192) = 8192 THEN 1 ELSE 0 END purgable FROM x$kccrl, (SELECT /*+ no_merge */ ceilasm FROM x$krasga) WHERE BITAND (rlfl2, 64) = 64 AND rlnam IS NOT NULL) rlr) fusg --//可以发现有1个条件is_recovery_dest_file = 'YES'限制查询结果。 xxxxx> select distinct is_recovery_dest_file from V$ARCHIVED_LOG where dest_id=1; IS_ --- NO --//正是这个限制导致查询v$flash_recovery_area_usage看到的情况。 --//如果执行如下(以sys用户执行)： SELECT 'ARCHIVED LOG' file_type ,SUM (al.file_size) space_used ,SUM (CASE WHEN dl.rectype = 11 THEN al.file_size ELSE 0 END) space_reclaimable ,COUNT (*) number_of_files FROM (SELECT recid ,CASE WHEN ceilasm = 1 AND name LIKE '+%' THEN CEIL ( ( (blocks * block_size) + 1) / 1048576 ) * 1048576 ELSE blocks * block_size END file_size FROM v$archived_log, (SELECT /*+ no_merge */ ceilasm FROM x$krasga) WHERE is_recovery_dest_file = 'NO' AND name IS NOT NULL and dest_id=1 ) al ,x$kccagf dl WHERE al.recid = dl.recid(+) AND dl.rectype(+) = 11; FILE_TYPE SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES ------------ ------------ ----------------- --------------- ARCHIVED LOG 14044626944 0 709 --//SPACE_USED=14044626944 --//14044626944/1024/1024/1024 = 13.08G,超出了db_recovery_file_dest_size的限制。 --//执行asmcmd $ asmcmd -p du +DATA/xxxxx/ARCHIVELOG Used_MB Mirror_used_MB 16547 16547 --//大于前面的值（13g），主要问题在于不知道为什么有2017年的一部分归档没有删除。 $ asmcmd -p ls +DATA/xxxxx/ARCHIVELOG 2017_02_09/ 2017_02_10/ 2017_02_11/ 2017_02_12/ 2017_02_13/ 2017_02_14/ 2017_02_15/ 2017_02_16/ 2017_02_17/ 2019_04_28/ 2020_01_08/ 2020_01_09/ 2020_01_10/ 2020_01_11/ 2020_01_12/ 2020_01_13/ 2020_01_14/ 2020_01_15/