ORA-16664: unable to receive the result from a database

Onur ARDAHANLI March 23, 2020 ORACLE

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

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)

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:

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

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.

[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

[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

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.

[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)

[oracle@physclFS ~]$ tnsping logical2 5

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 18-APR-2017 08:35:19

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)

Step3:

Check Listener on all DBs in configurations. We have seen that there is something odd only in the Primary database “Physical”.

[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

[oracle@physical ~]$ lsnrctl

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-APR-2017 08:36:31

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.

[Physical - 18-04-2017 08:49:54] SQL> select logins from v$instance;

LOGINS
----------
RESTRICTED

[Physical - 18-04-2017 08:49:54] SQL> select logins from v$instance;

LOGINS

----------

RESTRICTED

Step5:

Disable RESTRICT mode.

[Physical - 18-04-2017 08:43:34] SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

System altered.

[Physical - 18-04-2017 08:43:34] SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

System altered.

Step6:

Check listener again.

[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

[oracle@physical ~]$ lsnrctl

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-APR-2017 08:45:44

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.

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)

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)

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

ORA-16664: unable to receive the result from a database

SOLUTION

Step1:

Step2:

Step3:

Step4:

Step5:

Step6:

Step7:

About Onur ARDAHANLI

Leave a Reply Cancel reply