第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;

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

转载于:http://blog.itpub.net/235507/viewspace-555203/