Oracle10g 闪回数据库 (Flashback Database)

前端之家收集整理的这篇文章主要介绍了Oracle10g 闪回数据库 (Flashback Database)前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

生产上要做灾备系统的应急切换演练,灾备端是由OGG搭建的。由于生产库都比较大10多T,不想演练后重新初始化灾备库,生产库版本为10.2.0.4,于是想到10g的新特性flashback database。演练开始前记录一个还原点,演练结束后闪回到这个还原点,重新同步ogg即可。

测试闪加数据库

一、开启FLASHBACK DATABASE

数据库版本:10.2.0.4

启用FLASHBACK DATABASE数据库必须为ARCHIVELOG模式,还需要有一个闪回恢复区用于存储闪回日志,RAC中闪回恢复区必须在集群文件系统或ASM中。

--数据库已开启归档,但还未启用FLASHBACK
sql>selectflashback_on,log_modefromv$database;

FLASHBACK_ON					LOG_MODE
------------------------------------------------------------------------------------------
NO						ARCHIVELOG
--调整参数
sql>altersystemsetdb_recovery_file_dest_size=3Gscope=spfile;

Systemaltered.

sql>altersystemsetdb_recovery_file_dest='/u01/app/oracle/flashback_area/'scope=spfile;

Systemaltered.

sql>altersystemsetdb_flashback_retention_target=2880scope=spfile;

Systemaltered.
--关闭数据库
sql>shutdownimmediate;
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.
--启动到mount状态,开启FLASHBACK
sql>startupmount;
ORACLEinstancestarted.

TotalSystemGlobalArea599785472bytes
FixedSize		2085776bytes
VariableSize		192941168bytes
DatabaseBuffers	398458880bytes
RedoBuffers		6299648bytes
Databasemounted.
sql>alterdatabaseflashbackon;

Databasealtered.
--启动数据库
sql>alterdatabaSEOpen;

Databasealtered.
--查看新状态
sql>selectflashback_on,log_modefromv$database;

FLASHBACK_ON					LOG_MODE
------------------------------------------------------------------------------------------
YES						ARCHIVELOG

官方文档:http://docs.oracle.com/cd/B19306_01/backup.102/b14192/rpfbdb003.htm#sthref509

db_recovery_file_dest_size参数表示闪回恢复区的大小

db_recovery_file_dest参数表示闪回恢复区目录

db_flashback_retention_target表是闪回日志保留的时间,单位为分,默认1440(1天)

查看闪回恢复区里已生成闪回日志

[oracle@rhel5flashback_area]$cd/u01/app/oracle/flashback_area/MYDB/flashback/
[oracle@rhel5flashback]$ls-l
total8020
-rw-r-----1oracleoinstall8200192Jan914:48o1_mf_d76cxc1r_.flb

二、创建测试表

sql>connzx/zx
Connected.
sql>createtableflash_tasselect*fromall_tables;

Tablecreated.

sql>insertintoflash_tselect*fromflash_t;

1559rowscreated.

sql>/

3118rowscreated.

sql>commit;

Commitcomplete.

sql>selectcount(*)fromflash_t;

COUNT(*)
----------
6236

三、查看数据库可以闪回的时间点

sql>select*fromV$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCNOLDEST_FLASHBACK_RETENTION_TARGETFLASHBACK_SIZEESTIMATED_FLASHBACK_SIZE
-------------------------------------------------------------------------------------------
	77626682017010914:37:32		288014467072		590954496

四、测试闪回数据库到一个时间点

--记录当前SCN用于闪回数据库
sql>SELECTCURRENT_SCNFROMV$DATABASE;

CURRENT_SCN
-----------
7765990
--删除测试表
sql>conn/assysdba
Connected.
sql>selectcount(*)fromzx.flash_t;

COUNT(*)
----------
6236

sql>droptablezx.flash_t;

Tabledropped.

sql>selectcount(*)fromzx.flash_t;
selectcount(*)fromzx.flash_t
*
ERRORatline1:
ORA-00942:tableorviewdoesnotexist
--闪回数据库
sql>shutdownimmediate;
Databaseclosed.
Databasedismounted.
ORACLEinstanceshutdown.
sql>startupmount;
ORACLEinstancestarted.

TotalSystemGlobalArea599785472bytes
FixedSize		2085776bytes
VariableSize		192941168bytes
DatabaseBuffers	398458880bytes
RedoBuffers		6299648bytes
Databasemounted.
--执行闪回
sql>flashbackdatabasetoscn7765990;

Flashbackcomplete.
--以resetlogs方式打开数据库
sql>alterdatabaSEOpenresetlogs;
--可以使用alterdatabaSEOpenreadonly打开数据库查看闪回后数据库的状态,如果闪回的不理想,重启到mount模式重新做flashbackdatabase
Databasealtered.
--验证测试表得到恢复
sql>selectcount(*)fromzx.flash_t;

COUNT(*)
----------
6236

闪回还可以指定时间或还原点

FLASHBACK DATABASE TO RESTORE POINT BEFORE_CHANGES;

FLASHBACK DATABASE TO TIME"TO_DATE('09/20/00','MM/DD/YY')";

官方文档:http://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr005.htm#BGBDCAFA

猜你在找的Oracle相关文章