11G 闪回数据库

发布时间：2020-05-22 04:54:31 作者：vast2006
来源：网络阅读：416

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

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

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

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 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）、加入一条数据，进行测试，

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

TEST_FB@ odb>commit;

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

COUNT(*)

----------

（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 hh34: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.

11G 闪回数据库

相关阅读