一次truncate table 后的数据恢复记录

最新推荐文章于 2024-06-17 22:25:53 发布
最新推荐文章于 2024-06-17 22:25:53 发布
阅读量1.4k 收藏
点赞数
文章标签： 数据库
原文链接：https://yq.aliyun.com/articles/432264
版权
这次的数据恢复操作我我使用第一种方式，以下是操作步骤：
1、拷贝数据文件和2节点的归档日志文件。由于故障库的备份是nfs挂载到备份机上的，省去了拷贝数据文件的时间，只需要把2节点下的archive日志文件拷贝到测试库上就行。
2、创建新库的pfile参数文件，拷贝故障库的spfile文件，修改参数并删除有关rac的不需要参数信息。
3、创建新库的密码文件.
     orapwd file=/u02/app/oracle/product/11.2.0/db1/dbs/orapwklir passord=oracle entries=10 force=y
4、创建新库的dump目录。
5、启动新库到nomount下。
[oracle@kms2 dbs]$ export ORACLE_SID=klir
[oracle@kms2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 18 09:56:35 2010
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u02/pfile_klir.ora'
6、从备份集中恢复控制文件,并启动到mount状态下。
RMAN>   restore controlfile from /orabk/ctl_c-949039848-20101116-00.ctl;
RMAN>  alter database mount;
 
7、恢复表空间。因为只是一个表空间下的一些表被删除了，朋友建议我只恢复有问题的表空间就可以了，当然system，sysaux,undo这些基本的表空间是要恢复的。

RMAN> restore tablespace system;
Starting restore at 16-NOV-10
Starting implicit crosscheck backup at 16-NOV-10
allocated channel: ORA_DISK_1
Crosschecked 198 objects
Finished implicit crosscheck backup at 16-NOV-10
Starting implicit crosscheck copy at 16-NOV-10
using channel ORA_DISK_1
Finished implicit crosscheck copy at 16-NOV-10
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DG1/system01.dbf
channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13771_1_1_735012733.dbf
channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13771_1_1_735012733.dbf tag=TAG20101114T021212
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:09:15
Finished restore at 16-NOV-10
RMAN> restore tablespace sysaux;
Starting restore at 16-NOV-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to +DG1/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13770_1_1_735012528.dbf
channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13770_1_1_735012528.dbf tag=TAG20101114T021212                               
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:08:50
Finished restore at 16-NOV-10
RMAN> restore tablespace HZDATATBS;
Starting restore at 16-NOV-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to +DG1/hzdata01.dbf
channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13770_1_1_735012528.dbf
channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13770_1_1_735012528.dbf tag=TAG20101114T021212
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:16:35
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00154 to +DG1/hzdata02.dbf
channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13938_1_1_735186686.dbf
channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13938_1_1_735186686.dbf tag=TAG20101116T023123
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 16-NOV-10
RMAN> restore tablespace hzindtbs;
Starting restore at 16-NOV-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00017 to +DG1/hzind02.dbf
channel ORA_DISK_1: restoring datafile 00021 to +DG1/hzind03.dbf
channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13773_1_1_735012734.dbf
channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13773_1_1_735012734.dbf tag=TAG20101114T021212
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:13:45
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00012 to +DG1/hzind01.dbf
channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13771_1_1_735012733.dbf
channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13771_1_1_735012733.dbf tag=TAG20101114T021212
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:14:46
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00153 to +DG1/hzind04.dbf
channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13937_1_1_735186478.dbf
channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13937_1_1_735186478.dbf tag=TAG20101116T023123
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:15
Finished restore at 17-NOV-10

RMAN> restore tablespace undotbs2;
Starting restore at 17-NOV-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to +DG1/undotbs201.dbf
channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13770_1_1_735012528.dbf
channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13770_1_1_735012528.dbf tag=TAG20101114T021212
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:46
Finished restore at 17-NOV-10
RMAN> restore tablespace undotbs1;
Starting restore at 17-NOV-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to +DG1/undotbs101.dbf
channel ORA_DISK_1: reading from backup piece /orabk/db_KLIR_13773_1_1_735012734.dbf
channel ORA_DISK_1: piece handle=/orabk/db_KLIR_13773_1_1_735012734.dbf tag=TAG20101114T021212
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:11:05
Finished restore at 17-NOV-10
 
8.Offline 其它不用的表空间。
SQL> alter database datafile 5,7 offline drop;
Database altered.
SQL> alter database datafile 13,14,15,16 offline drop;
Database altered.
 
9、不完全恢复数据库。

SQL> recover database until time '2010-11-16 16:25:00'   using backup  controlfile;
ORA-00279: change 2602636589 generated at 11/14/2010 13:16:46 needed for thread
1
ORA-00289: suggestion : +ARCHIVE
ORA-00280: change 2602636589 for thread 1 is in sequence #5709

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
thread_1_seq_5709.528.735293603
ORA-00308: cannot open archived log 'thread_1_seq_5709.528.735293603'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/orabk/aaa/thread_1_seq_5709.528.735293603
ORA-00279: change 2602636589 generated at 11/14/2010 06:43:25 needed for thread
2
ORA-00289: suggestion : +ARCHIVE
ORA-00280: change 2602636589 for thread 2 is in sequence #6914
 
ORA-00289: suggestion : +ARCHIVE
ORA-00280: change 2609694870 for thread 1 is in sequence #5824
ORA-00278: log file '/orabk/aaa/arch11/1_5823_719402218.dbf' no longer needed
for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/orabk/aaa/arch11/1_5824_719402218.dbf
ORA-00279: change 2609942602 generated at 11/16/2010 16:07:30 needed for thread
2
ORA-00289: suggestion : +ARCHIVE
ORA-00280: change 2609942602 for thread 2 is in sequence #6990
ORA-00278: log file '/orabk/aaa/arch22/2_6989_719402218.dbf' no longer needed
for this recovery
........................省略中间部分
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/orabk/aaa/arch22/2_6990_719402218.dbf
ORA-00279: change 2609955345 generated at 11/16/2010 16:08:25 needed for thread
1
ORA-00289: suggestion : +ARCHIVE
ORA-00280: change 2609955345 for thread 1 is in sequence #5825
ORA-00278: log file '/orabk/aaa/arch11/1_5824_719402218.dbf' no longer needed
for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/ orabk/aaa/arch11/1_5825_719402218.dbf
Log applied.
Media recovery complete.
 
10、打开数据库。
SQL> alter database open resetlogs;
经过几分钟的等待后，数据成功打开，登录数据库查询需要的文件都已经恢复出来了。谢天谢地！
 
 
这次数据恢复操作，用了近1天的时间，当时发生问题时我都快搞懵了，幸好在朋友的帮助下顺利完数据恢复，在这里要非常感谢他们！顺便记录下恢复期间遇到的几个问题。
<1>、有些日志文件在故障库的日志目录里是没有的，需要从备份中还原，不同节点的日志文件恢复时要写上thread=*命令，例如:
 
RMAN>  restore archivelog from logseq 6914 until logseq 6915  thread=2;
Starting restore at 17-NOV-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=182 instance=klir2 device type=DISK
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=6914
channel ORA_DISK_1: reading from backup piece /orabk/arc_KLIR_13864_1_1_735184831.dbf
channel ORA_DISK_1: piece handle=/orabk/arc_KLIR_13864_1_1_735184831.dbf tag=TAG20101116T020016
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=6915
channel ORA_DISK_1: reading from backup piece /orabk/arc_KLIR_13866_1_1_735184956.dbf
channel ORA_DISK_1: piece handle=/orabk/arc_KLIR_13866_1_1_735184956.dbf tag=TAG20101116T020016
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 17-NOV-10
 
否则会提示以下错误，
RMAN>  restore archivelog from logseq 6914 until logseq 6915;
Starting restore at 17-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=182 instance=klir2 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/17/2010 08:15:07
RMAN-20242: specification does not match any archived log in the repository
<2>、故障库恢复出日志文件后，因为在asm磁盘组里，要进入asmcmd里拷贝出日志文件。
ASMCMD> cp +ARCHIVE/klir/archivelog/2010_11_17/thread_1_seq_5705.369.735274973 /orabk/aaa/
 
<3>、恢复命令要写对，刚开始写的几种命令都不正确。
SQL> recover database until time '2010-11-16 16:25:00'
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
 
SQL>  recover database until time to_date('2010-11-16 16:25:00','yyyy-mm-dd HH24:MI:SS');
ORA-00285: TIME not given as a string constant
<4>、对于这种只是丢失部分表的情况，就可以只还原需要的表空间来打开数据库，可以节省大量的恢复时间。
 


本文转自 gjm008 51CTO博客，原文链接：http://blog.51cto.com/gaoshan/426326，如需转载请自行联系原作者
weixin_33834628
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
truncate表后恢复方法总结
coco3600的博客
04-17 1367
truncate表后恢复方法总结 1.1 BLOG文档结构图 1.2 前言部分 1.2....
truncate的恢复
sqlora的专栏
12-04 1061
truncate恢复表，在11.2.0.4文件系统和19c asm上测试通过 参考：http://www.hellodba.com/reader.php?ID=217&lang=CN https://blog.csdn.net/ElanJie/article/details/89316570 代码下载： http://www.hellodba.com/Download/FY_Rec...
TRUNCATE表恢复
最新发布
gaoda00136的博客
06-17 518
不会逐个清除用户数据块上的数据，而仅仅重置数据字典和元数据块上的元数据（如存储段头和扩展段图）。尤其是在没有任何备份的情况下所以恢复起来相当麻烦，虽然在有备份的情况下是可以用。那有没有在不影响数据业务正常运行的情况下去快速恢复表呢，答案是确定的。恢复，但是在生产业务库中，一般是不能轻易停库的，而且为了一张表而关库也会对其它正常的数据产生影响。在幕后做了大量的工作，有时是不会不被公司其他人理解的。的表，这时找到相关有数据的表，把数据插入原表。的数据，需要及时备份其所在的数据文件。表所在的数据文件的目录相同。
truncate误操作恢复方法集合
08-01
truncate误操作恢复方法集合truncate误操作恢复方法集合truncate误操作恢复方法集合
SQL Server中TRUNCATE事务回滚操作方法
09-10
本文介绍在事务中回滚TRUNCATE操作的方法，并演示了操作的结果，是可以对TRUNCATE进行回滚操作的。
Truncate表恢复(无备份情况下)
07-18
Truncate 不会逐个清除用户数据块上的数据，而仅仅重置数据字典和元数据块上的元数据（如存储断头和扩展段图）。 也就是说，此时，基本数据表并未破坏，而是被系统回收，等待被重新分配—因此，要恢复被truncate的数据，需要及时备份所在的数据文件。
oracle中truncate table后的数据恢复
07-13
在Oracle数据库中，`TRUNCATE TABLE`是一个用于删除表中所有数据的命令，它与`DELETE`语句不同，不记录任何删除操作，并且在大多数情况下执行速度更快。然而，一旦执行`TRUNCATE TABLE`，数据通常无法通过常规的...
MySQL中truncate误操作后的数据恢复案例
09-10
在MySQL数据库管理中，误操作是一个常见的问题，尤其是像`TRUNCATE TABLE`这样的命令，它会立即删除表中的所有数据且无法通过`ROLLBACK`撤销。本案例将详细讲解如何在MySQL中，特别是在Percona Server 5.6.16环境下...
ORACLE truncate 数据恢复
01-16
由于`TRUNCATE`不记录单行删除，所以它在速度上比`DELETE`快很多，且不触发任何与删除相关的触发器或回滚段。然而，这也意味着`TRUNCATE`操作一旦执行，数据就无法通过正常的数据库事务回滚来恢复，这对于初学者来说...
delete from 表名与truncate table 表名区别
09-11
另一方面，`TRUNCATE TABLE` 不记录在事务日志中，因为它被视为结构修改而非数据修改。这意味着 `TRUNCATE` 无法被回滚，且不会触发 `DELETE` 触发器。对于需要审计或者依赖触发器的应用来说，这可能是一个重要的...
truncate 表恢复
11-08
为实现`TRUNCATE`操作后的数据恢复，我们使用了一个名为`Fy_Recover_Data`的存储过程包。这个存储过程包利用Oracle的表扫描机制和数据嫁接机制来恢复因`TRUNCATE`操作而丢失的数据。 - **编写语言**: 纯PL/SQL - **...
sqlserver 日志恢复方法(搞定drop和truncate)
12-15
–1 use master backup database logTest to disk=’D:\\Program Files\\Microsoft SQL Server\\MSSQL10.MSSQLSERVER\\MSSQL\\Backup\\logTest.bak’ with format –2 use logTest create table table_1(Id int, test varchar(max)) insert table_1(Id,test) values (1,’3333′); insert table_1(Id,test) values (2,’4333′); in
TRUNCATE TABLE恢复-脚本
Enmotech的博客
12-02 1319
基于本周二发的文章《TRUNCATE恢复-bbed》（详戳），有些朋友或许对块结构和bbed不熟悉，且bbed的方法也较为复杂，那么大家也可以尝试使用本文的方法来修复。结合truncate...
SQL Server数据恢复准备之TRUNCATE TABLE理解
weixin_34239592的博客
07-14 1194
当truncate table发生时，如何进行恢复，相信大部分人都会选择通过还原备份到truncate table前，然后将数据重新导入正式表中。那么在SQL Server中是不是真的只有这种方法呢，当然不是，这也是本文即将介绍的内容，前提条件是SQL Server完整恢复模式(大容量日志模式未做测试，暂不介绍)。首先，了解一下truncate table的一些相关知识官档：...
sql truncate_如何在SQL Delete和SQL Truncate语句后使用数据库备份恢复数据
culuo4781的博客
07-29 791
sql truncate This article explores the recovery of data removed by SQL Delete and SQL Truncate statements using SQL database backups. 本文探讨了使用SQL数据库备份恢复由SQL Delete和SQL Truncate语句删除的数据的方法。 ...
利用闪回恢复truncate表的数据
gumengkai的博客
01-07 6650
因为truncate不是DML语句，是DDL语句，不能使用闪回查询的方式恢复表数据，这里介绍一种通过flashback database的方式恢复数据额方法。 1. 创建测试表 SQL> create table t as select * from all_objects where rownum<11; Table created. SQL> select object_id from
恢复truncate数据---日常小记
MissYouFY的博客
10-12 2427
做了一个实验，恢复被truncate的数据，主要使用hellodba的脚本。很简单，也很实用 操作步骤 下载脚本： http://www.hellodba.com/download/FY_Recover_Data.zip解压完是一个sql脚本文件 在数据库里运行，会创建一个package和package body，要用SYS用户操作，以下操作均为SYS用户 然后使用如下语句执行： sel
【表恢复】无备份情况下，truncate表恢复
comk08263401的博客
11-19 202
1、表是否能成功恢复，取决于被truncate了的表所占用的数据块是不是被新的段(表、索引等)所重用，如果被重用就无法完成这样的恢复了，看个人运气了。2、需用到牛人写的恢复包，FY_Recover_Data。 ...
truncate table数据删除恢复
09-29
因为TRUNCATE TABLE命令是DDL语句，它直接释放表所占用的存储空间，而不是将数据放入回收站或者记录删除的操作。因此，如果没有备份，就无法通过简单的恢复操作来恢复被删除的数据。 但是，如果删除的数据非常重要...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值