ORA-19693: backup piece %s already included

Posted on 2011 年 11 月 13 日 by 惜分飞

一、问题现象

RMAN> restore database;

启动 restore 于 13-11月-11
使用通道 ORA_DISK_1

通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 将数据文件 00001 还原到 E:\ORACLE\ORADATA\XFF\SYSTEM01.DBF
通道 ORA_DISK_1: 将数据文件 00002 还原到 E:\ORACLE\ORADATA\XFF\SYSAUX01.DBF
通道 ORA_DISK_1: 将数据文件 00003 还原到 E:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF
通道 ORA_DISK_1: 将数据文件 00004 还原到 E:\ORACLE\ORADATA\XFF\USERS01.DBF
通道 ORA_DISK_1: 将数据文件 00005 还原到 E:\ORACLE\ORADATA\XFF\EXAMPLE01.DBF
通道 ORA_DISK_1: 将数据文件 00006 还原到 E:\ORACLE\ORADATA\XFF\O_ORACLE.DBF
通道 ORA_DISK_1: 将数据文件 00007 还原到 E:\ORACLE\ORADATA\XFF\XIFENFEI01.DBF
通道 ORA_DISK_1: 将数据文件 00008 还原到 E:\ORACLE\ORADATA\XFF\P_TEST01.DBF
通道 ORA_DISK_1: 将数据文件 00009 还原到 E:\ORACLE\ORADATA\XFF\SYS_MG01.DBF
通道 ORA_DISK_1: 将数据文件 00010 还原到 E:\ORACLE\ORADATA\XFF\P101.DBF
通道 ORA_DISK_1: 将数据文件 00011 还原到 E:\ORACLE\ORADATA\XFF\P201.DBF
通道 ORA_DISK_1: 将数据文件 00012 还原到 E:\ORACLE\ORADATA\XFF\P301.DBF
通道 ORA_DISK_1: 将数据文件 00015 还原到 E:\ORACLE\ORADATA\XFF\OGG01.DBF
通道 ORA_DISK_1: 将数据文件 00016 还原到 E:\ORACLE\ORADATA\XFF\SPOT01.DBF
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: restore 命令 (在 11/13/2011 18:19:31 上) 失败
ORA-19693: 已包括备份片段 F:\RMANBACKUP\9_12_0GMMD2KI_1_1

RMAN> list backup summary;


备份列表
===============
关键字     TY LV S 设备类型 完成时间   段数 副本数 压缩标记
------- -- -- - ----------- ---------- ------- ------- ---------- ---
6       B  F  A DISK        12-9月 -11 1       2       NO         TAG20110912T215425
7       B  F  A DISK        12-9月 -11 1       1       NO         TAG20110912T215425
8       B  F  A DISK        12-9月 -11 1       1       NO         TAG20110912T220120

19693, 00000, "backup piece %s already included"
// *Cause:  This backup piece was already specified for inclusion in the
//          restore conversation. A restore conversation may process only
//          a single instance of a backup piece.
// *Action: Remove the specified duplicate backup piece in restore steps
//          and restart the conversation.

二、解决办法
1、查找出副本数大于1的备份集
2、重命名对应的备份集
3、使用crosscheck backup检测出无效的备份集
4、使用delete noprompt expired backup删除无效备份集
5、使用CATALOG START WITH重新添加刚刚重命名的备份集

三、问题原因
Bug 4483368: SEVERAL RMAN CATALOG START WITH CREATES DUPLICATE CATALOG ENTRIES

After several "RMAN  catalog start with "we have several copies of the same backuppiece 
in our catalog which we can see with "list backup;" and also "list backup summary;".
However, when I now try to restore with that controlfile instead of a catalog 
I receive  ORA-19693 backup piece "/opt/oracle/admin/mnt/HERMES2/bkp/..." 
is already included.

使用flashback database找回被误删除表空间

Posted on 2011 年 11 月 12 日 by 惜分飞

我个人不怎么推荐使用flashback database功能，因为在生产库中，谁能够承受得起整个库的回退(也许特别极端的情况下可能需要使用到)，今天帮网友恢复了一个案例:删除表空间，然后尝试着flashback database功能把这个删除的表空间找回来，但是他在整个操作过程中思路比较混乱，最后导致数据库不能正常起来。因为网友有这个方面的需求，我做了一个flashback database 找回表空间操作的试验(官方建议:flashback database在不改变数据文件的情况下使用)

1、恢复过程

SQL> select * from v$version;

BANNER
-----------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> create tablespace xff_test datafile 'E:\ORACLE\ORADATA\XFF\xff_test01.dbf'
size 10m autoextend on next 1m maxsize 20m;

表空间已创建。

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2011-11-12 21:05:07

SQL> create table chf.t_flashback tablespace xff_test
  2  as
  3  select * from dba_objects;

表已创建。

SQL> select count(*) from chf.t_flashback;

  COUNT(*)
----------
     73211

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2011-11-12 21:06:35

