在Oracle的建库过程中,通常会提示是否开启闪回并指定闪回恢复区大小,我一般会选择不开启。这样,如果需要使用闪回功能,就需要手动开启。
1.环境准备
我们在Oracle11g上进行测试。点击(此处)折叠或打开
- SQL> select * from v$version;
- BANNER
- --------------------------------------------------------------------------------
- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
- PL/SQL Release 11.2.0.3.0 - Production
- CORE 11.2.0.3.0 Production
- TNS for Linux: Version 11.2.0.3.0 - Production
- NLSRTL Version 11.2.0.3.0 - Production
- SQL>
2.查询闪回功能是否开启
可以通过查询v$database视图的flashback_on字段来获取闪回功能的状态,该字段是一个布尔类型,YES表示开启,NO表示未开启。点击( 此处 )折叠或打开
- SQL>
- SQL> select flashback_on from v$database;
- FLASHBACK_ON
- ------------------
- NO
- SQL>
3.开启归档功能
在开启闪回功能之前,我们必须首先开启数据库归档,否则数据库会报错。
- SQL>
- SQL> select flashback_on from v$database;
- FLASHBACK_ON
- ------------------
- NO
- SQL>
点击(此处)折叠或打开
- SQL>
- SQL> archive log list;
- Database log mode No Archive Mode
- Automatic archival Disabled
- Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
- Oldest online log sequence 16
- Current log sequence 20
- SQL>
- SQL> alter database flashback on;
- alter database flashback on
- *
- ERROR at line 1:
- ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
- ORA-38707: Media recovery is not enabled.
- SQL>
ORA-38706和ORA-38707两个报错提醒我们要开启数据库归档,步骤如下;
点击(此处)折叠或打开
- SQL>
- SQL> alter database archive;
- alter database archive
- *
- ERROR at line 1:
- ORA-02231: missing or invalid option to ALTER DATABASE
- SQL> alter database archivelog;
- alter database archivelog
- *
- ERROR at line 1:
- ORA-01126: database must be mounted in this instance and not open in any
- instance
- SQL>
- SQL>
- SQL> shutdown immediate
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SQL>
- SQL> startup mount
- ORACLE instance started.
- Total System Global Area 941600768 bytes
- Fixed Size 1348860 bytes
- Variable Size 536873732 bytes
- Database Buffers 398458880 bytes
- Redo Buffers 4919296 bytes
- Database mounted.
- SQL>
- SQL> alter database archivelog;
- Database altered.
- SQL>
- SQL> alter database open;
- Database altered.
- SQL>
- SQL> archive log list;
- Database log mode Archive Mode
- Automatic archival Enabled
- Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
- Oldest online log sequence 16
- Next log sequence to archive 20
- Current log sequence 20
- SQL>
- SQL>
4.开启闪回功能
4.1设置参数
闪回功能和两个初始化参数有关,我们先认识一下,其中, db_recovery_file_dest_size表示闪回恢复区大小,db_recovery_file_dest表示闪回恢复区路径。点击(此处)折叠或打开
- SQL>
- SQL> show parameter db_recovery
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- db_recovery_file_dest string
- db_recovery_file_dest_size big integer 0
- SQL>
点击(此处)折叠或打开
- SQL>
- SQL> alter system set db_recovery_file_dest=\'/home/oracle/flashback\';
- alter system set db_recovery_file_dest=\'/home/oracle/flashback\'
- *
- ERROR at line 1:
- ORA-02097: parameter cannot be modified because specified value is invalid
- ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE
- SQL>
- SQL> alter system set db_recovery_file_dest_size=2g;
- System altered.
- SQL>
- SQL> alter system set db_recovery_file_dest=\'/home/oracle/flashback\';
- System altered.
- SQL>
- SQL> show parameter db_recovery
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- db_recovery_file_dest string /home/oracle/flashback
- db_recovery_file_dest_size big integer 2G
- SQL>
4.2开启闪回功能
需要注意的一点是,在10G中,如果要开启数据库级别的闪回,需要设置相关的参数,并且使数据库处于归档模式,然后再在MOUNT状态下开启闪回。在11G中,如果设置了相关的参数及其开启了归档,那么可以再OPEN状态下打开闪回。这也算是Oracle 11g的一个新特性。点击(此处)折叠或打开
- SQL>
- SQL> select status from v$instance;
- STATUS
- ------------
- OPEN
- SQL>
- SQL> alter database flashback on;
- Database altered.
- SQL>
- SQL> select flashback_on from v$database;
- FLASHBACK_ON
- ------------------
- YES
- SQL>
5.关闭闪回功能
点击(此处)折叠或打开
- SQL>
- SQL> alter database flashback off;
- Database altered.
- SQL> select flashback_on from v$database;
- FLASHBACK_ON
- ------------------
- NO
- SQL>
6.总结
从上面的过程来看,需要注意几点:1.Oracle11g支持在Open状态下开启闪回功能,这一点和Oracle 10g等较早版本不一样,算是一个新特性;
2.开启数据库闪回的前提条件是,开启数据库归档;