Automating Client Failover in a Data Guard Configuration


We target is preventing clients from connecting
to the wrong Database when failover occurs.

we use database services to prevent clients from connecting to the wrong database in the Data Guard configuration
we can arrange client tnsnames.ora so Clients connect to database services instead of database instances
Database services register with listeners
we creating services for the data guard configuration databases
DBMS_SERVICE.CREATE_SERVICE( –
SERVICE_NAME => ‘PROD’, –
NETWORK_NAME => ‘PROD’, –
FAILOVER_METHOD => ‘BASIC’, –
FAILOVER_TYPE => ‘SELECT’, –
FAILOVER_RETRIES => 120, –
FAILOVER_DELAY => 1);

DBMS_SERVICE.CREATE_SERVICE( –
SERVICE_NAME => ‘STBY’, –
NETWORK_NAME => ‘STBY’);

DBMS_SERVICE.CREATE_SERVICE( –
SERVICE_NAME => ‘LSBY’, –
NETWORK_NAME => ‘LSBY’);

we use Use a database event trigger to ensure that clients connect to a database in the Data Guard configuration that is in the correct state and role

CREATE TRIGGER MANAGE_SERVICES AFTER STARTUP ON DATABASE
DECLARE
ROLE VARCHAR(30);
OMODE VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO ROLE FROM V$DATABASE;
SELECT OPEN_MODE INTO OMODE FROM V$DATABASE;
IF ROLE = ‘PRIMARY’ THEN
DBMS_SERVICE.START_SERVICE (‘PROD’);
ELSIF ROLE = ‘PHYSICAL STANDBY’ THEN
IF OMODE = ‘READ ONLY’ THEN
DBMS_SERVICE.START_SERVICE (‘STBY’);
END IF;
ELSIF ROLE = ‘LOGICAL STANDBY’ THEN
DBMS_SERVICE.START_SERVICE (‘LSBY’);
END IF;
END;

Use the trigger to start database services
PROD: Primary database
STBY: Physical standby database opened in READ ONLY mode
LSBY: Logical standby database

configure client tnsnames.ora
PROD = (DESCRIPTION =
(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = PROD)))

STBY = (DESCRIPTION =
(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = STBY)))

LSBY = (DESCRIPTION =
(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = LSBY)))

Relocating database services to the new primary database as part of a failover operation

Advertisements

About oracledocuments

Zekeriya Beşiroğlu, It joined the Bilginc IT Academy in July 2000. In the meantime, the Oracle Education, Oracle Data Base Management System and Oracle Internet Technologies, Oracle development technologies such as the training of Oracle products is responsible for training as consultants. During this task, since 1 April 2008 with 293 Oracle training in total, in 2150 Oracle customers successfully submitted. Oracle 10g New York in November 2002 have received training and education in Turkey was the first time the consultants. 20 April 2008 on education in Turkey 11g is the first who was a consultant. Oracle Real Application Cluster Expert advisor is certified is the first . Http://zekeriyabesiroglu.blogspot.com and Http://www.oracleforum.info owner and manager of the site. Its own has more than one hundred articles. In addition, creation and improvement of training materials are also related to work. Oracle products are used, database management and reporting Academy eruditely It also is working on. From the date of 1 January 2009 will continue to work as the Technical Director
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

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