闪回系列专题(1)FlashBack Database

前不久,参加了一个Oracle原厂的网络研讨会,其中宣讲人再介绍Oracle MAA架构时提到了数据恢复的问题,会上宣讲人提到了许多Oracle的闪回技术,周末值守无法出门,那就在这里做一个闪回系列总结实验记录一下,顺便对闪回技术进行一次回顾。

Flashback Database,此项操作在之前的利用DataGuard 恢复误操作数据时提到过,原理一致。

--引用Oracle 官方文档针对FlashBack Database的简要功能
Use the FLASHBACK DATABASE statement to return the database to a past time or system change number (SCN). This statement provides a fast alternative to performing incomplete database recovery.

Following a FLASHBACK DATABASE operation, in order to have write access to the flashed back database, you must reopen it with an ALTER DATABASE OPEN RESETLOGS statement. 

实验环境为 Oracle 11.2.0.4 RAC in 3 nodes

1.首先设置闪回恢复区并且开启闪回特性,否则会抛出如下错误

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.

SQL> alter system set db_recovery_file_dest_size=30g;

System altered.

SQL> alter system set db_recovery_file_dest='+DG_DATA';

System altered.
 
SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +DG_DATA
db_recovery_file_dest_size           big integer 30G
recovery_parallelism                 integer     0

SQL> alter database flashback on;

Database altered.

创建测试表,删除测试表数据,以便是否验证数据恢复与否

SQL> create table ftest as select object_id from dba_objects;

Table created.

SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'
-----------------
20200425 10:25:51

SQL> truncate table ftest;

Table truncated.

数据已经被破坏,确认闪回日志写入FRA后,准备开启闪回操作,将RAC节点实例关闭

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

将节点1启动到Mount模式:

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.3495E+11 bytes
Fixed Size                  2270072 bytes
Variable Size            3.6507E+10 bytes
Database Buffers         9.8247E+10 bytes
Redo Buffers              197844992 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('20200425 10:28:10','yyyymmdd hh24:mi:ss');  --时间点为

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from ftest;

  COUNT(*)
----------
     72275

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DG_ARC
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1
SQL> 

闪回完成后,将RAC其他节点启动即可。

这里,需要注意的是我们在分配FRA区域时,要考虑到数据库事务繁忙程度,以免造成因为FRA区域满,无法写入flashback log导致闪回失败的情形,我将FRA区域改为30m,进行事务操作,因FRA满,闪回日志无法写入,缺失闪回日志所以无法进行闪回操作。

ORA-19815: WARNING: db_recovery_file_dest_size of 31457280 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
Setting Resource Manager plan SCHEDULER[0x32DE]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sat Apr 25 11:29:54 2020
Beginning global checkpoint up to RBA [0x79.272.10], SCN: 108173284
Completed checkpoint up to RBA [0x79.272.10], SCN: 108173284


QL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,
-----------------
20200425 11:31:21


SQL> flashback database to timestamp to_timestamp('20200425 11:30:00','yyyymmdd hh24:mi:ss');
flashback database to timestamp to_timestamp('20200425 11:30:00','yyyymmdd hh24:mi:ss')
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.


SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
           108194136 25-APR-20             1440     1467973632                        0

SQL> 
SQL> 
SQL> 
SQL> select * from V$RECOVERY_AREA_USAGE;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                          0                         0               0
BACKUP PIECE                          0                         0               0
IMAGE COPY                            0                         0               0
FLASHBACK LOG                      4096                         0               6
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.


--可以利用v$flashback_database_log与V$RECOVERY_AREA_USAGE视图来监控目前闪回日志的大小与可用空间。
--可以利用如下方式估算FRA的合理配置大小
SQL> set numwidth 16
SQL> select to_char(BEGIN_TIME,'yyyymmdd hh24:mi:ss') BEGIN_TIME,to_char(END_TIME,'yyyymmdd hh24:mi:ss'),FLASHBACK_DATA,DB_DATA,REDO_DATA,ESTIMATED_FLASHBACK_SIZE from V$FLASHBACK_DATABASE_STAT;

BEGIN_TIME        TO_CHAR(END_TIME,   FLASHBACK_DATA          DB_DATA        REDO_DATA ESTIMATED_FLASHBACK_SIZE
----------------- ----------------- ---------------- ---------------- ---------------- ------------------------
20200425 13:44:03 20200425 14:44:05         85270528        113090560         99827200             134545121280
20200425 12:37:54 20200425 13:44:03       6095396864      11968888832      14131610624             132688896000