Oracle回收站及flashback drop

2022-12-18 14:59:19

最近在执行健康检查脚本的时候脚本一直卡在了表空间查询这块，瞅了一眼SQL，根据经验小麦苗预估是由于DBA_FREE_SPACE视图的缘故，这个视图若回收站的对象很多的话查询就会非常的慢，接下来单独执行select count(1) from dba_free_space;果然非常的慢，没办法只能先将回收站的数据清理了再来查询表空间了，而回收站大约有200W的数据量，执行purge DBA_RECYCLEBIN非常慢，那就只能采用job的并行技术了，这个在本文最后给出了脚本，这个脚本比较通用，小麦苗以前做开发的时候经常性的用这个脚本，希望各位朋友能掌握。

说到回收站就涉及到闪回，而闪回分很多类，我们今天着重看看flashback drop和回收站。

1 相关知识点扫盲(摘自网络+个人总结)

1.1 闪回

当授权用户犯错，您需要使用工具来更正这些错误。Oracle 数据库 10g 提供了一系列人为错误更正技术，称为闪回。闪回从根本上改变了数据恢复。过去，数据库在几分钟内就可能损坏，但需要几小时才能恢复。利用闪回技术，更正错误的时间与错误发生时间几乎相同。而且它非常易用，使用一条短命令便可恢复整个数据库，而不必执行复杂的程序。闪回技术提供了一个 SQL 界面，能够快速分析和修复人为错误。闪回技术为本地数据损坏提供了细粒度外部分析和修复，如当错误删除客户订单时。闪回技术还支持修复更多广泛的损坏，同时快速避免长时间停机，如当本月的所有客户订单都被删除时。闪回技术是 Oracle 数据库独有的特性，支持各级恢复，包括行、事务、表、表空间和数据库范围。闪回技术是数据库恢复技术历史上一次重大的进步，从根本上改变了数据恢复。

Oracle 9i实现了基于回滚段的闪回查询（Flashback Query）技术，即从回滚段中读取一定时间内对表进行操作的数据，恢复错误的DML操作。

在Oracle 10g中，除提高了闪回查询功能，实现了闪回版本查询、闪回事务查询外，还实现了闪回表、闪回删除和闪回数据库的功能。

采用闪回技术，可以针对行级和事务级发生过变化的数据进行恢复，减少了数据恢复的时间，而且操作简单，通过SQL语句就可以实现数据的恢复，大大提高了数据库恢复的效率。

1.2 闪回技术分类

1. 闪回查询（Flashback Query）：查询过去某个时间点或某个SCN值时表中的数据信息；

2. 闪回版本查询（Flashback Version Query）：查询过去某个时间段或某个SCN段内表中数据的变化情况；

3. 闪回事务查询（Flashback Transaction Query）：查看某个事务或所有事务在过去一段时间对数据进行的修改；

4. 闪回表（Flashback Table）：将表恢复到过去的某个时间点或某个SCN值时的状态；

5. 闪回删除（Flashback Drop）：将已经删除的表及其关联对象恢复到删除前的状态；

6. 闪回数据库（Flashback Database）：将数据库恢复到过去某个时间点或某个SCN值时的状态。

注意

①　闪回查询、闪回版本查询、闪回事务查询以及闪回表主要是基于撤销表空间中的回滚信息实现的；

②　闪回删除是基于Oracle 10g中的回收站（Recycle Bin）特性实现的；

③　闪回数据库是基于闪回恢复区（Flash Recovery Area）中的闪回日志来实现的；

④　为了使用数据库的闪回技术，必须启用撤销表空间自动管理回滚信息。

⑤　如果要使用闪回删除技术和闪回数据库技术，还需要启用回收站、闪回恢复区。

1.3 闪回删除（Flashback Drop）

Oracle10g之前，一旦删除了一个表，那么该表就会从数据字典里面删除。要恢复该表，需要进行不完全恢复。Oracle10g以后，当我们删除表时，默认Oracle只是在数据库字典里面对被删的表的进行了重命名，并没有真正的把表删除。 Flashback Drop 是从Oracle 10g 开始出现的，用于恢复用户误删除的对象(包括表，索引等)，这个技术依赖于Tablespace Recycle Bin(表空间回收站)，这个功能和windows的回收站非常类似。

回收站：用来维护表被删除前的名字与删除后系统生成的名字之间的对应关系的数据字典，表上的相关对象（索引、触发器等）也会一并进入回收站

被drop掉的表能否闪回来与两个因素相关：

1、该表所在表空间的大小有关，即如果表空间够大，用drop语句删除的表，并不是真正的从数据库中删除，而是把表改成BIN$开头的表，但是如果表空间不够大，在有新数据要存入该表空间的时候，就会覆盖这些BIN$表的物理空间，此时也就没有办法利用闪回恢复该表了

