首页 > 代码库 > [转]开启闪回以及闪回的四种原理

[转]开启闪回以及闪回的四种原理

2024-07-09 09:42:59   222人阅读

1、首先确认db_recovery_file_dest和db_recovery_file_dest_size 有值。
sys@TEST0910> show parameter recovery
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_ area
db_recovery_file_dest_size           big integer 4122M
db_recovery_file_dest 放闪回日志，默认路径/u01/app/oracle/fast_recovery_ area
db_recovery_file_dest_size 存放闪回日志空间的大小，默认4122M
 
2、开启闪回
sys@TEST0910> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TEST0910> startup mount
ORACLE instance started.
 
Total System Global Area 2505338880 bytes
Fixed Size                  2230952 bytes
Variable Size             587203928 bytes
Database Buffers         1895825408 bytes
Redo Buffers               20078592 bytes
Database mounted.
sys@TEST0910> alter database flashback on;
 
Database altered.
 
sys@TEST0910> alter database open;
 
Database altered.
 
3、查询确认是否开启闪回
sys@TEST0910> select name,flashback_on from v$database;
 
NAME                                               FLASHBACK_ON
-------------------------------------------------- ------------------
TEST0910                                           YES
 
闪回四种不同原理
 
  • flashback drop：利用recyelebin 回收站原理
  • flashback table TEST10 to before drop [rename to test11];可以重命名
 
  • flashback query ，依赖undo数据，先查询，在做insert
  • insert into test10 select * from test10 as of timestamp to_timestamp(‘2013-09-23 11:52:06‘,‘yyyy-mm-dd hh24:mi:ss‘);
 
  • flashback table 依赖undo块
  • flashback table scott.test10 to scn 1952615
  • insert into test10 select * from scott.test10 as of scn 1952615;
        
  • flashback database DDL语句，依赖闪回区的闪回日志
  • flashback database to timestamp to_timestamp(‘2013-09-23 15:09:52‘,‘yyyy-mm-dd hh24:mi:ss‘);时间减一秒。
 
  • 闪回归档--create as

 

来源：http://blog.csdn.net/rlhua/article/details/11934831

 

flashback drop实验（sys用户的除外，它没有回收站）

1、查询原来表的数量
sys@TEST0910> select count(*) from scott.test10;
 
  COUNT(*)
----------
        14
2、误操作drop
sys@TEST0910> drop table scott.test10;
 
Table dropped.
3、登陆scoot用户，查询回收站
sys@TEST0910> conn scott/tiger
Connected.
 
scott@TEST0910> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST10           BIN$5w/YlwePKa7gQwEAAH+tZw==$0 TABLE        2013-09-23:11:40:56
4、查询回收站里的内容
scott@TEST0910>  select * from "BIN$5w/YlwePKa7gQwEAAH+tZw==$0";              ------注意：此处是双引号
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
 
14 rows selected.
5、两种闪回方式
使用insert into TEST10 select * from "BIN$5w/YlwePKa7gQwEAAH+tZw==$0";  闪回
使用flashback table闪回
scott@TEST0910> flashback table TEST10 to before drop [rename to test11];可以重命名
 
Flashback complete.
 
scott@TEST0910> select count(*) from test10;
 
  COUNT(*)
----------
        14

 

flashback query实验（基于undo数据）

误操作DML语句，刚刚发生，undo没有被覆盖
1、查询原数据
11:52:06 scott@TEST0910> select count(*) from test10;
 
  COUNT(*)
----------
        14
 
11:52:06 这个时间点还有数据，稍后作为参考
2、误操作
11:52:19 scott@TEST0910> delete from test10;
 
14 rows deleted.
 
11:52:31 scott@TEST0910> commit;
 
Commit complete.
 
11:52:35 scott@TEST0910> select count(*) from test10;
 
  COUNT(*)
----------
         0
3、使用闪回查询
11:54:25 scott@TEST0910> select * from test10 as of timestamp to_timestamp(‘2013-09-23 11:52:06‘,‘yyyy-mm-dd hh24:mi:ss‘);
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
 
14 rows selected.
 
4、插入闪回查询的结果
11:54:44 scott@TEST0910> insert into test10 select * from test10 as of timestamp to_timestamp(‘2013-09-23 11:52:06‘,‘yyyy-mm-dd hh24:mi:ss‘);
 
14 rows created.
 
11:55:02 scott@TEST0910> commit;
 
Commit complete.
 
11:55:30 scott@TEST0910> select count(*) from test10;
 
  COUNT(*)
----------
        14
 
 

flashback table实验（基于undo数据）

1、查询原数据
sys@TEST0910> select count(*) from test10;
 
  COUNT(*)
----------
        14
 
1 row selected.
2、查询数据库当前scn号，稍后作为恢复参考
sys@TEST0910> select checkpoint_change# from v$database;
 
CHECKPOINT_CHANGE#
------------------
           1952615
 
1 row selected.
3、误操作
sys@TEST0910> delete from scott.test10;
 
14 rows deleted.
 
sys@TEST0910> commit;
 
Commit complete.
 
4、根据之前的scn号使用undo数据闪回查询
sys@TEST0910> select * from scott.test10 as of scn 1952615;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
 
14 rows selected.
 
5、将数据还原
可以插入回去，如闪回查询那样：insert into test10 select * from scott.test10 as of scn 1952615;
另外一种，可以用闪回表的方式：flashback table scott.test10 to scn 1952615;
注意，闪回表时要开启 row movement，不然要报错。
 
sys@TEST0910> flashback table scott.test10 to scn 1952615;
flashback table scott.test10 to scn 1952615
                      *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
 
 
sys@TEST0910> alter table scott.test10 enable row movement;
 
Table altered.
 
sys@TEST0910> flashback table scott.test10 to scn 1952615;
 
Flashback complete.
 
sys@TEST0910> select count(*) from scott.test10;
 
  COUNT(*)
----------
        14
 
1 row selected.

 

flashback database实验（使用lgmnr找出还原时间点）

过程：激活日志--删数据--查日志--logmnr--关库--不用转储--mount--flashback数据库（时间减一秒）--read only
开库看正确与否--关库--mount--开库resetlogs
DDL语句的恢复：类似不完全恢复，可以恢复到某个时间点，依赖flashback log.
logmnr依赖的是redo和归档。
flashback database一般用于ddl操作，
dml用flashback query或flashback table。因为他们读undo块
ddl语句不生成undo块
 
闪回数据库有限制：比如解决不了media recovery等（硬盘坏了等）。还是备份最重要。
1、使用logmnr来记录时间点，首先必须设置有一下参数
show parameter utl; logmnr需要的路径
 
sys@TEST0910> show parameter utl
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines               string
utl_file_dir                         string      /u01/app/oracle/utl
 
使用flashback database首先设置两个参数：
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size           big integer 4122M
 
sys@TEST0910> show parameter recovery
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_
                                                 area
db_recovery_file_dest_size           big integer 4122M
 
 
而且需要alter database flashback on
ys@TEST0910> select name,flashback_on from v$database;
 
NAME                                               FLASHBACK_ON
-------------------------------------------------- ------------------
TEST0910                                           YES
 
1 row selected.
 
2、查询原数据
sys@TEST0910> select count(*) from scott.test12;
 
  COUNT(*)
----------
        14
 
1 row selected.
 
3.激活logmnr，注意，必须有此步骤，不然logmnr出不来数据
sys@TEST0910> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
 
Database altered.
 
4、误操作DDL操作
sys@TEST0910> truncate table scott.test12;
 
Table truncated.
 
sys@TEST0910> select count(*) from scott.test12;
 
  COUNT(*)
----------
         0
 
1 row selected.
 
5、查询当前日志组和归档日志
sys@TEST0910> select * from v$log;
 
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
         1          1          7   52428800        512          1 YES INACTIVE               1977009 2013-09-23 15:04:56      1981677 2013-09-23 15:05:11
         2          1          8   52428800        512          1 YES ACTIVE                 1981677 2013-09-23 15:05:11      1985831 2013-09-23 15:05:41
         3          1          9   52428800        512          1 NO  CURRENT                1985831 2013-09-23 15:05:41   2.8147E+14
 
3 rows selected.
 
sys@TEST0910> select * from v$logfile;
 
    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         2         ONLINE  /u01/app/oracle/oradata/test0910/redo02.log        NO
         1         ONLINE  /u01/app/oracle/oradata/test0910/redo01.log        NO
         3         ONLINE  /u01/app/oracle/oradata/test0910/redo03.log        NO
 
3 rows selected.
 
sys@TEST0910> select name from v$archived_log;
 
NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_6_94146rg7_.arc
/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_7_941477xf_.arc
/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_8_941485th_.arc
 
61 rows selected.
 
 
6、执行logmnr
 
EXECUTE DBMS_LOGMNR_D.BUILD(‘dictionary.ora‘, -
   ‘/u01/app/oracle/utl‘, -
    DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
 
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
   LOGFILENAME => ‘/u01/app/oracle/oradata/test0910/redo03.log‘, -
   OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
   LOGFILENAME => ‘/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_6_94146rg7_.arc‘, -
   OPTIONS => DBMS_LOGMNR.ADDFILE);
   EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
   LOGFILENAME => ‘/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_7_941477xf_.arc‘, -
   OPTIONS => DBMS_LOGMNR.ADDFILE);
   EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
   LOGFILENAME => ‘/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_8_941485th_.arc‘, -
   OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
   DICTFILENAME =>‘/u01/app/oracle/utl/dictionary.ora‘,OPTIONS => -
   DBMS_LOGMNR.DDL_DICT_TRACKING);
EXECUTE DBMS_LOGMNR.END_LOGMNR;
 
logminer官方参考：http://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm#i1009063
 
以下为执行步骤
sys@TEST0910> EXECUTE DBMS_LOGMNR_D.BUILD(‘dictionary.ora‘, -
> ‘/u01/app/oracle/utl‘, -
> DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
LogMnr Dictionary Procedure started
LogMnr Dictionary File Opened
Procedure executed successfully - LogMnr Dictionary Created
 
PL/SQL procedure successfully completed.
 
sys@TEST0910> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
> LOGFILENAME => ‘/u01/app/oracle/oradata/test0910/redo03.log‘, -
> OPTIONS => DBMS_LOGMNR.NEW);
 
PL/SQL procedure successfully completed.
 
sys@TEST0910> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
> LOGFILENAME => ‘/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_8_941485th_.arc‘, -
> OPTIONS => DBMS_LOGMNR.ADDFILE);
 
PL/SQL procedure successfully completed.
 
sys@TEST0910>  EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
> LOGFILENAME => ‘/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_7_941477xf_.arc‘, -
> OPTIONS => DBMS_LOGMNR.ADDFILE);
 
PL/SQL procedure successfully completed.
 
sys@TEST0910> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
> LOGFILENAME => ‘/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_23/o1_mf_1_6_94146rg7_.arc‘, -
> OPTIONS => DBMS_LOGMNR.ADDFILE);
 
PL/SQL procedure successfully completed.
 
sys@TEST0910> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
> DICTFILENAME =>‘/u01/app/oracle/utl/dictionary.ora‘,OPTIONS => -
> DBMS_LOGMNR.DDL_DICT_TRACKING);
 
PL/SQL procedure successfully completed.
 
7、设置session时间格式
 
sys@TEST0910> alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss‘;
 
Session altered.
 
 
8、查询logmnr分析出的数据
sys@TEST0910> select username,scn,timestamp,sql_redo from v$logmnr_contents
  2  where seg_name=‘TEST12‘;
 
USERNAME                              SCN TIMESTAMP           SQL_REDO
------------------------------ ---------- ------------------- --------------------------------------------------
SYS                               1989009 2013-09-23 15:09:53 truncate table scott.test12;
 
sys@TEST0910> EXECUTE DBMS_LOGMNR.END_LOGMNR;
 
PL/SQL procedure successfully completed.
 
8、mount状态下闪回
sys@TEST0910> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TEST0910> startup mount;
ORACLE instance started.
 
Total System Global Area 2505338880 bytes
Fixed Size                  2230952 bytes
Variable Size             587203928 bytes
Database Buffers         1895825408 bytes
Redo Buffers               20078592 bytes
Database mounted.
 
sys@TEST0910> flashback database to timestamp to_timestamp(‘2013-09-23 15:09:52‘,‘yyyy-mm-dd hh24:mi:ss‘); 
此处注意，时间在logmnr查询出的基础上减1秒
 
Flashback complete.
 
9、让数据库置于read only，检查数据是否正确恢复
sys@TEST0910> alter database open read only;
 
Database altered.
 
sys@TEST0910> select count(*) from scott.test12;
 
  COUNT(*)
----------
        14
 
1 row selected.
 
10、重新启动数据库，使用resetlogs打开数据库
sys@TEST0910> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TEST0910> startup mount;
ORACLE instance started.
 
Total System Global Area 2505338880 bytes
Fixed Size                  2230952 bytes
Variable Size             587203928 bytes
Database Buffers         1895825408 bytes
Redo Buffers               20078592 bytes
Database mounted.
sys@TEST0910> alter database open
  2  ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
 
 
sys@TEST0910> alter database open resetlogs;
 
Database altered.

 

 


联系
我们