用友网络股份有限公司-支持服务事业部技术案例--《DataGuard灾备安装部署报告》建立日期：2019-01-20文档属性：对内/客户文档创建：王安宁1/20文档控制创建记录适用范围审阅人日期审阅签字所属部门发布范围发布人姓名所属部门发布时间2/20日期作者所属部门邮件地址版本2019-01-20王安宁技术支持部wangann@yonyou.com版本领域备注内部员工伙伴客户✓✓✓目录一、系统环境.................................................................................................4二、背景介绍.................................................................................................4三、DG搭建过程概要.....................................................................................5四、DG搭建详细步骤.....................................................................................5五、附录.......................................................................................................19六、结果验证...............................................................................................20七、注意事项...............................................................................................203/20一、系统环境NC版本NC65正式环境数据库ORACLE11gR2正式环境数据库节点数2节点RAC操作系统RedHat6物理IP192.169.109.110（节点1）192.169.109.111（节点2）灾备环境数据库ORACLE11gR2灾备环境数据库节点数单机操作系统RedHat6物理IP192.169.109.115二、背景介绍xx集团数据库正式环境为RAC，有2套数据库。由于集团对数据库灾备要求，故搭建2套rac+单机的DG，作为灾备使用。4/20DataGuard灾备端搭建为物理备库，数据库状态为openwithreadonly，可以对外提供报表查询，以减少RAC主库端的压力。三、DG搭建过程概要1、主库操作：开启forcelogging2、主库操作：在主库添加备库的standbylog3、主库操作：修改主库的参数文件pfile.ora4、主库操作：修改主库的$ORACLE_HOME/network/admin/tnsnames.ora文件5、备库操作：修改备库的$ORACLE_HOME/network/admin/tnsnames.ora文件6、备库操作：拷贝主库的参数文件pfile_rac1.ora到备库7、主库操作：从主库，拷贝密码口令文件到备库8、备库操作：备库配置监听$ORACLE_HOME/network/admin/listener.ora9、备库操作：备库上，启动到nomount状态10、主库操作：在主库上，使用duplicate方式恢复备库11、备库操作：恢复完成后，备库开启日志应用12、备库操作：查看状态13、备库操作：备库起到open四、DG搭建详细步骤1.主库操作：开启forceloggingALTERDATABASEFORCELOGGING;2.主库操作：在主库添加备库的standbylog注意：1>rac有2个节点thread1、thread2，故添加standbylog时，需要区分thread1、thread22>standbylog需要比主库日志多至少1个1）查看当前rac的redo情况selectgroup#,thread#,bytes/1024/1024Mfromv$log;2）查看rac的redo日志组的位置selectmemberfromv$logfile;5/203）添加standblog日志（thread1和thread2）3.主库操作：修改主库的参数文件pfile.ora1）首先从主库创建pfile.ora（注意：2个节点分别做）createpfile='/tmp/pfile_rac1.ora'fromspfile;createpfile='/tmp/pfile_rac2.ora'fromspfile;2）在每个节点进行如下的修改6/203）rac的2个节点，分别重启生成spfile1>节点1sqlplus/assysdbacreatespfilefrompfile='/tmp/pfile_rac1.ora’;shutdownimmediatestartup2>节点2sqlplus/assysdbacreatespfilefrompfile='/tmp/pfile_rac2.ora’;shutdownimmediate7/20startup4.主库操作：修改主库的$ORACLE_HOME/network/admin/tnsnames.ora文件注意：1）只需要在主库rac的1个节点修改即可。2）主要作用：rmanduplicate备份恢复时，使用3）主库只需要配置NC1，不需要配置NC，ip为节点1的物理ip。原因为：rmantargetsys/oracle@NC1auxiliarysys/oracle@NCDG中，如果配置为NC，scan-ip会自动选择节点1或者节点2。会影响后边从主库rac拷贝口令文件到备库，恢复时会报错。NC1=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.169.109.110)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=NC)))NC=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac-scan)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=NC)))NCDG=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.169.109.115)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=NCDG)))8/205.备库操作：修改备库的$ORACLE_HOME/network/admin/tnsnames.ora文件NC=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.10.114)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=NC)))NCDG=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.10.115)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=NCDG)))6.备库操作：拷贝主库的参数文件pfile_rac1.ora到备库。删除群集相关参数，remote_listener参数是为VIP用的可以删除，另外注意要添加一个*.undo_tablespace参数9/207.主库操作：从主库，拷贝密码口令文件到备库(节点1上192.169.10.110执行)scp/u01/app/oracle/product/11.2.0/db_1/dbs/orapwNC1oracle@192.169.10.115:/u01/app/oracle/product/11.2.0.4/db_1/dbs/orapwNCDG8.备库操作：备库配置监听$ORACLE_HOME/network/admin/listener.ora[oracle@racdgadmin]$catlistener.oraSID_LIST_LISTENER=(SID_LIST=(SID_DESC=(GLOBAL_DBNAME=XGLDG)(ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)(SID_NAME=XGLDG)10/20)(SID_DESC=(GLOBAL_DBNAME=NCDG)(ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)(SID_NAME=NCDG))(SID_DESC=(SID_NAME=plsextproc)(ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)(PROGRAM=extproc)))LISTENER=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.169.10.115)(PORT=1521))(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))))9.备库操作：备库上，启动到nomount状态exportORACLE_SID=NCDGsqlplus/assysdbastartupnomountpfile='/tmp/pfile_rac1.ora';createspfilefrompfile='/tmp/pfile_rac1.ora';startupnomountforce;10.主库操作：在主库上，使用duplicate方式恢复备库exportORACLE_SID=NCrmantargetsys/oracle@NC1auxiliarysys/oracle@NCDGduplicatetargetdatabaseforstandbyfromactivedatabase;11/20恢复过程日志如下：[oracle@rac1admin]$rmantargetsys/oracle@NC1auxiliarysys/oracle@NCDGRecoveryManager:Release11.2.0.4.0-ProductiononThuJan1713:53:512019Copyright(c)1982,2011,Oracleand/oritsaffiliates.Allrightsreserved.connectedtotargetdatabase:NC(DBID=2524224295)connectedtoauxiliarydatabase:NC(notmounted)RMAN>duplicatetargetdatabaseforstandbyfromactivedatabase;StartingDuplicateDbat17-JAN-19usingtargetdatabasecontrolfileinsteadofrecoverycatalogallocatedchannel:ORA_AUX_DISK_1channelORA_AUX_DISK_1:SID=2376devicetype=DISKcontentsofMemoryScript:{backupascopyreusetargetfile'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwNC1'auxiliaryformat'/u01/app/oracle/product/11.2.0.4/db_1/dbs/orapwNCDG';}executingMemoryScriptStartingbackupat17-JAN-19allocatedchannel:ORA_DISK_1channelORA_DISK_1:SID=1430instance=NC1devicetype=DISKFinishedbackupat17-JAN-19contentsofMemoryScript:{backupascopycurrentcontrolfileforstandbyauxiliaryformat'/ncdata/control01.ctl';}executingMemoryScript12/20Startingbackupat17-JAN-19usingchannelORA_DISK_1channelORA_DISK_1:startingdatafilecopycopyingstandbycontrolfileoutputfilename=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_NC1.ftag=TAG20190117T135407RECID=1STAMP=997797248channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:00:01Finishedbackupat17-JAN-19contentsofMemoryScript:{sqlclone'alterdatabasemountstandbydatabase';}executingMemoryScriptsqlstatement:alterdatabasemountstandbydatabasecontentsofMemoryScript:{setnewnamefortempfile1to"/ncdata/temp.260.990886895";switchclonetempfileall;setnewnamefordatafile1to"/ncdata/system.256.990886809";setnewnamefordatafile2to"/ncdata/sysaux.257.990886809";setnewnamefordatafile3to"/ncdata/undotbs1.258.990886809";setnewnamefordatafile4to"/ncdata/users.259.990886811";setnewnamefordatafile5to"/ncdata/undotbs2.261.990886951";setnewnamefordatafile6to"/ncdata/nnc_data01.dbf";setnewnamefordatafile7to"/ncdata/nnc_index01.dbf";setnewnamefordatafile8to"/ncdata/nnc_data01a.dbf";setnewnamefordatafile9to"/ncdata/nnc_data01b.dbf";13/20setnewnamefordatafile10to"/ncdata/nnc_data01c.dbf";setnewnamefordatafile11to"/ncdata/nnc_data01d.dbf";setnewnamefordatafile12to"/ncdata/nnc_index01a.dbf";setnewnamefordatafile13to"/ncdata/system.271.990932493";setnewnamefordatafile14to"/ncdata/sysaux.272.990933605";setnewnamefordatafile15to"/ncdata/nnc_index01b.dbf";backupascopyreusedatafile1auxiliaryformat"/ncdata/system.256.990886809"datafile2auxiliaryformat"/ncdata/sysaux.257.990886809"datafile3auxiliaryformat"/ncdata/undotbs1.258.990886809"datafile4auxiliaryformat"/ncdata/users.259.990886811"datafile5auxiliaryformat"/ncdata/undotbs2.261.990886951"datafile6auxiliaryformat"/ncdata/nnc_data01.dbf"datafile7auxiliaryformat"/ncdata/nnc_index01.dbf"datafile8auxiliaryformat"/ncdata/nnc_data01a.dbf"datafile9auxiliaryformat"/ncdata/nnc_data01b.dbf"datafile10auxiliaryformat"/ncdata/nnc_data01c.dbf"datafile11auxiliaryformat"/ncdata/nnc_data01d.dbf"datafile12auxiliaryformat"/ncdata/nnc_index01a.dbf"datafile13auxiliaryformat"/ncdata/system.271.990932493"datafile14auxiliaryformat"/ncdata/sysaux.272.990933605"datafile14/2015auxiliaryformat"/ncdata/nnc_index01b.dbf";sql'altersystemarchivelogcurrent';}executingMemoryScriptexecutingcommand:SETNEWNAMErenamedtempfile1to/ncdata/temp.260.990886895incontrolfileexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEexecutingcommand:SETNEWNAMEStartingbackupat17-JAN-1915/20usingchannelORA_DISK_1channelORA_DISK_1:startingdatafilecopyinputdatafilefilenumber=00006name=+ASMNC/nc/datafile/nnc_data01.dbfoutputfilename=/ncdata/nnc_data01.dbftag=TAG20190117T135418channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:04:45channelORA_DISK_1:startingdatafilecopyinputdatafilefilenumber=00007name=+ASMNC/nc/datafile/nnc_index01.dbfoutputfilename=/ncdata/nnc_index01.dbftag=TAG20190117T135418channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:04:55channelORA_DISK_1:startingdatafilecopyinputdatafilefilenumber=00008name=+ASMNC/nc/datafile/nnc_data01a.dbfoutputfilename=/ncdata/nnc_data01a.dbftag=TAG20190117T135418channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:04:55channelORA_DISK_1:startingdatafilecopyinputdatafilefilenumber=00009name=+ASMNC/nc/datafile/nnc_data01b.dbfoutputfilename=/ncdata/nnc_data01b.dbftag=TAG20190117T135418channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:04:55channelORA_DISK_1:startingdatafilecopyinputdatafilefilenumber=00010name=+ASMNC/nc/datafile/nnc_data01c.dbfoutputfilename=/ncdata/nnc_data01c.dbftag=TAG20190117T135418channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:04:55channelORA_DISK_1:startingdatafilecopyinputdatafilefilenumber=00011name=+ASMNC/nc/datafile/nnc_data01d.dbfoutputfilename=/ncdata/nnc_data01d.dbftag=TAG20190117T135418channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:04:55channelORA_DISK_1:startingdatafilecopyinputdatafilefilenumber=00012name=+ASMNC/nc/datafile/nnc_index01a.dbfoutputfilename=/ncdata/nnc_index01a.dbftag=TAG20190117T135418channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:04:55channelORA_DISK_1:startingdatafilecopyinputdatafilefilenumber=00015name=+ASMNC/nc/datafile/nnc_index01b.dbfoutputfilename=/ncdata/nnc_index01b.dbftag=TAG20190117T135418channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:04:5516/20channelORA_DISK_1:startingdatafilecopyinputdatafilefilenumber=00013name=+ASMNC/nc/datafile/system.271.990932493outputfilename=/ncdata/system.271.990932493tag=TAG20190117T135418channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:01:45channelORA_DISK_1:startingdatafilecopyinputdatafilefilenumber=00014name=+ASMNC/nc/datafile/sysaux.272.990933605outputfilename=/ncdata/sysaux.272.990933605tag=TAG20190117T135418channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:01:55channelORA_DISK_1:startingdatafilecopyinputdatafilefilenumber=00003name=+ASMNC/nc/datafile/undotbs1.258.990886809outputfilename=/ncdata/undotbs1.258.990886809tag=TAG20190117T135418channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:01:15channelORA_DISK_1:startingdatafilecopyinputdatafilefilenumber=00005name=+ASMNC/nc/datafile/undotbs2.261.990886951outputfilename=/ncdata/undotbs2.261.990886951tag=TAG20190117T135418channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:00:55channelORA_DISK_1:startingdatafilecopyinputdatafilefilenumber=00001name=+ASMNC/nc/datafile/system.256.990886809outputfilename=/ncdata/system.256.990886809tag=TAG20190117T135418channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:00:25channelORA_DISK_1:startingdatafilecopyinputdatafilefilenumber=00002name=+ASMNC/nc/datafile/sysaux.257.990886809outputfilename=/ncdata/sysaux.257.990886809tag=TAG20190117T135418channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:00:15channelORA_DISK_1:startingdatafilecopyinputdatafilefilenumber=00004name=+ASMNC/nc/datafile/users.259.990886811outputfilename=/ncdata/users.259.990886811tag=TAG20190117T13541817/20channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:00:01Finishedbackupat17-JAN-19sqlstatement:altersystemarchivelogcurrentcontentsofMemoryScript:{switchclonedatafileall;}executingMemoryScriptdatafile1switchedtodatafilecopyinputdatafilecopyRECID=1STAMP=997800031filename=/ncdata/system.256.990886809datafile2switchedtodatafilecopyinputdatafilecopyRECID=2STAMP=997800031filename=/ncdata/sysaux.257.990886809datafile3switchedtodatafilecopyinputdatafilecopyRECID=3STAMP=997800031filename=/ncdata/undotbs1.258.990886809datafile4switchedtodatafilecopyinputdatafilecopyRECID=4STAMP=997800031filename=/ncdata/users.259.990886811datafile5switchedtodatafilecopyinputdatafilecopyRECID=5STAMP=997800031filename=/ncdata/undotbs2.261.990886951datafile6switchedtodatafilecopyinputdatafilecopyRECID=6STAMP=997800031filename=/ncdata/nnc_data01.dbfdatafile7switchedtodatafilecopyinputdatafilecopyRECID=7STAMP=997800031filename=/ncdata/nnc_index01.dbfdatafile8switchedtodatafilecopyinputdatafilecopyRECID=8STAMP=997800031filename=/ncdata/nnc_data01a.dbfdatafile9switchedtodatafilecopyinputdatafilecopyRECID=9STAMP=997800031filename=/ncdata/nnc_data01b.dbfdatafile10switchedtodatafilecopyinputdatafilecopyRECID=10STAMP=997800031filename=/ncdata/nnc_data01c.dbf18/20datafile11switchedtodatafilecopyinputdatafilecopyRECID=11STAMP=997800031filename=/ncdata/nnc_data01d.dbfdatafile12switchedtodatafilecopyinputdatafilecopyRECID=12STAMP=997800031filename=/ncdata/nnc_index01a.dbfdatafile13switchedtodatafilecopyinputdatafilecopyRECID=13STAMP=997800031filename=/ncdata/system.271.990932493datafile14switchedtodatafilecopyinputdatafilecopyRECID=14STAMP=997800031filename=/ncdata/sysaux.272.990933605datafile15switchedtodatafilecopyinputdatafilecopyRECID=15STAMP=997800031filename=/ncdata/nnc_index01b.dbfFinishedDuplicateDbat17-JAN-19RMAN>11.备库操作：恢复完成后，备库开启日志应用alterdatabaserecovermanagedstandbydatabasedisconnect;12.备库操作：查看状态selectopen_mode,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUSfromv$database;13.备库操作：备库起到openalterdatabaserecovermanagedstandbydatabasecancel;alterdatabaseopen;alterdatabaserecovermanagedstandbydatabaseusingcurrentlogfiledisconnect;selectopen_mode,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUSfromv$database;19/20五、附录参考文档：1、oracle官方文档：HighAvailability/DataGuardConceptsandAdministration/3CreatingaPhysicalStandbyDatabase2、https://blog.csdn.net/xxzhaobb/article/details/791089633、https://blog.csdn.net/hzcyhujw/article/details/830676684、https://www.linuxidc.com/Linux/2015-03/115167.htm六、结果验证1、备库查看状态selectopen_mode,PROTECTION_LEVEL,DATABASE_ROLE,SWITCHOVER_STATUSfromv$database;2、备库查看日志应用情况selectprocess,status,sequence#fromv$managed_standby;七、注意事项无。20/20
