# 問題簡介

```sql
SELECT TRUNC(COMPLETION_TIME) TIME,
       SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024  SIZE_MB,
       COUNT(*) num
  FROM V$ARCHIVED_LOG
 GROUP BY TRUNC(COMPLETION_TIME)
 ORDER BY 1;
```

這條 SQL 我們很熟悉，是用來統計每天生成的歸檔量的，但今天遇到了個奇怪的現象。

### 資訊採集

**歸檔量**

```sql
TIME                   SIZE_MB        NUM
------------------- ---------- ----------
2021-10-09 00:00:00 21354.2207        162
2021-10-10 00:00:00 8132.05078         66
2021-10-11 00:00:00 106382.476        882

```

**儲存使用情況**

```sql
SQL> SELECT GROUP_NUMBER GNO,
  2         NAME,
  3         TOTAL_MB/1024 TOTAL_GB,
  4         FREE_MB/1024 FREE_GB,
  5         FREE_MB / TOTAL_MB * 100 "FREE%",
  6         REQUIRED_MIRROR_FREE_MB RMFMB,
  7         USABLE_FILE_MB UFMB
  8    FROM V$ASM_DISKGROUP;

GNO NAME         TOTAL_GB    FREE_GB      FREE%      RMFMB       UFMB
---------- ---------- ---------- ---------- ---------- ---------- ----------
         1 ARCH              150  82.828125   55.21875          0      84816
         2 DATA             4000 926.681641  23.167041          0     948922
         3 OCR                30 29.0957031 96.9856771      10240       9777
```

**歸檔儲存情況**

```
[grid@REGIONAL-DB-1:/home/grid +ASM1]$ asmcmd
ASMCMD> cd arch
ASMCMD> cd *
ASMCMD> cd *
ASMCMD> ls
2021_10_08/
2021_10_09/
2021_10_10/
2021_10_11/
ASMCMD> 
```

### 疑問

顯示保留的三天的歸檔量，總量應該在 135G 左右，但是儲存顯示只是用了 70 G不到，計算出來的量約是實際佔用的兩倍，那麼是歸檔的統計語句寫錯了？

# 原因

透過了解，這個庫是一套 DG 的資料庫，實際歸檔是兩個歸檔路徑（本地+遠端DG庫）所產生的歸檔總和

```sql
SQL> SELECT value FROM v$parameter WHERE NAME LIKE 'log_archive_dest__' and value is not null;

VALUE
-----------------------------------------------------------------------------------------
location=+ARCH valid_for=(all_logfiles,all_roles) db_unique_name=cender
service=tns_cender_sty lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=cenderdg
```

所以我將原語句做了一個小的更改

```sql
SELECT TRUNC(COMPLETION_TIME) TIME,
       SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 /
       ((SELECT COUNT(*)
           FROM v$parameter
          WHERE NAME LIKE 'log_archive_dest__'
            AND VALUE IS NOT NULL)) SIZE_MB,
       COUNT(*) num
  FROM V$ARCHIVED_LOG
 GROUP BY TRUNC(COMPLETION_TIME)
 ORDER BY 1;

TIME                   SIZE_MB        NUM
------------------- ---------- ----------
2021-10-09 00:00:00 10677.1104        162
2021-10-10 00:00:00 4066.02539         66
2021-10-11 00:00:00  53191.238        872
```

再考慮到有可能做完遷移，沒有清理 DG 環境的可能性，所以又做了進一步的改寫

```sql
SELECT TRUNC(COMPLETION_TIME) TIME,
       SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 /
       ((SELECT COUNT(*)
           FROM v$parameter
          WHERE NAME LIKE 'log_archive_dest__'
            AND VALUE IS NOT NULL) -
        (SELECT COUNT(*) FROM v$archive_dest WHERE error IS NOT NULL)) SIZE_MB,
       COUNT(*) num
  FROM V$ARCHIVED_LOG
 GROUP BY TRUNC(COMPLETION_TIME)
 ORDER BY 1;
```

不足之處就是如果資料庫歸檔到異地出現中斷，因為沒有實時跟蹤異常，即有時異常，有時正常的話，統計就不準了。

Oracle：歸檔量統計

相關文章