1. 首页
  2. IT资讯

[20200114]关于log_archive_dest_1设置.txt

[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/

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

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183084.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code