Flashback Database

最新推荐文章于 2024-10-25 10:00:00 发布

原创最新推荐文章于 2024-10-25 10:00:00 发布 · 置顶 · 870 阅读

0 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

57 篇文章

订阅专栏

本文介绍如何使用Oracle的Flashback Database功能进行数据库恢复，包括检查Flash Recovery Area配置、确认归档模式及Force Logging状态、启用Flashback功能、记录SCN、执行数据库闪回并选择合适的打开方式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Flashback Database命令可以在RMAN和SQL*PLUS里面执行：

[root@linux5 ~]# su - oracle
[oracle@linux5 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 2 00:18:06 2014

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              75499764 bytes
Database Buffers           88080384 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> host clear

查看是否启动Flash Recovery Area（分配充足，容纳数据文件、控制文件、重做日志文件、备份文件、增量备份文集、尚未备份的归档文件、Flashback Logs……）
SQL> show parameter db_recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 2G

检查是否启用归档：
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/arch_orcl
Oldest online log sequence     57
Next log sequence to archive   59
Current log sequence           59

检查是否启用Flashback Database和Force Logging
SQL> select flashback_on,force_logging from v$database;

FLASHBACK_ON FOR
------------------ ---
NO YES

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              75499764 bytes
Database Buffers           88080384 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> alter database flashback on;

Database altered.

SQL> select flashback_on,force_logging from v$database;

FLASHBACK_ON FOR
------------------ ---
YES YES

SQL> select dbms_flashback.get_system_change_number from dual;
select dbms_flashback.get_system_change_number from dual
*
ERROR at line 1:
ORA-00904: "DBMS_FLASHBACK"."GET_SYSTEM_CHANGE_NUMBER": invalid identifier

SQL> alter database open;

Database altered.

查询当前的SCN

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
1249964

SQL> conn scott/oracle
Connected.
SQL> drop table temp purge;

Table dropped.

SQL> drop table flash_tbl purge;

Table dropped.

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 167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              75499764 bytes
Database Buffers           88080384 bytes
Redo Buffers                2973696 bytes
Database mounted.

恢复数据库：
SQL> flashback database to SCN 1249964;

Flashback complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

执行完Flashback Database命令之后，有两种打开方式：open resetlogs和read only。

open resetlogs：指定SCN之后的数据都会丢失。

read only ：打开数据库之后，通过逻辑导出误操作的表的数据，再执行recover database命令以重新应用数据库产生的REDO，将数据库修复到Flashback Database操作前的状态，然后通过逻辑导入的方式，将之前误操作的表重新导入，这样就不会有数据丢失了。
SQL> alter database open resetlogs;

Database altered.

SQL> conn scott/oracle
Connected.
SQL> select * from tab where tname in('flash_tbl','temp');

no rows selected

SQL> select * from tab where tname in('flash_tbl','temp');

no rows selected

SQL> select * from tab where tname in('FLASH_TBL','TEMP');

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
FLASH_TBL                      TABLE
TEMP                           TABLE

恢复操作成功……