Oracle数据库DG单实例双备机.pdf

《Oracle数据库DG单实例双备机.pdf》由会员分享，可在线阅读，更多相关《Oracle数据库DG单实例双备机.pdf（27页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Oracle数据库DG单实例双备机部署记录2020-03目录环境介绍.3建立测试主库：.5修改 oracle 用户 profile：.5设置主库监听.5启动监听后，创建测试主库：.6查看控制文件：.6查看数据文件：.7查看归档文件：.7查看 db_unique_name:.7主库开启强制同步：.7环境介绍本次测试在一台物理主机下新建3台虚拟机进行测试，主库用1521端口做业务服务，用1522端口做DGO d g l模拟大带宽备机使用dumplicate复制，dg2模拟窄带宽备机使用rman备份，各项配置计划如下：主库备 库 1备库2主机名primarysecondthirdIP地址规划192.

2、168.0.20192.168.0.21192.168.0.22ORACLE_SIDdccbdbdccbdgldccbdg2TNSNAMEdccbdbdccbdgldccbdg2数据文件路径/oradata/dccbdb/oradata/dccbdgl/oradata/dccbdg2归档文件路径/archivelog/dccbdb/archivelog/dccbdgl/archivelog/dccbdg2db_unique_namedccbdbdccbdgldccbdg2log_archvie_configdccbdb,dccbdgl,dccbdg2dccbdb,dccbdgl,dccbdg2

3、dccbdb,dccbdgl,dccbdg2fal_serverdccbdgl,dccbdg2dccbdb,dccbdg2dccbdb,dccbdglfal_clientdccbdbdccbdgldccbdg2db_file_name_convert/oradata/dccbdgl,/oradata/dccbdb/oradata/dccbdb,/oradata/dccbdgl,/oradata/dccbdb,/oradata/dccbdg2log_file_name_convert/oradata/dccbdgl,/oradata/dccbdb/oradata/dccbdb,/oradata/

4、dccbdgl,/oradata/dccbdb,/oradata/dccbdg2more/etc/hosts192.168.0.20 primary192.168.0.21 second192.168.0.22 third建立测试主库:修改 oracle 用户 profile：oracleprimary$more-/.bash_profile#.bash_profile#Get the aliases and functionsif -f-/.bashrc;then./.bashrcfi#User specific environment and startup programsPATH=$P

5、ATH:$HOME/.local/bin:$HOME/binexport PATH#Add for Oracleumask 022export ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/12.2.0/dbhome_l/export PATH=$PATH:/usr/sbin:$ORACLE_HOME/binexport ORACLE_SID=dccbdbexport ORACLE_TERM=xtermexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

6、CLASSPATH=$ORACLE_HOME/jdk/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlibif$USER=oracle;thenif SSHELL=7bin/ksh;thenulimit-p 16384ulimit-n 65536elseulimit-u 16384-n 65536fifi设置主库监听在监听中添加了 1522端口做为D G的监听端口。oracleprimary admin$more listener.ora#listener.ora Network Configuration File:/u01/app/oracle/pro

7、duct/12.2.0/dbhome_l/network/admin/listener.ora#Generated by Oracle configuration tools.SID_LIST_LISTENER=(SID_LIST 二(SID_DESC=(GLOBAL_DBNAME=dccbdb)(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_l)(SID.NAME 二 dccbdb)LISTENER 二(DESCRIPTION=(ADDRESS 二(PROTOCOL=TCP)(HOST=192.168.0.20)(PORT=1521)LI