SQL> host dir E:\ORACLE\ORADATA\XFF\xff_test01.dbf
 驱动器 E 中的卷没有标签。
 卷的序列号是 38D0-2A35

 E:\ORACLE\ORADATA\XFF 的目录

2011/11/12  21:04        10,493,952 XFF_TEST01.DBF
               1 个文件     10,493,952 字节
               0 个目录 14,644,822,016 可用字节

SQL> drop tablespace xff_test including contents and datafiles;

表空间已删除。

SQL> host dir E:\ORACLE\ORADATA\XFF\xff_test01.dbf
 驱动器 E 中的卷没有标签。
 卷的序列号是 38D0-2A35

 E:\ORACLE\ORADATA\XFF 的目录

找不到文件


SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area  368263168 bytes
Fixed Size                  1374668 bytes
Variable Size             276825652 bytes
Database Buffers           83886080 bytes
Redo Buffers                6176768 bytes
数据库装载完毕。
SQL> flashback database to timestamp 
2  to_timestamp('2011-11-12 21:06:35','yyyy-mm-dd hh24:mi:ss');

闪回完成。


SQL> alter database open resetlogs;

数据库已更改。

SQL> select name from v$datafile;

NAME
-------------------------------------------------------------
E:\ORACLE\ORADATA\XFF\SYSTEM01.DBF
E:\ORACLE\ORADATA\XFF\SYSAUX01.DBF
E:\ORACLE\ORADATA\XFF\UNDOTBS01.DBF
E:\ORACLE\ORADATA\XFF\USERS01.DBF
E:\ORACLE\ORADATA\XFF\EXAMPLE01.DBF
E:\ORACLE\ORADATA\XFF\O_ORACLE.DBF
E:\ORACLE\ORADATA\XFF\XIFENFEI01.DBF
E:\ORACLE\ORADATA\XFF\P_TEST01.DBF
E:\ORACLE\ORADATA\XFF\SYS_MG01.DBF
E:\ORACLE\ORADATA\XFF\P101.DBF
E:\ORACLE\ORADATA\XFF\P201.DBF

NAME
---------------------------------------------------------------

E:\ORACLE\ORADATA\XFF\P301.DBF
E:\ORACLE\11_2_0\DATABASE\UNNAMED00013
E:\ORACLE\ORADATA\XFF\OGG01.DBF
E:\ORACLE\ORADATA\XFF\SPOT01.DBF

已选择15行。

SQL> desc chf.t_flashback;
ERROR:
ORA-04043: 对象 chf.t_flashback 不存在

SQL> COL ERROR FOR A20
SQL> SELECT FILE#,ONLINE_STATUS,ERROR FROM V$RECOVER_FILE;

     FILE# ONLINE_ ERROR
---------- ------- --------------------
        13 OFFLINE FILE NOT FOUND

SQL> SELECT NAME FROM V$DATAFILE WHERE FILE#=13;

NAME
-----------------------------------------------------------------

E:\ORACLE\11_2_0\DATABASE\UNNAMED00013

SQL> HOST DIR E:\ORACLE\11_2_0\DATABASE\UNNAMED00013
 驱动器 E 中的卷没有标签。
 卷的序列号是 38D0-2A35

 E:\ORACLE\11_2_0\DATABASE 的目录

找不到文件

SQL> ALTER DATABASE CREATE DATAFILE 13 
AS 'E:\ORACLE\ORADATA\XFF\xff_test01.dbf';

数据库已更改。

SQL> HOST DIR E:\ORACLE\ORADATA\XFF\xff_test01.dbf
 驱动器 E 中的卷没有标签。
 卷的序列号是 38D0-2A35

 E:\ORACLE\ORADATA\XFF 的目录

2011/11/12  21:25        10,493,952 XFF_TEST01.DBF
               1 个文件     10,493,952 字节
               0 个目录 14,640,848,896 可用字节

SQL> recover datafile 13;
ORA-00279: 更改 10903431152368 (在 11/12/2011 21:04:40 生成) 对于线程 1
是必需的
ORA-00289: 建议: E:\ORACLE\ARCHIVELOG\ARC0000000241_0753489409.0001
ORA-00280: 更改 10903431152368 (用于线程 1) 在序列 #241 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
auto
已应用的日志。
完成介质恢复。

SQL> alter database datafile 13 online;

数据库已更改。

SQL> select count(*) from chf.t_flashback;

  COUNT(*)
----------
     73211

2、alert中关键信息

Sat Nov 12 21:12:30 2011
flashback database to timestamp to_timestamp('2011-11-12 21:06:35','yyyy-mm-dd hh24:mi:ss')
Flashback Restore Start
Flashback: created tablespace #18: 'XFF_TEST' in the controlfile.
Flashback: created OFFLINE file 'UNNAMED00013' for tablespace #18 in the controlfile.
Filename was:
'E:\ORACLE\ORADATA\XFF\XFF_TEST01.DBF' when dropped.
File will have to be restored from a backup and recovered.
Flashback: deleted datafile #13 in tablespace #18 from control file.
Flashback: dropped tablespace #18: 'XFF_TEST' from the control file.
Flashback Restore Complete
Flashback Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 2 slaves
Sat Nov 12 21:12:38 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 241 Reading mem 0
  Mem# 0: E:\ORACLE\ORADATA\XFF\REDO01
