|
如果表的很多分区被意外drop需要利用带库上的备份集在测试机上做不完全恢复具体日期需要达到一个特定的时间那么我们可以参考下文 ◆首先需要用logminer查出删除语句 uncompress /oracle/oradata/xxx/xxxARCZ beginsysdbms_logmnradd_logfile(/oracle/oradata/xxx/xxxARC);end;begin sysdbms_logmnrstart_logmnr(Options => sysdbms_logmnrDICT_FROM_ONLINE _CATALOG); end;create table xsb_logminer_ as select timestamp seg_nameoperation sql_redo from V$LOGMNR_CONTENTS where seg_owner=XXX and operation=DDL and sql_redo like ALTER TABLE XXX DROP PARTITION %;BEGIN sysdbms_logmnrend_logmnr();END; ◆然后在生产库上创建pfile create pfile= from spfile; 需要从带库上恢复rman备份集至生产机上将此备份集FTP至测试机上连同pfile文件 在测试机上创建与生产机上相同目录admin …… 在测试机上创建新实例 orapwd file= password=xxx 然后修改pfile文件内容改变control_files内容 ◆启动新实例 export ORACLE_SID=xxxsqlplus / as sysdbastartup nomount pfile=/home/oracle/init_xxxora;create spfile from pfile=/home/oracle/init_xxxora;exitRMAN target sys/xxxrestore controlfile from ;startup mountcrosscheck backup;list backup;run {set newname for datafile to /oracle/oradata/xxx/systemdbf;set newname for datafile to /oracle/oradata/xxx/undodbf;set newname for datafile to /oracle/oradata/xxx/sysauxdbf;set newname for datafile to /oracle/oradata/xxx/pay_tsdbf;restore datafile ;restore datafile ;restore datafile ; restore datafile ;}(注发现单个datafile恢复不如整库恢复快！)sql alter database backup controlfile to trace;shutdown immediate;exit 然后修改controlfile文件内容去掉不用的文件名 ◆不完全恢复数据库 sqlplus / as sysdbastartup nomount; ◆重建控制文件 CREATE CONTROLFILE REUSE DATABASE XXX RESETLOGS ARCHIVELOGMAXLOGFILES MAXLOGMEMBERS MAXDATAFILES MAXINSTANCES MAXLOGHISTORY LOGFILEGROUP (/oracle/oradata/xxx/rdb_redoa) SIZE MGROUP (/oracle/oradata/xxx/rdb_redoa) SIZE MDATAFILE /oracle/oradata/xxx/systemdbf/oracle/oradata/xxx/undodbf/oracle/oradata/xxx/sysauxdbf/oracle/oradata/xxx/pay_tsdbfCHARACTER SET ZHSGBK;recover database until cancel using backup controlfile;alter database open resetlogs; ◆做数据恢复操作 create table xxx as select xxx from xxx where xxx;expftp dmpimp 也可以如下所示这样可以更简单一点 restore database;recover database using backup controlfile until cancel;alter database open resetlogs;END |
