复习闪回功能

首先要开启归档与闪回

G:\Tools\ha_powercmd>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 21 08:43:04 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list
Database log mode	       No Archive Mode
Automatic archival	       Disabled
Archive destination	       D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS
Oldest online log sequence     3
Current log sequence	       5

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  778387456 bytes
Fixed Size		    1374808 bytes
Variable Size		  234882472 bytes
Database Buffers	  536870912 bytes
Redo Buffers		    5259264 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.
						 s\orcl\orcl\trace
SQL> show parameter db_recover

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string
db_recovery_file_dest_size	     big integer 0

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=2g;

System altered.

SQL> alter system set db_recovery_file_dest='J:\orcl\orcl\flash_recovery_area';

System altered.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

更改归档路径

SQL> select file_type from v$flash_recovery_area_usage;

FILE_TYPE
--------------------
CONTROL FILE
REDO LOG
ARCHIVED LOG
BACKUP PIECE
IMAGE COPY
FLASHBACK LOG
FOREIGN ARCHIVED LOG

7 rows selected.

SQL> alter system set log_archive_dest_1='location=J:\orcl\orcl\log';
System altered.

SQL> alter system archive log current;
System altered.

SQL> select name from v$archived_log;

NAME
-----------------------------------------------
J:\ORCL\ORCL\LOG\ARC0000000005_0824025384.0001

SQL> alter system archive log current;

System altered.

SQL> select name from v$archived_log;

NAME
-----------------------------------------------
J:\ORCL\ORCL\LOG\ARC0000000005_0824025384.0001
J:\ORCL\ORCL\LOG\ARC0000000006_0824025384.0001

SQL> exit

Flashback Drop

G:\Tools\ha_powercmd>sqlplus test/test
SQL> create table test as select * from scott.dept;

Table created.

SQL> drop table test;

Table dropped.

SQL> flashback table test to before drop;

Flashback complete.

SQL> select deptno from test;

    DEPTNO
----------
	10
	20
	30
	40

SQL> select * from test1;
select * from test1
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> drop table test;

Table dropped.

SQL> flashback table test to before drop rename to test1;

Flashback complete.

SQL> select * from test1;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	40 OPERATIONS	  BOSTON
/*关闭回收站*/
SQL> alter system set recyclebin=off deferred;
SQL> alter session set recyclebin=off;

Flashback Table

SQL> alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss";
SQL> create table test as select sysdate as t1 from dual;
Table created.

SQL> insert into test values(sysdate);
1 row created.

SQL> commit;
Commit complete.

SQL> insert into test values(sysdate);
1 row created.

SQL> commit;
Commit complete.

SQL> insert into test values(sysdate);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from test;

T1
-------------------
2013-08-21 09:39:19
2013-08-21 09:39:29
2013-08-21 09:39:35
2013-08-21 09:39:38

SQL> select versions_starttime,
  2         VERSIONS_STARTSCN,
  3         t1,
  4         versions_endtime,
  5         VERSIONS_ENDSCN
  6    from test versions between timestamp minvalue and maxvalue
  7   order by 1 desc nulls last;

VERSIONS_STARTTIME	 VERSIONS_STARTSCN T1		       VERSIONS_ENDTIME VERSIONS_ENDSCN 
------------------------ ----------------- ------------------- ---------------- -----------
21-AUG-13 09.39.36 AM    1041514           2013-08-21 09:39:38
21-AUG-13 09.39.36 AM    1041512           2013-08-21 09:39:35
21-AUG-13 09.39.30 AM    1041509           2013-08-21 09:39:29
                                           2013-08-21 09:39:19

SQL> select * from test as of timestamp to_timestamp('2013-08-21 09:39:38','yyyy-mm-dd hh24:mi:ss');

T1
-------------------
2013-08-21 09:39:19
2013-08-21 09:39:29

SQL> select * from test as of timestamp to_timestamp('2013-08-21 09:39:39','yyyy-mm-dd hh24:mi:ss');

T1
-------------------
2013-08-21 09:39:19
2013-08-21 09:39:29
2013-08-21 09:39:35
2013-08-21 09:39:38


SQL> select * from test as of scn 1041509;

T1
-------------------
2013-08-21 09:39:19
2013-08-21 09:39:29

SQL> select * from test as of scn 1041514;

T1
-------------------
2013-08-21 09:39:19
2013-08-21 09:39:29
2013-08-21 09:39:35
2013-08-21 09:39:38

SQL> flashback table test to scn 1041513;
flashback table test to scn 1041513
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled


SQL> alter table test enable row movement;
Table altered.

SQL> flashback table test to scn 1041513;
Flashback complete.

SQL> select * from test;
T1
-------------------
2013-08-21 09:39:19
2013-08-21 09:39:29
2013-08-21 09:39:35

SQL> flashback table test to timestamp to_timestamp('2013-08-21 09:39:34','yyyy-mm-dd hh24:mi:ss');
Flashback complete.

SQL> select * from test;
T1
-------------------
2013-08-21 09:39:19
2013-08-21 09:39:29