Flashback recovery: Added file #13 to control file as OFFLINE and 'UNNAMED00013'
because it was dropped during the flashback interval
or it was added during flashback media recovery.
File was originally created as:
'E:\ORACLE\ORADATA\XFF\XFF_TEST01.DBF'
File will have to be restored from a backup or
recreated using ALTER DATABASE CREATE DATAFILE command,
and the file has to be onlined and recovered.
Incomplete Recovery applied until change 10903431152644 time 11/12/2011 21:05:11
Sat Nov 12 21:12:44 2011
Flashback Media Recovery Complete
Completed: flashback database to timestamp to_timestamp('2011-11-12 21:06:35','yyyy-mm-dd hh24:mi:ss')
--flashback database 操作过程
……
Sat Nov 12 21:25:29 2011
ALTER DATABASE CREATE DATAFILE 13 AS 'E:\ORACLE\ORADATA\XFF\xff_test01.dbf'
Completed: ALTER DATABASE CREATE DATAFILE 13 AS 'E:\ORACLE\ORADATA\XFF\xff_test01.dbf'
Sat Nov 12 21:25:59 2011
ALTER DATABASE RECOVER  datafile 13  
Media Recovery Start
Serial Media Recovery started
Media Recovery start incarnation depth : 1, target inc# : 3, irscn : 10903431152644
ORA-279 signalled during: ALTER DATABASE RECOVER  datafile 13  ...
Sat Nov 12 21:28:27 2011
ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Media Recovery Log E:\ORACLE\ARCHIVELOG\ARC0000000241_0753489409.0001
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: E:\ORACLE\ORADATA\XFF\REDO01
Completed: ALTER DATABASE RECOVER    CONTINUE DEFAULT  
Sat Nov 12 21:29:04 2011
alter database datafile 13 online
Completed: alter database datafile 13 online
--恢复被删除数据文件过程,flashbackup database会创建表空间，但是数据文件需要人工干预

议rman的crosscheck和obsolete

Posted on 2011 年 11 月 08 日 by 惜分飞

今天有朋友对于crosscheck和obsolete理解的不太清楚，网上查找了一些资料，也发现很多错误，其中典型的理解就是:crosscheck 可以检测/删除违背(obsolete)备份策略的备份集

--登录rman
[oracle@node1 ~]$ $ORACLE_HOME/bin/rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Nov 8 13:39:17 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ECP (DBID=1669273445)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2048 M;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/10.2.0/db_1/dbs/snapcf_ecp.f'; # default
--发现配置的策略是保留1份

--全库备份
RMAN> list backup summary;
--当前数据库无备份集备份

RMAN> backup database format '/opt/backup/ecp_full_%U';

Starting backup at 2011-11-08 13:22:06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=536 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=534 devtype=DISK
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/opt/oracle/oradata/ecp/users01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/ecp/sysaux01.dbf
input datafile fno=00008 name=/opt/oracle/oradata/ecp/TS_Public_1.003.dbf
input datafile fno=00009 name=/opt/oracle/oradata/ecp/TS_Index_Base.001.dbf
input datafile fno=00010 name=/opt/oracle/oradata/ecp/TS_Index_Base.002.dbf
input datafile fno=00011 name=/opt/oracle/oradata/ecp/TS_Index_Base.003.dbf
channel ORA_DISK_1: starting piece 1 at 2011-11-08 13:22:07
channel ORA_DISK_2: starting compressed full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00002 name=/opt/oracle/oradata/ecp/undotbs01.dbf
input datafile fno=00001 name=/opt/oracle/oradata/ecp/system01.dbf
input datafile fno=00012 name=/opt/oracle/oradata/ecp/OGG.001.dbf
input datafile fno=00005 name=/opt/oracle/oradata/ecp/example01.dbf
input datafile fno=00006 name=/opt/oracle/oradata/ecp/TS_Public_1.001.dbf
input datafile fno=00007 name=/opt/oracle/oradata/ecp/TS_Public_1.002.dbf
channel ORA_DISK_2: starting piece 1 at 2011-11-08 13:22:07
channel ORA_DISK_1: finished piece 1 at 2011-11-08 13:22:22
piece handle=/opt/backup/ecp_full_11mr52bv_1_1 tag=TAG20111108T132207 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 2011-11-08 13:22:23
channel ORA_DISK_1: finished piece 1 at 2011-11-08 13:22:24
piece handle=/opt/backup/ecp_full_13mr52ce_1_1 tag=TAG20111108T132207 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 2011-11-08 13:22:25
channel ORA_DISK_1: finished piece 1 at 2011-11-08 13:22:26
piece handle=/opt/backup/ecp_full_14mr52cg_1_1 tag=TAG20111108T132207 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_2: finished piece 1 at 2011-11-08 13:22:41
piece handle=/opt/backup/ecp_full_12mr52bv_1_1 tag=TAG20111108T132207 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:34
Finished backup at 2011-11-08 13:22:41

