RMAN故障一例(归档的备份,从不obsolete)

问题描述:

近期的rman备份中,归档日志的备份没有被删除,rman的脚本和策略都没变过,归档的备份一直保留,每过一段时间就要物理删除备份,很是奇怪。


rman的configure如下

RMAN> show all;

RMAN configuration parameters for database with db_unique_name HUBSRAC are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;

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 'MEDIUM' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 20 TIMES TO DISK;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+dg_redo/hubsrac/snapcf_hubsrac.f';



RMAN> corsscheck backup;

RMAN> report obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 2

Report of obsolete backups and copies

Type Key Completion Time Filename/Handle

-------------------- ------ ------------------ --------------------

Control File Copy 44 2016/05/31 14:14:26 +DG_REDO01/orclrac/control_snapshot/snapcf_orclrac1.f

发现obsolete的列表里,只有一个controlfilecopy,而且是三个月前的?推算一下,应该是这个库之前做standby的时候遗留下来的,尝试删除这个过期的备份


RMAN> delete obsolete;


RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 2

using channel ORA_DISK_1

Deleting the following obsolete backups and copies:

Type Key Completion Time Filename/Handle

-------------------- ------ ------------------ --------------------

Control File Copy 44 2016/05/31 14:14:26 +DG_REDO01/orclrac/control_snapshot/snapcf_orclrac1.f


Do you really want to delete the above objects (enter YES or NO)? YES

deleted control file copy

control file copy file name=+DG_REDO01/orclrac/control_snapshot/snapcf_orclrac1.f RECID=44 STAMP=913299266

Deleted 1 objects


注意已经提示Deleted 1 objects,但是在此report时会发现,这个记录仍然存在

RMAN> report obsolete;


RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 2

Report of obsolete backups and copies

Type Key Completion Time Filename/Handle

-------------------- ------ ------------------ --------------------

Control File Copy 44 2016/05/31 14:14:26 +DG_REDO01/orclrac/control_snapshot/snapcf_orclrac1.f



换一种方法删除

RMAN> CROSSCHECK CONTROLFILECOPY '+DG_REDO01/orclrac/control_snapshot/snapcf_orclrac1.f';


released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=2064 instance=orclrac2 device type=DISK

validation failed for control file copy

control file copy file name=+DG_REDO01/orclrac/control_snapshot/snapcf_orclrac1.f RECID=44 STAMP=913299266

Crosschecked 1 objects



RMAN> delete obsolete;


RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 2

using channel ORA_DISK_1

Deleting the following obsolete backups and copies:

Type Key Completion Time Filename/Handle

-------------------- ------ ------------------ --------------------

Control File Copy 44 2016/05/31 14:14:26 +DG_REDO01/orclrac/control_snapshot/snapcf_orclrac1.f


Do you really want to delete the above objects (enter YES or NO)? YES

deleted control file copy

control file copy file name=+DG_REDO01/orclrac/control_snapshot/snapcf_orclrac1.f RECID=44 STAMP=913299266

Deleted 1 objects


仍让存在。。。

RMAN> report obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 2

Report of obsolete backups and copies

Type Key Completion Time Filename/Handle

-------------------- ------ ------------------ --------------------

Control File Copy 44 2016/05/31 14:14:26 +DG_REDO01/orclrac/control_snapshot/snapcf_orclrac1.f



再换一种方法

RMAN> DELETE FORCE NOPROMPT OBSOLETE DEVICE TYPE DISK; 

If RMAN-06214 still occurs, then try


RMAN> CROSSCHECK COPY OF CONTROLFILE;


还是存在

RMAN> report obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 2

Report of obsolete backups and copies

Type Key Completion Time Filename/Handle

-------------------- ------ ------------------ --------------------

Control File Copy 44 2016/05/31 14:14:26 +DG_REDO01/orclrac/control_snapshot/snapcf_orclrac1.f


没办法了,uncatalog之后就没有了,官方上给出的方法:

RMAN> change controlfilecopy '+DG_REDO01/orclrac/control_snapshot/snapcf_orclrac1.f' uncatalog;


