11G 閃回數(shù)據(jù)庫

第一種場景 庫是用來培訓(xùn)或測試環(huán)境的，測試完畢后恢復(fù)原來的樣子

SYS@ odb>archive log list;

Databaselog mode            No Archive Mode

Automaticarchival             Disabled

Archivedestination           USE_DB_RECOVERY_FILE_DEST

Oldestonline log sequence      220

Currentlog sequence           222

SYS@odb>show parameter recover     //閃回區(qū)

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string    /u01/app/oracle/fast_recovery_area

db_recovery_file_dest_size           big integer 4977M

db_unrecoverable_scn_tracking        boolean     TRUE

recovery_parallelism                 integer     0

[oracle@wenxi~]$ cd /u01/app/oracle/fast_recovery_area

[oracle@wenxifast_recovery_area]$ ls

Odb

SYS@odb>show parameter flashback    //閃回數(shù)據(jù)庫

NAME                                 TYPE        VALUE

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

db_flashback_retention_target        integer    1440    （分鐘：24小時）

SYS@ odb>select flashback_onfrom v$database;

FLASHBACK_ON

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

NO

SYS@odb>shutdown immediate;

Databaseclosed.

Databasedismounted.

ORACLEinstance shut down.

SYS@odb>startup mount

ORACLEinstance started.

TotalSystem Global Area 1603411968 bytes

FixedSize                  2253664 bytes

VariableSize             905972896 bytes

DatabaseBuffers          687865856 bytes

RedoBuffers                7319552 bytes

Databasemounted.

SYS@odb>alter database flashback on;

alterdatabase flashback on

*

ERRORat line 1:

ORA-38706:Cannot turn on FLASHBACK DATABASE logging.

ORA-38707:Media recovery is not enabled.

SYS@odb>alter database archivelog;

Databasealtered.

SYS@odb>alter database flashback on;

Databasealtered.

SYS@odb>alter database open;

Databasealtered.

在轉(zhuǎn)到閃回目錄查看有新的文件產(chǎn)生

[oracle@wenxifast_recovery_area]$ ls

odb  ODB

[oracle@wenxifast_recovery_area]$ cd ODB/

[oracle@wenxiODB]$ ls

flashback

[oracle@wenxiODB]$ cd flashback/

o1_mf_cwp9ykby_.flb  o1_mf_cwp9ynj5_.flb    //閃回日志

1、  在一起測試環(huán)境使用

（1）、建立還原點

SYS@odb>create restore point hyd2016 guarantee flashback database;

Restorepoint created.

（2）、查看表中數(shù)據(jù)的情況

SYS@odb>SELECT NAME,SCN,TIME,DATABASE_INCARNATION#

DI,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE/1024/1024/1024  FROM V$RESTORE_POINT WHEREGUARANTEE_FLASHBACK_DATABASE='YES';

NAME              SCN TIME                         DI GUASTORAGE_SIZE/1024/1024/1024

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

HYD2016       5539977 04-SEP-16 03.52.53.0          1 YES                  .048828125

                      00000000 AM

（3）、進(jìn)行數(shù)據(jù)庫操作

TEST_FB@odb>create table t001 as select * from shanhui;

TEST_FB@odb>create table t002 as select * from shanhui;

TEST_FB@odb>delete from test;

10rows deleted.

TEST_FB@odb>commit;

Commitcomplete.

TEST_FB@odb>select * from test;

norows selected

（4）、閃回數(shù)據(jù)庫，關(guān)閉數(shù)據(jù)庫啟動到mount狀態(tài)下進(jìn)行閃回

SYS@odb>shutdown immediate;

Databaseclosed.

Databasedismounted.

ORACLEinstance shut down.

SYS@odb>startup mount

ORACLEinstance started.

TotalSystem Global Area 1603411968 bytes

FixedSize                  2253664 bytes

VariableSize             905972896 bytes

DatabaseBuffers          687865856 bytes

RedoBuffers                7319552 bytes

Databasemounted.

SYS@ odb>flashback database torestore point hyd2016;

Flashbackcomplete.

（5）、只讀方式打開，登錄test_fb賬號，檢查數(shù)據(jù)是否符合要求

SYS@ odb>alter database open readonly;

Databasealtered.

