閃回數(shù)據(jù)庫的簡單介紹

閃回數(shù)據(jù)庫方法可以用sqlplus，也可以用RMAN執(zhí)行：

SQL> FLASHBACK DATABASE TO TIMESTAMP(''); --(基于時間點)

SQL> FLASHBACK DATABASE TO SCN ; --(基于SCN)

SQL> FLASHBACK DATABASE TO RESTORE POINT ; --(基于sestore point ，創(chuàng)建命令create restore point 名稱)

RMAN> FLASHBACK DATABASE TO TIME =  "TO_DATE('2009-05-27 16:00:00',  'YYYY-MM-DD HH24:MI:SS')";--(基于時間點)

RMAN> FLASHBACK DATABASE TO SCN=23565; --(基于SCN)

RMAN> FLASHBACK DATABASE TO SEQUENCE=223 THREAD=1; --（序列號，可用list incarnation of database 查看當(dāng)前sequence)

上述兩種方法都可以實現(xiàn)flashback database需要注意的是操作完成后使用命令打開數(shù)據(jù)庫alter database open resetlogs

下面來演示下基于時間點的閃回數(shù)據(jù)庫實驗：

1.查看當(dāng)前歸檔

SQL> archive log list

Database log mode       Archive Mode

Automatic archival       Enabled

Archive destination       USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     25

Next log sequence to archive   27

Current log sequence       27

2.查看下閃回數(shù)據(jù)庫打開沒，如果沒打開使用alter database flashback on命令打開

SQL> select flashback_on from v_$database;

FLASHBACK_ON

------------------

YES

3.先查詢當(dāng)前時間

SQL> select to_char(sysdate,'yyyy-mm-dd:hh34:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

-------------------

2017-07-17:16:16:45

4.切換HR用 ，用HR建個表TMP717并 插入一行數(shù)據(jù)，接著把表刪除（加上PURGE），用DBA用戶切換日志

SQL> conn hr/123456

Connected.

SQL> create table tmp717 (cons_no varchar2(10));

Table created.

SQL> insert into tmp717 values('1');

1 row created.

SQL> commit;

Commit complete.

SQL> select to_char(sysdate,'yyyy-mm-dd:hh34:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY

-------------------

2017-07-17:16:20:47

SQL> drop table tmp717 purge 

  2  ;

Table dropped.

SQL> conn / as sysdba

Connected.

SQL> alter system switch logfile;

System altered.

SQL> archive log list

Database log mode       Archive Mode

Automatic archival       Enabled

Archive destination       USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     26

Next log sequence to archive   28

Current log sequence       28

（當(dāng)前日志變成28）

5.關(guān)閉數(shù)據(jù)庫，啟動到MOUNT狀態(tài)（閃回數(shù)據(jù)庫必須的操作）

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  820236288 bytes

Fixed Size    1339628 bytes

Variable Size  549457684 bytes

Database Buffers  264241152 bytes

Redo Buffers    5197824 bytes

Database mounted.

SQL> exit

6.打開RMAN進行閃回操作

[oracle@station4 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jul 17 16:31:13 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1359978017, not open)

RMAN> list incarnation of database ;--（先查看下當(dāng)前化身）

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1       1       ORCL     1359978017       PARENT  1          13-AUG-09

2       2       ORCL     1359978017       CURRENT 754488     22-NOV-13

RMAN> run {

2> sql "alter session set nls_date_format=''YYYY-MM-DD:HH24:MI:SS''";

3> set until time '2017-07-17:16:20:47';

4> restore database;

5> recover database;}

7.用RESETLOGS打開數(shù)據(jù)庫，并查詢表還原成功

SQL> alter database open resetlogs;

Database altered.

SQL> select * from hr.tmp717;

CONS_NO

----------

1

8.重新查看化身

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     1359978017       PARENT  1          13-AUG-09

2       2       ORCL     1359978017       PARENT  754488     22-NOV-13

3       3       ORCL     1359978017       CURRENT 1083685    17-JUL-17

上述實驗也可以用SQL> FLASHBACK DATABASE TO TIMESTAMP('2017-07-17:16:20:47'); 操作結(jié)果是一樣的

實驗中看出化身了一個，當(dāng)前化身號是1083685，并且刪除的表還原回來了，表示成功執(zhí)行。