闪回数据库使用的是闪回日志,闪回日志存在于闪回目录(也就是快速闪回区中)
闪回日志:就是数据块修改之前的镜像,简称前像
1.查看闪回目录的位置:
show parameter recovery
如果闪回目录没有设定,要先设定
先设定大小:alter system set db_recovery_file_dest_size=2G;
再设定位置:alter system set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area';
闪回日志就存在于/u01/app/oracle/flash_recovery _area目录中
发现没有闪回日志因为闪回数据库功能没有打开:
SQL> select flashback_on from v$database;
2. 打开闪回数据库:
数据库必须处于归档模式:
SQL> archive log list
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog
SQL> alter database flashback on
SQL> alter database open;
SQL> select flashback_on from v$database;
cd /u01/app/oracle/flash_recovery_area/ORCL/flashback
ls
o1_mf_8vt1w7cz_.flb--这个文件就是闪回日志
闪回日志:就是数据块修改之前的镜像,简称前像,由RVWR进程写入闪回日志
ps -ef | grep rvwr
3. 使用闪回数据库恢复scott用户
模拟损坏:
SQL> drop user scott cascade;
SQL> create user tt default tablespace users identified by a;
SQL> grant connect, resource to tt;
SQL> conn tt/a
SQL> create table tt as select * from tab;
SQL> select * from tt;
SQL> conn /as sysdba
select 'execute dbms_logmnr.add_logfile('''||member||''')' from v$logfile
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/orcl/redo03.log')
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/orcl/redo02.log')
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/orcl/redo01.log')
SQL> exec dbms_logmnr.start_logmnr();
SQL> select scn, sql_redo from v$logmnr_contents where sql_redo like '%drop%';
SQL> select min(scn) from v$logmnr_contents where sql_redo like '%drop%' --找最新的scn
SQL> shutdown immediate
SQL> startup mount;
SQL> flashback database to scn 521102;
SQL> alter database open; --error
SQL> alter database open read only;--以只读方式打开,检查数据是否已经闪回,如果没有闪回,可以启动到mount重新闪回
select name, space_limit as quota, space_used as used, space_reclaimable as reclaimable, number_of_files as files from v$recovery_file_dest ;
列说明:
NAME:快速恢复区名称,指示位置字符串
SPACE_LIMIT:DB_RECOVERY_FILE_DEST_SIZE 参数中指定的磁盘限额
SPACE_USED:快速恢复区文件使用的空间(以字节表示)
SPACE_RECLAIMABLE:使用空间管理算法,通过删除过时、冗余的以及其它低优先级的文件而回收的空间大小
NUMBER_OF_FILES:文件数
5)查看哪些文件使用闪回区域:
select flashback_on from v$database;
create restore point b1;
select scn,TIME,NAME from v$restore_point;
drop user scott cascade;
SQL> shutdown immediate
SQL> flashback database to restore point b1;----b1就相当于scn号
SQL> alter database open resetlogs;
SQL> conn scott/tiger
SQL> select * from tab;
SQL> conn /as sysdba
闪回数据库无论打开还是关闭,都可以创建normal restore point
select flashback_on from v$database;
drop restore point b1;
shutdown immediate
startup mount
alter database flashback off;
select flashback_on from v$database;
alter database open;
create restore point b1;
drop restore point b1;
2.2)Guaranteed restore point:
1. 它的功能与normal restore point的功能基本一致,也是作为SCN的一个别名
2. 与flashback database相关的特性,在执行flashback database到之前的某个时间点时,必须保证所需要的flashback log存在,创建一个guaranteed restore point,可以保证能将数据库flashback到该点,即使没有系统开启flashback database功能,这是因为,在创建guaranteed restore point之后,对于任何block的第一次变更,都会将其前映象整个的记录下来
如果没有开启flashback database功能,只能将数据库闪回到guaranteed restore point;如果启用了flashback database功能,那么guaranteed restore point可以保证能将数据库flashback到guaranteed restore point之后的任何时间点
如果数据库没有开启闪回数据库功能,创建guaranteed restore point。此时只能将数据库闪回到guaranteed restore point
实验:
select flashback_on from v$database;
create restore point b1 guarantee flashback database;---报错,由于没有启用闪回数据库功能
shutdown immediate
startup mount
create restore point b1 guarantee flashback database;
select flashback_on from v$database;
alter database open;
drop user scott cascade;
shutdown immediate
startup mount
flashback database to restore point b1;
alter database open resetlogs;
conn scott/tiger
此时只能将数据库闪回到guaranteed restore point
drop restore point b1;
select flashback_on from v$database;
shutdown immediate
startup mount
create restore point b1 guarantee flashback database;
select flashback_on from v$database;
alter database open;
create user test default tablespace users identified by a;
grant connect,resource to test;
create restore point b2;
drop user test cascade;
shutdown immediate
startup mount
flashback database to restore point b2;
alter database open;
如果数据库开启闪回数据库功能,创建guaranteed restore point
此时能将数据库闪回到guaranteed restore point之后的任何时间点
select flashback_on from v$database;
create restore point b1 guarantee flashback database;---创建成功,由于启用闪回数据库功能
select flashback_on from v$database;
create user test default tablespace users identified by a;
grant connect,resource to test;
create restore point b2;
drop user test cascade;
shutdown immediate
startup mount
flashback database to restore point b2;
alter database open resetlogs;
查询restore point:
select NAME, SCN, TIME, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE from V$RESTORE_POINT
name:restore point的名称
scn:创建restore point时的scn
time:创建restore point的时间点
GUARANTEE_FLASHBACK_DATABASE:是否是确保
STORAGE_SIZE:为了满足确保,闪回日志所占用的磁盘空间大小