配置dg broker

broker 一键切换真的快,但我方生产都没有配置,ACE说这东西BUG多,自己实践学习下

[oracle@db1 admin]$ cat listener.ora
VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON
VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=ON
REGISTRATION_INVITED_NODES_LISTENER_SCAN1=(db1)

SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl11g)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl11g)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl11g_dgmgrl)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl11g)
)
)
LISTENER_DG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.220.210)(PORT = 1522))
)
)

SID_LIST_LISTENER_DG=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl11gdg)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl11gdg)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl11gdg_dgmgrl)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl11gdg)
)
)

LISTENER_GJ =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1523))
)
)

SID_LIST_LISTENER_GJ=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl11ggj)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl11ggj)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl11ggj_dgmgrl)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl11ggj)
)
)
DIAG_ADR_ENABLED_LISTENER_DG=OFF
DR_BASE_LISTENER = /oracle/app/oracle/diag/tnslsnr/db1

[oracle@db1 admin]$ cat tnsnames.ora
orcl11g =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl11g)
)
)

orcl11gdg =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.220.210)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl11gdg)
)
)

orcl11ggj =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl11ggj)
)
)

主备
SQL> alter system set dg_broker_start=true sid='*';

System altered.

[oracle@db1 backup]$ dgmgrl sys/oracle@orcl11g
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

 

DGMGRL> CREATE CONFIGURATION dg_config AS PRIMARY DATABASE IS orcl11g CONNECT IDENTIFIER IS orcl11g;
Configuration "my_dg_config" created with primary database "orcl11g"

 

DGMGRL> ADD DATABASE 'orcl11gdg' AS CONNECT IDENTIFIER IS 'orcl11gdg' maintained as physical;
Database "orcl11gdg" added
DGMGRL>
DGMGRL> enable configuration;
Enabled.
DGMGRL> show CONFIGURATION

Configuration - my_dg_config

Protection Mode: MaxPerformance
Databases:
orcl11g - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> ADD DATABASE 'orcl11gdg' AS CONNECT IDENTIFIER IS 'orcl11gdg' maintained as physical;
Database "orcl11gdg" added
DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration;

Configuration - dg_config

Protection Mode: MaxPerformance
Databases:
orcl11gdg - Primary database
orcl11g - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

 

DGMGRL> show database verbose orcl11g

Database - orcl11g

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 0 Byte/s
Real Time Query: ON
Instance(s):
orcl11g

Properties:
DGConnectIdentifier = 'orcl11g'
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 = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'orcl11g'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl11g_DGMGRL)(INSTANCE_NAME=orcl11g)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/home/oracle/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'

Database Status:
SUCCESS

DGMGRL> show database verbose orcl11gdg

Database - orcl11gdg

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

Properties:
DGConnectIdentifier = 'orcl11gdg'
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 = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/oracle/app/oracle/oradata/ORCL11G/datafile, /oradata/orcl11gdg'
LogFileNameConvert = '/oracle/app/oracle/oradata/ORCL11G/onlinelog, /oradata/orcl11gdg'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'orcl11gdg'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl11gdg_DGMGRL)(INSTANCE_NAME=orcl11gdg)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/oradata/orcl11gdg/archdg'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.arch'
TopWaitEvents = '(monitor)'

Database Status:
SUCCESS

 

一键切换试试
SQL> select name,open_mode,database_role,db_unique_name,protection_mode,protection_level from v$database;

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME PROTECTION_MODE PROTECTION_LEVEL
--------- -------------------- ---------------- ------------------------------ -------------------- --------------------
ORCL11G READ WRITE PRIMARY orcl11g MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

SQL>
SQL> select name,open_mode,database_role,db_unique_name,protection_mode,protection_level from v$database;

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME PROTECTION_MODE PROTECTION_LEVEL
--------- -------------------- ---------------- ------------------------------ -------------------- --------------------
ORCL11G READ ONLY WITH APPLY PHYSICAL STANDBY orcl11gdg MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

 

DGMGRL> SWITCHOVER TO orcl11gdg
Performing switchover NOW, please wait...
Operation requires a connection to instance "orcl11gdg" on database "orcl11gdg"
Connecting to instance "orcl11gdg"...
Connected.
New primary database "orcl11gdg" is opening...
Operation requires startup of instance "orcl11g" on database "orcl11g"
Starting instance "orcl11g"...
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "orcl11gdg"

SQL> select name,open_mode,database_role,db_unique_name,protection_mode,protection_level from v$database;

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME PROTECTION_MODE PROTECTION_LEVEL
--------- -------------------- ---------------- ------------------------------ -------------------- --------------------
ORCL11G READ ONLY WITH APPLY PHYSICAL STANDBY orcl11g MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

SQL> select name,open_mode,database_role,db_unique_name,protection_mode,protection_level from v$database;

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME PROTECTION_MODE PROTECTION_LEVEL
--------- -------------------- ---------------- ------------------------------ -------------------- --------------------
ORCL11G READ WRITE PRIMARY orcl11gdg MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

 

#一分钟又切换回来了
[oracle@db1 log]$ dgmgrl sys/oracle
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> SWITCHOVER TO orcl11g
Performing switchover NOW, please wait...
New primary database "orcl11g" is opening...
Operation requires startup of instance "orcl11gdg" on database "orcl11gdg"
Starting instance "orcl11gdg"...
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "orcl11g"

 

SQL> select name,open_mode,database_role,db_unique_name,protection_mode,protection_level from v$database;

NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME PROTECTION_MODE PROTECTION_LEVEL
--------- -------------------- ---------------- ------------------------------ -------------------- --------------------
ORCL11G READ WRITE PRIMARY orcl11g MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

 

 

DGMGRL> ADD DATABASE 'orcl11ggj' AS CONNECT IDENTIFIER IS 'orcl11ggj' maintained as physical;
Database "orcl11ggj" added
DGMGRL> show CONFIGURATION

Configuration - dg_config

Protection Mode: MaxPerformance
Databases:
orcl11g - Primary database
orcl11gdg - Physical standby database
orcl11ggj - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> enable configuration;
Enabled.
DGMGRL> show CONFIGURATION

Configuration - dg_config

Protection Mode: MaxPerformance
Databases:
orcl11g - Primary database
orcl11gdg - Physical standby database
orcl11ggj - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS