恢复误删除的表数据

好久好久没有写过Oracle相关的文章了好像。作为一个DBA着实有些惭愧,人eygle都出书了。 😀

上个周六晚上借着首届杰出数据库工程师评选活动之机,吃了eygle一顿,然后去了eygle新家,说是小坐一会儿,却被大雨挡在了屋子里面,结果后来开始看Mr. Bean,一帮人前仰后合,饶有兴味。准备回家的时候,当晚最大的暴雨出现了,幸好桔子开着一辆…开着一辆…开着一辆啥来着?反正4个轮子的车,先送大师和汪海回酒店,有幸见到了第一次那么清晰的一个区域瓢泼大雨,过了一座立交桥,地面就干的没事儿人一样,诡异。

好吧,我承认又多扯了一堆家常,言归正传。

周日上午酣睡中被电话吵醒,说,客户查不到数据了,一个客户化功能的重新移植将原来已经有很多数据的表drop掉然后重新创建了。

这个drop的操作发生在上周五,也就是接到电话的一天多前,一天多的新业务一定是不能丢掉的,所以不允许直接把数据库恢复到上周五。接到电话以后大略想了一下操作的步骤,后来去公司又做了一些调整,最后的恢复过程大体如下。

1。停产品数据库 (因为允许当库,所以down下来比较保险),将原有数据文件所在的文件系统umount,新做一个文件系统,挂载到原来数据文件所在的目录下。现在就有了一个没有数据文件的Oracle环境。

2。先恢复控制文件,DP的GUI界面连接不上(VNC那边有防火墙),以下均是在RMAN中执行的。
set dbid 1296121177; –dbid在下面的控制文件备份名称中就可以看到
run {
allocate channel ‘dev_0’ type ‘sbt_tape’
parms ‘ENV=(some ob2 parameters)’;
restore controlfile from ‘c-1296121177-20060806-00’; –因为最新的备份是在删除了表以后,所以要指定控制文件名以恢复倒数第二次的有效备份,否则直接from autobackup就可以
}

3。再恢复数据文件
alter database mount;
run {
allocate channel ‘dev_0’ type ‘sbt_tape’
parms ‘ENV=(some ob2 parameters)’;
restore database;
}

4。recover数据库到DROP表之前的时间点(因为客户化移植的具体时间系统中有记录,所以这个时间点很好确定)
run{
sql “alter session set nls_date_format=”yyyy-mm-dd hh24:mi:ss””;
set until time ‘2006-08-11 16:40:00’;
allocate channel ‘dev_0’ type ‘sbt_tape’
parms ‘ENV=(some ob2 parameters)’;
recover database;
}

5。将被DROP掉的那些表中数据EXP出来,记录下当时相关的最大Sequence序列

6。停掉这个恢复的库,再把刚才产品库的文件系统重新mount回来,打开正式库,启动应用

7。将exp出来的数据重新imp进入产品库(当然其中还有牵涉到Sequnce被重置以后,表中的ID也相当于重新开始了,直接imp必然发生数据冲突的问题,但是这些都是后续修改数据的操作,不多说了)

总结:本方法说大些,就是所谓的point-in-time recovery (PITR),通过将数据恢复到另外一个数据库来达到不完全恢复但是不丢失产品库新数据的目的。

探讨实体化视图的刷新机制

今天给客户搭建历史查询服务器,用oracle8i的snapshot实现,也就是9i的实体化视图。

顺手做了一下trace,看了一下刷新时候oracle后台是怎么工作的。

前期准备,使用DBMS_SUPPORT包,这个包默认是没有安装的,需要运行下面的命令来手动安装。

SQL>conn / as sysdba
SQL>@?\rdbms\admin\dbmssupp.sql
SQL>GRANT execute ON dbms_support TO kamus;
SQL>CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;

先看快速刷新,测试表是T1,创建了快照日志,用于刷新的视图是MV_T1,用户是KAMUS

执行trace:

SQL>conn kamus
SQL>exec dbms_support.start_trace(waits=>TRUE,binds=>TRUE);
SQL>exec dbms_mview.refresh(list => ‘MV_T1’);
SQL>exec dbms_support.stop_trace;

