Dataguard Adventures ...!!!!! - Part II

In my last post,we eneded up with primary shipping archivelogs to standby that are being applied to standby.The next steps would be to setup DG BROKER for this setup to automate switchover and failover.

Downtime required ???? …YES…! For what???? ….To test the switchover between primary and physical standby..>And how much….30 minutes

Make sure that both the primary and standby should be up using spfile. If not create the spfile and take a bounce. My primary wukprd is already running with spfile but standby is still on pfile (as mentioned in my previous post). So I’ll create an spfile from standby and take a database bounce to startup the database using spfile.

1) Configure DG Broker initialization parameters.

a) Configure on primary

SQL> select NAME,ISSYS_MODIFIABLE from v$parameter where name like '%broker%';

NAME ISSYS_MOD

-------------------------------------------------- ---------

dg_broker_start IMMEDIATE

dg_broker_config_file1 IMMEDIATE

dg_broker_config_file2 IMMEDIATE

SQL> !hostname

dguard1

SQL> show parameter dg_broker_start

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

dg_broker_start boolean FALSE

SQL> alter system set dg_broker_start=TRUE scope=both;

System altered.

SQL> show parameter broker

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

dg_broker_config_file1 string /u01/app/oracle/product/10.2.0

/dbs/dr1P_WUKPRD.dat

dg_broker_config_file2 string /u01/app/oracle/product/10.2.0

/dbs/dr2P_WUKPRD.dat

dg_broker_start boolean TRUE

SQL>

b) Repeat the above steps on standby.

SQL> !hostname

dguard2

SQL> show parameter broker

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

dg_broker_config_file1 string /u01/app/oracle/product/10.2.0

/dbs/dr1S_WUKPRD.dat

dg_broker_config_file2 string /u01/app/oracle/product/10.2.0

/dbs/dr2S_WUKPRD.dat

dg_broker_start boolean TRUE

SQL>

2) Configure network files.

a) Add the entry for DG Broker in network files (listener.ora) on primary

NOTE: 

1)The value for the GLOBAL_DBNAME should be a

concatenation of the initialization parameters _DGMGRL.

2) Make sure you add GLOBAL_DBNAME entry under SID_DESC in listener.ora.

This is my primary listener.ora after changes.

# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = wukprd)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0)

(SID_NAME = wukprd)

)

(SID_DESC =

(GLOBAL_DBNAME = P_WUKPRD_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0)

(SID_NAME = wukprd)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dguard1)(PORT = 1521))

)

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

b) Repeat the above steps on standby.

[oracle@dguard2 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = wukprd)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0)

(SID_NAME = wukprd)

)

(SID_DESC =

(GLOBAL_DBNAME = S_WUKPRD_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0)

(SID_NAME = wukprd)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = dguard2)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

c) Reload the listener on both primary and standby.

[oracle@dguard1 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 08-SEP-2009 13:11:32

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dguard1)(PORT=1521)))

The command completed successfully

[oracle@dguard1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 08-SEP-2009 13:11:37

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dguard1)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date 07-SEP-2009 18:40:32

Uptime 0 days 18 hr. 31 min. 5 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/10.2.0/network/admin/listener.ora

Listener Log File /u01/app/oracle/product/10.2.0/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dguard1)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "P_WUKPRD" has 1 instance(s).

Instance "wukprd", status READY, has 1 handler(s) for this service...

Service "P_WUKPRD_DGB" has 1 instance(s).

Instance "wukprd", status READY, has 1 handler(s) for this service...

Service "P_WUKPRD_DGMGRL" has 1 instance(s).

Instance "wukprd", status UNKNOWN, has 1 handler(s) for this service...

Service "P_WUKPRD_XPT" has 1 instance(s).

Instance "wukprd", status READY, has 1 handler(s) for this service...

Service "wukprd" has 1 instance(s).

Instance "wukprd", status UNKNOWN, has 1 handler(s) for this service...

Service "wukprdXDB" has 1 instance(s).

Instance "wukprd", status READY, has 1 handler(s) for this service...

The command completed successfully

[oracle@dguard1 admin]$

On Standby

[oracle@dguard2 admin]$ lsnrctl reload

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 08-SEP-2009 13:18:11

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dguard2)(PORT=1521)))

The command completed successfully

[oracle@dguard2 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 08-SEP-2009 13:18:15

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dguard2)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production

Start Date 07-SEP-2009 18:44:51

Uptime 0 days 18 hr. 33 min. 23 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/10.2.0/network/admin/listener.ora

Listener Log File /u01/app/oracle/product/10.2.0/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dguard2)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "S_WUKPRD" has 1 instance(s).

Instance "wukprd", status READY, has 1 handler(s) for this service...

Service "S_WUKPRD_DGB" has 1 instance(s).

Instance "wukprd", status READY, has 1 handler(s) for this service...

Service "S_WUKPRD_DGMGRL" has 1 instance(s).

Instance "wukprd", status UNKNOWN, has 1 handler(s) for this service...

Service "S_WUKPRD_XPT" has 1 instance(s).

Instance "wukprd", status READY, has 1 handler(s) for this service...

Service "wukprd" has 1 instance(s).

Instance "wukprd", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

[oracle@dguard2 admin]$

3) Creating DG Broker Configuration

a) On primary

[oracle@dguard1 ~]$ dgmgrl

DGMGRL for Linux: Version 10.2.0.1.0 - Production

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

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/sys123@P_WUKPRD

Connected.

DGMGRL>

DGMGRL> CREATE CONFIGURATION DGBROKER AS PRIMARY DATABASE IS P_WUKPRD CONNECT IDENTIFIER IS P_WUKPRD;

Configuration "dgbroker" created with primary database "p_wukprd"

b) Add the standby to the configuration and check it

DGMGRL> ADD DATABASE S_WUKPRD AS CONNECT IDENTIFIER IS S_WUKPRD

MAINTAINED AS PHYSICAL;

Database "S_WUKPRD" added

DGMGRL> show configuration;

Configuration

Name: dgbroker

Enabled: YES

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLED

Databases:

p_wukprd - Primary database

s_wukprd - Physical standby database

Current status for "dgbroker":

DISABLED

So we are done with the creation of DG Broker Configuration and addition or primary and standby database to this configuration. The configuration has the current status as DISABLED.

c) Check database properties

DGMGRL> show database verbose P_WUKPRD

Database

Name: p_wukprd

Role: PRIMARY

Enabled: YES

Intended State: ONLINE

Instance(s):

wukprd

Properties:

InitialConnectIdentifier = 'P_WUKPRD'

LogXptMode = 'ASYNC'

Dependency = ''

DelayMins = '0'

Binding = 'OPTIONAL'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '180'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '1200'

LogArchiveMaxProcesses = '2'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = ''

LogFileNameConvert = ''

FastStartFailoverTarget = ''

StatusReport = '(monitor)'

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

HostName = 'dguard1'

SidName = 'wukprd'

LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=dguard1)(PORT=1521))'

StandbyArchiveLocation = 'dgsby_P_WUKPRD'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = 'wukprd_%t_%s_%r.arc'

LatestLog = '(monitor)'

TopWaitEvents = '(monitor)'

Current status for "p_wukprd":

DISABLED

DGMGRL>

DGMGRL> show database verbose S_WUKPRD;

Database

Name: s_wukprd

Role: PHYSICAL STANDBY

Enabled: YES

Intended State: ONLINE

Instance(s):

wukprd

Properties:

InitialConnectIdentifier = 'S_WUKPRD'

LogXptMode = 'ASYNC'

Dependency = ''

DelayMins = '0'

Binding = 'OPTIONAL'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '180'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '1200'

LogArchiveMaxProcesses = '2'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = ''

LogFileNameConvert = ''

FastStartFailoverTarget = ''

StatusReport = '(monitor)'

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

HostName = 'dguard2'

SidName = 'wukprd'

LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=dguard2)(PORT=1521))'

StandbyArchiveLocation = '/u01/s_arch'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = 'wukprd_%t_%s_%r.arc'

LatestLog = '(monitor)'

TopWaitEvents = '(monitor)'

Current status for "s_wukprd":

DISABLED

DGMGRL>

4) Enabling the Broker Configuration

DGMGRL> enable configuration

Enabled.

DGMGRL> show configuration

Configuration

Name: dgbroker

Enabled: YES

Protection Mode: MaxPerformance

Fast-Start Failover: DISABLED

Databases:

p_wukprd - Primary database

s_wukprd - Physical standby database

Current status for "dgbroker":

SUCCESS

DGMGRL>

So we are done with the configuration of DG Broker for our setup.. Time to test switchover now.

DOWNTIME OF PRODUCTION STARTS HERE…!!!!

5) Testing switchover

a) On primary

DGMGRL> switchover to S_WUKPRD;

