使用ruby将ql用户下的表全部彻底删除，回收站没有，因此使用ldquo;闪回表rdquo;失效；flashback database被关闭；只有通过介质
1. 故障原因
使用ruby将ql用户下的表全部彻底删除，，回收站没有，因此使用“闪回表”失效；
flashback database被关闭；
只有通过介质恢复。
2. 使用rman实现基于时间点的恢复
2.1 启动数据库到mount
sql> startup nomount;
oracle instance started.
total system global area 2254802944 bytes
fixed size 2215344 bytes
variable size 1375732304 bytes
database buffers 872415232 bytes
redo buffers 4440064 bytes
2.2 恢复控制文件到误删除之前的一个副本
rman> restore controlfile from '/opt/oracle/11g/flash_recovery_area/orcl//2012_06_03/o1_mf_s_784987822_7worxgk6_.bkp';
2.3 mount数据库
sql> alter database mount;
database altered.
2.4 将数据库文件恢复到特定时间点
[oracle@localhost ~]$ export nls_date_format='yyyy-mm-dd hh24:mi:ss'
[oracle@localhost ~]$ rman target /;
rman> run
2> {
3> set until time '2012-06-06 21:00:00';
4> restore database;
5> recover database
6> };
executing command: set until clause
-- 开始介质恢复
starting restore at 2012-06-07 09:32:23
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 /opt/oracle/11g/oradata/o01.dbf
channel ora_disk_1: restoring datafile 00002 to /opt/oracle/11g/oradata/o01.dbf
channel ora_disk_1: restoring datafile 00003 to /opt/oracle/11g/oradata/os01.dbf
channel ora_disk_1: restoring datafile 00004 to /opt/oracle/11g/oradata/o1.dbf
channel ora_disk_1: restoring datafile 00005 to /opt/oracle/11g/oradata/oe01.dbf
channel ora_disk_1: restoring datafile 00006 to /opt/oracle/11g/software/s.dbf
channel ora_disk_1: reading from backup piece /opt/oracle/11g/flash_recovrcl/backupset/2012_06_03/o1_mf_nnndf_tag20120603t120856_7wortx30_.bkp
channel ora_disk_1: piece handle=/opt/oracle/11g/flash_recovery_area/orcltag20120603t120856_7wortx30_.bkp tag=tag20120603t120856
channel ora_disk_1: restored backup piece 1
channel ora_disk_1: restore complete, elapsed time: 00:01:08
finished restore at 2012-06-07 09:33:31
-- 开始日志恢复
starting recover at 2012-06-07 09:33:31
using channel ora_disk_1
starting media recovery
archived log for thread 1 with sequence 294 is already on disk as file /oorcl/archivelog/2012_06_04/o1_mf_1_294_7wqlknmq_.arc
archived log for thread 1 with sequence 295 is already on disk as file /oorcl/archivelog/2012_06_04/o1_mf_1_295_7wrdpv34_.arc
archived log for thread 1 with sequence 296 is already on disk as file /oorcl/archivelog/2012_06_05/o1_mf_1_296_7wtd8745_.arc
archived log for thread 1 with sequence 297 is already on disk as file /oorcl/archivelog/2012_06_05/o1_mf_1_297_7ww7rbh6_.arc
archived log for thread 1 with sequence 298 is already on disk as file /oorcl/archivelog/2012_06_06/o1_mf_1_298_7wx5rl22_.arc
archived log for thread 1 with sequence 299 is already on disk as file /olog
archived log file name=/opt/oracle/11g/flash_recovery_area/orcl/archivelo.arc thread=1 sequence=294
archived log file name=/opt/oracle/11g/flash_recovery_area/orcl/archivelo.arc thread=1 sequence=295
archived log file name=/opt/oracle/11g/flash_recovery_area/orcl/archivelo.arc thread=1 sequence=296
archived log file name=/opt/oracle/11g/flash_recovery_area/orcl/archivelo.arc thread=1 sequence=297
archived log file name=/opt/oracle/11g/flash_recovery_area/orcl/archivelo.arc thread=1 sequence=298
archived log file name=/opt/oracle/11g/oradata/orcl/redo02.log thread=1 s
media recovery complete, elapsed time: 00:00:44
finished recover at 2012-06-07 09:34:15
2.5 打开数据库
sql> alter database open resetlogs;
database altered.
2.6 查看恢复后的表
sql> conn ql/ql;
connected.
sql> select * from tab;
tname tabtype clusterid
------------------------------ ------- ----------
tbl_sns_attention table
tbl_sns_blog table
tbl_sns_blogread table
tbl_sns_blogreply table
tbl_sns_blogtranfer table
tbl_sns_imptblog table
tbl_sns_mail table
tname tabtype clusterid
------------------------------ ------- ----------
tbl_user_admin table
tbl_user_expert table
tbl_user_info table
tbl_user_parent table
tbl_user_starprincipal table
tbl_user_starteacher table
tbl_user_student table
tbl_user_teacher table
tbl_user_tranfer table
3. 后续操作
3.1 马上备份数据库
rman> backup database;
starting backup at 2012-06-07 09:36:21
using target database control file instead of recovery catalog
allocated channel: ora_disk_1
channel ora_disk_1: sid=129 device type=disk
channel ora_disk_1: starting full datafile backup set
channel ora_disk_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/opt/oracle/11g/oradata/orcl/undotb
input datafile file number=00002 name=/opt/oracle/11g/oradata/orcl/sysaux
input datafile file number=00001 name=/opt/oracle/11g/oradata/orcl/system
input datafile file number=00004 name=/opt/oracle/11g/oradata/orcl/users0
input datafile file number=00005 name=/opt/oracle/11g/oradata/orcl/exampl
input datafile file number=00006 name=/opt/oracle/11g/software/dbs/rman_t
channel ora_disk_1: starting piece 1 at 2012-06-07 09:36:22
channel ora_disk_1: finished piece 1 at 2012-06-07 09:37:17
piece handle=/opt/oracle/11g/flash_recovery_area/orcl/backupset/2012_06_0bkp tag=tag20120607t093621 comment=none
channel ora_disk_1: backup set complete, elapsed time: 00:00:55
finished backup at 2012-06-07 09:37:17
starting control file and spfile autobackup at 2012-06-07 09:37:17
piece handle=/opt/oracle/11g/flash_recovery_area/orcl/autobackup/2012_06_none
finished control file and spfile autobackup at 2012-06-07 09:37:18
3.2 回收ql删除表的权限