Oracle 11g R2 - Configuring Data Guard / Part III

Performing a Switchover Test

The last article is about performing a switchover test. After you configure the Data Guard configuration, it’s a good practice to simulate a disaster. There are also other ways but I’m going to make a switchover test only. 

*** Be careful about the server that you execute the commands on. In every section, there’s a note about on which server that the script is going to be executed***

PRIMARY SERVER

SQL/PLUS

[oracle@rome ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 28 10:43:05 2010

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

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

SQL> create table hr.emp_hist as select * from hr.employees;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> exit

DGMGRL

[oracle@rome ~]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.1.0 - Production

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

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/Oracle_1

Connected.

DGMGRL> show database verbose 'primary';

Database - primary

  Role:            PRIMARY

  Intended State:  TRANSPORT-ON

  Instance(s):

    primary

  Properties:

    DGConnectIdentifier             = 'primary'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'ASYNC'

    DelayMins                       = '0'

    Binding                         = 'optional'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'MANUAL'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '4'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = ''

    LogFileNameConvert              = ''

    FastStartFailoverTarget         = ''

    StatusReport                    = '(monitor)'

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    HostName                        = 'rome.europe'

    SidName                         = 'primary'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rome.europe)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=primary_DGMGRL.europe)(INSTANCE_NAME=primary)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = '/u01/app/oracle/flash_recovery_area'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = '%t_%s_%r.dbf'

    TopWaitEvents                   = '(monitor)'

Database Status:

SUCCESS

STANDBY SERVER

[oracle@venice ~]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.1.0 - Production

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

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/Oracle_1

Connected.

DGMGRL> show database verbose 'sby1';

Database - sby1

  Role:            PHYSICAL STANDBY

  Intended State:  APPLY-ON

  Transport Lag:   0 seconds

  Apply Lag:       0 seconds

  Real Time Query: OFF

  Instance(s):

    sby1

  Properties:

    DGConnectIdentifier             = 'sby1'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'ASYNC'

    DelayMins                       = '0'

    Binding                         = 'OPTIONAL'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '5'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = '/primary/, /sby1/'

    LogFileNameConvert              = '/primary/, /sby1/'

    FastStartFailoverTarget         = ''

    StatusReport                    = '(monitor)'

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    HostName                        = 'venice.europe'

    SidName                         = 'sby1'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=venice.europe)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sby1_DGMGRL.europe)(INSTANCE_NAME=sby1)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = '/u01/app/oracle/flash_recovery_area'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = '%t_%s_%r.dbf'

    TopWaitEvents                   = '(monitor)'

Database Status:

SUCCESS

DGMGRL>

PRIMARY SERVER

DGMGRL> edit database 'primary' set property DbFileNameConvert='/sby1/, /primary/';

Property "dbfilenameconvert" updated

DGMGRL> edit database 'primary' set property

> LogFileNameConvert='/sby1/, /primary/';

Property "logfilenameconvert" updated

DGMGRL> 

DGMGRL> show configuration ;

Configuration - DGConfig1

  Protection Mode: MaxPerformance

  Databases:

    sby1    - Primary database

    primary - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

SQL> select status from v$instance;

STATUS

------------

MOUNTED

STANDBY SERVER

DGMGRL> show configuration

Configuration - DGConfig1

  Protection Mode: MaxPerformance

  Databases:

    sby1    - Primary database

    primary - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

SQL> select status from v$instance;

STATUS

------------

OPEN

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

sby1

LATER SWITCHOVER TO PRIMARY AGAIN

SERVER: SBY1

DGMGRL> switchover to primary;

Performing switchover NOW, please wait...

New primary database "primary" is opening...

Operation requires shutdown of instance "sby1" on database "sby1"

Shutting down instance "sby1"...

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance "sby1" on database "sby1"

Starting instance "sby1"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "primary"

DGMGRL>

DGMGRL> show configuration;

Configuration - DGConfig1

  Protection Mode: MaxPerformance

  Databases:

    primary - Primary database

    sby1    - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>