在检查一个测试库的备份时出现了这样一个问题(该测试库没有使用recovery catalog)：RETENTION POLICY设置为REDUNDANCY 1，运行report need backup提示有一个datafile需要备份，但使用list命令查看这个datafile已经有3个备份，满足retention policy的设置，没有必要重新backup。以下是对这个问题的详细分析：

###RMAN config
RMAN> show all;

RMAN configuration parameters for database with db_unique_name TSTDB1 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/app/oracle/product/11.2.0/db_1/dbs/snapcf_tstdb1.f'; # default

RMAN> backup database;

Starting backup at 20150606 13:38:21
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/oradata06/testaaaaa/undotbs01.dbf
input datafile file number=00009 name=/oradata06/testaaaaa/ts0422_1.dbf
input datafile file number=00001 name=/oradata06/testaaaaa/system01.dbf
input datafile file number=00004 name=/oradata06/testaaaaa/users01.dbf
input datafile file number=00002 name=/oradata06/testaaaaa/sysaux01.dbf
input datafile file number=00006 name=/oradata06/testaaaaa/xdbts1.dbf
input datafile file number=00005 name=/oradata06/testaaaaa/ts0329_1.dbf
input datafile file number=00007 name=/oradata06/testaaaaa/ts0212.dbf
input datafile file number=00008 name=/oradata06/testaaaaa/ts0212_1.dbf
channel ORA_DISK_1: starting piece 1 at 20150606 13:38:23
channel ORA_DISK_1: finished piece 1 at 20150606 13:38:58
piece handle=/oradata06/fra/TSTDB1/backupset/2015_06_06/o1_mf_nnndf_TAG20150606T133821_1k67utf5Y_.bkp tag=TAG20150606T133821 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 20150606 13:38:59
channel ORA_DISK_1: finished piece 1 at 20150606 13:39:00
piece handle=/oradata06/fra/TSTDB1/backupset/2015_06_06/o1_mf_ncsnf_TAG20150606T133821_1k67x1hL1_.bkp tag=TAG20150606T133821 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20150606 13:39:00

###report need backup提示datafile 5需要备份
RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
5 0 /oradata06/testaaaaa/ts0329_1.dbf

RMAN> report schema;

Report of database schema for database with db_unique_name TSTDB1

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1024 SYSTEM YES /oradata06/testaaaaa/system01.dbf
2 896 SYSAUX NO /oradata06/testaaaaa/sysaux01.dbf
3 2048 UNDOTBS YES /oradata06/testaaaaa/undotbs01.dbf
4 1024 USERS NO /oradata06/testaaaaa/users01.dbf
5 128 TS0329 NO /oradata06/testaaaaa/ts0329_1.dbf
6 500 XDBTS NO /oradata06/testaaaaa/xdbts1.dbf
7 128 TS0212 NO /oradata06/testaaaaa/ts0212.dbf
8 48 TS0212 NO /oradata06/testaaaaa/ts0212_1.dbf
9 1800 TS0422_1 NO /oradata06/testaaaaa/ts0422_1.dbf

###备份信息显示datafile 5已经有三个备份了
RMAN> list backup of database by file;

List of Datafile Backups
========================

File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies Compressed Tag
---- ------- - -- - ---------- ----------------- ------- ------- ---------- ---
1 11198 B F A 12723362125653 20150606 13:38:23 1 1 NO TAG20150606T133821
2 11198 B F A 12723362125653 20150606 13:38:23 1 1 NO TAG20150606T133821
10697 B F A 12723362120672 20150606 12:52:47 1 1 NO TAG20150606T125246
3 11198 B F A 12723362125653 20150606 13:38:23 1 1 NO TAG20150606T133821
4 11198 B F A 12723362125653 20150606 13:38:23 1 1 NO TAG20150606T133821
5 11198 B F A 12723362115342 20150606 11:56:35 1 1 NO TAG20150606T133821
10970 B F A 12723362115342 20150606 11:56:35 1 1 NO TAG20150606T133114
10698 B F A 12723362115342 20150606 11:56:35 1 1 NO TAG20150606T125311
6 11198 B F A 12723362125653 20150606 13:38:23 1 1 NO TAG20150606T133821
7 11198 B F A 12723362125653 20150606 13:38:23 1 1 NO TAG20150606T133821
11104 B F A 12723362125558 20150606 13:37:27 1 1 NO TAG20150606T133725
8 11198 B F A 12723362125653 20150606 13:38:23 1 1 NO TAG20150606T133821
9 11198 B F A 12723362125653 20150606 13:38:23 1 1 NO TAG20150606T133821
10699 B F A 12723362120782 20150606 12:53:26 1 1 NO TAG20150606T125326

