最近接手了几套新的 ORACLE 数据库，发现其归档空间经常满，通过 crontab -l 初步一看是有定时删除归档脚本的，检查磁盘空间df -h 发现，挂载的 NFS 磁盘空间满了，归档是存放在 ASM 的，心想这不影响删除归档呢，这NFS 是用来存放备份的。然后检查 rman 脚本发现NFS确实是存放 rman 备份，但是感觉这跟删除归档失败应该没关系。之后就检查删除归档的脚本日志，发现日志报 Linux-x86_64 Error: 28: No space left on device 好家伙磁盘空间不足。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
input archived log thread=1 sequence=218876 RECID=332434 STAMP=1150349788
channel c2: starting piece 1 at 2023-10-18 08:01:24
RMAN-03009: failure of backup command on c1 channel at 10/18/2023 08:07:39
ORA-19502: write error on file "/backup1/db10cent_bak/archbak/ORCLDB.60855.1.1150531279.ARC", block number 1484801 (block size=512)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 1484801
Additional information: -1
channel c1 disabled, job failed on it will be run on another channel
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on c2 channel at 10/18/2023 08:07:39
ORA-19502: write error on file "/backup1/db10cent_bak/archbak/ORCLDB.60856.1.1150531282.ARC", block number 1607681 (block size=512)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 1607681
Additional information: -1

检查删除归档的脚本如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
……
echo "=================================================================================" >>${1}/backup_arch.log
echo "Begin backup at : `date`" >>${1}/backup_arch.log
rman target /  <<EOF >> ${1}/backup_arch.log
run{
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
ALLOCATE CHANNEL c1 TYPE DISK;
ALLOCATE CHANNEL c2 TYPE DISK;
#sql "alter system archive log current";
backup as COMPRESSED backupset archivelog all not backed up format '${1}/ORCL_a.%d.%s.%p.%t.ARC';
backup current controlfile format '${1}/%d.%s.%p.%T.CTL';
crosscheck archivelog all;
delete force noprompt archivelog all completed before 'sysdate - 5';
delete noprompt expired backup;
delete noprompt obsolete;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
}
exit
EOF
echo "End backup at : `date`" >>${1}/backup_arch.log
echo "=================================================================================" >>${1}/backup_arch.log
exit 0
[oracle@zdb001 archbak]$

确实存在 delete force noprompt archivelog all completed before 'sysdate - 5'; ，但是前面是先备份归档，然后再删除5天的归档，好吧，那备份的磁盘空间满了，肯定就导致备份失败，然后后面的删除就失败了。这不是本次的重点，重点是在清理备份文件的时候，发现备份文件名上根本没有时间戳，而list backup 超过一定时间之后的归档在控制文件不记录的。只能通过操作系统的命令进行删除，发现备份目录竟然都存在22年的备份文件，要删除这些备份就需要谨慎了，文件名没有时间戳，通过find 删除无法进行二次确认删除的是否准确，这是不能忍的的，查看了脚本的format，好家伙，虽然写了一堆的format ，但是感觉一个管用的都没有，什么%d%s%p%t，啥用都没有，都不如一个 %F%T 管用。所以这里整理了下 rman format的格式。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
9427    9427    1   1   AVAILABLE   DISK        ……/ora_a_ORCL_1147533053_198911l326btnt
9428    9428    1   1   AVAILABLE   DISK        ……/ora_a_ORCL_1147533088_198921l426btp0
9429    9429    1   1   AVAILABLE   DISK        ……/ora_a_ORCL_1147533134_198931l526btqe
9444    9444    1   1   AVAILABLE   DISK        ……/ora_a_ORCL_1147620267_199081lk26eitb
9445    9445    1   1   AVAILABLE   DISK        ……/ora_a_ORCL_1147620302_199091ll26eiue
9446    9446    1   1   AVAILABLE   DISK        ……/ora_a_ORCL_1147620338_199101lm26eivi
9452    9452    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1147870164_199181lu26m6uk
9461    9461    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1147879463_199271m726mg17
9466    9466    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1148040010_199341me26rcqa
9487    9487    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1148570495_199611n927birv
9490    9490    1   1   AVAILABLE   DISK        ……/ora_a_ORCL_1148571225_199641nc27bjip
9491    9491    1   1   AVAILABLE   DISK        ……/ora_a_ORCL_1148571370_199651nd27bjna
9492    9492    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1148644810_199671nf27drea
9493    9493    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1148647126_199681ng27dtmm
9494    9494    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1148649391_199691nh27dvtf
9495    9495    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1148651767_199701ni27e27n
9496    9496    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1148653773_199711nj27e46d
9497    9497    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1148655428_199721nk27e5q4
9498    9498    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1148656174_199731nl27e6he
9499    9499    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1148656839_199741nm27e767

废话不说，上官方连接：https://docs.oracle.com/en/database/oracle/oracle-database/19/rcmrf/formatSpec.html#GUID-E51F637A-57E0-4B06-803F-3F879DF5BEED

*formatSpec*

