Continuing my series on setting up a non-RAC, FileSystem Standby for a RAC, ASM Database....
On the Standby:
On the first instance of the Primary :
Errors in the STBY DataGuard Broker Logfile drcSTBY.log :
Errors in the RAC1 DataGuard Broker Logfile drcRAC1.log :
Actions taken on the Standby :
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.
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.
These are the drcSTBY.log entries when the TNSNAMES.ORA for RAC1 was incorrect on the Standby Server :
This is the drcSTBY.log entry on success :
And the entry in drcRAC1.log on success :
Setting Maximum Performance mode (command issued on the *Primary*) :
Will be testing Redo Transmission and Apply later.
.
.
.
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.
.
.
.