2、删除该表的时候是否用的purge，如果在drop的时候使用了purge，则该表就被从表空间中彻底的被删除了，如果要恢复，必须用以前的备份恢复，可以用TSPITR或12c可以直接从备份集中恢复单张表。

表空间的Recycle Bin 区域只是一个逻辑区域，而不是从表空间上物理的划出一块区域固定用于回收站，因此Recycle Bin是和普通对象共用表空间的存储区域，或者说是Recycle Bin的对象要和普通对象抢夺存储空间。

当发生空间不够时，Oracle会按照先入先出的顺序覆盖Recycle Bin中的对象。

Flashback Drop 需要注意的地方：

1). 只能用于非系统表空间和本地管理的表空间

2). 对象的参考约束不会被恢复，指向该对象的外键约束需要重建。

3). 对象能否恢复成功，取决与对象空间是否被覆盖重用。

4). 当删除表时，信赖于该表的物化视图也会同时删除，但是由于物化视图并不会被放入recycle bin，因此当你执行flashback table to before drop 时，也不能恢复依赖其的物化视图，需要dba 手工介入重新创建。

5). 对于Recycle Bin中的对象，只支持查询.

1.4 闪回指定的表

有时可能一个表被反复的建立和drop，这样在recycle一个origianl name的有多个记录相对，默认将是恢复最后一个，如果要恢复指定的一个可以用他们的OBJECT_name通过指定name的方式。

select * from user_recyclebin t where t.original_name='OLD_T';

select * from "BIN$zltzJRsMB0PgRAAY/i3Kdw==$0";

闪回回收站中指定的表：

flashback table "BIN$zltzJRsMB0PgRAAY/i3Kdw==$0" to before drop;

默认采用的是先进后出的方式，总是恢复最后被删除的表。

1.5 执行闪回操作后索引的处理

表被恢复以后，表上的索引，需要重建，虽然索引可以随着表的闪回而闪回，但是闪回后的索引仍然使用recyclebin的名字，因此我们需要重建索引。

1.6 回收空间

既然被删除的对象没有被物理的释放，那么该物理空间是如何进行回收的呢？Oracle通过两种方式进行回收。

1、自动回收

当表空间出现压力时，Oracle会首先使用表空间里不属于回收站的对象所占用的可用空间，如果这部分空间用完，仍然存在空间压力，则释放回收站里面最老的那些对象所占用的空间。直至释放完毕所有的空间，然后扩展数据文件（前提是数据文件支持自动扩展）

2、手工回收

使用purge命令来释放回收站里的对象所占用的空间

1.7 回收站对象的大小

表空间的占用大小中dba_free_space中不包括回收站中的对象的大小。

SELECT nvl(a.owner, '合计') owner,

round(SUM(a.space *

(SELECT value FROM v$parameter WHERE name = 'db_block_size')) / 1024 / 1024,

2) recyb_size_M,

count(1) recyb_cnt

FROM dba_recyclebin a

GROUP BY ROLLUP(a.owner);

1.8 开启回收站

Property	Description
Parameter type	String
Syntax	RECYCLEBIN = { on \| off }
Default value	on
Modifiable	ALTER SESSION, ALTER SYSTEM ... DEFERRED
Basic	No

RECYCLEBIN is used to control whether the Flashback Drop capability is turned on or off. If the parameter is set to off, then dropped tables do not go into the recycle bin. If this parameter is set to on, then dropped tables go into the recycle bin and can be recovered

alter system set recyclebin = on scope=spfile;

alter session set recyclebin= on;

1.9 回收站相关SQL命令

select * from "BIN$zltzJRsMB0PgRAAY/i3Kdw==$0";

select * from user_recyclebin;

select * from dba_recyclebin;

flashback table "BIN$zltzJRsMB0PgRAAY/i3Kdw==$0" to before drop;

flashback table t to before drop rename to old_t;

show recyclebin

purge table test;//我们purge回收站中表的时候，相对应的索引也会被删除。

purge index “索引名字”;

purge tablespace users;清除回收站里面属于users表空间的对象所占用的空间

purge user_recyclebin：清除回收站里面属于当前用户的所有对象所占用的空间

purge dba_recyclebin：清除回收站里所有对象所占用的空间

drop table xxxx purge;直接删除表，不进入回收站。

1.10 利用job来清空回收站

若回收站内容较多，则用dba_recyclebin清空回收站比较慢，这个时候可以考虑采用job分割的方法来晴空回收站，脚本如下：

SELECT D.owner,COUNT(1) FROM dba_recyclebin D GROUP BY D.owner;

CREATE TABLE XB_recyclebin_LHR NOLOGGING AS

SELECT ROWNUM RN, 'PURGE ' || A.type || ' ' || A.owner || '."' || A.object_name || '"' EXEC_SQL

FROM dba_recyclebin A

where a.type = 'TABLE';

CREATE INDEX IDX_recyclebin_rn on XB_recyclebin_LHR(rn) NOLOGGING PARALLEL;

create table XB_SPLIT_JOB_LHR

(

startrownum NUMBER(18),

endrownum NUMBER(18),

flag NUMBER(1)

);

SELECT * FROM xb_split_job_lhr;

CREATE OR REPLACE PROCEDURE pro_split_job_lhr AUTHID CURRENT_USER IS

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

-- copy on 2012/4/2 23:28:21 by lhr

--function：该存过用来分隔数据来建立job

--需要进行处理的数据量 ,需要处理的表加rn列，值取rownum，rn列加索引

--alter table tmp_dp_idp_lhr add rn number;

/* CREATE INDEX IDX_tmp_dp_idp_lhr_rn on tmp_dp_idp_lhr(rn)

TABLESPACE SDH_INDEX ONLINE NOLOGGING COMPUTE STATISTICS PARALLEL;*/

/* create table XB_SPLIT_JOB_LHR

(

startrownum NUMBER(18),

endrownum NUMBER(18),

flag NUMBER(1)

)*/

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

n NUMBER; --创建的job数

j NUMBER := 0;

n_startrownum NUMBER;

n_endrownum NUMBER;

n_patchnum NUMBER := 20000; -- 每批处理的记录数 ----modify

v_jobname VARCHAR2(200);

v_count NUMBER; --需要处理的表的数据量

BEGIN

SELECT COUNT(1) INTO v_count FROM XB_recyclebin_LHR; ----modify

--需要创建的job个数

n := trunc(v_count / n_patchnum) + 1;

EXECUTE IMMEDIATE 'truncate table xb_split_job_lhr';

WHILE j < n LOOP

--得到rownum

n_startrownum := j * n_patchnum + 1;

IF j = n - 1 THEN

n_endrownum := v_count;

ELSE

n_endrownum := (j + 1) * n_patchnum;

END IF;

INSERT INTO xb_split_job_lhr

(startrownum, endrownum)

VALUES

(n_startrownum, n_endrownum);

COMMIT;

j := j + 1;

END LOOP;

--循环创建job

j := 0;

FOR cur IN (SELECT * FROM xb_split_job_lhr) LOOP

v_jobname := 'JOB_SUBJOB_SPLIT_LHR' || (j + 1);

dbms_scheduler.create_job(job_name => v_jobname,

job_type => 'STORED_PROCEDURE',

job_action => 'PRO_SUB_SPLIT_LHR', --modify

number_of_arguments => 2,

start_date => SYSDATE + 1 / 5760, -- 15秒后启动作业

repeat_interval => NULL,

end_date => NULL,

job_class => 'DEFAULT_JOB_CLASS',

enabled => FALSE,

auto_drop => TRUE,

comments => 'to split job_subjob_Split_lhr');

COMMIT;

dbms_scheduler.set_job_argument_value(job_name => v_jobname,

argument_position => 1,

argument_value => cur.startrownum);

COMMIT;

dbms_scheduler.set_job_argument_value(job_name => v_jobname,

argument_position => 2,

argument_value => cur.endrownum);

COMMIT;

dbms_scheduler.enable(v_jobname);

j := j + 1;

END LOOP;

COMMIT;

-----等待所有的子job执行完

LOOP

SELECT COUNT(1)

INTO v_count

FROM xb_split_job_lhr t

WHERE t.flag IS NULL;

IF v_count = 0 THEN

EXIT;

ELSE

dbms_lock.sleep(10); ---存过休息10秒

END IF;

END LOOP;

EXECUTE IMMEDIATE 'purge dba_recyclebin';

EXCEPTION

WHEN OTHERS THEN

NULL;

END pro_split_job_lhr;

create or replace procedure pro_sub_split_lhr(p_startrownum number,

p_endrownum number) is

begin

for cur in (SELECT A.EXEC_SQL

FROM XB_recyclebin_LHR A ---modify

where A.rn <= p_endrownum

and A.rn >= p_startrownum) loop

begin

EXECUTE IMMEDIATE CUR.EXEC_SQL;

exception

when others then

null;

end;

end loop;

commit;

--更新标志

update xb_split_job_lhr t

set t.flag = 1

where t.startrownum = p_startrownum

and t.endrownum = p_endrownum;

commit;

exception

when others then

null;

end pro_sub_split_lhr;

码农公寓

1 相关知识点扫盲(摘自网络+个人总结)

1.1 闪回

1.2 闪回技术分类

1.3 闪回删除（Flashback Drop）

1.4 闪回指定的表

1.5 执行闪回操作后索引的处理

1.6 回收空间

1.7 回收站对象的大小

1.8 开启回收站

1.9 回收站相关SQL命令

1.10 利用job来清空回收站

相关文章