ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 (flashback)

oracle实验记录 (flashback)

原创 Linux操作系统 作者:fufuh2o 时间:2009-07-31 13:49:28 0 删除 编辑

简单的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=

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12020513/viewspace-610983/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    427794