闪回数据库测试（flashback database）

最新推荐文章于 2022-12-12 18:12:07 发布

aaron8219

最新推荐文章于 2022-12-12 18:12:07 发布

阅读量947

点赞数

分类专栏： Oracle Flashback 文章标签： Oracle flashback database 闪回数据库 SCN TIMESTAMP

本文链接：https://blog.csdn.net/aaron8219/article/details/41630715

版权

Oracle Flashback 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

10g中，出现了一个功能就是闪回数据库，只要开启这个功能，就能把数据库闪回到之前某个时刻的状态，来看测试过程：

--查看闪回区路径及大小

SQL> show parameter DB_RECOVERY_FILE_DEST

NAME TYPE VALUE

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

db_recovery_file_dest string /u01/app/oracle/flash_recovery

_area

db_recovery_file_dest_size big integer 2G

--查看闪回保留目标

SQL> show parameter DB_FLASHBACK_RETENTION_TARGET

NAME TYPE VALUE

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

db_flashback_retention_target integer 1440 --单位是分钟，这里1440=24小时

SQL> select 1440/60 from dual;

1440/60

----------

--启动到mount状态开启闪回数据库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 524288000 bytes

Fixed Size 1220384 bytes

Variable Size 310378720 bytes

Database Buffers 209715200 bytes

Redo Buffers 2973696 bytes

Database mounted.

SQL> select name from V$bgprocess where paddr<>'00' order by 1;

NAME

-----

CJQ0

CKPT

DBW0

LGWR

MMAN

MMNL

MMON

PMON

PSP0

RECO

SMON

11 rows selected.

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

YES

SQL> select name from V$bgprocess where paddr<>'00' order by 1;

NAME

-----

CJQ0

CKPT

DBW0

LGWR

MMAN

MMNL

MMON

PMON

PSP0

RECO

RVWR --开启闪回数据库后，多了一个RVWR进程

SMON

12 rows selected.

SQL> alter database open;

Database altered.

--查看闪回数据库相关的视图

SQL> SELECT estimated_flashback_size,flashback_size FROM V$FLASHBACK_DATABASE_LOG;

ESTIMATED_FLASHBACK_SIZE FLASHBACK_SIZE

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

0 8192000

SQL> SELECT oldest_flashback_scn,oldest_flashback_time FROM V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLA

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

1395293 2014-11-30

SQL> SELECT * FROM V$FLASHBACK_DATABASE_STAT;

BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE

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

2014-11-30 2014-11-30 819200 65536 112128 0

--查看当前系统的SCN

SQL> select dbms_flashback.get_system_change_number() from dual;

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()

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

1396046

SQL> col name for a40

SQL> SELECT name, space_limit AS quota,space_used AS used,space_reclaimable AS reclaimable,

2 number_of_files AS files FROM v$recovery_file_dest ;

NAME QUOTA USED RECLAIMABLE FILES

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

/u01/app/oracle/flash_recovery_area 2147483648 37478912 0 16

SQL> set time on;

22:28:50 SQL> select dbms_flashback.get_system_change_number() from dual; --记住这个时间点

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()

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

1396148 --SCN号随着时间的流逝，是不断地向前递增的

--删除数据库中的某个表

22:29:10 SQL> drop table zlm.tbl purge;

Table dropped.

22:29:38 SQL> select dbms_flashback.get_system_change_number() from dual;

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()

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

1396181

22:30:11 SQL> flashback database to scn 1396148;

flashback database to scn 1396148

ERROR at line 1:

ORA-38757: Database must be mounted and not open to FLASHBACK.

必须要在mount状态下进行flashback database

22:30:22 SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

22:31:01 SQL> startup mount

ORACLE instance started.

Total System Global Area 524288000 bytes

Fixed Size 1220384 bytes

Variable Size 314573024 bytes

Database Buffers 205520896 bytes

Redo Buffers 2973696 bytes

Database mounted.

22:31:15 SQL> flashback database to scn 1396148;

Flashback complete.

22:31:47 SQL> alter database open;

alter database open

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

