27 March, 2017

12cR1 RAC Posts -- 8d : Registering the two databases in DataGuard Broker

Continuing my series on setting up a non-RAC, FileSystem Standby for a RAC, ASM Database....

On the Standby:

[oracle@oem132 ~]$ . oraenv
ORACLE_SID = [oracle] ? STBY
The Oracle base has been set to /u01/app/oracle
[oracle@oem132 ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 27-MAR-2017 22:24:54

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

Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oem132/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oem132.racattack)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oem132.racattack)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                27-MAR-2017 22:24:58
Uptime                    0 days 0 hr. 0 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oem132/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oem132.racattack)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "STBY" has 1 instance(s).
  Instance "STBY", status UNKNOWN, has 2 handler(s) for this service...
The command completed successfully
[oracle@oem132 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 27 22:25:07 2017

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             230689520 bytes
Database Buffers          599785472 bytes
Redo Buffers                5455872 bytes
SQL> alter database mount standby database;

Database altered.

SQL>
SQL> show parameter dg_broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/12.1.0
                                                 /dbhome_1/dbs/dr1STBY.dat
dg_broker_config_file2               string      /u01/app/oracle/product/12.1.0
                                                 /dbhome_1/dbs/dr2STBY.dat
dg_broker_start                      boolean     FALSE
SQL>
SQL> alter system set dg_broker_Start=TRUE;

System altered.

SQL>


On the first instance of the Primary :

SQL> show parameter dg_broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/12.1.0
                                                 /dbhome_1/dbs/dr1RAC.dat
dg_broker_config_file2               string      /u01/app/oracle/product/12.1.0
                                                 /dbhome_1/dbs/dr2RAC.dat
dg_broker_start                      boolean     FALSE
SQL> alter system set dg_broker_config_file1='+DATA/RAC/dgbroker1.dat' sid='*' scope=BOTH;

System altered.

SQL> alter system set dg_broker_config_file2='+FRA/RAC/dgbroker2.dat' sid='*' scope=BOTH;

System altered.

SQL> alter system set dg_broker_start=TRUE;

System altered.

SQL>

[oracle@collabn1 ~]$ tnsping RAC1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 27-MAR-2017 22:37:41

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RAC.racattack)))
OK (0 msec)
[oracle@collabn1 ~]$ sqlplus sys/racattack@STBY

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 27 22:37:51 2017

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

ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0


Enter user-name: sys/racattack@STBY as SYSDBA

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@collabn1 ~]$ tnsping RAC1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 27-MAR-2017 22:38:07

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RAC.racattack)))
OK (0 msec)
[oracle@collabn1 ~]$ tnsping STBY

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 27-MAR-2017 22:38:10

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oem132)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STBY)))
OK (0 msec)
[oracle@collabn1 ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/racattack@RAC1
Connected as SYSDBA.
DGMGRL> create configuration RAC as primary database is RAC connect identifier is RAC1;
Configuration "rac" created with primary database "rac"
DGMGRL> add database stby as connect identifier is stby maintained as physical;
Database "stby" added
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - rac

  Protection Mode: MaxPerformance
  Members:
  rac  - Primary database
    stby - Physical standby database
      Error: ORA-16664: unable to receive the result from a database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 7 seconds ago)

DGMGRL>


Errors in the STBY DataGuard Broker Logfile drcSTBY.log :

03/27/2017 22:45:12
Warning: Property 'StandbyFileManagement' has inconsistent values:METADATA='MANUAL', SPFILE='', DATABASE='MANUAL'
Warning: Property 'ArchiveLagTarget' has inconsistent values:METADATA='0', SPFILE='', DATABASE='0'
Warning: Property 'LogArchiveMaxProcesses' has inconsistent values:METADATA='4', SPFILE='', DATABASE='4'
Warning: Property 'LogArchiveMinSucceedDest' has inconsistent values:METADATA='1', SPFILE='', DATABASE='1'
SPFILE is missing value for property 'LogArchiveTrace' with sid='STBY'
Warning: Property 'LogArchiveTrace' has inconsistent values:METADATA='0', SPFILE='(missing)', DATABASE='0'
SPFILE is missing value for property 'LogArchiveFormat' with sid='STBY'
Warning: Property 'LogArchiveFormat' has inconsistent values:METADATA='%t_%s_%r.dbf', SPFILE='(missing)', DATABASE='%t_%s_%r.dbf'
Failed to send message to site rac. Error code is ORA-16501.

