现在的位置: 首页 > 数据库 > 正文

Oracle数据误删除或修改并commit后恢复方法

2016年03月11日 数据库 ⁄ 共 6456字 暂无评论 ⁄ 阅读 909 次
文章目录

做数据库难免会遇到SQL语句或表名、字段名写错的时候,在DELETE或UPDATE语句执行后顺手commit了一下,然后SELECT校验数据,顿时一身冷汗,尼玛字段名写错了!别急,说不定我们可以神不知鬼不觉的恢复数据,伟大的flashback,现身吧!

先来以最易操作的方法解决最常见的delete或update后commit的问题,然后我们深入探讨一下其原理,以及更方便的方法。

例如,我们在使用test表,然后不小心delete或update了这张表,然后没有校验而是直接手贱commit了,结果校验时发现数据搞错了。在表不是特别大的情况下,我们可以使用Flashback Query闪回查询修改前的数据,然后通过insert into select或create table as select或update select。

Flashback Query有两种,一种是基于时间(as of timestamp),一种是基于SCN(as of scn)。前者基于时间点,可以查询到指定时刻某表的数据记录;后者基于SCN,可以查询到某个SCN记录点的某表数据记录。

--创建测试表test及测试数据

create table test (t_id number(4),t_name varchar2(20));

insert into TEST (T_ID, T_NAME) values (1, '张三');

insert into TEST (T_ID, T_NAME) values (2, '李四');

insert into TEST (T_ID, T_NAME) values (3, '王五');

insert into TEST (T_ID, T_NAME) values (4, '赵六');

--删除t_id为3的数据并commit

delete from test where t_id =3;

commit;

过了一会王五找到你说不能登录账户了,你一查数据库,去球,本来是赵六离职了,结果你手一抖把王五给删掉了。假设数据有很多字段,怎么找回被误删的记录又不被老板发现这事呢?假设你干这怂事是15分钟左右之前,只要在查询后加as of timestamp子句就可以查到test表15分钟之前数据是什么样了,例如:

--使用Flashback Query基于时间查询表数据

select * from test as of timestamp sysdate – 15/1440;

结果你会完美发现数据跟你没误删时一模一样,窃喜吧,然后你用语句insert into test (select * from test as of timestamp sysdate – 15/1440 where t_id = 3); commit;完美恢复了数据,给王五一个高大上的解释,让他糊涂并膜拜去吧。至于sysdate – 15/1440是什么意思,sysdata是当前时间,一天有60*24=1440分钟,15分钟前就是-15/1440了。同理,如果你是想指定某个时间,也可以把sysdate – 15/1440换成一个TO_DATE()指定的时间。

使用基于时间的Flashback Query较方便,但在处理有主外键约束的表时可能会由于时间点不统一而失败,但如果使用基于SCN的Flashback Query就不会出现这个问题了。

常见的查询当前SCN及某指定时刻SCN方法如下:

--查询当前SCN的两种方法

select dbms_flashback.get_system_change_number from dual;

select current_scn from v$database;

--查询某指定时刻SCN方法

select max(scn) from sys.smon_scn_time where time_dp < to_date(时间点,时间格式);

然而,若你权限不足你会发现你什么也查不到!别急,让我郑重而装B的向你介绍两个Oracle函数SCN_TO_TIMESTAMP()和TIMESTAMP_TO_SCN(),使用方法如下:

--根据时间和SCN互查

select timestamp_to_scn(sysdate) from dual;

select scn_to_timestamp(9999322) from dual;

select timestamp_to_scn(to_date('20160310 17:38:00','yyyymmdd hh24:mi:ss')) from dual;

--使用Flashback Query基于SCN查询表数据

select * from test as of scn 9999322;

好的,到此基本你已经可以通过最容易操作的方式恢复误删或误改且commit后的数据了,下面我们一起深入来了解一下Flashback的原理及使用方式。

Flashback开启

使用flashback闪回的前提是数据库处于归档模式且闪回打开,若数据库并未处于归档模式则需要先关闭数据库,启动到mount状态然后开启归档和闪回。

开启归档模式(在sql*plus中,plsql中不可以):

1)使用DBA账户登录sql*plus

sqlplus sys/sys@orcl as dba;

2)关闭数据库

SQL>shutdown immediate;

TIPS:关闭数据库时最好使用shutdown immediate(阻止任何新连接,阻止新事务回退旧事务)或abort(强制结束所有进程且不回退事务),如果使用了shutdown normal(不断开现有用户连接且阻止包括管理在内的新连接)或transactional(阻止任何用户建立新连接,等待当前所有连接用户未提交的事务提交完毕),若此时仍有用户在使用数据库,你就会发现数据库一直关不了。关闭sqlplus,重新连接执行shutdown时会提示错误“ORA-01090: shutdown in progress - connection is not permitted”(正在关机,不允许连接),再次执行shutdown又会提示“ORA-01031: insufficient privileges”。此时,我们只需要以DBA用户再次登录conn sys/sys as sysdba,然后执行shutdown abort就可以关闭Oracle实例了。

3)启动到mount状态

SQL>startup mount;

4)查看当前是否为归档模式,若非归档模式则更改

SQL>archive log lise;

--改为非归档模式为noarchivelog,若数据库开启flashback则更改为noarchivelog之前要先关闭flashback

SQL>alter database archivelog;

5)查看数据库是否开启闪回,若为NO则开启闪回

--闪回开启为YES,关闭为NO,注意是NO不要看成ON然后以为已开启

SQL>select flashback_on from V$database;

--关闭闪回为flashback off

SQL>alter database flashback on;

6)打开数据库

SQL>alter database open;

7)flashback的其他设置

闪回恢复区主要通过3个初始化参数来设置和管理:

db_recovery_file_dest:指定闪回恢复区的位置

db_recovery_file_dest_size:指定闪回恢复区的可用空间大小

db_flashback_retention_target:指定数据库可以回退的时间

查看闪回周期:

SQL>show parameter db_flashback;

更改闪回周期:

SQL>alter database set db_flashback_retention_target= 2880 scope=spfile;

默认闪回周期是1440分钟,即一天;scope设置作用域,有4个参数,spfile仅更改spfile文件,不更改内存,也就是不立即生效而是在数据库重启后生效;memory仅更改内存,不改spfile,即重启后失效;both内存和spfile都修改,不指定参数默认为both。

查看闪回设置:

--位置和大小信息都有了

SQL>show parameter db_recovery_file

设置闪回区大小:

SQL>alter system set db_recovery_file_dest_size=6g scope=both;

设置闪回区位置:

SQL>alter system set db_recovery_file_dest='/orcl_work/flashback_area' scope=both;

Flashback使用

Flashback通常有Flashback Query、Flashback Table、Flashback Drop和Flashback Database。

Flashback Query有基于timestamp和SCN两种方式,具体使用等在上文已有详解,此处不再叙述。

Flashback Table使用UNDO tablespace的内容来实现对数据的回退,需要注意的是SYS用户不支持闪回,而且表必须允许row movement(行迁移)。

--查看test表是否允许行迁移

SQL>select row_movement from user_tables where table_name = ‘test’;

--启用或禁止行迁移

SQL>alter table test enable row movement;

SQL>alter table test disable row movement;

--基于时间和SCN闪回表,还可同时闪回多表

SQL>flashback table test to timestamp to_date(‘时间’,’格式’);

SQL>flashback table test to scn SCN号;

SQL>flashback table a,b,c to scn SCN号;

需要注意若对表使用了truncate则不可通过Flashback Query查询或Flashback Table恢复,若想使用Flashback则只能Flashback Database。

Flashback Drop用于恢复用户删除的对象(表、索引、触发器等),该技术信赖于Tablespace Recycle Bin(表空间回收站),需要注意的是Flashback不支持sys用户及system表空间下的对象,也不能从回收站拿到。

表空间回收站是每个表空间都有的一个逻辑区域,对象被删除时并非物理删除而是先转移到了回收站,默认recyclebin是开启的,可以通过语句更改回收站可用性:

SQL>alter system set recyclebin=off;

SQL>alter system set recyclebin=on;

--查看recycle状态

SQL>show parameter recycle

--查看recyclebin中对象

SQL>select original_name,object_name from recyclebin;

--查看recyclebin对象里的内容

SQL>select * from “OBJECT_NAME”;

注意禁用回收站后对象将被直接删除,不会写到recycle中。另外,在删除对象时指定purge参数也可直接删除而不进入recycle中。

表空间的Recycle Bin区域只是一个逻辑区域,而不是从表空间上物理的划出一块区域固定用于回收站,因此Recycle Bin是和普通对象共用表空间的存储区域,或者说是Recycle Bin的对象要和普通对象抢夺存储空间。当发生空间不够时,Oracle会按照先入先出的顺序覆盖Recycle Bin中的对象,也可以手动的删除Recycle Bin占用的空间。

1)Purge tablespace tablespace_name:用于清空表空间的Recycle Bin;

2)Purge tablespace tablespace_name user user_name:清空指定表空间的Recycle Bin中指定用户的对象;

3)Purge recyclebin:删除当前用户的Recycle Bin中的对象;

4)Purge dba_recyclebin:删除所有用户的Recycle Bin中的对象,该命令要sysdba权限;

5)Drop table table_name purge:删除对象并且不放在Recycle Bin中,即永久的删除,不能用Flashback恢复。

Flashback Drop使用:

SQL>flashback table test to before drop;

--若有人新建了名为test的表则需要以重命名的方式恢复

SQL>flashback table test to before drop raname to test1;

--若recycle中有两个同名的对象则需要指定object_name来恢复

SQL>flashback table “object_name” to before drop;

另外,在flashback表时,若存在主外键关系,该外键约束并不会恢复。

Flashback Database类似于RMAN的不完全恢复,但比RMAN更快速高效。它把整个数据库回退至过去某个时点,该功能依赖于Flashback Log。然而,若重建了控制文件则不能使得Flashback Database;而且Flashback仅能回退到最早的SCN,即Flashback Log中记录的最早SCN。

通过V$flashback_database_log视图可以查看能回退的最早时间,该视图字段说明如下:oldest_flashback_time/oldest_flashback_time字段记录了该时间,retention_target为闪回周期,flashback_size为当前使用的flash recovery area空间大小,estimated_flashback_size为需要空间大小估算值。

Flashback Database整个架构包括一个Recover Writer(RVWR)后台进程,Flashback Database Log日志和Flash Recovery Area。一旦数据库启用了Flashback Database,则RVWR进程会启动,该进程会向Flash Recovery Area中写入Flashback Database Log,这些日志包括的是数据块的“前镜像(before image)”,这也是Flashback Database技术不完全恢复块的原因。

Flashback Database使用时要关闭数据库重启到mount状态,可以基于时间或SCN进行闪回,如:

SQL>flashback database to timestamp to_date(‘时间’,’格式’);

SQL>flashback database to scn SCN号;

在执行完flashback database 命令之后,oracle 提供了两种方式修复数据库:

1)直接alter database open resetlogs 打开数据库,当然,指定scn 或者timestamp 时间点之后产生的数据统统丢失。

resetlogs打开数据库后重置重做日志,即将重做日志的sequence置零,不完全恢复后,原来的online redo log里面包含的是未做恢复前的数据,而这些数据对于恢复后的数据库不再有效,所以数据库会要求在Open之前先对online redo log的sequence置零。

2)先执行alter database open read only 命令以read-only 模式打开数据库,然后立刻通过逻辑导出的方式将误操作涉及表的数据导出,再执行recover database 命令以重新应用数据库产生的redo,将数据库修复到flashback database 操作前的状态,然后再通过逻辑导入的方式,将之前误操作的表重新导入,这样的话对现有数据的影响最小,不会有数据丢失。

» 声明:本站文章源于个人经验总结或书籍、互联网转载,内容仅用于个人学习,请勿转载,否则后果自负!

给我留言

留言无头像?