su - oracle
1.登录sqlplus,打开sqlplus的时间提示功能
sqlplus "/ as sysdba"
set time on
2.查看是否使用了闪回功能。如果FLASHBACK_ON为NO说明闪回功能未使用
SELECT FLASHBACK_ON,LOG_MODE FROM V$DATABASE;
---------------------------------
FLASHBACK_ON LOG_MODE
------------------ ------------
NO NOARCHIVELOG
3.关闭数据库
shutdown immediate
4.启动例程并装载数据库
STARTUP MOUNT;
5.配置数据库归档模式
ALTER DATABASE ARCHIVELOG;
6.查看恢复区设置
show parameter db_recovery;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
7.恢复区未设置,创建恢复区目录
host mkdir flash_recocery_area;
设置恢复区大小
alter system set db_recovery_file_dest_size='2G';
设置恢复区路径
alter system set db_recovery_file_dest='/opt/ora/flash_recovery_area';
8.打开闪回开关并创建担保还原点
alter database flashback on;
CREATE RESTORE POINT SP1 GUARANTEE FLASHBACK DATABASE;
查看变更结果
select flashback_on,LOG_MODE from v$database;
SELECT NAME,TO_CHAR(TIME,'YYYY/MM/DD HH24:MI:SS'),GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT;
9.找开数据库
ALTER DATABASE OPEN;
10.执行变更测试
create table test(id int);
insert into test values('1');
commit;
11.关闭数据库
shutdown immediate;
12.启动例程加载数据库
startup mount
13.还原数据库
flashback database to timestamp to_date('2013-02-17 15:18:16','yyyy-mm-ddhh24:mi:ss');
或者使用担保还原点
FLASHBACK DATABASE TO RESTORE POINT SP1;
14.打开数据库并验证结果
alter database open read only;
select * from test; --此时就报错,test不存在
15.关闭数据库再重新打开
shutdown immediate
startup mount
alter database open resetlogs;