使用RMAN report need backup命令檢查備份時遇到一個詭異的問題
在檢查一個測試庫的備份時出現了這樣一個問題(該測試庫沒有使用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即使控制檔案被重建了也不會出現這個問題。
###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部落格 ” ，連結：http://blog.itpub.net/53956/viewspace-1689445/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Using RMAN REPORT NEED BACKUP with Different Retention Policies
- Backup And Recovery User's Guide-備份RMAN備份-使用RMAN備份備份集GUIIDE
- 一個看似詭異的Oracle連線問題Oracle
- Backup And Recovery User's Guide-RMAN備份概念-RMAN備份的多個拷貝-備份的備份GUIIDE
- Backup And Recovery User's Guide-RMAN備份概念-增量備份-差異增量備份GUIIDE
- netbakcup備份時遇到 status 6: the backup failed to back up the requested files 問題解決AI
- Oracle OCP 1Z0-053 Q510(RMAN REPORT NEED BACKUP)Oracle
- 一個詭異的MySQL查詢超時問題，居然隱藏著存在了兩年的BUGMySql
- Backup And Recovery User's Guide-RMAN備份概念-RMAN備份的多個拷貝GUIIDE
- oracle rman backup命令檢查資料庫錯誤Oracle資料庫
- 串列埠使用Pipeline時詭異的ReadOnlySequence問題串列埠
- linux下使用ls命令時遇到的一個怪異現象Linux
- Backup And Recovery User's Guide-備份RMAN備份-用RMAN備份映象拷貝備份GUIIDE
- Backup And Recovery User's Guide-備份RMAN備份-關於備份的備份GUIIDE
- Oracle Rman 命令詳解(List report backup configure)Oracle
- MySQL Backup--Xtrabackup備份限速問題MySql
- 同一欄位多個查詢條件時遇到的一個問題
- Oracle 11g RMAN備份-RMAN的backup database plus archivelog命令背後的操作OracleDatabaseHive
- 如何使用Hyper Backup備份資料至異地
- Backup And Recovery User's Guide-備份資料庫-RMAN備份概覽-RMAN備份的目的GUIIDE資料庫
- oracle rman備份命令Oracle
- 常用RMAN 備份命令
- 一個詭異的 Pulsar InterruptedException 異常Exception
- ASM+RMAN使用delete input備份日誌的問題ASMdelete
- 一次詭異的Oracle使用者無法su問題Oracle
- rman backup ENCRYPTION(備份加密特性)測試！加密
- oracle實驗記錄 （rman 備份檢查&preview）OracleView
- Backup And Recovery User's Guide-RMAN備份概念-RMAN何時執行控制檔案自動備份GUIIDE
- 一次詭異的MySQL問題處理故事MySql
- Backup And Recovery User's Guide-備份RMAN備份-備份保留期對備份的備份的影響GUIIDE
- Backup And Recovery User's Guide-備份RMAN備份-備份集的多重拷貝GUIIDE
- 詭異的”慢查詢“
- 介面詭異的404問題記錄
- 很詭異的博弈問題分析方法
- NBU report error 6 when RMAN backupError
- Backup And Recovery User's Guide-RMAN備份概念-增量備份GUIIDE
- Oracle 11g 檢查 RMAN 備份的有效性Oracle
- 聊一個複用元件中使用debounce時遇到的問題元件