1、Oracle DB RMAN备份与恢复MA0I2实例分析,Lucky Huang MA0I2 2011年9月/22日,Agenda,备份,恢复,RMAN备份,恢复准备,RMAN备份,建议及改善方案,Recovery Manager（RMAN）是一种用于备份(backup)、还原(restore) 和恢复(recover) 数据库的 Oracle 工具。RMAN只能用于ORACLE8或更高 的版本中。它能够备份整个数据 库或数据库部件，如表空间、数据文件、 控制文件、归档文件以及Spfile参数文件。RMAN 也允许您进行增量数据块 级别的备份，增量RMAN备份是时间和空间有效的，因为他们只备
2、份自上次 备份以来有变化的那些数据块。而且，通过RMAN提供的接口，第三方的 备份与恢 复软件如veritas将提供更强大的备份与恢复的管理功能。,具体定义和介绍见：http:/ of TERM has been set to “xterm“. WARNING: YOU ARE SUPERUSER !hpbach1root/# rman Recovery Manager: Release 9.2.0.8.0 - 64bit Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. RMAN exi
3、t,备份,DB资料,备份,Step1:编辑以下备份脚本分别备份 DATABASE 、 ARCHIVELOG 、 CONTROLFILE 保存在/rman目录下面 -more rman_db_bachos.sh #!/bin/ksh #echo “RMAN BackUp start at :date“ $ORACLE_HOME/bin/rman EOF connect target / run allocate channel t1 type disk; BACKUP FILESPERSET 3 DATABASE format /rman/bachos/bachos_FULL_%U.bak; s
4、ql alter system archive log current; BACKUPFILESPERSET 10 ARCHIVELOG ALL format /rman/bachos/bachos_ARC_%U.bak ; BACKUP FORMAT /rman/bachos /bachos_cntrl_%s_%p_%t CURRENT CONTROLFILE; exit; EOF #echo “RMAN BackUp End at :date“,注：“/rman/bachos/”为备份文件地址，请预先Check 空间是否足够,备份,Step2:Run 脚本 (1)切换Unix 账号为 Or
5、acle hpbach1root/rman# su oracle(2)找到脚本目录 roothpbach1:/home/oracle -$ cd /rman/ (3)Run 脚本(sh rman_db_bachos.sh) roothpbach1:/home/oracle -$ llroothpbach1:/home/oracle -$ sh rman_db_bachos.sh,drwxrwxrwx 2 oracle dba 8192 Sep 21 19:34 bachos drwxrwxrwx 2 oracle dba 8192 Aug 14 01:50 bachs drwxrwxrwx 2
6、 oracle dba 96 Aug 2 15:14 lost+found -rwxrwxrwx 1 oracle dba 300 Aug 2 17:14 rman_db_bachos.sh -rwxrwxrwx 1 oracle dba 296 Aug 2 17:13 rman_db_bachs.sh,备份,Step3:开始备份,Starting backup at 2011-09-21 16:33:47 current log archived channel t1: starting archive log backupset channel t1: specifying archive
7、 log(s) in backup set input archive log thread=1 sequence=219879 recid=219955 stamp=762225572 input archive log thread=1 sequence=219880 recid=219956 stamp=762226952 input archive log thread=1 sequence=219881 recid=219957 stamp=762228066 input archive log thread=1 sequence=219882 recid=219958 stamp=
8、762228262 input archive log thread=1 sequence=219883 recid=219959 stamp=762229465 input archive log thread=1 sequence=219884 recid=219960 stamp=762230388 input archive log thread=1 sequence=219885 recid=219961 stamp=762231873 input archive log thread=1 sequence=219886 recid=219962 stamp=762232797 in
9、put archive log thread=1 sequence=219887 recid=219963 stamp=762234290 input archive log thread=1 sequence=219888 recid=219964 stamp=762235064 channel t1: starting piece 1 at 2011-09-21 16:33:50 channel t1: finished piece 1 at 2011-09-21 16:34:26 piece handle=/rman/bachos/bachos_ARC_u1mn477d_1_1.bak
10、comment=NONE Finished backup at 2011-09-21 16:50:24,注：“如果不能正常备份可以检测Archive文件在DB中的完整性，删除的Archive在 DB中的记录也要清除”,备份,Step4:备份成功,roothpbach1:/rman -$ cd bachos roothpbach1:/rman/bachos -$ ll total 273606432 -rw-r- 1 oracleos dba 524225536 Sep 21 16:34 bachos_ARC_u1mn477d_1_1.bak -rw-r- 1 oracleos dba 5242
11、63424 Sep 21 16:34 bachos_ARC_u2mn478i_1_1.bak -rw-r- 1 oracleos dba 524238848 Sep 21 16:35 bachos_ARC_u3mn479m_1_1.bak -rw-r- 1 oracleos dba 524255232 Sep 21 16:36 bachos_ARC_u4mn47aq_1_1.bak -rw-r- 1 oracleos dba 524236800 Sep 21 16:36 bachos_ARC_u5mn47bu_1_1.bak -rw-r- 1 oracleos dba 524256256 Se
12、p 21 16:37 bachos_ARC_u6mn47d2_1_1.bak -rw-r- 1 oracleos dba 524250112 Sep 21 16:38 bachos_ARC_u7mn47eh_1_1.bak -rw-r- 1 oracleos dba 524229632 Sep 21 16:38 bachos_ARC_u8mn47fl_1_1.bak -rw-r- 1 oracleos dba 524234752 Sep 21 16:39 bachos_ARC_u9mn47gp_1_1.bak -rw-r- 1 oracleos dba 524249088 Sep 21 16:
13、40 bachos_ARC_uamn47ht_1_1.bak -rw-r- 1 oracleos dba 524236800 Sep 21 16:40 bachos_ARC_ubmn47j2_1_1.bak -rw-r- 1 oracleos dba 524254208 Sep 21 16:41 bachos_ARC_ucmn47k6_1_1.bak -rw-r- 1 oracleos dba 524257280 Sep 21 16:41 bachos_ARC_udmn47la_1_1.bak -rw-r- 1 oracleos dba 524254208 Sep 21 16:42 bacho
14、s_ARC_uemn47me_1_1.bak -rw-r- 1 oracleos dba 524244992 Sep 21 16:43 bachos_ARC_ufmn47nj_1_1.bak -rw-r- 1 oracleos dba 524261376 Sep 21 16:43 bachos_ARC_ugmn47on_1_1.bak -rw-r- 1 oracleos dba 524249088 Sep 21 16:44 bachos_ARC_uhmn47pr_1_1.bak -rw-r- 1 oracleos dba 461212672 Sep 21 16:44 bachos_ARC_ui
15、mn47qv_1_1.bak ,恢复准备,恢复准备,Step1:找一台恢复机器，上面留有原始DBFile 两倍还大的空间能容得下新的Oracle 安装 文件及新的DB Instance 文件 这边找了一台RX66_TEST机器，从Eva8400划了差不多50GB+300GB的空间 50GB给Oracle ，300GB给数据文件Step2:RX66_TEST上面建立新的Unix用户Oracleos并加入oinstall组和DBA组 如下：,恢复准备,Step3:建立存放数据库文件的目录和Oracle的安装目录 这边在根目录下面建立了/oradata/rman/qcos、 /oradata/qco
16、s、 /oradata/archqcos 与Oracle9i的目录.目录加入oinstall组 $ Chown -R oracle:oinstall /oradata/rman/qcos,注：“为了正常恢复，需要新的主机的上的备份目录和之前的DB上的目录结构一样 如果不一样需要建立一个Link /oradata/rman/qcos - /rman/bachos $ ln -s /oradata/rman/qcos /rman/bachos”,lrwxr-xr-x 1 oracleos oinstall 18 Sep 21 17:29 bachos - /oradata/rman/qcos,恢复
17、准备,Step4:从hpbach1:/rman/bachos Copy 数据到Rx66_TEST: /oradata/rman/qcos下面 这边用Ftp命令Copy 过来,rx66testroot/oradata# ftp -i 10.85.17.230 Connected to 10.85.17.230. 220 hpbach1 FTP server (Version 1.1.214.4(PHNE_27765) Wed Sep 4 05:59:34 GMT 2002) ready. Name (10.85.17.230:root): root 331 Password required f
18、or root. Password: 230 User root logged in. Remote system type is UNIX. Using binary mode to transfer files. ftp binary 200 Type set to I. ftp lcd /oradata/rman/qcos Local directory now /oradata/rman/qcos ftp dir 200 PORT command successful. 150 Opening ASCII mode data connection for /usr/bin/ls.,恢复
19、准备,Step5:Check数据文件是否Copy完整Step6:Unix下面安装新的Oracle程式 （1）用oracleos用户登录找到安装包的路径 /oracle/patch/Disk1# （2）开启Xmanager下面的Passive 工具用Export DisPlay=10.85.30.103:0.0把远端屏幕显示在本地（3）开始安装./runInstaller,$ ./runInstaller $ Initializing Java Virtual Machine from /tmp/OraInstall2011-07-26_02-20-05PM/jre/bin/java. Plea
20、se wait.,恢复准备,Step6:Unix下面安装新的Oracle程式 （3）开始安装./runInstaller,Welcome,注：hp unix下存放oraInst.loc的路径：用来存放oraInventory路径的文件 存放在：/var/opt/oracle,恢复准备,Step6:Unix下面安装新的Oracle程式 （3）开始安装,File Location,这边主要是选择Oracle 安装路径 /Oracle9i/OraHome,恢复准备,Step6:Unix下面安装新的Oracle程式 （3）开始安装,Product Choose,选择需要安装的产品,恢复准备,Step6
21、:Unix下面安装新的Oracle程式 （3）开始安装,Installion Types,选择需要安装的产品Version,恢复准备,Step6:Unix下面安装新的Oracle程式 （3）开始安装,Database Configuration,选择需要安装的产品的功能,恢复准备,Step6:Unix下面安装新的Oracle程式 （3）开始安装,Choose Java,选择安装的驱动Java包,恢复准备,Step6:Unix下面安装新的Oracle程式 （3）开始安装,Summary,预览需要安装的一些程式,恢复准备,Step6:Unix下面安装新的Oracle程式 （3）开始安装,Insta
22、ll,安装,恢复准备,Step6:Unix下面安装新的Oracle程式 （3）开始安装,Install,这个画面需要运行一个shell 改变权限,恢复准备,Step6:Unix下面安装新的Oracle程式 （3）开始安装,Install,这个画面需要运行一个shell 改变权限,到下面目录执行 sh root.sh /oracle9i/OraHome/ sh root.sh,恢复准备,Step6:Unix下面安装新的Oracle程式 （3）开始安装,End Install,结束安装,恢复准备,Step6:Unix下面安装新的Oracle程式 （4）打Oracle补丁 找到补丁目录，和安装Ora
23、cle一样，进入下面界面,Install Patch,安装补丁,恢复准备,Step6:Unix下面安装新的Oracle程式 （4）打Oracle补丁 找到补丁目录，和安装Oracle一样，进入下面界面,Install Patch,安装补丁,恢复准备,Step6:Unix下面安装新的Oracle程式 （5）安装完毕,恢复准备,Step7:查看环境变量,home/oracleos# more .profile,# (#)B11.23_LR # Default user .profile file (/usr/bin/sh initialization). # Set up the terminal
24、:if “$TERM“ = “ theneval tset -s -Q -m :?hp elseeval tset -s -Q fistty erase “H“ kill “U“ intr “C“ eof “D“stty hupcl ixon ixofftabs # Set up the search paths:PATH=$PATH:. # Set up the shell environment:set -utrap “echo logout“ 0 # Set up the shell variables:EDITOR=viexport EDITOR #set ulimitulimit -
25、n 1000 umask 022 # Setup Oracle 9.2.0.8 environment: export ORACLE_BASE=/oracle/OraHome9i export ORACLE_SID=bachos export ORACLE_HOME=/oracle/OraHome9i export NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS,恢复准备,Step8:设定新的环境变量,设置ORACLE_SID=qcosexport ORACLE_SID=qcos确认文件路径数据文件路径名：/oracledata/bachos/ = /oradata
26、/qcos/ Online redo log路径名：/oracledata/bachos/ /oracle/orafile_backup/bachos/ = /oradata/qcos/控制文件路径名：/oracledata/bachos/ = /oradata/qcos/归档路径名：/archfile/bachos =/oradata/archqcosDump路径名：/oracle/admin/bachos/ =/oracle/OraHome9i/admin/qcos备份文件路径：/rman/bachos/,恢复准备,Step9:建立新的初始化参数,可以从之前的DB上Copy一个过来修改文件
27、名 cd $ORACLE_HOME/dbs 这里从/oracle/OraHome9i/dbs/ Copy到/oracle9i/OraHome/dbs/下面,需要修改的参数 instance_name=qcos services_name=qcos control_files = (“/oradata/qcos/control01.ctl“, “/oradata/qcos/control02.ctl“) background_dump_dest = /oracle9i/OraHome/admin/qcos/bdump core_dump_dest = /oracle9i/OraHome/admi
28、n/qcos/cdump user_dump_dest = /oracle9i/OraHome/admin/qcos/udump log_archive_dest_1= /oradata/archqcos,恢复准备,Step10:准备工作结束,准备工作结束,恢复,恢复,Step1:创建恢复脚本,restore controlfile from /rman/bachos/bachos_cntrl_210910_1_762464072;,（1）创建Controlfile 恢复脚本,run allocate channel c1 device type disk; allocate channel
29、c2 device type disk; set newname for datafile 1 to /oradata/qcos/system01.dbf; set newname for datafile 2 to /oradata/qcos/tools01.dbf; set newname for datafile 3 to /oradata/qcos/rbs01.dbf; set newname for datafile 4 to /oradata/qcos/temp01.dbf; set newname for datafile 5 to /oradata/qcos/users01.d
30、bf; set newname for datafile 107 to /oradata/qcos/bqos51.dbf; set newname for datafile 108 to /oradata/qcos/indx44.dbf; set newname for datafile 109 to /oradata/qcos/bqos52.dbf; restore database; switch datafile all; release channel c1; release channel c2;,（2）创建Datafile 恢复脚本（Restore）,恢复,Step1:创建恢复脚本
31、,run allocate channel c1 device type disk; allocate channel c2 device type disk; set until time “to_date(2011/09/21 08:30:00,yyyy/mm/dd hh24:mi:ss)” ; recover database; release channel c1; release channel c2; ,（3）创建Datafile 恢复脚本（Recover）,恢复,Step2:恢复还原,（1） 创建spfile,数据启动的nomount状态$export ORACLE_SID=qc
32、os$sqlplus / nologSqlconn / as sysdbaSqlstartup nomout Sqlcreate spfile from pfile; Sqlshutdown immediate; Sqlstartup nomout Exit （2）还原控制文件 $export ORACLE_SID=qcos cd $ORACLE_HOME/bin/ $rman target / Rman restore controlfile from /rman/bachos/bachos_cntrl_210910_1_762464072; （3） 数据库启动到mount状态和创建密码文件
33、 rmanalter database mount rmanexit $cd $ORACLE_HOME/dbs $orapwd file=orapwqcos password=oracle entries=10; d. restore datafile $export ORACLE_SID=qcos,恢复,Step2:恢复还原,（4） restore datafile $export ORACLE_SID=qcos $rman target / rman run allocate channel c1 device type disk; allocate channel c2 device t
34、ype disk; set newname for datafile 1 to /oradata/qcos/system01.dbf; set newname for datafile 2 to /oradata/qcos/tools01.dbf; set newname for datafile 3 to /oradata/qcos/rbs01.dbf; （5）rescover datafile rman run allocate channel c1 device type disk; allocate channel c2 device type disk; set until time
35、 “to_date(2011/09/21 08:30:00,yyyy/mm/dd hh24:mi:ss)” ; recover database; release channel c1; release channel c2; ,恢复,Step2:恢复还原,（6） 修改redo file路径 alter database rename file /oracledata/bachos/redo01.log to /oradata/qcos/redo01.log; alter database rename file /oracledata/bachos/redo02.log to /oradat
36、a/qcos/redo02.log; alter database rename file /oracledata/bachos/redo03.log to /oradata/qcos/redo03.log; （7）打开数据库 sqlalter database open resetlogs;（8）修改db_name nid 数据启动到mount状态 sqlshutdown immediate; sqlstartup mount sqlnid target=sys/oracle dbname=qcos,恢复,Step2:恢复还原,(9)修改初始化参数 db_name=qcos sqlshutd
37、own immediate; sqlhost rm r spfileqcos.ora sqlstartup nomount sqlcreate spfile from pfile; sqlshutdowm immediate; sqlstart mount (10)重新建立pwd文件 Orapwd file=orapwqcos password=oracle entries=10;Sql alter database open resetlogs;(11)Shutdown数据库,恢复,Step2:恢复还原,(12)修改listener &TNS 修改listener SID_LIST_LIST
38、ENER =(SID_LIST =(SID_DESC =(SID_NAME = PLSExtProc)(ORACLE_HOME = /oracle/OraHome9i)(PROGRAM = extproc)(SID_DESC =(GLOBAL_DBNAME = bachos)(ORACLE_HOME = /oracle/OraHome9i)(SID_NAME = bachos)(SID_DESC =(GLOBAL_DBNAME = qcos)(ORACLE_HOME = /oracle9i/OraHome)(SID_NAME = qcos),恢复,Step2:恢复还原,(12)修改listener &TNS 修改TNS QCOS_TEST =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.85.17.60)(PORT = 1521)(CONNECT_DATA =(server=dedicated)(service_name = qcos) (13)Check Listener(14)Start Up,
