Flashback base on Time and Sequence

Flashback Manager 1)theory 1@@@@theory and relative v$ view. @@@modify the date formate. [oracle@station3 ~]$ vim .bash_profile export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss" [oracle@station3 ~]$ . .bash_profile SQL> select sysdate from dual ; SYSDATE ------------------- 2011-11-23 14:36:38 ? @@@ SQL> select table_name from dict where table_name like '%FLASH%'; ? @@@ @@@v$flashback_database_log @@@ SQL> select OLDEST_FLASHBACK_SCN,OLDEST_FLASHBACK_TIME from v$flashback_database_log; OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI -------------------- ------------------- ????????????? 747014 2011-11-23 14:10:04 ? @@@ @@@v$flashback_database_stat @@@ SQL>? select BEGIN_TIME,END_TIME,FLASHBACK_DATA,DB_DATA,REDO_DATA from? v$flashback_database_stat; BEGIN_TIME????????? END_TIME??????????? FLASHBACK_DATA??? DB_DATA? REDO_DATA ------------------- ------------------- -------------- ---------- ---------- 2011-11-23 14:10:04 2011-11-23 14:40:25??????? 3301376??? 5120000??? 2694144 ? @@@ @@@v$undostat @@@ @@@obviously,record the data every 10 minutes. SQL> select BEGIN_TIME,UNDOTSN,UNDOBLKS from? v$undostat; BEGIN_TIME????????? END_TIME?????????????? UNDOTSN?? UNDOBLKS ------------------- ------------------- ---------- ---------- 2011-11-23 14:49:36 2011-11-23 14:52:51????????? 1????????? 2 2011-11-23 14:39:36 2011-11-23 14:49:36????????? 1???????? 28 2011-11-23 14:29:36 2011-11-23 14:39:36????????? 1???????? 31 2011-11-23 14:19:36 2011-11-23 14:29:36????????? 1???????? 16 2011-11-23 14:09:36 2011-11-23 14:19:36????????? 1???????? 78 ? @@@ @@@V$FLASHBACK_DATABASE_LOGFILE @@@ SQL> select * from V$FLASHBACK_DATABASE_LOGFILE; NAME -------------------------------------------------------------------------------- ????? LOG#??? THREAD#? SEQUENCE#????? BYTES FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ------------- ------------------- /u01/app/oracle/flash_recovery_area/ORCL/flashback/o1_mf_7ds3kvlm_.flb ???????? 1????????? 1????????? 1??? 8192000??????? 747648 2011-11-23 14:10:04 ? ? 2)flashback using sequence 1@@@@prepare @@@rman recovery to sequence is including @@@create a test table. SQL> conn hr/hr SQL> create table test_squence(a number);? Table created. SQL> insert into test_squence values(1);? 1 row created. SQL> commit; Commit complete. SQL> conn /as sysdba;? Connected. SQL> archive log list ;? Database log mode????????????? Archive Mode Automatic archival???????????? Enabled Archive destination??????????? USE_DB_RECOVERY_FILE_DEST Oldest online log sequence???? 22 Next log sequence to archive?? 26 Current log sequence?????????? 26?? @@@in using. SQL> alter system switch logfile ;? @@@LGWR archive 26 System altered. ? @@@ SQL>? truncate table hr.test_squence; Table truncated. SQL> archive log list; Database log mode????????????? Archive Mode Automatic archival???????????? Enabled Archive destination??????????? USE_DB_RECOVERY_FILE_DEST Oldest online log sequence???? 23 Next log sequence to archive?? 27 Current log sequence?????????? 27??? @@@in using. SQL> alter system switch logfile ;?? @@@LGWR archive 27 System altered. @@@if recovery to sequence 27,the table is missing,@@@if recovery to sequence 26,the table exist. ? 2@@@@begin SQL> shutdown immediate? SQL> startup mount? [oracle@station3 admin]$ rman target / RMAN> flashback database to sequence 26 thread 1 ; RMAN> sql "alter database open read only"? @@@to check SQL> select * from test_squence; ???????? A ---------- ???????? 1 @@@only if you haven't resetlogs,flashback continue. SQL> shutdown immediate ; SQL> startup mount; RMAN> flashback database to sequence 27 thread 1;? RMAN> alter database open resetlogs; RMAN> list incarnation of database;? List of Database Incarnations DB Key? Inc Key DB Name? DB ID??????????? STATUS? Reset SCN? Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1?????? 1?????? ORCL???? 1289234111?????? PARENT? 1????????? 2005-06-30 19:09:40 2?????? 2?????? ORCL???? 1289234111?????? PARENT? 446075???? 2011-09-10 02:49:38 3?????? 3?????? ORCL???? 1289234111?????? CURRENT 752156???? 2011-11-23 15:32:24 ?