flashback database之后，必须以resetlogs或norestlogs选项来打开数据库

22:31:53 SQL> alter database open resetlogs;

Database altered.

22:32:20 SQL> select * from zlm.tbl;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980-12-17 3000 20

7499 ALLEN SALESMAN 7698 1981-02-20 3000 300 30

7521 WARD SALESMAN 7698 1981-02-22 3000 500 30

7566 JONES MANAGER 7839 1981-04-02 3000 20

7654 MARTIN SALESMAN 7698 1981-09-28 3000 1400 30

7698 BLAKE MANAGER 7839 1981-05-01 3000 30

7782 CLARK MANAGER 7839 1981-06-09 3000 10

7788 SCOTT ANALYST 7566 1987-04-19 3000 20

7839 KING PRESIDENT 1981-11-17 3000 10

7844 TURNER SALESMAN 7698 1981-09-08 3000 0 30

7876 ADAMS CLERK 7788 1987-05-23 3000 20

7900 JAMES CLERK 7698 1981-12-03 3000 30

7902 FORD ANALYST 7566 1981-12-03 3000 20

7934 MILLER CLERK 7782 1982-01-23 3000 10

14 rows selected.

通过闪回数据库到删除zlm.tbl表之前的SCN后，数据已经回来了

--通过时间进行闪回数据库

22:47:52 SQL> flashback database to timestamp to_timestamp( '2014-11-30 22:28:50','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

22:49:53 SQL> alter database open;

alter database open

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

22:50:00 SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 0

Next log sequence to archive 1

Current log sequence 1

22:50:06 SQL> alter database open noresetlogs;

alter database open noresetlogs

ERROR at line 1:

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

如果使用noresetlogs来打开数据库，那么会提示需要加上用备份的控制文件选项，重新闪回数据库，用resetlogs打开数据库

22:50:13 SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

22:50:35 SQL> startup mount

ORACLE instance started.

Total System Global Area 524288000 bytes

Fixed Size 1220384 bytes

Variable Size 318767328 bytes

Database Buffers 201326592 bytes

Redo Buffers 2973696 bytes

Database mounted.

22:50:47 SQL> flashback database to timestamp to_timestamp( '2014-11-30 22:28:50','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

22:50:57 SQL> alter database open resetlogs;

Database altered.

22:51:16 SQL> select * from zlm.tbl;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980-12-17 3000 20

7499 ALLEN SALESMAN 7698 1981-02-20 3000 300 30

7521 WARD SALESMAN 7698 1981-02-22 3000 500 30

7566 JONES MANAGER 7839 1981-04-02 3000 20

7654 MARTIN SALESMAN 7698 1981-09-28 3000 1400 30

7698 BLAKE MANAGER 7839 1981-05-01 3000 30

7782 CLARK MANAGER 7839 1981-06-09 3000 10

7788 SCOTT ANALYST 7566 1987-04-19 3000 20

7839 KING PRESIDENT 1981-11-17 3000 10

7844 TURNER SALESMAN 7698 1981-09-08 3000 0 30

7876 ADAMS CLERK 7788 1987-05-23 3000 20

7900 JAMES CLERK 7698 1981-12-03 3000 30

7902 FORD ANALYST 7566 1981-12-03 3000 20

7934 MILLER CLERK 7782 1982-01-23 3000 10

14 rows selected.

闪回数据库是10g出现的新特性，但是由于该功能非常不实用，通常在生产环境中都是禁用的，虽然看起来可以很方便地通过SCN，TIMESTAMP等是数据库回退到之前某个点得状态。flashback database有很多局限性，比方说闪回的时候，必须先停库启动到mount状态下，而且必须用resetlogs来open数据库，另外，还会占用大量的db_recovery_file_dest空间，通常默认的就是用的FRA空间，同时，也比较消耗性能。另外，在以下几种情况下，即使开启了flashback database，也无法使用：

1. 控制文件重新建立，或者使用老的控制文件

2. 表空间被 drop

3. 数据库文件被回缩

4. 数据库 resetlogs 以后