当前位置：Gxlcms > 数据库问题 > 部署Physical Dataguard 与双节点Oracle RAC环境

部署Physical Dataguard 与双节点Oracle RAC环境

时间：2021-07-01 10:21:17 帮助过：14人阅读

系统信息：

Primary端

RAC Primary	RACDG1（主机）	RACDG2（主机）	备注
Public IP	172.20.10. 11	172.20.10. 12
Virtual IP	10.0.0.11	10.0.0.12
Instance	racdg1	racdg2
DB name	RACDG
DATA file	+DATA2/racdg/datafile
Control file	+DATA2/racdg/controlfile +DATA1/racdg/controlfile/
Redo Log file	+DATA2/racdg/onlinelog/ db_recovery_file_dest
db_unique_name	racdg
service_names	racdg
Oracle_Version	11.2.0.1.0

Standby 端

Single instance standby	主机名	备注
IP	172.20.10.100
Oracle_version	11.2.0.1.0
Instance	racdg
DB name	RACDG
DB_unique_name	racdg_standy
service_names	racdg_standy
DATA file	/u01/oracle/racdg_standy/datafile/
Control file	/u01/app/oracle/controlfile1/ /u01/app/oracle/controlfile2/
Redo Log file	/u01/oracle/racdg_standy/onlinelog3/ /u01/oracle/racdg_standy/onlinelog/

结构图

技术分享

Standby 库类型说明

1、 PhysicalStandby Databases

其特性：

Disaster recovery and high availability
Data protection
Reduction in primary database workload
Performance

2、 LogicalStandby Databases

其特性：

Protection against additional kinds of failure
Effcient use of resource
Workload distribution
Optimized of reporting add decision supportrequirements
Minimizing downtime on software upgrade

3、Snapshot StandbyDatabases

A snapshot standby database is a type of updatable standby databasethat provides full data protection for a primary database. A snapshot standbydatabase receives and archives, but does not apply, redo data from its primarydatabase. Redo data received from the primary database is applied when asnapshot standby database is converted back into a physical standby database,after discarding all local updates to the snapshot standby database

A snapshot standby database typically diverges from its primarydatabase over time because redo data from the primary database is not appliedas it is received. Local updates to the snapshot standby database will causeadditional divergence. The data in the primary database is fully protectedhowever, because a snapshot standby can be converted back into a physicalstandby database at any time, and the redo data received from the primary willthen be applied

这段话可以看出snapshot standby DB对主库的数据完全保护，snapshot standby DB是通常对生产库不能做业务的测试和开发是一种解脱。如下这段话：

Benefits of a SnapshotStandby Database

A snapshot standby database is a fullyupdatable standby database that provides disaster recovery and data protectionbenefits that are similar to those of a physical standby database. Snapshotstandby databases are best used in scenarios where the benefit of having atemporary, updatable snapshot of the primary database justifies the increasedtime to recover from primary database failures.

The benefits of using a snapshot standbydatabase include the following:

It provides an exact replica of aproduction database for development and testing purposes, while maintainingdata protection at all times.

It can be easily refreshed to containcurrent production data by converting to a physical standby andresynchronizing.

The ability to create a snapshot standby,test, resynchronize with production, and then again create a snapshot standbyand test, is a cycle that can be repeated as often as desired. The same processcan be used to easily create and regularly update a snapshot standby forreporting purposes where read/write access to data is required.

以上可说明，对生产库的实时保护。将开发和测试的主库，转到备库。备库并将接受主库的redlog但不apply，等测试完成后，再将snapshot standby DB切换回physical standby DB，恢复之前状态。

这里配置是的physical standby database类型

准备配置环境：

配置 tnsname.ora，在tnsname.ora文件中添加如下内容，并把tnsname.ora文件复制到所有节点及standby DB中。

rac_dg1 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = racdg)

(INSTANCE_NAME = racdg1)

)

rac_dg2 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = racdg)

(INSTANCE_NAME = racdg2)

)

racdg_standy =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SID = racdg)

)

配置physical standby DB 监听：listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = PLSExtProc)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /u01/app/oracle

Preparingthe Primary Database for Standby Database Creation

EnableForced Logging (Place the primary database in FORCE LOGGING mode.)
configure redotransport authentication
Configurethe Primary Database to Receive Redo Data
Set PrimaryDatabase Initialization Parameters
EnableArchiving

Creatinga Physical Standby Database

