Managing Client Connectivity in a Data Guard Configuration

Managing Client Connectivity.

Understanding Client Connectivity in a Data Guard Configuration.Be aware of the following issues when you manage client connectivity in a Data Guard configuration:
• Databases reside on different hosts in a Data Guard configuration.
• Clients must connect to the correct database:
– Primary
– Logical standby
– Snapshot standby
– Physical standby with real-time query
• If clients send connection requests to the wrong host, they may be connected to the wrong Database or receive an error.
• Clients must automatically reconnect to the correct database in the event of a failover. 

Preventing Clients from Connecting to the Wrong Database
• Use database services to prevent clients from connecting to the wrong database in the Data Guard configuration.
• Database services act as an abstraction layer between the client and database instances.
• Database services register with listeners.
• Clients connect to database services instead of database instances.
• Listeners use registration details to determine which instances support a particular service at a particular
moment in time.
• Listeners then direct connection requests to the correct instances; otherwise, the appropriate error is returned.

Preventing Clients from Connecting to the Wrong Database
Clients who send connection requests to the wrong host might be connected to the wrong database instance, or they might receive an error message such as the following: ORA-01033: ORACLE initialization or shutdown in progress You can prevent clients from connecting to the wrong database by using database services.

Creating a Service for orcl Instance Invoke SQL*Plus on your primary database host and connect as the SYSDBA user.

SQL> show parameter service_names

On Primary

Managing Services
 
• Database services can be managed by using the DBMS_SERVICE package when Oracle Restart is not used.
• Database services attributes:
– Service Name: For administration of the service
– Network Name: For services that are implemented for external client connections
– Transparent Application Failover (TAF) attributes: For TAFenabled client connections 

Managing Services

Database services are implemented with the DBMS_SERVICE package. This package provides for the creation, deletion, starting, and stopping of services for a single database instance.
 
Note: For Oracle Real Application Clusters (RAC) and single-instance databases managed by Oracle Restart or Oracle Clusterware (which includes ASM), the DBMS_SERVICE procedure is deprecated in Oracle Database 11g Release 2 (11.2) and srvctl should be used instead to create services.




$ srvctl add service -d orcl -s prmy -l PRIMARY -m BASIC -e SELECT -w 1 -z 180
$ lsnrctl services
$ srvctl start service -d orcl -s prmy
$ lsnrctl services

SQL> show parameter service_names

Creating a Service for the stdy Instance

SQL> show parameter service_names

$ srvctl add service -d stdy -s prmy -l PRIMARY -m BASIC -e SELECT -w 1 -z 180
$ srvctl start service -d stdy -s prmy
$ srvctl config service -d stdy -s prmy

Creating an Oracle Net Service Name on Your Primary Database Host

prmy =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2.rhel5.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2sb.rhel5.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prmy.rhel5.com)
    )
  )

Creating an Oracle Net Service Name on Your Standby Database Host

prmy =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2.rhel5.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 11gr2sb.rhel5.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prmy.rhel5.com)
    )
  )

Testing Your Implementation on your primary database

SQL> create user dgtest identified by dgtest;

SQL> grant create session to dgtest;

SQL> grant select_catalog_role to dgtest;

On your physical standby database host, connect to the primary database as the DGTEST user by using the prmy service.
$ sqlplus dgtest/dgtest@prmy

SQL> select host_name from v$instance;

HOST_NAME
----------------------------------------------------------------
11gr2.rhel5.com

SQL> select failover_type, failover_method, failed_over from v$session where username = 'DGTEST';

FAILOVER_TYPE FAILOVER_M FAI
------------- ---------- ---
SELECT        BASIC      NO

Return to your primary database

$ dgmgrl

DGMGRL> connect sys/oracle
DGMGRL> switchover to stdy;
DGMGRL> connect sys/oracle
DGMGRL> startup mount

On your primary database host, check the listener services again. prmy is no longer listed.

$ lsnrctl services

Return to your (original) physical standby database host. In your existing SQL*Plus session, query V$INSTANCE to determine what host you are now connected to

SQL> select host_name from v$instance;
SQL> select failover_type, failover_method, failed_over from v$session where username = 'DGTEST';
SQL> show parameter service

$ lsnrctl services listener

If the prmy service does not start. Set the local_listener value to null as it is only needed for Non-default listener port to do dynamic registeration with the service. 

Thanks (Commands Plz)