flashback之flashback database

《三思笔记》--读书笔记

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.

显然数据回来了