Create a Backup Copy of the Primary Database Datafiles
Create a Control File for the Standby Database
Copy Files from the Primary System to the Standby System
Prepare an Initialization Parameter File for the Standby Database
Start the Physical Standby Database
Verify the Physical Standby Database Is Performing Properly

Primarydatabase 配置：

1、开启Force logging

SQL> ALTER DATABASE FORCE LOGGING;

执行这条语句database 至少在mounted或者open状态，而且也可能会花费很多时间来完成，因为enable forced logging要等待all unlogged write I/O 结束。

查看select force_logging from v$database;

2、Configure RedoTransport Authentication

Data Guard usesOracle Net sessions to transport redo data and control messages between themembers of a Data Guard configuration. These redo transport sessions areauthenticated using either the Secure Sockets Layer (SSL) protocol or a remotelogin password file. 这里使用password file。

在rac 节点1上创建 pwdfile

orapwdfile=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwracdg1 password=FANfan1234entries=10 force=y

并将orapwracdg1复制到节点2和 standby DB 节点上并命名。

scp$ORACLE_HOME/dbs/orapwracdg1 oracle@racdg2:$ORACLE_HOME/dbs/orapwracdg2

scp$ORACLE_HOME/dbs/orapwracdg1 oracle@racdg2:$ORACLE_HOME/dbs/orapwracdg

3、Configure thePrimary Database to Receive Redo Data

这里在primary 主库做switchoverto standby db 时，再配置。

4、Set Primary DatabaseInitialization Parameters

Primary DB initialization parameter：

SQL> show parameter pfile

NAME TYPE VALUE

----------------------------------------------- ------------------------------

spfile string +DATA1/racdg/spfileracdg.ora

SQL>

createpfile=’/home/oracle/primary_pfile.pfile’ fromspfile=’+DATA1/racdg/spfileracdg.ora’

编辑参数：

racdg2.__db_cache_size=276824064

racdg1.__db_cache_size=276824064

racdg2.__java_pool_size=4194304

racdg1.__java_pool_size=4194304

racdg2.__large_pool_size=4194304

racdg1.__large_pool_size=4194304

racdg2.__pga_aggregate_target=339738624

racdg1.__pga_aggregate_target=339738624

racdg2.__sga_target=503316480

racdg1.__sga_target=503316480

racdg2.__shared_io_pool_size=0

racdg1.__shared_io_pool_size=0

racdg2.__shared_pool_size=209715200

racdg1.__shared_pool_size=209715200

racdg2.__streams_pool_size=0

racdg1.__streams_pool_size=0

*.audit_file_dest=‘/u01/app/oracle/admin/racdg/adump‘

*.audit_trail=‘db‘

*.cluster_database=true

*.compatible=‘11.2.0.0.0‘

*.control_files=‘+DATA2/racdg/controlfile/current.260.875118793‘,‘+DATA1/racdg/controlfile/current.256.875118799‘

*.db_block_size=8192

*.db_create_file_dest=‘+DATA2‘

*.db_domain=‘‘

*.db_name=‘racdg‘

*.db_recovery_file_dest=‘+DATA1‘

*.db_recovery_file_dest_size=4070572032

*.diagnostic_dest=‘/u01/app/oracle‘

*.dispatchers=‘(PROTOCOL=TCP)(SERVICE=racdgXDB)‘

racdg1.instance_number=1

racdg2.instance_number=2

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

*.memory_target=839909376

*.open_cursors=300

*.processes=150

*.remote_listener=‘scan.localdomain:1521‘

*.remote_login_passwordfile=‘exclusive‘

racdg2.thread=2

racdg1.thread=1

racdg2.undo_tablespace=‘UNDOTBS2‘

racdg1.undo_tablespace=‘UNDOTBS1‘

添加参数：

*.db_unique_name=racdg

*.service_names=racdg

*.log_archive_config=‘dg_config=(racdg,racdg_standy)‘

*.log_archive_dest_1=

‘location=use_db_recovery_file_dest

valid_for=(all_logfiles,all_roles)

db_unique_name=racdg‘

*.log_archive_dest_2=

‘service=racdg_standy async

valid_for=(online_logfiles,primary_role)

db_unique_name=racdg_standy‘

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.log_archive_max_processes=30

*.fal_server=racdg_standy

racdg1.fal_client=racdg1

racdg2.fal_client=racdg2

*.db_file_name_convert=

‘/u01/oracle/racdg_standy/datafile/‘,‘+DATA2/racdg/datafile/‘,

