最近接手了几套新的 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://www.liups.com/posts/405b1392/

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