oracle闪回默认是开启的吗,如何开启 oracle 闪回功能

单实例开启闪回

1、查看是否开启闪回

SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------

NO

2、查看闪回恢复区和大小是否设置

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string

db_recovery_file_dest_size           big integer 0

设置闪回恢复区路径：

SQL> alter system set db_recovery_file_dest ='/opt/flash_recovery_area' scope=spfile;

System altered.

设置闪回恢复区大小

SQL> alter system set db_recovery_file_dest_size =2 G scope=spfile;

System altered.

查看闪回retyention时间

SQL> show parameter db_flashback_retention_target

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_flashback_retention_target        integer     1440

3、查看数据库是否处于归档模式

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     5261

Next log sequence to archive   5270

Current log sequence           5270

** 如果没有开启归档需要将数据库设置为归档模式，在数据库MOUNT状态下;alter database archivelog;

4、关闭数据库开启闪回功能

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                  2213776 bytes

Variable Size            1023412336 bytes

Database Buffers          570425344 bytes

Redo Buffers                7360512 bytes

Database mounted.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

查看是否开启闪回功能；

SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------

YES

SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

OLDEST_FLASHBACK_SCN     OLDEST_FL

---------------------------------------      -----------------------

43197582                             13-AUG-12

恢复删除表测试：

SQL> drop table  test.order_items;

恢复表；

SQL> flashback table test.order_items to before drop;

Flashback complete.

SQL> select * from test.order_items;

ORDER_ID LIN_ITEM_ID PRODUCT_ID   QUANTITY  UNI_PRICE ---------- ----------- ---------- ---------- ----------        104           5          5        100           1          1          5        100        101           2          2         30         50        102           3          4          2        100        103           4          5         10         70