rman备份文件,而且是备份的数据文件,没有控制文件没有参数文件的备份,所以普通的
(1) 先恢复控制文件restore controlfile from ‘…bak’;
(2) 然后catalog start with ‘/data/2015-05-01/’注册备份文件,
(3) 最后restore database;recover database;恢复数据库。
三板斧的常规途径是彻底的行不通了,咋办?咋办?咋办呢?……
2、先进行数据文件的剥离
突然想起以前记得看过关于restoreDatafileTo数据抽取的操作思路,大概是如果没有控制文件后,可以从rman的数据文件备份和归档日志备份里面抽取数据文件,然后重新建立控制文件,再用resetlogs方式打开数据库:
因为抽取命令里面需要填写一个个数据文件,这里有个前提是自己要熟悉自己的Oracle实例的文件目录,知道备份的时候oracle实例有多少个数据文件(包括文件存放目录),这样就可以快速的整理出来抽取命令的sql。如果这些都忘记了,还可以去备份日志文件里面去查看check下,一般备份日志文件里面都会有的。
根据以前记录整理下抽取命令:
DECLARE
devtype varchar2(256);
done boolean;
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=>'/data/2015-05-01/full_POWERDES_20150501_3566.bak', params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
/
执行过程如下:
[oracle@pldb236 oradata]$ rlwrap sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Oct 17 21:19:32 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
DECLARE
owerdes/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');
devtype varchar2(256);
done boolean;
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=>'/data/2015-05-01/full_POWERDES_20150501_3566.bak', params=>null);
sys.dbms_backup_restore.deviceDeallocate;
END;
21 /
PL/SQL procedure successfully completed.
SQL>
后台alert日志会显示正在不停的剥离出文件到指定目录里面去:
CKPT started with pid=13, OS id=23993
Mon Oct 17 21:16:59 2016
SMON started with pid=14, OS id=23995
Mon Oct 17 21:16:59 2016
RECO started with pid=15, OS id=23997
Mon Oct 17 21:16:59 2016
MMON started with pid=16, OS id=23999
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Mon Oct 17 21:16:59 2016
MMNL started with pid=17, OS id=24001
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /oracle/app/oracle
Mon Oct 17 21:20:14 2016
Full restore complete of datafile 7 to datafile copy /home/oradata/powerdes/pl01.dbf. Elapsed time: 0:00:27
checkpoint is 11106141982
last deallocation scn is 11082135537
Full restore complete of datafile 8 to datafile copy /home/oradata/powerdes/help01.dbf. Elapsed time: 0:00:03
checkpoint is 11106141982
last deallocation scn is 9881798870
Full restore complete of datafile 9 to datafile copy /home/oradata/powerdes/adobelc01.dbf. Elapsed time: 0:00:01
checkpoint is 11106141982
Mon Oct 17 21:20:32 2016
Full restore complete of datafile 10 to datafile copy /home/oradata/powerdes/sms01.dbf. Elapsed time: 0:00:10
checkpoint is 11106141982
Mon Oct 17 21:21:25 2016
Full restore complete of datafile 3 to datafile copy /home/oradata/powerdes/undotbs01.dbf. Elapsed time: 0:01:23
checkpoint is 11106141982
last deallocation scn is 11106022955
Undo Optimization current scn is 11106076830
Mon Oct 17 21:22:28 2016
Full restore complete of datafile 4 to datafile copy /home/oradata/powerdes/users01.dbf. Elapsed time: 0:02:53
checkpoint is 11106141982
last deallocation scn is 11082633897
Mon Oct 17 21:23:47 2016
Full restore complete of datafile 11 to datafile copy /home/oradata/powerdes/plcrm01.dbf. Elapsed time: 0:04:11
checkpoint is 11106141982
last deallocation scn is 11100156728
Mon Oct 17 21:25:30 2016
Full restore complete of datafile 1 to datafile copy /home/oradata/powerdes/system01.dbf. Elapsed time: 0:05:34
checkpoint is 11106141982
last deallocation scn is 11039454999
Undo Optimization current scn is 11106076830
Mon Oct 17 21:28:10 2016
Full restore complete of datafile 2 to datafile copy /home/oradata/powerdes/sysaux01.dbf. Elapsed time: 0:08:27
checkpoint is 11106141982
last deallocation scn is 11101587434
Mon Oct 17 21:29:11 2016
Full restore complete of datafile 6 to datafile copy /home/oradata/powerdes/plas01.dbf. Elapsed time: 0:09:33
checkpoint is 11106141982
last deallocation scn is 11082142314
3、建立控制文件
数据文件抽取成功后,需要单独自己创建控制文件,如果不知道如何创建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命令。
这里有个前提是自己要熟悉自己的oracle实例的文件目录,知道备份的时候oracle实例有多少个数据文件,有多少个redo log文件,这样就可以快速的整理出来创建控制文件的sql。如果这些都忘记了,还可以去备份日志文件里面去查看check下,一般备份日志文件里面都会有的。
整理出来创建控制文件sql命令如下:
(1)命令如下
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)执行过程如下:
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
23 CHARACTER SET ZHS16GBK;
Control file created.
SQL>
4、进行数据恢复
开始执行数据恢复,还是依然在sql窗口里面进行操作的,操作如下:
#(1) 执行recover恢复
SQL> recover database using backup controlfile until cancel ;
ORA-00279: change 11106141982 generated at 05/01/2015 03:00:08 needed for
thread 1
ORA-00289: suggestion :
/oracle/app/oracle/flash_recovery_area/POWERDES/archivelog/2016_10_17/o1_mf_1_32
117_%u_.arc
ORA-00280: change 11106141982 for thread 1 is in sequence #32117
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} # 这里一般选择输入cancel即可
cancel
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 2 needs more recovery to be consistent
ORA-01110: data file 2: '/home/oradata/powerdes/sysaux01.dbf'
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 2 needs more recovery to be consistent
ORA-01110: data file 2: '/home/oradata/powerdes/sysaux01.dbf'
# 这时,我们无法将数据库打开,一直报ORA-01194错误,说明数据库的SCN号和数据文件的SCN号不一致了。这是因为控制文件我们不是从备份集里面恢复回来的,而是在抽取数据文件后手动建立的控制文件,因此要比数据文件的SCN号要大(甚至特殊情况当前的数据库的会是0)。通过对v$database和v$datafile的checkpoint_change#列的查询,可以确定出本次操作中当前数据库的checkpoint_chenage#为0,两者完全不一致导致通过resetlogs打开数据库异常。
Bty:如果这里当前数据库v$database的值不为0,但是仍然比数据文件v$datafile里面的值大,那么则会不停报ORA-01152错误。
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
0
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1.1106E+10
2 1.1106E+10
3 1.1106E+10
4 1.1106E+10
5 1.1106E+10
6 1.1106E+10
7 1.1106E+10
8 1.1106E+10
9 1.1106E+10
10 1.1106E+10
11 1.1106E+10
11 rows selected.
----------------为什么不回复archivelog apply一下呢
怎么办呢?这个时候,就需要我们使用_allow_resetlogs_corruption的隐含参数来处理了。
整个调整的目标是强制启动数据库,设置此参数之后,在数据库Open过程中,Oracle会跳过某些一致性检查,从而使数据库可能跳过不一致状态,Open打开:
# 启动隐含参数
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL> # 然后重启数据库,使参数生效,在此基础上再次恢复数据库
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 6680915968 bytes
Fixed Size 2213936 bytes
Variable Size 4898949072 bytes
Database Buffers 1744830464 bytes
Redo Buffers 34922496 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 11106141982 generated at 05/01/2015 03:00:08 needed for
thread 1
ORA-00289: suggestion :
/oracle/app/oracle/flash_recovery_area/POWERDES/archivelog/2016_10_17/o1_mf_1_32
117_%u_.arc
ORA-00280: change 11106141982 for thread 1 is in sequence #32117
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel-----既然不一致打开这个还有必要吗
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 2 needs more recovery to be consistent
ORA-01110: data file 2: '/home/oradata/powerdes/sysaux01.dbf'
# 然后使用resetlogs打开数据库,成功了。
SQL> alter database open resetlogs;
Database altered.
SQL>
# 然后检查scn,都是统一的了。
select checkpoint_change# from v$database;
select file#,checkpoint_change# from v$datafile;
然后使用业务表数据来判断是否已经恢复成功到这一天,查看后确认成功:
SQL> select t2.* from(select t.uiid,t.updated_date from plas.plas_acct t where t.updated_date is not null order by t.updated_date desc ) t2 where rownum <10;
UIID UPDATED_DATE
-------------------------------------------------- ------------
wangyu1 01-MAY-15
gaihy 01-MAY-15
xuhl 01-MAY-15
dingchuan1 01-MAY-15
zhangcong 01-MAY-15
chenwh2 01-MAY-15
yuli2 01-MAY-15
zhangxya 01-MAY-15
qiuwj 01-MAY-15
9 rows selected.
SQL>
至此,没有控制文件下通过rman恢复一年的数据做成了,然后通过expdp导出需要的数据,之后对数据库进行恢复或者重建等等。
这里假定是nocatalog的情况下,我们采用了RMAN备份,但是丢失了控制文件,因为控制文件中包含了rman的备份信息,所以没有办法用平常的方法来恢复,如果想恢复数据库,则需要利用一个叫DBMS_BACKUP_RESTORE的包来实现,这个包在nomount下就可以正常运行,也就是说,只需要启动到nomount下就可以利用它来恢复控制文件或者数据文件以及归档日志。
1、从备份片中恢复控制文件
最好有控制文件能恢复,就算恢复的这个控制文件没有最新的备份信息也好,起码可以利用它来定位数据文件。如果没有控制文件可恢复,后来的数据文件恢复将变的更复杂。以下是恢复控制文件的脚本,需要在sys as dba的连接下执行。
- DECLARE
- v_dev varchar2(50); /* 设备类型 */
- v_done boolean; /* 恢复(restore)完成标志 */
- type t_fileTable is table of varchar2(255) index by binary_integer;
- v_fileTable t_fileTable; /* 备份片的名字 */
- v_maxPieces number:=1; /* 备份片的个数 */
- BEGIN
- /*首先定义可以用的备份片与备份片的个数 */
- v_fileTable(1):='fulldb_s15_p1';
- v_fileTable(2):='fulldb_s15_p2';
- v_fileTable(3):='fulldb_s15_p3';
- v_fileTable(4):='fulldb_s15_p4';
- v_maxPieces:=4;
- /*分配设备类型,如果是磁带,则是sbt_tape,如果是磁盘,则是null*/
- v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>'sbt_tape', ident=>'t1');
- sys.dbms_backup_restore.restoreSetDatafile;
- /*要恢复的控制文件路径与文件名称,文件名称要求正确*/
- sys.dbms_backup_restore.restoreControlfileTo(cfname=>'/u1/oradata/dbs/ctrl1V804.ctl');
- /*开始恢复*/
- FOR i IN 1..v_maxPieces LOOP
- sys.dbms_backup_restore.restoreBackupPiece(done=>v_done,
- handle=>v_fileTable(i),
- params=>null);
- IF v_done THEN
- GOTO all_done;
- END IF;
- END LOOP;
- <<all_done>>
- /* 释放设备 */
- sys.dbms_backup_restore.deviceDeallocate;
- END;
- /
2、从备份片中恢复数据文件
恢复数据文件的时候,必须要知道数据文件对应的文件编号,而且最好能知道数据文件位于哪个备份片,所以就是前面说的最好能先resotre一个控制文件出来,如果实在不行,能用备份时候的日志也可以。以下是恢复全备份的下的数据文件的脚本:
- DECLARE
- v_dev varchar2(50); /* 设备类型 */
- v_done boolean:=false; /* 恢复(restore)完成标志 */
- type t_fileTable is table of varchar2(255) index by binary_integer;
- v_fileTable t_fileTable; /* 备份片的名字 */
- v_maxPieces number:=1; /* 备份片的个数 */
- BEGIN
- /* 初始化备份片 */
- v_fileTable(1):='fulldb_s15_p1';
- v_fileTable(2):='fulldb_s15_p2';
- v_fileTable(3):='fulldb_s15_p3';
- v_fileTable(4):='fulldb_s15_p4';
- v_maxPieces:=4;
- /* 设备类型,磁带:sbt_tape,磁盘:null */
- v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>'sbt_tape',ident=>'t1');
- sys.dbms_backup_restore.restoreSetDatafile;
- /* 要恢复的文件,指定文件号与文件名称 */
- sys.dbms_backup_restore.restoreDataFileTo(dfnumber=>1,
- toname=>'/u1/oradata/dbs/sysV804.dbf');
- /* 恢复数据文件 */
- FOR i IN 1..v_maxPieces LOOP
- sys.dbms_backup_restore.restoreBackupPiece(done=>v_done,
- handle=>v_fileTable(i),
- params=>null);
- IF v_done THEN
- GOTO all_done;
- END IF;
- END LOOP;
- <<all_done>>
- /* 释放设备 */
- sys.dbms_backup_restore.deviceDeallocate;
- END;
- /
当然,有的时候,我们不仅仅是做全备份,还要做增量备份,那么我们怎么从增量备份中应用备份到数据文件中呢,我们可以参考如下脚本:
- DECLARE
- v_dev varchar2(50); /* 设备类型 */
- v_done boolean:=false; /* 恢复(restore)完成标志 */
- type t_fileTable is table of varchar2(255) index by binary_integer;
- v_fileTable t_fileTable; /* 备份片的名字 */
- v_maxPieces number:=1; /* 备份片的个数 */
- BEGIN
- /* 初始化备份片,这里指的是增量备份的备份片 */
- v_fileTable(1):='fulldb_level2_s18_p1';
- v_maxPieces:=1;
- /* 设备类型,磁带:sbt_tape,磁盘:null */
- v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>'sbt_tape',ident=>'t1');
- sys.dbms_backup_restore.applySetDataFile;
- /* 如果合并的数据文件,也就是需要从增量中恢复部分新的块到该数据文件中去 */
- sys.dbms_backup_restore.applyDataFileTo(dfnumber=>1,
- toname=>'/u1/oradata/dbs/sysV804.dbf');
- /* 恢复数据文件 */
- FOR i IN 1..v_maxPieces LOOP
- sys.dbms_backup_restore.applyBackupPiece(done=>v_done,
- handle=>v_fileTable(i),
- params=>null);
- IF v_done THEN
- GOTO all_done;
- END IF;
- END LOOP;
- <<all_done>>
- /* 释放设备 */
- sys.dbms_backup_restore.deviceDeallocate;
- END;
- /
3、从备份片中恢复归档日志
如果restore完数据文件,归档日志也是可能需要做restore的,用于做recover,可以采用如下的脚本恢复archive log:
- DECLARE
- v_dev varchar2(50); /* 设备类型 */
- v_done boolean:=false; /* 恢复(restore)完成标志 */
- type t_fileTable is table of varchar2(255) index by binary_integer;
- v_fileTable t_fileTable; /* 备份片的名字 */
- v_maxPieces number:=1; /* 备份片的个数 */
- BEGIN
- /* 初始化备份片,归档日志的备份片 */
- v_fileTable(1):='al_s20_p1';
- v_fileTable(2):='al_s20_p2';
- v_maxPieces:=2;
- /* 设备类型,磁带:sbt_tape,磁盘:null */
- v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>'sbt_tape',ident=>'t1');
- sys.dbms_backup_restore.restoreSetArchivedLog(destination=>'/app/oracle/admin/arch/arch_');
- /* 归档日志的序列号 */
- sys.dbms_backup_restore.restoreArchivedLog(thread=>1, sequence=>100);
- /* 开始恢复 */
- FOR i IN 1..v_maxPieces LOOP
- sys.dbms_backup_restore.restoreBackupPiece(done=>v_done,
- handle=>v_fileTable(i),
- params=>null);
- IF v_done THEN
- GOTO all_done;
- END IF;
- END LOOP;
- <<all_done>>
- /* 释放通道 */
- sys.dbms_backup_restore.deviceDeallocate;
- END;
- /
以上是恢复一个归档日志的脚本,如果是恢复批量的归档日志,可以采用增加如下内容在上面的脚本中
- for seq in <min seq#>..<max seq#> loop
- sys.dbms_backup_restore.restoreArchivedLog(thread=>1,
- sequence=>seq);
- end loop
注:以上的脚本来自metalink
在Oracle 816 以后的版本中,Oracle提供了一个包:DBMS_BACKUP_RESTORE.DBMS_BACKUP_RESTORE 包是由dbmsbkrs.sql 和 prvtbkrs.plb 这两个脚本创建的.catproc.sql 脚本运行后会调用这两个包.所以是每个数据库都有的这个包是Oracle服务器和操作系统之间IO操作的接口.由恢复管理器直接调用。而且据说这两个脚本 的功能是内建到Oracle的一些库文件中的.
由此可见,我们可以在数据库 nomount 情况下调用这些package ,来达到我们的恢复目的。在dbmsbkrs.sql 和prvtbkrs.plb 这两个脚本中有详细的说明文档,出于篇幅问题,就不一一加以翻译了,但在下面会直接引用一些原文说明。
关键的内容有:
FUNCTION deviceAllocate(
type IN varchar2 default NULL
,name IN varchar2 default NULL
,ident IN varchar2 default NULL
,noio IN boolean default FALSE
,params IN varchar2 default NULL )
RETURN varchar2;
-- Describe the device to be used for sequential I/O. For device types where
-- only one process at a time can use a device, this call allocates a device
-- for exclusive use by this session. The device remains allocated until
-- deviceDeallocate is called or session termination. The device can be used
-- both for creating and restoring backups.
--
-- Specifying a device allocates a context that exists until the session
-- terminates or deviceDeallocate is called. Only one device can be specified
-- at a time for a particular session. Thus deviceDeallocate must be called
-- before a different device can be specified. This is not a limitation since
-- a session can only read or write one backup at a time.
--
-- The other major effect of allocating a device is to specify the name space
-- for the backup handles (file names). The handle for a sequential file does
-- not necessarily define the type of device used to write the file. Thus it
-- is necessary to specify the device type in order to interpret the file
-- handle. The NULL device type is defined for all systems. It is the file
-- system supplied by the operating system. The sequential file handles are
-- thus normal file names.
--
-- A device can be specified either by name or by type.
-- If the type is specified but not the name, the system picks an
-- available device of that type.
-- If the name is specified but not the type, the type is determined
-- from the device.
-- If neither the type or the name is given, the backups are files in
-- the operating system file system.
-- Note that some types of devices, optical disks for example, can be shared
-- by many processes, and thus do not really require allocation of the device
-- itself. However we do need to allocate the context for accessing the
-- device, and we do need to know the device type for proper interpretation
-- of the file handle. Thus it is always necessary to make the device
-- allocation call before making most other calls in this package.
--
-- Input parameters:
-- type
-- If specified, this gives the type of device to use for sequential
-- I/O. The allowed types are port specific. For example a port may
-- support the type "TAPE" which is implemented via the Oracle tape
-- API. If no type is specified, it may be implied by specifying a
-- particular device name to allocate. The type should be allowed to
-- default to NULL if operating system files are to be used.
--
-- name
-- If specified, this names a particular piece of hardware to use for
-- accessing sequential files. If not specified, any available
-- device of the correct type will be allocated. If the device cannot
-- be shared, it is allocated to this session for exclusive use.
-- The name should be allowed to default to NULL if operating system
-- files are to be used.
--
-- ident
-- This is the users identifier that he uses to name this device. It
-- is only used to report the status of this session via
-- dbms_application_info. This value will be placed in the CLIENT_INFO
-- column of the V$SESSION table, in the row corresponding to the
-- session in which the device was allocated. This value can also
-- be queried with the dbms_application_info.read_client_info procedure.
--
-- noio
-- If TRUE, the device will not be used for doing any I/O. This allows
-- the specification of a device type for deleting sequential files
-- without actually allocating a piece of hardware. An allocation for
-- noio can also be used for issuing device commands. Note that some
-- commands may actually require a physical device and thus will get
-- an error if the allocate was done with noio set to TRUE.
--
-- params
-- This string is simply passed to the device allocate OSD. It is
-- completely port and device specific.
--
-- Returns:
-- It returns a valid device type. This is the type that should be
-- allocated to access the same sequential files at a later date. Note
-- that this might not be exactly the same value as the input string.
-- The allocate OSD may do some translation of the type passed in. The
-- return value is NULL when using operating system files.
PROCEDURE restoreControlfileTo(cfname IN varchar2);
-- This copies the controlfile from the backup set to an operating system
-- file. If the database is mounted, the name must NOT match any of the
-- current controlfiles.
--
-- Input parameters:
-- cfname
-- Name of file to create or overwrite with the controlfile from the
-- backup set.
PROCEDURE restoreDataFileTo( dfnumber IN binary_integer
,toname IN varchar2 default NULL);
--
-- restoreDataFileTo creates the output file from a complete backup in the
-- backup set.
如果您有兴趣可以去阅读一下这两个文件的注释说明.
解决过程
首先,用控制文件作数据库系统的全备份:
C:WUTemp>rman target /
Recovery Manager: Release 9.2.0.1.0 - Production.
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: DEMO (DBID=3272375326)
RMAN> run {
2> allocate channel C1 type disk;
3> backup full tag 'FullBackup' format 'd:/KDE/%d_%u_%s_%p.dbf' database include current controlfile;
4> sql ' alter system archive log current';
5> release channel C1;
6> }
using target database controlfile instead of recovery catalog
allocated channel: C1
channel C1: sid=15 devtype=DISK
Starting backup at 18-JUL-04
channel C1: starting full datafile backupset
channel C1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=D:/ORACLE/ORADATA/DEMO/SYSTEM01.DBF
input datafile fno=00002 name=D:/ORACLE/ORADATA/DEMO/UNDOTBS01.DBF
input datafile fno=00004 name=D:/ORACLE/ORADATA/DEMO/EXAMPLE01.DBF
input datafile fno=00009 name=D:/ORACLE/ORADATA/DEMO/XDB01.DBF
input datafile fno=00005 name=D:/ORACLE/ORADATA/DEMO/INDX01.DBF
input datafile fno=00008 name=D:/ORACLE/ORADATA/DEMO/USERS01.DBF
input datafile fno=00003 name=D:/ORACLE/ORADATA/DEMO/DRSYS01.DBF
input datafile fno=00006 name=D:/ORACLE/ORADATA/DEMO/ODM01.DBF
input datafile fno=00007 name=D:/ORACLE/ORADATA/DEMO/TOOLS01.DBF
channel C1: starting piece 1 at 18-JUL-04
channel C1: finished piece 1 at 18-JUL-04
piece handle=D:/KDE/DEMO_01FR79OT_1_1.DBF comment=NONE
channel C1: backup set complete, elapsed time: 00:01:17
Finished backup at 18-JUL-04
sql statement: alter system archive log current
released channel: C1
如上所示,我们做了一次数据库的Full备份.备份片中包括控制文件.注意上面输出内容的黑体部分.我们在后面的恢复操作中会用到.
模拟错误,关掉实例,删掉所有的控制文件和所有的.DBF文件。然后starup会看到如下的出错信息:
SQL> startup
ORACLE instance started.
Total System Global Area 152115804 bytes
Fixed Size 453212 bytes
Variable Size 100663296 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes
ORA-00205: error in identifying controlfile, check alert log for more info
查看alert Log,应该是系统找不到控制文件.现在情形和客户问题一致.不过在继续讲述之前,我们还需要介绍一点背景知识.
我们首先尝试恢复控制文件:
-----磁盘--------------
SQL>startup force nomount;
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreControlfileTo(cfname=>'d:/oracle/Control01.ctl');
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'D:/KDE/DEMO_01FR79OT_1_1.DBF', params=>null);
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /
PL/SQL procedure successfully completed.
OK,控制文件恢复完成.对以上内容的解释:
* 第五行 分配一个device channel,因为使用的操作系统文件,所以这里为空,如果是从磁带上恢复要用 "sbt_tape";
* 第六行 指明开始restore ;
* 第七行 指出待恢复文件目标存储位置;
* 第八行 从哪个备份片中恢复;
* 第九行 释放设备通道.
不妨对以上操作的结果验证一下:
SQL> host dir d:/oracle
Volume in drive D is DATA
Volume Serial Number is DC79-57F8
Directory of d:/oracle
07/18/2004 09:08 PM <DIR> .
07/18/2004 09:08 PM <DIR> ..
06/08/2004 03:21 PM <DIR> admin
07/18/2004 09:08 PM 1,871,872 CONTROL01.CTL
07/16/2004 11:27 AM <DIR> ORA92
07/18/2004 09:02 PM <DIR> oradata
这样,我们成功的restore了控制文件 .如果控制文件在Full备份之后单独做的,接下来关掉实例,拷贝控制文件到具体位置,然后rman 执行restore database;即可。 可是,我们这里的情况有些不同. 视丢失文件的情况而定,继续进行如下的恢复操作:
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'
d:/oracle/oradata/demo/SYSTEM01.DBF');
8 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'
d:/oracle/oradata/demo/UNDOTBS01.DBF');
9 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'
d:/oracle/oradata/demo/DRSYS01.DBF');
10 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'
d:/oracle/oradata/demo/EXAMPLE01.DBF');
11 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'
d:/oracle/oradata/demo/INDX01.DBF');
12 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>06,toname=>'
d:/oracle/oradata/demo/ODM01.DBF');
13 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>07,toname=>'
d:/oracle/oradata/demo/TOOLS01.DBF');
14 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>08,toname=>'
d:/oracle/oradata/demo/USERS01.DBF');
15 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>09,toname=>'
d:/oracle/oradata/demo/XDB01.DBF');
16 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'
D:/KDE/DEMO_01FR79OT_1_1.DBF', params=>null);
17 sys.dbms_backup_restore.deviceDeallocate;
18 END;
19 /
PL/SQL procedure successfully completed.
--我们的情形是所有的数据文件都丢失了,那就如法炮制 ........... --文件对应编号来自前面全备份时候的屏幕输出内容.所以,在备份的时候保留操作Log是个很好的习惯.
SQL> startup force mount;
ORACLE instance started.
Total System Global Area 152115804 bytes
Fixed Size 453212 bytes
Variable Size 100663296 bytes
Database Buffers 50331648 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> Recover database using backup controlfile until cancel ;
ORA-00279: change 243854 generated at 07/18/2004 20:57:03 needed for thread 1
ORA-00289: suggestion : D:/KDE/ARC00002.001
ORA-00280: change 243854 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
D:/KDE/ARC00002.001
ORA-00279: change 244089 generated at 07/18/2004 20:58:18 needed for thread 1
ORA-00289: suggestion : D:/KDE/ARC00003.001
ORA-00280: change 244089 for thread 1 is in sequence #3
ORA-00278: log file 'D:/KDE/ARC00002.001' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
最后,不得不resetlogs .
然后,打扫战场,马上进行数据库的全备份。如果您是DBA的话,应该进一步制定并完善备份计划.亡羊补牢,为时未晚。
总结一下
* 控制文件在备份中意义重大,建议每次对其单独备份,如果数据库版本允许的话,应该设置为控制文件自动备 份。同时应该尽可能地增大CONTROL_FILE_RECORD_KEEP_TIME这个初始化参数的值。以便备份信息能更长时间的保留
* 应该制定比较完善的备份计划,否则备份计划一旦出现缺口,将可能给系统带来灾难.记住, "可能出错的地方一定会出错".
* 熟悉RMAN内部备份机制,对DBMS_BACKUP_RESTORE的用法有一定的掌握在关键时侯很有帮助.
* 备份脚本应该对Log重定向并保存.以便在出错的查找有用信息.
参考信息
RMAN Recovery Without Recovery Catalog or Controlfiles by Bonnie Bizzaro
dbmsbkrs.sql 和 prvtbkrs.plb 文件说明注释(可在你的系统 $ORACLE_HOME/rdbms/admin/中找到.)
-----------------------------------
rman备份, 控制文件丢失的解决办法