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;
/
SQL>
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>alterdatabaseopenresetlogs2;Databasealtered.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
不要气馁于那前方的阴影,那只是因为我背后光芒万丈