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

Application Client Failover and DR Test

Posted by Hendry chinnapparaj on June 17, 2010

Application Client Failover and DR Test

Hendry Dasan – DBA

Thursday, 17 June 2010

Server and Database original setup Details

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

1.1     Environment Details – original setting

  • 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) – Dasan Street
  • Standby Site: 1 Node (DEVU009N4) – 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

Tnsnames.ora setting in the  remote client

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

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)

)

)

Data Guard Config Now

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

Note:_ the primary database is already at the DR Site- FC – before this exercise.

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n4

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> show parameter service_name

NAME                                 TYPE        VALUE

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

service_names                        string      cps

INST_NAME

—————-

HOST_NAME

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

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n4

MAXIMUM AVAILABILITY OK                              134         134

18/05 12:01:30          0

Connection from the remote client – pointing to primary database server (Christ court)

select host_name from v$instance;

HOST_NAME

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

devu009n4

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PHYSICAL STANDBY MOUNTED

SQL> show parameter service_name

NAME                                 TYPE        VALUE

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

service_names                        string      cpstest_f

DOING A SWITCHOVER NOW from the current Primary Node(devu009n4) at Christ Court while the TOAD Client is still connected.

TEST1 à SWITCHOVER FROM CHRIST COURT TO DASAN STREET

DGMGRL> CONNECT SYS/CPSTEST

Connected.

DGMGRL> switchover to cpstest_j;

Performing switchover NOW, please wait…

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

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

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

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

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

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

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

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “cpstest_j”

DGMGRL>

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PHYSICAL STANDBY MOUNTED

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n4

SQL> show parameter service_name à Note:- the service_name changed back to cpstest_f in the standby role.

NAME                                 TYPE        VALUE

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

service_names                        string      cpstest_f

Connection from the remote TOAD client again – pointing to primary database server (Dasan Street)

select host_name from v$instance;

HOST_NAME

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

devu009n3

Also from the command prompt / remote sqlplus client connection to primary database

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

SQL*Plus: Release 10.2.0.4.0 – Production on Tue May 18 13:12:16 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

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

devu009n3

Thursday, June 17, 2010

Query the new Primary database

SQL>  show parameter service_name à Note:- The Primary database always has the service_names as ”cps”

NAME                                 TYPE        VALUE

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

service_names                        string      cps

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

Query the primary and standby database for any offline files

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

select file#,name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’)

no rows selected

NST_NAME

—————-

HOST_NAME

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

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n4

MAXIMUM AVAILABILITY OK                              465         465

17/06 06:03:31          0

SQL> alter system switch logfile;

System altered.

select user from dual;

USER

——————————

CPS_OWNER

1 row selected.

select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME

————— —————-

HOST_NAME

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

VERSION           STARTUP_TIME STATUS       PARALLEL    THREAD# ARCHIVER

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

LOG_SWITCH_WAIT LOGINS     SHUTDOWN_PENDING DATABASE_STATUS   INSTANCE_ROLE

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

ACTIVE_STATE BLOCKED

———— ——-

1 cpstest

devu009n4

10.2.0.4.0        11-JUN-10    OPEN         NO                1 STARTED

ALLOWED    NO               ACTIVE            PRIMARY_INSTANCE

NORMAL       NO

1 row selected.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

DGMGRL>

DGMGRL> switchover to cpstest_j;

Performing switchover NOW, please wait…

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

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

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

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

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

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

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

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “cpstest_j”

DGMGRL>

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 * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME

————— —————-

HOST_NAME

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

VERSION           STARTUP_TIME STATUS       PARALLEL    THREAD# ARCHIVER

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

LOG_SWITCH_WAIT LOGINS     SHUTDOWN_PENDING DATABASE_STATUS   INSTANCE_ROLE

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

ACTIVE_STATE BLOCKED

———— ——-

1 cpstest

devu009n3

10.2.0.4.0        17-JUN-10    OPEN         NO                1 STARTED

ALLOWED    NO               ACTIVE            PRIMARY_INSTANCE

NORMAL       NO

1 row selected.

select user from dual;

USER

——————————

CPS_OWNER

1 row selected.

Apps tested here – successfully connected to the other database.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

INST_NAME

—————-

HOST_NAME

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

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n3

MAXIMUM AVAILABILITY OK                              475         475

17/06 13:56:23          0

Primary

————

SQL> select file#,name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’);

no rows selected

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

——————

YES

SQL> select PROTECTION_MODE,PROTECTION_LEVEL from v$database;

PROTECTION_MODE      PROTECTION_LEVEL

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

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

