ORACLE FLASHBACK DATABASE知识整理(二)
----------------
837455 2013-07-27 16:50:30
3)
[sql]
select oldest_flashback_scn as oldest_scn,to_char(oldest_flashback_time,'yyyy-mm-ddhh24:mi:ss') as oldest_time,retention_target,flashback_size/1024/1024 as fb_size_mb,estimated_flashback_size/1024/1024 as estimated_size_mb from v$flashback_database_log;
4) 查找每个时间段内闪回数据库所需的数据
[sql]
SQL>select to_char(begin_time,'yyyy-mm-dd hh24:mi:ss') as begin_time,to_char(end_time,'yyyy-mm-dd hh24:mi:ss') as end_time,flashback_data/1024/1024 as fb_date_mb,db_data/1024/1024 as db_date_mb,redo_data/1024/1024 as redo_date_mb,estimated_flashback_size as estimated_size from v$flashback_database_stat;
5) SGA 中自动分配的闪回缓冲区
[sql]
SQL>select * from v$sgastat where name='flashback generation buff';
POOL NAME BYTES
---------------------------------------------------- ----------
shared pool flashback generation buff 8388608
6) 查找具体的表空间是否打开闪回
[sql]
SQL>select name,flashback_on from v$tablespace;
NAME FLASHBACK_ON
----------------------------------------------------------
SYSTEM YES
SYSAUX YES
UNDOTBS1 YES
USERS YES
TEMP YES
TEST_SPACE YES
READONLY_SPACE YES
OFFLINE_SPACE YES
INDEX_SPACE YES
4、使用方法
4.1 SQLPLUS
4.1.1环境模拟
1） 确定当前时间
[sql]
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2013-07-27 20:56:32
2） 确定当前SCN
[sql]
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
839748
3） 指定表原始状态
[sql]
SQL> select * from knight.books;
ID NAME PRICE
------------------------------ ----------
1 LINUX 25.55
2 AIX 50.5
3 NETWORK 125.89
4 JAVA 88.99
5 JQUERY 102.98
4） 插入新数据，这些数据在闪回后应该不存在。
[sql]
SQL> insert into knight.books values(200,'WEBWORK',299.9);
1 row created.
5） 插入新数据，这些数据在闪回后应该不存在。
[sql]
SQL> insert into knight.books values(201,'PMP',56.99);
1 row created.
[sql]
SQL> commit;
Commit complete.
6） 表的新状态。
[sql]
SQL> select * from knight.books;
ID NAME PRICE
------------------------------ ----------
200 WEBWORK 299.9
201 PMP 56.99
1 LINUX 25.55
2 AIX 50.5
3 NETWORK 125.89
4 JAVA 88.99
5 JQUERY 102.98
7 rows selected.
7） 模拟断电关机
[sql]
SQL>shutdown abort;
4.1.2 开始闪回数据库
[sql]
I SQL>startup mount;
[sql]
II SQL>flashback database to timestamp to_timestamp('2013-07-27 20:56:32','yyyy-MM-dd hh24:mi:ss');//最细粒度为百万分之一秒
Flashback complete.
[sql]