RMAN> list backup of datafile 5 summary;

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
10698 B F A DISK 20150606 12:53:12 1 1 NO TAG20150606T125311
10970 B F A DISK 20150606 13:31:16 1 1 NO TAG20150606T133114
11198 B F A DISK 20150606 13:38:51 1 1 NO TAG20150606T133821

为何report need backup还会提示datafile 5需要备份？

###datafile 5所在的TS0329是只读表空间
select ts.status,ts.tablespace_name,df.name,df.file# from dba_tablespaces ts,v$tablespace vts,v$datafile df where df.file#=5 and df.ts#=vts.ts# and vts.name=ts.tablespace_name;
STATUS TABLESPACE_NAME NAME FILE#
--------- ------------------------------ -------------------------------------------------- ----------------
READ ONLY TS0329 /oradata06/testaaaaa/ts0329_1.dbf 5

###datafile 5的RESETLOGS_CHANGE#比其它datafile都要小
col name format a50
set linesize 150 numwidth 16
select file#,name,checkpoint_change#,resetlogs_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
---------------- -------------------------------------------------- ------------------ -----------------
1 /oradata06/testaaaaa/system01.dbf 12723362125653 12723362115437
2 /oradata06/testaaaaa/sysaux01.dbf 12723362125653 12723362115437
3 /oradata06/testaaaaa/undotbs01.dbf 12723362125653 12723362115437
4 /oradata06/testaaaaa/users01.dbf 12723362125653 12723362115437
5 /oradata06/testaaaaa/ts0329_1.dbf 12723362115342 12723361984663
6 /oradata06/testaaaaa/xdbts1.dbf 12723362125653 12723362115437
7 /oradata06/testaaaaa/ts0212.dbf 12723362125653 12723362115437
8 /oradata06/testaaaaa/ts0212_1.dbf 12723362125653 12723362115437
9 /oradata06/testaaaaa/ts0422_1.dbf 12723362125653 12723362115437

select incarnation#,resetlogs_change#,status from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# STATUS
---------------- ----------------- -------
1 12723362115437 CURRENT

结合v$datafile_header、v$database_incarnation可以判断数据库经历过open resetlogs，且open resetlogs发生在datafile 5置为read only之后，因为是只读所以datafile 5的RESETLOGS_CHANGE#停留在了之前的scn=12723361984663上，没有随着open resetlogs更新为最新的SCN，通过v$database_incarnation发现当前控制文件对于RESETLOGS_CHANGE#=12723361984663所在的incarnation记录缺失，导致RMAN无法判断datafile 5所处的incarnation。那么又是什么原因引起控制文件没有记录下resetlogs之前的incarnation信息呢？重建控制文件！

下面我们把datafile 9所在的TS0422_1表空间置为read only重现一下这个现象
alter tablespace ts0422_1 read only;

col name format a50
set linesize 150 numwidth 16
select ts.status,ts.tablespace_name,df.name,df.file# from dba_tablespaces ts,v$tablespace vts,v$datafile df where df.file#=9 and df.ts#=vts.ts# and vts.name=ts.tablespace_name;

STATUS TABLESPACE_NAME NAME FILE#
--------- ------------------------------ -------------------------------------------------- ----------------
READ ONLY TS0422_1 /oradata06/testaaaaa/ts0422_1.dbf 9

