标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,605)
- DB2 (22)
- MySQL (71)
- Oracle (1,469)
- Data Guard (50)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle 23ai (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (533)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- 分布式存储故障导致数据库无法启动故障处理
- read_me_recover_tn勒索恢复
- WINDOWS 下用dg broker搭建ADG(单机to单机)
- 存储故障后oracle报—ORA-01122/ORA-01207故障处理
- Oracle 23ai rm redo*.log恢复
- Oracle 发布计划—包含Oracle 23ai版本
- Oracle 23ai 变化之—-默认数据文件变为bigfile
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
分类目录归档:rman备份/恢复
关于blockrecover 解决坏块相关测试与总结
悲剧的客户因为IBM p系列小机更换电源导致主机直接掉电,起来后发现数据库出现不少坏块,而且还有部分坏块中含有回滚事务,导致alert日志一直报smon回滚遇到坏块错误,该数据库版本是9.2.0.8 RAC,根据客户的备份情况,为了减少对业务的影响,决定使用blockrecover对其处理.这里通过10g数据库大概模拟出现含事务坏块的情况以及处理过程,重现了我们在处理的时候不确定的一些知识.
创建测试表
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production SQL> conn /as sysdba Connected. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oracle/oradata/XFF/archivelog Oldest online log sequence 231 Next log sequence to archive 233 Current log sequence 233 SQL> conn chf/xifenfei Connected. SQL> create table t_xifenfei 2 as 3 select * from dba_objects where rownum<10; Table created. SQL> select rowid, 2 dbms_rowid.rowid_relative_fno(rowid) rel_fno, 3 dbms_rowid.rowid_block_number(rowid) block 4 from chf.t_xifenfei; ROWID REL_FNO BLOCK ------------------ ---------- ---------- AAANIqAAEAAAAAcAAA 4 28 AAANIqAAEAAAAAcAAB 4 28 AAANIqAAEAAAAAcAAC 4 28 AAANIqAAEAAAAAcAAD 4 28 AAANIqAAEAAAAAcAAE 4 28 AAANIqAAEAAAAAcAAF 4 28 AAANIqAAEAAAAAcAAG 4 28 AAANIqAAEAAAAAcAAH 4 28 AAANIqAAEAAAAAcAAI 4 28 9 rows selected.
当前的seq是233(也就是说我在233归档上创建了t_xienfei表)
dbv检查block
[oracle@xifenfei ~]$ dbv file='/u01/oracle/oradata/XFF/users01.dbf' DBVERIFY: Release 10.2.0.4.0 - Production on Sun Jan 20 22:16:16 2013 Copyright (c) 1982, 2007, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/XFF/users01.dbf DBVERIFY - Verification complete Total Pages Examined : 1280 Total Pages Processed (Data) : 904 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 38 Total Pages Failing (Index): 0 Total Pages Processed (Other): 44 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 294 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Highest block SCN : 3224018224 (2860.3224018224)
证明无任何坏块
切换归档
SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oracle/oradata/XFF/archivelog Oldest online log sequence 233 Next log sequence to archive 235 Current log sequence 235
现在已经切换seq到235
rman备份我们需要测试block(file 4 block 28)对应的数据文件
RMAN> backup datafile 4 format '/u01/oracle/oradata/xff_4.rman';
具体见:rman制造坏块,bbed修复坏块
模拟数据库进行其他操作
SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oracle/oradata/XFF/archivelog Oldest online log sequence 235 Next log sequence to archive 237 Current log sequence 237 SQL> conn chf/xifenfei Connected. SQL> create table t_xff 2 as 3 select * from dba_objects; Table created. SQL> alter system switch logfile; System altered.
主要是为了模拟对其他block操作,对于block 28的恢复影响
对block 28进行操作
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oracle/oradata/XFF/archivelog Oldest online log sequence 237 Next log sequence to archive 239 Current log sequence 239 SQL> update chf.t_xifenfei set object_name='www.xifenfei.com'; 9 rows updated. SQL> commit; Commit complete. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered.
在seq为239的时候对block 28进行了一次update操作
模拟其他业务操作
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oracle/oradata/XFF/archivelog Oldest online log sequence 240 Next log sequence to archive 242 Current log sequence 242 SQL> delete from chf.t_xff ; 50491 rows deleted. SQL> commit; Commit complete. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oracle/oradata/XFF/archivelog Oldest online log sequence 242 Next log sequence to archive 244 Current log sequence 244 SQL> alter system switch logfile; System altered. SQL> / System altered.
这里可以知道在seq为246的时候做了备份归档操作
备份归档操作
RMAN> backup archivelog all format '/u01/oracle/oradata/xff_arch_%U' delete input;
模拟继续操作
SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oracle/oradata/XFF/archivelog Oldest online log sequence 248 Next log sequence to archive 250 Current log sequence 250 SQL> update chf.t_xifenfei set object_name='www.orasos.com' where rownum<5; 4 rows updated.
这里可以发现,在seq为250的时候我们再次对block 28进行了操作
使用rman制造坏块
RMAN> BLOCKRECOVER DATAFILE 4 block 28 clear;
dbv检查坏块
[oracle@xifenfei ~]$ dbv file='/u01/oracle/oradata/XFF/users01.dbf' DBVERIFY: Release 10.2.0.4.0 - Production on Sun Jan 20 23:01:24 2013 Copyright (c) 1982, 2007, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/XFF/users01.dbf Page 28 is influx - most likely media corrupt Corrupt block relative dba: 0x0100001c (file 4, block 28) Fractured block found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x0100001c last change scn: 0x0b2c.c02ab081 seq: 0x1 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xb0c4a6ea check value in block header: 0x393f computed block checksum: 0xc917 DBVERIFY - Verification complete Total Pages Examined : 1280 Total Pages Processed (Data) : 903 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 38 Total Pages Failing (Index): 0 Total Pages Processed (Other): 47 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 291 Total Pages Marked Corrupt : 1 Total Pages Influx : 1 Highest block SCN : 3224022228 (2860.3224022228)
强制kill数据库
[oracle@xifenfei ~]$ ps -ef|grep pmon oracle 9744 9638 0 23:03 pts/1 00:00:00 grep pmon oracle 32156 1 0 14:17 ? 00:00:10 ora_pmon_XFF [oracle@xifenfei ~]$ kill -9 32156 [oracle@xifenfei ~]$ ps -ef|grep pmon oracle 9751 9638 0 23:03 pts/1 00:00:00 grep pmon
为了模拟含事务的block出现坏块
启动数据库
SQL> startup mount ORACLE instance started. Total System Global Area 306184192 bytes Fixed Size 1267164 bytes Variable Size 109054500 bytes Database Buffers 188743680 bytes Redo Buffers 7118848 bytes Database mounted. SQL> ALTER DATABASE OPEN; Database altered.
数据库启动正常
查询坏块
SQL> select count(*) from chf.t_xifenfei; select count(*) from chf.t_xifenfei * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 4, block # 28) ORA-01110: data file 4: '/u01/oracle/oradata/XFF/users01.dbf'
alert日志
Sun Jan 20 23:04:37 2013 SMON: enabling tx recovery Sun Jan 20 23:04:37 2013 Database Characterset is ZHS16GBK Sun Jan 20 23:04:37 2013 Hex dump of (file 4, block 28) in trace file /u01/oracle/admin/XFF/bdump/xff_smon_9775.trc Corrupt block relative dba: 0x0100001c (file 4, block 28) Fractured block found during buffer read Data in bad block: type: 6 format: 2 rdba: 0x0100001c last change scn: 0x0b2c.c02ab081 seq: 0x1 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xb0c4a6ea check value in block header: 0x393f computed block checksum: 0xc917 Reread of rdba: 0x0100001c (file 4, block 28) found same corrupted data ORACLE Instance XFF (pid = 8) - Error 1578 encountered while recovering transaction (9, 37) on object 53802. Sun Jan 20 23:04:38 2013 Errors in file /u01/oracle/admin/XFF/bdump/xff_smon_9775.trc: ORA-01578: ORACLE data block corrupted (file # 4, block # 28) ORA-01110: data file 4: '/u01/oracle/oradata/XFF/users01.dbf'
通过试验步骤和alert日志可以发现因为block有事务,但是被标记为了坏块,所以smon无法回滚该事务,从而出现alert中类似提示
继续切换归档
SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. SQL> / System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/oracle/oradata/XFF/archivelog Oldest online log sequence 252 Next log sequence to archive 254 Current log sequence 254
移走归档
[oracle@xifenfei archivelog]$ ls -l total 2224 -rw-r----- 1 oracle oinstall 360960 Jan 20 22:59 1_247_792679299.dbf -rw-r----- 1 oracle oinstall 1024 Jan 20 22:59 1_248_792679299.dbf -rw-r----- 1 oracle oinstall 1630208 Jan 20 23:04 1_249_792679299.dbf -rw-r----- 1 oracle oinstall 249344 Jan 20 23:09 1_250_792679299.dbf -rw-r----- 1 oracle oinstall 1024 Jan 20 23:09 1_251_792679299.dbf -rw-r----- 1 oracle oinstall 4608 Jan 20 23:09 1_252_792679299.dbf -rw-r----- 1 oracle oinstall 1024 Jan 20 23:09 1_253_792679299.dbf [oracle@xifenfei archivelog]$ mkdir bak [oracle@xifenfei archivelog]$ mv *.dbf bak [oracle@xifenfei archivelog]$ ll total 4 drwxr-xr-x 2 oracle oinstall 4096 Jan 20 23:11 bak
为了重现,当我们使用blockrecover恢复的时候,如果缺少归档会怎么样
尝试blockrecover
RMAN> BLOCKRECOVER DATAFILE 4 block 28; Starting blockrecover at 20-JAN-13 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=135 devtype=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: sid=126 devtype=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: sid=125 devtype=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: sid=124 devtype=DISK channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00004 channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/xff_4.rman channel ORA_DISK_1: restored block(s) from backup piece 1 piece handle=/u01/oracle/oradata/xff_4.rman tag=TAG20130120T222333 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01 starting media recovery RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of blockrecover command at 01/20/2013 23:11:41 RMAN-06053: unable to perform media recovery because of missing log RMAN-06025: no backup of log thread 1 seq 253 lowscn 12286830510311 found to restore RMAN-06025: no backup of log thread 1 seq 252 lowscn 12286830510307 found to restore RMAN-06025: no backup of log thread 1 seq 251 lowscn 12286830510305 found to restore RMAN-06025: no backup of log thread 1 seq 250 lowscn 12286830509979 found to restore RMAN-06025: no backup of log thread 1 seq 249 lowscn 12286830489543 found to restore RMAN-06025: no backup of log thread 1 seq 248 lowscn 12286830489541 found to restore RMAN-06025: no backup of log thread 1 seq 247 lowscn 12286830489279 found to restore
alert日志记录
Sun Jan 20 23:11:38 2013 alter database recover datafile list clear Sun Jan 20 23:11:38 2013 Completed: alter database recover datafile list clear Sun Jan 20 23:11:38 2013 Starting block media recovery Sun Jan 20 23:11:39 2013 Media Recovery Log /u01/oracle/oradata/XFF/archivelog/1_235_792679299.dbf Sun Jan 20 23:11:41 2013 alter database recover cancel Sun Jan 20 23:11:41 2013 Media Recovery Canceled Completed: alter database recover cancel
blockrecover恢复途中或者异常终止,dbv检测
[oracle@xifenfei ~]$ dbv file=/u01/oracle/oradata/XFF/users01.dbf DBVERIFY: Release 10.2.0.4.0 - Production on Sun Jan 20 23:18:29 2013 Copyright (c) 1982, 2007, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/XFF/users01.dbf Page 28 is marked corrupt Corrupt block relative dba: 0x0100001c (file 4, block 28) Bad check value found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x0100001c last change scn: 0x0b2c.c02b0248 seq: 0x1 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x02480601 check value in block header: 0x13fc computed block checksum: 0x663b DBVERIFY - Verification complete Total Pages Examined : 1280 Total Pages Processed (Data) : 903 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 38 Total Pages Failing (Index): 0 Total Pages Processed (Other): 47 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 291 Total Pages Marked Corrupt : 1 Total Pages Influx : 0 Highest block SCN : 3224022228 (2860.3224022228)
在blockrecover处理过程,或者处理失败的后,block依然是坏块,如果遇到这类情况,没有事务可以直接指定seq/scn/time的方法来恢复,如果有事务,需要指定恢复时间点过该事务的时间点,让smon能够正常回滚,从而使得smon进程正常工作
还原归档后继续测试
[oracle@xifenfei archivelog]$ mv bak/* ./ RMAN> BLOCKRECOVER DATAFILE 4 block 28; starting media recovery archive log thread 1 sequence 247 is already on disk as file /u01/oracle/oradata/XFF/archivelog/1_247_792679299.dbf ………… media recovery complete, elapsed time: 00:00:01 Finished blockrecover at 20-JAN-13 [oracle@xifenfei ~]$ dbv file=/u01/oracle/oradata/XFF/users01.dbf DBVERIFY: Release 10.2.0.4.0 - Production on Sun Jan 20 23:15:43 2013 Copyright (c) 1982, 2007, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/XFF/users01.dbf DBVERIFY - Verification complete Total Pages Examined : 1280 Total Pages Processed (Data) : 904 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 38 Total Pages Failing (Index): 0 Total Pages Processed (Other): 47 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 291 Total Pages Marked Corrupt : 0 Total Pages Influx : 0 Highest block SCN : 3224023169 (2860.3224023169)
>
blockrecover处理alert日志
Sun Jan 20 23:15:01 2013 Media Recovery Log /u01/oracle/oradata/XFF/archivelog/1_250_792679299.dbf Sun Jan 20 23:15:01 2013 Media Recovery Log /u01/oracle/oradata/XFF/archivelog/1_251_792679299.dbf Sun Jan 20 23:15:01 2013 Recovery of Online Redo Log: Thread 1 Group 3 Seq 252 Reading mem 0 Mem# 0: /u01/oracle/oradata/XFF/redo03.log Sun Jan 20 23:15:01 2013 Recovery of Online Redo Log: Thread 1 Group 1 Seq 253 Reading mem 0 Mem# 0: /u01/oracle/oradata/XFF/redo01.log Sun Jan 20 23:15:01 2013 Recovery of Online Redo Log: Thread 1 Group 2 Seq 254 Reading mem 0 Mem# 0: /u01/oracle/oradata/XFF/redo02.log Sun Jan 20 23:15:02 2013 Completed block media recovery
补充说明
1.在9i中使用blockrecover会在Archive destination中生产block的备份文件,类似469_519791_3063_2442393528.bkd(file 469 block 519791),需要注意Archive destination目录结尾需要”/”,不然可能出现直接写入和该目录并列的Archive destination+blockrecover产生文件;10g中不生成该文件;如果该block在9i中未备份,也不会在生产相关文件,而是利用归档恢复.
2.blockrecover需要还原从该block从备份之后的所有归档(如果被备份起来需要还原出来),如果缺少归档可能导致恢复失败,包括基于scn/seq/time的等
3.blockrecover在执行过程中或者执行异常终止,该block依然是坏块,不会对其他block产生影响.在使用blockrecover出现异常终止后的block修复,在后续blog中提供解决方法
4.对于不含事务的坏块,如果数据允许丢失可以通过设置event跳过坏块(特殊block除外),然后重建对象;对于含event的block 坏块,建议使用blockrecover处理或者直接恢复数据文件,如果没有备份,考试使用event跳过事务回滚,然后屏蔽坏块处理
ORACLE 12C备份与恢复测试
12C引进了pdb的概念,使得rman的恢复相对来说复杂了一些,这里对pdb的常规备份和恢复进行了简单测试,供大家参考
cdb启动和pdb关系测试
[oracle@xifenfei tmp]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.0.2 Beta on Wed Dec 12 23:48:02 2012 Copyright (c) 1982, 2012, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> startup ORACLE instance started. Total System Global Area 939495424 bytes Fixed Size 2267128 bytes Variable Size 662702088 bytes Database Buffers 268435456 bytes Redo Buffers 6090752 bytes Database mounted. Database opened. SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs; CON_ID DBID NAME OPEN_MODE ---------- ---------- ------------------------------ ---------- 2 4043918109 PDB$SEED READ ONLY 3 2346805300 LX1 MOUNTED 4 2385557792 LX2 MOUNTED 5 1565384817 FF MOUNTED SQL> alter pluggable database all open; Pluggable database altered. SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs; CON_ID DBID NAME OPEN_MODE ---------- ---------- ------------------------------ ---------- 2 4043918109 PDB$SEED READ ONLY 3 2346805300 LX1 READ WRITE 4 2385557792 LX2 READ WRITE 5 1565384817 FF READ WRITE
证明直接startup cdb里面的pdb不会自动open,需要手工进行open
rman使用cdb备份数据库
[oracle@xifenfei ~]$ rman target / Recovery Manager: Release 12.1.0.0.2 on Wed Dec 12 21:36:08 2012 Copyright (c) 1982, 2012, Oracle and/or its affiliates. All rights reserved. connected to target database: xifenfei (DBID=2412861330) RMAN> backup filesperset = 5 as compressed backupset database format '/tmp/full_db_%U'; Starting backup at 12-DEC-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=259 device type=DISK channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/xifenfei/system01.dbf input datafile file number=00010 name=/u01/app/oracle/oradata/xifenfei/LX2/system01.dbf input datafile file number=00011 name=/u01/app/oracle/oradata/xifenfei/LX2/sysaux01.dbf channel ORA_DISK_1: starting piece 1 at 12-DEC-12 channel ORA_DISK_1: finished piece 1 at 12-DEC-12 piece handle=/tmp/full_db_06nsn3uq_1_1 tag=TAG20121212T213626 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00019 name=/u01/app/oracle/oradata/xifenfei/xffexample01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/xifenfei/undotbs01.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/xifenfei/LX1/system01.dbf input datafile file number=00018 name=/u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf input datafile file number=00008 name=/u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf channel ORA_DISK_1: starting piece 1 at 12-DEC-12 channel ORA_DISK_1: finished piece 1 at 12-DEC-12 piece handle=/tmp/full_db_07nsn407_1_1 tag=TAG20121212T213626 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00017 name=/u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/xifenfei/pdbseed/system01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/xifenfei/pdbseed/sysaux01.dbf channel ORA_DISK_1: starting piece 1 at 12-DEC-12 channel ORA_DISK_1: finished piece 1 at 12-DEC-12 piece handle=/tmp/full_db_08nsn41l_1_1 tag=TAG20121212T213626 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15 channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00003 name=/u01/app/oracle/oradata/xifenfei/sysaux01.dbf input datafile file number=00016 name=/u01/app/oracle/oradata/xifenfei/xffsystem01.dbf input datafile file number=00006 name=/u01/app/oracle/oradata/xifenfei/users01.dbf input datafile file number=00009 name=/u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf input datafile file number=00012 name=/u01/app/oracle/oradata/xifenfei/LX2/LX2_users01.dbf channel ORA_DISK_1: starting piece 1 at 12-DEC-12 channel ORA_DISK_1: finished piece 1 at 12-DEC-12 piece handle=/tmp/full_db_09nsn440_1_1 tag=TAG20121212T213626 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45 channel ORA_DISK_1: starting compressed 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 12-DEC-12 channel ORA_DISK_1: finished piece 1 at 12-DEC-12 piece handle=/tmp/full_db_0ansn45d_1_1 tag=TAG20121212T213626 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 12-DEC-12 RMAN> list backup summary; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 1 B F A DISK 12-DEC-12 1 1 YES TAG20121212T213250 2 B F A DISK 12-DEC-12 1 1 YES TAG20121212T213626 3 B F A DISK 12-DEC-12 1 1 YES TAG20121212T213626 4 B F A DISK 12-DEC-12 1 1 YES TAG20121212T213626 5 B F A DISK 12-DEC-12 1 1 YES TAG20121212T213626 6 B F A DISK 12-DEC-12 1 1 YES TAG20121212T213626 RMAN> report schema; Report of database schema for database with db_unique_name xifenfei List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 700 SYSTEM *** /u01/app/oracle/oradata/xifenfei/system01.dbf 2 210 PDB$SEED:SYSTEM *** /u01/app/oracle/oradata/xifenfei/pdbseed/system01.dbf 3 550 SYSAUX *** /u01/app/oracle/oradata/xifenfei/sysaux01.dbf 4 165 PDB$SEED:SYSAUX *** /u01/app/oracle/oradata/xifenfei/pdbseed/sysaux01.dbf 5 310 UNDOTBS1 *** /u01/app/oracle/oradata/xifenfei/undotbs01.dbf 6 5 USERS *** /u01/app/oracle/oradata/xifenfei/users01.dbf 7 210 LX1:SYSTEM *** /u01/app/oracle/oradata/xifenfei/LX1/system01.dbf 8 165 LX1:SYSAUX *** /u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf 9 5 LX1:USERS *** /u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf 10 210 LX2:SYSTEM *** /u01/app/oracle/oradata/xifenfei/LX2/system01.dbf 11 165 LX2:SYSAUX *** /u01/app/oracle/oradata/xifenfei/LX2/sysaux01.dbf 12 5 LX2:USERS *** /u01/app/oracle/oradata/xifenfei/LX2/LX2_users01.dbf 16 270 FF:SYSTEM *** /u01/app/oracle/oradata/xifenfei/xffsystem01.dbf 17 570 FF:SYSAUX *** /u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf 18 5 FF:USERS *** /u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf 19 341 FF:EXAMPLE *** /u01/app/oracle/oradata/xifenfei/xffexample01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 530 TEMP 32767 /u01/app/oracle/oradata/xifenfei/temp01.dbf 2 20 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/xifenfei/pdbseed/temp01.dbf 3 20 LX1:TEMP 32767 /u01/app/oracle/oradata/xifenfei/LX1/temp01.dbf 4 20 LX2:TEMP 32767 /u01/app/oracle/oradata/xifenfei/LX2/temp01.dbf 5 20 FF:TEMP 32767 /u01/app/oracle/oradata/xifenfei/xfftemp01.dbf
试验证明:通过rman通过cdb库的备份,可以实现对对应的cdb和所包含的pdb进行备份
配置pdb访问tns
[oracle@xifenfei ~]$ lsnrctl status LSNRCTL for Linux: Version 12.1.0.0.2 on 12-DEC-2012 22:33:27 Copyright (c) 1991, 2012, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.0.2 Start Date 12-DEC-2012 22:31:55 Uptime 0 days 0 hr. 1 min. 32 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/xifenfei/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei)(PORT=5500))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "ff" has 1 instance(s). Instance "xff", status READY, has 1 handler(s) for this service... Service "xifenfei" has 1 instance(s). Instance "xff", status READY, has 1 handler(s) for this service... Service "lx1" has 1 instance(s). Instance "xff", status READY, has 1 handler(s) for this service... Service "lx2" has 1 instance(s). Instance "xff", status READY, has 1 handler(s) for this service... Service "xffXDB" has 1 instance(s). Instance "xff", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@xifenfei admin]$ vi tnsnames.ora lx1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = lx1) ) ) ff = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ff) ) )
sqlplus访问pdb
[oracle@xifenfei admin]$ sqlplus sys@lx1 as sysdba SQL*Plus: Release 12.1.0.0.2 Beta on Wed Dec 12 22:35:07 2012 Copyright (c) 1982, 2012, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show con_name; CON_NAME ------------------------------ LX1 SQL> create user xff identified by xifenfei; User created. SQL> GRANT SYSDBA TO XFF; Grant succeeded.
rman备份pdb数据库
[oracle@xifenfei admin]$ rman target xff/xifenfei@lx1 Recovery Manager: Release 12.1.0.0.2 on Wed Dec 12 22:44:46 2012 Copyright (c) 1982, 2012, Oracle and/or its affiliates. All rights reserved. connected to target database: xifenfei (DBID=2412861330) RMAN> backup filesperset = 5 as compressed backupset database format '/tmp/lx1_db_%U'; Starting backup at 12-DEC-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=256 device type=DISK channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00007 name=/u01/app/oracle/oradata/xifenfei/LX1/system01.dbf input datafile file number=00008 name=/u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf input datafile file number=00009 name=/u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf channel ORA_DISK_1: starting piece 1 at 12-DEC-12 channel ORA_DISK_1: finished piece 1 at 12-DEC-12 piece handle=/tmp/lx1_db_0bnsn80f_1_1 tag=TAG20121212T224534 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25 Finished backup at 12-DEC-12 RMAN> report schema; Report of database schema for database with db_unique_name xifenfei List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 7 210 LX1:SYSTEM *** /u01/app/oracle/oradata/xifenfei/LX1/system01.dbf 8 165 LX1:SYSAUX *** /u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf 9 5 LX1:USERS *** /u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 3 20 LX1:TEMP 32767 /u01/app/oracle/oradata/xifenfei/LX1/temp01.dbf
rman通过cdb备份pdb
[oracle@xifenfei admin]$ rman target / Recovery Manager: Release 12.1.0.0.2 on Wed Dec 12 23:02:07 2012 Copyright (c) 1982, 2012, Oracle and/or its affiliates. All rights reserved. connected to target database: xifenfei (DBID=2412861330) RMAN> backup filesperset = 5 as compressed backupset pluggable database FF format '/tmp/ff_db_%U'; Starting backup at 12-DEC-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=262 device type=DISK channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00017 name=/u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf input datafile file number=00019 name=/u01/app/oracle/oradata/xifenfei/xffexample01.dbf input datafile file number=00016 name=/u01/app/oracle/oradata/xifenfei/xffsystem01.dbf input datafile file number=00018 name=/u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf channel ORA_DISK_1: starting piece 1 at 12-DEC-12 channel ORA_DISK_1: finished piece 1 at 12-DEC-12 piece handle=/tmp/ff_db_0cnsn8vm_1_1 tag=TAG20121212T230214 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26 Finished backup at 12-DEC-12
模拟pdb库全库恢复
SQL> conn xff/xifenfei@lx1 as sysdba Connected. SQL> create table t_xifenfei as select * from dba_objects; Table created. SQL> select count(*) from t_xifenfei; COUNT(*) ---------- 19121 --在pdb中不能切换日志(因为日志是全局的) SQL> alter system switch logfile; alter system switch logfile * ERROR at line 1: ORA-65040: operation not allowed from within a pluggable database SQL> shutdown immediate; Pluggable Database closed. --删除数据文件 [oracle@xifenfei admin]$ rm /u01/app/oracle/oradata/xifenfei/LX1/* --rman基于cdb恢复pdb库 [oracle@xifenfei ~]$ rman target / Recovery Manager: Release 12.1.0.0.2 on Wed Dec 12 23:11:22 2012 Copyright (c) 1982, 2012, Oracle and/or its affiliates. All rights reserved. connected to target database: xifenfei (DBID=2412861330) RMAN> restore pluggable database lx1; Starting restore at 12-DEC-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=25 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/xifenfei/LX1/system01.dbf channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/xifenfei/LX1/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/xifenfei/LX1/LX1_users01.dbf channel ORA_DISK_1: reading from backup piece /tmp/lx1_db_0bnsn80f_1_1 channel ORA_DISK_1: piece handle=/tmp/lx1_db_0bnsn80f_1_1 tag=TAG20121212T224534 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:25 Finished restore at 12-DEC-12 RMAN> recover pluggable database lx1; Starting recover at 12-DEC-12 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 12-DEC-12 RMAN> alter pluggable database lx1 open; Statement processed --验证恢复结果 SQL> conn xff/xifenfei@lx1 as sysdba Connected. SQL> select count(*) from t_xifenfei; COUNT(*) ---------- 19121
试验证明:对于pdb库的备份,使用rman可以在cdb级别进行还原和恢复
模拟数据文件恢复
SQL> create table t_xifenfei tablespace example 2 as 3 select * from dba_objects; Table created. SQL> select count(*) from t_xifenfei; COUNT(*) ---------- 89604 SQL> col name for a60 SQL> set lines 134 SQL> select file#,name from v$datafile; FILE# NAME ---------- ----------------------------------------------------------- 5 /u01/app/oracle/oradata/xifenfei/undotbs01.dbf 16 /u01/app/oracle/oradata/xifenfei/xffsystem01.dbf 17 /u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf 18 /u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf 19 /u01/app/oracle/oradata/xifenfei/xffexample01.dbf --离线含测试数据的数据文件 SQL> alter database datafile 19 offline; Database altered. --删除数据文件 SQL> !rm /u01/app/oracle/oradata/xifenfei/xffexample01.dbf SQL> !ls -l /u01/app/oracle/oradata/xifenfei/xffexample01.dbf ls: /u01/app/oracle/oradata/xifenfei/xffexample01.dbf: No such file or directory --尝试pdb级别恢复 [oracle@xifenfei ~]$ rman target sys/xifenfei@ff Recovery Manager: Release 12.1.0.0.2 on Wed Dec 12 23:29:03 2012 Copyright (c) 1982, 2012, Oracle and/or its affiliates. All rights reserved. connected to target database: xifenfei (DBID=2412861330) RMAN> restore datafile 19; Starting restore at 12-DEC-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 12/12/2012 23:29:15 RMAN-06026: some targets not found - aborting restore RMAN-06023: no backup or copy of datafile 19 found to restore --pdb级别不能识别对应数据文件(一种可能是我在cdb级别备份FF库,另一种可能bug) RMAN> list backup of datafile 19; specification does not match any backup in the repository --在cdb级别还原 [oracle@xifenfei tmp]$ rman target / Recovery Manager: Release 12.1.0.0.2 on Wed Dec 12 23:44:21 2012 Copyright (c) 1982, 2012, Oracle and/or its affiliates. All rights reserved. connected to target database: xifenfei (DBID=2412861330) RMAN> list backup of datafile 19; using target database control file instead of recovery catalog List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 3 Full 76.81M DISK 00:00:44 12-DEC-12 BP Key: 3 Status: AVAILABLE Compressed: YES Tag: TAG20121212T213626 Piece Name: /tmp/full_db_07nsn407_1_1 List of Datafiles in backup set 3 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 19 Full 1860043 12-DEC-12 /u01/app/oracle/oradata/xifenfei/xffexample01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 8 Full 189.52M DISK 00:01:25 12-DEC-12 BP Key: 8 Status: AVAILABLE Compressed: YES Tag: TAG20121212T230214 Piece Name: /tmp/ff_db_0cnsn8vm_1_1 List of Datafiles in backup set 8 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 19 Full 1860043 12-DEC-12 /u01/app/oracle/oradata/xifenfei/xffexample01.dbf RMAN> restore datafile 19; Starting restore at 12-DEC-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=28 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00019 to /u01/app/oracle/oradata/xifenfei/xffexample01.dbf channel ORA_DISK_1: reading from backup piece /tmp/ff_db_0cnsn8vm_1_1 channel ORA_DISK_1: piece handle=/tmp/ff_db_0cnsn8vm_1_1 tag=TAG20121212T230214 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:45 Finished restore at 12-DEC-12 --cdb级别恢复数据文件 RMAN> recover datafile 19; Starting recover at 12-DEC-12 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 12-DEC-12 --cdb级别不能直接online RMAN> alter database datafile 19 online; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of sql statement command at 12/12/2012 23:46:15 ORA-01516: nonexistent log file, data file, or temporary file "19" RMAN> alter pluggable database ff datafile 19 online; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of sql statement command at 12/12/2012 23:47:53 ORA-65046: operation not allowed from outside a pluggable database --进入pdb库进行online SQL> alter database datafile 19 online; Database altered. --验证数据 SQL> select count(*) from t_xifenfei; COUNT(*) ---------- 89604
试验证明:rman通过cdb级别操作,还是一步步恢复了pdb中离线异常的数据文件
总结说明
1.rman可以比较好的操作cdb和pdb备份
2.pdb的备份和恢复可以通过cdb来完成
通过odu验证rman backup对于truncate对象备份处理
rman backup 对于truncate和drop等相关操作的extent到底是怎么处理的,这里通过rman backup 结合odu证明出来,在较新版本的rman中,rman backup 并未完全的备份这些被认为不需要的extent.
创建模拟环境
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production SQL> create tablespace xifenfei datafile '/u01/oracle/oradata/XFF/xifenfei01.dbf' 2 size 10m autoextend on maxsize 10g; Tablespace created. SQL> conn chf/xifenfei Connected. SQL> create table t_xifenfei tablespace xifenfei 2 as 3 select * from dba_objects; Table created. SQL> insert into t_xifenfei 2 select * from dba_objects; 50055 rows created. SQL> commit; Commit complete. SQL> select BYTES from dba_free_space where TABLESPACE_NAME='XIFENFEI'; BYTES ---------- 983040 SQL> select BYTES from v$datafile where name='/u01/oracle/oradata/XFF/xifenfei01.dbf'; BYTES ---------- 12582912 SQL> select 12582912-983040 from dual; 12582912-983040 --------------- 11599872 SQL> select object_id,data_object_id from dba_objects where object_name='T_XIFENFEI'; OBJECT_ID DATA_OBJECT_ID ---------- -------------- 51833 51833 --这里我们得到信息有: --1.dataobj#=51833 --2.使用数据文件空间为:11599872
rman备份no truncate table 数据文件
[oracle@xifenfei ~]$ rman target / Recovery Manager: Release 10.2.0.4.0 - Production on Thu Dec 15 06:00:05 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: XFF (DBID=3440302261) RMAN> backup tablespace xifenfei format '/u01/oracle/oradata/tmp/no_truncate_xifenfei'; Starting backup at 15-DEC-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=158 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00005 name=/u01/oracle/oradata/XFF/xifenfei01.dbf channel ORA_DISK_1: starting piece 1 at 15-DEC-11 channel ORA_DISK_1: finished piece 1 at 15-DEC-11 piece handle=/u01/oracle/oradata/tmp/no_truncate_xifenfei tag=TAG20111215T060343 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 15-DEC-11
truncate table 操作
[oracle@xifenfei ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 15 06:03:58 2011 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> truncate table chf.t_xifenfei; Table truncated.
rman备份truncate table 数据文件
RMAN> backup tablespace xifenfei format '/u01/oracle/oradata/tmp/truncate_xifenfei'; Starting backup at 15-DEC-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=140 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00005 name=/u01/oracle/oradata/XFF/xifenfei01.dbf channel ORA_DISK_1: starting piece 1 at 15-DEC-11 channel ORA_DISK_1: finished piece 1 at 15-DEC-11 piece handle=/u01/oracle/oradata/tmp/truncate_xifenfei tag=TAG20111215T060445 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 15-DEC-11 [root@xifenfei ~]# ls -l /u01/oracle/oradata/tmp/*_xifenfei -rw-r----- 1 oracle oinstall 11640832 Dec 15 06:03 /u01/oracle/oradata/tmp/no_truncate_xifenfei -rw-r----- 1 oracle oinstall 630784 Dec 15 06:04 /u01/oracle/oradata/tmp/truncate_xifenfei
odu挖rman备份前数据文件
ODU> unload dict CLUSTER C_USER# file_no: 1 block_no: 89 TABLE OBJ$ file_no: 1 block_no: 121 CLUSTER C_OBJ# file_no: 1 block_no: 25 CLUSTER C_OBJ# file_no: 1 block_no: 25 found IND$'s obj# 19 found IND$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3 found TABPART$'s obj# 266 found TABPART$'s dataobj#:266,ts#:0,file#:1,block#:2121,tab#:0 found INDPART$'s obj# 271 found INDPART$'s dataobj#:271,ts#:0,file#:1,block#:2161,tab#:0 found TABSUBPART$'s obj# 278 found TABSUBPART$'s dataobj#:278,ts#:0,file#:1,block#:2217,tab#:0 found INDSUBPART$'s obj# 283 found INDSUBPART$'s dataobj#:283,ts#:0,file#:1,block#:2257,tab#:0 found IND$'s obj# 19 found IND$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3 found LOB$'s obj# 151 found LOB$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:6 found LOBFRAG$'s obj# 299 found LOBFRAG$'s dataobj#:299,ts#:0,file#:1,block#:2393,tab#:0 ODU> scan extent tablespace 6 scan extent start: 2011-12-15 06:12:28 scanning extent... scanning extent finished. scan extent completed: 2011-12-15 06:12:28 ODU> unload table chf.t_xifenfei object 51833 Unloading table: T_XIFENFEI,object ID: 51833 Unloading segment,storage(Obj#=51833 DataObj#=51833 TS#=6 File#=5 Block#=11 Cluster=0) 100110 rows unloaded --这里可以看到odu全部找到被truncate掉的记录条数
使用rman 备份后数据文件
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@xifenfei odu]$ rm /u01/oracle/oradata/XFF/xifenfei01.dbf [oracle@xifenfei odu]$ rman target / Recovery Manager: Release 10.2.0.4.0 - Production on Thu Dec 15 06:14:00 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database (not started) RMAN> startup mount; Oracle instance started database mounted Total System Global Area 318767104 bytes Fixed Size 1267236 bytes Variable Size 104860124 bytes Database Buffers 205520896 bytes Redo Buffers 7118848 bytes RMAN> restore datafile 5; Starting restore at 15-DEC-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=157 devtype=DISK channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00005 to /u01/oracle/oradata/XFF/xifenfei01.dbf channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/truncate_xifenfei channel ORA_DISK_1: restored backup piece 1 piece handle=/u01/oracle/oradata/tmp/truncate_xifenfei tag=TAG20111215T060445 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 15-DEC-11
odu挖rman还原后数据文件
ODU> scan extent tablespace 6 scan extent start: 2011-12-15 06:14:43 scanning extent... scanning extent finished. scan extent completed: 2011-12-15 06:14:43 ODU> unload table chf.t_xifenfei object 51833 Unloading table: T_XIFENFEI,object ID: 51833 Unloading segment,storage(Obj#=51833 DataObj#=51833 TS#=6 File#=5 Block#=11 Cluster=0) 4774 rows unloaded --odu只找到极少数数据4774/100110
通过odu挖rman备份前和备份后的数据文件,得知rman backup备份的过程,对绝大多数truncate的表的原始数据未正常备份(为什么是绝大多数,我无法给出解释),这里也可以看出rman backup并非是真正意义上的完全物理上复制(和rman copy还是有区别,copy不能完全被取代)