RMAN 备份数据库到DISK,然后进行数据恢复
一、rman备份
1. 全备脚本
vi bakup_level0.sqlconnect target /run { allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; backup incremental filesperset format "/backup/orcl/lev0_%d_%s_%I_%T_%t.bak" database; release channel c1; release channel c2; release channel c3; allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; sql "alter system archive logcurrent"; backup format '/backup/orcl/arch_%T_%t_%U' archivelog all delete all input; release channel c1; release channel c2; release channel c3; allocate channel c1 type disk; backup format "/backup/orcl/spfile_%d_%s_%I_%T_%t.bak" spfile; release channel c1; allocate channel c1 type disk; backup format "/backup/orcl/controlfile_%d_%s_%I_%T_%t.bak" (current controlfile); release channel c1;}
— 执行调用rman脚本(start.sh)
#!/bin/sh# exec: sh start.sh# reload oracle env# reload profile[ -f ${HOME}/.bash_profile ] && . ${HOME}/.bash_profileexport NLS_DATE_FORMAT='yyyy-mm-dd HH24:MI:SS'" ]]; then SCRIPT_NAME=$else echo "usage: $0 filename... "fi# Define the work pathAPP_PATH=/home/oracle/scriptsLOGPATH=${APP_PATH}/logs[[ -d "${APP_PATH}" ]] || mkdir -p ${APP_PATH}[[ -d "${LOGPATH}" ]] || mkdir -p ${LOGPATH}SCRIPT_FILE=${APP_PATH}/${SCRIPT_NAME}LOGFILE=${LOGPATH}/bklevel0_$(date +%Y%m%d%H%M%S).log# execute scripts[[ -f "${SCRIPT_FILE}" ]] && ${ORACLE_HOME}/bin/rman cmdfile ${SCRIPT_FILE} msglog ${LOGFILE}
2. 增备(level 1)脚本
connect target /run { allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; # REM cumulative backup incremental filesperset format "/backup/orcl/lev1_%d_%s_%I_%T_%t.bak" database; release channel c1; release channel c2; release channel c3; allocate channel c1 type disk; allocate channel c2 type disk; sql "alter system archive log current"; backup format '/backup/orcl/arhc_%T_%t_%U' archivelog all delete all input; release channel c1; release channel c2; allocate channel c1 type disk; backup format "/backup/orcl/spfile_%d_%s_%I_%T_%t.bak" spfile; release channel c1; allocate channel c1 type disk; backup format "/backup/orcl/controlfile_%d_%s_%I_%T_%t.bak" (current controlfile); release channel c1; }
2.2 执行增备脚本
sh start.sh bakup_level1.sh
3. 备份归档日志
-- vi bakup_arch.sqlconnect target /run { allocate channel c1 type disk; allocate channel c2 type disk; sql "alter system archive log current"; backup format '/backup/orcl/arhc_%T_%t_%U' archivelog all delete all input; release channel c1; release channel c2; allocate channel c1 type disk; backup format "/backup/orcl/spfile_%d_%s_%I_%T_%t.bak" spfile; release channel c1; allocate channel c1 type disk; backup format "/backup/orcl/controlfile_%d_%s_%I_%T_%t.bak" (current controlfile); release channel c1;}
4. 维护rman备份集
connect target /run{ allocate channel for maintenance type disk; report obsolete; delete noprompt obsolete; release channel;}
二、利用rman备份集恢复
1. 恢复步骤
1)安装oracle db软件,建议与源库版本一致;
2)目标端创建pfile,即相关目录(adump);
3)目标端创建密码文件
4)目标端启动nomount状态并创建spfile;
5)rman 恢复控制文件
6)还原数据文件
7)同步归档备份集到目标端并还原归档日志到目标端数据库
8)resetlog打开数据库
9)创建临时表空间
2. 具体操作代码
2.1)目标端创建pfile,即相关目录(adump)
# 创建pfile文件vi pfile.ora*.audit_file_dest='/oracle/app/oracle/admin/orcl/adump'*.audit_sys_operations=TRUE*.audit_trail='DB'*.compatible='11.2.0.4.0'*.control_files='/oradata1/orcl/control01.ctl'*.db_block_size=*.db_domain=''*.db_files=*.db_name='orcl'*.diagnostic_dest='/oracle/app/oracle'*.dispatchers=''orcl.log_archive_dest_1='location=/arch1'orcl.log_archive_format='orcl_%t_%s_%r.arc'*.max_dump_file_size='1024M'*.O7_DICTIONARY_ACCESSIBILITY=FALSE*.open_cursors=*.parallel_force_local=TRUE*.parallel_max_servers=*.parallel_threads_per_cpu=*.pga_aggregate_target=*.processes=*.remote_login_passwordfile='EXCLUSIVE'*.sga_target=*.undo_tablespace='UNDOTBS1'# 创建目录export ORACLE_SID=orclmkdir -p /oracle/app/oracle/admin/${ORACLE_SID}/{adump,pfile,dpdump}
2.2)目标端创建密码文件
2.3)目标端启动nomount状态并创建spfile
sqlplus / as sysdbastartup nomount pfile='/home/oracle/pfile.ora';create spfile from pfile='/home/oracle/pfile.ora';--使用spfile启动数据库
sqlplus / as sysdba <<EOFshutdown immediate;startup nomount;quit;EOF
2.4)rman 恢复控制文件
rman target /
set dbid=<database id>;
run{ restore controlfile from '/backup/orcl/controlfile_orcl_292_1223534412_20170418_941608807.bak'; # 启动到mount sql 'alter database mount';}
2.5)还原数据文件
connect target /
set dbid=<database id>;
run{allocate channel c1 type disk;allocate channel c2 type disk;allocate channel c3 type disk; to '/oradata1/orcl/system01.dbf'; to '/oradata1/orcl/sysaux01.dbf'; to '/oradata1/orcl/undotbs01.dbf'; to '/oradata1/orcl/users01.dbf'; to '/oradata1/orcl/orcltbs01.dbf'; to '/oradata1/orcl/orcltbs02.dbf'; to '/oradata1/orcl/orcltbs03.dbf'; to '/oradata1/orcl/orcltbs04.dbf'; to '/oradata1/orcl/orcltbs05.dbf';restore database;switch datafile all;recover database;release channel c1;release channel c2;release channel c3;}
2.6)同步归档备份集到目标端并还原归档日志到目标端数据库
rman target /
set dbid=<database id>;
run{ allocate channel c1 type disk; allocate channel c2 type disk; set archivelog destination to '/backup/orcl/archivelog'; ; release channel c1; release channel c2;}
2.7)resetlog打开数据库
sqlplus "/ as sysdba" <<EOF# reset redo log
alter database rename file ‘/oraredo1/redo01_1.log’ to ‘/oradata1/orcl/redo01_1.rd’;
alter database rename file ‘/oraredo2/redo01_2.log’ to ‘/oradata1/orcl/redo01_2.rd’;
alter database rename file ‘/oraredo1/redo02_1.log’ to ‘/oradata1/orcl/redo02_1.rd’;
alter database rename file ‘/oraredo2/redo02_2.log’ to ‘/oradata1/orcl/redo02_2.rd’;
alter database open resetlogs;quit;EOF
2.8)创建临时表空间
select tablespace_name from dba_tablespaces where contents='TEMPORARY';create temporary tablespace TEMP1 tempfile '/oradata1/orcl/temp01.dbf' size 10240M reuse;alter database default temporary tablespace TEMP1;drop tablespace TEMP including contents and datafiles;