DataGuard Broker Notes

After the fact, great reference someone forwarded me:
https://blogs.oracle.com/xpsoluxdb/entry/4_steps_to_create_a_physical_standby_database
Pre-reqs
========
* must be same version
* must use spfile
* dg_broker_start = true
* dg_broker_config_file<n> for RAC systems
* tns networking configured * see problems for _DGMGRL (required) and _DGB

Terms
=====
A broker configuration is the grouping of primary, standby databases, and the redo transport and apply.
A “database object” is a primary or standby database
DMON is the process that manages broker configurations
Broker config files
===================
Each databases always has two broker files so as to have a copy of the last known good configuration
DG_BROKER_CONFIG_FILE1
DG_BROKER_CONFIG_FILE2
These get created automatically when broker is first started.
the names of these files can be changed with
dgmgrl disable
alter system set dg_broker_start=false
move files
alter system set dg_broker_start=true”
dbmgrl enable
in RAC all instances must use the same files
To start/stop broker
====================
just “alter system set dg_broker_start=<true/false>;”
To setup a DataGuard Broker configuration
=========================================
1. Create the config and add databases
create configuration ‘T2X_DG’ as primary database is ‘T20AC’ connect identifier is T20AC;
show configuration
add database ‘T21AC’ as connect identifier is T21AC;
2. Edit properties (if needed, usually I config things before setting up broker)
edit database ‘T20AC’ set property ‘LogArchiveFormat’=’log_%t_%s_%r_%d.arc’;
show database verbose ‘T20AC’;
3. Enable config and databases
enable configuration;
show configuration — do this on all databases in the config to make sure
4. Set the protection mode (if you need to)
edit configuration set protection mode as maxavailability/maxperformance/maxprotection;
show configuration
5. Enable fast start failover and observer (if desired)
need standby redo logs, log transport SYNC
edit database ‘T20AC’ set property FastStartFailoverTarget=’T21AC’;
might want to enable maxavailability if required
might want to enable flashback database
start observer;
enable fast_start failover;
show fast_start failover;
Change transport and apply
==========================
edit database ‘<PRIMARY>’ set state=transport-on’; — or TRANSPORT-OFF
edit database ‘<STANDBY>’ set state=apply-off’; — or APPLY-ON
Switchover
==========
show database verbose ‘T20AC’; — do this for both primary and standby
show configuration;
switchover to ‘T21AC’;
show configuration;
Failover
========
failover to ‘T20AC’;
show configuration;
show database verbose ‘T20AC’; — do this for both if possible.
After failover, if you rebuild database manually
================================================
disable database ‘T21AC’
<rebuild it>
reinstate database ‘T21AC’
enable database ‘T21AC’
useful commands in DGMGRL
=========================
enable/disable database ‘<db-unique-name>’
show database [verbose] ‘<db-unique-name>’ [‘<property>’]
edit database ‘<db-unique-name>’ <property> = <value>
EDIT DATABASE ‘South_Sales’ SET STATE=’APPLY-OFF’;
EDIT DATABASE ‘<db-unique-name>’ SET PROPERTY ‘LogShipping’=’OFF’; — turn off log shipping example
CONVERT DATABASE
SWITCHOVER
FAILOVER TO <database-name> [IMMEDIATE];
REINSTATE DATABASE <db-unique_name>
ENABLE FAST_START FAILOVER
show fast_start failover
START OBSERVER; — watches database configuration for fast start failover functionality
SHOW FAST_START FAILOVER;

Views
=====
V$DATABASE
V$FS_FAILOVER_STATS

Fast Start Failover properties
==============================
FastStartFailoverTarget
FastStartFailoverThreshold
FastStartFailoverPmyShutdown
FastStartFailoverLagLimit
FastStartFailoverAutoReinstate

Interesting/Annoying things
===========================

– ApplyParallel database property is not displayed on the Edit Properties page of Enterprise Manager
– If a standby database is an Oracle RAC database, only one instance of the Oracle RAC database can have log apply services running at any time.
This instance is called the apply instance.
If the apply instance fails, the broker automatically moves log apply services to a different instance; this is called apply instance failover
set PreferredApplyInstance database property to the name of the instance (see the SidName property) you prefer to be the apply instance
– Oracle also provides the DBMS_DG PL/SQL package to allow an application to request a fast-start failover

Problems
========
Error: ORA-16816: incorrect database role
Error: ORA-16810: multiple errors or warnings detected for the database
many sites says to manually switch the roles back so that they match broker, but this might be OK on a production system
stop broker on all databases
remove broker configuration files
start broker
create configuration and add databases again
Error: failed switchover, TNS-12514
listener.ora must be specially configured after starting the broker:
add an entry to listener.ora with that for GLOBAL_DBNAME with <SID>_DGMGRL
If you don’t, on switcher Oracle can’t communicate properly and you’ll have two down databases.
you will see a message like this in alert log:
Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=t20ac-db-ip)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=T20AC_DGB)(CID=(PROGRAM=oracle)(HOST=ac-tst-db3.cmc.cable.comcast.com)(USER=oracle))))
example listener.ora entry for DB named T20AC, instance T20AC
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = T20AC_DGMGRL)
(ORACLE_HOME = /oracle/product/11.2.0.2)
(SID_NAME = T20AC)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = T21AC_DGMGRL)
(ORACLE_HOME = /oracle/product/11.2.0.2)
(SID_NAME = T21AC)
)
)
Error: ORA-00600: internal error code, arguments: [kcbz_zib_simulation_1], [49094], [5130], [], [], [], [], [], [], [], [], []
seems to happen on the physical standby during switchover with the broker.
Everything appears to work fine once the switchover succeeds. no info on Oracle
Issue: Primary becomes standby, but standby doesn’t open
ORACLE instance started.
Database mounted.
Database opened.
<hangs here>
Can’t start manually
Connected.
SQL> startup
ORA-01012: not logged on
Found shared memory segment with ipcs, removed it with ipcrm
started with sqlplus and then dgmgrl completed:
DGMGRL> Switchover succeeded, new primary is “T21AC”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s