Oracle数据库数据丢失恢复的几种方法总结

根据oracle数据库的特点和提供的工具,主要方法有以下几种方法:

  1. 利用逻辑备份使用import工具丢失数据的表
  2. 利用物理备份来通过还原数据文件并进行不完全恢复
  3. 利用dbms_logmnr包从redo log文件中恢复
  4. 利用flashback特性恢复数据

前提

为了方便使用方法的介绍,上述恢复方法都将基于以下场景进行:系统管理员在前一天晚上11点用export对数据库做了全库逻辑备份,然后对所有数据文件进行了热备份。第二天上午10点,系统管理员在修改表TFUNDASSET的数据时,由于修改语句的条件写错了,导致一批记录(几千条)的ztm字段被修改成了错误的值,而且已经提交。这个表是资产表,相对而言数据变化不频繁。

一、利用逻辑备份使用import工具恢复丢失的数据

export/import是oracle提供的用于对数据库进行逻辑备份的工具。该工具适用于备份那些数据量不大、业务量不多的数据库系统。因为如果在前一天晚上11点用export做了逻辑备份,那么当今天上午10点数据库意外崩溃时,从备份起到数据库崩溃的这段时间里的数据修改操作(包括DDL和DML)都会丢失。如果丢失数据内的表上的数据是相对比较稳定,也就是说该表上基本没有DML操作,例如标准代码表、分区表里的历史数据,那么采用import来导入该表可以比较完整的恢复数据。如果该表是经常变化的业务表,那么这些丢失的数据只能根据业务情况从纸质记录恢复,或者其他途径恢复。

▲示例如下:这个表是一个资产表。相对来说,今天系统运行中修改的数据较少,丢失的数据量可以承受或者可以从别的途径恢复。那就可以用import来恢复。

方法一:

1、把这个表的数据备份到另一个表:

2、删除该表的记录:

3、执行下面的命令:

这个命令中在关键字tables中指定需要导入的表名字,ignore=y表示忽略表已经存在的错误。

4、导入结束后,检查表中的记录,并用适当的方法恢复当天的修改。

方法二:

1、 把需要恢复的表导入到另一个用户下面:

2、检查数据以后,把原表记录删除:

3、然后从另一用户表中插入回去:

4、 数据量比较大时可以采用如下方法:

二、利用物理备份来通过还原数据文件并进行不完全恢复

如果数据库运行在归档模式下,那么可以通过使用以前的数据文件备份进行还原,然后利用归档日志进行前滚,直到回滚到错误操作的时间点前,然后重置日志文件打开数据库。

可以通过下列方法确认是否是运行在归档模式:

如果是如上所示,那么就是运行在归档模式了。

▲假定在前一天晚上11点做了全库物理备份,那么可以考虑如下恢复:

1、关闭数据库:

由于数据库的不完全恢复必须在一个关闭的数据库上实施,利用一个旧的数据库的备份还原,然后用日志根据需要逐步前滚,而不能还原一个新的备份,再回退到某个时间点。

通知各客户端数据库将关闭,然后发出:

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

2、确定错误操作的时间:

可以根据操作员的估计来确定不完全恢复需要前滚停止的时间,也可以利用LogMiner来分析日志文件(这个工具将在后面介绍),找出错误操作的准确时间。

3、还原数据文件:

先对当前的数据库文件进行备份,然后再用以前的最近一次备份覆盖现有数据文件。注意:不覆盖现有的控制文件。

4、基于时间点恢复,启动数据库到装配状态:

这样数据库就恢复到了2015年10月20日的9点58分零秒。

然后再利用业务资料补充这段时间内的数据。

三、利用dbms_logmnr包从log文件中恢复

这个包是由Oracle提供,与dbms_logmnr_d包配合使用可以方便地分析联机日志文件和归档日志文件,从这些日志文件中提取出所有对数据库的更改操作。

在使用这个包之前,需要先做一些设置和修改:

1、打开initorcl.ora,修改初始化参数utl_file_dir,设置dbms_logmnr_d包将要使用的数据字典文件的放置目录。

然后重启数据库使参数生效。

2、以sys用户连接到数据库执行dbmslmd.sql脚本重建dbms_logmnr_d这个包。

应用Logminer分析重做日志文件的操作主要有以下步骤:

● 使用dbms_logmnr_d里的存储过程build创建一个外部数据字典文件;

● 使用dbms_logmnr里的存储过程add_logfile添加要分析的日志文件;

● 使用dbms_logmnr里的存储过程start_logmnr启动分析;

● 查询与dbms_logmnr相关的几个视图来获取日志文件内容;

