闪回数据库flashback database

1.使用闪回数据库的前提条件及打开数据库闪回功能

归档

快速恢复区

打开数据库闪回功能

 

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 32

Next log sequence to archive 34

Current log sequence 34

SQL> show parameter recover

 

NAME TYPE VALUE

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

db_recovery_file_dest string /u02

db_recovery_file_dest_size big integer 8000M

recovery_parallelism integer 0

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area 418484224 bytes

Fixed Size 1336932 bytes

Variable Size 335546780 bytes

Database Buffers 75497472 bytes

Redo Buffers 6103040 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

 

 

2.使用闪回

 

模拟故障

SQL> drop user scott cascade;

 

User dropped.

 

SQL> select 'exec dbms_logmnr.add_logfile('''||member||''')' from v$logfile;

 

'EXECDBMS_LOGMNR.ADD_LOGFILE('''||MEMBER||''')'

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

exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/orcl/redo03.log')

exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/orcl/redo02.log')

exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/orcl/redo01.log')

 

SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/orcl/redo03.log')

exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/orcl/redo02.log')

 

PL/SQL procedure successfully completed.

 

SQL>

PL/SQL procedure successfully completed.

 

SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/orcl/redo01.log')

 

PL/SQL procedure successfully completed.

 

SQL>

SQL> exec dbms_logmnr.start_logmnr();

 

PL/SQL procedure successfully completed.

 

 

1 select scn,sql_Redo

2 from v$logmnr_contents

3* where sql_redo like '%drop%'

SQL> /

 

SCN

----------

SQL_REDO

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

1016218

drop table "SCOTT"."T" cascade constraints purge force;

 

1016240

drop table "SCOTT"."SALGRADE" cascade constraints purge force;

 

1016262

drop table "SCOTT"."BONUS" cascade constraints purge force;

 

1016276

drop table "SCOTT"."EMP" cascade constraints purge force;

 

1016315

drop table "SCOTT"."DEPT" cascade constraints purge force;

 

1016365

drop user scott cascade;

 

 

6 rows selected.

 

1 select min(scn)

2 from v$logmnr_contents

3* where sql_redo like '%drop%'

SQL> /

 

MIN(SCN)

----------

1016218

 

 

使用SYS执行闪回

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area 418484224 bytes

Fixed Size 1336932 bytes

Variable Size 335546780 bytes

Database Buffers 75497472 bytes

Redo Buffers 6103040 bytes

Database mounted.

SQL> flashback database to scn 1016217;

 

Flashback complete.

 

SQL> alter database open read only;---------------以后凡是恢复都要使用这条命令看看看

 

Database altered.

 

使用SCOTT检查是否被闪回回来

SQL> conn scott/tiger

Connected.

SQL> select * from tab;

 

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

T TABLE

 

以resetlogs的方式打开数据库（思考为什么要用resetlogs的方式打开）

 

 

3.监视数据库闪回功能

 

闪回保留目标

SQL> show parameter retention

 

NAME TYPE VALUE

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

db_flashback_retention_target integer 1440

undo_retention integer 900

SQL> select 1440/60 from dual;

 

1440/60

----------

24

 

查看闪回日志及闪回相关信息

 

SQL> select estimated_flashback_size,flashback_size from v$flashback_database_log;

 

ESTIMATED_FLASHBACK_SIZE FLASHBACK_SIZE

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

0 16384000

 

SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

 

OLDEST_FLASHBACK_SCN OLDEST_FL

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

1016069 31-MAY-16

 

SQL> select begin_time,end_time,flashback_data,db_data,redo_data,estimated_flashback_size from v$flashback_Database_stat;

 

BEGIN_TIM END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE

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

31-MAY-16 31-MAY-16 811008 0 120832 0

 

 

监视快速恢复区

SQL> select space_limit,space_used,number_of_files from v$recovery_file_dest;

 

SPACE_LIMIT SPACE_USED NUMBER_OF_FILES

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

8388608000 410885632 31

 

SQL> select file_type,PERCENT_SPACE_USED,PERCENT_SPACE_RECLAIMABLE,NUMBER_OF_FILES from v$flash_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 1.18 .13 25

BACKUP PIECE 3.53 .05 4

IMAGE COPY 0 0 0

FLASHBACK LOG .2 0 2

FOREIGN ARCHIVED LOG 0 0 0

 

7 rows selected.

 

4.还原点

还原点就时间的别名

一个还原点，代表着一个时间，或者一个SCN

SQL> create restore point rp1 guarantee flashback database;

 

Restore point created.

 

SQL> drop restore point rp1;

 

Restore point dropped.

 

使用还原点闪回

SQL> flashback database to restore point rp1;

 

 

查看还原点

SQL> select NAME,SCN,TIME from v$restore_point;

 

NAME SCN TIME

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

RP1 1017045 31-MAY-16 03.50.15.000000000 PM