本文共 10564 字，大约阅读时间需要 35 分钟。
[20160721]rman与undo表空间备份.txt
--//UNDO表空间主要用于存储前镜像数据，这些数据在回滚以及恢复过程中可能被用到。
--//一般生产数据库的UNDO表空间可能会变得非常巨大，甚至包括多个数据文件，而备份完整的UNDO数据文件在恢复时一般可能用到的比
--//例很小。所以UNDO的很大一部分备份是多余的，在Oracle11g中，Oracle引入了一个新的特性RMAN UNDO备份优化。--//在RMAN备份UNDO表空间时，提交事务的UNDO信息将不再备份，这个特性随RMAN强制启用，看来这一特性的好处是不容置疑的。
--//测试看看：
1.环境：
SCOTT@book> @ &r/ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionSCOTT@book> create table t tablespace tea as select rownum id ,'abcdefghijklmnz'||rownum name from dual connect by level<=100;
Table created.SCOTT@book> set numw 12
SCOTT@book> select current_scn,sysdate from v$database ; CURRENT_SCN SYSDATE ------------ ------------------- 13244616644 2016-07-21 09:50:04SCOTT@book> update t set name='1234567890' ;
100 rows updated.SCOTT@book> commit ;
Commit complete. --我已经提交。SCOTT@book> select * from t as of scn 13244616644 where rownum<=1;
ID NAME ------------ -------------------- 1 abcdefghijklmnz1SYS@book> startup open read only ; ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes Variable Size 197133392 bytes Database Buffers 427819008 bytes Redo Buffers 7524352 bytes Database mounted. Database opened.SYS@book> select * from scott.t as of scn 13244616644 where rownum<=1;
ID NAME ---------- -------------------- 1 abcdefghijklmnz12.rman备份：
RMAN> report schema ;
using target database control file instead of recovery catalog Report of database schema for database with db_unique_name BOOKList of Permanent Datafiles
=========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 780 SYSTEM *** /mnt/ramdisk/book/system01.dbf 2 2110 SYSAUX *** /mnt/ramdisk/book/sysaux01.dbf 3 1435 UNDOTBS1 *** /mnt/ramdisk/book/undotbs01.dbf 4 200 USERS *** /mnt/ramdisk/book/users01.dbf 5 346 EXAMPLE *** /mnt/ramdisk/book/example01.dbf 6 100 SUGAR *** /mnt/ramdisk/book/sugar01.dbf 7 5 TEA *** /mnt/ramdisk/book/tea01.dbfList of Temporary Files
======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 100 TEMP 32767 /mnt/ramdisk/book/temp01.dbfRMAN> backup datafile 3 format '/home/oracle/backup/UNDOTBS1_%U';
Starting backup at 2016-07-21 09:53:43
allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=46 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=56 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=68 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00003 name=/mnt/ramdisk/book/undotbs01.dbf channel ORA_DISK_1: starting piece 1 at 2016-07-21 09:53:44 channel ORA_DISK_1: finished piece 1 at 2016-07-21 09:53:45 piece handle=/home/oracle/backup/UNDOTBS1_65rb8a18_1_1 tag=TAG20160721T095344 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2016-07-21 09:53:45$ du -sm /mnt/ramdisk/book/undotbs01.dbf
1438 /mnt/ramdisk/book/undotbs01.dbf $ du -sm /home/oracle/backup/UNDOTBS1_65rb8a18_1_1 6 /home/oracle/backup/UNDOTBS1_65rb8a18_1_1--可以发现备份仅仅6M很小。
$ strings /home/oracle/backup/UNDOTBS1_65rb8a18_1_1 | grep abcdefghijklmnz|wc 100 100 1865--说明实际上还在备份的。会不会与参数undo_retention有关。
SYS@book> show parameter undo NAME TYPE VALUE ---------------- -------- ---------- undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1--重启数据库，等900秒.....
SYS@book> select current_scn,sysdate from v$database ;
CURRENT_SCN SYSDATE ------------ ------------------- 13244618001 2016-07-21 10:25:18...备份发现相关信息依旧存在，继续等，说明以下我的机器是测试环境，业务很少。
SYS@book> select current_scn,sysdate from v$database ;
CURRENT_SCN SYSDATE ------------ ------------------- 13244621031 2016-07-21 11:29:28SYS@book> select * from scott.t as of scn 13244616644 where rownum<=1;
ID NAME ------------ -------------------- 1 abcdefghijklmnz1RMAN> backup datafile 3 format '/home/oracle/backup/UNDOTBS1_%U';
Starting backup at 2016-07-21 11:29:04 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00003 name=/mnt/ramdisk/book/undotbs01.dbf channel ORA_DISK_1: starting piece 1 at 2016-07-21 11:29:04 channel ORA_DISK_1: finished piece 1 at 2016-07-21 11:29:05 piece handle=/home/oracle/backup/UNDOTBS1_6crb8fk0_1_1 tag=TAG20160721T112904 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2016-07-21 11:29:05$ strings /home/oracle/backup/UNDOTBS1_6crb8fk0_1_1 | grep abcdefghijklmnz|wc
0 0 0--这样备份相关的信息已经不存在。无法找到abcdefghijklmnz字符串。
$ ls -l UNDOTBS1*
-rw-r----- 1 oracle oinstall 6127616 2016-07-21 10:32:28 UNDOTBS1_6arb8c9r_1_1 -rw-r----- 1 oracle oinstall 6397952 2016-07-21 11:06:24 UNDOTBS1_6brb8e9g_1_1 -rw-r----- 1 oracle oinstall 2940928 2016-07-21 11:29:04 UNDOTBS1_6crb8fk0_1_1--可以发现备份UNDOTBS1_6crb8fk0_1_1比原来小一些。
--//注：第一次备份集我已经删除了。 $ strings /home/oracle/backup/UNDOTBS1_6brb8e9g_1_1 | grep abcdefghijklmnz|wc 100 100 1865 --而11:06分的备份还存在。3.继续测试：
SYS@book> shutdown immediate ;
Database closed. Database dismounted. ORACLE instance shut down.SYS@book> startup open read only ;
ORACLE instance started. Total System Global Area 634732544 bytes Fixed Size 2255792 bytes Variable Size 197133392 bytes Database Buffers 427819008 bytes Redo Buffers 7524352 bytes Database mounted. Database opened. SYS@book> select * from scott.t as of scn 13244616644 where rownum<=1; ID NAME ------------ -------------------- 1 abcdefghijklmnz1--//可以发现查询使用as of scn依旧可以查询到历史数据。因为我这个数据库是测试数据库，业务很少，但是rman备份undo已经不备份这部分内容。
SYS@book> shutdown immediate ;
Database closed. Database dismounted. ORACLE instance shut down.--删除undo表空间恢复看看。
$ cd /mnt/ramdisk/book/ $ mv undotbs01.dbf undotbs01.dbfxSYS@book> alter database datafile 3 offline;
Database altered.RMAN> restore datafile 3;
Starting restore at 2016-07-21 11:37:56 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=12 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=24 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 00003 to /mnt/ramdisk/book/undotbs01.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/backup/UNDOTBS1_6crb8fk0_1_1 channel ORA_DISK_1: piece handle=/home/oracle/backup/UNDOTBS1_6crb8fk0_1_1 tag=TAG20160721T112904 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 Finished restore at 2016-07-21 11:38:00 --//注意取的备份集是最后一次备份。RMAN> recover datafile 3;
Starting recover at 2016-07-21 11:38:40 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 2016-07-21 11:38:40SYS@book> alter database datafile 3 online;
Database altered.SYS@book> alter database open read only ;
Database altered.SYS@book> select * from scott.t as of scn 13244616644 where rownum<=1;
select * from scott.t as of scn 13244616644 where rownum<=1 * ERROR at line 1: ORA-01555: snapshot too old: rollback segment number 8 with name "_SYSSMU8_517538920$" too smallSYS@book> select * from scott.t where rownum<=1;
ID NAME ---------- ---------------------------------------- 1 1234567890--由于还原的备份没有这部分内容，查询报错ORA-01555。
SYS@book> select (to_date('2016-07-21 11:29:28','yyyy-mm-dd hh24:mi:ss') - to_date('2016-07-21 09:50:04','yyyy-mm-dd hh24:mi:ss'))*86400 N20 from dual ; N20 ---------- 5964--过了5964秒，具体一些细节还是不是很清楚。
总结：
--提交后的undo相关信息实际还是会备份。至于等多久才不备份不是很清楚。oracle内部如何控制的还是不清楚。--补充再使用11:06分备份的undo来恢复的情况。
SYS@book> shutdown immediate ;
Database closed. Database dismounted. ORACLE instance shut down.RMAN> startup mount Oracle instance started database mounted Total System Global Area 634732544 bytes Fixed Size 2255792 bytes Variable Size 197133392 bytes Database Buffers 427819008 bytes Redo Buffers 7524352 bytes
$ mv UNDOTBS1_6crb8fk0_1_1 UNDOTBS1_6crb8fk0_1_1.OLD
SYS@book> alter database datafile 3 offline;
Database altered.RMAN> restore datafile 3 ;
Starting restore at 2016-07-21 11:57:04 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 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 00003 to /mnt/ramdisk/book/undotbs01.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/backup/UNDOTBS1_6crb8fk0_1_1 channel ORA_DISK_1: ORA-19870: error while restoring backup piece /home/oracle/backup/UNDOTBS1_6crb8fk0_1_1 ORA-19505: failed to identify file "/home/oracle/backup/UNDOTBS1_6crb8fk0_1_1" ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 failover to previous backup 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 00003 to /mnt/ramdisk/book/undotbs01.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/backup/UNDOTBS1_6brb8e9g_1_1 channel ORA_DISK_1: piece handle=/home/oracle/backup/UNDOTBS1_6brb8e9g_1_1 tag=TAG20160721T110624 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 Finished restore at 2016-07-21 11:57:07--//因为最后一个备份不在，使用/home/oracle/backup/UNDOTBS1_6brb8e9g_1_1备份集。
RMAN> recover datafile 3;
Starting recover at 2016-07-21 11:59:04 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 2016-07-21 11:59:04SYS@book> alter database open read only ;
Database altered.SYS@book> select * from scott.t as of scn 13244616644 where rownum<=1;
ID NAME ------------ -------------------------------------------------- 1 abcdefghijklmnz1SYS@book> select * from scott.t where rownum<=1;
ID NAME ------------ -------------------------------------------------- 1 1234567890 --这样有能查询到了。转载地址：http://dyzgl.baihongyu.com/