--备份sysdata01.dbf文件
RMAN> backup datafile 1 format '/opt/backup/ecp_system_%U';

Starting backup at 2011-11-08 13:23:44
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/opt/oracle/oradata/ecp/system01.dbf
channel ORA_DISK_1: starting piece 1 at 2011-11-08 13:23:45
channel ORA_DISK_2: starting compressed full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_2: starting piece 1 at 2011-11-08 13:23:45
channel ORA_DISK_2: finished piece 1 at 2011-11-08 13:23:46
piece handle=/opt/backup/ecp_system_16mr52f1_1_1 tag=TAG20111108T132344 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_2: starting compressed full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_2: starting piece 1 at 2011-11-08 13:23:47
channel ORA_DISK_2: finished piece 1 at 2011-11-08 13:23:48
piece handle=/opt/backup/ecp_system_17mr52f2_1_1 tag=TAG20111108T132344 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: finished piece 1 at 2011-11-08 13:24:13
piece handle=/opt/backup/ecp_system_15mr52f1_1_1 tag=TAG20111108T132344 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:28
Finished backup at 2011-11-08 13:24:13
--注：所有备份system空间，都是会自动备份spfile和控制文件

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
28      B  F  A DISK        2011-11-08 13:22:19 1       1       YES        TAG20111108T132207
29      B  F  A DISK        2011-11-08 13:22:23 1       1       YES        TAG20111108T132207
30      B  F  A DISK        2011-11-08 13:22:25 1       1       YES        TAG20111108T132207
31      B  F  A DISK        2011-11-08 13:22:35 1       1       YES        TAG20111108T132207
32      B  F  A DISK        2011-11-08 13:23:45 1       1       YES        TAG20111108T132344
33      B  F  A DISK        2011-11-08 13:23:47 1       1       YES        TAG20111108T132344
34      B  F  A DISK        2011-11-08 13:24:03 1       1       YES        TAG20111108T132344
--查看所有备份集情况

RMAN> crosscheck backup;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_11mr52bv_1_1 recid=28 stamp=766675327
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_13mr52ce_1_1 recid=29 stamp=766675343
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_14mr52cg_1_1 recid=30 stamp=766675345
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_12mr52bv_1_1 recid=31 stamp=766675327
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_16mr52f1_1_1 recid=32 stamp=766675425
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_17mr52f2_1_1 recid=33 stamp=766675427
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_15mr52f1_1_1 recid=34 stamp=766675425
--查看全部有效

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           29     2011-11-08 13:22:23
  Backup Piece       29     2011-11-08 13:22:23 /opt/backup/ecp_full_13mr52ce_1_1
Backup Set           30     2011-11-08 13:22:25
  Backup Piece       30     2011-11-08 13:22:25 /opt/backup/ecp_full_14mr52cg_1_1
--因为system01.dbf备份了两次，所以违背了备份策略

drwxr-xr-x  2 root    root      4096 07-12 15:54 vmdir
[root@node1 opt]# cd /opt/backup/
[root@node1 backup]# ll
总计 265356
-rw-r----- 1 oracle oinstall  47497216 11-08 13:22 ecp_full_11mr52bv_1_1
-rw-r----- 1 oracle oinstall 129433600 11-08 13:22 ecp_full_12mr52bv_1_1
-rw-r----- 1 oracle oinstall   1130496 11-08 13:22 ecp_full_13mr52ce_1_1
-rw-r----- 1 oracle oinstall     98304 11-08 13:22 ecp_full_14mr52cg_1_1
-rw-r----- 1 oracle oinstall  92012544 11-08 13:24 ecp_system_15mr52f1_1_1
-rw-r----- 1 oracle oinstall   1130496 11-08 13:23 ecp_system_16mr52f1_1_1
-rw-r----- 1 oracle oinstall     98304 11-08 13:23 ecp_system_17mr52f2_1_1
[root@node1 backup]# mv ecp_system_15mr52f1_1_1 ecp_system_15mr52f1_1_1_bak
[root@node1 backup]# ll
总计 265356
-rw-r----- 1 oracle oinstall  47497216 11-08 13:22 ecp_full_11mr52bv_1_1
-rw-r----- 1 oracle oinstall 129433600 11-08 13:22 ecp_full_12mr52bv_1_1
-rw-r----- 1 oracle oinstall   1130496 11-08 13:22 ecp_full_13mr52ce_1_1
-rw-r----- 1 oracle oinstall     98304 11-08 13:22 ecp_full_14mr52cg_1_1
-rw-r----- 1 oracle oinstall  92012544 11-08 13:24 ecp_system_15mr52f1_1_1_bak
-rw-r----- 1 oracle oinstall   1130496 11-08 13:23 ecp_system_16mr52f1_1_1
-rw-r----- 1 oracle oinstall     98304 11-08 13:23 ecp_system_17mr52f2_1_1
--对备份集中的其中一个文件重命名

