centos 7下Oracle11.2.0.4 dataguard一主两备配置

于 2018-08-07 16:30:50 发布
阅读量1.9k 收藏 4
点赞数 1
分类专栏： ORACLE 文章标签： Oracle 11g 一主两备 centos7 centos7
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u010257584/article/details/81482792
版权

       应生产需要，需将目前的一主一备（读写分离）的架构改为一主两备（两备库完成读操作）的架构，目前生产数据不多，生产环境允许停机操作，所以这里无需主备切换来实现增加备库，先在测试环境模拟了已有数据的主备库环境，在这基础上增加备库，下面将测试机测试的配置一主两备的主要过程简述如下（主备库安装数据库部分的内容就不赘述）。

      主要的步骤是：1）安装slave2备库；2）配置slave2数据库；3）修改master、slave1的tnsname.ora和initpridb.ora文件；4）同步复制master数据库到slave2数据库，开启slave2的日志应用和Active DataGuard模式；5）测试同步情况。

1.概要信息

1）系统版本

[oracle@master ~]$ cat /etc/redhat-release
CentOS Linux release 7.3.1611 (Core)

2）数据库版本

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

3）数据库的其他信息

主机名

IP

db_name

db_unique_name

instance_name

Tnsname

master

172.16.56.130

pridb

primarydb

pridb

ser_pri

slave1

172.16.56.136

pridb

standbydb

stddb

ser_std

Slave2

172.16.56.139

pridb

standbydb2

stddb2

ser_std2

2.master主库相关配置

1)监听listener.ora配置

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.56.130)(PORT = 1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_NAME = pridb)
     (ORACLE_HOME = /home/app/oracle/product)
      (SID_NAME = pridb)
    )
 )
ADR_BASE_LISTENER = /home/app/oracle

2）tnsnames.ora配置

SER_STD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.56.136)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stddb)
    )
  )
SER_PRI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.56.130)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pridb)
    )
)
SER_STD2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.56.139)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stddb2)
    )
  )

3）初始化参数initpridb.ora配置

初始化各参数的说明推荐参见网友博客https://www.cnblogs.com/Elliot-wang/p/3790878.html

其中：LOG_ARCHIVE_DEST_n(从1到10)定义redo文件路径，该参数必须通过location或service指定归档文件路径，location表示本地路径，service是tnsnames.ora中配置的远程连接服务名，即接收redo数据的standby数据库。

另外，涉及到master和slave1库的初始化参数修改主要是：fal_server、log_archive_config、log_archive_dest_3、log_archive_dest_state_3、log_file_name_convert，可以通过alter system set来修改，也可以直接修改initpridb.ora文件。

*.db_name='pridb'
*.DB_UNIQUE_NAME='primarydb'
*.fal_client='SER_PRI'
*.fal_server='SER_STD','SER_STD2'
*.log_archive_config='DG_CONFIG=(primarydb,standbydb,standbydb2)'
*.LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=primarydb'
*.LOG_ARCHIVE_DEST_2='SERVICE=SER_STD ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=standbydb'
*.log_archive_dest_3='SERVICE=SER_STD2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standbydb2'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
*.log_file_name_convert='/home/app/oracle/fast_recovery_area','/home/app/oracle/fast_recovery_area','/home/app/oracle/fast_recovery_area','/home/app/oracle/fast_recovery_area'
*.remote_login_passwordfile='EXCLUSIVE'
*.STANDBY_FILE_MANAGEMENT='AUTO'

3.slave1备库配置

1)监听listener.ora配置

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.56.136)(PORT = 1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_NAME = stddb)
     (ORACLE_HOME = /home/app/oracle/product)
      (SID_NAME = stddb)
    )
 )
ADR_BASE_LISTENER = /home/app/oracle

2）tnsnames.ora配置

SER_PRI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.56.130)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pridb)
    )
)
SER_STD=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.56.136)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stddb)
    )
)
SER_STD2=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.56.139)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stddb2)
    )
)

3）初始化参数initstddb.ora配置

*.db_name='pridb'
*.DB_UNIQUE_NAME='standbydb'
*.fal_client='SER_STD'
*.fal_server='SER_PRI','SER_STD2'
*.log_archive_config='DG_CONFIG=(primarydb,standbydb,standbydb2)'
*.LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=standbydb'
*.LOG_ARCHIVE_DEST_2='SERVICE=SER_PRI ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=primarydb'
*.log_archive_dest_3='SERVICE=SER_STD2 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standbydb2'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
*.log_file_name_convert='/home/app/oracle/fast_recovery_area','/home/app/oracle/fast_recovery_area','/home/app/oracle/fast_recovery_area','/home/app/oracle/fast_recovery_area'
*.remote_login_passwordfile='EXCLUSIVE'
*.STANDBY_FILE_MANAGEMENT='AUTO'

4.slave2数据库配置

1）监听listener.ora配置

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.56.139)(PORT = 1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_NAME = stddb2)
     (ORACLE_HOME = /home/app/oracle/product)
      (SID_NAME = stddb2)
    )
 )
ADR_BASE_LISTENER = /home/app/oracle

2）tnsnames.ora配置

SER_STD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.56.136)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stddb)
    )
  )
SER_PRI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.56.130)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pridb)
    )
)
SER_STD2 =
  (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.56.139)(PORT = 1521))
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stddb2)
    )
  )

3）初始化参数initstddb2.ora配置

*.db_name='pridb'
*.DB_UNIQUE_NAME='standbydb2'
*.fal_client='SER_STD2'
*.fal_server='SER_PRI','SER_STD'
*.log_archive_config='DG_CONFIG=(primarydb,standbydb,standbydb2)'
*.LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=standbydb2'
*.LOG_ARCHIVE_DEST_2='SERVICE=SER_PRI ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=primarydb'
*.log_archive_dest_3='SERVICE=SER_STD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standbydb'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
*.log_file_name_convert='/home/app/oracle/fast_recovery_area','/home/app/oracle/fast_recovery_area','/home/app/oracle/fast_recovery_area','/home/app/oracle/fast_recovery_area'
*.remote_login_passwordfile='EXCLUSIVE'
*.STANDBY_FILE_MANAGEMENT='AUTO'

5.搭建完毕测试

1）主备库状态

master主库：
SQL> select database_role,switchover_status,protection_mode,protection_level from v$database;

DATABASE_ROLE	 SWITCHOVER_STATUS    PROTECTION_MODE	   PROTECTION_LEVEL
---------------- -------------------- -------------------- --------------------
PRIMARY 	 TO STANDBY	      MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

slave1备库：
SQL>  select database_role,switchover_status,protection_mode,protection_level from v$database;

DATABASE_ROLE	 SWITCHOVER_STATUS    PROTECTION_MODE	   PROTECTION_LEVEL
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY NOT ALLOWED	      MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

slave2备库：
SQL>  select database_role,switchover_status,protection_mode,protection_level from v$database;

DATABASE_ROLE	 SWITCHOVER_STATUS    PROTECTION_MODE	   PROTECTION_LEVEL
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY NOT ALLOWED	      MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

2）主备库同步测试

主库：

11:07:24 SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     114
Next log sequence to archive   116
Current log sequence	       116
11:07:51 SQL> select * from nn.t1;

       TID TNAME
---------- --------------------
	 2 dsf
	 1 sds
	 4 sad
	 3 sda

11:08:12 SQL> delete from nn.t1 where tid=4;

1 row deleted.

11:08:26 SQL> commit;

Commit complete.

11:08:30 SQL> alter system switch logfile;

System altered.

11:08:42 SQL> select * from nn.t1;

       TID TNAME
---------- --------------------
	 2 dsf
	 1 sds
	 3 sda

11:08:46 SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     115
Next log sequence to archive   117
Current log sequence	       117
11:08:58 SQL> show parameter db_unique_name;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 primarydb

slave1备库：

11:07:38 SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     111
Next log sequence to archive   0
Current log sequence	       116
11:07:56 SQL>  select * from nn.t1;

       TID TNAME
---------- --------------------
	 2 dsf
	 1 sds
	 4 sad
	 3 sda

11:08:35 SQL> select * from nn.t1;

       TID TNAME
---------- --------------------
	 2 dsf
	 1 sds
	 3 sda

11:08:48 SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     115
Next log sequence to archive   0
Current log sequence	       117
11:08:56 SQL> show parameter db_unique_name;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 standbydb

slave2备库：

11:07:40 SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     114
Next log sequence to archive   0
Current log sequence	       116
11:07:58 SQL>  select * from nn.t1;

       TID TNAME
---------- --------------------
	 2 dsf
	 1 sds
	 4 sad
	 3 sda

11:08:37 SQL> select * from nn.t1;

       TID TNAME
---------- --------------------
	 2 dsf
	 1 sds
	 3 sda

11:08:50 SQL> archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     115
Next log sequence to archive   0
Current log sequence	       117
11:11:45 SQL> show parameter db_unique_name;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 standbydb2

 

 

 

 

 

山与先生
关注
  • 1
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值