第一种场景 库是用来培训或测试环境的,测试完毕后恢复原来的样子

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     //闪回区

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    //闪回数据库

 

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.

 

在转到闪回目录查看有新的文件产生

[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、  在一起测试环境使用

(1)、建立还原点

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

Restorepoint created.

 

(2)、查看表中数据的情况

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)、进行数据库操作

 

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)、闪回数据库,关闭数据库启动到mount状态下进行闪回

 

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账号,检查数据是否符合要求

 

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)、检查数据后重新打开数据库

 

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

 

第二种场景,数据文件实验切换,原来的数据文件有数据,为了测试新的数据删除表中的数据

(1)、模拟数据

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 hh24: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)、加入一条数据,进行测试,

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)、测试完毕后可以闪回到之前表的状态

 

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 hh24:mi:ss');

Flashbackcomplete.

 

SYS@ odb>alter database open read only;

Database altered.

 

(6)、登录test_fb账号进行检查,和原来的数据一样

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)、正式打开数据库

 

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.