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

Oracle 11g R2 - Configuring Data Guard / Part II

Enabling Data Guard Broker

The following article, depends on the previous article which is “Configuring Data Guard Part I”.

After I created the physical standby server, although it’s necessary, I decided to enable Data Guard Broker. The advantage of Data Guard Broker is easier management. You’ll see this on my other article (Part III). 

So here are the steps. 

*** 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> show parameter dg_broker

NAME                                 TYPE        VALUE

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

dg_broker_config_file1               string      /u01/app/oracle/product/11.2.0

                                                 /db_1/dbs/dr1primary.dat

dg_broker_config_file2               string      /u01/app/oracle/product/11.2.0

                                                 /db_1/dbs/dr2primary.dat

dg_broker_start                      boolean     FALSE

SQL> alter system set dg_broker_start=true;

System altered.

SQL> show parameter dg_broker_start

NAME                                 TYPE        VALUE

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

dg_broker_start                      boolean     TRUE

STANDBY  SERVER

SQL> show parameter dg_broker

NAME                                 TYPE        VALUE

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

dg_broker_config_file1               string      /u01/app/oracle/product/11.2.0

                                                 /db_1/dbs/dr1primary.dat

dg_broker_config_file2               string      /u01/app/oracle/product/11.2.0

                                                 /db_1/dbs/dr2primary.dat

dg_broker_start                      boolean     FALSE

SQL> alter system set dg_broker_start=true;

System altered.

SQL> show parameter dg_broker_start

NAME                                 TYPE        VALUE

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

dg_broker_start                      boolean     TRUE

LISTENER.ORA    --STANDBY SERVER -- Add this section to listener.ora.

SID_LIST_LISTENER =

  (SID_LIST =

  

  (SID_DESC = 

    (GLOBAL_DBNAME = sby1_DGMGRL.europe)

    (SID_NAME = sby1)

    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1))

  )

lsnrctl stop

lsnrctl start

LISTENER.ORA    --PRIMARY SERVER -- Add this section to listener.ora.

SID_LIST_LISTENER =

  (SID_LIST =

  

  (SID_DESC = 

    (GLOBAL_DBNAME = primary_DGMGRL.europe)

    (SID_NAME = primary)

    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1))

  )

lsnrctl stop

lsnrctl start

Still on the primary server

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

ORA-16532: Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL

DGMGRL> create configuration 'DGConfig1' as 

> primary database is 'primary'

> connect identifier is primary;

Configuration "DGConfig1" created with primary database "primary"

DGMGRL> show configuration

Configuration - DGConfig1

  Protection Mode: MaxPerformance

  Databases:

    primary - Primary database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL> add database 'sby1' as 

> connect identifier is sby1;

Database "sby1" added

DGMGRL> show configuration

Configuration - DGConfig1

  Protection Mode: MaxPerformance

  Databases:

    primary - Primary database

    sby1    - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

DGMGRL> enable configuration;

Enabled.

DGMGRL> show configuration

Configuration - DGConfig1

  Protection Mode: MaxPerformance

  Databases:

    primary - Primary database

    sby1    - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>