report obsolete not found real expire archivelog

兜兜

SQL> select * from v$version;
BANNER
——————————————————————————--
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bi
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production
SQL> show parameter control_file_record_keep_time
NAME TYPE VALUE
———————————— ———————- ——————————
control_file_record_keep_time integer 10
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
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 ARCHIVELOG DELETION POLICY TO NONE; # default
问题1：
蓝色部分的归档日志备份集，竟然没有被删除？正常情况下2次全备之前的，超过恢复窗口的是应该被删除的？
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
——- -- -- – ———-- ————— ——- ——- ———- —
10291 B A A DISK 24-SEP-13 1 1 NO TAG20130924T114933
10292 B A A DISK 24-SEP-13 1 1 NO TAG20130924T114933
10293 B A A DISK 24-SEP-13 1 1 NO TAG20130924T114933
10294 B A A DISK 24-SEP-13 1 1 NO TAG20130924T114933
......
10532 B 0 A DISK 29-SEP-13 1 1 YES TAG20130929T040003
10533 B 0 A DISK 29-SEP-13 1 1 YES TAG20130929T040003
10534 B 0 A DISK 29-SEP-13 1 1 YES TAG20130929T040003
10535 B 0 A DISK 29-SEP-13 1 1 YES TAG20130929T040003
10538 B 0 A DISK 29-SEP-13 1 1 YES TAG20130929T040003
10539 B 0 A DISK 29-SEP-13 1 1 YES TAG20130929T040003
...
10624 B 0 A DISK 06-OCT-13 1 1 YES TAG20131006T040003
10625 B 0 A DISK 06-OCT-13 1 1 YES TAG20131006T040003
10626 B 0 A DISK 06-OCT-13 1 1 YES TAG20131006T040003
10627 B 0 A DISK 06-OCT-13 1 1 YES TAG20131006T040003
10628 B 0 A DISK 06-OCT-13 1 1 YES TAG20131006T040003
10630 B 0 A DISK 06-OCT-13 1 1 YES TAG20131006T040003
10631 B 0 A DISK 06-OCT-13 1 1 YES TAG20131006T040003
...


RMAN> report obsolete;
也发现不了过期的归档日志备份集和归档日志

问题2：
怎么能正常的删除过期的归档日志和归档备份集呢，RMAN又是根据什么来判断归档日志和归档备份集是过期的呢？

Maclean Liu(刘相兵

收集以下 信息 并上传为附件

spool log to rman_report.log
set echo on
show all;
report schema;
list incarnation;
list backup summary;
list backup;
list copy;
report need backup;
report obsolete;
restore database preview;
spool log off

兜兜

刘大，信息收集压缩包传上

zhuang3088

关注下，顶

兜兜

刘大，请以此附件信息为准

ziyoo0830

关注一下。

Maclean Liu(刘相兵

select recid from v$backup_set where keep_until is not null;

并给出alert.log

兜兜

SQL> select recid from v$backup_set where keep_until is not null;

no rows selected

Maclean Liu(刘相兵

Errors in file /u01/oracle/admin/research/bdump/research_arc0_29503.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
PING[ARC0]: Heartbeat failed to connect to standby 'orcl'. Error is 12154.
Sun Sep 29 19:36:28 2013
ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;

似乎是有dg  且日志传输一直没成功

兜兜

恩是的。现在这个DG已经废除了，这个会影响RMAN标识过期的归档备份集么？

Maclean Liu(刘相兵

如果已经废除 那么清理干净参数， 并后续观察该问题

Maclean Liu(刘相兵

本来以为可能是keep until 导致了该问题

RMAN archivelog backups are not becoming obsoleted (Doc ID 986382.1)        To BottomTo Bottom       
Applies to:
Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.
Symptoms

Assumptions: this is a simple database configuration. There is no standby, no flashback logs, no streams. All files are online and not in need of backups.

RMAN does not appear to consider regular archivelog backups as obsolete even though the retention period has passed there are full backups taken subsequently.

Consider the following backups:
sql 'alter system switch logfile';

run {
allocate channel d1 device type disk maxopenfiles 1 rate 5m;

  backup full  tag 'LB_KEEP1DAY' format '/tmp/full_1day_%U'
     keep until time 'trunc(sysdate)+1' logs
     (database skip offline include current controlfile);

  backup tag 'LB_ARC' format '/tmp/arch_%U'
     archivelog all
     delete input;
}




The backups taken over time:

[oracle@osbr-linux testcase]$ rman target / catalog rman/rman@Rcat

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Feb 24 16:01:19 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA112 (DBID=232055638)
connected to recovery catalog database

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time    #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------ ------- ------- ---------- ---
48      B  F  A DISK        02 FEB 10 15:30:40 1       1       NO         LB_KEEP1DAY
72      B  F  A DISK        02 FEB 10 15:44:31 1       1       NO         LB_KEEP1DAY
73      B  F  A DISK        02 FEB 10 15:44:36 1       1       NO         LB_KEEP1DAY
98      B  A  A DISK        02 FEB 10 15:44:44 1       1       NO         LB_ARC
133     B  F  A DISK        03 FEB 10 08:14:45 1       1       NO         LB_KEEP1DAY
134     B  F  A DISK        03 FEB 10 08:14:49 1       1       NO         LB_KEEP1DAY
159     B  A  A DISK        03 FEB 10 08:15:12 1       1       NO         LB_ARC
775     B  F  A DISK        12 FEB 10 15:49:49 1       1       NO         LB_KEEP1DAY
776     B  F  A DISK        12 FEB 10 15:49:55 1       1       NO         LB_KEEP1DAY
800     B  A  A DISK        12 FEB 10 15:51:13 1       1       NO         LB_ARC
1265    B  F  A DISK        24 FEB 10 15:55:02 1       1       NO         LB_NOKEEP
1266    B  F  A DISK        24 FEB 10 15:55:08 1       1       NO         LB_NOKEEP
1302    B  A  A DISK        24 FEB 10 15:59:26 1       1       NO         LB_ARC



Note: FULL backupsets 1265 and 1266 were taken with the option of keep until time 'trunc(sysdate)+1'



On the 24th of February, only the backups with KEEP options are reported as obsolete as they have past their KEEP period. The archivelog backups are NOT listed as obsolete.

[oracle@osbr-linux testcase]$ rman target / catalog rman/rman@Rcat

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Feb 24 16:02:34 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA112 (DBID=232055638)
connected to recovery catalog database

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 3 days
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           48     02 FEB 10 15:30:40
  Backup Piece       49     02 FEB 10 15:30:40 /tmp/full_1day_02l50vgu_1_1
Backup Set           72     02 FEB 10 15:44:31
  Backup Piece       76     02 FEB 10 15:44:31 /tmp/full_1day_03l5101f_1_1
Backup Set           73     02 FEB 10 15:44:36
  Backup Piece       77     02 FEB 10 15:44:36 /tmp/full_1day_04l510b1_1_1
Backup Set           133    03 FEB 10 08:14:45
  Backup Piece       137    03 FEB 10 08:14:45 /tmp/full_1day_06l52q25_1_1
Backup Set           134    03 FEB 10 08:14:49
  Backup Piece       138    03 FEB 10 08:14:49 /tmp/full_1day_07l52qbn_1_1
Backup Set           775    12 FEB 10 15:49:49
  Backup Piece       777    12 FEB 10 15:49:49 /tmp/full_1day_0ol5rbup_1_1
Backup Set           776    12 FEB 10 15:49:55
  Backup Piece       778    12 FEB 10 15:49:55 /tmp/full_1day_0pl5rcd1_1_1




Changes

Full database backups are taken with the KEEP option.
Cause

The KEEP option specifies that the backup should be exempt from the retention policy either forever or for a specified period of time. The general name for a backup created with BACKUP ... KEEP is an archival backup.

Bug 10152820 - closed as not a bug:

KEEP backups in 11g no long play in the same sandbox as other DR backups. The feature was expanded in 11g to provide an archival backup mechanism that
would not disturb nightly backup activity.  In general a KEEP backup only
knows about other KEEP backups with the same TAG.

Regular archivelog backups will only become obsolete if there is a full or level 0 backup taken, and these backups fall outside the retention policy.
Solution

In order for regular archivelog backups to become obsolete, we must also take full non-archival backups.  That is, a FULL or level 0 backup WITHOUT the KEEP option.


On Mar 1, the archivelog backups have finally become obsolete:

[oracle@osbr-linux testcase]$ rman target / catalog rman/rman@rcat

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Mar 1 10:30:49 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA112 (DBID=232055638)
connected to recovery catalog database

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time    #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------ ------- ------- ---------- ---
48      B  F  A DISK        02 FEB 10 15:30:40 1       1       NO         LB_KEEP1DAY
72      B  F  A DISK        02 FEB 10 15:44:31 1       1       NO         LB_KEEP1DAY
73      B  F  A DISK        02 FEB 10 15:44:36 1       1       NO         LB_KEEP1DAY
98      B  A  A DISK        02 FEB 10 15:44:44 1       1       NO         LB_ARC
133     B  F  A DISK        03 FEB 10 08:14:45 1       1       NO         LB_KEEP1DAY
134     B  F  A DISK        03 FEB 10 08:14:49 1       1       NO         LB_KEEP1DAY
159     B  A  A DISK        03 FEB 10 08:15:12 1       1       NO         LB_ARC
775     B  F  A DISK        12 FEB 10 15:49:49 1       1       NO         LB_KEEP1DAY
776     B  F  A DISK        12 FEB 10 15:49:55 1       1       NO         LB_KEEP1DAY
800     B  A  A DISK        12 FEB 10 15:51:13 1       1       NO         LB_ARC
1265    B  F  A DISK        24 FEB 10 15:55:02 1       1       NO         LB_NOKEEP
1266    B  F  A DISK        24 FEB 10 15:55:08 1       1       NO         LB_NOKEEP
1302    B  A  A DISK        24 FEB 10 15:59:26 1       1       NO         LB_ARC

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 3 days
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           48     02 FEB 10 15:30:40
  Backup Piece       49     02 FEB 10 15:30:40 /tmp/full_1day_02l50vgu_1_1
Backup Set           72     02 FEB 10 15:44:31
  Backup Piece       76     02 FEB 10 15:44:31 /tmp/full_1day_03l5101f_1_1
Backup Set           73     02 FEB 10 15:44:36
  Backup Piece       77     02 FEB 10 15:44:36 /tmp/full_1day_04l510b1_1_1
Backup Set           98     02 FEB 10 15:44:44
  Backup Piece       101    02 FEB 10 15:44:44 /tmp/arch_05l510bb_1_1
Backup Set           133    03 FEB 10 08:14:45
  Backup Piece       137    03 FEB 10 08:14:45 /tmp/full_1day_06l52q25_1_1
Backup Set           134    03 FEB 10 08:14:49
  Backup Piece       138    03 FEB 10 08:14:49 /tmp/full_1day_07l52qbn_1_1
Backup Set           159    03 FEB 10 08:15:12
  Backup Piece       162    03 FEB 10 08:15:12 /tmp/arch_08l52qc2_1_1
Backup Set           775    12 FEB 10 15:49:49
  Backup Piece       777    12 FEB 10 15:49:49 /tmp/full_1day_0ol5rbup_1_1
Backup Set           776    12 FEB 10 15:49:55
  Backup Piece       778    12 FEB 10 15:49:55 /tmp/full_1day_0pl5rcd1_1_1
Backup Set           800    12 FEB 10 15:51:13
  Backup Piece       803    12 FEB 10 15:51:13 /tmp/arch_0ql5rcdj_1_1










MORE INFO:Bug 7828095: OBSOLETE BACKUPSET ARE NOT DELETED FROM THE FRA AUTOMATICALLY

Maclean Liu(刘相兵

再查一下：
select capture_name, required_checkpoint_scn from dba_capture;

兜兜

Maclean Liu(刘相兵 发表于 2013-10-9 16:35
如果已经废除 那么清理干净参数， 并后续观察该问题

好的，马上清理干净参数

兜兜

Maclean Liu(刘相兵 发表于 2013-10-9 16:37
再查一下：
select capture_name, required_checkpoint_scn from dba_capture;

SQL> select capture_name, required_checkpoint_scn from dba_capture;

no rows selected

Maclean Liu(刘相兵

        Report/Delete obsolete not reporting/deleting obsolete backups of Archivelog Files (Doc ID 1553261.1)

Report/Delete obsolete not reporting/deleting obsolete backups of Archivelog Files (Doc ID 1553261.1)

Reporte Obsolete Doesnot Report Archivelog Backup (Doc ID 803635.1)

兜兜

Maclean Liu(刘相兵 发表于 2013-10-9 16:49
Report/Delete obsolete not reporting/deleting obsolete backups of Archivelog Files (Doc ID 1553261. ...

谢谢刘大，我去研究一下

兜兜

结贴！把DG残留的参数去掉后，重新注册备份集
catalog start with '/rman/';
report obsolete;   有结果了，删除也正常了！

但是还没有知道根本原因是什么？刘大可以指点一下，是哪些DG的参数导致的这个问题么？

dba_sky

关注一下。