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 and DR Switchover

Posted by Hendry chinnapparaj on May 18, 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)- 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

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 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) while the TOAD Client is still connected.

TEST1 à SWITCHOVER FROM FRAMCE 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

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

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: