简单的flashback记录
flashback query~~~~~~~~~`查询遇到指定SCN以来发生变化的block 后进出rollback segment抽取所用undo数据回滚变化
利用undo
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO~~~必须要auto
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> create table t1 (a int);
Table created.
SQL> insert into t1 values(1);
1 row created.
SQL> insert into t1 values(2);
1 row created.
SQL> insert into t1 values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5776871
SQL> select * from t1;
A
----------
1
2
3
SQL> delete from t1;
3 rows deleted.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5777337
SQL> commit;
Commit complete.
SQL> select * from t1 ;
no rows selected
SQL> select * from t1 as of scn 5776871;
A
----------
1
2
3
as of timestamp的 只能闪回5天内(database逻辑时间) 不能 闪回5 分钟内的更改,sys.smon_scn_time只存1440条记录,实际用as of timestamp也是转换为 scn(oracle自动)
9I 是5分钟,10G 是也是5分钟 ,不过10G 多了TIM_SCN_MAP通过这个字段可以scn和time的转换精确到6秒钟
SQL> select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss') from smon_scn_time;
SCN TO_CHAR(TIME_DP,'YY
---------- -------------------
5661263 2009-07-28 13:22:13
5661369 2009-07-28 13:27:24
5661487 2009-07-28 13:32:32
5661593 2009-07-28 13:37:39
5665742 2009-07-28 14:13:30
5665866 2009-07-28 14:18:34
5628746 2009-07-27 15:04:20
5678135 2009-08-29 14:54:01
5678147 2009-08-29 14:54:19
5678419 2009-08-29 14:59:29
5679018 2009-08-30 15:02:11~~~~~~~~~~~~~~~~~~~~~每5分钟更新下
SQL> select timestamp_to_scn(sysdate) from dual;
TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
5818027
SQL> desc smon_scn_time;
Name Null? Type
----------------------------------------- -------- ----------------------------
THREAD NUMBER
TIME_MP NUMBER
TIME_DP DATE
SCN_WRP NUMBER
SCN_BAS NUMBER
NUM_MAPPINGS NUMBER
TIM_SCN_MAP RAW(1200)*******
SCN NUMBER
ORIG_THREAD NUMBER~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~10G
SQL> select count(*) from smon_scn_time;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~10G 也不是
COUNT(*)
----------
1529
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5818887
SQL> select scn_to_timestamp(5818887) from dual;
SCN_TO_TIMESTAMP(5818887)
-------------------------------------------------------
29-7月 -09 03.44.59.000000000 下午
SQL> select scn_to_timestamp(5818886) from dual;
SCN_TO_TIMESTAMP(5818886)
-------------------------------------------------------
29-7月 -09 03.44.59.000000000 下午
SQL> select scn_to_timestamp(5818885) from dual;
SCN_TO_TIMESTAMP(5818885)
-------------------------------------------------------
29-7月 -09 03.44.56.000000000 下午~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~只有3秒的差距哦
SQL> delete from t1;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> select * from t1;
no rows selected
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;~~~~~~~~~~~~~~~~~~~~~~~~~~
TO_CHAR(SYSDATE,'YY
-------------------
2009-07-29 15:50:54
SQL> insert into t1 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
A
----------
1
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2009-07-29 15:51:18
SQL> select * from t1 as of timestamp to_timestamp('2009-07-29 15:50:54','yyyy-m
m-dd hh24:mi:ss');
no rows selected
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2009-07-29 15:52:40
SQL> insert into t1 values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
A
----------
1
2
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2009-07-29 15:53:47
SQL> select * from t1 as of timestamp to_timestamp('2009-07-29 15:51:18','yyyy-m
m-dd hh24:mi:ss');
A
----------
1
10G后 不在是不能闪回5分钟内的时间了
用dbms_flashback做下flashback query
SQL> conn / as sysdba
Connected.
SQL> select * from t1;
A
----------
1
2
SQL> desc dbms_flashback;
PROCEDURE DISABLE
PROCEDURE ENABLE_AT_SYSTEM_CHANGE_NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUERY_SCN NUMBER IN
PROCEDURE ENABLE_AT_TIME
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
QUERY_TIME TIMESTAMP IN
FUNCTION GET_SYSTEM_CHANGE_NUMBER RETURNS NUMBER
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5840315
SQL> update t1 set a=6;
2 rows updated.
SQL> commit;
Commit complete.
SQL> select * from t1;
A
----------
6
6
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5840351
SQL> execute dbms_flashback.enable_at_system_change_number(5840315);
BEGIN dbms_flashback.enable_at_system_change_number(5840315); END;
*
ERROR at line 1:
ORA-08185: Flashback not supported for user SYS~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~SYS USER 不支持
ORA-06512: at "SYS.DBMS_FLASHBACK", line 12
ORA-06512: at line 1
ORA-08185: Flashback not supported for user SYS
Cause: user logged on as SYS
Action: logon as a different (non SYS) user. ************************
SQL> conn xh/a831115
Connected.
SQL> execute dbms_flashback.enable_at_system_change_number(5840315); ~
PL/SQL procedure successfully completed.
SQL> select * from sys.t1;
A
----------
1
2
SQL>
SQL> execute dbms_flashback.enable_at_system_change_number(5840316);
BEGIN dbms_flashback.enable_at_system_change_number(5840316); END;~~~~~~~~~~~~~~~~~~要执行下一次必须先关了这次
*
ERROR at line 1:
ORA-08184: attempting to re-enable Flashback while in Flashback mode
ORA-06512: at "SYS.DBMS_FLASHBACK", line 12
ORA-06512: at line 1
SQL> execute dbms_flashback.disable
PL/SQL procedure successfully completed.
*****************flashback table (10G中才有)用undo
需要启动rowmovement功能(最早应该是为分区出现,意思就是把改变的分区KEY VALUE 的行移动到合适的分区,允许改变一个row的rowid)
CONN XH/A831115
SQL> create table t1 (a int);
Table created.
SQL> create table t2 (a int);
Table created.
SQL> insert into t1 values(1);
1 row created.
SQL> insert into t2 values(1);
1 row created.
SQL> select * from t1;
A
----------
1
SQL> select * from t2;
A
----------
1
SQL> select rowid from t1 union select rowid from t2;
ROWID
------------------
AAANNYAAEAAAAHMAAA
AAANNZAAEAAAAHUAAA
SQL> create index it1 on t1 (a);
Index created.
SQL> select table_name from user_indexes where index_name='IT1';
TABLE_NAME
------------------------------
T1
SQL> select row_movement from dba_TABLES where table_name IN ('T1','T2','T3');
ROW_MOVE
--------
DISABLED
DISABLED
SQL> alter table t1 enable row movement;
Table altered.
SQL> alter table t2 enable row movement;
Table altered.
SQL> select row_movement from dba_TABLES where table_name IN ('T1','T2');
ROW_MOVE
--------
ENABLED
ENABLED
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5863982
SQL> delete from t1;
1 row deleted.
SQL> drop index it1;
Index dropped.
SQL> truncate table t2;~~~~~~~~~~~~~~简单改数据字典,标记为可用
Table truncated.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5864225
SQL> alter session set events '10046 trace name context forever, level 1';
Session altered.
SQL> flashback table t2 to scn 5863982;
flashback table t2 to scn 5863982
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed~~~~~~~~~~~~~~~~~~~~~~~~~~~~这是必然的 ddl不产生undo(实际有也会产生一些 毕竟修改了数据字典)
SQL> select rowid from t1 union select rowid from t2;
ROWID
------------------
AAANNYAAEAAAAHMAAA~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> select * from t1;
A
----------
1
SQL> select table_name from user_indexes where index_name='IT1';~~~~~~~~~~~~~DDL 闪回不了
no rows selected
ORA-08189: cannot flashback the table because row movement is not enabled~~~~~~~~~如果flashback 前未开启row movement则 flashback时报错
SQL> alter table t1 disable row movement;
Table altered.
闪回删除 不是利用undo 而是 利用回收站
10G drop一个table 实际只是改了名字,数据还在,直到手动清楚,或者空间不够自动清除
SQL> select * from t2;
A
----------
1
SQL> col segment_name format a10
SQL> select segment_name,segment_type,bytes,blocks from dba_extents where segmen
T_name='T2';
SEGMENT_NA SEGMENT_TYPE BYTES BLOCKS
---------- ------------------ ---------- ----------
T2 TABLE 65536 8
SQL> drop table t2;
Table dropped.
SQL> show recycles;
SP2-0158: unknown SHOW option "recycles"
SQL> show recycle;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T2 BIN$+zCOgS++TLKtL3mH2R2fcg==$0 TABLE 2009-07-30:13:14:51
SQL> select segment_name,segment_type,bytes,blocks from dba_segments where segme
nT_name='BIN$+zCOgS++TLKtL3mH2R2fcg==$0';~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~***************Drop改了数据字典而已
SEGMENT_NA SEGMENT_TYPE BYTES BLOCKS
---------- ------------------ ---------- ----------
BIN$+zCOgS TABLE 65536 8
++TLKtL3mH
2R2fcg==$0
SQL> flashback table t2 to before drop;~~~~~~~~~~~~~~~~~~~
Flashback complete.
SQL> select segment_name,segment_type,bytes,blocks from dba_segments where segme
nT_name='BIN$+zCOgS++TLKtL3mH2R2fcg==$0';
no rows selected
SQL> select segment_name,segment_type,bytes,blocks from dba_extents where segmen
T_name='T2';
SEGMENT_NA SEGMENT_TYPE BYTES BLOCKS
---------- ------------------ ---------- ----------
T2 TABLE 65536 8
此时在show recycle那条已经没有了
SQL> desc user_recyclebin~~~~~~~~~~~~~~~~~~~~~~~~~~~~这个更详细些(dba_recyclebin)
Name Null? Type
----------------------------------------- -------- ---------------------------
OBJECT_NAME NOT NULL VARCHAR2(30)
ORIGINAL_NAME VARCHAR2(32)
OPERATION VARCHAR2(9)
TYPE VARCHAR2(25)
TS_NAME VARCHAR2(30)
CREATETIME VARCHAR2(19)
DROPTIME VARCHAR2(19)
DROPSCN NUMBER
PARTITION_NAME VARCHAR2(32)
CAN_UNDROP VARCHAR2(3)
CAN_PURGE VARCHAR2(3)
RELATED NOT NULL NUMBER
BASE_OBJECT NOT NULL NUMBER
PURGE_OBJECT NOT NULL NUMBER
SPACE NUMBER
SQL> purge recyclebin;~清除回收站
Recyclebin purged.
SQL> show recyclebin;
SQL>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~XH 中看回收站已经没有信息
SQL> show user
USER is "XH"
SQL> show recyclebin;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~这个清除 是针对当前USER在回收站中对象的信息
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$6/pYJDsWRzCjDRLnlA4SEA==$0 TABLE 2009-07-20:16:08:19
TEST1 BIN$GSwtviIGQemrOLj5Lyvv5Q==$0 TABLE 2009-07-22:14:21:18 sys中还有
SQL> show user
USER is "SYS"
conn yy/a123
SQL> create table t3 (a int);
Table created.
SQL> drop table t3;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T3 BIN$6hnYbEY7TmCE2M8PzMabnQ==$0 TABLE 2009-07-30:13:49:37
SQL> show user~~~~~~~~~~~~~另一个用户就看不到
USER is "XH"
SQL> show recyclebin;
SQL>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~XH USER在回收站看不到
SQL> show recyclebin;
SQL> purge tablespace users~~~~~~~~~~~~~~~~~~~~~~~~~XH 清除
2 ;
SQL> show user
USER is "YY"~~~~~~~~~~~~~~~~~~~~~~YY中还有,所以 purge是针对当前 user对象的(default)
SQL> select object_name,ts_name from user_recyclebin;
OBJECT_NAME TS_NAME
------------------------------ ------------------------------
BIN$6hnYbEY7TmCE2M8PzMabnQ==$0 USERS~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
purge table XX~~~~~~~~清除表当前user,object信息
purge tablespace XX~~~~~~~~~~~表空间当前user,object信息
purge tablespace XX USERxx~~~~~~~~~~~表空间中用户
PURGE INDEX~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~索引
SQL> purge user_recyclebin;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~删除recyclebin中当前user,object信息
Recyclebin purged.
SQL> purge dba_recyclebin;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~删除recyclebin中全部
DBA Recyclebin purged.
SQL> purge tablespace USERS user YY;(xh执行)这个可以清除别的USER的对象
Tablespace purged.
SQL> show user
USER is "YY"
SQL> select object_name,ts_name from user_recyclebin;
no rows selected
SQL>
SQL> flashback table t3 to before drop ;
flashback table t3 to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN~~~~~~~~~~~~~~~~~~~~~~~~~~~~删除后就不能删回了
**************************************
SQL> show user;
USER is "XH"
SQL> drop table t1;
Table dropped.
SQL> create table t1 (a int);
Table created.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T1 BIN$YRidh+ggQpmOKmZUlZ7Rsg==$0 TABLE 2009-07-30:14:57:03
SQL> flashback table t1 to before drop;
flashback table t1 to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object~~~~~~~~~~~~~~~~~~名字已经被用了
SQL> flashback table t1 to before drop rename to t3;~~~~~~~~~~用rename就可以了
Flashback complete.
SQL>
SQL> show recyclebin;
SQL>
******************************************
SQL> create table ty (a int);
Table created.
SQL> show user
USER is "YY"
SQL> show recyclebin
SQL> drop user yy cascade;
drop user yy cascade
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn / as sysdba
Connected.
SQL> drop user yy cascade;~~~~~~~~~~~~~~~~~~~~~~~~删除user,user中对象都被删除 并且不出现在recyclebin中(因为只能看当前USER 对象在recyclebin中状态,user都删了看
不了了)
User dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$6/pYJDsWRzCjDRLnlA4SEA==$0 TABLE 2009-07-20:16:08:19
TEST1 BIN$GSwtviIGQemrOLj5Lyvv5Q==$0 TABLE 2009-07-22:14:21:18
SQL>
****************************************
SQL> show user;
USER is "XH"
SQL> create index it1 on t1(a);
Index created.
SQL> select table_owner,table_name,index_name,index_type from user_indexes where
index_name='IT1';
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
XH T1
IT1 NORMAL
SQL> drop index it1;~~~~~~~~~~~~~单独删除index ,trigger不会出现在回收站
Index dropped.
SQL> show recyclebin;
SQL> show recyclebin
SQL> select * from user_recyclebin;
no rows selected
SQL> select table_owner,table_name,index_name,status from user_indexes where ind
ex_name='IT1';
no rows selected
SQL> select object_name,original_name,operation,type from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION
------------------------------ -------------------------------- ---------
TYPE
-------------------------
BIN$vVsu0Z44Sh6tB1nUfKRBHQ==$0 IT1 DROP~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~看见index信息
INDEX
BIN$udVsNyp+TjWL6wkmH9/vlA==$0 T1 DROP
TABLE
SQL> flashback table t1 to before drop;~~~~~~~~~~flashback该表后,表上的index,trigger也一起恢复了
Flashback complete.
SQL> select object_name,original_name,operation,type from user_recyclebin;
no rows selected
SQL> select table_owner,table_name,index_name,status from user_indexes where ind
ex_name='IT1';
no rows selected~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> select table_owner,table_name,index_name,status from user_indexes where tab
le_name='T1';
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
INDEX_NAME STATUS
------------------------------ --------
XH T1
BIN$vVsu0Z44Sh6tB1nUfKRBHQ==$0 VALID~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~INDEX是可以使用的 只不过名字 太怪了(trigger同理)
SQL> set autotrace traceonly
SQL> select * from t1 where a=1;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=1 Card=1 Bytes=13)
1 0 INDEX (RANGE SCAN) OF 'BIN$vVsu0Z44Sh6tB1nUfKRBHQ==$0' (IN**********************************
DEX) (Cost=1 Card=1 Bytes=13)
drop tablespce XX~~~~~~~~~~~~之后 该表空间回收站中信息就都没了,因为实际数据已经删除
drop table XX PURGE~~~~~~~~~删除后 不出现在回收站,直接清除
SQL> show recyclebin
SQL> drop table t1 purge;
Table dropped.
SQL> show recyclebin
SQL>
********************************************
SQL> conn xh/a831115
Connected.
SQL> create table t1(a int);
Table created.
SQL> drop table t1 ;
Table dropped.
SQL> alter session set events '10046 trace name context forever;
alter session set events '10046 trace name context forever
*
ERROR at line 1:
ORA-01756: quoted string not properly terminated
SQL> alter session set events '10046 trace name context forever ,level 12';
Session altered.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T1 BIN$4G2IymfGRbCO8HnpuHbKyQ==$0 TABLE 2009-07-30:16:22:25
10046trace中
select text from view$ where rowid=:1
END OF STMT
PARSE #3:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=7315691492
BINDS #3:
bind 0: dty=11 mxl=16(16) mal=00 scl=00 pre=00 acflg=18 oacfl2=0001 size=16 ffset=0
bfp=0636b880 bln=16 avl=16 flg=05
value=00001933.0003.0001
SQL> select obj# from view$ where dbms_rowid.rowid_to_restricted(rowid,0)='00001
933.0003.0001';
OBJ#
----------
3668
SQL> select object_name,object_type from dba_objects where object_id=3668;
OBJECT_NAME
OBJECT_TYPE
-------------------------------------------------------------------------------
------------------------------------------------ -------------------
USER_RECYCLEBIN
VIEW~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~访问这个VIEW
SQL> select text from view$ where obj#=3668;
TEXT
--------------------------------------------------------------------------------
select o.name, r.original_name,
decode(r.operation, 0, 'DROP', 1, 'TRUNCA~~~~~~~~~~~~~~~~~~~~~~雪特显示不全
SELECT ORIGINAL_NAME ORIGNAME_PLUS_SHOW_RECYC,OBJECT_NAME OBJECTNAME_PLUS_SHOW_RECYC,TYPE OBJTYPE_PLUS_SHOW_RECYC,DROPTIME DROPTIME_PLUS_SHOW_RECYC FROM
USER_RECYCLEBIN ~~~~~~~~~简单记录查询user_recyclebin(not dba_recyblebin)
SQL> conn xh/a831115
Connected.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T1 BIN$4G2IymfGRbCO8HnpuHbKyQ==$0 TABLE 2009-07-30:16:22:25
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> purge recyclebin
2 ;
Recyclebin purged.
10046TRACE
select obj# from RecycleBin$ where owner#=:1 and to_number(bitand(flags, 4)) = 4
END OF STMT
PARSE #3:c=0,e=58,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=7658078270
BINDS #3:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 acflg=00 oacfl2=0001 size=24 ffset=0
bfp=04038a18 bln=22 avl=02 flg=05
value=68~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
END OF STMT
PARSE #5:c=0,e=62,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=7658096868
BINDS #5:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 acflg=08 oacfl2=0001 size=24 ffset=0
bfp=040387d8 bln=22 avl=04 flg=05
value=5411*****************************************************************
drop table "XH"."BIN$4G2IymfGRbCO8HnpuHbKyQ==$0" purge******************************************
上面比较乱由于view ,基表等 以后会有专门的实验 分析数据字典
一个小补充
SQL> conn xh/a831115
Connected.
SQL> select * from t1;
no rows selected
SQL> select original_name,dropscn from user_recyclebin;
ORIGINAL_NAME DROPSCN
-------------------------------- ----------
T1 5887400~~~~~~~~~~~~~~~~~~~~~~~~
SQL> create table t1 (a int);
Table created.
SQL> drop table t1;
Table dropped.
SQL> select original_name,dropscn from user_recyclebin;
ORIGINAL_NAME DROPSCN
-------------------------------- ----------
T1 5887400~~~~~~~~~~~~~~~~~~~~~~~~同名的object
T1 5887423
SQL> create table t1 (a int);
Table created.
SQL> select original_name,dropscn from user_recyclebin;
ORIGINAL_NAME DROPSCN
-------------------------------- ----------
T1 5887400
T1 5887423
SQL> drop table t1;
Table dropped.
SQL> select original_name,dropscn from user_recyclebin order by dropscn;
ORIGINAL_NAME DROPSCN
-------------------------------- ----------
T1 5887400
T1 5887423
T1 5887443
SQL> purge table t1;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~删除时候删除drop scn最小的(最老的)
Table purged.
SQL> select original_name,dropscn from user_recyclebin order by dropscn;
ORIGINAL_NAME DROPSCN
-------------------------------- ----------
T1 5887423
T1 5887443
Flashback complete.
SQL> select original_name,dropscn from user_recyclebin order by dropscn;~~~~~~~~~~~~~~flashback时用最新的
ORIGINAL_NAME DROPSCN
-------------------------------- ----------
T1 5887423
理由太简单了，最早删除后的 又重新建立新的 再删除,那么最早的已经完全没用了 先删这dropscn最老的(最小),由于user_recyclebin对于同一个object名 多次drop不会只记录
一次(每次object名一样但 结果内容 可能完全不一样),而flashback要最新的 用dropscn最新的(最大)
***********************************************************************************
闪回版本查询
10G 看2个时间点间的信息 依赖与undo 覆盖了就不行了 undo_retention是一个建议性参数,~~如果实在没有空间了还是会覆盖(具体有4种情况 会在装备分析undo的实验中解释)
所以将UNDO表空间 设置为guarantee
alter tablespace xx guarantee保证~~不会覆盖(未过undo_retention )
实验
SQL> show user;
USER is "XH"
SQL> create table t1 (a int);
Table created.
SQL> insert into test values(1);
insert into test values(1)
*
ERROR at line 1:
ORA-00947: not enough values
SQL> insert into t1 values(1);
1 row created.
SQL> insert into t1 values(2);
1 row created.
SQL> insert into t1 values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> update t1 set a=6;
3 rows updated.
SQL> commit;
Commit complete.
SQL> delete from t1;
3 rows deleted.
SQL> select versions_startscn,versions_endscn,versions_xid,versions_operation fr
om t1 versions between scn minvalue and maxvalue;
VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V
----------------- --------------- ---------------- -
5875290 04001B00720B0000 U
5875290 04001B00720B0000 U
5875290 04001B00720B0000 U NULL 这段时间没操作,没有最后的endscn(endtime)
5875283 5875290 04002300720B0000 I
5875283 5875290 04002300720B0000 I
5875283 5875290 04002300720B0000 I
6 rows selected.
commit刚才的del(未提交的不会记录)
SQL> select versions_startscn,versions_endscn,versions_xid,versions_operation fr
om t1 versions between scn minvalue and maxvalue;
VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V
----------------- --------------- ---------------- -
5875551 04000B00730B0000 D~~~~~~~~~~~~~~~~~~~************因为del 3rows
5875551 04000B00730B0000 D
5875551 04000B00730B0000 D
5875290 5875551 04001B00720B0000 U
5875290 5875551 04001B00720B0000 U~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~update 3rows
5875290 5875551 04001B00720B0000 U
5875283 5875290 04002300720B0000 I
5875283 5875290 04002300720B0000 I
5875283 5875290 04002300720B0000 I
从语法上 基本都能看懂 versions_stattime ,endtime I=inser,u=update,d=delete 只记录DML
VERSIONS_XID事务操作的ID 可以关联v$flashback_transaction_query
VERSIONS_ENDSCN (endtime) NULL 该记录失效时的scn 或时间，如果为空，说明记录当前时间在当前表内存在或者已经被删除了，应该配合着VERSIONS_OPERATION 列来看，如果
VERSIONS_OPERATION 列值为D，说明该列已被删除，如果该列为空，说明记录在这段时间无操作。
minvalue oracle 检查undo segment中最早信息 maxvalue 当前SCN OR TIME
SQL> desc flashback_transaction_query;
Name Null? Type
----------------------------------------- -------- ----------------------------
XID RAW(8)
START_SCN NUMBER
START_TIMESTAMP DATE
COMMIT_SCN NUMBER
COMMIT_TIMESTAMP DATE
LOGON_USER VARCHAR2(30)
UNDO_CHANGE# NUMBER
OPERATION VARCHAR2(32)
TABLE_NAME VARCHAR2(256)
TABLE_OWNER VARCHAR2(32)
ROW_ID VARCHAR2(19)
UNDO_SQL VARCHAR2(4000)
XID RAW(8) Transaction identifier
START_SCN NUMBER Transaction start system change number (SCN)
START_TIMESTAMP DATE Transaction start timestamp
COMMIT_SCN NUMBER Transaction commit system change number; NULL for active transactions
COMMIT_TIMESTAMP DATE Transaction commit timestamp; NULL for active transactions
LOGON_USER VARCHAR2(30) Logon user for the transaction
UNDO_CHANGE# NUMBER Undo system change number (1 or higher) ***************************
OPERATION VARCHAR2(32) Forward-going DML operation performed by the transaction:
D - Delete
I - Insert
U - Update
B
UNKNOWN
TABLE_NAME VARCHAR2(256) Name of the table to which the DML applies
TABLE_OWNER VARCHAR2(32) Owner of the table to which the DML applies
ROW_ID VARCHAR2(19) Rowid of the row that was modified by the DML
UNDO_SQL VARCHAR2(4000) SQL to undo the DML indicated by OPERATION
SQL> select START_SCN ,COMMIT_SCN,LOGON_USER,undo_change#,operation ,undo_sql fr
om flashback_transaction_query where xid='04000B00730B0000';
START_SCN COMMIT_SCN LOGON_USER UNDO_CHANGE# OPERATION
---------- ---------- ---------- ------------ --------------------------------
UNDO_SQL
--------------------------------------------------------------------------------
5875290 5875551 XH 1 DELETE
insert into "XH"."T1"("A") values ('6');
5875290 5875551 XH 2 DELETE~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~3rows~3条insert
insert into "XH"."T1"("A") values ('6');
5875290 5875551 XH 3 DELETE
insert into "XH"."T1"("A") values ('6');
START_SCN COMMIT_SCN LOGON_USER UNDO_CHANGE# OPERATION
---------- ---------- ---------- ------------ --------------------------------
UNDO_SQL
--------------------------------------------------------------------------------
5875290 5875551 XH 4 BEGI
有点audit与logmnr的功能,短时间使用还行~~~~~~~~~因为受undo影响~~覆盖了也就没了
*******************************
flashback database
需要archivelog mode
简单介绍下原理:起用flashback database 后,变化的block (dirty block)不断从database buffer_cache中复制到SGA中一个叫flash buffer的新区
一个新的后台进程rvwr将flashback buffer写入disk(flashback log),与redolog有区别,flashback log是完整的数据块印象日志 (redo是变化日志)
flashback log自动创建管理(flashbackt database off 时自己删除)
flachback buffer大小oracle 自动控制(有一套自己的算法) 保证不影响database性能
flashback database时 oracle 读flashback buffer来抽取每个已变化数据块的版本,将这些版本写入datafile,不是所有的已变化block 的所有版本些到flashback buffer和
flashblack log,也许某个块改了多次,但flashback log只记录了一部分,所以database无法闪回到确切的时间点
例:a块 9:00 发生变化,9:10又变化了 但flachback log只记录了9:00变化
B块 9:15 9:20发生2次变化~~~~都记录到flachback log现在要闪回到9:18 用a 9:00版本 B 9:15版本(选最近的 到恢复时间 但不能是恢复时间后的)这造成了不同的块有不同的
SCN,接着oracle 用redolog(or archive log)恢复 将所有block恢复到相同时间点, 同步到相同的SCN最后rollback 未提交事务 跟不完全恢复一样 最后resetlog open
与不完全恢复比 flaschback更块些 速度是与修改的块多少成正比(改块越多 要时间越长),不完全恢复 与数据库大小有关系成正比 越大 恢复时间越长
但是介质恢复 数据文件损坏丢失 它就无能为力了
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> shutdown immediate;~~startup force mount不行~~~~~~因为数据库不同步
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;~~~~~~~~~~~~~~~~~必须mount下
ORACLE instance started.
Total System Global Area 188743680 bytes
Fixed Size 788068 bytes
Variable Size 145488284 bytes
Database Buffers 41943040 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> select FLASHBACK_ON from v$database;
FLA
---
YES~~~~~~~
SQL> select * from v$sgastat where name like '%flashback%';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool flashback generation buff 1449104~~~~~~~~~~~~~~~~~~~~~~~~~~~~多了这个 flashback buffer DBA 无法控制
SQL> select description from v$bgprocess where name='RVWR';~~~~~~~~~~~~~~~~~新进程有了
DESCRIPTION
----------------------------------------------------------------
Recovery Writer
SQL> desc v$flashback_database_log;
Name Null? Type
----------------------------------------- -------- ----------------------------
OLDEST_FLASHBACK_SCN NUMBER 保留的最低系统改变号
OLDEST_FLASHBACK_TIME DATE 最低系统改变号的时间
RETENTION_TARGET NUMBER 闪回日志保留时间（单位：时间分钟）
FLASHBACK_SIZE NUMBER 当前闪回日志的大小（单位：字节）
ESTIMATED_FLASHBACK_SIZE NUMBER 预估满足保留时间所需要的空间大小（单位：字节）
SQL> select retention_target,flashback_size,estimated_flashback_size from v$flas
hback_database_log;
RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
---------------- -------------- ------------------------
1440 8192000 0**************运行段时间
SQL> show parameter flash
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~保留时间
SQL>
SQL> select retention_target,flashback_size/1024/1024,estimated_flashback_size/1
024/1024 from v$flashback_database_log;
RETENTION_TARGET FLASHBACK_SIZE/1024/1024 ESTIMATED_FLASHBACK_SIZE/1024/1024
---------------- ------------------------ ----------------------------------
1440 7.8125 103.382813
现在只有8M 如果保留一天大概要104M
SQL> select oldest_flashback_scn,to_char(oldest_flashback_time,'yyyy-mm-dd hh24:
mi:ss') from v$flashback_database_log;
OLDEST_FLASHBACK_SCN TO_CHAR(OLDEST_FLAS
-------------------- -------------------
5910817 2009-07-31 10:52:46~~~~~~~~~~~~~~~~~~~~能闪回的最早SCN 与时间
SQL> desc v$flashback_database_stat;
Name Null? Type
----------------------------------------- -------- ----------------------------
BEGIN_TIME DATE 时间间隔的开始
END_TIME DATE 时间间隔的结束
FLASHBACK_DATA NUMBER 此时间间隔内写闪回日志大小（单位：字节）
DB_DATA NUMBER 在此时间间隔内数据库读写大小（单位：字节）
REDO_DATA NUMBER 在此时间间隔内输出在线日志的大小（单位：字节）
ESTIMATED_FLASHBACK_SIZE NUMBER 在时间间隔的结束时预估满足保留时间所需要的空间大小（单位：字节
SQL> select end_time,flashback_data,db_data,redo_data from v$flashback_database_
stat;
END_TIME FLASHBACK_DATA DB_DATA REDO_DATA
-------------- -------------- ---------- ----------
31-7月 -09 1589248 1851392 275968~~~~~~~~~~~~~~~~~~~~~~一般是近似1小时为间隔 记录
实验
SQL> conn xh/a831115
Connected.
SQL> select * from t1;
no rows selected
SQL> conn / as sysdba
Connected.
SQL> select * from test;
A
----------
1
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5912310
SQL> drop user xh cascade;
User dropped.
SQL> drop table test;
Table dropped.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 188743680 bytes
Fixed Size 788068 bytes
Variable Size 145488284 bytes
Database Buffers 41943040 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL> flashback database to scn 5912310;
Flashback complete.
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
5912897 SYSTEM SCN来自controlfile
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
5912897
5912897
5912897
5912897
5912897 DATAFILE SCN 来自controlfile
5912897
5912897
5912897
5912897
9 rows selected.
SQL> select last_change# from v$datafile;
LAST_CHANGE#
------------
5912897
5912897
5912897
5912897 END SCN 来自controlfile
5912897
5912897
5912897
5912897
5912897
9 rows selected.
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
5912313
5912313
5912313
5912313 startscn 来自datafile header
5912313
5912313
5912313
5912313
5912313
9 rows selected.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~可以看出来控制文件和 数据文件不同步,控制文件新,而数据文件已经闪回 所以resetlogs同步下 改变log file sequence SCN+1
SQL>
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [2738], [2], [], [], [], [], [], []~~~~~~~~~~~~雪特雪特 600
SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done~~~~~~~~~~~~~~~~雪特
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 188743680 bytes
Fixed Size 788068 bytes
Variable Size 145488284 bytes
Database Buffers 41943040 bytes
Redo Buffers 524288 bytes
~~~~~~~~alter database backup controlfile to trace
SQL> CREATE CONTROLFILE REUSE DATABASE "XHTEST" NORESETLOGS FORCE LOGGING ARCHIV
ELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 454
7 LOGFILE
8 GROUP 1 (
9 'E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/REDO01.LOG',
10 'E:/REDO01_2.LOG',
11 'E:/REDO01_3.LOG'
12 ) SIZE 10M,
13 GROUP 2 'E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/REDO02.LOG' SIZE 10M,
14 GROUP 3 'E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/REDO03.LOG' SIZE 10M,
15 GROUP 4 (
16 'D:/REDO04_1.LOG'
17 ) SIZE 10M
18 -- STANDBY LOGFILE
19 DATAFILE
20 'E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/SYSTEM01.DBF',
21 'E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/UNDOTBS01.DBF',
22 'E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/SYSAUX01.DBF',
23 'E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/USERS01.DBF',
24 'E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/EXAMPLE01.DBF',
25 'E:/TEST.DBF',
26 'E:/TEST2.DBF',
27 'E:/TEST3.DBF',
28 'E:/TEST4.DBF'
29 CHARACTER SET ZHS16GBK
30 ;
CREATE CONTROLFILE REUSE DATABASE "XHTEST" NORESETLOGS FORCE LOGGING ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01189: file is from a different RESETLOGS than previous files~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ NORESETLOG datafile checkpoint来自current
logfile~~~~
ORA-01517: log member: 'E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/REDO01.LOG'~~~~~~~~~~~~~~redo损坏了
SQL> ed
Wrote file afiedt.buf
1 CREATE CONTROLFILE REUSE DATABASE "XHTEST" RESETLOGS FORCE LOGGING ARCHIVE~~~~~~~~~~~~~~~resetlog datafile checkpoint来自datafileheader
LOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 454
7 LOGFILE
8 GROUP 1 (
9 'E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/REDO01.LOG',
10 'E:/REDO01_2.LOG',
11 'E:/REDO01_3.LOG'
12 ) SIZE 10M,
13 GROUP 2 'E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/REDO02.LOG' SIZE 10M,
14 GROUP 3 'E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/REDO03.LOG' SIZE 10M,
15 GROUP 4 (
16 'D:/REDO04_1.LOG'
17 ) SIZE 10M
18 -- STANDBY LOGFILE
19 DATAFILE
20 'E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/SYSTEM01.DBF',
21 'E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/UNDOTBS01.DBF',
22 'E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/SYSAUX01.DBF',
23 'E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/USERS01.DBF',
24 'E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORCL/EXAMPLE01.DBF',
25 'E:/TEST.DBF',
26 'E:/TEST2.DBF',
27 'E:/TEST3.DBF',
28 'E:/TEST4.DBF'
29* CHARACTER SET ZHS16GBK
30 ;
Control file created.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs; 数据都恢复了 ,看样子问题出在redo 重新建立下controlfile就恢复OK了,数据都恢复是因为flashback 已经完成 建立控制文
件的datafile checkepoint 来自 datafile header , open resetlogs会重设REDO 序号为1 删除current online logfile中未应用的重做记录
Database altered.
*************************************~*************重新来一次
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL> select flashback_on from v$database;
FLA
---
NO
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> select * from test;~~~~~~~~~~~~~~~~~~~
A
----------
1
SQL> conn xh/a831115~~~~~~~~~~~~~~~~~~~~~~~~~~
Connected.
SQL> select * from t1;
no rows selected
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5914362
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5914366
SQL> conn / as sysdba
Connected.
SQL> drop user xh cascade;
User dropped.
SQL> create index itest on test(a);
Index created.
SQL> select index_name from user_indexes where index_name='ITEST';
INDEX_NAME
------------------------------
ITEST
SQL> select index_name,status from user_indexes where index_name='ITEST';
INDEX_NAME STATUS
------------------------------ --------
ITEST VALID
SQL> drop table test;
Table dropped.
SQL> select index_name,status from user_indexes where index_name='ITEST';
no rows selected
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5914609
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 188743680 bytes
Fixed Size 788068 bytes
Variable Size 145488284 bytes
Database Buffers 41943040 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL> flashback database to scn 5914362;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from test;
A
----------
1
SQL> select index_name,status from user_indexes where index_name='ITEST';
no rows selected
SQL> conn xh/a831115
Connected.
SQL> select * from t1;
no rows selected
SQL> conn / as sysdba
Connected.
SQL> select index_name,status from user_indexes where table_name='TEST';
no rows selected~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~忘了 create index是在恢复到的SCN 后面 drop 的user及其对象 和DROP table已经恢复
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 188743680 bytes
Fixed Size 788068 bytes
Variable Size 145488284 bytes
Database Buffers 41943040 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL> alter database flashback off
2 ;
Database altered.
SQL> alter database open;
Database altered.
SQL> select flashback_on from v$database;
FLA
---
NO
SQL> select retention_target,flashback_size,estimated_flashback_size from v$flas~~~~~~~~~~~~~~~~~~~~~~闪回日志自己删除
hback_database_log;
no rows selected
SQL> select * from v$sgastat where name like '%flashback%';~~~~~~~~~~flashback buffer也没了
no rows selected
另外还有闪回点(10GR2)
1.create restore point XX ,flashback该点就可以(要用到flashback log )~作为SCN的一个别名
2. guaranteed restore point () CREATERESTOREPOINT XX GUARANTEEFLASHBACKDATABASE 在不开flashback log时候 也可以 闪回,guaranteed restore point后对于变更都会记录下来
查v$restore_point
rman中也可执行
flashback database to time=
to scn=
to sequence=
扫一扫
1542
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