###模拟incomplete recovery，open resetlogs打开数据库
shutdown immediate;
startup mount;
recover database until cancel;
alter database open resetlogs;

###v$database_incarnation对于上一个的incarnation是有一条完整的记录的
select incarnation#,resetlogs_change#,status from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# STATUS
---------------- ----------------- -------
1 12723362115437 PARENT
2 12723362128541 CURRENT

###datafile 9的resetlogs_change#停留在了12723362115437
col name format a50
set linesize 150 numwidth 16
select file#,name,checkpoint_change#,resetlogs_change# from v$datafile_header;
FILE# NAME CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
---------------- -------------------------------------------------- ------------------ -----------------
1 /oradata06/testaaaaa/system01.dbf 12723362128544 12723362128541
2 /oradata06/testaaaaa/sysaux01.dbf 12723362128544 12723362128541
3 /oradata06/testaaaaa/undotbs01.dbf 12723362128544 12723362128541
4 /oradata06/testaaaaa/users01.dbf 12723362128544 12723362128541
5 /oradata06/testaaaaa/ts0329_1.dbf 12723362115342 12723361984663
6 /oradata06/testaaaaa/xdbts1.dbf 12723362128544 12723362128541
7 /oradata06/testaaaaa/ts0212.dbf 12723362128544 12723362128541
8 /oradata06/testaaaaa/ts0212_1.dbf 12723362128544 12723362128541
9 /oradata06/testaaaaa/ts0422_1.dbf 12723362128288 12723362115437

###因为v$database_incarnation对于RESETLOGS_CHANGE#=12723362115437所在的incarnation有记录，所以report need backup输出里没有datafile 9，还是只输出datafile 5
RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
5 0 /oradata06/testaaaaa/ts0329_1.dbf

###重建控制文件
alter database backup controlfile to trace as '/tmp/ctl.0606' noresetlogs;

shutdown immediate;

@/tmp/ctl.0606

col name format a50
set linesize 150 numwidth 16
select file#,name,checkpoint_change#,resetlogs_change# from v$datafile_header;

FILE# NAME CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
---------------- -------------------------------------------------- ------------------ -----------------
1 /oradata06/testaaaaa/system01.dbf 12723362131452 12723362128541
2 /oradata06/testaaaaa/sysaux01.dbf 12723362131452 12723362128541
3 /oradata06/testaaaaa/undotbs01.dbf 12723362131452 12723362128541
4 /oradata06/testaaaaa/users01.dbf 12723362131452 12723362128541
5 /oradata06/testaaaaa/ts0329_1.dbf 12723362115342 12723361984663
6 /oradata06/testaaaaa/xdbts1.dbf 12723362131452 12723362128541
7 /oradata06/testaaaaa/ts0212.dbf 12723362131452 12723362128541
8 /oradata06/testaaaaa/ts0212_1.dbf 12723362131452 12723362128541
9 /oradata06/testaaaaa/ts0422_1.dbf 12723362128288 12723362115437

###控制文件里只有当前的resetlogs change#，之前的RESETLOGS_CHANGE#=12723362115437&RESETLOGS_CHANGE#=12723361984663记录丢失
set numwidth 16
select incarnation#,resetlogs_change#,status from v$database_incarnation;
INCARNATION# RESETLOGS_CHANGE# STATUS
---------------- ----------------- -------
1 12723362128541 CURRENT

###控制文件里的backup信息也全无，只能重新进行备份
RMAN> list backupset;

specification does not match any backup in the repository

RMAN> backup database;

