DG BROKER配置主从及快速故障转移

DG BROKER

dmon是dg broker的进程，observer用于监控主库状态

Role	Primary	Standby
IP	192.168.62.11	192.168.62.13
Hostname	oradb01	oradb02

主库配置

开启归档

alter system set log_archive_dest='LOCATION=/oradata/arch/' scope=both sid='*';
alter database archivelog;

开启force logging

alter database force logging;

添加standby redo log

alter database add standby logfile group 4 ('/oradata/orcl/redo04.log') size 50M;
alter database add standby logfile group 5 ('/oradata/orcl/redo05.log') size 50M;
alter database add standby logfile group 6 ('/oradata/orcl/redo06.log') size 50M;
alter database add standby logfile group 7 ('/oradata/orcl/redo07.log') size 50M;

开启闪回

快速故障转移需要开启闪回，用于切换后，主库切换为备库，根据需要配置

mkdir -p /oradata/flashback
select * from v$version;
select flashback_on from v$database;
alter system set db_recovery_file_dest_size=10g scope=both sid='*';
alter system set db_recovery_file_dest='/oradata/flashback' scope=both sid='*';
alter database flashback on;

配置文件转换

alter system set db_file_name_convert='/oradata/dgdb/','/oradata/orcl/' scope=spfile sid='*';
alter system set log_file_name_convert='/oradata/dgdb/','/oradata/orcl/' scope=spfile sid='*';
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile sid='*';

开启DG BROKER

alter system set dg_broker_start=true scope=both;

配置监听

vim listener.ora
LISTENER =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.62.11)(PORT=1521))
 )

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (GLOBAL_DBNAME=orcl)
         (SID_NAME=orcl)
         (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
       )
      )

配置TNS

orcl =
   (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.62.11)(PORT = 1521))
        (CONNECT_DATA =
        (SERVER = DEDICATED)
        (INSTANCE_NAME = orcl)
        )
   )
dgdb =
   (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.62.13)(PORT = 1521))
        (CONNECT_DATA =
        (SERVER = DEDICATED)
        (INSTANCE_NAME = dgdb)
        )
   )

创建pfile

创建pfile，用于创建备库

create pfile='/tmp/dgdb.pfile' from spfile;

备份数据文件

vim backup.sh
. ~/.bash_profile
rman target / nocatalog log=/tmp/orcl/rman_full.log <<EOF
run{
CONFIGURE DEVICE TYPE DISK PARALLELISM 6;
configure channel device type disk maxpiecesize=2048m;
sql 'alter system archive log current';
BACKUP AS COMPRESSED BACKUPSET SKIP INACCESSIBLE
TAG hot_db_bk_full  FORMAT '/tmp/orcl/bk_%s_%p_%t'
FULL DATABASE;
}
exit;
EOF

备份控制文件

alter database create standby controlfile as  '/tmp/zjzf/orcl_stdby.ctl';

数据拷贝

拷贝pfile、密码文件、备份片、控制文件到备库

备库配置

配置监听

vim listener.ora
LISTENER =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.62.13)(PORT=1521))
 )

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (GLOBAL_DBNAME=orcl)
         (SID_NAME=orcl)
         (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
       )
      )

启动监听

lsnrctl start

配置密码文件

cd $ORACLE_HOME/dbs
mv orapworcl orapwdgdb

编辑pfile

fal_server、fal_client、dg_config、log_archive_dest_n使用dgmgrl可自行配置

*.audit_file_dest='/u01/app/oracle/admin/dgdb/adump'
*.control_files='/oradata/dgdb/control01.ctl','/oradata/dgdb/control02.ctl'
*.db_file_name_convert='/oradata/orcl','/oradata/dgdb'
*.db_unique_name='dgdb'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dgdbXDB)'
*.log_file_name_convert='/oradata/orcl','/oradata/dgdb'
*.db_file_name_convert='/oradata/orcl','/oradata/dgdb'

创建实例

启动实例，创建spfile，通过spfile启动实例

startup nomount pfile='/tmp/dgdb.pfile';
create spfile from pfile='/tmp/dgdb.pfile';
shutdown immediate;
startup

创建目录

用于存放审计日志

mkdir -p /u01/app/oracle/admin/dgdb/adump

用于存放数据文件

mkdir -p /oradata/dgdb

恢复控制文件

恢复控制文件，启动数据库到mount状态

RMAN> restore controlfile from '/tmp/orcl/orcl_stdby.ctl';
RMAN> alter database mount;

恢复数据文件