‘/u01/oracle/racdg_standy/tempfile/‘,‘+DATA2/racdg/tempfile/‘

*.log_file_name_convert=

‘/u01/oracle/racdg_standy/onlinelog/‘,‘+DATA1/racdg/onlinelog/‘,

‘/u01/oracle/racdg_standy/onlinelog3/‘,‘+DATA2/racdg/onlinelog/‘

*.standby_file_management=auto

注：

这里需要注意fal_server和fal_client的配置，db_unique_name的参数设置。

创建primary DB spfile:

关闭集群

[grid@racdg1 ~]$ srvctl stop database -d racdg

[oracle@racdg1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production onTue Mar 17 18:18:09 2015

Connected to an idle instance.

SQL> createspfile=‘+DATA1/racdg/spfileracdg.ora‘ from pfile=‘/home/oracle/config.pfile‘;

File created.

启动集群：

[grid@racdg1 ~]$ srvctl start database -dracdg

启动后登陆两个节点查看参数：

在Racdg1节点查询参数：

SQL> show parameter fal

NAME TYPE VALUE

----------------------------------------------- ------------------------------

fal_client string racdg1

fal_server string racdg_standy

SQL> show parameter archive

NAME TYPE VALUE

----------------------------------------------- ------------------------------

archive_lag_target integer 0

log_archive_config string dg_config=(racdg,racdg_standy)

log_archive_dest string

log_archive_dest_1 string location=use_db_recovery_file_

dest

valid_for=(all_logfiles,all_

roles)

db_unique_name=racdg

log_archive_dest_10 string

log_archive_dest_11 string

log_archive_dest_12 string

NAME TYPE VALUE

----------------------------------------------- ------------------------------

log_archive_dest_13 string

log_archive_dest_14 string

log_archive_dest_15 string

log_archive_dest_16 string

log_archive_dest_17 string

log_archive_dest_18 string

log_archive_dest_19 string

log_archive_dest_2 string service=racdg_standy async

valid_for=(online_logfiles,p

rimary_role)

db_unique_name=racdg_standy

SQL> show parameter db_file_name_convert

NAME TYPE VALUE

----------------------------------------------- ------------------------------

db_file_name_convert string /u01/app/oracle/racdg_standy/d

atafile, +DATA1/racdg/datafile

/, /u01/app/oracle/racdg_stand

y/tempfile, +DATA1/racdg/tempf

ile/

SQL>

SQL> show parameterlog_file_name_convert

NAME TYPE VALUE

----------------------------------------------- ------------------------------

log_file_name_convert string /u01/app/oracle/racdg_standy/o

nlinelog, +DATA1/racdg/onlinel

og/, /u01/app/oracle/racdg_sta

ndy/onlinelog3, +DATA3/racdg/o

nlinelog/

SQL> show parameter control

NAME TYPE VALUE

----------------------------------------------- ------------------------------

control_file_record_keep_time integer 7

control_files string +DATA1/racdg/controlfile/curre

nt.296.874604041, +DATA3/racdg

/controlfile/current.426.87460

4051

control_management_pack_access string DIAGNOSTIC+TUNING

SQL>

在racdg2节点查看参数:

SQL> show parameter fal

NAME TYPE VALUE

----------------------------------------------- ------------------------------

fal_client string racdg2

fal_server string racdg_standy

SQL> show parameter archive

NAME TYPE VALUE

----------------------------------------------- ------------------------------

archive_lag_target integer 0

log_archive_config string dg_config=(racdg,racdg_standy)

log_archive_dest string

log_archive_dest_1 string location=use_db_recovery_file_

dest

valid_for=(all_logfiles,all_

roles)

db_unique_name=racdg

log_archive_dest_10 string

log_archive_dest_11 string

log_archive_dest_12 string

NAME TYPE VALUE

----------------------------------------------- ------------------------------

log_archive_dest_13 string

log_archive_dest_14 string

log_archive_dest_15 string

log_archive_dest_16 string

log_archive_dest_17 string

log_archive_dest_18 string

log_archive_dest_19 string

log_archive_dest_2 string service=racdg_standy async

valid_for=(online_logfiles,p

rimary_role)

db_unique_name=racdg_standy

SQL> show parameter control

NAME TYPE VALUE

----------------------------------------------- ------------------------------

control_file_record_keep_time integer 7

control_files string +DATA1/racdg/controlfile/curre

nt.296.874604041,+DATA3/racdg

/controlfile/current.426.87460

4051

control_management_pack_access string DIAGNOSTIC+TUNING

SQL> show parameterlog_file_name_convert

NAME TYPE VALUE

----------------------------------------------- ------------------------------

log_file_name_convert string /u01/app/oracle/racdg_standy/o

nlinelog, +DATA1/racdg/onlinel

og/, /u01/app/oracle/racdg_sta

ndy/onlinelog3, +DATA3/racdg/o

nlinelog/

SQL> show parameter db_file_name_convert

NAME TYPE VALUE

----------------------------------------------- ------------------------------

db_file_name_convert string /u01/app/oracle/racdg_standy/d

atafile, +DATA1/racdg/datafile

/, /u01/app/oracle/racdg_stand

y/tempfile, +DATA1/racdg/tempf

ile/

SQL>

5、开启RAC归档

略

PhysicalStandby Database 配置

1、创建primary DB 备份

Rman 备份primary DB

[oracle@racdg1db_backup]$ rman target /

Recovery Manager:Release 11.2.0.1.0 - Production on Tue Mar 17 18:58:43 2015

connected to targetdatabase: RACDG (DBID=1109864007)

RMAN> backup database format‘/home/oracle/db_backup/racdbfull_%T_%t_%u_%s_%p‘;

Starting backup at17-MAR-15

using targetdatabase control file instead of recovery catalog

allocated channel:ORA_DISK_1

channel ORA_DISK_1:SID=32 instance=racdg1 device type=DISK

channel ORA_DISK_1:starting full datafile backup set

channel ORA_DISK_1:specifying datafile(s) in backup set

input datafile filenumber=00001 name=+DATA1/racdg/datafile/system.292.874603637

input datafile filenumber=00002 name=+DATA1/racdg/datafile/sysaux.293.874603643

input datafile filenumber=00003 name=+DATA1/racdg/datafile/undotbs1.294.874603645

input datafile filenumber=00005 name=+DATA1/racdg/datafile/undotbs2.300.874604699

input datafile filenumber=00004 name=+DATA1/racdg/datafile/users.295.874603647

channel ORA_DISK_1:starting piece 1 at 17-MAR-15

channel ORA_DISK_1:finished piece 1 at 17-MAR-15

piecehandle=/home/oracle/db_backup/racdbfull_20150317_874609135_01q22uff_1_1tag=TAG20150317T185854 comment=NONE

channel ORA_DISK_1:backup set complete, elapsed time: 00:02:45

channel ORA_DISK_1:starting full datafile backup set

channel ORA_DISK_1:specifying datafile(s) in backup set

including currentcontrol file in backup set

including currentSPFILE in backup set

channel ORA_DISK_1:starting piece 1 at 17-MAR-15

channel ORA_DISK_1:finished piece 1 at 17-MAR-15

piece handle=/home/oracle/db_backup/racdbfull_20150317_874609302_02q22ukm_2_1tag=TAG20150317T185854 comment=NONE

channel ORA_DISK_1:backup set complete, elapsed time: 00:00:16

Finished backup at17-MAR-15

RMAN>

2、为standby db创建controlfile

SQL> ALTER DATABASE CREATE STANDBYCONTROLFILE AS ‘/home/oracle/db_backup/racdg.ctl‘;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL>

3、复制primaryDB备份文件到standbyDB上

[oracle@racdg1db_backup]$ ll

total 1074652

-rw-r--r-- 1 oracleasmadmin 2330 Mar 16 11:00backcuppfile.pfile

-rw-r----- 1 oracleasmadmin 1062264832 Mar 17 19:01 racdbfull_20150317_874609135_01q22uff_1_1

-rw-r----- 1 oracleasmadmin 18579456 Mar 17 19:02racdbfull_20150317_874609302_02q22ukm_2_1

-rw-r----- 1 oracleasmadmin 18497536 Mar 17 19:06racdg.ctl

-rw-r--r-- 1 oracleoinstall 1575 Mar 6 19:51 standby_db.pfile

[oracle@racdg1db_backup]$ scp racd* oracle@172.20.10.100:/home/oracle/db_backup/

oracle@172.20.10.100‘spassword:

racdbfull_20150317_874609135_01q22uff_1_1 24% 203

4、创建standby initialization parameter

准备工作

部署Physical Dataguard 与 双节点Oracle RAC环境

1、 PhysicalStandby Databases

其特性：

2、 LogicalStandby Databases

其特性：

3、Snapshot StandbyDatabases

人气教程排行

部署Physical Dataguard 与双节点Oracle RAC环境