When querying the status of the configuration from broker, errors like below were seen.
Warning: ORA-16664: unable to receive the result from a database
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
DGMGRL> show configuration Configuration - DRSolution Protection Mode: MaxPerformance Members: physical - Primary database physclFS - Far sync instance Warning: ORA-16664: unable to receive the result from a database primary - Logical standby database Error: ORA-16664: unable to receive the result from a database snapshot - Physical standby database Error: ORA-16664: unable to receive the result from a database logical - Logical standby database Error: ORA-16664: unable to receive the result from a database logical2 - Physical standby database Error: ORA-16664: unable to receive the result from a database Members Not Receiving Redo: prmyFS - Far sync instance Warning: ORA-16664: unable to receive the result from a database Fast-Start Failover: DISABLED Configuration Status: ERROR (status updated 38 seconds ago) |
Standby databases also receive connection errors as below.
Fatal NI connect error 12526, connecting to:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Fatal NI connect error 12526, connecting to: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.20.42.62)(PORT=1521)(SEND_BUF_SIZE=10485760)(RECV_BUF_SIZE=10485760)))(SDU=65535)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=physical_DGB)(INSTANCE_NAME=physical)(CID=(PROGRAM=oracle)(HOST=physclFS.tivibulab.local)(USER=oracle)))) VERSION INFORMATION: TNS for Linux: Version 12.1.0.2.0 - Production TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production Time: 18-APR-2017 08:51:11 Tracing not turned on. Tns error struct: ns main err code: 12564 TNS-12564: TNS:connection refused ns secondary err code: 0 nt main err code: 0 nt secondary err code: 0 nt OS err code: 0 |
SOLUTION
We can fix the problem by following the steps below.
Step1:
Since the TNS problem appears, it is first checked whether the TNSNAMES.ora and Listener.ora files are working. Test access to Standby from Physical database with sqlplus. As you can see below, only one database connection is seen.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
[oracle@physical ~]$ sqlplus sys/Passw0rd4@logical2 as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 18 08:33:19 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. 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 Session altered. [Physical - 18-04-2017 08:33:19] SQL> show parameter name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cell_offloadgroup_name string db_file_name_convert string primary, logical2 db_name string LOGICAL2 db_unique_name string logical2 global_names boolean FALSE instance_name string logical2 lock_name_space string log_file_name_convert string primary, logical2 pdb_file_name_convert string processor_group_name string service_names string logical2 |
Step2:
Try tnsping. As you can see below, only one database connection is seen.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[oracle@physclFS ~]$ tnsping logical2 5 TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 18-APR-2017 08:35:19 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/12.1.0/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.42.42)(PORT = 1521)(SEND_BUF_SIZE = 10485760)(RECV_BUF_SIZE = 10485760))) (SDU = 65535) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = logical2))) OK (20 msec) OK (0 msec) OK (0 msec) OK (0 msec) OK (0 msec) |
Step3:
Check Listener on all DBs in configurations. We have seen that there is something odd only in the Primary database “Physical”.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
[oracle@physical ~]$ lsnrctl LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-APR-2017 08:36:31 Copyright (c) 1991, 2014, Oracle. All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> status Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=physical.tivibulab.local)(PORT=1521)(SEND_BUF_SIZE=10485760)(RECV_BUF_SIZE=10485760))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 03-MAR-2017 08:31:16 Uptime 45 days 23 hr. 5 min. 17 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/physical/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=physical.tivibulab.local)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=physical.tivibulab.local)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/physical/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "physical" has 2 instance(s). Instance "physical", status UNKNOWN, has 1 handler(s) for this service... Instance "physical", status RESTRICTED, has 1 handler(s) for this service... Service "physicalXDB" has 1 instance(s). Instance "physical", status RESTRICTED, has 1 handler(s) for this service... Service "physical_DGB" has 1 instance(s). Instance "physical", status RESTRICTED, has 1 handler(s) for this service... Service "physical_DGMGRL" has 1 instance(s). Instance "physical", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully |
Step4:
In this case, I suspect that the Primary database was opened in RESTRICT mode. We do the necessary checks.
|
1 2 3 4 5 |
[Physical - 18-04-2017 08:49:54] SQL> select logins from v$instance; LOGINS ---------- RESTRICTED |
Step5:
Disable RESTRICT mode.
|
1 2 3 |
[Physical - 18-04-2017 08:43:34] SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION; System altered. |
Step6:
Check listener again.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
[oracle@physical ~]$ lsnrctl LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-APR-2017 08:45:44 Copyright (c) 1991, 2014, Oracle. All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> status Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=physical.tivibulab.local)(PORT=1521)(SEND_BUF_SIZE=10485760)(RECV_BUF_SIZE=10485760))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date 03-MAR-2017 08:31:16 Uptime 45 days 23 hr. 14 min. 30 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/physical/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=physical.tivibulab.local)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=physical.tivibulab.local)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/physical/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "physical" has 2 instance(s). Instance "physical", status UNKNOWN, has 1 handler(s) for this service... Instance "physical", status READY, has 1 handler(s) for this service... Service "physicalXDB" has 1 instance(s). Instance "physical", status READY, has 1 handler(s) for this service... Service "physical_DGB" has 1 instance(s). Instance "physical", status READY, has 1 handler(s) for this service... Service "physical_DGMGRL" has 1 instance(s). Instance "physical", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully |
Step7:
Check the configuration status.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
DGMGRL> show configuration Configuration - DRSolution Protection Mode: MaxPerformance Members: physical - Primary database physclFS - Far sync instance primary - Logical standby database snapshot - Physical standby database logical - Logical standby database logical2 - Physical standby database Members Not Receiving Redo: prmyFS - Far sync instance Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 36 seconds ago) |
