[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 Production
SCOTT@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:04
SCOTT@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 abcdefghijklmnz1
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
2.rman备份：
RMAN> report schema ;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name BOOK
List 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.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 100 TEMP 32767 /mnt/ramdisk/book/temp01.dbf
RMAN> 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:28
SYS@book> select * from scott.t as of scn 13244616644 where rownum<=1;
ID NAME
------------ --------------------
1 abcdefghijklmnz1
RMAN> 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.dbfx
SYS@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:40
SYS@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 small
SYS@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:04
SYS@book> alter database open read only ;
Database altered.
SYS@book> select * from scott.t as of scn 13244616644 where rownum<=1;
ID NAME
------------ --------------------------------------------------
1 abcdefghijklmnz1
SYS@book> select * from scott.t where rownum<=1;
ID NAME
------------ --------------------------------------------------
1 1234567890
--这样有能查询到了。
