[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/
[20200114]关于log_archive_dest_1设置.txt
-
lfree
2020-01-15 08:57:40 -
Oracle
- 原创