Performing switchover NOW, please wait...

Operation requires shutdown of instance "wukprd" on database "p_wukprd"

Shutting down instance "wukprd"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires shutdown of instance "wukprd" on database "s_wukprd"

Shutting down instance "wukprd"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "wukprd" on database "p_wukprd"

Starting instance "wukprd"...

ORACLE instance started.

Database mounted.

Operation requires startup of instance "wukprd" on database "s_wukprd"

Starting instance "wukprd"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "s_wukprd"

DGMGRL>

Superb….!!!!! …Primary shutdown….Standby shutdown…primary started as standby and standby started as primary……..

Let’s check the role reversal movement and application of archivelogs.

SQL> !hostname

dguard1

SQL> select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE, PROTECTION_MODE from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE

--------- ------------------------------ ---------- ---------------- --

WUKPRD P_WUKPRD MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE

So, dguard1 is now physical standby.

SQL> selectNAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE, PROTECTION_MODE from v$database;

NAME DB_UNIQUE_NAMEOPEN_MODE DATABASE_ROLE PROTECTION_MODE

--------- ------------------------------ ---------- ---------------- --

WUKPRD S_WUKPRD READ WRITE PRIMARY MAXIMUM PERFORMANCE

And dguard2 is the new primary.

Let’s see if arhivelogs are moving from new primary to new standby.

On Primary

SQL> !hostname

dguard2

SQL> select max(SEQUENCE#) from v$archived_log AL, V$DATABASE DB where AL.RESETLOGS_CHANGE#=DB.RESETLOGS_CHANGE# AND AL.RESETLOGS_TIME = DB.RESETLOGS_TIME and AL.ARCHIVED='YES' and AL.APPLIED='YES' AND DEST_ID=2;

MAX(SEQUENCE#)

--------------

124

SQL>

On Standby

SQL> !hostname

dguard1

SQL> select max(SEQUENCE#) from v$archived_log AL, V$DATABASE DB where AL.RESETLOGS_CHANGE#=DB.RESETLOGS_CHANGE# AND AL.RESETLOGS_TIME = DB.RESETLOGS_TIME and AL.ARCHIVED='YES' and AL.APPLIED='YES';

MAX(SEQUENCE#)

--------------

124

Let’s try a logfile switch on primary.

On Primary

SQL> !hostname

dguard2

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

Check on Standby

SQL> !hostname

dguard1

SQL> select max(SEQUENCE#) from v$archived_log AL, V$DATABASE DB where AL.RESETLOGS_CHANGE#=DB.RESETLOGS_CHANGE# AND AL.RESETLOGS_TIME = DB.RESETLOGS_TIME and AL.ARCHIVED='YES' and AL.APPLIED='YES';

MAX(SEQUENCE#)

--------------

126

SQL>

Check on Primary

SQL> !hostname

dguard2

SQL>

SQL> select max(SEQUENCE#) from v$archived_log AL, V$DATABASE DB where AL.RESETLOGS_CHANGE#=DB.RESETLOGS_CHANGE# AND AL.RESETLOGS_TIME = DB.RESETLOGS_TIME and AL.ARCHIVED='YES' and AL.APPLIED='YES' AND DEST_ID=2;

MAX(SEQUENCE#)

--------------

124

Remember it takes some time to reflect the latest applied standby sequence# on primary. So when you will execute the above query on primary, the value of latest applied sequence# (on standby) will take some time to reflect. (on primary)

Let’s try the switchback now…I did it from dguard2.

[oracle@dguard2 admin]$ dgmgrl

DGMGRL for Linux: Version 10.2.0.1.0 - Production

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

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/sys123@P_WUKPRD

Connected.

DGMGRL> switchover to P_WUKPRD;

Performing switchover NOW, please wait...

Operation requires shutdown of instance "wukprd" on database "s_wukprd"

Shutting down instance "wukprd"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires shutdown of instance "wukprd" on database "p_wukprd"

Shutting down instance "wukprd"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "wukprd" on database "s_wukprd"

Starting instance "wukprd"...

ORACLE instance started.

Database mounted.

Operation requires startup of instance "wukprd" on database "p_wukprd"

Starting instance "wukprd"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "p_wukprd"

DGMGRL>

DOWNTIME OF PRODUCTION ENDS HERE…!!!!

Cool….!!!!! So we are done with the DG Broker Configuration well within the downtime window of 30 minutes. Will do application testing with both the primary and standby site next…!!!!

-- Rishi