[20160820]11g Restore Point Enhancements

[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