RAC + DataGuard + Dgborker 测试

最新推荐文章于 2018-02-06 11:31:00 发布

clswy9390456

最新推荐文章于 2018-02-06 11:31:00 发布

阅读量138

点赞数

搭建DG

准备工作

一、修改 /etc/hosts/ ，把机器名和 IP 的映射关系加上（包括所有的主库和备库，这一步很重要）

10.10.21.18 dgn

10.10.21.19 dgs

二、在 LISTENER.ORA 中添加静态监听；service_name 应以 _DGMGRL 的格式命名，以下是DGN服务器的静态监听：

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = dgn_DGMGRL)

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

(SID_NAME = dgn)

)

三、创建相应的目录；如：审计日志路径 controlfile 路径等

四、设置 TNS （主备的TNS是一样的，如果是生产环境搭建DG，存在防火墙，需要端口映射，主备库的TNS会不一样）

dgn =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dgn)

)

dgs =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dgs)

)

开始搭建

--主库

-- 备份数据库、控制文件和参数文件及口令文件

RMAN> backup as compressed backupset database format '/home/oracle/orabak/database%U';

Sql> create pfile='/home/oracle/orabak/initdgn.ora' from spfile;

rman> backup format '/home/oracle/orabak/control%U' current controlfile for standby;

cp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwdgn /home/oracle/orabak/

--把以上文件拷贝到备库服务器上（主库和备库的目录应一致）

scp * root@10.10.21.19:/home/oracle/orabak/

--备库

--把 pfile 和口令文件拷贝到 $ORACLE_HOME/dbs 下，并作相应修改，修改后的参数如下：

[oracle@dgs dbs]$ cat initdgs.ora

*.archive_lag_target=0

*.audit_file_dest='/u01/app/oracle/admin/dgs/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/dgs/control01.ctl','/u01/app/oracle/fast_recovery_area/dgs/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='dgn'

*.db_unique_name='dgs'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4385144832

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

*.dispatchers='(PROTOCOL=TCP) (SERVICE=dgnXDB)'

*.log_archive_dest_1='location=/u01/app/oracle/arch'

*.log_archive_max_processes=4

*.log_archive_min_succeed_dest=1

*.open_cursors=300

*.pga_aggregate_target=294649856

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=883949568

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

--更改口令文件的命字，命名规则如下：

orapw

更改后的名字为：orapwdgs

--启动备库到nomount 状态

Sql> startup nomount

rman target 'sys/"oracle"'@dgn auxiliary /

duplicate target database for standby nofilenamecheck;

--创建 spfile

Sql> create spfile from pfile;

--设置DG 参数

--主库

sql> alter system set log_archive_config='dg_config=(dgn,dgs)';

sql> alter system set log_archive_dest_2='service=dgs lgwr async db_unique_name=dgs valid_for=(primary_role,online_logfile)';

sql> alter system set fal_server=dgs;

sql> alter system set fal_client=dgn;

sql> alter system set standby_file_management=auto;

sql> alter database force logging;

sql> alter system set log_archive_max_processes=4 scope=spfile;

--备库

sql> alter system set log_archive_config='dg_config=(dgn,dgs)';

sql> alter system set log_archive_dest_2='service=dgn lgwr async db_unique_name=dgn valid_for=(primary_role,online_logfile)';

sql> alter system set fal_server=dgn;

sql> alter system set fal_client=dgs;

sql> alter system set standby_file_management=auto;

sql> alter database force logging;

sql> alter system set log_archive_max_processes=4 scope=spfile;

--添加standby log

--主库

alter database add standby logfile group 4 ('/u01/app/oracle/oradata/dgn/standbylog/standby_redo0401.log') size 50m;

alter database add standby logfile group 5 ('/u01/app/oracle/oradata/dgn/standbylog/standby_redo0501.log') size 50m;

alter database add standby logfile group 6 ('/u01/app/oracle/oradata/dgn/standbylog/standby_redo0601.log') size 50m;

--备库

--主库添加standby log

alter database add standby logfile group 4 ('/u01/app/oracle/oradata/standbylog/standby_redo0401.log') size 50m;

alter database add standby logfile group 5 ('/u01/app/oracle/oradata/standbylog/standby_redo0501.log') size 50m;

alter database add standby logfile group 6 ('/u01/app/oracle/oradata/standbylog/standby_redo0601.log') size 50m;

--在备库上查看DG进程情况

SQL> select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE# ,STATUS from v$managed_standby;

PROCESS CLIENT_P THREAD# SEQUENCE# STATUS

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

ARCH ARCH 0 0 CONNECTED

ARCH ARCH 1 4 CLOSING

ARCH ARCH 0 0 CONNECTED

ARCH ARCH 1 3 CLOSING

MRP0 N/A 1 5 APPLYING_LOG

RFS ARCH 0 0 IDLE

RFS UNKNOWN 0 0 IDLE

RFS LGWR 1 5 IDLE

配置 dgbroker

--设置 dg_broker_config_file1 、 dg_broker_config_file2

dg_broker_config_file1 、 dg_broker_config_file2 的路径为 $ORACLE_HOME/dbs/，如果是单实例，不用再去改了，如果是RAC，则需要设置到一个所有实例都能访问的共享位置，如 ASM，只有当 dg_broker_start 为 false 的时候才可以修改。

--开启dgbroker （主库和备库）

SQL> alter system set dg_broker_start=true;

--创建configuration

DGMGRL> create CONFIGURATION dgtestbroker as PRIMARY DATABASE IS dgn CONNECT IDENTIFIER IS dgn;

--添加备库

DGMGRL> add database dgs AS CONNECT IDENTIFIER IS dgs MAINTAINED AS PHYSICAL;

-- 使配置文件和数据库可用

DGMGRL> enable configuration

DGMGRL> enable database dgs

--查看配置信息

DGMGRL> show configuration

Configuration - dgtestbroker

Protection Mode: MaxPerformance

Databases:

dgn - Primary database

dgs - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

--switchover ，现在dgn是主库，dgs是备库，通过switchover 使 dgs 成为主库 dgn 成为备库

DGMGRL> switchover to dgs

Performing switchover NOW, please wait...

Operation requires a connection to instance "dgs" on database "dgs"

Connecting to instance "dgs"...

Connected.

New primary database "dgs" is opening...

Operation requires startup of instance "dgn" on database "dgn"

Starting instance "dgn"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "dgs"

--从下面的信息可以看出已经切换过来了

DGMGRL> show configuration

Configuration - dgtestbroker

Protection Mode: MaxPerformance

Databases:

dgs - Primary database

dgn - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

--Failover ，需要连接备库执行

DGMGRL> failover to dgn

--使用DGBROKER 作 failover 后，其DG状态会变为 reinstated如下：

DGMGRL> show configuration

Configuration - dgtestbroker

Protection Mode: MaxPerformance

Databases:

dgn - Primary database

dgs - Physical standby database (disabled)

ORA-16661: the standby database needs to be reinstated

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

--恢复步骤如下：

一、重新搭建DG

二、DGMGRL> reinstate database dgs

三、DGMGRL> enable database dgs

注：RAC（主） + 单实例（备库）的 BROKER 搭建方法是一样的，只是在设置 RAC 的dg_broker_config_file1 和 dg_broker_config_file2 时，要设置到一个共享路径下，使所有实例都能访问，如ASM等；在备库上的 /etc/hosts中设置到主库的IP 和主机名的映射，否则switchover 时会出问题。