Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Tuesday, August 25, 2015

Oracle Fast Start Failover

Enable & Test Fast Start Failover:


[oracle@collabn2 admin]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/XXXXXX
Connected.
DGMGRL> show configuration

Configuration - DG_PROD

 Protection Mode: MaxAvailability
 Databases:
    msft - Primary database
    orcl - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

For FastStartFailover set the property FastStartFailoverTarget :

DGMGRL> show database 'orcl' FastStartFailoverTarget                              
 FastStartFailoverTarget = ''
DGMGRL> show database 'msft' FastStartFailoverTarget
 FastStartFailoverTarget = ''

DGMGRL> edit database 'orcl' set property FastStartFailoverTarget=msft;
Property "faststartfailovertarget" updated
DGMGRL> edit database 'msft' set property FastStartFailoverTarget=orcl;
Property "faststartfailovertarget" updated

DGMGRL> show database 'msft' FastStartFailoverTarget
 FastStartFailoverTarget = 'orcl'
DGMGRL> show database 'orcl' FastStartFailoverTarget
 FastStartFailoverTarget = 'msft'
DGMGRL>

Verify LogXptMode is SYNC:
DGMGRL> show database 'orcl' LogXptMode
 LogXptMode = 'SYNC'
DGMGRL> show database 'msft' LogXptMode
 LogXptMode = 'SYNC'

Start Observer:  <- Remember after you start the observer CTRL will not be returned

DGMGRL>
DGMGRL> start observer
Observer started

Enable FAST START FAILOVER:

DGMGRL> show configuration

Configuration - DG_PROD

 Protection Mode: MaxAvailability
 Databases:
    msft - Primary database
    orcl - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


DGMGRL> show configuration verbose

Configuration - DG_PROD

 Protection Mode: MaxAvailability
 Databases:
    msft - Primary database
    orcl - (*) Physical standby database

 (*) Fast-Start Failover target

Fast-Start Failover: ENABLED

 Threshold:        30 seconds
 Target:           orcl
 Observer:         collabn1.shaiksameer
 Lag Limit:        30 seconds (not in use)
 Shutdown Primary: TRUE
 Auto-reinstate:   TRUE

Configuration Status:
SUCCESS


DGMGRL> show fast_start failover

Fast-Start Failover: ENABLED

 Threshold:        30 seconds
 Target:           orcl
 Observer:         collabn1.shaiksameer
 Lag Limit:        30 seconds (not in use)
 Shutdown Primary: TRUE
 Auto-reinstate:   TRUE

Configurable Failover Conditions
 Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

 Oracle Error Conditions:
    (none)



Terminate the primary to initiate the Fast-Start Failover:


[oracle@collabn2 ~]$ pgrep -lf "smon.*msft"
31665 ora_smon_msft

[oracle@collabn2 ~]$ pgrep -f  "smon.*msft" | kill -9

[oracle@collabn2 ~]$ pgrep -lf "smon.*msft"

In Alert_log:
Tue Aug 25 11:54:46 2015
PMON (ospid: 31629): terminating the instance due to error 474
Tue Aug 25 11:54:46 2015
System state dump is made for local instance
System State dumped to trace file /u01/app/oracle/diag/rdbms/msft/msft/trace/msft_diag_31637.trc
Trace dumping is performing id=[cdmp_20150825115446]
Termination issued to instance processes. Waiting for the processes to exit
Tue Aug 25 11:54:56 2015
Instance termination failed to kill one or more processes
Instance terminated by PMON, pid = 31629


DGMGRL>
DGMGRL> start observer
Observer started

11:55:17.29  Tuesday, August 25, 2015
Initiating Fast-Start Failover to database "orcl"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "orcl"
11:55:20.65  Tuesday, August 25, 2015


11:58:37.45  Tuesday, August 25, 2015
Initiating reinstatement for database "msft"...
Reinstating database "msft", please wait...
Operation requires shutdown of instance "msft" on database "msft"
Shutting down instance "msft"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "msft" on database "msft"
Starting instance "msft"...
Unable to connect to database
ORA-12541: TNS:no listener

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the REINSTATE command:
   start up and mount instance "msft" of database "msft"

11:58:55.43  Tuesday, August 25, 2015


DGMGRL> connect sys
Password:
Connected.

DGMGRL> show configuration verbose

Configuration - DG_PROD

 Protection Mode: MaxAvailability
 Databases:
    orcl - Primary database
     Warning: ORA-16817: unsynchronized fast-start failover configuration

    msft - (*) Physical standby database (disabled)
     ORA-16661: the standby database needs to be reinstated

 (*) Fast-Start Failover target

Fast-Start Failover: ENABLED

 Threshold:        30 seconds
 Target:           msft
 Observer:         collabn1.shaiksameer
 Lag Limit:        30 seconds (not in use)
 Shutdown Primary: TRUE
 Auto-reinstate:   TRUE

Configuration Status:
WARNING

DGMGRL> reinstate database msft
Reinstating database "msft", please wait...
Reinstatement of database "msft" succeeded


DGMGRL> show configuration verbose

Configuration - DG_PROD

 Protection Mode: MaxAvailability
 Databases:
    orcl - Primary database
    msft - (*) Physical standby database

 (*) Fast-Start Failover target

Fast-Start Failover: ENABLED

 Threshold:        30 seconds
 Target:           msft
 Observer:         collabn1.shaiksameer
 Lag Limit:        30 seconds (not in use)
 Shutdown Primary: TRUE
 Auto-reinstate:   TRUE

Configuration Status:
SUCCESS


After the fast start failover automatic reinstate of standby database failed and I have to issue the command “reinstate database msft” manually from Primary dgmgrl.

SQL> select db_unique_name,open_mode,database_role from v$database;

DB_UNIQUE_NAME              OPEN_MODE        DATABASE_ROLE
------------------------------ -------------------- ----------------
msft                  MOUNTED           PHYSICAL STANDBY


SQL> select db_unique_name,open_mode,database_role from v$database;

DB_UNIQUE_NAME              OPEN_MODE        DATABASE_ROLE
------------------------------ -------------------- ----------------
orcl                  READ WRITE        PRIMARY

Configure Oracle Data Guard

Configure & Enable Data guard:


Configure listeners with servicenames SID_DGMGRL & SID_DGB on Primary and Standby.

Standby:
[oracle@collabn1 ~]$ /u01/app/oracle/product/12.1.0/grid/bin/lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-AUG-2015 17:02:23

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                23-AUG-2015 17:00:54
Uptime                    0 days 0 hr. 1 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/collabn1/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.78.51)(PORT=1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.78.61)(PORT=1521)))
Services Summary...
Service "orcl.shaiksameer" has 1 instance(s).
 Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_dgmgrl.shaiksameer" has 1 instance(s).
 Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully


Primary:
[oracle@collabn2 admin]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-AUG-2015 16:53:16

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=collabn2.shaiksameer)(PORT=1621))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                23-AUG-2015 16:33:31
Uptime                    0 days 0 hr. 19 min. 45 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.2/SHAIKPROD/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/collabn2/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=collabn2.shaiksameer)(PORT=1621)))
Services Summary...
Service "PROD2" has 1 instance(s).
 Instance "PROD2", status UNKNOWN, has 1 handler(s) for this service...
Service "msft.shaiksameer" has 1 instance(s).
 Instance "msft", status UNKNOWN, has 1 handler(s) for this service...
Service "msft_dgmgrl.shaiksameer" has 1 instance(s).
 Instance "msft", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Start the dg_broker on Standby & Primary:

SQL> select db_unique_name,database_role,open_mode from v$database;

DB_UNIQUE_NAME              DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- --------------------
msft                  PRIMARY       READ WRITE


SQL> show parameter dg_broker

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1            string     /u01/app/oracle/product/11.2.0.2/SHAIKPROD/dbs/dr1msft.dat
dg_broker_config_file2            string     /u01/app/oracle/product/11.2.0.2/SHAIKPROD/dbs/dr2msft.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
SQL>



SQL> select db_unique_name,database_role,open_mode from v$database;

DB_UNIQUE_NAME              DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- --------------------
orcl                  PHYSICAL STANDBY MOUNTED

SQL> show parameter dg_broker

NAME                    TYPE     VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1            string     /u01/app/oracle/product/11.2.0.2/SHAIKPROD/dbs/dr1orcl.dat
dg_broker_config_file2            string     /u01/app/oracle/product/11.2.0.2/SHAIKPROD/dbs/dr2orcl.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
SQL>

Configure Data Guard:

[oracle@collabn2 admin]$ dgmgrl

DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/XXXXXXX@msft
Connected.

DGMGRL> create configuration 'DG_PROD' as primary database is 'msft' connect identifier is 'msft';
Configuration "DG_PROD" created with primary database "msft"

DGMGRL> add database 'orcl' as connect identifier is 'orcl';
Database "orcl" added

DGMGRL> show configuration;

Configuration - DG_PROD

 Protection Mode: MaxAvailability
 Databases:
    msft - Primary database
    orcl - Physical standby database

Fast-Start Failover: DISABLED


Configuration Status:
DISABLED




Issue-1:
I got below errors due to missing servicename entry orcl_DGB.shaiksameer once I added the service name containing SID_DGB.domain below errors went away.


2015-08-23 23:38:03.431                      NSV1: Site orcl returned ORA-16665.
2015-08-23 23:38:18.433                      NSV1: Site orcl returned ORA-16664.
2015-08-23 23:38:18.433 00001000   134389449 DMON: Database orcl returned ORA-16664
2015-08-23 23:38:18.434 00001000   134389449       for opcode = CTL_ENABLE, phase = RESYNCH, req_id = 1.1.134389449
2015-08-23 23:38:18.435                      INSV: Received message for inter-instance publication
2015-08-23 23:38:18.435                            req ID 1.1.134389449, opcode CTL_ENABLE, phase BEGIN, flags 5
2015-08-23 23:38:18.434 00001000   134389449 DMON: status from rfi_post_instances() for CTL_ENABLE = ORA-00000
2015-08-23 23:38:18.435 00001000   134389449 DMON: dispersing message to standbys for ENABLE phase BEGIN
2015-08-23 23:38:18.435 00001000   134389449 DMON: Entered rfmeexinst for phase 1
2015-08-23 23:38:18.435                      DMON: Instance ID 1 is the broker FSFP HOME instance
2015-08-23 23:38:18.435                      DMON: Instance ID 1 is the broker health check master
2015-08-23 23:38:18.435                      INSV: Reply received for message with
2015-08-23 23:38:18.435                            req ID 1.1.134389449, opcode CTL_ENABLE, phase BEGIN


alert_msft.log
***********************************************************************

Fatal NI connect error 12537, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=collabn1.shaiksameer)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl_DGB.shaiksameer)(CID=(PROGRAM=oracle)(HOST=collabn2.shaiksameer)(USER=oracle))))

 VERSION INFORMATION:
   TNS for Linux: Version 11.2.0.1.0 - Production
   TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
 Time: 23-AUG-2015 23:47:04
 Tracing not turned on.
 Tns error struct:
    ns main err code: 12537
   
TNS-12537: TNS:connection closed
    ns secondary err code: 12560
    nt main err code: 507



issue2:
DGMGRL> show database verbose 'orcl';

Database - orcl

 Role:            PHYSICAL STANDBY
 Intended State:  APPLY-ON
 Transport Lag:   0 seconds
 Apply Lag:       0 seconds
 Real Time Query: OFF
 Instance(s):
    orcl
     Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
     Warning: ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the database setting
     Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting

DGMGRL> edit database 'orcl' set property  LogArchiveMaxProcesses=4;
Property "logarchivemaxprocesses" updated
DGMGRL> edit database  'msft' set property  LogArchiveMaxProcesses=4;
Property "logarchivemaxprocesses" updated

DGMGRL> edit database 'orcl' set property LogArchiveMinSucceedDest=1;
Property "logarchiveminsucceeddest" updated
DGMGRL> edit database 'msft' set property LogArchiveMinSucceedDest=1;
Property "logarchiveminsucceeddest" updated

To fix the issue2:

DGMGRL> edit database 'orcl' set property ArchiveLagTarget=3600;
Property "archivelagtarget" updated
DGMGRL> edit database 'msft' set property ArchiveLagTarget=3600;
Property "archivelagtarget" updated


DGMGRL> show configuration

Configuration - DG_PROD

 Protection Mode: MaxAvailability
 Databases:
    msft - Primary database
    orcl - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>


DGMGRL> show database verbose 'orcl';

Database - orcl

 Role:            PHYSICAL STANDBY
 Intended State:  APPLY-ON
 Transport Lag:   0 seconds
 Apply Lag:       0 seconds
 Real Time Query: OFF
 Instance(s):
    orcl

 Properties:
    DGConnectIdentifier             = 'orcl'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'SYNC'
    DelayMins                       = '0'
    Binding                             =      'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'auto'
    ArchiveLagTarget                = '3600'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'collabn1.shaiksameer'
    SidName                         = 'orcl'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl_DGMGRL.shaiksameer)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '+DATA2'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS


Initialization Parameters set:
On Primary: 
SQL> show parameter fal
------------------------------------ ----------- ------------------------------
fal_client                 string
fal_server                 string     msft
log_archive_config             string     DG_CONFIG=(msft,orcl)


On Standby:
SQL> show parameter  fal
------------------------------------ ----------- ------------------------------
fal_client                 string
fal_server                 string     orcl
log_archive_config             string     DG_CONFIG=(orcl,msft)