[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20200219]log_archive_dest_1定義問題.txtHive
- [20240804]關於kitty設定與linux LANG環境設定問題.txtLinux
- [20221214]limits.conf裡面關於memlock設定問題.txtMIT
- MySQL 關於Table cache設定MySql
- css中關於table的相關設定CSS
- postman關於斷言的設定Postman
- 關於border設定無邊框
- [20190930]關於資料結構設計問題.txt資料結構
- 關於移動端rem的設定REM
- [20191202]關於hugepages相關問題.txt
- [201804012]關於hugepages 3.txt
- [20180306]關於DEFERRED ROLLBACK.txt
- [20210506]]關於ORA-01450.txt
- [20230224]bbed設定偏移技巧.txt
- [20201221]spfile設定問題.txt
- HTML頁面關於高分屏的設定HTML
- 關於遊戲中“設定介面”的思考遊戲
- [20181123]關於降序索引問題.txt索引
- [20181212]關於truncate reuse storage.txt
- [20190401]關於semtimedop函式呼叫.txt函式
- [20180912]關於ANSI joins語法.txt
- [20180705]關於hash join 2.txt
- [20190821]關於CPU成本計算.txt
- [20191129]關於hugepages的問題.txt
- [20180306]關於DEFERRED ROLLBACK2.txt
- [20180403]關於時區問題.txt
- [20210527]關於v$wait_chain.txtAI
- [20210410]關於time命令的解析.txt
- [20200220]windows設定keepalive引數.txtWindows
- [BUG反饋]關於設定選單的BUG
- 關於部落格園設定awescnb皮膚教程
- [20190415]關於shared latch(共享栓鎖).txt
- [20190918]關於函式索引問題.txt函式索引
- [20200223]關於latch and mutext的優化.txtMutex優化
- [20201104]關於稀疏檔案(sparse files).txt
- [20181229]關於字串的分配問題.txt字串
- [20230317]關於TIME_WAIT問題.txtAI
- [20210520]關於主鍵索引問題.txt索引