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

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

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

**********************************************************************************


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]


My Cloud Certifications:

AWS Certified Solutions Architect Associate

Oracle Cloud Infrastructure 2018 Certified Architect Associate.

Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.

Oracle Database Cloud Administrator Certified Professional.

Oracle Database Cloud Service Operations Certified Associate.

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)