Oracle 11g闪回flashback
Oracle 11g闪回flashback
Oracle 11g闪回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>
Oracle 11g flashback Data Archive(闪回数据归档)
Oracle flashback闪回机制
flashback table快速恢复误删除的数据
Oracle 备份恢复：flashback闪回
[Oracle]闪回flashback功能的使用
评论暂时关闭