Errors in the RAC1 DataGuard Broker Logfile drcRAC1.log :

Site stby returned ORA-16664.
Data Guard Broker Status Summary:
  Type                        Name                             Severity  Status
  Configuration               rac                               Warning  ORA-16607
  Primary Database            rac                               Success  ORA-00000
  Physical Standby Database   stby                                Error  ORA-16664


Actions taken on the Standby :

SQL> show parameter standby

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      MANUAL
SQL> alter system set standby_file_management='AUTO' scope=BOTH;

System altered.

SQL> alter system set archive_lag_target=0 scope=BOTH;

System altered.

SQL> alte system set log_archive_max_processes=4 scope=BOTH;
SP2-0734: unknown command beginning "alte syste..." - rest of line ignored.
SQL> alter system set log_archive_max_processes=4 scope=BOTH;

System altered.

SQL> alter system set log_archive_min_succeed_dest=1 scope=BOTH;

System altered.

SQL> alter system set log_archive_trace=0 scope=BOTH;

System altered.

SQL> alter system set log_archive_format='%t_%s_%r.dbf' scope=BOTH;
alter system set log_archive_format='%t_%s_%r.dbf' scope=BOTH
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL>


I also had to edit the TNSNAMES.ORA on the STBY server for the correct *VIP* entry (not the physical hostname) for the RAC1 instance.

RAC1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.78.61)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RAC.racattack)
    )
  )

RAC1_DGB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.78.61)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = RAC_DGMGRL)
    )
  )


Thereafter, it worked.  Note : You sometimes have to wait a while to enter the SHOW CONFIGURATION, the first result may show an error.  So I entered it twice.

DGMGRL> disable configuration;
Disabled.
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - rac

  Protection Mode: MaxPerformance
  Members:
  rac  - Primary database
    stby - Physical standby database
      Error: ORA-16664: unable to receive the result from a database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 125 seconds ago)

DGMGRL> show configuration;

Configuration - rac

  Protection Mode: MaxPerformance
  Members:
  rac  - Primary database
    stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 14 seconds ago)

DGMGRL>


These are the drcSTBY.log entries when the TNSNAMES.ORA for RAC1 was incorrect on the Standby Server :

Failed to connect to remote database rac. Error is ORA-12541
Failed to send message to site rac. Error code is ORA-12541.


This is the drcSTBY.log entry on success :

03/27/2017 23:00:51
Apply Instance for Database stby set to STBY


And the entry in drcRAC1.log on success :

03/27/2017 23:00:10
DISABLE CONFIGURATION
03/27/2017 23:00:46
ENABLE CONFIGURATION
03/27/2017 23:00:57
EDIT DATABASE stby SET PROPERTY ActualApplyInstance = STBY
03/27/2017 23:01:00
Apply Instance for database stby is STBY


Setting Maximum Performance mode (command issued on the *Primary*) :

SQL> alter database set standby to maximize performance;

Database altered.

SQL>

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxPerformance;
Succeeded.
DGMGRL> show configuration;

Configuration - rac

  Protection Mode: MaxPerformance
  Members:
  rac  - Primary database
    stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 34 seconds ago)

DGMGRL>
DGMGRL> show configuration verbose

Configuration - rac

  Protection Mode: MaxPerformance
  Members:
  rac  - Primary database
    stby - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>


Will be testing Redo Transmission and Apply later.
.
.
.

No comments: