in Oracle

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