The following table lists RMAN substitution variables that are valid in format strings.

Syntax Element Description
%a Specifies the activation ID of the database.
%b Specifies the file name stripped of directory paths. It is only valid for SET NEWNAME and backup resetwhen producing image copies It yields errors if used as a format specification for a backup that produces backup pieces.
%c Specifies the copy number of the backup piece within a set of duplexed backup pieces. If you did not duplex a backup, then this variable is 1 for backup sets and 0 for proxy copies. If a command is enabled, then the variable shows the copy number. The maximum value for %c is 256.
%d Specifies the name of the database (see Example 4-23).
%D Specifies the current day of the month from the Gregorian calendar in format DD.
%e Specifies the archived log sequence number.
%f Specifies the absolute file number (see Example 4-23).
%F Combines the DBID, day, month, year, and sequence into a unique and repeatable generated name. This variable translates into c-IIIIIIIIII-YYYYMMDD-QQ, where:IIIIIIIIII stands for the DBID. The DBID is printed in decimal so that it can be easily associated with the target database.YYYYMMDD is a time stamp in the Gregorian calendar of the day the backup is generated*QQ* is the sequence in hexadecimal number that starts with 00 and has a maximum of ‘FF’ (256)Note: %F is valid only in the CONFIGURE CONTROLFILE AUTOBACKUP FORMAT command.
%h Specifies the archived redo log thread number.
%I Specifies the DBID.
%M Specifies the month in the Gregorian calendar in format MM.
%N Specifies the tablespace name. This substitution variable is only valid when backing up data files as image copies.
%n Specifies the name of the database, padded on the right with x characters to a total length of eight characters. For example, if prod1 is the database name, then the padded name is prod1xxx.
%p Specifies the piece number within the backup set. This value starts at 1 for each backup set and is incremented by 1 as each backup piece is created.Note: If you specify PROXY, then the %p variable must be included in the FORMAT string either explicitly or implicitly within %U.
%r Specifies the resetlogs ID.
%s Specifies the backup set number. This number is a counter in the control file that is incremented for each backup set. The counter value starts at 1 and is unique for the lifetime of the control file. If you restore a backup control file, then duplicate values can result. Also, CREATE CONTROLFILE initializes the counter back to 1.
%t Specifies the backup set time stamp, which is a 4-byte value derived as the number of seconds elapsed since a fixed reference time. You can use a combination of %s and %t to form a unique name for the backup set.
%T Specifies the year, month, and day in the Gregorian calendar in this format: YYYYMMDD.
%u Specifies an 8-character name constituted by compressed representations of the backup set or image copy number and the time the backup set or image copy was created.
%U Specifies a system-generated unique file name (default).The meaning of %U is different for image copies and backup pieces. For a backup piece, %U specifies a convenient shorthand for %u_%p_%c that guarantees uniqueness in generated backup file names. For an image copy of a data file, %U means the following:data-D-%d_id-%I_TS-%N_FNO-%f_%uFor an image copy of an archived redo log, %U means the following:arch-D_%d-id-%I_S-%e_T-%h_A-%a_%uFor an image copy of a control file, %U means the following:cf-D_%d-id-%I_%u
%Y Specifies the year in this format: YYYY.
%% Specifies the percent (%) character. For example, %%Y translates to the string %Y.

使用FORMAT参数进行替换变量（注意大小写）：

%a：Oracle数据库的activation ID即RESETLOG_ID
%c：备份片段的复制数（从1开始编号，最大不超过256）
%d：Oracle数据库名称
%T：当前时间的年月日格式（YYYYMMDD）
%Y：当前时间中的年，格式为YYYY
%M：当前时间中的月，格式为MM
%D：当前时间中的日，格式为DD
%e：归档序号
%f：绝对文件编号
%F：基于”DBID+时间”确定的唯一名称，格式的形式为c--YYYYMMDD-QQ，YYYYMMDD为日期，QQ是一个1～256的序列
%h：归档日志线程号
%I：Oracle数据库的DBID。
%N：表空间名称。
%n：数据库名称，并且会在右侧用x字符进行填充，使其保持长度为8
%p：备份集中备份片段的编号，从1开始
%s：备份集号
%t：备份集时间戳
%u：是一个由备份集编号和建立时间压缩后组成的8字符名称。利用%u可以为每个备份集生成一个唯一的名称
%U：默认是%u_%p_%c的简写形式，利用它可以为每一个备份片段（即磁盘文件）生成一个唯一名称，默认格式

%U对应各种默认格式生成备份片段时，%U=%u_%p_%c
生成数据文件镜像复制时，%U=data-D-%d_id-%I_TS-%N_FNO-%f_%u
生成归档文件镜像复制时，%U=arch-D_%d-id-%I_S-%e_T-%h_A-%a_%u
生成控制文件镜像复制时，%U=cf-D_%d-id-%I_%u

其实就使用一个 %F即可，或者使用一个 $U，就可以确保唯一，还带时间戳。

以上作为记录备查。

原文作者: liups.com

原文链接: http://liups.cn/posts/405b1392/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议