RMAN> crosscheck backup;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_11mr52bv_1_1 recid=28 stamp=766675327
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_13mr52ce_1_1 recid=29 stamp=766675343
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_14mr52cg_1_1 recid=30 stamp=766675345
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_12mr52bv_1_1 recid=31 stamp=766675327
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_16mr52f1_1_1 recid=32 stamp=766675425
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_17mr52f2_1_1 recid=33 stamp=766675427
Crosschecked 6 objects

crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/backup/ecp_system_15mr52f1_1_1 recid=34 stamp=766675425
Crosschecked 1 objects
--发现一个无效的备份集

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
28      B  F  A DISK        2011-11-08 13:22:19 1       1       YES        TAG20111108T132207
29      B  F  A DISK        2011-11-08 13:22:23 1       1       YES        TAG20111108T132207
30      B  F  A DISK        2011-11-08 13:22:25 1       1       YES        TAG20111108T132207
31      B  F  A DISK        2011-11-08 13:22:35 1       1       YES        TAG20111108T132207
32      B  F  A DISK        2011-11-08 13:23:45 1       1       YES        TAG20111108T132344
33      B  F  A DISK        2011-11-08 13:23:47 1       1       YES        TAG20111108T132344
34      B  F  X DISK        2011-11-08 13:24:03 1       1       YES        TAG20111108T132344
--也标志为无效'X'

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           29     2011-11-08 13:22:23
  Backup Piece       29     2011-11-08 13:22:23 /opt/backup/ecp_full_13mr52ce_1_1
Backup Set           30     2011-11-08 13:22:25
  Backup Piece       30     2011-11-08 13:22:25 /opt/backup/ecp_full_14mr52cg_1_1
--策略还是显示这两个备份集违背规则

RMAN> DELETE NOPROMPT OBSOLETE;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
using channel ORA_DISK_2
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           29     2011-11-08 13:22:23
  Backup Piece       29     2011-11-08 13:22:23 /opt/backup/ecp_full_13mr52ce_1_1
Backup Set           30     2011-11-08 13:22:25
  Backup Piece       30     2011-11-08 13:22:25 /opt/backup/ecp_full_14mr52cg_1_1
deleted backup piece
backup piece handle=/opt/backup/ecp_full_13mr52ce_1_1 recid=29 stamp=766675343
deleted backup piece
backup piece handle=/opt/backup/ecp_full_14mr52cg_1_1 recid=30 stamp=766675345
Deleted 2 objects

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
no obsolete backups found
--删除违法策略的备份集

RMAN> delete  NOPROMPT  expired backup;

using channel ORA_DISK_1
using channel ORA_DISK_2

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
34      34      1   1   EXPIRED     DISK        /opt/backup/ecp_system_15mr52f1_1_1
deleted backup piece
backup piece handle=/opt/backup/ecp_system_15mr52f1_1_1 recid=34 stamp=766675425
Deleted 1 EXPIRED objects

RMAN> crosscheck backup;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_11mr52bv_1_1 recid=28 stamp=766675327
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_full_12mr52bv_1_1 recid=31 stamp=766675327
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_16mr52f1_1_1 recid=32 stamp=766675425
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/backup/ecp_system_17mr52f2_1_1 recid=33 stamp=766675427
Crosschecked 4 objects
--删除无效的备份集

通过实验说明：crosscheck只能够检测备份集是否有效(最常见的情况就是物理上是否还存在),并且可以用它来删除失效(expired)的备份集，而不是用来删除违背备份策略(obsolete)的备份集，如果要删除违背备份策略(废弃)的备份集，需要使用obsolete操作。出现这个问题的主要原因应该是expired和obsolete翻译成中文的时候理解的出入导致。

块修改跟踪特性(Block Change Tracking)

Posted on 2011 年 11 月 03 日 by 惜分飞

有一服务器有1T左右的数据，备份策略是(1+2)*2（1全备，2增量备份，备份保留2周期）的备份策略，随便增量备份减少了备份的体积，但是增量备份的时间，基本上和全备无差别，都是要近6小时（包括压缩）。这里没有完全体现出增量备份的强大之处，因为没有开启块修改跟踪，无论是增量备份还是全备都需要扫描所有的数据块。虽然网上说开启块修改跟踪可能会会触发一些bug，但是我找了下，10.2.0.4以后块修改跟踪还是比较稳定的，所以决定开启块修改跟踪功能，节约增量备份时间，提高系统性能。

一、开启块修改跟踪

[oracle@node1 bdump]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 – Production on Thu Nov 3 11:13:54 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database enable block change tracking using file

2 '/opt/oracle/oradata/ecp/Oracle_change.trace';

Database altered.

SQL> set long 200

SQL> col filename for a50

