[20160820]11g Restore Point Enhancements.txt
--今天看了11g的文档,发现11g Restore Point的一些增强功能,可以建立特定scn或者时间的restore point,做一个记录:
1.环境:
SYS@test> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
--//家里仅仅有12c,应该也能说明问题:
SYS@test> select name, preserved from V$RESTORE_POINT;
no rows selected
SYS@test> select current_scn from v$database;
CURRENT_SCN
-----------
23979284
2.可以建立特定scn或者时间的restore point:
SYS@test> create restore point restore1 as of scn 20979284;
Restore point created.
--//注意scn=20979284,与当前相差很远.
SYS@test> select * from V$RESTORE_POINT;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME RESTORE_POINT_TIME PRE NAME CON_ID
---------- --------------------- --- ------------ ------------------------------ ------------------------------ --- -------------------- ----------
20979284 2 NO 0 2016-08-20 22:25:43.000000000 NO RESTORE1 0
SYS@test> create restore point restore2 as of scn 25979284;
create restore point restore2 as of scn 25979284
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'RESTORE2'.
ORA-38865: cannot create restore point for a future SCN or time.
--也可以建立特定时间点的restore point:
SYS@test> create restore point restore2 as of timestamp date'2016-08-19';
Restore point created.
SYS@test> select * from V$RESTORE_POINT;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME RESTORE_POINT_TIME PRE NAME CON_ID
---------- --------------------- --- ------------ ------------------------------ ------------------------------ --- -------------------- ----------
20979284 2 NO 0 2016-08-20 22:25:43.000000000 NO RESTORE1 0
23946650 2 NO 0 2016-08-20 22:29:14.000000000 2016-08-19 00:00:00.000000000 NO RESTORE2 0
3.还可以建立restore point属性preserve(应该不常用):
SYS@test> create restore point restore3 as of scn 23979284 preserve;
Restore point created.
SYS@test> select * from V$RESTORE_POINT;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME RESTORE_POINT_TIME PRE NAME CON_ID
---------- --------------------- --- ------------ ------------------------------ ------------------------------ --- -------------------- ----------
23979284 2 NO 0 2016-08-20 22:32:51.000000000 YES RESTORE3 0
20979284 2 NO 0 2016-08-20 22:25:43.000000000 NO RESTORE1 0
23946650 2 NO 0 2016-08-20 22:29:14.000000000 2016-08-19 00:00:00.000000000 NO RESTORE2 0
4.收尾:
SYS@test> drop restore point restore1;
Restore point dropped.
SYS@test> drop restore point restore2;
Restore point dropped.
SYS@test> drop restore point restore3;
Restore point dropped.
5.补充测试:
SYS@test> create restore point restore2 as of timestamp date'2016-08-01';
Restore point created.
--ok 可以设置!
SYS@test> create restore point restore3 as of timestamp date'2015-08-01';
create restore point restore3 as of timestamp date'2015-08-01'
*
ERROR at line 1:
ORA-08180: no snapshot found based on specified time
SYS@test> host oerr ora 8180
08180, 00000, "no snapshot found based on specified time"
// *Cause: Could not match the time to an SCN from the mapping table.
// *Action: try using a larger time.
SYS@test> select * from V$RESTORE_POINT;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME RESTORE_POINT_TIME PRE NAME CON_ID
---------- --------------------- --- ------------ ------------------------------ ------------------------------ --- -------------------- ----------
23596587 2 NO 0 2016-08-21 21:36:38.000000000 2016-08-01 00:00:00.000000000 NO RESTORE2 0
--从这里可以看出使用时间实际上也是转化为scn.
--可以指定时间如果超出范围,出现在ORA-08180: no snapshot found based on specified time.
--做1个10046跟踪看看:
SYS@test> @ 10046on 12
old 1: alter session set events '10046 trace name context forever, level &1'
new 1: alter session set events '10046 trace name context forever, level 12'
Session altered.
SYS@test> create restore point restore3 as of timestamp date'2015-08-01';
create restore point restore3 as of timestamp date'2015-08-01'
*
ERROR at line 1:
ORA-08180: no snapshot found based on specified time
SYS@test> @ 10046off
Session altered.
--//检查跟踪文件,可以发现:
=====================
PARSING IN CURSOR #496279672 len=151 dep=1 uid=0 oct=3 lid=0 tim=5974906174 hv=665645835 ad='7ff1eb4f6f0' sqlid='5jgckj4mutwsb'
select time_mp, scn, num_mappings, tim_scn_map from smon_scn_time where time_mp = (select max(time_mp) from smon_scn_time where time_mp <= :1)
END OF STMT
PARSE #496279672:c=0,e=625,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=5974906173
BINDS #496279672:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=1a0e6b38 bln=22 avl=05 flg=05
value=1438358400
SYS@test> select time_mp, scn, num_mappings, tim_scn_map from smon_scn_time where time_mp= (select max(time_mp) from smon_scn_time where time_mp <= 1438358400);
no rows selected
SYS@test> SELECT DATE '1970-01-01' + time_mp / 86400, time_dp FROM smon_scn_time where rownum<=10;
DATE'1970-01-01'+TI TIME_DP
------------------- -------------------
2016-05-22 13:31:43 2016-05-22 13:31:43
2016-05-22 13:36:45 2016-05-22 13:36:45
2016-05-22 13:41:48 2016-05-22 13:41:48
2016-05-22 13:46:51 2016-05-22 13:46:51
2016-05-22 13:51:52 2016-05-22 13:51:52
2016-05-22 13:56:55 2016-05-22 13:56:55
2016-03-14 13:37:40 2016-03-14 13:37:40
2016-03-14 13:42:40 2016-03-14 13:42:40
2016-03-14 13:47:44 2016-03-14 13:47:44
2016-03-14 13:52:47 2016-03-14 13:52:47
10 rows selected.
--可以发现time_mp实际上从1970/1/1的秒数.
--但是1438358400实际上是:
SYS@test> select date'1970-01-01'+1438358400/86400 from dual ;
DATE'1970-01-01'+14
-------------------
2015-07-31 16:00:00
--相差8个小时.为什么?也就是oracle要取不能取最小时间,而必须相差8个小时.
--可以发现最小可以设置:
SYS@test> column TIM_SCN_MAP noprint
select * from smon_scn_time where scn=(select min(scn) from smon_scn_time)
union all
select * from smon_scn_time where time_dp=(select min(time_dp) from smon_scn_time);
THREAD TIME_MP TIME_DP SCN_WRP SCN_BAS NUM_MAPPINGS SCN ORIG_THREAD
---------- ---------- ------------------- ---------- ---------- ------------ ---------- -----------
0 1451313130 2015-12-28 14:32:10 0 21259794 95 21259794 0
0 1451313130 2015-12-28 14:32:10 0 21259794 95 21259794 0
--//马上执行如下,不然这个最小的TIME_DP会删除的.
SYS@test> create restore point restore3 as of timestamp to_date('&x','yyyy-mm-dd hh24;mi:ss');
Enter value for x: 2015-12-28 22:32:10
old 1: create restore point restore3 as of timestamp to_date('&x','yyyy-mm-dd hh24;mi:ss')
new 1: create restore point restore3 as of timestamp to_date('2015-12-28 22:32:10','yyyy-mm-dd hh24;mi:ss')
Restore point created.
--比上面输入参数少1秒.
SYS@test> create restore point restore4 as of timestamp to_date('&x','yyyy-mm-dd hh24;mi:ss');
Enter value for x: 2015-12-28 22:32:09
old 1: create restore point restore4 as of timestamp to_date('&x','yyyy-mm-dd hh24;mi:ss')
new 1: create restore point restore4 as of timestamp to_date('2015-12-28 22:32:09','yyyy-mm-dd hh24;mi:ss')
create restore point restore4 as of timestamp to_date('2015-12-28 22:32:09','yyyy-mm-dd hh24;mi:ss')
*
ERROR at line 1:
ORA-08180: no snapshot found based on specified time
SYS@test> select * from V$RESTORE_POINT;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME RESTORE_POINT_TIME PRE NAME CON_ID
---------- --------------------- --- ------------ ------------------------------ ------------------------------ --- -------------------- ----------
23596587 2 NO 0 2016-08-21 21:36:38.000000000 2016-08-01 00:00:00.000000000 NO RESTORE2 0
21259794 2 NO 0 2016-08-21 22:32:50.000000000 2015-12-28 22:32:10.000000000 NO RESTORE3 0
--但是scn不受这个限制:
SYS@test> create restore point restore4 as of scn 21259790;
Restore point created.
SYS@test> create restore point restore5 as of scn 20259790;
Restore point created.
SYS@test> select * from V$RESTORE_POINT;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME RESTORE_POINT_TIME PRE NAME CON_ID
---------- --------------------- --- ------------ ------------------------------ ------------------------------ --- -------------------- ----------
23596587 2 NO 0 2016-08-21 21:36:38.000000000 2016-08-01 00:00:00.000000000 NO RESTORE2 0
21259794 2 NO 0 2016-08-21 22:32:50.000000000 2015-12-28 22:32:10.000000000 NO RESTORE3 0
21259790 2 NO 0 2016-08-21 22:36:56.000000000 NO RESTORE4 0
20259790 2 NO 0 2016-08-21 22:37:05.000000000 NO RESTORE5 0
--你可以看出使用时间的话,存在8个小时的偏差,是bug吗?
--scn=21259794,实际上时间是 2015-12-28 14:32:10.
--上班补充测试在11g也是这种情况。不在贴出
SYS@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
总结：
1.其实这些东西很少用。
2.实际上使用时间作为restore point，实际上是通过表smon_scn_time获取scn的，真正的restore point还是scn，并且由于采样的原因
，存在一定的偏差，一般不建议采用，而是如果不在这个表的范围，或者讲没有对照，报ORA-08180: no snapshot found based on specified time。
3.而使用scn就不存在这个问题。
4.个人感觉相差8个小时应该是bug
