be异机恢复linux oracle,Oracle 11g没有备份文件参数文件在异机通过RMAN备份恢复找回被误删的数据...

2，使用sys.dbms_backup_restore.restoreDatafileTo来做不完全一致数据恢复

2.1，先做restoreDatafileTo的数据抽取工作，也就是将数据文件从备份集中抽取出来，记得数据文件路径要与线上保持一致。

执行命令如下：

SQL> DECLARE

devtype varchar2(256);

doneboolean;

BEGIN

devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'t1');

sys.dbms_backup_restore.restoreSetDatafile;

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'/home/oradata/powerdes/system01.dbf');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'/home/oradata/powerdes/sysaux01.dbf');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'/home/oradata/powerdes/undotbs01.dbf');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'/home/oradata/powerdes/users01.dbf');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'/home/oradata/powerdes/powerdesk01.dbf');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>06,toname=>'/home/oradata/powerdes/plas01.dbf');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>07,toname=>'/home/oradata/powerdes/pl01.dbf');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>08,toname=>'/home/oradata/powerdes/help01.dbf');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>09,toname=>'/home/oradata/powerdes/adobelc01.dbf');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>10,toname=>'/home/oradata/powerdes/sms01.dbf');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>11,toname=>'/home/oradata/powerdes/plcrm01.dbf');

sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/pddata2/Oracle/backup/data/2015-03-30/full_POWERDES_20150330_3395.bak',params=>null);

sys.dbms_backup_restore.deviceDeallocate;

END;

/

PS：handle是执行备份的bak文件；最后的/不能少。

SQL>

执行过程需要一段时间，慢慢等待，后台的alert日志会依次有如下信息显示出来提示抽取成功：

Full restore complete of datafile 7 to datafilecopy /home/oradata/powerdes/pl01.dbf. Elapsed time: 0:00:41

checkpoint is 11029786628

last deallocation scn is 11022714700

Full restore complete of datafile 8 todatafile copy /home/oradata/powerdes/help01.dbf.  Elapsed time: 0:00:01

checkpoint is 11029786628

last deallocation scn is 9881798870

Wed Apr 01 05:01:54 2015

Full restore complete of datafile 9 todatafile copy /home/oradata/powerdes/adobelc01.dbf.  Elapsed time: 0:00:00

checkpoint is 11029786628

Wed Apr 01 05:02:12 2015

Full restore complete of datafile 10 todatafile copy /home/oradata/powerdes/sms01.dbf. Elapsed time: 0:00:12

checkpoint is 11029786628

Wed Apr 01 05:05:15 2015

Full restore complete of datafile 3 todatafile copy /home/oradata/powerdes/undotbs01.dbf.  Elapsed time: 0:04:45

checkpoint is 11029786628

last deallocation scn is 11029784977

Undo Optimization current scn is 11029779851

Wed Apr 01 05:06:04 2015

Full restore complete of datafile 11 todatafile copy /home/oradata/powerdes/plcrm01.dbf.  Elapsed time: 0:06:01

checkpoint is 11029786628

last deallocation scn is 11007637870

Wed Apr 01 05:06:27 2015

Full restore complete of datafile 4 todatafile copy /home/oradata/powerdes/users01.dbf.  Elapsed time: 0:06:24

checkpoint is 11029786628

last deallocation scn is 11029437543

Wed Apr 01 05:10:33 2015

Full restore complete of datafile 1 todatafile copy /home/oradata/powerdes/system01.dbf.  Elapsed time: 0:09:27

checkpoint is 11029786628

last deallocation scn is 10910611499

Undo Optimization current scn is 11029779851

Wed Apr 01 05:13:39 2015

Full restore complete of datafile 2 todatafile copy /home/oradata/powerdes/sysaux01.dbf.  Elapsed time: 0:13:29

checkpoint is 11029786628

last deallocation scn is 11028968586

Wed Apr 01 05:14:44 2015

Full restore complete of datafile 6 todatafile copy /home/oradata/powerdes/plas01.dbf.  Elapsed time: 0:14:37

checkpoint is 11029786628

last deallocation scn is 11028348038

Wed Apr 01 05:39:30 2015

Full restore complete of datafile 5 todatafile copy /home/oradata/powerdes/powerdesk01.dbf.  Elapsed time: 0:39:12

checkpoint is 11029786628

last deallocation scn is 11029438448

2.2 数据抽取成功后，要创新创建控制文件

如果不知道如何创建controlfile的命令，可以在线上生成trace文件

一般默认的控制文件是二进制的，打开来是乱码的 ，备份一个trace出来 可以打开看到语句了,$ORACLE_BASE/admin/$ORACLE_SID/udump目录下，生成的新的 trace 文件里，trace文件有生成控制文件的脚本，使用如下命令alter database backup controlfile to trace as'/oracle/app/oracle/admin/powerdes/pfile/control.sql';可以得到创建控制文件的sql命令。

整理出来创建控制文件命令如下：

CREATE CONTROLFILE REUSE SET DATABASE"POWERDES" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 2920

DATAFILE

'/home/oradata/powerdes/system01.dbf',

'/home/oradata/powerdes/sysaux01.dbf',

'/home/oradata/powerdes/undotbs01.dbf',

'/home/oradata/powerdes/users01.dbf',

'/home/oradata/powerdes/powerdesk01.dbf',

'/home/oradata/powerdes/plas01.dbf',

'/home/oradata/powerdes/pl01.dbf',

'/home/oradata/powerdes/help01.dbf',

'/home/oradata/powerdes/adobelc01.dbf',

'/home/oradata/powerdes/sms01.dbf',

'/home/oradata/powerdes/plcrm01.dbf'

LOGFILE

GROUP 1 '/home/oradata/powerdes/redo01.log'  SIZE 50M BLOCKSIZE 512,

GROUP 2 '/home/oradata/powerdes/redo02.log'  SIZE 50M BLOCKSIZE 512,

GROUP 3 '/home/oradata/powerdes/redo03.log'  SIZE 50M BLOCKSIZE 512

CHARACTER SET ZHS16GBK;

2.3 恢复数据并且打开数据库

RMAN> recover database;

Starting recover at 02-APR-15

using target database control file insteadof recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=767 devicetype=DISK

starting media recovery

unable to find archived log

archived log thread=1 sequence=31023

RMAN-00571:===========================================================

RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-03002: failure of recover command at04/02/2015 13:35:40

RMAN-06054: media recovery requestingunknown archived log for thread 1 with sequence 31023 and starting SCN of11029786628

RMAN>

RMAN> restore database from'/pddata2/oracle/backup/data/2015-03-30/full_POWERDES_20150330_3395.bak';

Starting restore at 02-APR-15

using channel ORA_DISK_1

RMAN-00571:===========================================================

RMAN-00569: =============== ERROR MESSAGESTACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-03002: failure of restore command at04/02/2015 13:37:05

RMAN-06509: only SPFILE or control file canbe restored from AUTOBACKUP

RMAN>

看到打开都报错了，所以试试直接open打开吧

SQL> SQL> alter database open resetlogs

2  ;

Database altered.

SQL>

3，然后exp导出表数据,imp导入到生产环境

导出：exp system/manager@myoracle file=/tmp/qd.dmp tables=(hr_resume_qingdao)    system是用户名，manager 是密码，myoracle 是数据库名。

传输数据：scp /tmp/qd.dmp 192.168.120.217:/tmp/

导入：imp username/pwd[@sid] file=/tmp/ qd.dmp  tables=( hr_resume_qingdao)

4，一些报错记录

找到数据文件

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-01200: actual file size of 128679 issmaller than correct size of 340480

blocks

ORA-01110: data file 2:'/home/oradata/powerdes/sysaux01.dbf'

解决方法：

dd if=/dev/zeroof=/home/oradata/powerdes/sysaux01.dbf bs=8192 count=1 seek=340480