standby

———-

SQL> select file#,name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’);

no rows selected

TEST1 à SWITCHOVER FROM CHRIST COURT TO DASAN STREET successfully completed

TEST2 à FAILOVER FROM DASAN STREET TO CHRIST COURT

Primary server Query

———————

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

FFF

SQL> select database_role,PROTECTION_MODE from v$database;

DATABASE_ROLE    PROTECTION_MODE

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

PRIMARY          MAXIMUM AVAILABILITY

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

standby server Query

————————–

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PHYSICAL STANDBY MOUNTED

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n4

NOW KILL THE PRIMARY SERVER DEVU009N3 – CAN PULLOUT / DISABLE/ shutdown  THE NETWORK INTERFACE

After killing ther primary server, connect to dgmgrl tool in standby database –

may be a bit slow, but gets connected

– Now the primary server hangs / all the putty sessions hangs

STANDBY SERVER

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”:

— JUST HANGS HERE IN STANDBY SERVER – DEVU009N4

Error: ORA-16662: network timeout when contacting a remote database

DGMGRL> DGMGRL>

DGMGRL> failover to cpstest_f;

Performing failover NOW, please wait…

Failover succeeded, new primary is “cpstest_f”

DGMGRL>

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database (disabled)

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> select PROTECTION_MODE,PROTECTION_LEVEL from v$database;

PROTECTION_MODE      PROTECTION_LEVEL

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

MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME

————— —————-

HOST_NAME

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

VERSION           STARTUP_TIME STATUS       PARALLEL    THREAD# ARCHIVER

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

LOG_SWITCH_WAIT LOGINS     SHUTDOWN_PENDING DATABASE_STATUS   INSTANCE_ROLE

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

ACTIVE_STATE BLOCKED

———— ——-

1 cpstest

devu009n4

10.2.0.4.0        17-JUN-10    OPEN         NO                1 STARTED

ALLOWED    NO               ACTIVE            PRIMARY_INSTANCE

NORMAL       NO

1 row selected.

SQL> select file#,name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’);

no rows selected

NOW BRING UP THE OLD PRIMARY SERVER – ENABLE NETWORK

INTERFACE

Reinstate the old primary as new standby

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                  2083760 bytes

Variable Size             138413136 bytes

Database Buffers          171966464 bytes

Redo Buffers                2109440 bytes

Database mounted.

SQL> select flashback_on from v$database;

FLASHBACK_ON

——————

YES

SO CONNECT TO NEW PRIMARY DATABASE AND ISSUE THE BELOW FOR

REINSTATING THE OLD PRIMARY AS NEW STANDBY DATABASE

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> reinstate database cpstest_j;

Reinstating database “cpstest_j”, please wait…

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

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

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

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Continuing to reinstate database “cpstest_j” …

Reinstatement of database “cpstest_j” succeeded

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

NOW CONNECT TO THE NEW STANDBY DATABASE SERVER

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

SQL> CONN / AS SYSDBA

Connected.

SQL> select name,status,checkpoint_change# from v$datafile where status not in

(‘ONLINE’,’SYSTEM’);  2

no rows selected

SQL> show parameter service_name

NAME                                 TYPE        VALUE

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

service_names                        string      cpstest_j

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PHYSICAL STANDBY MOUNTED

GMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

DGMGRL>

CONNECT TO PRIMARY AND SWITCH LOGS AND CHECK IF THEY ARE IN SYNC

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

INST_NAME

—————-

HOST_NAME

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

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n4

MAXIMUM PERFORMANCE  CHECK CONFIGURATION               4           4

17/06 15:28:37          0

Note:- After the failover, the protection mode changes to maxperformance.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

Succeeded.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

INST_NAME

—————-

HOST_NAME

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

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n4

MAXIMUM AVAILABILITY OK 33          33

21/06 14:48:59          0

1 row selected.

Check TOAD Connection

INSTANCE_NUMBER INSTANCE_NAME

————— —————-

HOST_NAME

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

VERSION           STARTUP_TIME STATUS       PARALLEL    THREAD# ARCHIVER

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

LOG_SWITCH_WAIT LOGINS     SHUTDOWN_PENDING DATABASE_STATUS   INSTANCE_ROLE

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

ACTIVE_STATE BLOCKED

———— ——-

1 cpstest

devu009n4

10.2.0.4.0        17-JUN-10    OPEN         NO                1 STARTED

ALLOWED    NO               ACTIVE            PRIMARY_INSTANCE

NORMAL       NO

1 row selected.

Apps tested here – successfully connected to the other database.

Shalom!

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: