Oracle DG-Broker主备高可用一键切换操作系统：
[oracle@node139 ~]$ uname -a
Linux node139 4.1.12-61.1.28.el6uek.x86_64 #2 SMP Thu Feb23 20:03:53 PST 2017 x86_64 x86_64 x86_64 GNU/Linux
[oracle@node139 ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.9 (Santiago)
数据库版本：
Oracle: Oracle 11.2.0.4.0
DataGuard 配置前题条件：
两台服务器已配置完成一主一备的DG环境,在配置DG broker之前需要确保Dataguard配置正常且主库和备库均使用spfile.
两台服务器的保护模式一定要一样
主库ORA11G_PRIMARY：
02:21:10SYS@ DB-Primary>select name,dbid,database_role,protection_mode fromv$database;
NAME DBID DATABASE_ROLE PROTECTION_MODE
--------- ---------- ------------------------------------
ORCL 1518610607PRIMARY MAXIMUM AVAILABILITY
备库ORA11G_STANDBY：
02:21:18SYS@ ORA11G_STANDBY>select name,dbid,database_role,protection_mode fromv$database;
NAME DBID DATABASE_ROLE PROTECTION_MODE
--------- ---------- ---------------- --------------------
ORCL 1518610607PHYSICAL STANDBY MAXIMUM AVAILABILITY
主库开启flashback database：
02:22:53 SYS@ DB-Primary>select name,flashback_on fromv$database;
NAME FLASHBACK_ON
--------- ------------------
DB-Primary NO
02:23:12 SYS@ DB-Primary>show parameter recovery
NAME TYPE VALUE
------------------------------------ -----------------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_ area
db_recovery_file_dest_size big integer 4122M
recovery_parallelism integer 0
02:23:44 SYS@ DB-Primary>alter database flashback on;
Database altered.
Elapsed: 00:00:01.60
02:24:03 SYS@ DB-Primary>select name,flashback_on fromv$database;
NAME FLASHBACK_ON
--------- ------------------
DB-Primary YES
Elapsed: 00:00:00.00
备库也要开启flashbackdatabase：
SQL> select flashback_on fromV$database;
FLASHBACK_ON
------------------
NO
SQL> alter database recover managedstandby database cancel;
SQL> shutdown immediate
SQL> startup nomount
SQL> alter database mount standbydatabase;
SQL> alter database flashback on;
SQL> shutdown immediate
SQL> startup nomount
SQL> alter database mount standbydatabase;
Database altered.
SQL> alter database recover managedstandby database using current logfile disconnect from session;
Database altered.
SQL> select flashback_on,name fromv$database;
FLASHBACK_ON NAME
------------------ ---------
YES ORCL
DGBroker 配置：
· 1.主库设置
· 2.备库设置
· 3.创建DataGuard Broker配置
· 4.添加standby database到配置
· 5.开启配置
· 6.验证配置和switch over
DG配置环境：
Database Name
|
DB-Primary
|
DB-Standby
|
Database Unqie Name
|
ORA11G_PRIMARY
|
ORA11G_STANDBY
|
Net Service Name
|
ORA11G_PRIMARY
|
ORA11G_STANDBY
|
Version
|
11.2.0.4 Linux for x86_64
|
11.2.0.4 Linux for x86_64
|
1.主库设置
DB_BROKER_CONFIG_FILEn参数用于指定DataGuard配置文件的路径,
DG_BROKER_START参数设置实例启动的时候是否自动启动Broken.
SQL>alter system setdg_broker_config_file1='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/dr1ORA11G_PRIMARY.dat' scope=both sid='*';
System altered.
SQL>alter system setdg_broker_config_file2='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/dr2ORA11G_PRIMARY.dat'scope=both sid='*';
System altered.
SQL>alter system set DG_BROKER_START=TRUE scope=both sid='*';
Systemaltered.
设置完上面的参数后,我们还需要修改监听listener.ora文件.我们必须添加一个静态注册的GLOBAL_DBNAME为db_unique_name_DGMGRL.db_domain,这个GLOBAL_DBNAME会在DGMGRL重启数据库的时候用到.通过DGMGRL重启数据库时DMON进程会先将数据库关闭,然后DGMGRL在通过静态监听中的service_name连接到数据库,发送启动的命令.如果不这么做的话,在做switchover的时候我们容易遇到TNS-12514错误
[oracle@node139 admin]$ cat listener.ora
# listener.ora Network Configuration File:/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME=ora11g_primary_DGMGRL)
(SERVICE_NAME = ora11g_primary)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0.4/dbhome_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =node139)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
这里需要说明的是GLOBAL_DBNAME=<db_unique_name>_DGMGRL,<db_domain>.
SERVICE_NAME=<db_unique_name>,<db_domain>.
SID_NAME=echo $ORACLE_SID.
ORACLE_HOME=echo $ORACLE_HOME
tnsnames配置：
主库
[oracle@node139 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File:/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORA11G_PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =node139)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora11g_primary)
)
)
ORA11G_STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =node140)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora11g_standby)
)
)
2.备库设置
和主库设置一样,同样需要设置DB_BROKER_CONFIG_FILEn参数和DG_BROKER_START参数.还有静态监听.
SQL>alter system setdg_broker_config_file1='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/dr1ORA11G_STANDBY.dat' scope=both sid='*';
Systemaltered.
SQL>alter system setdg_broker_config_file2='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/dr2ORA11G_STANDBY.dat'scope=both sid='*';
Systemaltered.
SQL>alter system set DG_BROKER_START=TRUE scope=both sid='*';
Systemaltered.
备库监听器配置：
[oracle@node140 admin]$ cat listener.ora
# listener.ora Network Configuration File:/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME= ora11g_standby_orcl)
(SERVICE_NAME=ora11g_standby)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0.4/dbhome_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME=ora11g_standby_DGMGRL)
(SERVICE_NAME=ora11g_standby)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0.4/dbhome_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =node140)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
备库tnsnames 配置：
[oracle@node140 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File:/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORA11G_PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =node139)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora11g_primary)
)
)
ORA11G_STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =node140)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora11g_standby)
)
)
3.创建DataGuard Broker配置
在主库上使用dgmgrl连接到数据库.创建配置.
添加别名记录：可以使用上下键翻页：
[oracle@node139 ~]$ cat /home/oracle/.bash_profile
alias dgmgrl='rlwrapdgmgrl'
[oracle@dg1 admin]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit ProductionCopyright (c) 2000,2009, Oracle. All rights reserved.Welcome to DGMGRL, type "help" forinformation.
DGMGRL> connect sys/oracleConnected.
DGMGRL>create configuration 'ORA11G_PRIMARYcfg' as primary databaseis 'ORA11G_PRIMARY' connect identifier is 'ORA11G_PRIMARY';
Configuration"ORA11G_PRIMARYcfg" created with primary database"ORA11G_PRIMARY"
DGMGRL>
这里的参数要说明一下.ORA11G_PRIMARYcfg是配置的名称,这里可以随便填.
PRIMARY DATABASE IS ‘ORA11G_PRIMARY′ ,这儿的ORA11G_PRIMARY是指database的db_unique_name,
而connect identifier is ‘ORA11G_PRIMARY′这里的ORA11G_PRIMARY是指tnsname.ora连接到主库的net service name.
我们可以使用show confiruration查看配置信息.
DGMGRL>show configuration
Configuration- ORA11G_PRIMARYcfg
ProtectionMode: MaxAvailability
Databases:
ORA11G_PRIMARY - Primary database
Fast-StartFailover: DISABLED
ConfigurationStatus:
DISABLED
4.添加standby database到配置
DGMGRL>add database 'ORA11G_STANDBY' as connect identifier is ORA11G_STANDBY maintained as physical;
Database "ORA11G_STANDBY" added
这里的参数要说明一下.add database ‘ORA11G_STANDBY′ ,这儿的ORA11G_STANDBY是指database的db_unique_name,
而AS CONNECT IDENTIFIER IS ORA11G_STANDBY 这里的ORA11G_STANDBY是指tnsname.ora连接到standby database的net service name.
DGMGRL>show configuration
Configuration- ORA11G_PRIMARYcfg
ProtectionMode: MaxAvailability
Databases:
ORA11G_PRIMARY - Primary database
ORA11G_STANDBY - Physical standby database
Fast-StartFailover: DISABLED
ConfigurationStatus:
DISABLED
5.开启配置
DGMGRL> enable Configuration
DGMGRL> Enabled.
DGMGRL>show configuration;
Configuration- ORA11G_PRIMARYcfg
ProtectionMode: MaxAvailability
Databases:
ORA11G_PRIMARY - Primary database
ORA11G_STANDBY - Physical standby database
Fast-StartFailover: DISABLED
ConfigurationStatus:
SUCCESS
修改DG broker 参数：
编辑数据库属性
LogXptMode
默认情况下，Broker 将主数据库设置为使用异步日志传输。针对最高可用性环境时，需要将此设置更改为同步。
NetTimeout
NetTimeout 属性指定在考虑连接丢失前 LGWR 将阻塞对同步模式中来自备用数据库的确认的等待秒数（对应于log_archive_dest_n 的 NET_TIMEOUT 选项）。默认值为 30 秒。使用最高可用性模式时，考虑降低该值以减少备用数据库不可用时的提交阻塞时间。选择一个足够高的值，避免由间歇性网络问题引起的假性断开。本示例使用 10 秒钟。
ObserverConnectIdentifier（11g 及更高版本）
Oracle数据库 11g 将 ObserverConnectIdentifier 数据库属性添加到 Broker 配置，使您可以为观察器指定一个连接标识符，用于监视主数据库和故障切换目标。默认情况下，观察器和 Data Guard 使用相同的连接标识符在主数据库和备用数据库间进行重做传输和信息交换（Oracle 数据库 11g 中为DGConnectIdentifier，Oracle 数据库 10g 中为InitialConnectIdentifier）。ObserverConnectIdentifier 使您可以指定观察器使用不同的连接标识符。例如，您可以用此参数使观察器使用与客户端应用程序相同的连接标识符监视数据库。
在本指南中，我们将在保留其他属性的默认值，但您应熟悉所有 Broker 配置和数据库属性。Data Guard Broker 文档（10g 和 11g）第 9 章中包含了每个属性的描述。其中一些属性已经在这两个版本中有所改动。
注：Broker 的许多数据库属性与数据库 spfile 参数相对应。Broker 在角色转换、数据库启动/关闭以及其他事件期间，通过执行相应的 ALTER SYSTEM 命令来维护这些参数。如果这些参数在 Broker 外部进行了修改，将出现警告。要查看特定参数，使用“show database ... StatusReport”命令。
editdatabase db1_a set property LogXptMode='SYNC';
edit database db1_a set property NetTimeout=10;
edit database db1_b set property NetTimeout=10;
其中最大性能要对应异步
MaxPerformance/async
只有最大可用才能对应同步
MaxAvailability/sync
在这里一下要以SYNC的方式来配置,如果通过ASYNC不能配置成功
DGMGRL> edit configuration set protection modeas maxavailability; #配置主备库使用的保护模式
DGMGRL> edit database 'ORA11G_PRIMARY' set property' logxptmode'='sync';
Property"logxptmode" updated
DGMGRL> edit database 'ORA11G_STANDBY' set property 'logxptmode' ='sync';
Property"logxptmode" updated
DGMGRL>
DGMGRL> enable fast_start failover;
Enabled.
DGMGRL>show configuration;
Configuration- ORA11G_PRIMARYcfg
ProtectionMode: MaxAvailability
Databases:
ORA11G_PRIMARY - Primary database
Warning: ORA-16819: fast-start failover observer not started
ORA11G_STANDBY - (*) Physical standby database
Warning: ORA-16819: fast-start failover observer not started
Fast-StartFailover: ENABLED
ConfigurationStatus:
WARNING
DGMGRL>
DGMGRL> start observer;
Observerstarted
在备库上查看：
[oracle@shsrv~]$dgmgrl
DGMGRLfor Linux: Version 11.2.0.3.0 - 64bit Production
Copyright(c) 2000, 2009, Oracle. All rights reserved.
Welcometo DGMGRL, type "help" for information.
DGMGRL>show configuration;
notlogged on
DGMGRL>connect sys/oracle@ORA11G_PRIMARY
Connected.
DGMGRL>show configuration;
Configuration- ORA11G_PRIMARYcfg
ProtectionMode: MaxAvailability
Databases:
ORA11G_PRIMARY - Primary database
ORA11G_STANDBY - (*) Physical standby database
Fast-StartFailover: ENABLED
ConfigurationStatus:
SUCCESS
验证FFS:
主库：
SQL>col FS_FAILOVER_OBSERVER_HOST for a30
SQL> selectfs_failover_observer_present,fs_failover_observer_host,fs_failover_threshold from v$database;
FS_FAIL FS_FAILOVER_OBSERVER_HOST FS_FAILOVER_THRESHOLD
------- ---------------------------------------------------
YES node139 30
备库：
SQL> col FS_FAILOVER_OBSERVER_HOST fora30
SQL> selectfs_failover_observer_present,fs_failover_observer_host,fs_failover_threshold from v$database;
FS_FAIL FS_FAILOVER_OBSERVER_HOST FS_FAILOVER_THRESHOLD
------- ---------------------------------------------------
YES node139 30
查看主库配置信息：
DGMGRL> show database verboseora11g_primary;
查看备库配置信息：
DGMGRL> show database verboseORA11G_STANDBY;
6.验证swictover
DGMGRL>switchover to 'ORA11G_STANDBY';
Performingswitchover NOW, please wait...
Newprimary database "ORA11G_STANDBY" is opening...
Operationrequires shutdown of instance "ORA11G_PRIMARY" on database
"ORA11G_PRIMARY"Shuttingdown instance "ORA11G_PRIMARY"..
.ORACLEinstance shut down.Operation requires startup of instance"ORA11G_PRIMARY" on database
"ORA11G_PRIMARY"Startinginstance "ORA11G_PRIMARY"...
ORACLEinstance started.
Databasemounted.Switchover succeeded,
newprimary is "ORA11G_STANDBY"
ORA11G_PRIMARY：
SQL>select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
------------------------------------
PHYSICALSTANDBY NOT ALLOWED
ORA11G_STANDBY：
SQL>select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
------------------------------------
PRIMARY TO STANDBY
查看切换后的配置文件：
DGMGRL> show configuration;
Configuration - primarycfg
Protection Mode: MaxAvailability
Databases:
ora11g_standby - Primary database
ora11g_primary - (*) Physical standbydatabase
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS
7、failover 验证：
在主库上做shutdown abort的操作，备库会主动的切换为主库！
中止主数据库。
shutdown abort
观察器日志：
Initiating Fast-Start Failover to database
"ORA11G_PRIMARY"...Performing failover NOW,please wait...
Failover succeeded, new primary is"ORA11G_STANDBY"
通过登录到新主数据库上的 dgmgrl 查看 Broker 配置。您会看到之前的主数据库现在已禁用。
dgmgrl sys/oracle@ORA11G_STANDBY configuration
Configuration Name:
FSF Enabled: YES
Protection Mode: MaxAvailability
Databases:
ORA11G_STANDBY - Primary database
ORA11G_PRIMARY - Physical standby database (disabled) -
Fast-Start Failover target
Fast-Start Failover: ENABLED
Current status for "FSF":
Warning: ORA-16608: one or more databases have warnings
查看测试数据
登录到新的主数据库并验证更改与之前主数据库一致。
select count(*) from x;
COUNT(*)
----------
68855
将之前中止的主数据库恢复为备用数据库
通过安装数据库启动恢复。注意，数据库此时不会打开。仅当观察器验证主数据库仍为主数据库后，主数据库才会打开。如果观察器发现该数据库不再是主数据库，会尝试将其恢复为故障切换的目标备用数据库。
恢复的第一步是将数据库闪回到备用数据库变为主数据库的 SCN 处（新主数据库上的v$database.standby_became_primary_scn）。如闪回数据库部分中所述，闪回数据库将分成两个阶段进行：恢复阶段和介质恢复阶段。在恢复阶段，闪回数据库使用闪回数据库日志中的前映像块将数据库恢复到standby_became_primary_scn 之前的一点。在介