8、STENER_DG 二(DESCRIPTION 二(ADDRESS 二(PROTOCOL=TCP)(HOST 二 192.168.0.20)(PORT=1522)SID_LIST_LISTENER_DG 二(SID_LIST=(SID_DESC=(GLOBAL.DBNAME=dccbdb)(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_l)(SID_NAME=dccbdb)ADR_BASE_LISTENER=/uOl/app/oracle启动监听后，创建测试主库：oracleprimary dbs$Isnrctl start listene

9、roracleprimary dbs$Isnrctl start listener_dg查看控制文件：SQL select name from v$controlfile;/uOl/app/oracle/oradata/dccbdb/controlOl.ctl/u01/app/oracle/fast_recovery_area/dccbdb/control02.ctl查看数据文件：SQLselect name from v$datafile;/oradata/dccbdb/systemOl.dbf/oradata/dccbdb/sysauxOl.dbf/oradata/dccbdb/undot

10、bsOl.dbf/oradata/dccbdb/usersOl.dbf查看归档文件：确认主库是否处于归档模式,若为非归档模式,需要修改为归档模式。此处我们已经开启归档。SQL show parameter db_recovery_fiIe_dest;NAME TYPE VALUEd b_recovery_f i I e_dest string/archivelog/dccbdbdb_recovery_file_dest_size big integer 8016M查看 db_unique_name:SQL show parameter db_unique_name;NAME TYPE VALU

11、Edb_unique_name string dccbdb主库开启强制同步:SQL show user;USER is SYSSQL alter database force logging;Database altered.验证是否同步：SQL select force_logging from v$database;FORCE_LOGGINGYES主库添加standby redostandby redo比联机日志多一个：首先确认联机日志的路径和大小：SQL select group#,thread#,bytes/1024/1024 MB from v$log;GROUP#THREAD#MB

12、1 12 13 1200200200SQL select member from vSlogfile;MEMBER/oradata/dccbdb/redo03.log/oradata/dccbdb/redo02 og/oradata/dccbdb/redoOl.log或：SQL select a.group#,a.thread#,a.bytes/1024/1024 Mb.b.member from v$log a,v$logfile bwhere a.group#=b.group#;GROUP#THREAD#MB MEMBER3 12 11 1200/oradata/dccbdb/redo03

13、.log200/oradata/dccbdb/redo02.log200/oradata/dccbdb/redoOl.log添 力 口 standby redolog 文 件：SQL alter database add standby logfile thread 1 group 4 7oradata/dccbdb/stdredo04.log size200M;Database altered.SQL alter database add standby logfile thread 1 group 5 7oradata/dccbdb/stdredo05.log size200M;Datab

14、ase altered.SQL alter database add standby logfile thread 1 group 6 7oradata/dccbdb/stdredo06.log size200M;Database altered.SQL alter database add standby logfile thread 1 group 7 7oradata/dccbdb/stdredo07.log size200M;Database altered.添 力 口 standby redolog 后查看:SQL select type,member from vSlogfile;

15、TYPE MEMBERONLINE/oradata/dccbdb/redo03.logONLINE/oradata/dccbdb/redo02.logONLINE/oradata/dccbdb/redoOl.logSTANDBY/oradata/dccbdb/stdredo04.logSTANDBY/oradata/dccbdb/stdredo05.logSTANDBY/oradata/dccbdb/std redo06.logSTANDBY/oradata/dccbdb/stdredo07.log查看监听文件：oracleprimary admin$more listener.ora#lis

16、tener.ora Network Configuration File:/u01/app/oracle/product/12.2.0/dbhome_l/network/admin/listener.oraSID_LIST_LISTENER 二(SID.LIST 二(SID_DESC=(GLOBAL.DBNAME 二 dccbdb)(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_l)(SID_NAME=dccbdb)LISTENER 二(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.2

17、0)(PORT=1521)ADR_BASE_LISTENER=/uOl/app/oracle启动监听：oracleprimary admin$Isnrctl start LISTENER编辑tnsnames文件：oracleprimary admin$vi tnsnames.ora#tnsnames.ora Network Configuration File:/u01/app/oracle/product/12.2.0/dbhome_l/network/admin/tnsnames.ora#Generated by Oracle configuration tools.DCCBDB二(DES

18、CRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.20)(PORT=1522)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dccbdb)DCCBDG1二(DESCRIPTION=(ADDRESS 二(PROTOCOL=TCP)(HOST=192.168.0,21)(PORT=1522)(CONNECT.DATA=(SERVER=DEDICATED)(SERVICE_NAME=dccbdgl)DCCBDG2=(DESCRIPTION 二(ADDRESS=(PROTOCOL=TCP)(HOST=192.1

19、68.0.22)(PORT=1522)(CONNECT_DATA 二(SERVER=DEDICATED)(SERVICE_NAME=dccbdg2)LISTENER_DCCBDB 二(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.20)(PORT=1521)修改主库DG参数：先对参数文件进行一个备份：oracleprimary admin$export ORACLE_SID=dccbdboracleprimary admin$sqlplus/as sysdbaSQL create pfile=7u01/app/oracle/product/12.2.0/dbhom

20、e_l/dbs/initdccbdb.ora,from spfile;一可将initdccbdb.ora文件再拷贝一个备份。SQL alter system set log_archive_config=dg_config=(dccbdbIdccbdgl1dccbdg2)scope二both;SQL alter system set log_archive_dest_l=location=/archivelog/dccbdb/valid_for=(all_logfiles,all_roles)db_unique_name=dccbdb scope=both;SQL alter system s

21、et log_archive_dest_2=service=dccbdgl Igwr async compression=enabIe valid_for=(online_logfiles,primary_role)db_unique_name=dccbdgl scope=both;SQL alter system set Iog_archive_dest_3=service=dccbdg2 Igwr async compression=enabIe valid_for=(online_logfiles,primary_role)db_unique_name=dccbdg2 scope=bot

22、h;SQL alter system set log_archive_dest_state_l=enable scope=both;SQL alter system set Iog_archive_dest_state_2=defer scope=both;SQL alter system set Iog_archive_dest_state_3=defer scope=both;SQL alter system set fal_=,dccbdgl,dccbdg2,scope=both;SQL alter system set fal_client=dccbdb scope=both;SQL

23、alter system set standby_file_management=auto scope=both;SQL alter system set dbjile.name.convert=/oradata/dccbdgl./oradata/dccbdb scope=spfile;SQL alter system set log_file_name_convert=7archivelog/dccbdgl,7archivelog/dccbdb scope=spfile;修改数据后重启数据库，然后再次生成一个参数文件，可以和之前生产的参数文件作对照。SQL shutdown immediat

24、e;SQL startup;SQL create pfile=7u01/app/oracle/product/12.2.0/dbhome_l/dbs/initdccb_new.ora fromspfile;对比前后的参数文件（红色为修改过的部分，绿色为新增部分）:(oracleprlmary dbs)$cat lnltdccbdb.ora.bakdccMb.data t ransfer cache slze0dccbdb.db cache size1828716544dcctxJb.inmemory ext roarea*6dccbdb.i nmemo ry二 ex t rwa rdeebdb

25、.二Java_pooT_slie-16777216deebdb.large poolslze-33554432dccMb.oracle base*/u81/app/oracle,ORACIE BASE set from environmentdcc6Jb._ pga aggregate targct-83886e860deebdb.sga tarQet-25i6582496deebdb.shared lo pool slze117449512deebdb.shared pool slzcs5e331648ddccWb.$trns pool.a u d it f ile d e s t-/uei

26、/app/oracle/admln/dccbdb/adunip.a u d it tra il d b,.com patibles12.2.6,.centrol f ile*-/u61/app/oracle/ora block size8192.db3create_onllne_loo_de$t_l,.db二create二on liM llo g d est12,.db nMedccbdb.db recovery f ile d es t-/archivelog/dccbdb,.db reco ve ry,file dest size-8816n.db unique nane-deebdb,.

27、diagnostic dest-ZuO l/app/oracle,.dispatchers-(PROTOCOL-TCP)(SERVICE-dccbdbXOB)o c a l U stm 2r-IIS T F N E R DCCBDB,Ilog archive dest I 1,log archive dest 2=,og archive dest state 1=enable,OQ archive Qest,.tog archive fo rn atA t Ks r.d b f,.n ls languageAMERICAN,.n ls二te rrito ry二.AMERICA*.opn_cur

28、sors-3ee,.pga aggregate target795m.processes-329.rem ote.lofllnpassw ordtiie.,EXCLUSIVE,.sga_target-2385fi,.undo tablespace*UND0TBS1(oracle9prlnary dbs$(oraclejSprimary dbs$oraclejprlm ary dbs$joraclegiirimary dbsj$口Edtt Vlow Sevrh Temwul Haipdeebdb.p g aggregate target838866868deebdb.sga target=251

29、65824O8dccMb.shared io pool slze=11744eS12deebdb.shared pool sizeM3316480deebdb.streans pool size6.a u d it f ile dest=Zufil/app/oracle/aitein/dccbdb/adunip,.a u d lC tra ii-d b,.com patible-1 2.2.G,.c o n tro l files-/u O l/ap p/o racle/o rad ata/d ccb d b/co n tro lO l.ctl.7u61/app/oracle/fast rec

30、overyarea/d ccM b/co n tro ie2.ctl.db_block_size-8192,.db create online log dest 1-*.db create online loo dest 2=1.db name convart/ordaata/lcct)d g r,/oradata/dccbdB I,.db recovery f ile dest/archivelog/dccbdb,.db recovery f ile dest sizeGO16m,.unlqu n(M=,deebdb,.diagnostic d est-/uO l/app/oracle：.g

31、 p a h e s(y X O L T C P)(SERVItEkcbdbXD8),*.fa l etientsdeeixft1|,fa l server*dccbdol.dccbdo?M I.lo c a l lis le.o r-LISTENER OCCBDB,.lo g archive contig=dg cortig=tdccbdb,dccbOgl,JCCD(JOiJ.log archive dest 1=locaxion/archivelofl/db/valid fo r(a ll lo g tile s.a U _ro le s)db unique jMwe-dcctxlb7,.

32、log archive dest 2-servlce-dccW g l Igwr async cenable valid fo r-(o n lln e lo g files.prlnary ro le)db unique namedccM9,.log archive dost 3*$rvlcedccb(g2 Igwr async compresslon-onabU valid fo r*(onlln_logflies.primary ro le)db unique name-dccblg2.lo g archive dest state 1 ENABLE,.lo g archive dest

33、 state 2=DEFER,Ion Arrhtvo dost teloQin_passwordf lie-EXCLUSIVE,.saa taro ety25(_.stanCbv f ile l y e d e n。-AU!。_ _ _ _.undo tablespace-UNDOTBSIlo ra c le g rliw y dbsls 发现参数文件中的fal_server有个乱码，重新执行SQL alter system set faLserver=dccbdgl,deebdb scope=both;再次生产pfile文件，查看并无乱码了。生成最终的参数文件，并将参数文件传输到second

34、和 third。oracleprimary$sep$ORACLE_HOME/dbs/initdccbdb.ora oraclesecond:pwdoracleprimary-$sep$ORACLE_HOME/dbs/initdccbdb.ora oraclethird:pwd将主库的密码文件传输到两个备库:oracleprimary dbs$scp orapwdccbdb oraclesecond:pwd登陆到second(1)主机上，将密码文件修改为or叩wdccbdgloracleprimary dbs$scp orapwdccbdb oraclethird:pwd登陆到third(2)主

35、机上，将密码文件修改为orapwdccbdg2创建备库监听配置second主 机(d g l)的监听：oraclesecond admin$more listener.oraLISTENER=(DESCRIPTION 二(ADDRESS=(PROTOCOL=TCP)(HOST=second)(PORT=1521)LISTENER_DG 二(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST 二 second)(PORT=1522)SID_LIST_LISTENER_DG 二(SID_LIST 二(SID_DESC=(GLOBAL.DBNAME 二 dccbdgl)

36、(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_l)(SID_NAME=dccbdgl)ADR_BASE_LISTENER=/uOl/app/oracle配置Third(d g 2)的监听：oraclesecond admin$more listener.oraLISTENER=(DESCRIPTION=(ADDRESS 二(PROTOCOL=TCP)(HOST 二 third)(PORT=1521)LISTENER_DG=(DESCRIPTION 二(ADDRESS 二(PROTOCOL=TCP)(HOST=third)(PORT=152

37、2)SID_LIST_LISTENER_DG 二(SID_LIST=(SID-DESC=(GLOBAL_DBNAME=dccbdg2)(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_l)(SID_NAME=dccbdg2)ADR_BASE_LISTENER=/uOl/app/oracle启动监听并查看状态：oraclethird admin$Isnrctl start listeneroraclethird admin$Isnrctl start listener_dg将主库的tnsnames文件拷贝到备库：oracleprimary ad

38、min$scp tnsnames.ora oraclesecond:pwdoracleseconds password:tnsnames.ora 100%787 145.0KB/s 00:00oracleprimary admin$scp tnsnames.ora oraclethird:pwdoraclethirds password:tnsnames.ora 100%787 1.4MB/S 00:00修改传输到两个备库的参数文件修改d g l的参数文件：首先创建必要的目录：rootstandby-#mkdir/oradatarootstandby#chown oracle:oinstall

39、-R/oradatarootstandby#su-oracleLast login:Wed Mar 25 17:03:32 CST 2020 on pts/1oraclesecond-$mkdir-p/oradata/dccbdboraclesecond-$mkdir-p/oradata/dccbdgloraclesecond-$mkdir-p/archivelog/dccbdboraclesecond-$mkdir-p/archivelog/dccbdgloraclesecond-$mkdir-p/uOl/app/oracle/admin/dccbdb/adump修改参数文件如下：oracl

40、esecond admin$vi initdccbdb.oradccbdb._data_transfer_cache_size=Odccbdb._db_cache_size=1828716544dccbdb._inmemo ry_ext_roa rea=0dccbdb._inmemory_ext_rwarea=0dccbdb._java_pool_size=16777216dccbdb._large_pool_size=33554432dccbdb._data_transfer_cache_size=Odccbdb._db_cache_size=1828716544dccbdb._inmemo

41、 ry_ext_roa rea=0dccbdb._inmeino ry_ext_rwa rea=0dccbdb._java_pool_size=16777216d ccbd b._la rg e_pool_size=33554432dccbdb._oracle_base=7u01/app/oracle#ORACLE_BASE set from environmentdccbdb._pga_aggregate_target=838860800dccbdb._sga_target=2516582400dccbdb._shared_io_pool_size=117440512dccbdb._shar

42、ed_pool_size=503316480dccbdb._streams_pool_size=0*.audit_file_dest=7u01/app/oracle/admin/dccbdb/adump*.audit_trail=db*.compatible=1220*.control_files=7u01/app/oracle/oradata/dccbdgl/control01.ctr,7u01/app/oracle/fast_recovery_area/dccbdgl/control02.ctl*.db_block_size=8192*.d b_create_o nlinej og_d e

43、st_l=*.d b_create_onl i neog_dest_2=*.db_file_name_convert=7oradata/dccbdb,7oradata/dccbdgl,*.db_name=dccbdb*.db_recovery_file_dest=7archivelog/dccbdgl,*.db_recovery_file_dest_size=8016m*.db_unique_name=dccbdgl*.diagnostic_dest=7u01/app/oracle*.dispatchers=(PROTOCOL=TCP)(SERVICE=dccbdglXDB)*.fal_cli

44、ent=,dccbdgl*.fal_server=dccbdb,*local_listener=,LISTENER_DCCBDB*.log_archive_config=dg_config=(dccbdb,dccbdgl1dccbdg2)*.log_archive_dest_l=location=/archivelog/dccbdgl/valid_for=(all_logfiles,all_roles)db_unique_name=dccbdgl*.log_archive_dest_2=service=dccbdb Igwr async compression=enable valid_for

45、=(onlineJogfiles,primary_role)db_unique_name=dccbdb*.log_archive_dest_3=service=dccbdg2 Igwr async compression=enable valid_for=(onlineogfiles,primary_role)db_unique_name=dccbdg2*.log_archive_dest_state_l=ENABLE*.log_arch ive_dest_state_2=DEFER*.log_archive_dest_state_3=DEFER*.log_archive_format=%t_

46、%s_%r.dbf*.log_file_name_convert=7archivelog/dccbdb,7archivelog/dccbdgl*.nlsanguage=AMERICAN*.nls_territory=AMERICA*.open_cursors=300*.open_cursors=300*.pga_aggregate_target=795m*.processes=320*.remote_login_passwordfile=,EXCLUSIVE*.sga_target=2385m札 standby_file_management=AUTO*.undo_tablespace=UND

47、OTBSl创建d g l的 spfile,启动到nomountoracle(3)second admin$export ORACLE_SID=dccbdgloraclesecond admin$sqlplus/as sysdbaSQL create spfile from pfile=7u01/app/oracle/product/12.2.0/dbhome_l/dbs/initdccbdb.ora;SQL startup nomount;ORACLE instance started.Total System Global Area 2516582400 bytesFixed Size 86

48、23832 bytesVariable Size 687868200 bytesDatabase Buffers 1811939328 bytesRedo Buffers 8151040 bytesdg2的酉己置创建如下目录：rootthird#mkdir-p/oradata/dccbdg2rootthird#mkdir-p/archivelog/dccbdg2rootthird#mkdir-p/uOl/app/oracle/admin/dccbdb/aduiriprootthird#chown-R oracle:oinstall/oradatarootthird#chown-R oracle

49、:oinstall/archivelog/oraclethird$mkdir-p/u01/app/oracle/admin/dccbdb/adump修改d g 2的参数文件：dccbdb._data_transfer_cache_size=Odccbdb._db_cache_size=1828716544dccbdb._inmemo ry_ext_roa rea=0dccbdb._inmemo ry_ext_rwa rea=0dccbdb._java_pooLsize=16777216dccbdb._large_pool_size=33554432dccbdb._oracle_base=7u0

50、1/app/oracle#ORACLE_BASE set from environmentdccbdb._pga_aggregate_target=838860800dccbdb._sga_target=2516582400dccbdb._shared_io_pool_size=117440512dccbdb._shared_pool_size=503316480dccbdb._streams_pool_size=0*.audit_file_dest=7u01/app/oracle/admin/dccbdb/adump,*.audit_trail=db*.compatible=12.20札 c