[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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2123766/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- RESTORE POINTREST
- [20160822]11g Restore Point Enhancement2REST
- 閃回還原點restore pointREST
- restore point -特色的SCN標誌(一)REST
- Orace Flash Restore Point(閃回還原點)REST
- Oracle10GR2 中的RESTORE POINTOracleREST
- [20171204]guaranteed restore point.txtREST
- Partitioning Enhancements in Oracle Database 11g Release 1OracleDatabase
- restore point特色二&閃回資料庫特性(三)REST資料庫
- How to Create or Remove Restore Point on Standby database (文件 ID 1672977.1)REMRESTDatabase
- Resource Manager Enhancements in Oracle Database 11g (文件 ID 884082.1)OracleDatabase
- 6150680: Cannot Create Guaranteed Restore Point In Physical Standby DB_602270.1REST
- Oracle OCP 1Z0 053 Q390(Flashback database&Named restore point)OracleDatabaseREST
- 11g asm md_backup md_restoreASMREST
- Tracing Enhancements Using DBMS_MONITOR (In 10g, 11g and Above)_293661.1
- oracle小知識點3--有保證的復原點guaranteed restore pointOracleREST
- archivelog模式和flashback db以及guarantee restore point之間的相互制約關係!Hive模式REST
- db_recovery_file_dest_size, v$flashback_database_logfile,v$restore_point引發的血案.DatabaseREST
- Tracing Enhancements Using DBMS_MONITOR
- Tablespace Management Enhancements in Oracle Database 10gOracleDatabase
- guarantee restore points-Flashback after RMAN restoreREST
- canvas restore()CanvasREST
- SQL*Plus Enhancements in Oracle Database 10g(一)SQLOracleDatabase
- Wait Event Enhancements in Oracle 10g(zt)AIOracle 10g
- JavaScript some pointJavaScript
- study critical point and saddle point using Hessian Matrix
- Flashback New Features and Enhancements in Oracle Database 10gOracleDatabase
- Restore ArchivelogRESTHive
- DOCKER特性 - LIVE RESTOREDockerREST
- canvas save()和restore()CanvasREST
- SQL Server Backup & RestoreSQLServerREST
- How restore CBO statisticsREST
- TFS Express backup and restoreExpressREST
- backup and restore tipsREST
- TMS request queue restoreREST
- Windows Embedded for Point of Service概述Windows
- Windows Embedded for Point of Service 益處Windows
- 收藏What’s the Point of Oracle Checkpoints?Oracle