Starting backup at 20150606 14:42:06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/oradata06/testaaaaa/undotbs01.dbf
input datafile file number=00009 name=/oradata06/testaaaaa/ts0422_1.dbf
input datafile file number=00001 name=/oradata06/testaaaaa/system01.dbf
input datafile file number=00004 name=/oradata06/testaaaaa/users01.dbf
input datafile file number=00002 name=/oradata06/testaaaaa/sysaux01.dbf
input datafile file number=00006 name=/oradata06/testaaaaa/xdbts1.dbf
input datafile file number=00005 name=/oradata06/testaaaaa/ts0329_1.dbf
input datafile file number=00007 name=/oradata06/testaaaaa/ts0212.dbf
input datafile file number=00008 name=/oradata06/testaaaaa/ts0212_1.dbf
channel ORA_DISK_1: starting piece 1 at 20150606 14:42:07
channel ORA_DISK_1: finished piece 1 at 20150606 14:42:42
piece handle=/oradata06/fra/TSTDB1/backupset/2015_06_06/o1_mf_nnndf_TAG20150606T144207_1k6BSr5PC_.bkp tag=TAG20150606T144207 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 20150606 14:42:44
channel ORA_DISK_1: finished piece 1 at 20150606 14:42:45
piece handle=/oradata06/fra/TSTDB1/backupset/2015_06_06/o1_mf_ncsnf_TAG20150606T144207_1k6BU_AsX_.bkp tag=TAG20150606T144207 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20150606 14:42:45

RMAN> list backup of datafile 5,9;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
1 Full 2.45G DISK 00:00:29 20150606 14:42:36
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20150606T144207
Piece Name: /oradata06/fra/TSTDB1/backupset/2015_06_06/o1_mf_nnndf_TAG20150606T144207_1k6BSr5PC_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ----------------- ----
5 Full 12723362115342 20150606 11:56:35 /oradata06/testaaaaa/ts0329_1.dbf
9 Full 12723362128288 20150606 14:15:42 /oradata06/testaaaaa/ts0422_1.dbf

###datafile 5、9出现在了report need backup输出里
RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
5 0 /oradata06/testaaaaa/ts0329_1.dbf
9 0 /oradata06/testaaaaa/ts0422_1.dbf

###解决方式是把datafile 5、datafile 9 先设为read write，再设回read only后进行备份
alter tablespace TS0329 read write;
alter tablespace TS0422_1 read write;
alter tablespace TS0329 read only;
alter tablespace TS0422_1 read only;

SYS@tstdb1-SQL> select file#,name,checkpoint_change#,resetlogs_change# from v$datafile_header;

FILE# NAME CHECKPOINT_CHANGE# RESETLOGS_CHANGE#
---------------- -------------------------------------------------- ------------------ -----------------
1 /oradata06/testaaaaa/system01.dbf 12723362131452 12723362128541
2 /oradata06/testaaaaa/sysaux01.dbf 12723362131452 12723362128541
3 /oradata06/testaaaaa/undotbs01.dbf 12723362131452 12723362128541
4 /oradata06/testaaaaa/users01.dbf 12723362131452 12723362128541
5 /oradata06/testaaaaa/ts0329_1.dbf 12723362134869 12723362128541
6 /oradata06/testaaaaa/xdbts1.dbf 12723362131452 12723362128541
7 /oradata06/testaaaaa/ts0212.dbf 12723362131452 12723362128541
8 /oradata06/testaaaaa/ts0212_1.dbf 12723362131452 12723362128541
9 /oradata06/testaaaaa/ts0422_1.dbf 12723362134884 12723362128541

RMAN> backup datafile 5,9;

Starting backup at 20150606 15:16:47
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/oradata06/testaaaaa/ts0422_1.dbf
input datafile file number=00005 name=/oradata06/testaaaaa/ts0329_1.dbf
channel ORA_DISK_1: starting piece 1 at 20150606 15:16:47
channel ORA_DISK_1: finished piece 1 at 20150606 15:16:54
piece handle=/oradata06/fra/TSTDB1/backupset/2015_06_06/o1_mf_nnndf_TAG20150606T151647_1k6DOnyF2_.bkp tag=TAG20150606T151647 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 20150606 15:16:54

RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------

注：此例中tstdb1库没有使用recovery catalog，控制文件重建后包括备份集,历史incarnation在内的所有信息都不复存在，如果使用了recovery catalog即使控制文件被重建了也不会出现这个问题。

ITPub博客

使用RMAN report need backup命令检查备份时遇到一个诡异的问题

最新文章