第5章 閃回資料庫Understanding the Flashback Database

第5章 閃回資料庫Understanding the Flashback Database
配置閃回恢復區
sql>alter system set db_recovery_file_dest_size = 6g scope=both;
sql>alter system set db_recovery_file_dest='c:\oracle\flash_recovery_area\ora101t';
sql>alter system set db_recovery_file_dest_size=2000m;
aql>alter system set db_recovery_file_dest='';

使用閃回恢復區Using the Flash Recovery Area
Let's walk through performing an automated disk-based backup and recovery:
1.First, connect to the target database:
command>set ORACLE_SID=ora101rc
command>rman
rman>connect target
2.Next, perform. a backup of the target database:
rman>backup as copy database;
3. Finally, query the V$RECOVERY_FILE_DEST to determine if the SPACE_USED column value has increased.
sql>select * from v$recovery_file_dest;

備份閃回恢復區
rman>backup recovery area;
rman>backup recovery files;

配置閃回恢復區
1. Start the database in MOUNT mode:
sql>connect / as sysdba
sql>startup mount
2. Set the DB_FLASHBACK_RETENTION_TARGET parameter to the desired value. This value can be set as an initialization parameter if you're not using the SPFILE. This value is in minutes, which equates to three days:
SQL> alter system set db_flashback_retention_target=4320;
3. Enable the flashback capability:
sql>alter database flashback on;
4. Now the database can be opened for normal use:
sql>alter database open;

在RMAN中使用閃回資料庫
(example演示了閃回某個時刻（能閃回的最舊scn）,但我在這中間曾經resetlogs也幾次,也更改了控制檔案,能成嗎,以後測試,估計要先恢復控制檔案)
1. First, query the V$FLASHBACK_DATABASE_LOG view to retrieve the OLDEST_FLASHBACK_SCN:
command>sqlplus /nolog
sql>connect / as sysdba
sql>select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;
2. Next, shut down and start the database in MOUNT mode:
sql>shutdown
sql>startup mount
3. Next, issue the Flashback Database recovery from RMAN:
command>rman
rman>connect target
rman>flashback database to scn=689316;
4. Finally, open the database with the RESETLOGS option,
sql>alter database open resetlogs;

監視閃回資料庫
sql>select flashback_on from v$database;
sql>select oldest_flashback_scn,oldest_flashback_time,retention_target,estimated_flashback_size from v$flashback_database_log;
sql>select * from v$flashback_database_stat;

EM使用閃回資料庫（略）

第6章 從使用者錯誤恢復Recovering from User Errors
使用閃回丟棄
sql>flashback table t1 to before drop;
瞭解回收站
sql>select * from t1;
sql>drop table t1;
sql>show recyclebin;
sql>select object_name as recycle_name ,original_name,object_name from recyclebin;
BIN$HbesBq6flungQAB/AQBm7A==$0 TB01
sql>select * from "BIN$0ZVR8xxxxxxxxxxxxxxx"
sql>flashback table "BIN$0Zxxxxxxxxxxxxxxx" to before drop rename to t2;
sql>select * from t2;
sql>show recyclebin;
回收站和空間利用
sql>purge table "BIN$0ZVR8eDEQbK4sxxxxxxxxxxx==$0"
sql>purge tablespace users user test;
The PURGE RECYCLEBIN command is used to purge your own Recycle Bin.
The PURGE DBA_RECYCLEBIN command removes all objects from all users' Recycle Bins.
sql>purge recyclebin;
sql>connect / as sysdba;
sql>purge dba_recyclebin;
閃回丟棄和回收站的侷限性（略）
通過EM實現閃回丟棄表

使用閃回版本查詢Using Flashback Versions Query
1.
sql>update t1 set salary=18000 where employee='JONES';
sql>commit;
sql>update t1 set salary=21000 where employee='JONES';
sql>commit;
sql>update t1 set salary=25000 where employee='JONES';
sql>commit;
2.顯示不同時刻的value
sql>select salary from t1 versions between scn minvalue and maxvalue where employee = 'JONES';
sql>select salary from t1
versions between timestamp
to_timestamp('2004-10-26 11:37:01','YYYY-MM-DD HH:MI:SS') and
to_timestamp('2004-10-26 11:43:01','YYYY-MM-DD HH:MI:SS')
where employee = 'JONES';
sql>select current_scn,scn_to_timestamp(current_scn) from v$database;
There are several new pseudocolumns that help you work with the Flashback Versions
Query:
VERSIONS_STARTTIME
VERSIONS_STARTSCN
VERSIONS_ENDTIME
VERSIONS_ENDSCN
VERSIONS_XID
VERSIONS_OPERATION

SQL>select to_char(versions_starttime,'DD-MON HH:MI') "START DATE",
to_char (versions_endtime, 'DD-MON HH:MI') "END DATE",
versions_xid,
versions_operation,
employee,
salary
from test.t1
versions between scn
minvalue and maxvalue
where employee = 'JONES'

select to_char(versions_starttime,'DD-MON HH:MI:SS') "START DATE",
to_char (versions_endtime, 'DD-MON HH:MI:SS') "END DATE",
versions_xid,
versions_operation,
salary
from t1
versions between scn
minvalue and maxvalue
where employee_id =197;

使用閃回事務查詢 Using Flashback Transaction Query
The Flashback Transaction Query is designed to be a diagnostic tool to help identify changes made to the database at the transaction level.
The Flashback Transaction Query is based on undo data 。
Using the FLASHBACK_TRANSACTION_QUERY view can help identify the table and operation that is performed against the table. This view can be large, so it is helpful to use a filter like the transaction identifier, which is in the column XID. The XID value was identified in the previous
The privilege required to use the Flashback Transaction Query is the system privilege FLASHBACK ANY TABLE. （用sysdba管理吧？）

Flashback Versions Query example.
SQL>SELECT table_name,operation,undo_sql from flashback_transaction_query
where xid='020018001F030000';
sql>select table_name,operation,undo_sql from flashback_transaction_query
where start_timestamp >= to_timestamp('2004-10-26 06:45:00','YYYY-MM-DD HH:MI:SS')
and table_owner='TEST';

使用閃回表Using Flashback Table
Flashback Table is a Flashback Technology that allows you to recover a table or set tables to a specific point-in-time without performing an incomplete recovery.
There are two main clauses that are used with the Flashback Table:
The TO SCN clause can recover the Flashback Table to a certain SCN.
The TO TIMESTAMP clause can recover the Flashback Table to a certain point-in-time.
Let's walk through performing a Flashback Table with SCN:
1.Enable ROW MOVEMENT on table T1:
sql>alter table t1 enable row movement;
2.Retrieve the current SCN before you modify the table:
sql>select current_scn from v$database; 771511
3.
sql>update t1 set salary=50000 where employee = 'JONES';
sql>commit;
4.
sql>flashback table t1 to scn 771511;
5.
sql>select * from t1 where employee='JONES';
OK已恢復

Let's walk through performing a Flashback Table with SCN: 測試時間點閃回表
FLASHBACK TABLE employee TO TIMESTAMP TO_TIMESTAMP('2003-04-04 09:30:00', `YYYY-MM-DD HH24:MI:SS');

Triggers are disabled by default during the Flashback Table process.
sql>flashback table table_name to scn 771551 enable triggers;