uncataloged control file copy

control file copy file name=+DG_REDO01/orclrac/control_snapshot/snapcf_orclrac1.f RECID=44 STAMP=913299266

Uncataloged 1 objects


过期的控制文件备份是干掉了,可是再次去检查obsolete,发现那些老的归档日志备份仍然不是obsolete~



查看一下capture,发现有oggcapture注册在数据库

SQL> col required_checkpoint_scn for 9999999999999999999

SQL> select capture_name, required_checkpoint_scn from dba_capture;


CAPTURE_NAME REQUIRED_CHECKPOINT_SCN

------------------------------ -----------------------

OGG$CAP_FM1 46264409580

OGG$CAP_ET1 46264410538


根源就在这里,这个库以前作为oggsource端使用过一段时间,后来业务变更,ogg就停掉了。但是配置ogg时注册进ogg的一些服务并没有清除,ogg 11g的 logretention和12cintegrated在日志没有被抽取的时候,会阻止rman中的归档备份被清除。



登入ogg把之前的两个抽取进程unregister一下

GGSCI (hubsrac01 as oggadmin@orclrac1) 7> dblogin userid oggadmin,password oggadmin

GGSCI (hubsrac01 as oggadmin@orclrac1) 7> unregister extract fm1 database

GGSCI (hubsrac01 as oggadmin@orclrac1) 7> unregister extract et1 database



清除过程中数据库的alert信息如下

GoldenGate Apply: OGG$FM1 APPLY Dropped

APPLY OGG$FM1: Apply User: OGGADMIN

APPLY OGG$FM1: Apply Tag: 00

Tue Aug 30 15:05:40 2016

Streams Capture: OGG$CAP_FM1 CAPTURE Dropped

CAPTURE OGG$CAP_FM1: Start SCN: 46167477979 (0xbfcbcedb.0000000a)

CAPTURE OGG$CAP_FM1: First SCN: 46167477979 (0xbfcbcedb.0000000a)

CAPTURE OGG$CAP_FM1: Required Checkpoint SCN: 46264409580 (0xc592ddec.0000000a)

CAPTURE OGG$CAP_FM1: Captured SCN: 46264409883 (0xc592df1b.0000000a)

CAPTURE OGG$CAP_FM1: Applied SCN: 46264409580 (0xc592ddec.0000000a)

CAPTURE OGG$CAP_FM1: Capture Type: LOCAL

CAPTURE OGG$CAP_FM1: Logminer Id: 4

CAPTURE OGG$CAP_FM1: Source Database: ORCLRAC

Tue Aug 30 15:05:53 2016

ALTER SYSTEM SET service_names='SYS$OGGADMIN.OGG$Q_ET1.ORCLRAC' SCOPE=MEMORY SID='orclrac2';

Tue Aug 30 15:06:31 2016

GoldenGate Apply: OGG$ET1 APPLY Dropped

APPLY OGG$ET1: Apply User: OGGADMIN

APPLY OGG$ET1: Apply Tag: 00

Tue Aug 30 15:06:32 2016

Streams Capture: OGG$CAP_ET1 CAPTURE Dropped

CAPTURE OGG$CAP_ET1: Start SCN: 46165434320 (0xbfac9fd0.0000000a)

CAPTURE OGG$CAP_ET1: First SCN: 46165434320 (0xbfac9fd0.0000000a)

CAPTURE OGG$CAP_ET1: Required Checkpoint SCN: 46264410538 (0xc592e1aa.0000000a)

CAPTURE OGG$CAP_ET1: Captured SCN: 46264410853 (0xc592e2e5.0000000a)

CAPTURE OGG$CAP_ET1: Applied SCN: 46264410538 (0xc592e1aa.0000000a)

CAPTURE OGG$CAP_ET1: Capture Type: LOCAL

CAPTURE OGG$CAP_ET1: Logminer Id: 2

CAPTURE OGG$CAP_ET1: Source Database: ORCLRAC

Tue Aug 30 15:06:42 2016

ALTER SYSTEM SET service_names='hubsrac' SCOPE=MEMORY SID='orclrac2';


再次检查obsolete

