AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2017-11-27 1197
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
日志服务 SLS，月写入数据量 50GB 1个月
简介：
这次的数据恢复操作我我使用第一种方式，以下是操作步骤：
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，如需转载请自行联系原作者
文章标签：
日志服务
数据库
SQL
关系型数据库
Oracle
相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
技术小阿哥
目录
相关文章
蓝易云
|
13天前
|
数据管理 分布式数据库 数据库
循序渐进丨MogDB 数据库v5.0之闪回DROP/TRUNCATE
MogDB v5.0中的闪回DROP/TRUNCATE功能为数据库管理提供了强大的数据恢复能力。通过撤销日志和版本控制机制，能够高效地恢复误操作导致的数据丢失。这一功能不仅提高了数据管理的灵活性和安全性，还简化了日常维护工作。希望本文能帮助读者深入理解和应用MogDB的闪回功能，提高数据库管理效率。
蓝易云
35 15
qhhf_地道酬勤
|
8月前
|
SQL 关系型数据库 MySQL
MySQL删除表数据、清空表命令（truncate、drop、delete 区别）
MySQL删除表数据、清空表命令（truncate、drop、delete区别） 使用原则总结如下： 当你不需要该表时（删除数据和结构），用drop； 当你仍要保留该表、仅删除所有数据表内容时，用truncate； 当你要删除部分记录、且希望能回滚的话，用delete；
qhhf_地道酬勤
3515 1
jianz123
|
10月前
|
存储 关系型数据库 MySQL
Mysql数据库清空表 truncate和delete
Mysql数据库清空表 truncate和delete
jianz123
174 0
小麦苗
|
SQL Oracle 关系型数据库
truncate表后恢复方法总结
truncate表后恢复方法总结   1.1  BLOG文档结构图   1.2  前言部分   1.
小麦苗
1741 0
lfreeali
|
SQL 机器学习/深度学习 Oracle
[20180630]truncate table的另类恢复2.txt
[20180630]truncate table的另类恢复2.txt --//上个星期做了truncate table的另类恢复,通过修改数据块的段号,再通过rowid定位收集数据,达到修复的目的.
lfreeali
1573 0
lfreeali
|
数据库管理
[20180627]truncate table的另类恢复.txt
[20180627]truncate table的另类恢复.txt --//前几天看链接http://www.xifenfei.com/2018/06/truncate-table-recovery.
lfreeali
1383 0
科技小能手
|
安全 Oracle 关系型数据库
oracle 中删除表 drop delete truncate 的区别
科技小能手
1037 0
技术小美
|
索引
闪回之 回收站、Flashback Drop （table、index、trigger等）
技术小美
1203 0
技术小美
|
uml
truncate有外键约束的表，报ORA-02266处理。
技术小美
962 0
忆夏老师
|
SQL Oracle 关系型数据库
Oracle的闪回特性之恢复truncate删除表的数据
/* 2008/06/06*环境：Windows XP +Oracle10.2.0.1*循序渐进oracle——数据库管理、优化与备份恢复*循序渐进oracle第8章：Oracle的闪回特性之恢复truncate删除表的数据*/SQL> show parameter flashback...
忆夏老师
1443 0

热门文章

最新文章

  • 1
    Mac 上 iterm2 和 VSCode 终端中的字体设置建议
  • 2
    从零开始做逆变器系列 ( 二 ): 单极性、双极性、单极性倍频SPWM
  • 3
    信用算力基于 RocketMQ 实现金融级数据服务的实践
  • 4
    ubuntu如何实现访问实际网络中windows共享文件夹
  • 5
    springboot之使用redistemplate优雅地操作redis
  • 6
    阿里云服务器价格表2022更新（多CPU内存配置报价）
  • 7
    向AI医疗再次进军，DeepMind利用人工智能预测急性肾损伤
  • 8
    蒙特卡罗方法 python 实现2
  • 9
    [ASP.NET]国外的几款菜单似的导航栏
  • 10
    mm_struct
  • 1
    基于模糊PID控制的六步逆变器供电无刷直流电机调速simulink仿真
    21
  • 2
    使用 Python 合并微信与支付宝账单，生成财务报告
    18
  • 3
    泵浦光与斯托克斯光相遇耦合效应的matlab模拟与仿真
    22
  • 4
    一、MyBatis简介：MyBatis历史、MyBatis特性、和其它持久化层技术对比、Mybatis下载依赖包流程
    92
  • 5
    《DeepSeek成本“瘦身”秘籍：云原生技术全解析》
    31
  • 6
    《DeepSeek牵手云原生，流量管理玩出新高度》
    27
  • 7
    《DeepSeek借云原生容器化，解锁产品迭代新速度》
    26
  • 8
    《云原生技术：DeepSeek分布式推理的效能倍增器》
    20
  • 9
    《深度解析DeepSeek-M8：量子经典融合，重塑计算能效格局》
    32
  • 10
    JVM简介—2.垃圾回收器和内存分配策略
    29

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册

    • 相关实验场景

    更多
  • 使用SQL语句实现数据插入、修改和删除操作
  • PolarDB MySQL从表回收站中恢复误删的表
    • 下一篇
    通义万相2.1视频/图像模型新升级！可在阿里云百炼直接体验