RMAN 备份数据库到DISK后进行数据恢复
RMAN 备份数据库到DISK，然后进行数据恢复
一、rman备份
1. 全备脚本
vi bakup_level0.sql
connect target / run { allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; backup incremental level=0 filesperset 10 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_profile export NLS_DATE_FORMAT='yyyy-mm-dd HH24:MI:SS' if [[ "$#" = "1" ]]; then SCRIPT_NAME=$1 else echo "usage: $0 filename... " fi # Define the work path APP_PATH=/home/oracle/scripts LOGPATH=${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 backup incremental level=1 cumulative backup incremental level=1 filesperset 10 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.sql connect 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=8192 *.db_domain='' *.db_files=1024 *.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=1000 *.parallel_force_local=TRUE *.parallel_max_servers=12 *.parallel_threads_per_cpu=1 *.pga_aggregate_target=3848273920 *.processes=3000 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=38482739200 *.undo_tablespace='UNDOTBS1' # 创建目录 export ORACLE_SID=orcl mkdir -p /oracle/app/oracle/admin/${ORACLE_SID}/{adump,pfile,dpdump}
2.2）目标端创建密码文件
2.3）目标端启动nomount状态并创建spfile
sqlplus / as sysdba startup nomount pfile='/home/oracle/pfile.ora'; create spfile from pfile='/home/oracle/pfile.ora';
--使用spfile启动数据库
sqlplus / as sysdba <<EOF
shutdown 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; set newname for datafile 1 to '/oradata1/orcl/system01.dbf'; set newname for datafile 2 to '/oradata1/orcl/sysaux01.dbf'; set newname for datafile 3 to '/oradata1/orcl/undotbs01.dbf'; set newname for datafile 4 to '/oradata1/orcl/users01.dbf'; set newname for datafile 5 to '/oradata1/orcl/orcltbs01.dbf'; set newname for datafile 6 to '/oradata1/orcl/orcltbs02.dbf'; set newname for datafile 7 to '/oradata1/orcl/orcltbs03.dbf'; set newname for datafile 8 to '/oradata1/orcl/orcltbs04.dbf'; set newname for datafile 9 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'; restore archivelog low sequence 363; 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;
