oracle 10G 闪回技术_oracle recly-CSDN博客

本文链接：https://blog.csdn.net/radkitty/article/details/2853843

以下是关于在oracle 10g 中相关闪回技术的应用的详细操作例子.

闪回技术包括：

按行版本闪回

闪回表中的数据

闪回删除

闪回事务

闪回数据库

1．要使用闪回查询和闪回表功能，必须配置的参数：

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900（秒）

undo_tablespace string UNDOTBS1

2．要闪回数据库，数据库必须工作在归档模式下，并与以下的参数相关：

NAME TYPE VALUE

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

db_recovery_file_dest string E:/oracle/product/ 10.2 .0 /flash

_recovery_area

db_recovery_file_dest_size big integer 2 G ――（由每天的数据量来决定大小）

启用或禁用数据库的闪回功能：

alter database flashback on/off;

启用或禁用表空间的闪回功能：

alter tablespace users flashback on/off;

闪回日志的生命周期与下面的参数有关：

NAME TYPE VALUE

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

db_flashback_retention_target integer 1440（分）

重要视图：

V$FLASHBACK_DATABASE_LOG,闪回日志视图

V$FLASHBACK_DATABASE_STAT,闪回统计视图

FLASHBACK_TRANSACTION_QUERY –闪回事务

v$flash_recovery_area_usage 闪回空间使用情况视图

V$flashback_Database_LogFILE 闪回日志文件视图

与回收站相关表和视图

dba_recyclebin 、user_recyclebin

支持闪回技术的PL/sql 包 DBMS_FLASHBACK

DBMS_FLASHBACK.ENABLE_AT_TIME –以时间方式启用闪回功能

ENABLE_AT_SYSTEM_CHANGE_NUMBER 以系统scn来启动闪回功能

DISABLE 禁用闪回

GET_SYSTEM_CHANGE_NUMBER 获取当前scn

Timestamp_to_scn –将时间转成scn

SCN_TO_TIMESTAMP 将scn 转成时间戳

闪回查询操作方法：

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

TO_CHAR(SYSDATE,'HH24:MI:SS')

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

16:09:54

SQL> select empno,sal from emp where empno='7788';

EMPNO SAL

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

7788 3000.00

SQL> update emp set sal=5000 where empno='7788';

1 row updated

SQL> set time on;

16:12:39 SQL> commit;

16:13:20 SQL> variable scn_current number;

16:13:32 SQL> execute :scn_current :=dbms_flashback.get_system_change_number;

PL/SQL procedure successfully completed

scn_current

---------

680990

16:14:03 SQL> update emp set sal=5000 where empno='7788';

1 row updated

16:14:39 SQL> conn scott/tiger--隐式提交

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0 .1.0

Connected as scott

16:15:26 SQL> variable scn_current number;

16:16:12 SQL> execute :scn_current :=dbms_flashback.get_system_change_number;

PL/SQL procedure successfully completed

scn_current

---------

681086

16:16:21 SQL> execute dbms_flashback.enable_at_system_change_number(:scn_current);

PL/SQL procedure successfully completed

scn_current

---------

681086

16:17:53 SQL> select empno,sal from emp where empno='7788';

EMPNO SAL

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

7788 5000.00

16:18:00 SQL> execute dbms_flashback.disable;

PL/SQL procedure successfully completed

16:18:42 SQL> execute dbms_flashback.enable_at_system_change_number(680990);

PL/SQL procedure successfully completed

16:19:07 SQL> select empno,sal from emp where empno='7788';

EMPNO SAL

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

7788 4000.00

16:19:16 SQL> execute dbms_flashback.disable;

PL/SQL procedure successfully completed

16:19:59 SQL> execute dbms_flashback.enable_at_time(to_date('2008-08-17 16:09:54','yyyy-mm-dd hh24:mi:ss' ));

PL/SQL procedure successfully completed

16:21:41 SQL> select empno,sal from emp where empno='7788';

EMPNO SAL

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

7788 3000.00

16:21:45 SQL> execute dbms_flashback.disable;

PL/SQL procedure successfully completed

16:22:07 SQL>

表闪回查询：

select * from test as of timestamp (to_timestamp('2008-08-15 12:27:00','yyyy-mm-dd HH24:mi:ss'))

select * from test as of timestamp (systimestamp-interval '5'minute)--离现在5分钟时间的闪回查询。

闪回表：

16:25:57 SQL> flashback table emp to scn 680990;

flashback table emp to scn 680990

ORA-08189: 因为未启用行移动功能, 不能闪回表

16:30:49 SQL> alter table emp enable row movement;

Table altered

16:31:14 SQL> flashback table emp to scn 680990;

Done

16:31:18 SQL>

闪回删除：

手工删除回收站的对象：

purge table table_name;

purge tablespace tablespace_name;

purge tablespace tablespace_name user user_name;--清楚表空间中某用户的对象

purge index index_name;

purge recyclebin;

16:37:06 SQL> select object_name,original_name,droptime,dropscn from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME DROPTIME DROPSCN

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

BIN$EX0U5rNoS0GIkBk/TsrOkQ==$0 LOGMNR_LOG_TIMEZONE 2008-08-16 :16:24:12 673037

BIN$CaYQIUfNTv6d96BtuSbhOg==$0 LOGMNR_LOG_ALL 2008-08-16 :16:24:16 673048

BIN$i3iw 1l 8JQg6S7o4n/szeBQ==$0 LOGMNR_LOG1 2008-08-16 :16:24:20 673056

16:38:34 SQL> flashback table logmnr_log1 to before drop;

Done

16:39:22 SQL> select object_name,original_name,droptime,dropscn from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME DROPTIME DROPSCN

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

BIN$EX0U5rNoS0GIkBk/TsrOkQ==$0 LOGMNR_LOG_TIMEZONE 2008-08-16 :16:24:12 673037

BIN$CaYQIUfNTv6d96BtuSbhOg==$0 LOGMNR_LOG_ALL 2008-08-16 :16:24:16 673048

16:40:08 SQL> select count(*) from logmnr_log1;

COUNT(*)

----------

72634

16:41:16 SQL>

闪回表的同时，从属的索引，触发器等都会被闪回。

闪回时更改表名：

Flashback table old_name to before drop rename to new_name;

Old_name = （user_reclybin）object_name或original_name

如果删除一个表不放入回收站：

drop table logmnr_log1 purge;

闪回版本的查询：

只要在撤销段中现有的数据都可以被用于闪回版本查询。所以最大的可用版本依赖于undo_retention,为了保证在该参数所设置的时间内，不会覆盖撤销段中已经提交的数据，就需要设置撤销表空间设置RETENTION GUARANTEE选项、

16:48:23 SQL> select tablespace_name,retention,contents from dba_tablespaces;

TABLESPACE_NAME RETENTION CONTENTS

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

SYSTEM NOT APPLY PERMANENT

UNDOTBS1 NOGUARANTEE UNDO

SYSAUX NOT APPLY PERMANENT

TEMP NOT APPLY TEMPORARY

USERS NOT APPLY PERMANENT

16:56:10 SQL> alter tablespace undotbs1 retention guarantee;

Tablespace altered

16:56:56 SQL> select tablespace_name,retention,contents from dba_tablespaces;

TABLESPACE_NAME RETENTION CONTENTS

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

SYSTEM NOT APPLY PERMANENT

UNDOTBS1 GUARANTEE UNDO

SYSAUX NOT APPLY PERMANENT

TEMP NOT APPLY TEMPORARY

USERS NOT APPLY PERMANENT

select to_char(versions_starttime, 'yyyy-mm-dd hh24:mi:ss'),

to_char(versions_endtime, 'yyyy-mm-dd hh24:mi:ss'),

versions_xid,

versions_operation,

from tt versions between timestamp minvalue and maxvalue;

或minvalue and maxvalue 为一个时间范围：to_date(….)

基于scn查询的关键字：

select versions_startscn,

versions_endscn,

versions_xid,

versions_operation,

from tt versions between scn minvalue and maxvalue;

闪回事务：

--根据xid查询某个事务的undo_sql ，从而进行事务闪回

select start_scn,

to_char(start_timestamp, 'yyyy-mm-dd hh24:mi:ss') starttime,

undo_sql,

operation

from v$flashback_transaction_query

where xid = '&versions_xid';

闪回数据库：

---闪回数据库功能

SQL>startup mount;

SQL>FLASHBACK DATABASE TO SCN SCN_VALUE;

SQL>FLASHBACK DATABASE TO TIMESTAMP(TIME_VALUE;

SQL>FLASHBACK DATABASE TO TIMESTAMP(TO_DATE('2008-08-19 08:00:00','YYYY-MM-DD HH24:MI:SS'));

---可以先启动到read only 模式下，先验证数据的正确性。

SQL>ALTER DATABASE OPEN READ ONLY;

SQL>SELECT .....

SQL>SHUTDOWN IMMEDIATE

SQL>STARTUP MOUNT;

SQL>ALTER DATABASE OPEN RESETLOGS;

检测和解决锁争用：

--检测锁：

select 'blocker(' || sb.sid || ':' || sb.serial# || '-' || sb.username || ')-' ||

qb.sql_text blockers,

'waiters(' || sw.sid || ':' || sw.serial# || '-' || sw.username || ')-' ||

qw.sql_text waters

from v$lock lb, v$lock lw, v$session sb, v$session sw, v$sql sb, v$sql sw

where lb.sid = sb.sid

and lw.sid = sw.sid

and sb.prev_sql_addr = qb.address

and sw.sql_address = qw.address

and lb.id1 = lw.id1

and sb.lockwait is null

and sw.lockwait is not null

and lb.block = -1;