《三思笔记》--读书笔记
flashback database要求数据库在归档模式,且数据库必须启动flashback database和force logging
制约因素:
1,flashback database 用来将数据库中的数据恢复至之前的某个时间点,而非介质恢复,因此不能使用flashback database恢复之前被删除的某个数据文件
2,如果控制文件被重建,则在此之前所产生的所有flashback logs通通失效,也就是说不能将flashback databse恢复到控制文件被重建之前
3,不支持对数据库执行过shrink操作后的恢复
flashback database操作示例
1,将数据库启动flashback database和force logging
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 318770480 bytes
Database Buffers 92274688 bytes
Redo Buffers 4272128 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.
SQL> alter database force logging;
Database altered.
SQL> alter database open;
Database altered.
2,检查是否启动了flash recovery area
SQL> conn / as sysdba
Connected.
SQL> show parameter DB_RECOVER
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/oracle/app/oracle/fast_re
covery_area
db_recovery_file_dest_size big integer 4122M
3,检查是否启用了归档
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/arch_log
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
4,检查是否启用了flashback database 和force logging
SQL> select flashback_on,force_logging from v$database;
FLASHBACK_ON FOR
------------------ ---
YES YES
5,查询当前的SCN
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1806450
6,模拟误删除操作
SQL> conn cindy/cindy;
Connected.
SQL> drop table flash_tbl purge;
Table dropped.
7,重新启动到Mount 状态,然后执行恢复
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 318770480 bytes
Database Buffers 92274688 bytes
Redo Buffers 4272128 bytes
Database mounted.
SQL> flashback database to scn 1806450;
Flashback complete.
至此之后,有两种方式打开数据库
1)alter database open resetlogs
2)另一种方式是先执行alter database open read only打开数据库,然后立刻通过逻辑导出的方式将误操作涉及表的数据导出,再执行recover database命令以重新应用数据库产生的redo,将数据库修复到flashback database操作前的状态,然后再通过逻辑导入的方式,将之前误操作的表重新导入
SQL> alter database open resetlogs;
Database altered.
SQL> conn cindy/cindy;
Connected.
SQL> select * from flash_tbl;
ID VL
---------- --
8 G
9 H
10 I
11 J
12 K
13 L
14 M
15 N
116 O
117 P
118 Q
ID VL
---------- --
119 R
120 S
201 A1
202 B1
15 rows selected.
显然数据回来了