SQL> SELECT * FROM v$block_change_tracking;

STATUS FILENAME BYTES

———- ————————————————– ———-

ENABLED /opt/oracle/oradata/ecp/Oracle_change.trace 11599872

SQL> !/opt/oracle/oradata/ecp/Oracle_change.trace

/bin/bash: /opt/oracle/oradata/ecp/Oracle_change.trace: 权限不够

SQL> !

[oracle@node1 ~]$ ll /opt/oracle/oradata/ecp/Oracle_change.trace

-rw-r—– 1 oracle oinstall 11600384 11-03 11:05 /opt/oracle/oradata/ecp/Oracle_change.trace

[oracle@node1 ~]$ ps -ef|grep ctwr|grep -v grep

oracle 2771 1 0 11:05 ? 00:00:00 ora_ctwr_ecp

[oracle@node1 bdump]$ tail -11 /opt/oracle/admin/ecp/bdump/alert_ecp.log

Thu Nov 03 11:05:47 CST 2011

alter database enable block change tracking using file

'/opt/oracle/oradata/ecp/Oracle_change.trace'

Thu Nov 03 11:05:47 CST 2011

Block change tracking file is current.

Starting background process CTWR

CTWR started with pid=18, OS id=2771

Block change tracking service is active.

Thu Nov 03 11:05:48 CST 2011

Completed: alter database enable block change tracking using file

'/opt/oracle/oradata/ecp/Oracle_change.trace'

通过这些都可以看出来，开启块修改跟踪功能，会启动CTWR进程，并且修改的块号会被记录到指定文件中

二、关闭块改变跟踪

SQL> alter database disable block change tracking;

Database altered.

SQL> col filename for a50

SQL> SELECT * FROM v$block_change_tracking;

STATUS FILENAME BYTES

———- ————————————————– ———-

DISABLED

SQL> !

[oracle@node1 bdump]$ ll /opt/oracle/oradata/ecp/Oracle_change.trace

ls: /opt/oracle/oradata/ecp/Oracle_change.trace: 没有那个文件或目录

[oracle@node1 bdump]$ ps -ef|grep ctwr|grep -v grep

[oracle@node1 bdump]$ tail -8 /opt/oracle/admin/ecp/bdump/alert_ecp.log

Thu Nov 03 11:14:06 CST 2011

alter database disable block change tracking

Thu Nov 03 11:14:07 CST 2011

Block change tracking service stopping.

Thu Nov 03 11:14:07 CST 2011

Stopping background process CTWR

Deleted file /opt/oracle/oradata/ecp/Oracle_change.trace

Completed: alter database disable block change tracking

通过这些都可以看出来，关闭块修改跟踪功能，会关闭CTWR进程，并且删除跟踪文件（Linux系统会删除，Window不会）

三、块修改跟踪文件重命名

SQL> SELECT * FROM v$block_change_tracking;

STATUS FILENAME BYTES

———- ————————————————– ———-

ENABLED /opt/oracle/oradata/ecp/Oracle_change.trace 11599872

SQL> alter database rename file '/opt/oracle/oradata/ecp/Oracle_change.trace'

2 to '/opt/oracle/oradata/ecp/Oracle_change.trace_new';

alter database rename file '/opt/oracle/oradata/ecp/Oracle_change.trace'

ERROR at line 1:

ORA-01511: error in renaming log/data files

ORA-19771: cannot rename change tracking file while database is open

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1610612736 bytes

Fixed Size 2096632 bytes

Variable Size 385876488 bytes

Database Buffers 1207959552 bytes

Redo Buffers 14680064 bytes

Database mounted.

SQL> alter database rename file '/opt/oracle/oradata/ecp/Oracle_change.trace'

2 to '/opt/oracle/oradata/ecp/Oracle_change.trace_new';

Database altered.

SQL> alter database open;

Database altered.

SQL> SELECT * FROM v$block_change_tracking;

STATUS FILENAME BYTES

———- ————————————————– ———-

ENABLED /opt/oracle/oradata/ecp/Oracle_change.trace_new 11599872

[oracle@node1 bdump]$ tail -100 /opt/oracle/admin/ecp/bdump/alert_ecp.log |more

Thu Nov 03 11:22:34 CST 2011

alter database rename file '/opt/oracle/oradata/ecp/Oracle_change.trace'

to '/opt/oracle/oradata/ecp/Oracle_change.trace_new'

Completed: alter database rename file '/opt/oracle/oradata/ecp/Oracle_change.trace'

to '/opt/oracle/oradata/ecp/Oracle_change.trace_new'

Thu Nov 03 11:22:40 CST 2011

alter database open

Thu Nov 03 11:22:40 CST 2011

CHANGE TRACKING is enabled for this database, but the

change tracking file can not be found. Recreating the file.

Change tracking file recreated.

Block change tracking file is current.

[oracle@node1 bdump]$ ll /opt/oracle/oradata/ecp/Oracle_change.trace*

