如何开启 oracle 闪回功能

来源：互联网发布：linux用的什么sh指令编辑：程序博客网时间：2024/05/16 12:47

单实例开启闪回

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