首页 > 代码库 > 11G 闪回数据库
11G 闪回数据库
第一种场景 库是用来培训或测试环境的，测试完毕后恢复原来的样子
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.
11G 闪回数据库