Re: Data Guard

From: Carel-Jan Engel <cjpengel.dbalert_at_xs4all.nl>
Date: Fri, 07 Apr 2006 15:06:31 +0200
Message-Id: <1144415191.11391.27.camel@dbalert199.dbalert.nl>

Hi Willy,

Oracle invented TAF for this. TAF stands for Transparent Application Failover, or Translucent Application Failover as it was coined by the great Pete Sharman.
TAF lets you specify several instances using one alias. Below I attached a part of a tnsnames.ora as I configured it. Now there is one problem: For SQL*Net a connection is not unavailable when the listener is running. So, SQL*Net doesn't see any reason to try the next connect string when it succeeds connecting to the first one, even if the database will reply the connect attempt with an ORA-1033 (Startup or shutdown in progress). TAF was documented in the first Data Guard Concepts Manual (9.0.1), but it was removed from it in 9.2, as the documented setup simply didn't work.

For 9.0.1 I created my own workaround and I decided to use several listeners for several roles of the databases when running Data Guard. This is still the way I configure this, now at over 20 sites and many more databases.

In a Data Guard environment I see the following roles for the databases:

- FAL Server
- Application Database
- Reporting (Read-only) Database
- RFS

The FAL Server is the instance that sends missing archive logs to the standby that detects it is missing some of them. Generally speaking, this is the Primary database.
The Application Database is the database used by the instance(s) the users are actually connecting to for their data-processing The Reporting (Read-only) Database is the Physical Standby Database in Read Only mode, used for reports, queries, recovery from human failures, whatever
The RFS is the process that receives redo information from the primary. It is active for standby databases

I use to have a listener for every role. For the RFS process I use one listener per server. This listener is serving all instances on that server. All instances share the same listener port for redo stream transport connections

For the other roles every instance has its own listener. Both the FAL listener and the Application listener are active when the database is running as a Primary. Otherwise the listeners are stopped.

The Read-Only listener is active when a Physical Standby is running in Read Only mode. All the stopping and starting of the listeners is automated in the dataguard scripting I use.

Using this modus operandi, when a role-switch is performed, all components (1st, 2nd, 3rd standby, application, reporting engines) will automagically follow the role they need as it is moving from one instance to another. Especially when you have two standby's, the standby that is not involved in a role-switch can detect its new FAL-server automagically.

Note that this not-involved standby will see an end-of-redo marker that is written at the 'commit to switchover to standby' at the primary. It will therefor remain in a 'switchover pending' state. This situation can be solved by creating a new standby controlfile at the (new) primary, stop the standby, copy the standby controlfile to it and restart it.

Best regards, Carel-Jan Engel

===
If you think education is expensive, try ignorance. (Derek Bok) ===

DBA!ert, Independent Oracle consultancy Kastanjelaan 61C
2743 BX Waddinxveen
The Netherlands
tel. +31 (0) 182 64 04 28
fax +31 (0) 182 64 04 29
e-mail info.dbalert_at_xs4all.nl

#
# The individual Data Guard redo listeners
#

d10b1_dg =
  (DESCRIPTION =
    (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbalert100)(PORT = 1522))
    )
    (CONNECT_DATA =
(SERVICE_NAME = d10b)

    )
  )

d10b2_dg =
  (DESCRIPTION =
    (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbalert101)(PORT = 1522))
    )
    (CONNECT_DATA =
(SERVICE_NAME = d10b)

    )
  )

d10b3_dg =
  (DESCRIPTION =
    (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbalert102)(PORT = 1522))
    )
    (CONNECT_DATA =
(SERVICE_NAME = d10b)

    )
  )

d10b4_dg =
  (DESCRIPTION =
    (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbalert103)(PORT = 1522))
    )
    (CONNECT_DATA =
(SERVICE_NAME = d10b)

    )
  )

#
# The application alias, failover to the active primary
#

d10b =
(DESCRIPTION =

    (FAILOVER=ON)
    (LOAD_BALANCE=OFF)
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbalert100)(PORT = 1523))
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbalert101)(PORT = 1523))
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbalert102)(PORT = 1523))
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbalert103)(PORT = 1523))
    (CONNECT_DATA =


      (SERVICE_NAME = d10b)

)
)

#
# The FAL alias, failover to the active primary
#

d10b_fal =

(DESCRIPTION =
(FAILOVER=ON)
(LOAD_BALANCE=OFF)
(ADDRESS = (PROTOCOL = TCP)(HOST = dbalert100)(PORT = 1524))
(ADDRESS = (PROTOCOL = TCP)(HOST = dbalert101)(PORT = 1524))
(ADDRESS = (PROTOCOL = TCP)(HOST = dbalert102)(PORT = 1524))
(ADDRESS = (PROTOCOL = TCP)(HOST = dbalert103)(PORT = 1524))
(CONNECT_DATA =

        (SERVICE_NAME = d10b)
      )

)

#
# The RO alias, failover to the Read Only standby
#

d10b_ro =

(DESCRIPTION =
(FAILOVER=ON)
(LOAD_BALANCE=OFF)
(ADDRESS = (PROTOCOL = TCP)(HOST = dbalert100)(PORT = 1525))
(ADDRESS = (PROTOCOL = TCP)(HOST = dbalert101)(PORT = 1525))
(ADDRESS = (PROTOCOL = TCP)(HOST = dbalert102)(PORT = 1525))
(ADDRESS = (PROTOCOL = TCP)(HOST = dbalert103)(PORT = 1525))
(CONNECT_DATA =

        (SERVICE_NAME = d10b)
      )

)

On Fri, 2006-04-07 at 14:16 +0200, willy wrote:

> Hi Database Gurus,
>  
> I've a question regarding Data Guard. If a database switches role from
> standby to primary can I still connect to the new primary database
> without setting a network alias or adjusting the tnsnames.ora with the
> new IP adress ? I guess it has something to do with the tnsnames.ora
> but I can't figure out how to do this.
>  
> TIA,
>  
> Willy Schriemer

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Apr 07 2006 - 08:06:31 CDT