然后tkprof生成trace结果的报表,下面只是节选了其中一部分。

执行一次dbms_mview.refresh,Oracle后台会执行13个 user SQL和92个 internal SQL,实在是一个繁杂的工作。

1。开始刷新

BEGIN dbms_mview.refresh(list => ‘MV_T1’); END;

2。检查SNAP$表,确认当前用户是否有需要刷新的视图

3。在DBMS_LOCK_ALLOCATED数据字典中更新记录,设置过期时间

UPDATE DBMS_LOCK_ALLOCATED SET EXPIRATION = SYSDATE + (:B1 /86400)
WHERE
ROWID = :B2

4。检查可能会用到的dblink和一些高级队列的数据字典

5。检查表的相关约束

6。检查几个初始化参数的值,包括_enable_refresh_schedule,_delay_index_maintain,compatible

7。将MLOG中所有没有标志为定时刷新的记录更新为立刻刷新

update “KAMUS”.”MLOG$_T1″ set snaptime= :1
where
snaptime
> to_date(‘2100-01-01:00:00:00′,’YYYY-MM-DD:HH24:MI:SS’)

8。重新编译MV_T1实体化视图

ALTER SUMMARY “KAMUS”.”MV_T1″ COMPILE

这一步比较可疑,SQL中是没有alter summary找个命令的,如果是编译的话,那么就可能锁定对象,就有可能产生library cache lock

9。检查要执行的SQL文,这一步比较有趣

SELECT operation#, cols, sql_txt, tabnum, fcmaskvec, ejmaskvec, setnum
FROM
sys.snap_refop$ WHERE ((operation# >= 0 AND operation# <= 6) OR operation# IN (10, 12, 13)) AND sowner = :1 AND vname = :2 AND instsite = :3 ORDER BY tabnum, setnum, operation#

对于一个MV刷新将会使用到SQL全部存在这张表中。
如果是fast刷新,那么对于查询mlog表,查询基表的数据,insert、update、delete实体化视图都分别有一句SQL。
其中operation#字段值的常见含义如下:

0:查询mlog表

1:对于实体化视图的delete操作

2:查询基表的最新数据

3:对于实体化视图的update操作

4:对于实体化视图的insert操作

如果是complete刷新,那么只有一条记录,是基于基表的全表insert操作,operation#是7。
此处的执行计划显示是对于snap_refop$的全表扫描,如果系统中存在大量需要refresh的实体化视图,无疑是影响性能的。

10。取得需要更新的记录主键

SELECT DISTINCT LOG.”IDATE”
FROM
(SELECT MLOG
.”IDATE” FROM “KAMUS”.”MLOG_T1″ MLOG WHERE “SNAPTIME” > :1
AND (“DMLTYPE
” != ‘I’)) LOGWHERE (LOG.”IDATE”) NOT IN (SELECT
MAS_TAB.”IDATE” FROM “T1” “MAS_TAB” WHERE LOG.”IDATE” = MAS_TAB.”IDATE”)
SELECT CURRENT.”IDATE”,CURRENT.”C”
FROM
(SELECT “T1″.”IDATE” “IDATE”,”T1″.”C” “C” FROM “T1” “T1”) CURRENT, (SELECT
DISTINCT MLOG
.”IDATE” FROM “KAMUS”.”MLOG_T1″ MLOG WHERE “SNAPTIME” >
:1 AND (“DMLTYPE
” != ‘D’)) LOGWHERE CURRENT.”IDATE” = LOG$.”IDATE”

这一步操作表示,mlog中只存储修改操作涉及到的记录主键,其它的字段值仍然会到基表中去作查询。
此处的执行计划显示对于mlog表再一次作了全表扫描。

12。用取得的最新值更新实体化视图

UPDATE “KAMUS”.”MV_T1″ SET “IDATE” = :1,”C” = :2
WHERE
“IDATE” = :1

这一步仍然比较奇怪,因为我的测试中只作了insert,并没有update的操作,莫非oracle在刷新时,并不管是否存在update的操作,都会例行作一次视图数据的更新?不过此处更新会使用实体化视图中的主键,速度应该时很快的。

13。将取得的最新值插入到实体化视图中

INSERT INTO “KAMUS”.”MV_T1″ (“IDATE”,”C”)
VALUES
(:1,:2)

这步才到了真正要实现的目的上,呵呵。

14。更新一批数据字典,表明刷新已经完成

15。删除mlog表中已经刷新过的记录

delete from “KAMUS”.”MLOG_T1″
where
snaptime
$ <= :1

这一步操作是比较耗费资源的,使用delete,产生redo和undo,无法降低mlog表的HWM标志,同时又是一次全表扫描,如果经常有大量更新发生,最好能定时作mlog表的truncate动作,否则这一步操作可能会越来越慢。

至此,一次实体化视图的快速刷新算是完全结束了。

我们继续看一下完全刷新的后台机制。

1-8步跟快速刷新基本相同。

9。检查要执行的SQL文

SELECT operation#, cols, sql_txt
FROM
sys.snap_refop$ WHERE operation# = 7 AND sowner = :1 AND vname = :2 AND
instsite = :3

可以看到直接去找operation# = 7的SQL了,这就是完全刷新需要使用的SQL。

10。检查完全刷新涉及到的约束,索引,触发器

11。删除实体化视图中的原有数据

delete from “KAMUS”.”MV_T”

这一步让我很诧异,记得文档中说应该是truncate操作,但是此处显示的是delete?这样的话,完全刷新的代价实在是很大了。

12。插入基表中所有数据

INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO “KAMUS”.”MV_T”(“X”) SELECT “T”.”X”
FROM “T” “T”

这里使用到的提示/*+ BYPASS_RECURSIVE_CHECK */,是不是在实际应用中可以提高INSERT的效率呢?

13。更新一批数据字典,表明刷新已经完成

14。如果在基表上创建了刷新日志mlog表,那么Oracle不管这次刷新是不是完全刷新,都会去作一次删除mlog表中数据的操作。如果没有创建过mlog,那么这一步将被省略。所以如果决定使用完全刷新,那么就不要在基表上创建刷新日志了,省得无谓的资源消耗。

最简单的创建复制环境中只读实体化视图站点的方法

目的:创建一个实体化视图站点,定时刷新,获取主站点中指定表的变化,这个实体化站点可以作为查询服务器使用

步骤:

1。主站点上创建实体化视图日志表

CREATE MATERIALIZED VIEW LOG ON kamus.account2004;

2。实体化视图站点上创建公用数据库链接

conn system/password

CREATE PUBLIC DATABASE LINK orcl using ‘ORCL’;

3。实体化视图站点上创建刷新组,本例中3分钟刷新一次

conn system/password

BEGIN
DBMS_REFRESH.MAKE (
name => ‘kamus.test_repg’,
list => ”,
next_date => SYSDATE,
interval => ‘SYSDATE + 3/(24*60)’,
implicit_destroy => FALSE,
rollback_seg => ”,
push_deferred_rpc => TRUE,
refresh_after_errors => FALSE);
END;
/
COMMIT;
注意:此处必须commit,否则创建的刷新组并没有真正生效。

4。实体化视图站点上创建用户私有数据库链接

conn kamus/password

CREATE DATABASE LINK orcl CONNECT TO kamus IDENTIFIED BY password;

5。实体化视图站点上创建实体化视图

conn kamus/password

CREATE MATERIALIZED VIEW KAMUS.ACCOUNT2004 REFRESH FAST WITH PRIMARY KEY AS SELECT * FROM KAMUS.ACCOUNT2004@orcl;

6。实体化视图站点上将创建的视图加入刷新组

conn kamus/password

exec DBMS_REFRESH.ADD(name => ‘kamus.TEST_REPG’, list => ‘kamus.ACCOUNT2004’, lax => TRUE);

7。测试,在主站点中更新ACCOUNT2004表,过3分钟检查实体化试图站点中的视图,发现更新已经复制成功。

以上为建立只读实体化视图站点的最简单方法:

不需要创建其它的任何用户,比如复制环境中需要的传播者,刷新者,接收者等等

不需要创建任何复制组

不需要生成任何复制对象,不需要生成任何对象的复制支持