------------------------------TigerLiu的技术园地(lc724234511@163.com)--------------------------------
参考文档：
官方文档：https://docs.oracle.com/cd/B19306_01/server.102/b14230/concepts.htm#i1019612
环境：redhat linux6.5 Oracle 11.2.0.2
用途：当主库发生宕机时备库会自动切换为主库，保证数据库的高可用
搭建思路：
1、配置 Oracle Net(56.112和56.221)
a、静态注册数据库服务
b、配置TNS
2、配置ADG主数据库(56.221)
a、配置local_listener
b、配置force logging
c、创建 pfile
d、创建口令文件
e、启用远程登录
f、设置 db_unique_name
g、配置快速恢复区
h、启用自动备用文件管理
i、log_archive_config
j、创建备用重做日志
k、启用存档日志模式 ---mount状态设置，重启生效
l、启用闪回数据库 ---mount状态设置，重启生效
m、启用最高可用性模式 ---mount状态设置，重启生效
3、创建物理备用数据库(56.112)
a、配置口令文件
b、创建 oratab 条目
c、创建 init.ora 文件
d、以非安装方式启动备用数据库
e、pfile中创建所需目录
f、使用 RMAN 创建备用数据库
g、启用闪回数据库
4、配置DG broker(56.117)
a、设置 Data Guard Broker 配置文件的位置--主备库都要设置
b、安装11gR2客户端 -----此步忽略
c、设置tns
d、在主数据库上启动 dgmgrl 实用程序并以 SYS 身份进行连接
e、添加主数据库信息
f、添加备用数据库
g、编辑数据库属性
h、启用配置
i、启用fast_start
j、启动观察器
5、启用并测试 FSFO
a、switchover测试
b、failover测试
搭建具体架构如下：
| DB_NAME | SID | HOSTNAME | DB_UNIQUE_NAME | TNS_NAME |
| prim112 | prim112 | standby | prim112_a | prim112_a、prim112_a_static |
| Perfmonitor2 | prim112_b | prim112_b、prim112_b_static |
1、配置 Oracle Net(56.112和56.221)
a、静态注册数据库服务
>>>>>>>>>56.221
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=prim112)
(SDU=32767)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(GLOBAL_DBNAME=prim112_a_static)
)
(SID_DESC=
(SID_NAME=prim112)
(SDU=32767)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(GLOBAL_DBNAME=prim112_a_dgmgrl)
)
)
>>>>>>>>56.112
LISTENER =
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=Perfmonitor2)(PORT=1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=prim112)
(SDU=32767)
(ORACLE_HOME=/u02/app/oracle/product/11.2.0/db_1)
(GLOBAL_DBNAME=prim112_b_static)
)
(SID_DESC=
(SID_NAME=prim112)
(SDU=32767)
(ORACLE_HOME=/u02/app/oracle/product/11.2.0/db_1)
(GLOBAL_DBNAME=prim112_b_dgmgrl)
)
)
启动监听并检查状态：
lsnrctl status
>>>>>>>>>56.221
Services Summary...
Service "prim112_a_dgmgrl" has 1 instance(s).
Instance "prim112", status UNKNOWN, has 1 handler(s) for this service...
Service "prim112_a_static" has 1 instance(s).
Instance "prim112", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
>>>>>>>>>56.112
Service "prim112_b_dgmgrl" has 1 instance(s).
Instance "prim112", status UNKNOWN, has 1 handler(s) for this service...
Service "prim112_b_static" has 1 instance(s).
Instance "prim112", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
b、配置TNS
>>>>>>>>>56.221&56.112
prim112_a=
(description=
(SDU=32767)
(address_list=
(address=(protocol=tcp)(host=standby)(port=1521))
)
(connect_data=
(service_name=prim112_a)
(server=dedicated)
)
)
prim112_b=
(description=
(SDU=32767)
(address_list=
(address=(protocol=tcp)(host=Perfmonitor2)(port=1521))
)
(connect_data=
(service_name=prim112_b)
(server=dedicated)
)
)
prim112_a_static=
(description=
(SDU=32767)
(address_list=
(address=(protocol=tcp)(host=standby)(port=1521))
)
(connect_data=
(service_name=prim112_a_static)
(server=dedicated)
)
)
prim112_b_static=
(description=
(SDU=32767)
(address_list=
(address=(protocol=tcp)(host=Perfmonitor2)(port=1521))
)
(connect_data=
(service_name=prim112_b_static)
(server=dedicated)
)
)
tnsping验证一下
tnsping prim112_b_static
tnsping prim112_a_static
tnsping prim112_b
tnsping prim112_a
2、配置ADG主数据库(56.221)
a、配置local_listener
配置local_listener
alter system set local_listener='(address=(host=standby)(port=1521)(protocol=tcp))';
alter system register;
SQL> show parameter local_listener;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (address=(host=standby)(port=1
521)(protocol=tcp))
b、配置force logging
alter database force logging;
SQL> select force_logging from v$database;
FOR
---
YES
c、创建 pfile
show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_1/dbs/spfileprim112.ora
create pfile='/home/oracle/initprim112.ora' from spfile;
d、创建口令文件
orapwd file=$ORACLE_HOME/dbs/orapwprim112
e、启用远程登录
alter system set remote_login_passwordfile=exclusive scope=spfile;
show parameter remote_login_passwordfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
f、设置 db_unique_name
alter system set db_unique_name = prim112_a scope=spfile;
show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string PRIM112_A
g、配置快速恢复区
mkdir -p /u01/fra
chown -R oracle:oinstall /u01/fra
chmod -R 775 /u01/fra
alter system set db_recovery_file_dest_size = 20g scope=both;
alter system set db_recovery_file_dest = '/u01/fra' scope=both;
show parameter db_recovery_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/fra
db_recovery_file_dest_size big integer 20G
h、启用自动备用文件管理
alter system set standby_file_management=auto;
show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
i、设置log_archive_config
alter system set log_archive_config='DG_CONFIG=(prim112_b)' scope=both;
show parameter log_archive_config
j、创建备用重做日志
select bytes, count(group#) from v$log group by bytes;
select max(group#) from v$log;
alter database add standby logfile group 11 '/oradata/prim112/stby-t01-g11-m1.log' size 52428800;
alter database add standby logfile group 12 '/oradata/prim112/stby-t01-g12-m1.log' size 52428800;
alter database add standby logfile group 13 '/oradata/prim112/stby-t01-g13-m1.log' size 52428800;
alter database add standby logfile group 14 '/oradata/prim112/stby-t01-g14-m1.log' size 52428800;
select group#, type, member from v$logfile where type = 'STANDBY';
k、启用存档日志模式 ---mount状态设置，重启生效
alter database archivelog;
select log_mode from v$database;
l、启用闪回数据库 ---mount状态设置，重启生效
alter database flashback on;
alter system set db_flashback_retention_target = 60 scope=both;
select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
show parameter db_flashback_retention_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 60
m、启用最高可用性模式 ---mount状态设置，重启生效
alter database set standby database to maximize availability;
select protection_mode from v$database;
PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY
3、创建物理备用数据库(56.112)
a、配置口令文件
将主数据库中的口令文件复制到备用数据库
scp orapwprim112 192.168.56.112:/u02/app/oracle/product/11.2.0/db_1/dbs/
b、创建 oratab 条目
cat /etc/oratab
prim112:/u02/app/oracle/product/11.2.0/db_1:Y
c、创建 init.ora 文件
vi /u02/app/oracle/product/11.2.0/db_1/dbs/initprim112.ora
db_name = prim112
d、以非安装方式启动备用数据库
startup nomount
e、pfile中创建所需目录
mkdir -p /u02/app/oracle/admin/prim112/adump
chmod -R 775 /u02/app/oracle/admin/prim112/adump
chown -R oracle:oinstall /u02/app/oracle/admin/prim112/adump
mkdir -p /oradata/prim112
chmod -R 775 /oradata/prim112
chown -R oracle:oinstall /oradata/prim112
mkdir -p /u01/fra
chmod -R 775 /u01/fra
chown -R oracle:oinstall /u01/fra
mkdir -p /oradata/arch/
chmod -R 775 /oradata/arch/
chown -R oracle:oinstall /oradata/arch/
ls -ld /oradata/arch/
ls -ld /u01/fra
ls -ld /oradata/prim112
ls -ld /u02/app/oracle/admin/prim112/adump
f、使用 RMAN 创建备用数据库
运行 RMAN 实用程序并连接到目标（主数据库）和辅助对象（新备用数据库）。
rman target sys/oracle@prim112_a_static auxiliary sys/oracle@prim112_b_static
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='prim112_b'
SET log_archive_config=''
SET log_file_name_convert= ' ',' '
SET local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Perfmonitor2)(PORT=1521)))'
SET audit_file_dest='/u02/app/oracle/admin/prim112/adump'
SET control_files='/oradata/prim112/control01.ctl','/oradata/prim112/control02.ctl'
SET db_recovery_file_dest='/u01/fra'
SET diagnostic_dest='/u02/app/oracle'
SET log_archive_config='DG_CONFIG=(prim112_a)'
SET log_archive_dest_1='LOCATION=/oradata/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim112_b'
SET log_archive_dest_2='SERVICE=prim112_a ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prim112_a'
NOFILENAMECHECK;
g、启用闪回数据库
alter database flashback on;
alter system set db_flashback_retention_target = 60 scope=both;
select flashback_on from v$database;
show parameter db_flashback_retention_target
alter database set standby database to maximize availability;
select protection_mode from v$database;
4、配置DG broker(56.117)
a、设置 Data Guard Broker 配置文件的位置--主备库都要设置
设置（主数据库和备用数据库）
alter system set dg_broker_config_file1='/u01/app/oracle/admin/prim112/dgbroker/dg1prim112.dat';
alter system set dg_broker_config_file2='/u02/app/oracle/admin/prim112/dgbroker/dg2prim112.dat';
show parameter dg_broker_config_file
创建目录对应（主数据库和备用数据库）
mkdir -p /u01/app/oracle/admin/prim112/dgbroker/
chown -R oracle:oinstall /u01/app/oracle/admin/prim112/dgbroker/
chmod -R 775 /u01/app/oracle/admin/prim112/dgbroker/
mkdir -p /u02/app/oracle/admin/prim112/dgbroker/
chown -R oracle:oinstall /u02/app/oracle/admin/prim112/dgbroker/
chmod -R 775 /u02/app/oracle/admin/prim112/dgbroker/
ls -ld /u01/app/oracle/admin/prim112/dgbroker/
ls -ld /u02/app/oracle/admin/prim112/dgbroker/
启用（主数据库和备用数据库）
alter system set dg_broker_start=true;
show parameter dg_broker_start
b、安装11gR2客户端 -----此步忽略
c、设置tns
prim112_a=
(description=
(SDU=32767)
(address_list=
(address=(protocol=tcp)(host=standby)(port=1521))
(address=(protocol=tcp)(host=Perfmonitor2)(port=1521))
)
(connect_data=
(service_name=prim112_a)
(server=dedicated)
)
)
prim112_b=
(description=
(SDU=32767)
(address_list=
(address=(protocol=tcp)(host=Perfmonitor2)(port=1521))
(address=(protocol=tcp)(host=standby)(port=1521))
)
(connect_data=
(service_name=prim112_b)
(server=dedicated)
)
)
prim112_a_static=
(description=
(SDU=32767)
(address_list=
(address=(protocol=tcp)(host=standby)(port=1521))
)
(connect_data=
(service_name=prim112_a_static)
(server=dedicated)
)
)
prim112_b_static=
(description=
(SDU=32767)
(address_list=
(address=(protocol=tcp)(host=Perfmonitor2)(port=1521))
)
(connect_data=
(service_name=prim112_b_static)
(server=dedicated)
)
)
d、在主数据库上启动 dgmgrl 实用程序并以 SYS 身份进行连接
dgmgrl sys/oracle@prim112_a
e、添加主数据库信息
create configuration 'prim112_dg' as
primary database is prim112_a
connect identifier is prim112_a;
Configuration "prim112_dg" created with primary database "prim112_a"
f、添加备用数据库
add database prim112_b as
connect identifier is prim112_b
maintained as physical;
Database "prim112_b" added
验证配置
show configuration
Configuration - prim112_dg
Protection Mode: MaxAvailability
Databases:
prim112_a - Primary database
prim112_b - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
g、编辑数据库属性
edit database prim112_a set property NetTimeout=15; ---连接超时时间
edit database prim112_b set property NetTimeout=15;
edit database prim112_a set property LogXptMode='SYNC'; ---连接日志同步模式为同步
edit database prim112_b set property LogXptMode='SYNC';
edit configuration set protection mode as maxavailability; ----保护模式为最大可用模式
edit database prim112_a set property FastStartFailoverTarget='prim112_b'; ---设置failover目标库
edit database prim112_b set property FastStartFailoverTarget='prim112_a';
使用Maximum performance的时候，参数（备库）LogXptMode设置为async就好
使用Maximum protection或者Maximum availability的时候，参数（备库）LogXptMode要设置为sync。
h、启用配置
enable configuration;
Enabled.
验证配置
show configuration
Configuration - prim112_dg
Protection Mode: MaxAvailability
Databases:
prim112_a - Primary database
prim112_b - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
i、启用fast_start
enable fast_start failover;
DGMGRL> show FAST_START FAILOVER;
Fast-Start Failover: ENABLED
Threshold: 30 seconds
Target: prim112_b
Observer: (none)
Lag Limit: 30 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Configurable Failover Conditions
Health Conditions:
Corrupted Controlfile YES
Corrupted Dictionary YES
Inaccessible Logfile NO
Stuck Archiver NO
Datafile Offline YES
Oracle Error Conditions:
show database prim112_a statusreport
DGMGRL> show database prim112_a statusreport
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
* WARNING ORA-16819: fast-start failover observer not started
j、启动观察器
dgmgrl sys/password@prim112_a
start observer;
observer started
此时，终端会话将呈现挂起状态。
5、启用并测试 FSFO
a、switchover测试
dgmgrl sys/oracle@prim112_a
switchover to prim112_b
日志如下，主库自动关闭，备库同步完日志后切换为主库。
Performing switchover NOW, please wait...
New primary database "prim112_b" is opening...
Operation requires shutdown of instance "prim112" on database "prim112_a"
Shutting down instance "prim112"...
ORA-01092: ORACLE instance terminated. Disconnection forced
Please complete the following steps to finish switchover:
shut down instance "prim112" of database "prim112_a"
start up and mount instance "prim112" of database "prim112_a"
再次查看dg状态需要重新连接
dgmgrl sys/oracle@prim112_a
DGMGRL> show configuration
Configuration - prim112_dg
Protection Mode: MaxAvailability
Databases:
prim112_b - Primary database
Error: ORA-16825: multiple errors or warnings, including fast-start failover-related errors or warnings, detected for the database
prim112_a - (*) Physical standby database
Error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Fast-Start Failover: ENABLED
Configuration Status:
ERROR
手工启动原主库后，dg状态自动恢复
56.221：startup
56.117：dgmgrl sys/oracle@prim112_a
show configuration verbose
Configuration - prim112_dg
Protection Mode: MaxAvailability
Databases:
prim112_a - Primary database
prim112_b - (*) Physical standby database
(*) Fast-Start Failover target
Fast-Start Failover: ENABLED
Threshold: 30 seconds
Target: prim112_b
Observer: dgbroker
Lag Limit: 30 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE
Configuration Status:
SUCCESS
b、failover测试
将56.112主机强制关机
56.221自动切换为主
dg broker日志：
15:52:41.84 Monday, December 11, 2017
Initiating Fast-Start Failover to database "prim112_b"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "prim112_b"
15:52:50.87 Monday, December 11, 2017
重启prim112_a后自动进行日志应用，dg_broker恢复
531
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