-rw-r—– 1 oracle oinstall 11600384 11-03 11:22 /opt/oracle/oradata/ecp/Oracle_change.trace

-rw-r—– 1 oracle oinstall 11600384 11-03 12:05 /opt/oracle/oradata/ecp/Oracle_change.trace_new

数据库在open状态下不能修改，所以必须把数据库重启至mount状态才能够修改，如果库不能重启，那么可以先关闭原块修改跟踪功能，再开启块修改跟踪功能。
两种方法比较：
1、都会重新建立一个跟踪文件
2、如果是重命名，老的跟踪文件不会自动被删除，需要人工删除
3、推荐使用关闭跟踪功能，然后在重新制定跟踪文件开启跟踪功能

清除离线数据文件记录

Posted on 2011 年 10 月 21 日 by 惜分飞

测试前提：数据文件离线，系统上删除了该文件，需要删除在数据字典中，关于这条离线数据文件记录

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/oradata/test/archivelog
Oldest online log sequence     210
Next log sequence to archive   212
Current log sequence           212
--数据库是归档模式

SQL> col file_name for a40
SQL>  select file_id,file_name,bytes from dba_data_files order by 1;

   FILE_ID FILE_NAME                                     BYTES
---------- ---------------------------------------- ----------
         1 /opt/oracle/oradata/test/system01.dbf     524288000
         2 /opt/oracle/oradata/test/undotbs01.dbf   1289748480
         3 /opt/oracle/oradata/test/sysaux01.dbf     377487360
         4 /opt/oracle/oradata/test/users01.dbf        5242880
         5 /opt/oracle/oradata/test/user32g.dbf       10485760
         6 /opt/oracle/oradata/test/xifenfei01.dbf    20971520
         7 /opt/oracle/oradata/test/user02.dbf        10485760
         8 /opt/oracle/oradata/test/odu02.dbf       1.1283E+10
         9 /opt/oracle/oradata/test/odu01.dbf        104857600
        10 /opt/oracle/oradata/test/odu03.chf                            

10 rows selected.

SQL> col error for a20
SQL>  select file#,ONLINE_STATUS,ERROR,CHANGE# from V$RECOVER_FILE order by 1;

     FILE# ONLINE_ ERROR                   CHANGE#
---------- ------- -------------------- ----------
        10 OFFLINE FILE NOT FOUND                0

SQL> !ls /opt/oracle/oradata/test/odu03.chf
ls: /opt/oracle/oradata/test/odu03.chf: No such file or directory
--说明该数据文件已经从硬盘上删除

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  2082784 bytes
Variable Size             130025504 bytes
Database Buffers           71303168 bytes
Redo Buffers                6303744 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/opt/oracle/oradata/test/redo01.log'  SIZE 50M,
  9    GROUP 2 '/opt/oracle/oradata/test/redo02.log'  SIZE 50M,
 10    GROUP 3 '/opt/oracle/oradata/test/redo03.log'  SIZE 50M
 11  DATAFILE
 12    '/opt/oracle/oradata/test/system01.dbf',
 13    '/opt/oracle/oradata/test/undotbs01.dbf',
 14    '/opt/oracle/oradata/test/sysaux01.dbf',
 15    '/opt/oracle/oradata/test/users01.dbf',
 16    '/opt/oracle/oradata/test/user32g.dbf',
 17    '/opt/oracle/oradata/test/xifenfei01.dbf',
 18    '/opt/oracle/oradata/test/user02.dbf',
 19    '/opt/oracle/oradata/test/odu02.dbf',
 20    '/opt/oracle/oradata/test/odu01.dbf'
        ,'/opt/oracle/oradata/test/odu03.chf'     --文件不存在，创建控制文件这条记录需要除掉
 21  CHARACTER SET ZHS16GBK
 22  ;

Control file created.


SQL> alter database open;

Database altered.

SQL> select file_id,file_name,bytes from dba_data_files order by 1;

   FILE_ID FILE_NAME                                     BYTES
---------- ---------------------------------------- ----------
         1 /opt/oracle/oradata/test/system01.dbf     524288000
         2 /opt/oracle/oradata/test/undotbs01.dbf   1289748480
         3 /opt/oracle/oradata/test/sysaux01.dbf     377487360
         4 /opt/oracle/oradata/test/users01.dbf        5242880
         5 /opt/oracle/oradata/test/user32g.dbf       10485760
         6 /opt/oracle/oradata/test/xifenfei01.dbf    20971520
         7 /opt/oracle/oradata/test/user02.dbf        10485760
         8 /opt/oracle/oradata/test/odu02.dbf       1.1283E+10
         9 /opt/oracle/oradata/test/odu01.dbf        104857600
        10 /opt/oracle/product/10.2.0/db_1/dbs/MISSING00010     --系统默认创建了自定义的数据文件名称

10 rows selected.

SQL> select file#,ONLINE_STATUS,ERROR,CHANGE# from V$RECOVER_FILE order by 1;

     FILE# ONLINE_ ERROR                   CHANGE#
---------- ------- -------------------- ----------
        10 OFFLINE FILE MISSING                  0  
--提示该文件是离线状态，需要恢复，结果同开始时候状态

SQL> select file#,STATUS$,TS#,RELFILE# from file$ order by 1;

     FILE#    STATUS$        TS#   RELFILE#
---------- ---------- ---------- ----------
         1          2          0          1
         2          2          1          2
         3          2          2          3
         4          2          4          4
         5          2          4          5
         6          2          6          6
         7          2          4          7
         8          2          7          9
         9          2          7          6
        10          2          7         10
        11          1

11 rows selected.

SQL> delete from file$ where file#=10;    ---重要的就是这个操作

1 row deleted.

SQL> select file#,STATUS$,TS#,RELFILE# from file$ order by 1;

     FILE#    STATUS$        TS#   RELFILE#
---------- ---------- ---------- ----------
         1          2          0          1
         2          2          1          2
         3          2          2          3
         4          2          4          4
         5          2          4          5
         6          2          6          6
         7          2          4          7
         8          2          7          9
         9          2          7          6
        11          1

10 rows selected.

SQL> col name for a40
SQL> select * from v$dbfile order by 1;

     FILE# NAME
---------- ----------------------------------------
         1 /opt/oracle/oradata/test/system01.dbf
         2 /opt/oracle/oradata/test/undotbs01.dbf
         3 /opt/oracle/oradata/test/sysaux01.dbf
         4 /opt/oracle/oradata/test/users01.dbf
         5 /opt/oracle/oradata/test/user32g.dbf
         6 /opt/oracle/oradata/test/xifenfei01.dbf
         7 /opt/oracle/oradata/test/user02.dbf
         8 /opt/oracle/oradata/test/odu02.dbf
         9 /opt/oracle/oradata/test/odu01.dbf
        10 /opt/oracle/product/10.2.0/db_1/dbs/MISSING00010

10 rows selected.
--需要重建控制文件，删除不存在的数据文件

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  2082784 bytes
Variable Size             130025504 bytes
Database Buffers           71303168 bytes
Redo Buffers                6303744 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/opt/oracle/oradata/test/redo01.log'  SIZE 50M,
  9    GROUP 2 '/opt/oracle/oradata/test/redo02.log'  SIZE 50M,
 10    GROUP 3 '/opt/oracle/oradata/test/redo03.log'  SIZE 50M
 11  DATAFILE
 12    '/opt/oracle/oradata/test/system01.dbf',
 13    '/opt/oracle/oradata/test/undotbs01.dbf',
 14    '/opt/oracle/oradata/test/sysaux01.dbf',
 15    '/opt/oracle/oradata/test/users01.dbf',
 16    '/opt/oracle/oradata/test/user32g.dbf',
 17    '/opt/oracle/oradata/test/xifenfei01.dbf',
 18    '/opt/oracle/oradata/test/user02.dbf',
 19    '/opt/oracle/oradata/test/odu02.dbf',
 20    '/opt/oracle/oradata/test/odu01.dbf'
 21  CHARACTER SET ZHS16GBK
 22  ;

Control file created.

SQL> alter database open;

Database altered.

SQL> select file_id,file_name,bytes from dba_data_files order by 1;

   FILE_ID FILE_NAME                                     BYTES
---------- ---------------------------------------- ----------
         1 /opt/oracle/oradata/test/system01.dbf     524288000
         2 /opt/oracle/oradata/test/undotbs01.dbf   1289748480
         3 /opt/oracle/oradata/test/sysaux01.dbf     377487360
         4 /opt/oracle/oradata/test/users01.dbf        5242880
         5 /opt/oracle/oradata/test/user32g.dbf       10485760
         6 /opt/oracle/oradata/test/xifenfei01.dbf    20971520
         7 /opt/oracle/oradata/test/user02.dbf        10485760
         8 /opt/oracle/oradata/test/odu02.dbf       1.1283E+10
         9 /opt/oracle/oradata/test/odu01.dbf        104857600

9 rows selected.

SQL> select * from v$dbfile order by 1;

     FILE# NAME
---------- ----------------------------------------
         1 /opt/oracle/oradata/test/system01.dbf
         2 /opt/oracle/oradata/test/undotbs01.dbf
         3 /opt/oracle/oradata/test/sysaux01.dbf
         4 /opt/oracle/oradata/test/users01.dbf
         5 /opt/oracle/oradata/test/user32g.dbf
         6 /opt/oracle/oradata/test/xifenfei01.dbf
         7 /opt/oracle/oradata/test/user02.dbf
         8 /opt/oracle/oradata/test/odu02.dbf
         9 /opt/oracle/oradata/test/odu01.dbf

9 rows selected.

补充说明：非归档模式下，NOARCHIVELOG创建控制文件，其他无太大区别
测试来源：itpub:数据文件物理性删除相关问题疑惑?
参考blog：roger:如何彻底删除已经不存在的数据文件？