vim restore.sh
. ~/.bash_profile
rman target / nocatalog log /tmp/orcl/restoredb.log <<EOF
run{
restore database;
}
exit
EOF

DG BROKER配置

配置configuration，connect identifier对应TNS中配置的名称

配置完成后standby自动启动MRP进程

dgmgrl sys/oracle@orcl
DGMGRL> create configuration 'orcl_broker' as primary database is 'orcl' connect identifier is 'orcl';
DGMGRL> add database 'dgdb' as connect identifier is 'dgdb' maintained as physical;
enable configuration

查看configuration状态

DGMGRL> show configuration;

Configuration - orcl_broker

  Protection Mode: MaxPerformance
  Members:
  orcl - Primary database
    dgdb - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 44 seconds ago)

# 查看详细信息
DGMGRL> show configuration verbose

Configuration - orcl_broker

  Protection Mode: MaxPerformance
  Databases:
    orcl - Primary database
    dgdb - (*) Physical standby database

  (*) Fast-Start Failover target

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'

Fast-Start Failover: ENABLED

  Threshold:          30 seconds
  Target:             dgdb
  Observer:           oradb02
  Lag Limit:          30 seconds
  Shutdown Primary:   TRUE
  Auto-reinstate:     TRUE
  Observer Reconnect: (none)
  Observer Override:  FALSE

Configuration Status:
SUCCESS

查看database状态

DGMGRL> show database orcl

Database - orcl

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    orcl

Database Status:
SUCCESS

# 查看详细信息
DGMGRL> show database verbose orcl

Database - orcl

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    orcl

  Properties:
    DGConnectIdentifier             = 'orcl'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'MANUAL'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/oradata/dgdb,/oradata/orcl'
    LogFileNameConvert              = '/oradata/dgdb,/oradata/orcl'
    FastStartFailoverTarget         = 'dgdb'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    SidName                         = 'orcl'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradb01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/oradata/arch'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DG BROKER主备切换

DGMGRL> switchover to dgdb
Performing switchover NOW, please wait...
Operation requires a connection to database "dgdb"
Connecting ...
Connected to "dgdb"
Connected as SYSDBA.
New primary database "dgdb" is opening...
Operation requires start up of instance "orcl" on database "orcl"
Starting instance "orcl"...
Connected to an idle instance.
ORACLE instance started.
Connected to "orcl"
Database mounted.
Database opened.
Connected to "orcl"
Switchover succeeded, new primary is "dgdb"


DGMGRL> show configuration

Configuration - orcl_broker

  Protection Mode: MaxPerformance
  Members:
  dgdb - Primary database
    orcl - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 42 seconds ago)

快速故障转移

当存在多个standby时，可以配置用于接管的数据库，配置时需要关闭fast-start failover

edit database orcl SET PROPERTY FastStartFailoverTarget='dgdb';

开启闪回

如已开启无需再次配置

mkdir -p /oradata/flashback
select * from v$version;
select flashback_on from v$database;
alter system set db_recovery_file_dest_size=10g scope=both sid='*';
alter system set db_recovery_file_dest='/oradata/flashback' scope=both sid='*';
alter database flashback on;

开启observer

standby开启observer，开启快速故障转移需要

DGMGRL> start observer

observer需要持续运行，需要将其配置为后台运行

1. start the dgmgrl process with nohup command, eg:

nohup dgmgrl -logfile /tmp/dgmgrl.log <<eof
connect sys/passwd@connect_string
start observer
EOF

1. create a shell script and run the shell script at background:

#!/bin/bash
## Script to start observer via DGMGRL
dgmgrl -echo -logfile /tmp/dgmgrl.log << EOF
connect sys/passwd@connect_string
start observer
EOF

chmod +x observer.sh
./observer.sh &

1. From 11.2 onwards, one can use the following command to start observer:

% dgmgrl -logfile /tmp/observer.log sys/passwd@connect_string "start observer" &

开启fast-start failover

DGMGRL> enable fast_start failover
Enabled.

DGMGRL> SHOW FAST_START FAILOVER;

Fast-Start Failover: ENABLED

  Threshold:          30 seconds
  Target:             dgdb
  Observer:           oradb02
  Lag Limit:          30 seconds
  Shutdown Primary:   TRUE
  Auto-reinstate:     TRUE
  Observer Reconnect: (none)
  Observer Override:  FALSE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

  Oracle Error Conditions:
    (none)

故障切换

在新备库连接新的主库

# 将新备库启动到mount状态
sqlplus / as sysdba
startup mount

dgmgrl sys/oracle@dgdb
DGMGRL> reinstate database orcl