● 使用dbms_logmnr里的存储过程end_logmnr结束分析。

▲下面详细讲述使用的过程

1、使用dbms_logmnr_d里的存储过程build创建一个外部数据字典文件:

2、使用dbms_logmnr里的存储过程add_logfile添加要分析的日志文件到待分析文件列表:

如果没有运行在归档模式,那么由于重做日志文件的循环使用可能导致日志文件被覆盖而无法获取到所要寻找的恢复条目。如果运行在归档模式,则可以通过查看$ORACLE_HOME\admin\orcl\bdump目录下的alert_orcl.log里日志文件归档的时间和错误操作的时间来确定加入哪些归档日志文件到待分析的文件列表中去。

注意:执行以上过程时logfilename参数需要写日志文件的全路径,否则会报错。重复以上操作直到把所有需要分析的文件都加到列表中去。这样就可以启动进行分析。

3、使用dbms_logmnr里的存储过程start_logmnr启动分析;

这样就可以通过下面的查询来获取日志文件的内容了。

4、查询与dbms_logmnr相关的几个视图来获取日志文件内容;

这样就可以找出要恢复所需的语句。注意:v$logmnr_contents只对执行dbms_logmnr.start_logmnr的会话有效,如果通过其他会话或者使用dbms_logmnr.end_logmnr终止了分析,都将不能访问v$logmnr_contents的数据。如果要使其他会话也能获取到这些数据,可以通过另外建表来实现,如:

create table undo_sql as select * from v$logmnr_contents。

再对undo_sql进行授权,其他用户就可以访问v$logmnr_contents的数据了。

5、使用dbms_logmnr里的存储过程end_logmnr结束分析。

使用完成以后用下面的命令来结束分析活动:exec dbms_logmnr.end_logmnr;

这样就释放了分配给logminer的资源(内存和数据结构)。

从上面的过程可知,如果是更新的数据量比较大,而日志文件比较小,就可能会导致日志文件的切换。如果没有及时去挖掘日志文件(没有运行在归档模式),那么可能会由于日志文件的循环使用而导致数据不可恢复。如果运行在归档模式,也可能由于需要分析的日志文件比较多而时间较长。

四、利用flashback新特性恢复数据

Oracle9i 开始提供了闪回查询(Flashback Query)功能,对于误删除或者误更新并且已经commit了的情况提供了简便快捷的恢复方法;而在Oracle 提供闪回查询之前,碰到这种情况只能通过备份来进行基于时间点的恢复或者使用logmnr挖掘日志来恢复,无疑这比闪回查询要麻烦而且费时。

使用这个Flashback Query特性的前提条件:

1. 数据库必须处于Automatic Undo Management 状态。

2. 最大可以闪回查询的时间段由UNDO_RETENTION 初始化参数(单位为秒)指定

可以通过ALTER SYSTEM SET UNDO_RETENTION = <seconds>;来动态修改参数值。

▲如何使用Flashback Query来恢复数据呢?

1. 通过SQL

使用SELECT 语句的AS OF 来进行闪回查询,语法如下:

使用AS OF 关键字来对表,视图或者物化视图进行Flashback Query,如果指定了SCN,那么expr 部分必须是一个数字,如果指定了TIMESTAMP,那么expr 必须是一个timestamp类型的值。查询结果将返回在指定的SCN 或者时间点上的数据。

下面我们使用scott 方案来作一个实验。

如果想在update 的子查询部分使用AS OF,那么该查询只能返回一条记录,否则将会报错。

可以通过添加一个临时表作为中转,然后再作更新,如下:

2.通过DBMS_FLASHBACK包来恢复

DBMS_FLASHBACK 包提供了以下几个函数:

ENABLE_AT_TIME:设置当前SESSION 的闪回查询时间

ENABLE_AT_SYSTEM_CHANGE_NUMBER:设置当前SESSION 的闪回查询SCN

GET_SYSTEM_CHANGE_NUMBER:取得当前数据库的SCN

DISABLE:关闭当前SESSION 的闪回查询

当将一个SESSION 设置为闪回查询模式之后,后续的查询都会基于那个时间点或者SCN 的数据库状态,如果SESSION 结束,那么即使没有明确指定DISABLE,闪回查询也会自动失效。

当SESSION 运行在闪回查询状态时,不允许进行任何DML 和DDL 操作。如果要用DML操作来进行数据恢复就必须使用PL/SQL 游标。

▲示例:

通过上面的例子可以看出,只要这个修改的时间不早于sysdate- (UNDO_RETENTION指定的秒数),就可通过这种方式来恢复数据。