RMAN> report obsolete;


RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 2

Report of obsolete backups and copies

Type Key Completion Time Filename/Handle

-------------------- ------ ------------------ --------------------

Backup Set 326851 2016/08/28 00:01:42

Backup Piece 326851 2016/08/28 00:01:42 /mnt/orclbackup/log_20160828_328204_1

Backup Set 326855 2016/08/28 01:01:55

Backup Piece 326855 2016/08/28 01:01:55 /mnt/orclbackup/log_20160828_328208_1

Backup Set 326859 2016/08/28 02:01:54

Backup Piece 326859 2016/08/28 02:01:54 /mnt/orclbackup/log_20160828_328212_1

Backup Set 326872 2016/08/28 03:01:44

Backup Piece 326872 2016/08/28 03:01:44 /mnt/orclbackup/log_20160828_328227_1

Backup Set 326880 2016/08/28 04:01:33

Backup Piece 326880 2016/08/28 04:01:33 /mnt/orclbackup/log_20160828_328234_1

Backup Set 326884 2016/08/28 05:01:35

Backup Piece 326884 2016/08/28 05:01:35 /mnt/orclbackup/log_20160828_328238_1

Backup Set 326888 2016/08/28 06:02:08

Backup Piece 326888 2016/08/28 06:02:08 /mnt/orclbackup/log_20160828_328242_1

Backup Set 326892 2016/08/28 07:01:20

Backup Piece 326892 2016/08/28 07:01:20 /mnt/orclbackup/log_20160828_328246_1

Backup Set 326893 2016/08/28 07:02:27

Backup Piece 326893 2016/08/28 07:02:27 /mnt/orclbackup/log_20160828_328247_1

Backup Set 326897 2016/08/28 08:01:17

Backup Piece 326897 2016/08/28 08:01:17 /mnt/orclbackup/log_20160828_328251_1

Backup Set 326898 2016/08/28 08:02:24

Backup Piece 326898 2016/08/28 08:02:24 /mnt/orclbackup/log_20160828_328252_1

Backup Set 326902 2016/08/28 09:01:14

Backup Piece 326902 2016/08/28 09:01:14 /mnt/orclbackup/log_20160828_328256_1

Backup Set 326903 2016/08/28 09:02:19

Backup Piece 326903 2016/08/28 09:02:19 /mnt/orclbackup/log_20160828_328257_1

Backup Set 326907 2016/08/28 10:01:17

Backup Piece 326907 2016/08/28 10:01:17 /mnt/orclbackup/log_20160828_328261_1

Backup Set 326908 2016/08/28 10:02:24

Backup Piece 326908 2016/08/28 10:02:24 /mnt/orclbackup/log_20160828_328262_1

Backup Set 326912 2016/08/28 11:02:08

Backup Piece 326912 2016/08/28 11:02:08 /mnt/orclbackup/log_20160828_328266_1

Backup Set 326916 2016/08/28 12:02:13

Backup Piece 326916 2016/08/28 12:02:13 /mnt/orclbackup/log_20160828_328270_1

Backup Set 326920 2016/08/28 13:02:23

Backup Piece 326920 2016/08/28 13:02:23 /mnt/orclbackup/log_20160828_328274_1

Backup Set 326924 2016/08/28 14:01:26

Backup Piece 326924 2016/08/28 14:01:26 /mnt/orclbackup/log_20160828_328278_1

Backup Set 326925 2016/08/28 14:02:41

删除即可

RMAN> delete obsolete;

 

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。
一 前言  纳西姆.尼古拉斯.塔勒布的经典著作《黑天鹅》中对“黑天鹅现象”的定义是 - 不可预测,人们事前往往低估其发生的可能性 - 造成极大影响 - 事后回头再看,又觉得此事发生的有理 二 分析   稳定性 是一项衡量基础系统是否永续服务的绝对指标,作为资深DBA从业人员,相信大多数公司运维团队都会制定稳定性的SLA指标达到N个9,为用户提供Full-Time 服务。然而前一段时间各种"黑天鹅”式的因素导致一系列的系统故障,严重影响了C端B端的用户的使用体验。 故 障 是数据库系统或者说业务系统的“脆弱
最近有个同事碰到一个问题,想让我给点思路。我大体了解了一下,是一个系统目前在做压力测试,但是经业务反馈发现某个环节的处理时间有些长,排查了一圈,最后这件事情就落在了DB这边,希望DB能够给点意见,是否存在一些性能瓶颈。     我们从开发同学那里得到的一个基本的SQL语句,根据关键字从v$sql中做了提取,发现对应的SQL语句的执行时间还是OK的。 得到的SQL语句如下: SQL_ID        SQL_FULLTEXT ------------- ---------------------------

Oracle之不可见索引 - 2016-08-27 17:08:09

Oracle 之不可见索引 1    BLOG 文档结构图   2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ①  Oracle 不可见索引的使用   Tips: ① 本文在 ITpub ( http://blog.itpub.net/26736162 )、博客园 ( http://www.cnblogs.com/lhrbest )和微信公众号( xiaomaimiaolhr )有同步更新
【故障处理】序列 cache 值过小导致 CPU 利用率过高 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ① enq: SQ - contention 等待事件的解决 ② 一般等待事件的解决办法 ③  DFS lock handle 等待事件 ④ 与序列有关的等待事件   Tips: ① 本文在 ITpub ( http://blog.itpub.
这两天做性能测试碰见一个问题,比较有意思。 一条SQL,使用了绑定变量,查看V$SQLAREA发现version_count是2,  查看V$SQL,发现有两条记录,分别对应了0和1两个child cursor:  再查看这两个child cursor对应的执行计划:  child cursor:0 -----------------------------------------------------------------------------------------------------| I

OCP如何查看历史成绩(2) - 2016-08-25 14:08:08

OCP如何查看历史成绩( 2 ) 之前写过一篇文章 OCP如何查看历史成绩: http://blog.itpub.net/26736162/viewspace-1624996/  , 但是现在界面好像有所变动,而且有的连接也找不见了,今天有朋友问如何查询历史的成绩,晚上小麦苗就抽了点时间来找找,顺便记录下来。最近心情不好,不想写技术文章了。       首先登陆网址: https://education.oracle.com/ https://education.oracle.com/pls/eval-e
下午的时候收到这么一条报警。 ZABBIX- 监控系统 : ------------------------------------ 报警内容 : Too many parallel sessions on xxxxx_ xx机房 _xxxxx ------------------------------------ 报警级别 : PROBLEM ------------------------------------ 监控项目 : parallel_session_cnt : 66 -----------

【MySQL】Tokudb安装测试初探 - 2016-08-24 14:08:02

一 前言    TokuDB 是一个高性能、支持MVCC的MySQL 和 MariaDB 的存储引擎。TokuDB 的主要特点是数据压缩功能出色,对高写压力的支持,由美国TokuTek公司(http://www.tokutek.com/) 研发,该公司于2015年4月份被Percona收购,理所当然地提供了TokuDB版本的Percona Server。本文使用Peronca server 5.6.30 版本进行测试安装。 二 安装前的准备     请参考官方文档 Percona Server YUM源

通过shell脚本添加备库日志 - 2016-08-21 17:08:14

今天下午的时候,准备顺手写一个简单的脚本,但是发现很多事情较真起来真是寸步难行。在写脚本的过程中碰到了太多的问题,很多时候感觉像要实现的功能更通用,就得做更多的检查,更多的校验也就意味着有更多的预先条件,这些条件里面有些是规范和建议,有些是按照已有的配置情况,尽管如此,自己感觉还是缺少了太多的检查。   先来说说今天尝试的简单脚本,就是给主库添加standby logfile,这个需求听起来非常简单,都甚至在我的半自动化脚本中隐去了,但是把这个需求要落到纸面上来,简直了。 首先这个需求会涉及到下面的几个数据
今天在测试时遇到一个ORA-38706ORA-38707报错,乍一看到报错内容竟然没有回过神儿来。 ORA-38706ORA-38707报错 点击( 此处 )折叠或打开 SYS @ HOEGH select banner from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition R