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

Oracle10g Database Failover Test using Data Guard Broker

Posted by Hendry chinnapparaj on May 18, 2010

Hendry Dasan – DBA

Tuesday, 18 May 2010

Oracle10g Database Failover Test using Data Guard Broker

The following is a step by step operation on the Data Guard Failover operations for Oracle10g Database in an Oracle11gR2 ASM Env. After the successful failover, the original data Guard Configuration is reverted with a switchover

Server and Database original setup Details

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

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

BUT IN THE CURRENT SETTING THE PRIMARY IS “cpstest_f” and standby is “cpstest_j”

Connect to primary server – devu009n4 and query the below

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> connect sys/cpstest

Connected.

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

——————

YES

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n4

SQL> select database_role,PROTECTION_MODE from v$database;

DATABASE_ROLE    PROTECTION_MODE

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

PRIMARY          MAXIMUM AVAILABILITY

In standby database server

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

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PHYSICAL STANDBY MOUNTED

SQL>

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

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

After killing ther primary server, connect to dgmgrl tool in standby database – may be a bit slow, but gets connected

devu009n3 $

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.

Connected.

DGMGRL> DGMGRL>

DGMGRL>

DGMGRL> connect sys/cpstest

Connected.

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

Error: ORA-16625: cannot reach the database

DGMGRL> failover to cpstest_j;

Performing failover NOW, please wait…

Failover succeeded, new primary is “cpstest_j”

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

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

SQL>  select FLASHBACK_ON from v$database;

FLASHBACK_ON

——————

YES

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database (disabled)

Current status for “cpstest_dgb”:

SUCCESS

DGMGRL> show database cpstest_j;

Database

Name:            cpstest_j

Role:            PRIMARY

Enabled:         YES

Intended State:  ONLINE

Instance(s):

cpstest

Current status for “cpstest_j”:

SUCCESS

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

no rows selected

query from the already connected remote client and test which server it connected to – the output below shows that the client has successfully failedover to the new primary server without being disconnected.

15:04:55 SQL> show user

USER is “DGTEST”

15:21:01 SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

Alert log in new primary shows now

PING[ARC0]: Heartbeat failed to connect to standby ‘(DESCRIPTION=(ADDRESS_LIST=(address=(protocol=TCP)(host=devu009n3)(port=1521)))(CONNECT_DATA=(SERVICE_NAME=cpstest_j_XPT)(INSTANCE_NAME=cpstest)(SERVER=dedicated)))’. Error is 16009.

Tue May 18 15:15:26 2010

ALTER SYSTEM SET log_archive_dest_state_2=’RESET’ SCOPE=BOTH;

SQL> show parameter service_name

NAME                                 TYPE        VALUE

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

service_names                        string      cps

NOW BRING UP THE OLD PRIMARY SERVER – ENABLE NETWORK INTERFACE

Reinstate the old primary as new standby

devu009n4 $ . oraenv

ORACLE_SID = [cpstest] ?

devu009n4 $ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Tue May 18 15:16:47 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 database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n4

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL>

SQL>

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 database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          MOUNTED

IMPORTANT – DON’T ISSUE THE FOLLOWING COMMAND IN THE OLD PRIMARY DATABASE AS SHOWN BELOW

YOU WILL GET  ORA-16795 ERRORS

GMGRL> connect sys/cpstest

Connected.

DGMGRL>

DGMGRL>

DGMGRL>

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Error: ORA-16795: database resource guard detects that database re-creation is required

Configuration details cannot be determined by DGMGRL

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> reinstate database cpstest_f;

Error: ORA-16795: database resource guard detects that database re-creation is required

Configuration details cannot be determined by DGMGRL

1.2      ORA-16795: database resource guard detects that database re-creation is required

Cause: In the act of failover or switchover, the database resource guard may have detected that re-creation of the database is necessary. This occurs when the database resource guard recognizes a situation in which the database in question cannot be a viable standby database for the new primary database. Until this error status is resolved for this database, information about this database and the broker configuration to which it belongs is unavailable to a broker client that is connected to this database. Therefore, all commands directed by that client to this database cannot be completed.
Action: Re-create (or flash back) the standby database. Connect to the primary database in the broker configuration and reenable broker management of that database. At this point you may connect to that standby database and resume issuing client commands. Alternatively, many client commands that cannot be completed at the standby database when in this error state can be completed successfully when issued to the primary database. In this case, simply reconnect to the primary database and retry the command.

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_f;

Reinstating database “cpstest_f”, 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 startup of instance “cpstest” on database “cpstest_f”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Continuing to reinstate database “cpstest_f” …

Reinstatement of database “cpstest_f” succeeded

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

Check if the data guard is in sync

SELECT INST_NAME, HOST_NAME, PROTECTION_MODE, SYNCHRONIZATION_STATUS,

LOG_ARCHIVED, LOG_APPLIED, TIME_APPLIED,

LOG_ARCHIVED – LOG_APPLIED LOG_GAP

FROM (SELECT   INST_ID, INSTANCE_NAME INST_NAME, HOST_NAME

FROM GV$INSTANCE

ORDER BY INST_ID) NAME,

(SELECT   INST_ID, PROTECTION_MODE, SYNCHRONIZATION_STATUS

FROM GV$ARCHIVE_DEST_STATUS

WHERE DEST_ID = 2

ORDER BY INST_ID) STAT,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_ARCHIVED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1

AND ARCHIVED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) ARCH,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_APPLIED,

TO_CHAR (MAX (COMPLETION_TIME),

‘DD/MM HH24:MI:SS’

) TIME_APPLIED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 2

AND APPLIED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) APPL

WHERE NAME.INST_ID = STAT.INST_ID

AND NAME.INST_ID = ARCH.THREAD#

AND NAME.INST_ID = APPL.THREAD#;

INST_NAME

—————-

HOST_NAME

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

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n3

MAXIMUM PERFORMANCE  CHECK CONFIGURATION               4           4

18/05 15:51:19          0

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

no rows selected

SQL> show parameter service_name

NAME                                 TYPE        VALUE

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

service_names                        string      cps

The remote client is still connected

15:21:06 SQL> show user

USER is “DGTEST”

15:53:11 SQL> show user

USER is “DGTEST”

15:53:16 SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

Query the new standby database now

SQL> conn / as sysdba

Connected.

SQL> 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> select name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’);

no rows selected

SQL> show parameter service_name

NAME                                 TYPE        VALUE

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

service_names                        string      cpstest_f

DGMGRL> connect sys/cpstest

Connected.

DGMGRL>  show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

Reverting to original data guard configuration

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

Check if the data guard is in sync

SELECT INST_NAME, HOST_NAME, PROTECTION_MODE, SYNCHRONIZATION_STATUS,

LOG_ARCHIVED, LOG_APPLIED, TIME_APPLIED,

LOG_ARCHIVED – LOG_APPLIED LOG_GAP

FROM (SELECT   INST_ID, INSTANCE_NAME INST_NAME, HOST_NAME

FROM GV$INSTANCE

ORDER BY INST_ID) NAME,

(SELECT   INST_ID, PROTECTION_MODE, SYNCHRONIZATION_STATUS

FROM GV$ARCHIVE_DEST_STATUS

WHERE DEST_ID = 2

ORDER BY INST_ID) STAT,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_ARCHIVED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1

AND ARCHIVED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) ARCH,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_APPLIED,

TO_CHAR (MAX (COMPLETION_TIME),

‘DD/MM HH24:MI:SS’

) TIME_APPLIED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 2

AND APPLIED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) APPL

WHERE NAME.INST_ID = STAT.INST_ID

AND NAME.INST_ID = ARCH.THREAD#

AND NAME.INST_ID = APPL.THREAD#;

INST_NAME

—————-

HOST_NAME

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

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n3

MAXIMUM PERFORMANCE  CHECK CONFIGURATION               6           6

18/05 16:06:19          0

Remote client connected to devu009n3 as being the primary server

16:08:12 SQL> show user

USER is “DGTEST”

16:08:15 SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

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

Database dismounted.

ORACLE instance shut down.

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

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

SQL> conn / as sysdba

Connected.

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

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

SQL>

Again check the remote client connection – failedover to primary database now

16:08:12 SQL> show user

USER is “DGTEST”

16:08:15 SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

16:08:24 SQL>

16:16:58 SQL>

16:16:59 SQL> select host_name from v$instance;

HOST_NAME

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

devu009n4

16:17:03 SQL> show user

USER is “DGTEST”

Now connect to PRIMARY DATABASE SERVER – devu009n4 and query

 

SQL> conn / as sysdba

Connected.

SQL> show parameter service_name

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

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

devu009n4

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

no rows selected

DGMGRL> 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

Check if the data guard is in sync

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> alter system archive log current;

System altered.

SELECT INST_NAME, HOST_NAME, PROTECTION_MODE, SYNCHRONIZATION_STATUS,

LOG_ARCHIVED, LOG_APPLIED, TIME_APPLIED,

LOG_ARCHIVED – LOG_APPLIED LOG_GAP

FROM (SELECT   INST_ID, INSTANCE_NAME INST_NAME, HOST_NAME

FROM GV$INSTANCE

ORDER BY INST_ID) NAME,

(SELECT   INST_ID, PROTECTION_MODE, SYNCHRONIZATION_STATUS

FROM GV$ARCHIVE_DEST_STATUS

WHERE DEST_ID = 2

ORDER BY INST_ID) STAT,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_ARCHIVED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1

AND ARCHIVED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) ARCH,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_APPLIED,

TO_CHAR (MAX (COMPLETION_TIME),

‘DD/MM HH24:MI:SS’

) TIME_APPLIED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 2

AND APPLIED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) APPL

WHERE NAME.INST_ID = STAT.INST_ID

AND NAME.INST_ID = ARCH.THREAD#

AND NAME.INST_ID = APPL.THREAD#;

INST_NAME

—————-

HOST_NAME

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

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n4

MAXIMUM PERFORMANCE  CHECK CONFIGURATION              16          16

18/05 16:23:02          0

On the primary database, change the protection mode back to maximum availability

select NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

NAME      OPEN_MODE  PROTECTION_MODE      PROTECTION_LEVEL

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

CPSTEST   READ WRITE MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Database altered.

SQL> select NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

NAME      OPEN_MODE  PROTECTION_MODE      PROTECTION_LEVEL

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

CPSTEST   READ WRITE MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

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: