闪回数据库实验

--如何打开闪回数据库SYS@PROD1> select flashback_on from v$database;FLASHBACK_ON------------------NOSYS@PROD1> shutdown immediate  --必须要一致性关闭Database closed.Database dismounted.ORACLE instance shut down.SYS@PROD1> startup mountORACLE instance started.Total System Global Area  732352512 bytesFixed Size    1347456 bytesVariable Size  348127360 bytesDatabase Buffers  381681664 bytesRedo Buffers    1196032 bytesDatabase mounted.SYS@PROD1> alter database archivelog;  --必须先打开归档Database altered.SYS@PROD1> alter database flashback on;     Database altered.SYS@PROD1> alter database open;Database altered.SYS@PROD1> select flashback_on from v$database;FLASHBACK_ON------------------YESSYS@PROD1> show parameter flashback  --保留一天的闪回数据NAME     TYPE VALUE------------------------------------ ----------- ------------------------------db_flashback_retention_target     integer 1440SYS@PROD1> show parameter db_recovery_   --闪回日志存放路径NAME     TYPE VALUE------------------------------------ ----------- ------------------------------db_recovery_file_dest     string /u01/app/oracle/fast_recovery_ area/db_recovery_file_dest_size     big integer 4122M--模拟删除闪回数据库SYS@PROD1> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;TO_CHAR(SYSDATE,'YY-------------------2016-12-29 21:35:12SYS@PROD1> drop table scott.emp purge;Table dropped.--通过数据挖掘查到sql的scn或timestampSYS@PROD1> select group#,status from v$log;    GROUP# STATUS---------- ---------------- 1 CURRENT 2 INACTIVE 3 INACTIVESYS@PROD1> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/PROD1/redo01.log');PL/SQL procedure successfully completed.SYS@PROD1> exec dbms_logmnr.start_logmnr;PL/SQL procedure successfully completed.SYS@PROD1> select sql_redo,scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') timestamp from v$logmnr_contents where sql_redo like '%DROP TABLE%';no rows selectedSYS@PROD1> select sql_redo,scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') timestamp from v$logmnr_contents where sql_redo like '%DROP TABLE %EMP%';no rows selectedSYS@PROD1> select sql_redo,scn,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') timestamp from v$logmnr_contents where sql_redo like '%drop table %emp%';  SQL_REDO----------------------------------------------------------------------------------------------------       SCN TIMESTAMP---------- -------------------drop table emp AS "BIN$RMuU96dhD73gU0edqMCaWg==$0" ;4300076357 2016-12-29 20:54:11drop table scott.emp purge;4300079926 2016-12-29 21:35:16--执行第一次闪回SYS@PROD1> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SYS@PROD1> startup mountORACLE instance started.Total System Global Area  732352512 bytesFixed Size    1347456 bytesVariable Size  348127360 bytesDatabase Buffers  381681664 bytesRedo Buffers    1196032 bytesDatabase mounted. SYS@PROD1> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;  --记录时间TO_CHAR(SYSDATE,'YY-------------------2016-12-29 21:40:40SYS@PROD1> flashback database to timestamp to_timestamp('2016-12-29 21:35:15','yyyy-mm-dd hh24:mi:ss');Flashback complete.SYS@PROD1> alter database open read only;  --只读打开数据库Database altered.SYS@PROD1> desc scott.emp; Name       Null?Type ----------------------------------------------------- -------- ------------------------------------ EMPNO       NOT NULL NUMBER(4) ENAMEVARCHAR2(10) JOBVARCHAR2(9) MGRNUMBER(4) HIREDATEDATE SALNUMBER(7,2) COMMNUMBER(7,2) DEPTNO NUMBER(2)SYS@PROD1> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@ocm1 ~]$ exp scott/tiger tables=emp file=emp.dmp  --导出要恢复的表Export: Release 11.2.0.3.0 - Production on Thu Dec 29 21:41:57 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in US7ASCII character set and AL16UTF16 NCHAR character setserver uses AL32UTF8 character set (possible charset conversion)About to export specified tables via Conventional Path .... . exporting table                            EMP         14 rows exportedEXP-00091: Exporting questionable statistics.EXP-00091: Exporting questionable statistics.EXP-00091: Exporting questionable statistics.EXP-00091: Exporting questionable statistics.EXP-00091: Exporting questionable statistics.EXP-00091: Exporting questionable statistics.Export terminated successfully with warnings.[oracle@ocm1 ~]$ sqlplus  / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 29 21:42:08 2016Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSYS@PROD1> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SYS@PROD1> startup mountORACLE instance started.Total System Global Area  732352512 bytesFixed Size    1347456 bytesVariable Size  348127360 bytesDatabase Buffers  381681664 bytesRedo Buffers    1196032 bytesDatabase mounted.--第二次闪回数据库SYS@PROD1> flashback database to timestamp to_timestamp('2016-12-29 21:40:39','yyyy-mm-dd hh24:mi:ss');Flashback complete.SYS@PROD1> alter database open resetlogs;Database altered.SYS@PROD1> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@ocm1 ~]$ imp scott/tiger tables=emp file=emp.dmpImport: Release 11.2.0.3.0 - Production on Thu Dec 29 21:45:38 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport file created by EXPORT:V11.02.00 via conventional pathimport done in US7ASCII character set and AL16UTF16 NCHAR character setimport server uses AL32UTF8 character set (possible charset conversion). importing SCOTT's objects into SCOTT. importing SCOTT's objects into SCOTT. . importing table                          "EMP"         14 rows importedImport terminated successfully without warnings.[oracle@ocm1 ~]$ sqlplus  / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 29 21:45:43 2016Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSYS@PROD1> desc scott.emp  --闪回成功 Name       Null?Type ----------------------------------------------------- -------- ------------------------------------ EMPNO       NOT NULL NUMBER(4) ENAMEVARCHAR2(10) JOBVARCHAR2(9) MGRNUMBER(4) HIREDATEDATE SALNUMBER(7,2) COMMNUMBER(7,2) DEPTNO NUMBER(2)