[20200114]關於log_archive_dest_1設定.txt

lfree發表於2020-01-15

[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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章