对于问题中的批量数据,可以写个过程来完成获取到更改前的数据:

然后再用这个临时表里的数据来更新TFUNDASSET就可以了。

五、总结

比较以上几种恢复数据的方法的使用过程,我们可以看出:

● exp/imp只适合于数据变化不大的表的数据丢失的情况,即使用这种方法处理后也需要从业务办理资料中修正数据,否则导致数据丢失;

● 采用基于时间点的不完全恢复可以恢复丢失的数据,但是需要关关闭数据库,减少系统可用时间,而且也会丢失恢复时间点以后的数据;

● 使用LogMiner可以较好的恢复数据,但是要求数据库尽可能运行在归档模式,否则也可能导致数据丢失。好处是不用关闭系统,能够从日志文件中得到所有的数据。

● 使用Flashback最方便和简洁,可以直接得到修改前的数据,但是需要依赖系统设置,并且需要占用大量的回滚表空间。

因此选择什么样的方法来恢复数据,取决于你的系统环境和具体情况,不能生搬硬套。采用正确的方法才能最大程度的减少数据的丢失。

当然,最好是不需要用到这些恢复的办法。前提是,你必须做好以下的工作:

1、 为不同环境创建不同的数据库用户、不同密码(如果不能用户不同,一定要密码不同);

2、 将owner和应用用户分开,并做适度授权;

3、 在做DML前,先用同样的条件做查询,看根据结果集是否符合预期。

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流。

(0)

