Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Client Failover Test Setup and Test in Data Guard Broker Env

Posted by Hendry chinnapparaj on May 18, 2010

Client failover Test for DR

1.1     Environment Details

  • OS: IBM AIX 5.3 TL05
  • Hardware: IBM P-Series 595 Model
  • Oracle Software: Oracle 10gR2 Enterprise Edition -10204 (64bit)
  • ASM Oracle 11Gr2 Instance
  • Primary Site: 1 Node (DEVU009N3) in Dasan Street
  • Standby Site: 1 Node (DEVU009N4) in Christ Court
  • Storage: ASM11gR2
  • Database Name: CPSTEST
  • Instance Name: CPSTEST
  • Primary DB_UNIQUE_NAME: CPSTEST_J
  • Standby DB_UNIQUE_NAME: CPSTEST_F
  • Standby Type: Physical Standby

PRIMARY

————–

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n3

SQL> SHOW parameter service_names

NAME                                 TYPE        VALUE

———————————— ———– ——————————

service_names                        string      cpstest_j

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

devu009n3 $ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production on 14-MAY-2010 16:16:24

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

————————

Alias                     LISTENER

Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production

Start Date                13-MAY-2010 13:27:56

Uptime                    1 days 2 hr. 48 min. 27 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      ON

Listener Parameter File   /u01/oracle/product/11.2.0/grid/network/admin/listener.ora

Listener Log File         /u01/oracle/diag/tnslsnr/devu009n3/listener/alert/log.xml

Listening Endpoints Summary…

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

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=devu009n3.greatwest.com)(PORT=1521)))

Services Summary…

Service “+ASM” has 1 instance(s).

Instance “+ASM”, status READY, has 1 handler(s) for this service…

Service “cpstest_j” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j_DGB” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j_XPT” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j_dgmgrl” has 1 instance(s).

Instance “cpstest”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

Creating a service

Set echo on

SQL> exec DBMS_SERVICE.CREATE_SERVICE ( –

> service_name => ‘cps’, –

> network_name => ‘cps’, –

> failover_method => ‘BASIC’, –

> failover_type => ‘SELECT’, –

> failover_retries => 180, –

> failover_delay => 1);

PL/SQL procedure successfully completed.

Start the service

SQL> exec dbms_service.start_service(‘cps’);

PL/SQL procedure successfully completed.

Now the service added to listener

devu009n3 $ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production on 14-MAY-2010 16:18:39

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

————————

Alias                     LISTENER

Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production

Start Date                13-MAY-2010 13:27:56

Uptime                    1 days 2 hr. 50 min. 42 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      ON

Listener Parameter File   /u01/oracle/product/11.2.0/grid/network/admin/listener.ora

Listener Log File         /u01/oracle/diag/tnslsnr/devu009n3/listener/alert/log.xml

Listening Endpoints Summary…

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

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=devu009n3.greatwest.com)(PORT=1521)))

Services Summary…

Service “+ASM” has 1 instance(s).

Instance “+ASM”, status READY, has 1 handler(s) for this service…

Service “cps” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j_DGB” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j_XPT” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j_dgmgrl” has 1 instance(s).

Instance “cpstest”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

SQL> show parameter service_names

NAME                                 TYPE        VALUE

———————————— ———– ——————————

service_names                        string      cps

Note the value has changed to “cps” now from cpstest_j

creating an after startup trigger

SQL> @create_trigger.sql

SQL> set echo on

SQL> create or replace trigger manage_service after startup on database

2  declare

3     role varchar(30);

4  begin

5     select database_role into role from v$database;

6     if role = ‘PRIMARY’ then

7        DBMS_SERVICE.START_SERVICE(‘cps’);

8     Else

9        DBMS_SERVICE.STOP_SERVICE(‘cps’);

10     End if;

11  End;

12  /

Trigger created.

Create Oracle net service name on primary and standby

cps =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = devu009n3.greatwest.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = devu009n4.greatwest.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = cps.greatwest.com)

)

)

devu009n3 $ tnsping cps

TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – Production on 14-MAY-2010 16:44:27

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n3.greatwest.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n4.greatwest.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = cps.greatwest.com)))

OK (0 msec)

devu009n4 $ tnsping cps

TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – Production on 14-MAY-2010 16:42:28

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n3.greatwest.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n4.greatwest.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = cps.greatwest.com)))

OK (10 msec)

Testing the Implementation

——————————–

Create user dgtest identified by dgtest;

Grant create session to dgtest;

Grant select_catalog_role to dgtest;

Connect to primary instance now

devu009n4 $ sqlplus dgtest/dgtest@cps

SQL*Plus: Release 10.2.0.4.0 – Production on Fri May 14 17:16:43 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME

————— —————-

HOST_NAME

—————————————————————-

VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT

—————– ——— ———— — ———- ——- —————

LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO

———- — —————– —————— ——— —

1 cpstest

devu009n3

10.2.0.4.0        14-MAY-10 OPEN         NO           1 STARTED

ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO

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

FAILOVER_TYPE FAILOVER_M FAI

————- ———- —

SELECT        BASIC      NO

devu009n3 $ dgmgrl

DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – 64bit Production

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

Welcome to DGMGRL, type “help” for information.

DGMGRL> sys/cpstest

Unrecognized command “sys”, try “help”

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

DGMGRL> switchover to cpstest_f;

Performing switchover NOW, please wait…

Operation requires shutdown of instance “cpstest” on database “cpstest_j”

Shutting down instance “cpstest”…

ORA-01109: database not open

atabase dismounted.

ORACLE instance shut down.

Operation requires startup of instance “cpstest” on database “cpstest_j”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Operation requires startup of instance “cpstest” on database “cpstest_f”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “cpstest_f”

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

Select failover_type, failover_method, failed_over from v$session where username = ‘DGTEST’;

FAILOVER_TYPE FAILOVER_M FAI

————- ———- —

SELECT        BASIC      YES

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n4

SQL> show parameter service

NAME                                 TYPE        VALUE

———————————— ———– ——————————

service_names                        string      cps

Also test from a remote client – my laptop – connect thru sqlplus to the primary server when the stby listener is down

– enter the tns string in my laptop 10g tns

cps =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = devu009n5.greatwest.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = devu009n4.greatwest.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = cps)

)

)

— note I have given a wrong server name for the first host, thereby trying to use the second host to connect

— it has connected to the primary server – second line – host

C:\Documents and Settings\bb15908>sqlplus dgtest/dgtest@cps

SQL*Plus: Release 10.2.0.4.0 – Production on Fri May 14 17:40:10 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n4

Advertisements

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

 
%d bloggers like this: