--如何打开闪回数据库
SYS@PROD1> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SYS@PROD1> shutdown immediate --必须要一致性关闭
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD1> startup mount
ORACLE instance started.
Total System Global Area 732352512 bytes
Fixed Size 1347456 bytes
Variable Size 348127360 bytes
Database Buffers 381681664 bytes
Redo Buffers 1196032 bytes
Database 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
------------------
YES
SYS@PROD1> show parameter flashback --保留一天的闪回数据
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SYS@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:12
SYS@PROD1> drop table scott.emp purge;
Table dropped.
--通过数据挖掘查到sql的scn或timestamp
SYS@PROD1> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
SYS@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 selected
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 %EMP%';
no rows selected
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 %emp%';
SQL_REDO
----------------------------------------------------------------------------------------------------
SCN TIMESTAMP
---------- -------------------
drop table emp AS "BIN$RMuU96dhD73gU0edqMCaWg==$0" ;
4300076357 2016-12-29 20:54:11
drop table scott.emp purge;
4300079926 2016-12-29 21:35:16
--执行第一次闪回
SYS@PROD1> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD1> startup mount
ORACLE instance started.
Total System Global Area 732352512 bytes
Fixed Size 1347456 bytes
Variable Size 348127360 bytes
Database Buffers 381681664 bytes
Redo Buffers 1196032 bytes
Database mounted.
SYS@PROD1> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; --记录时间
TO_CHAR(SYSDATE,'YY
-------------------
2016-12-29 21:40:40
SYS@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)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SYS@PROD1> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With 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 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table EMP 14 rows exported
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.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
[oracle@ocm1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 29 21:42:08 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@PROD1> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD1> startup mount
ORACLE instance started.
Total System Global Area 732352512 bytes
Fixed Size 1347456 bytes
Variable Size 348127360 bytes
Database Buffers 381681664 bytes
Redo Buffers 1196032 bytes
Database 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> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ocm1 ~]$ imp scott/tiger tables=emp file=emp.dmp
Import: Release 11.2.0.3.0 - Production on Thu Dec 29 21:45:38 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import 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 imported
Import terminated successfully without warnings.
[oracle@ocm1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 29 21:45:43 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@PROD1> desc scott.emp --闪回成功
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
闪回数据库实验
最新推荐文章于 2021-04-11 08:00:01 发布