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

Simple switchover from primary site to DR using Data Guard Broker

Posted by Hendry chinnapparaj on May 18, 2010

Hendry Dasan – DBA

Tuesday, 18 May 2010

Simple switchover from primary site to DR using Data Guard Broker

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

TEST2 à SWITCHOVER FROM DASAN STREET to CHRIST COURT

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

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

devu009n3

SQL>

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> /

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

devu009n3

MAXIMUM AVAILABILITY OK                              141         141

18/05 13:18:10          0

CONNECTION FROM THE REMOTE CLIENT TO PRIMARY SERVER

 

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

Now I connect to the current standby server(devu009n4) using dgmrgl tool and invoke the switchover from JS to FC

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

Make sure you connect as “sys” user for a successful switchover.

DGMGRL> connect sys/cpstest

Connected.

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

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

without disconnecting the dgtest client, query the database again – the hostname indicates that the client has failedover to the other server

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

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n4

SQL>

Also confirmed from this query

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

FAILOVER_TYPE FAILOVER_M FAI

————- ———- —

SELECT        BASIC      YES

Also ensure that all the readonly datafiles and other files are ONLINE

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

no rows selected

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

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

devu009n4

MAXIMUM AVAILABILITY OK                              149         146

18/05 13:27:03          3

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

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: