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

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)
 

No comments: