oracle11g rman备份与恢复详细实例

登录
sqlplus sys/oracle as sysdba

查询归档模式
archvie log list

修改归档模式
shutdown immediate
startup mount
alter database archivelog
alter database open
archive log list;或者select log_mode from v$database;

登陆raman 恢复管理器
rman target /

orcl dbid=1313107278
备份
run
{
sql 'alter system archive log current'; --强制归档
backup database format 'd:\backup\dbfull_%d_%s_%U.bak' ;--备份全库
backup current controlfile format 'd:\backup\ctl_%d_%U.bak';--备份控制文件
backup archivelog all format 'd:\backup\arc_%U.bak';--备份归档日志
}



恢复

1 sqlplus sys/oracle as sysdba
2 shutdown immediate;

3 删除所有的数据文件

4 rman target /


5 startup nomount

set dbid=1377317512
6 exit
--7 rman target /
8
restore controlfile from 'd:\rman\CTL_ORCL_20141031_746';

9
alter database mount;
///sql 'alter database mount';

10 如果安装路径不一样则跳过10去实行20、21 步 再执行11步后面的
restore database;


11 exit

12 复制归档日志到 D:\oracle\product\10.2.0\db_1\flash_recovery_area\ORCL\ARCHIVELOG


13 sqlplus / as sysdba
-- sconnect / as sysdba
14 recover database using backup controlfile until cancel;
15
使用resetlogs方式打开数据库：
SQL> alter database open resetlogs;

如果出现 '没有过旧的备份中还原'之类错误 跳回14步 操作后 ，用在线日志恢复， 指定文件 'E:\rman\REDO03.LOG'

16、如果导航台登录不行添加临时表空间
alter tablespace temp add tempfile ' D:\oracle\product\10.2.0\oradata\orcl\TEMP02.DBF' size 40m ;

select dest_name,destination,status from v$archive_dest;



select 'set newname for datafile '||chr(39)||to_char(name)||chr(39)||' to '||Chr(39)||'D:\app\Administrator\oradata\orcl\'||substr(name,instr(Name,'\',-1)+1)||chr(39)||';' from v$datafile;


select 'alter database rename file '||chr(39)||name||chr(39)||' to '||Chr(39)||'D:\app\Administrator\oradata\orcl\'||substr(name,instr(Name,'\',-1)+1)||chr(39)||';' aa from v$tempfile
Union All
select 'alter database rename file '||chr(39)||MEMBER||chr(39)||' to '||Chr(39)||'D:\app\Administrator\oradata\orcl\'||substr(MEMBER,instr(MEMBER,'\',-1)+1)||chr(39)||';' aa from v$Logfile


20、


run{
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF' to 'D:\app\Administrator\oradata\orcl\SYSTEM01.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF' to 'D:\app\Administrator\oradata\orcl\SYSAUX01.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF' to 'D:\app\Administrator\oradata\orcl\UNDOTBS01.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\USERS01.DBF' to 'D:\app\Administrator\oradata\orcl\USERS01.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF' to 'D:\app\Administrator\oradata\orcl\EXAMPLE01.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZLTOOLSTBS.DBF' to

'D:\app\Administrator\oradata\orcl\ZLTOOLSTBS.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9BASEITEM.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9BASEITEM.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9PATIENT.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9PATIENT.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9EXPENSE.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9EXPENSE.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9MEDLST.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9MEDLST.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9DUEREC.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9DUEREC.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9CISREC.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9CISREC.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9EPRLOB.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9EPRLOB.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9EPRDAT.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9EPRDAT.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9HISTORY.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9HISTORY.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9INDEXHIS.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9INDEXHIS.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9INDEXCIS.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9INDEXCIS.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9INDEXHISTORY.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9INDEXHISTORY.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9MEDBASE.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9MEDBASE.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9MEDREC.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9MEDREC.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9MEDDAY.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9MEDDAY.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9INDEXMDR.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9INDEXMDR.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZLBAKZLHIS.DBF' to 'D:\app\Administrator\oradata\orcl\ZLBAKZLHIS.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9PEISDATA.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9PEISDATA.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9DEVBASE.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9DEVBASE.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9DEVREC.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9DEVREC.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9DEVUSE.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9DEVUSE.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9INDEXDEV.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9INDEXDEV.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9MTLBASE.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9MTLBASE.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9MTLREC.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9MTLREC.DBF';
set newname for d

atafile 'E:\ORACLE\ORADATA\ORCL\ZL9INDEXMTL.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9INDEXMTL.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZLBAK2011.DBF' to 'D:\app\Administrator\oradata\orcl\ZLBAK2011.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZLMBR.DBF' to 'D:\app\Administrator\oradata\orcl\ZLMBR.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9CISAUDIT.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9CISAUDIT.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9OPSDATA.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9OPSDATA.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9BLOODDATA.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9BLOODDATA.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9LISDATA.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9LISDATA.DBF';
set newname for datafile 'E:\ORACLE\ORADATA\ORCL\ZL9INDEXLIS.DBF' to 'D:\app\Administrator\oradata\orcl\ZL9INDEXLIS.DBF';
restore database;
switch datafile all;
}
21、在sqlplus中修改联机日志的路径和名称及将以源库的路径修改为当前路径
，以免在后面的open resetlogs报错：具体命令如下：
sqlplus sys/oracle as sysdba

alter database rename file 'E:\ORACLE\ORADATA\ORCL\TEMP01.DBF' to 'D:\app\Administrator\oradata\orcl\TEMP01.DBF';
alter database rename file 'E:\ORACLE\ORADATA\ORCL\ZLTOOLSTMP.DBF' to 'D:\app\Administrator\oradata\orcl\ZLTOOLSTMP.DBF';
alter database rename file 'E:\ORACLE\ORADATA\ORCL\REDO03.LOG' to 'D:\app\Administrator\oradata\orcl\REDO03.LOG';
alter database rename file 'E:\ORACLE\ORADATA\ORCL\REDO02.LOG' to 'D:\app\Administrator\oradata\orcl\REDO02.LOG';
alter database rename file 'E:\ORACLE\ORADATA\ORCL\REDO01.LOG' to 'D:\app\Administrator\oradata\orcl\REDO01.LOG';

exit

rman target /

RMAN> recover database;