Oracle数据库DG单实例双备机

Oracle数据库DG单实例双备机

部署记录

2020-03

目录

环境介绍................................................................3

建立测试主库：..........................................................5

修改oracle用户profile：..........................................................................................5

设置主库监听................................................................5

启动监听后，创建测试主库：..................................................6

查看控制文件：..........................................................6

查看数据文件：..........................................................7

查看归档文件：..........................................................7

查看db_unique_name:.......................................................................................7

主库开启强制同步：..........................................................7

环境介绍

本次测试在一台物理主机下新建3台虚拟机进行测试，主库用

1521端口做业务服务，用1522端口做DGOdgl模拟大带宽备机使用

dumplicate复制，dg2模拟窄带宽备机使用rman备份，各项配置计划

如下：

主库备库1备库2

主机名primarysecondthird

IP地址规划012

ORACLE_SIDdccbdbdccbdgldccbdg2

TNSNAMEdccbdbdccbdgldccbdg2

数据文件路径/oradata/dccbdb/oradata/dccbdgl/oradata/dccbdg2

归档文件路径/archivelog/dccbdb/archivelog/dccbdgl/archivelog/dccbdg2

db_unique_namedccbdbdccbdgldccbdg2

log_archvie_configdccbdb,dccbdgl,dccbdb,dccbdgl,dccbdb,dccbdgl,

dccbdg2dccbdg2dccbdg2

fal_serverdccbdgl,dccbdg2dccbdb,dccbdg2dccbdb,dccbdgl

fal_clientdccbdbdccbdgldccbdg2

db_file_name_convert/oradata/dccbdgl,/oradata/dccbdb,/oradata/dccbdb,

/oradata/dccbdb/oradata/dccbdgl,/oradata/dccbdg2

log_file_name_convert/oradata/dccbdgl,/oradata/dccbdb,/oradata/dccbdb,

/oradata/dccbdb/oradata/dccbdgl,/oradata/dccbdg2

more/etc/hosts

0primary

1second

2third

建立测试主库:

修改oracle用户profile：

[oracle@primary~]$more-/.bash_profile

#.bash_profile

#Getthealiasesandfunctions

if[-f-/.bashrc];then

.~/.bashrc

fi

#Userspecificenvironmentandstartupprograms

PATH=$PATH:$HOME/.local/bin:$HOME/bin

exportPATH

#AddforOracle

umask022

exportORACLE_BASE=/u01/app/oracle

exportORACLE_HOME=$ORACLE_BASE/product/12.2.0/dbhome_l/

exportPATH=$PATH:/usr/sbin:$ORACLE_HOME/bin

exportORACLE_SID=dccbdb

exportORACLE_TERM=xterm

exportLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

CLASSPATH=$ORACLE_HOME/jdk/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

if[$USER="oracle"];then

if[SSHELL=7bin/ksh"];then

ulimit-p16384

ulimit-n65536

else

ulimit-u16384-n65536

fi

fi

设置主库监听

在监听中添加了1522端口做为DG的监听端口。

[oracle@primaryadmin]$morelistener.ora

#listener.oraNetworkConfigurationFile:

/u01/app/oracle/product/12.2.0/dbhome_l/network/admin/listener.ora

#GeneratedbyOracleconfigurationtools.

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=0)(PORT=1521))

)

LISTENER_DG二

(DESCRIPTION二

(ADDRESS二(PROTOCOL=TCP)(HOST二0)(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

启动监听后，创建测试主库：

[oracle@primarydbs]$Isnrctlstartlistener

[oracle@primarydbs]$Isnrctlstartlistener_dg

查看控制文件：

SQL>selectnamefromv$controlfile;

/uOl/app/oracle/oradata/dccbdb/controlOl.ctl

/u01/app/oracle/fast_recovery_area/dccbdb/control02.ctl

查看数据文件：

SQL>selectnamefromv$datafile;

/oradata/dccbdb/systemOl.dbf

/oradata/dccbdb/sysauxOl.dbf

/oradata/dccbdb/undotbsOl.dbf

/oradata/dccbdb/usersOl.dbf

查看归档文件：

确认主库是否处于归档模式,若为非归档模式,需要修改为归档模式。此处我们已经开启归档。

SQL>showparameterdb_recovery_fiIe_dest;

NAMETYPEVALUE

db_recovery_fiIe_deststring/archivelog/dccbdb

db_recovery_file_dest_sizebiginteger8016M

查看db_unique_name:

SQL>showparameterdb_unique_name;

NAMETYPEVALUE

db_unique_namestringdccbdb

主库开启强制同步:

SQL>showuser;

USERis"SYS"

SQL>alterdatabaseforcelogging;

Databasealtered.

验证是否同步：

SQL>selectforce_loggingfromv$database;

FORCE_LOGGING

YES

主库添加standbyredo

standbyredo比联机日志多一个：

首先确认联机日志的路径和大小：

SQL>selectgroup#,thread#,bytes/1024/1024MBfromv$log;

GROUP#THREAD#MB

11200

21200

31200

SQL>selectmemberfromvSlogfile;

MEMBER

/oradata/dccbdb/redo03.log

/oradata/dccbdb/redo02」og

/oradata/dccbdb/redoOl.log

或：

SQL>selecta.group#,a.thread#,a.bytes/1024/1024Mb.b.memberfromv$loga,v$logfileb

wherea.group#=b.group#;

GROUP#THREAD#MBMEMBER

31200/oradata/dccbdb/redo03.log

21200/oradata/dccbdb/redo02.log

11200/oradata/dccbdb/redoOl.log

添力口standbyredolog文件：

SQL>alterdatabaseaddstandbylogfilethread1group47oradata/dccbdb/stdredo04.log'size

200M;

Databasealtered.

SQL>alterdatabaseaddstandbylogfilethread1group57oradata/dccbdb/stdredo05.log'size

200M;

Databasealtered.

SQL>alterdatabaseaddstandbylogfilethread1group67oradata/dccbdb/stdredo06.log'size

200M;

Databasealtered.

SQL>alterdatabaseaddstandbylogfilethread1group77oradata/dccbdb/stdredo07.log'size

200M;

Databasealtered.

添力口standbyredolog后查看:

SQL>selecttype,memberfromvSlogfile;

TYPEMEMBER

ONLINE/oradata/dccbdb/redo03.log

ONLINE/oradata/dccbdb/redo02.log

ONLINE/oradata/dccbdb/redoOl.log

STANDBY/oradata/dccbdb/stdredo04.log

STANDBY/oradata/dccbdb/stdredo05.log

STANDBY/oradata/dccbdb/stdredo06.log

STANDBY/oradata/dccbdb/stdredo07.log

查看监听文件：

[oracle@primaryadmin]$morelistener.ora

#listener.oraNetworkConfigurationFile:/u01/app/oracle/product/12.2.0/dbhome_l/netwo

rk/admin/listener.ora

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=0)(PORT=1521))

)

ADR_BASE_LISTENER=/uOl/app/oracle

启动监听：

[oracle@primaryadmin]$IsnrctlstartLISTENER

编辑tnsnames文件：

[oracle@primaryadmin]$vitnsnames.ora

#tnsnames.oraNetworkConfigurationFile:

/u01/app/oracle/product/12.2.0/dbhome_l/network/admin/tnsnames.ora

#GeneratedbyOracleconfigurationtools.

DCCBDB二

(DESCRIPTION=

(ADDRESS=(PROTOCOL=TCP)(HOST=0)(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=2)(PORT=1522))

(CONNECT_DATA二

(SERVER=DEDICATED)

(SERVICE_NAME=dccbdg2)

)

LISTENER_DCCBDB二

(ADDRESS=(PROTOCOL=TCP)(HOST=0)(PORT=1521))

修改主库DG参数：

先对参数文件进行一个备份：

[oracle@primaryadmin]$exportORACLE_SID=dccbdb

[oracle@primaryadmin]$sqlplus/assysdba

SQL>createpfile=7u01/app/oracle/product/12.2.0/dbhome_l/dbs/initdccbdb.ora,fromspfile;

一可将initdccbdb.ora文件再拷贝一个备份。

SQL>altersystemsetlog_archive_config='dg_config=(dccbdbIdccbdgl1dccbdg2)'scope二b

oth;

SQL>altersystemsetlog_archive_dest_l='location=/archivelog/dccbdb/valid_for=(all_logfi

les,all_roles)db_unique_name=dccbdb'scope=both;

SQL>altersystemsetlog_archive_dest_2='service=dccbdglIgwrasynccompression=enab

Ievalid_for=(online_logfiles,primary_role)db_unique_name=dccbdgl'scope=both;

SQL>altersystemsetIog_archive_dest_3='service=dccbdg2Igwrasynccompression=enab

Ievalid_for=(online_logfiles,primary_role)db_unique_name=dccbdg2'scope=both;

SQL>altersystemsetlog_archive_dest_state_l=enablescope=both;

SQL>altersystemsetIog_archive_dest_state_2=deferscope=both;

SQL>altersystemsetIog_archive_dest_state_3=deferscope=both;

SQL>altersystemsetfal_=,dccbdgl,dccbdg2,scope=both;

SQL>altersystemsetfal_client='dccbdb'scope=both;

SQL>altersystemsetstandby_file_management=autoscope=both;

SQL>altersystemset.convert='/oradata/dccbdgl'.'/oradata/dccbdb'scope=s

pfile;

SQL>altersystemsetlog_file_name_convert=7archivelog/dccbdgl',7archivelog/dccbdb'sc

ope=spfile;

修改数据后重启数据库，然后再次生成一个参数文件，可以和之前生产的参数文件

作对照。

SQL>shutdownimmediate;

SQL>startup;

SQL>createpfile=7u01/app/oracle/product/12.2.0/dbhome_l/dbs/initdccb_new.ora'from

spfile;

对比前后的参数文件（红色为修改过的部分，绿色为新增部分）:

EdttVlowSevrhTemwulHaip

(oracle^prlmarydbs)$catlnltdccbdb.ora.bakdeebdb.pgaggregatetarget«838866868

dccMb.datatransfercacheslze«0deebdb.sgatarget=25165824O8

dccbdb.dbcachesize"1828716544dccMb.sharediopoolslze=11744eS12

dcctxJb.inmemoryextroarea*6deebdb.sharedpoolsize>M3316480

dccbdb.~inmemory二ext]rwardeebdb.streanspoolsize«6

deebdb.二Java_pooT_slie-16777216'.auditfiledest="Zufil/app/oracle/aitein/dccbdb/adunip'

deebdb.largepoolslze-33554432,.audlCtraii-'db'

dccMb.oraclebase*'/u81/app/oracle,«ORACIEBASEsetfromenvironment,.compatible-'12.2.G'

dcc6<Jb._pgaaggregatetargct-83886e860,.controlfiles-'/uOl/app/oracle/oradata/dccbdb/controlOl.ctl'.7u61/app/oracle/fastrecovery

deebdb.sgatarQet-25i6582496area/dccMb/controie2.ctl'

deebdb.sharedlopoolslze»117449512•.db_block_size-8192

deebdb.sharedpoolslzcs5e331648d,.dbcreateonlinelogdest1-''

dccWb.$tr««nspool*.dbcreateonlineloodest2='1

•.auditfiledest-'/uei/app/oracle/admln/dccbdb/adunip''.dbnameconvart»'/ordaata/<lcct)dgr,'/oradata/dccbdB'I

•.audittrail«'db'

,.compatibles'12.2.6,,.dbrecoveryfiledest—/archivelog/dccbdb'

,.centrolfile*-'/u61/app/oracle/ora<lata/dccbdb/controlGl.ctl'.'/udl/app,.dbrecoveryfiledestsize^GO16m

b/controlB2.ctU,.<tt>unlqu«n(M>«=>,deebdb'

*.dt>blocksize>8192,.diagnosticdest-'/uOl/app/oracle'

•.db3create_onllne_loo_de$t_l»,,：.g"pa"he[s]("<yXOL♦TCP)(SERVItE»<kcbdbXD8),

•.db二create二onliMllog[dest12・''*.faletients'deeixft1~~|

,.dbnMe«'dccbdb',falserver*'dccbdol.dccbdo?M'I

•.dbrecoveryfiledest-'/archivelog/dccbdb''.locallisle.or-LISTENEROCCBDB'

,.dbrecovery,filedestsize-8816n,.logarchivecontig=dgcor'tig=tdccbdb,dccbOgl,<JCCD(JO<iJ'

•.dbuniquenane-'deebdb'•.logarchivedest1='locaxion«/archivelofl/<Jcct>db/validfor«(alllogtiles.aU_roles)dbuniqu

,.diagnosticdest-'ZuOl/app/oracle,ejMwe-dcctxlb7

•.dispatchers-'(PROTOCOL-TCP)(SERVICE-dccbdbXOB)',.logarchivedest2-servlce-dccWglIgwrasyncc<npre$slon>enablevalidfor-(onllnelogfiles

•[ocalUstm2r-IISTFNERDCCBDB'.prlnaryrole)dbuniquename»dccM9「

1

,IlogarchivedestI"',.logarchivedost3>*$«rvlce>dccb«(g2Igwrasynccompresslon-onabUvalidfor*(onlln«_logflies

,logarchivedest2=''.primaryrole)dbuniquename-dccb<lg2'

,\ogarchivedeststate1='enable'•.logarchivedeststate1«'ENABLE'

,]OQarchiveQest,.logarchivedeststate2='DEFER'

,.togarchivefornat»AtKs^r.dbf',IonArrhtvodost<tAt«3-DF^

,.nlslanguage^'AMERICAN

,.nls二territory二.AMERICA'filenaneconvert»/archivelog/dccbdgr,/archiveloq/dcctxib,|

*.op«n_cursors-3ee,.nl《Tanguage='ArtEAlcAN'

,.pgaaggregatetarget>795m,nUt«rrltory-AMERICA,

•.processes-329,.opencursors-36e

•.remote.lofllnpasswordtiie.,EXCLUSIVE,*.pgaaggregatetarget»795m

,.sga_target-2385fi,.processes-328-

,.undotablespace*'UND0TBS1',.re«M>te^loQin_passwordflie-'EXCLUSIVE'

(oracle9prlnarydbs]$,.saataroet«y25(^^_

(oraclejSprimarydbs]$•.stanCbvfilelyeden。--'AU!。'____

[oraclejprlmarydbs]$•.undotablespace--UNDOTBSI'

joraclegiirimarydbsj$口loraclegrliwydbsls■

发现参数文件中的fal_server有个乱码，重新执行

SQL>altersystemsetfaLserver='dccbdgl,deebdb'scope=both;

再次生产pfile文件，查看并无乱码了。

生成最终的参数文件，并将参数文件传输到second和third。

[oracle@primary〜]$sep$ORACLE_HOME/dbs/initdccbdb.oraoracle@second:'pwd'

[oracle@primary-]$sep$ORACLE_HOME/dbs/initdccbdb.oraoracle@third:'pwd'

将主库的密码文件传输到两个备库:

[oracle@primarydbs]$scporapwdccbdboracle@second:'pwd'

登陆到second(1)主机上，将密码文件修改为or叩wdccbdgl

[oracle@primarydbs]$scporapwdccbdboracle@third:'pwd'

登陆到third(2)主机上，将密码文件修改为orapwdccbdg2

创建备库监听

配置second主机(dgl)的监听：

[oracle@secondadmin]$morelistener.ora

LISTENER=

(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)

(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_l)

(SID_NAME=dccbdgl)

)

)

ADR_BASE_LISTENER=/uOl/app/oracle

配置Third(dg2)的监听：

[oracle@secondadmin]$morelistener.ora

LISTENER=

(DESCRIPTION=

(ADDRESS二(PROTOCOL=TCP)(HOST二third)(PORT=1521))

)

LISTENER_DG=

(DESCRIPTION二

(ADDRESS二(PROTOCOL=TCP)(HOST=third)(PORT=1522))

)

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

启动监听并查看状态：

[oracle@thirdadmin]$Isnrctlstartlistener

[oracle@thirdadmin]$Isnrctlstartlistener_dg

将主库的tnsnames文件拷贝到备库：

[oracle@primaryadmin]$scptnsnames.oraoracle@second:'pwd'

oracle@second'spassword:

tnsnames.ora100%787145.0KB/s00:00

[oracle@primaryadmin]$scptnsnames.oraoracle@third:'pwd'

oracle@third'spassword:

tnsnames.ora100%7871.4MB/S00:00

修改传输到两个备库的参数文件

修改dgl的参数文件：

首先创建必要的目录：

[root@standby-]#mkdir/oradata

[root@standby〜]#chownoracle:oinstall-R/oradata

[root@standby〜]#su-oracle

Lastlogin:WedMar2517:03:32CST2020onpts/1

[oracle@second-]$mkdir-p/oradata/dccbdb

[oracle@second-]$mkdir-p/oradata/dccbdgl

[oracle@second-]$mkdir-p/archivelog/dccbdb

[oracle@second-]$mkdir-p/archivelog/dccbdgl

[oracle@second-]$mkdir-p/uOl/app/oracle/admin/dccbdb/adump

修改参数文件如下：

[oracle@secondadmin]$viinitdccbdb.ora

dccbdb._data_transfer_cache_size=O

dccbdb.__db_cache_size=1828716544

dccbdb._inmemory_ext_roarea=0

dccbdb._inmemory_ext_rwarea=0

dccbdb._java_pool_size=16777216

dccbdb._large_pool_size=33554432

dccbdb._data_transfer_cache_size=O

dccbdb._db_cache_size=1828716544

dccbdb._inmemory_ext_roarea=0

dccbdb._inmeinory_ext_rwarea=0

dccbdb._java_pool_size=16777216

dccbdb._large_pool_size=33554432

dccbdb._oracle_base=7u01/app/oracle'#ORACLE_BASEsetfromenvironment

dccbdb._pga_aggregate_target=838860800

dccbdb._sga_target=2516582400

dccbdb._shared_io_pool_size=117440512

dccbdb._shared_pool_size=503316480

dccbdb._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_rec

overy_area/dccbdgl/control02.ctl'

*.db_block_size=8192

*.db_create_onlinejog_dest_l="

*.db_create_online」og_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_client=,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=dccbdbIgwrasynccompression=enablevalid_for=(online

Jogfiles,primary_role)db_unique_name=dccbdb'

*.log_archive_dest_3='service=dccbdg2Igwrasynccompression=enablevalid_for=(onlin

e」ogfiles,primary_role)db__unique_name=dccbdg2"

*.log_archive_dest_state_l='ENABLE'

*.log_archive_dest_state_2='DEFER'

*.log_archive_dest_state_3='DEFER'

*.log_archive_format='%t_%s_%r.dbf'

*.log_file_name_convert=7archivelog/dccbdb',7archivelog/dccbdgl'

*.nls」anguage='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='UNDOTBSl'

创建dgl的spfile,启动到nomount

[oracle(3)secondadmin]$exportORACLE_SID=dccbdgl

[oracle@secondadmin]$sqlplus/assysdba

SQL>createspfilefrompfile=7u01/app/oracle/product/12.2.0/dbhome_l/dbs/initdccb

db.ora';

SQL>startupnomount;

ORACLEinstancestarted.

TotalSystemGlobalArea2516582400bytes

FixedSize8623832bytes

VariableSize687868200bytes

DatabaseBuffers1811939328bytes

RedoBuffers8151040bytes

dg2的酉己置

创建如下目录：

[root@third~]#mkdir-p/oradata/dccbdg2

[root@third〜]#mkdir-p/archivelog/dccbdg2

[root@third~]#mkdir-p/uOl/app/oracle/admin/dccbdb/a