首页 > 代码库 > oracle11G闪回flashback

oracle11G闪回flashback

2024-11-16 09:28:39   204人阅读



oracle11G闪回flashback
oracle9i的闪回：

      闪回查询从9i引入，可以按照时间点或者SCN向前查

询，获取修改 前的数据。

      闪回查询依赖于回滚段中存储的数据前镜像，通过设置

undo_retention参数设置前镜像的保留时间。

      查询的语法：

      select   …    as of scn | timestamp


Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as hr@JIAGULUN

SQL> select * from student;
         ID NAME                    AGE
----------- -------------------- -----------
          1 zhangsan                21
          2 lisi                           22
          3 wangwu                  23

SQL> select to_char(sysdate,‘yyyymmdd hh24:mi:ss‘) from dual;
TO_CHAR(SYSDATE,‘YYYYMMDDHH24:
------------------------------
20150124 17:58:58

SQL>
SQL> update student s set s.name=‘mazi‘ where s.id = 1;
1 row updated

SQL> select to_char(sysdate,‘yyyymmdd hh24:mi:ss‘) from dual;
TO_CHAR(SYSDATE,‘YYYYMMDDHH24:
------------------------------
20150124 18:02:56

SQL> delete from student s where s.name = ‘lisi‘;
1 row deleted

SQL> select * from student;
         ID NAME                    AGE
----------- -------------------- -----------
          1 mazi                       21
          3 wangwu                  23

SQL> select to_char(sysdate,‘yyyymmdd hh24:mi:ss‘) from dual;
TO_CHAR(SYSDATE,‘YYYYMMDDHH24:
------------------------------
20150124 18:04:00

SQL> delete from student;
2 rows deleted

SQL> select * from student;
         ID NAME                    AGE
----------- -------------------- -----------

SQL> select to_char(sysdate,‘yyyymmdd hh24:mi:ss‘) from dual;
TO_CHAR(SYSDATE,‘YYYYMMDDHH24:
------------------------------
20150124 18:04:26

SQL> select * from student as of scn 3099731;
         ID NAME                    AGE
----------- -------------------- -----------
          1 zhangsan                21
          2 lisi                           22
          3 wangwu                  23
 
SQL> select * from student;
         ID NAME                    AGE
----------- -------------------- -----------

SQL> commit;
Commit complete

SQL> insert into student values(1,‘zhangsan‘,23);
1 row inserted

SQL> commit;
Commit complete

SQL>
SQL>  select to_char(sysdate,‘yyyymmdd hh24:mi:ss‘) from dual;
TO_CHAR(SYSDATE,‘YYYYMMDDHH24:
------------------------------
20150124 19:36:30

SQL>
SQL> delete from student;
1 row deleted

SQL> commit;
Commit complete

SQL>  select to_char(sysdate,‘yyyymmdd hh24:mi:ss‘) from dual;
TO_CHAR(SYSDATE,‘YYYYMMDDHH24:
------------------------------
20150124 19:36:51

SQL> select * from student as of timestamp to_date(‘20150124 19:36:30‘,‘yyyymmdd hh24:mi:ss‘);
         ID NAME                    AGE
----------- -------------------- -----------
          1 zhangsan                23

SQL> select * from student as of timestamp to_date(‘20150124 18:04:26‘,‘yyyymmdd hh24:mi:ss‘);
         ID NAME                    AGE
----------- -------------------- -----------
          1 zhangsan                21
          2 lisi                           22
          3 wangwu                  23

SQL> 

oracle10g的闪回版本查询：

      对于9i的闪回查询进行增强，Oracle10g提供了闪回版本

查询，因为9i的闪回查询仅仅能够得到过去某个时间点上的

数据，但是无法反映出一段时间内数据表中数据的变化，

10g的闪回版本查询可以对时间段内数据表的不同版本进行

查询。

     闪回版本查询的语法：

      select   …  from  …   versions  between

     其中，select后面可以选择伪列，来获得事务的开始、结

束时间、SCN号、ID号等。

SQL>  select to_char(sysdate,‘yyyymmdd hh24:mi:ss‘) from dual;
TO_CHAR(SYSDATE,‘YYYYMMDDHH24:
------------------------------
20150124 19:40:11

SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------使用上面的系统包必须赋予权限：grant execute on dbms_flashback to hr;
                 3115836

SQL>
SQL> insert into student values(1,‘zhangsan‘,23);
1 row inserted

SQL> commit;
Commit complete

SQL>  select to_char(sysdate,‘yyyymmdd hh24:mi:ss‘) from dual;
TO_CHAR(SYSDATE,‘YYYYMMDDHH24:
------------------------------
20150124 19:51:36

SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                 3115938

SQL> update student s set s.name=‘wangwu‘ where s.id = 1;
1 row updated

SQL> commit;
Commit complete

SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                 3115954

SQL>  select to_char(sysdate,‘yyyymmdd hh24:mi:ss‘) from dual;
TO_CHAR(SYSDATE,‘YYYYMMDDHH24:
------------------------------
20150124 19:52:26

SQL> insert into student values (2,‘mazi‘,24);
1 row inserted

SQL> commit;
Commit complete

SQL>  select to_char(sysdate,‘yyyymmdd hh24:mi:ss‘) from dual;
TO_CHAR(SYSDATE,‘YYYYMMDDHH24:
------------------------------
20150124 19:52:58

SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                 3116062

SQL>
SQL> select  versions_starttime,versions_endtime,versions_xid,versions_operation,id,name,age from student
  2  versions between scn minvalue and maxvalue;
VERSIONS_STARTTIME                                               VERSIONS_ENDTIME                                                  VERSIONS_XID VERSIONS_OPERATION          ID NAME                    AGE
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------- ------------------ ----------- -------------------- -----------
24-1月 -15 07.52.54 下午                                                                                                                           0B00080005010000 I                            2 mazi                       24
24-1月 -15 07.52.15 下午                                                                                                                           13000100FE000000 U                           1 wangwu                  23
24-1月 -15 07.51.29 下午                                          24-1月 -15 07.52.15 下午                                          12001F00FF000000 I                            1 zhangsan                23

SQL>
SQL> select  to_char( versions_starttime,‘yyyymmdd hh24:mi:ss‘) ,to_char( versions_endtime,‘yyyymmdd hh24:mi:ss‘),versions_xid,versions_operation,id,name,age from student
  2  versions between scn minvalue and maxvalue;
TO_CHAR(VERSIONS_STARTTIME,‘YY TO_CHAR(VERSIONS_ENDTIME,‘YYYY VERSIONS_XID VERSIONS_OPERATION          ID NAME                    AGE
------------------------------ ------------------------------ ---------------- ------------------ ----------- -------------------- -----------
20150124 19:52:54                                0B00080005010000 I                            2 mazi                       24
20150124 19:52:15                                13000100FE000000 U                           1 wangwu                  23
20150124 19:51:29 20150124 19:52:15 12001F00FF000000 I                            1 zhangsan                23

SQL>
SQL> select  to_char( versions_starttime,‘yyyymmdd hh24:mi:ss‘) ,to_char( versions_endtime,‘yyyymmdd hh24:mi:ss‘),versions_xid,versions_operation,id,name,age from student
  2  versions between timestamp minvalue and maxvalue;
TO_CHAR(VERSIONS_STARTTIME,‘YY TO_CHAR(VERSIONS_ENDTIME,‘YYYY VERSIONS_XID VERSIONS_OPERATION          ID NAME                    AGE
------------------------------ ------------------------------ ---------------- ------------------ ----------- -------------------- -----------
20150124 19:52:54                                0B00080005010000 I                            2 mazi                       24
20150124 19:52:15                                13000100FE000000 U                           1 wangwu                  23
20150124 19:51:29 20150124 19:52:15 12001F00FF000000 I                            1 zhangsan                23

SQL> 
过一段时间你会发现查询的结果变少了：
SQL> select  to_char( versions_starttime,‘yyyymmdd hh24:mi:ss‘) ,to_char( versions_endtime,‘yyyymmdd hh24:mi:ss‘),versions_xid,versions_operation,id,name,age from student
  2  versions between timestamp minvalue and maxvalue;
TO_CHAR(VERSIONS_STARTTIME,‘YY TO_CHAR(VERSIONS_ENDTIME,‘YYYY VERSIONS_XID VERSIONS_OPERATION          ID NAME                    AGE
------------------------------ ------------------------------ ---------------- ------------------ ----------- -------------------- -----------
                                                                                                            2 mazi                       24
                                                                                                            1 wangwu                  23

SQL>
这是因为undo_retention这个值设置的时间到期了。

oracle10g的闪回事务查询：

      Oracle10g可以进行基于闪回版本查询的恢复，就是闪回事务查询。

      从flashback_transaction_query中查询引起数据变化的

     事务，和撤销事务的SQL语句，就是查询 operation和undo_sql 列。


SQL> select xid,start_timestamp,operation,undo_sql from flashback_transaction_query where table_name=‘STUDENT‘;
XID            START_TIMESTAMP OPERATION                UNDO_SQL
---------------- --------------- -------------------------------- --------------------------------------------------------------------------------
0F00020002010000 24-1月-15 20:32: UNKNOWN                
0F00040002010000 24-1月-15 20:32: UNKNOWN                
0F00040002010000 24-1月-15 20:32: UNKNOWN                
13000100FE000000 24-1月-15 19:52: UNKNOWN                
1400200004010000 24-1月-15 20:32: UNKNOWN    
             技术分享技术分享
如上图所示的，但是这里查不出来奇怪了。。。。
通过undo_sql来进行回滚事务（就是再做次反向操作）


oracle10g的闪回表：

Oracle10g的闪回表是把表里的数据回退到以前的某个时

刻或者SCN上。

     特点：可以在线操作；自动恢复相关的属性，包括索引、

触发器等。

     前提：对表启用行迁移。

     语法：

     flashback table <table_name> to timestamp | scn

Connected as hr@JIAGULUN

SQL> select * from student;
         ID NAME                    AGE
----------- -------------------- -----------
          3 zhangsan                21

SQL> alter table student enable row movement;
Table altered

SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                 3143334

SQL> delete from student where id in(1);
0 rows deleted

SQL> delete from student where id in(3);
1 row deleted

SQL> commit;
Commit complete

SQL>  flashback  table student to scn 3143334;
Done

SQL> select * from student;
         ID NAME                    AGE
----------- -------------------- -----------
          3 zhangsan                21

SQL> 

     注意：sys的表不能闪回。

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as sys@JIAGULUN AS SYSDBA

SQL> select * from student;
         ID NAME                    AGE
----------- -------------------- -----------
          1 zhangsan                20
          2 lisi                           21
          3 wangwu                  22

SQL> alter table student enable row movement;
Table altered

SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                 3142994

SQL> delete from student where id in(1,2);
2 rows deleted

SQL> commit;
Commit complete

SQL> select * from student;
         ID NAME                    AGE
----------- -------------------- -----------
          3 wangwu                  22

SQL> flashback  table student to scn 3142994;
flashback  table student to scn 3142994
ORA-08185: 用户 SYS 不支持闪回

SQL> show user;
User is "SYS"

SQL> 

oracle10g的闪回删除：

Oracle10g的闪回删除：可以恢复一个被drop的对象，因

为进行drop时，Oracle先把它放到回收站中。（回收站和操作系统的回收站很相似）

     回收站内的信息：show recyclebin

     闪回删除：flashback table <table_name> to before drop;

     彻底删除：drop table <table_name>  purge;

     清空回收站: purge recyclebin;

     

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as hr@JIAGULUN

SQL> purge recyclebin;
Done

SQL> select * from tab;
TNAME                     TABTYPE  CLUSTERID
------------------------------ ------- ----------
ADDRESS                 TABLE

SQL> select * from address;
         ID NAME
----------- --------------------
          1 jiangxi
          2 hunan

SQL> show recyclebin;
SQL> select * from user_recyclebin;--回收站并没有任何垃圾
OBJECT_NAME         ORIGINAL_NAME        OPERATION TYPE                   TS_NAME                 CREATETIME DROPTIME       DROPSCN PARTITION_NAME      CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------

SQL> drop table address;--删除表
Table dropped

SQL> show recyclebin;
SQL> select * from user_recyclebin;--会发现回收站中出现了——表，索引垃圾
OBJECT_NAME         ORIGINAL_NAME        OPERATION TYPE                   TS_NAME                 CREATETIME DROPTIME       DROPSCN PARTITION_NAME      CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------
BIN$DXVcPQqfU6HgUKjAA1tAfw==$0 ADDRESS                   DROP  TABLE                 USERS                     2015-01-22:06:10:58 2015-01-24:22:49:08    3145534                                  YES     YES         77236       77236        77236          8
BIN$DXVcPQqeU6HgUKjAA1tAfw==$0 PK_ADDRESS             DROP  INDEX                 USERS                     2015-01-22:06:10:58 2015-01-24:22:49:08    3145530                                  NO      YES         77236       77236        77239          8
BIN$DXVcPQqdU6HgUKjAA1tAfw==$0 INDEX_ADDRESS_NAME DROP  INDEX                 USERS                     2015-01-22:06:10:58 2015-01-24:22:49:08    3145525                                  NO      YES         77236       77236        77240          8

SQL> select * from tab;--刚刚存在address表不见了，但是多了一张下面的表
TNAME                     TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$DXVcPQqfU6HgUKjAA1tAfw==$0 TABLE

SQL> flashback table student  to before drop;
flashback table student  to before drop
ORA-38305: 对象不在回收站中

SQL> flashback table address  to before drop;--对回收站的表进行闪回
Done

SQL> select * from user_recyclebin;--回收站中的表被闪回了
OBJECT_NAME         ORIGINAL_NAME        OPERATION TYPE                   TS_NAME                 CREATETIME DROPTIME       DROPSCN PARTITION_NAME      CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------

SQL> select * from tab;--表被闪回了
TNAME                     TABTYPE  CLUSTERID
------------------------------ ------- ----------
ADDRESS                 TABLE

SQL> drop table address;
Table dropped

SQL> create table address(id number(10));
Table created

SQL> insert into address values(1);
1 row inserted

SQL> commit;
Commit complete

SQL> drop table address;
Table dropped

SQL> select * from user_recyclebin;--回收站中有两个address表
OBJECT_NAME         ORIGINAL_NAME        OPERATION TYPE                   TS_NAME                 CREATETIME DROPTIME       DROPSCN PARTITION_NAME      CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------
BIN$DXVcPQqkU6HgUKjAA1tAfw==$0 ADDRESS                   DROP  TABLE                 USERS                     2015-01-24:22:51:52 2015-01-24:22:52:19    3145976                                  YES     YES         77424       77424        77424          8
BIN$DXVcPQqjU6HgUKjAA1tAfw==$0 ADDRESS                   DROP  TABLE                 USERS                     2015-01-22:06:10:58 2015-01-24:22:51:38    3145840                                  YES     YES         77236       77236        77236          8
BIN$DXVcPQqiU6HgUKjAA1tAfw==$1 BIN$DXVcPQqeU6HgUKjAA1tAfw==$0 DROP  INDEX                 USERS                     2015-01-22:06:10:58 2015-01-24:22:51:38    3145836                                  NO      YES         77236       77236        77239          8
BIN$DXVcPQqhU6HgUKjAA1tAfw==$1 BIN$DXVcPQqdU6HgUKjAA1tAfw==$0 DROP  INDEX                 USERS                     2015-01-22:06:10:58 2015-01-24:22:51:38    3145831                                  NO      YES         77236       77236        77240          8

SQL> flashback table address to before drop;--闪回表
Done

SQL> select * from user_recyclebin;
OBJECT_NAME         ORIGINAL_NAME        OPERATION TYPE                   TS_NAME                 CREATETIME DROPTIME       DROPSCN PARTITION_NAME      CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------
BIN$DXVcPQqjU6HgUKjAA1tAfw==$0 ADDRESS                   DROP  TABLE                 USERS                     2015-01-22:06:10:58 2015-01-24:22:51:38    3145840                                  YES     YES         77236       77236        77236          8
BIN$DXVcPQqiU6HgUKjAA1tAfw==$1 BIN$DXVcPQqeU6HgUKjAA1tAfw==$0 DROP  INDEX                 USERS                     2015-01-22:06:10:58 2015-01-24:22:51:38    3145836                                  NO      YES         77236       77236        77239          8
BIN$DXVcPQqhU6HgUKjAA1tAfw==$1 BIN$DXVcPQqdU6HgUKjAA1tAfw==$0 DROP  INDEX                 USERS                     2015-01-22:06:10:58 2015-01-24:22:51:38    3145831                                  NO      YES         77236       77236        77240          8

SQL> select * from address;--被闪回的表示最晚送到回收站的那张表
         ID
-----------
          1

SQL>
SQL> drop table address purge;--删除表并不送入到回收站中
Table dropped

SQL> select * from user_recyclebin;
OBJECT_NAME         ORIGINAL_NAME        OPERATION TYPE                   TS_NAME                 CREATETIME DROPTIME       DROPSCN PARTITION_NAME      CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------
BIN$DXVcPQqjU6HgUKjAA1tAfw==$0 ADDRESS                   DROP  TABLE                 USERS                     2015-01-22:06:10:58 2015-01-24:22:51:38    3145840                                  YES     YES         77236       77236        77236          8
BIN$DXVcPQqiU6HgUKjAA1tAfw==$1 BIN$DXVcPQqeU6HgUKjAA1tAfw==$0 DROP  INDEX                 USERS                     2015-01-22:06:10:58 2015-01-24:22:51:38    3145836                                  NO      YES         77236       77236        77239          8
BIN$DXVcPQqhU6HgUKjAA1tAfw==$1 BIN$DXVcPQqdU6HgUKjAA1tAfw==$0 DROP  INDEX                 USERS                     2015-01-22:06:10:58 2015-01-24:22:51:38    3145831                                  NO      YES         77236       77236        77240          8

SQL>
SQL> flashback table address to before drop;--闪回原来存在于回收站的表
Done

SQL> select * from user_recyclebin;
OBJECT_NAME         ORIGINAL_NAME        OPERATION TYPE                   TS_NAME                 CREATETIME DROPTIME       DROPSCN PARTITION_NAME      CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------

SQL> select * from address;
         ID NAME
----------- --------------------
          1 jiangxi
          2 hunan

SQL> 
通过参数recyclebin来启用、禁用回收站。
SQL> show parameter recycle;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle                  string     
db_recycle_cache_size                big integer 0
recyclebin                           string      on

SQL> 
SQL> select * from address;
         ID NAME
----------- --------------------
          1 jiangxi
          2 hunan

SQL> alter session set recyclebin = off;--关闭回收站
Session altered

SQL> drop table address;
Table dropped

SQL> select * from user_recyclebin;--回收站为空的
OBJECT_NAME         ORIGINAL_NAME        OPERATION TYPE                   TS_NAME                 CREATETIME DROPTIME       DROPSCN PARTITION_NAME      CAN_UNDROP CAN_PURGE    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------

SQL> 

闪回数据库：

如果数据库出现逻辑错误，无法采用闪回表的方式进行恢

复，或者数据库的结构发生了改变，可以通过闪回数据库的

方式把整个数据库回退到出错前的时间点上。步骤：

     1，配置数据库为归档模式

     2，配置闪回恢复区

     3，配置闪回保留时间

     4，启用数据库闪回 alter database flashback on;

     5，进行闪回数据库

           flashback database to timestamp |scn


SQL> show user
USER is "SYS"
SQL> archive log list;--查看是否是归档模式
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archive/archive02/
Oldest online log sequence     362
Next log sequence to archive   365
Current log sequence           365
SQL> alter system set log_archive_start = false scope =spfile;--设置为不是自动归档（在9i后失效了，归档模式就是自动的）

System altered.

SQL> shutdown immediate--关闭数据库
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount--打开数据库到mount状态
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  400846848 bytes
Fixed Size                  2213776 bytes
Variable Size             276826224 bytes
Database Buffers          117440512 bytes
Redo Buffers                4366336 bytes
Database mounted.
SQL> alter database noarchivelog--修改为非归档模式
  2  ;

Database altered.

SQL> alter database open--打开数据库
  2  ;
------------------------------------前面是修改数据库为非归档模式------------------
Database altered.

SQL> archive log list;--查看是否归档
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/archive/archive02/
Oldest online log sequence     362
Current log sequence           365
SQL> clear
SQL> !clear

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/archive/archive02/
Oldest online log sequence     362
Current log sequence           365
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  400846848 bytes
Fixed Size                  2213776 bytes
Variable Size             276826224 bytes
Database Buffers          117440512 bytes
Redo Buffers                4366336 bytes
Database mounted.
SQL> alter database archivelog;--修改为归档模式

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archive/archive02/--归档区，默认是在闪回区
Oldest online log sequence     362
Next log sequence to archive   365
Current log sequence           365
SQL> show parameter db_rec

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
--闪回区配置                                                 _area
db_recovery_file_dest_size           big integer 3882M
db_recycle_cache_size                big integer 0
SQL> show parameter db_flashback_retent

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440--闪回保留时间默认是24小时，也就是1440分钟
SQL> !ls /u01/app/oracle/flash_recovery_area
jiagulun  JIAGULUN——里面是空的

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  400846848 bytes
Fixed Size                  2213776 bytes
Variable Size             276826224 bytes
Database Buffers          117440512 bytes
Redo Buffers                4366336 bytes
Database mounted.

SQL> alter database flashback on;--启动数据库闪回

Database altered.

SQL> !ls /u01/app/oracle/flash_recovery_area
jiagulun  JIAGULUN
[oracle@localhost flashback]$ pwd
/u01/app/oracle/flash_recovery_area/JIAGULUN/flashback
[oracle@localhost JIAGULUN]$ cd flashback/
[oracle@localhost flashback]$ ls
o1_mf_bd97qnhk_.flb  o1_mf_bd987hyb_.flb
SQL> alter database open;

Database altered.

SQL> select to_char(sysdate,‘yyyymmdd hh24:mi:ss‘) from dual;

TO_CHAR(SYSDATE,‘
-----------------
20150124 23:47:01

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE
TESTTS
TEMP2
TEMP3
UNDOTBS2
TEST_TRAN_TS

11 rows selected.

SQL> create tablespace test_flashback_database datafile ‘/u01/app/oracle/oradata/jiagulun/test_flashback_database.dbf‘ size 10m;

Tablespace created.

SQL>  select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE
TESTTS
TEMP2
TEMP3
UNDOTBS2
TEST_TRAN_TS

NAME
------------------------------
TEST_FLASHBACK_DATABASE

12 rows selected.

SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  400846848 bytes
Fixed Size                  2213776 bytes
Variable Size             276826224 bytes
Database Buffers          117440512 bytes
Redo Buffers                4366336 bytes
Database mounted.

SQL>  flashback database to timestamp to_date(‘20150124 23:47:01‘,‘yyyymmdd hh24:mi:ss‘);

Flashback complete.

SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  400846848 bytes
Fixed Size                  2213776 bytes
Variable Size             276826224 bytes
Database Buffers          117440512 bytes
Redo Buffers                4366336 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;--为什么会要求open为resetlog？
因为：数据库被闪回了，那么从闪回前在到闪回点的重做日志对于数据库来说已经是无效的了。所以要求重设日志。
SQL> alter database open read only;--有两种模式：read only、read write；之所以为这种状态，因为数据库在闪回后，就不是现在的数据库了
数据库的状态发生了改变，不希望其他用户登录进来防止出现问题

Database altered.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE
TESTTS
TEMP2
TEMP3
UNDOTBS2
TEST_TRAN_TS
TEST_FLASHBACK_DATABASE--已经不存在了，被闪回了
11 rows selected.

SQL>

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  400846848 bytes
Fixed Size                  2213776 bytes
Variable Size             276826224 bytes
Database Buffers          117440512 bytes
Redo Buffers                4366336 bytes
Database mounted.
SQL> alter database archivelog
  2  ;

Database altered.

SQL>
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archive/archive02/
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1
SQL>  alter database noarchivelog;--归档变成非归档
alter database noarchivelog
*
ERROR at line 1:
ORA-38774: cannot disable media recovery - flashback database is enabled
--表示在flashback状态不能变成非归档

SQL> alter database flashback off;--关闭flashback

Database altered.

SQL>  alter database noarchivelog;--再进行归档到非归档的切换

Database altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/archive/archive02/
Oldest online log sequence     1
Current log sequence           1

SQL> alter database open;

Database altered.

SQL>
















oracle11G闪回flashback


联系
我们