TEST_FB@ odb>conn test_fb/test_fb

Connected.

TEST_FB@ odb>select * from cat;

TABLE_NAME                     TABLE_TYPE

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

SHANHUI                        TABLE

TEST                           TABLE

2 rowsselected.

（6）、檢查數(shù)據(jù)后重新打開數(shù)據(jù)庫

SYS@odb>shutdown immediate

Databaseclosed.

Databasedismounted.

ORACLEinstance shut down.

SYS@odb>startup mount

ORACLEinstance started.

TotalSystem Global Area 1603411968 bytes

FixedSize                  2253664 bytes

VariableSize             905972896 bytes

DatabaseBuffers          687865856 bytes

RedoBuffers                7319552 bytes

Databasemounted.

SYS@odb>alter database open；

alterdatabase open

*

ERRORat line 1:

ORA-01589:must use RESETLOGS or NORESETLOGS option for database open

SYS@ odb>alter database open resetlogs;

Databasealtered

第二種場景，數(shù)據(jù)文件實驗切換，原來的數(shù)據(jù)文件有數(shù)據(jù)，為了測試新的數(shù)據(jù)刪除表中的數(shù)據(jù)

（1）、模擬數(shù)據(jù)

TEST_FB@odb>select * from cat;

TABLE_NAME                     TABLE_TYPE

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

SHANHUI                        TABLE

TEST                           TABLE

TEST_FB@odb>select count(*) from test;

  COUNT(*)

----------

   1310720

（2）、記錄時間戳

TEST_FB@odb>select to_char(systimestamp,'yyyy-mm-dd hh34:mi:ss') from dual;

TO_CHAR(SYSTIMESTAM

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

2016-09-0405:09:55

查看大小

TEST_FB@odb>select segment_name,bytes from user_segments where segment_name='TEST';

SEGMENT_NAME              BYTES

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

TEST                  142606336

（3）、truncate test表

TEST_FB@odb>truncate table test;

TEST_FB@odb>select * from test;

no rows selected

閃回日志

[oracle@wenxi flashback]$ls -l

total 102432

-rw-r-----. 1oracle oinstall 52445184 Sep  4 04:41o1_mf_cwp9ykby_.flb

-rw-r-----. 1oracle oinstall 52445184 Sep  4 03:43o1_mf_cwp9ynj5_.flb

（4）、加入一條數(shù)據(jù)，進(jìn)行測試，

TEST_FB@ odb>insert into test select * fromall_objects where rownum<2;

TEST_FB@ odb>commit;

TEST_FB@ odb>select count(*) from test;

  COUNT(*)

----------

         1

（5）、測試完畢后可以閃回到之前表的狀態(tài)

SYS@ odb>shutdown immediate;

Database closed.

Databasedismounted.

ORACLE instanceshut down.

SYS@ odb>startup mount;

ORACLE instancestarted.

Total SystemGlobal Area 1603411968 bytes

Fixed Size                  2253664 bytes

Variable Size             905972896 bytes

DatabaseBuffers          687865856 bytes

Redo Buffers                7319552 bytes

Database mounted.

SYS@odb>Flashback database to timestamp to_timestamp('2016-09-0405:09:55','yyyy- mm-dd hh34:mi:ss');

Flashbackcomplete.

SYS@ odb>alter database open read only;

Database altered.

（6）、登錄test_fb賬號進(jìn)行檢查，和原來的數(shù)據(jù)一樣

TEST_FB@odb>conn test_fb/test_fb

Connected.

TEST_FB@odb>select * from cat;

TABLE_NAME                     TABLE_TYPE

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

SHANHUI                        TABLE

TEST                           TABLE

2 rows selected.

TEST_FB@odb>select count(*) from test;

  COUNT(*)

----------

   1310720

（7）、正式打開數(shù)據(jù)庫

SYS@ odb>shutdown immediate;

Database closed.

Databasedismounted.

ORACLE instanceshut down.

SYS@ odb>startup mount;

ORACLE instancestarted.

Total SystemGlobal Area 1603411968 bytes

Fixed Size                  2253664 bytes

Variable Size             905972896 bytes

DatabaseBuffers          687865856 bytes

Redo Buffers                7319552 bytes

Database mounted.

SYS@ odb>alter database open resetlogs;

Database altered.