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