13518219792
本篇内容主要讲解“SQL怎么恢复drop掉的表”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“SQL怎么恢复drop掉的表”吧!
让客户满意是我们工作的目标，不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户，将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴，公司提供的服务项目有：空间域名、雅安服务器托管、营销软件、网站建设、沧源网站维护、网站推广。
如果误删数据，在10g或更高版本，可用使用回收站恢复drop掉的表。
首先确保回收站在使用中
SQL> show parameter recycle
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle string
db_recycle_cache_size big integer 0
recyclebin string on
确保recyclebin中有drop掉的表
SQL> select owner,original_name from dba_recyclebin where original_name='BACKUPTEST';
OWNER ORIGINAL_NAME
------------------------------ --------------------------------
SCOTT BACKUPTEST
闪回表
SQL> flashback table scott.backuptest to before drop;
Flashback complete.
SQL> select * from scott.backuptest;
A
----------
1
从其他介质恢复删除的表
1.如果有在数据变更前的备份（exp，expdp，duplicate，rman备份），可用利用这个备份恢复数据库或者表空间等等，找到变更前的表，然后转入到数据库中。
2.使用rman的until scn，seq，time等参数恢复数据库或表空间到另外一个环境，找到更改前的表然后转入正式库
恢复数据库
RMAN> restore database ;
Starting restore at 2017-07-30 14:44:56
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
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 /u01/app/oracle/oradata/test/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/test/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/test/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/1csal8ic_1_1
channel ORA_DISK_1: piece handle=/home/oracle/1csal8ic_1_1 tag=TAG20170730T131244
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 2017-07-30 14:45:12
recover到指定时间点
RMAN> recover database until time '2017-07-30 14:00:00';
Starting recover at 2017-07-30 14:51:57
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/test/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/test/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/test/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/test/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/1fsal9aj_1_1
channel ORA_DISK_1: piece handle=/home/oracle/1fsal9aj_1_1 tag=TAG20170730T132538
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2017-07-30 14:51:58
RMAN> alter database open resetlogs;
database opened
找到丢失的表然后执行导出导入到目标库就可以了
参考文档
How to Recover From a DROP / TRUNCATE / DELETE TABLE with RMAN (文档 ID 223543.1)
到此，相信大家对“SQL怎么恢复drop掉的表”有了更深的了解，不妨来实际操作一番吧！这里是创新互联网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