相关推荐

  • ORACLE数据库日常维护知识点总结

    首先要说的是,不同版本数据库提供的系统表会有不同,你可以根据数据字典查看该版本数据库所提供的表 like this: select * from dict where table_name like '%SESSION% '; 就可以查出一些表,然后根据这些表就可以获得会话信息. 像这样就是 查询当前正在操作的会话: SELECT SID, SERIAL#, STATUS, USERNAME, SCHEMANAME, OSUSER,TERMINAL, MACHINE, PROGRAM, A.NA

  • oracle数据库sql的优化总结

    一:使用where少使用having; 二:查两张以上表时,把记录少的放在右边: 三:减少对表的访问次数: 四:有where子查询时,子查询放在最前: 五:select语句中尽量避免使用*(执行时会把*依次转换为列名): 六:尽量多的使用commit: 七:Decode可以避免重复扫描相同的记录或重复连接相同的表: 八:通过内部函数也可提高sql效率: 九:连接多个表时,使用别名并把别名前缀于每个字段上: 十:用exists代替in 十一:not exists代替 not in(not in 字

  • 简析Oracle数据库常见问题及解决方案

    Oracle数据库在使用的过程中常常会遇到这样或那样的问题,而这些问题常常又使我们感到很困惑,本文我们总结了Oracle数据库在使用过程中的一些问题,并给出了解决方法,下面我们就开始分析一下这些问题. 一.oracle监听启动后,立即停止. TNS-12545: 因目标主机或对象不存在,连接失败. TNS-12560: TNS: 协议适配器错误. TNS-00515: 因目标主机或对象不存在,连接失败. 32-bit Windows Error: 1004: Unknown error. 不再监

  • DB2数据库切换为oracle数据库经验教训总结(必看篇)

    由于DB2数据库使用的人太少,公司有没有专业的DBA,决定把数据库从DB2数据库切换为oracle数据库,本以为很简单,可当真的切换时,却发现,有很多东西出乎意料. 由于系统底层使用的是ORM映射工具,由于没有使用存储过程,自定义函数,触发器,因此我以为系统改动不大,但发现的问题却不少. 1.我们的主键基本上都采用共的是Sequence,没有采用自动增长作为主键. 但获取Sequence在两种数据库中是不相同的. DB2获取的方法 values next value for eas.seq_Se

  • Oracle数据库数据丢失恢复的几种方法总结

    根据oracle数据库的特点和提供的工具,主要方法有以下几种方法: 利用逻辑备份使用import工具丢失数据的表 利用物理备份来通过还原数据文件并进行不完全恢复 利用dbms_logmnr包从redo log文件中恢复 利用flashback特性恢复数据 前提 为了方便使用方法的介绍,上述恢复方法都将基于以下场景进行:系统管理员在前一天晚上11点用export对数据库做了全库逻辑备份,然后对所有数据文件进行了热备份.第二天上午10点,系统管理员在修改表TFUNDASSET的数据时,由于修改语句的

  • Oracle数据库中ora-12899错误的解决方法

    在使用ORACLE的过程中,会出现各种各样的问题,各种各样的错误,其中ORA-12899就是前段时间我在将数据导入到我本地机器上的时候一直出现的问题.不过还好已经解决了这个问题,现在分享一下,解决方案; 出现ORA-12899,是字符集引起的,中文在UTF-8中占3个字节,ZHS16GBK中占2个字节,而源dmp文件字符集是ZHS16GBK库里倒出来的数据,现在要导入到目标字符集为UTF-8的库里,所以会出现ORA-12899 其实只要修改一下ORACLE 的字符集就可以很好的解决这个问题; 但

  • Oracle数据库TNS常见错误的解决方法汇总

    TNS是Oracle Net的一部分,是专门用来管理和配置Oracle数据库和客户端连接的一个工具,在大多数情况下客户端和数据库要通讯,就必须配置TNS.本文主要讲述了Oracle数据库TNS常见错误的解决方法如下: 1.ORA-12541:TNS:没有监听器 原因:没有启动监听器或者监听器损坏.若是前者,使用命令net start OracleOraHome10gTNSListener(名字可能有出入)即可;如果是后者,则使用"Net Configuration Assistant"

  • Oracle查看表结构的几种方法示例代码

    1,DESCRIBE 命令 使用方法如下: SQL> describe nchar_tst(nchar_tst为表名) 显示的结果如下: 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- NAME NCHAR(6) ADDR NVARCHAR2(16) SAL NUMBER(9,2) 2,DBMS_METADATA.GET_DDL包 使用方法如下: SQL> S

  • Oracle数据库并行查询出错的解决方法

    Oracle的并行查询是使用多个操作系统级别的Server Process来同时完成一个SQL查询,本文讲解Oracle数据库并行查询出错的解决方法如下: 1.错误描述 ORA-12801: 并行查询服务器P007中发出错误信号 ORA-01722:无效数字 12801.00000 -"error signaled in parallel query server %s" *Cause: A parallel query server reached an exception cond

  • lnmp重置mysql数据库root密码的两种方法

    第一种方法:用军哥的一键修改LNMP环境下MYSQL数据库密码脚本 一键脚本肯定是非常方便.具体执行以下命令: wget http://soft.vpser.net/lnmp/ext/reset_mysql_root_password.sh sh reset_mysql_root_password.sh 方便吧! 第二种方法:通过命令修改,具体如下: a.停止MySQL服务 执行:/etc/init.d/mysql stop b.跳过验证启动MySQL /usr/local/mysql/bin/

  • tensorflow2.0保存和恢复模型3种方法

    方法1:只保存模型的权重和偏置 这种方法不会保存整个网络的结构,只是保存模型的权重和偏置,所以在后期恢复模型之前,必须手动创建和之前模型一模一样的模型,以保证权重和偏置的维度和保存之前的相同. tf.keras.model类中的save_weights方法和load_weights方法,参数解释我就直接搬运官网的内容了. save_weights( filepath, overwrite=True, save_format=None ) Arguments: filepath: String,

  • 解决MySQL主从数据库没有同步的两种方法

    目录 解决MySQL主从数据库没有同步的两种方法 一.不同步情况 二.解决方案 1.先进入主库,进行锁表,防止数据写入 2.进行数据备份 3.查看master 状态 4.把mysql备份文件传到从库机器,进行数据恢复 5.停止从库的状态 6.然后到从库执行mysql命令,导入数据备份 7.设置从库同步 8.重新开启从同步 9.查看同步状态 10.回到主库并执行如下命令解除表锁定. 解决MySQL主从数据库没有同步的两种方法 工作的过程中发现Mysql的主从数据库没有同步 一.不同步情况 Mast

  • 配置SQL Server数据库恢复模式(2种方法)

    下面主要介绍配置SQL Server数据库恢复模式的两种方法. 用T-SQL设置恢复模式 你可以使用"ALTER DATABASE"命令加"SET RECOVERY"语句来修改数据库的恢复模式.例如,下面的查询语句把"AdventureWorks"数据库的恢复模式设置为完全恢复模式. ALTER DATABASE AdventureWorks SET RECOVERY FULL ; 你可以查询"sys.databases"的目

  • plsql连接oracle数据库报ora 12154错误解决方法

    plsql连接oracle数据库报ora 12154错误 今天遇到一个问题,使用sqlplus能够连接到远程的数据库,但是使用plsql却连接不上,报错"ORA-12154: TNS: 无法解析指定的连接标识符" 解决方法如下: 1.先检查服务器端的监听服务是否打开,如果没有打开请启动其监听 客户端:tnsping <tns_name> 服务器Linux下: #>lsnrctl status 查看监听状态 #>lsnrctl start 启动监听 2.通过Sql

随机推荐