欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

使用RMAN实现不完全恢复

程序员文章站 2022-05-14 22:08:48
...

使用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删除表的权限

使用RMAN实现不完全恢复