Fatal NI connect error 12170.

From Mos Doc ID 1286376.1

Fatal NI Connect Error 12170, 'TNS-12535: TNS:operation timed out' Reported in 11g Alert Log   

In this Document
Symptoms
Changes
Cause
Solution
References
APPLIES TO:

Oracle Net Services - Version 11.1.0.6 to 12.1.0.2 [Release 11.1 to 12.1]
Information in this document applies to any platform.
TNS-12170, ORA-12170, TNS-12535, TNS-00505 alert.log


SYMPTOMS


nt secondary err code: 110   Monitoring of the 11g database Alert log(s) may show frequent timeout related messages such as:

- On Oracle Solaris:

***********************************************************************

Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for Solaris: Version 11.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production
Time: 22-JAN-2011 21:48:23
Tracing not turned on.
Tns error struct:
ns main err code: 12535

TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505

TNS-00505: Operation timed out
nt secondary err code: 145
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.xxx.yy.117)(PORT=1092))

---------
The "nt secondary err code" will be different based on the operating system.

Linux x86 or Linux x86-64: "nt secondary err code: 110"
HP-UX Server: "nt secondary err code: 238"
AIX: "nt secondary err code: 78"




CHANGES

No changes are necessary, but may have recently upgraded the database to 11g release 1 or higher, or installed a new Oracle11g database.

Note: Prior to 11gR1 these same 'Fatal NI connect error 12170' are written to the sqlnet.log

CAUSE

These time out related messages are mostly informational in nature.  The messages indicate the specified client connection (identified by the 'Client address:' details) has experienced a time out.  The 'nt secondary err code' identifies the underlying network transport, such as (TCP/IP) timeout limits after a client has abnormally terminated the database connection.

The 'nt secondary err code' translates to underlying network transport timeouts for the following Operating Systems:

For the Solaris system: nt secondary err code: 145:
ETIMEDOUT 145 /* Connection timed out */

For the Linux operating system: nt secondary err code: 110
ETIMEDOUT 110 Connection timed out

For the HP-UX system: nt secondary err code: 238:
ETIMEDOUT 238 /* Connection timed out */

For AIX: nt secondary err code: 78:
ETIMEDOUT 78 /* Connection timed out */


For Windows based platforms: nt secondary err code: 60 (which translates to Winsock Error: 10060)

Description:  A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

The reason the messages are written to the alert log is related to the use of the new 11g Automatic Diagnostic Repository (ADR) feature being enabled by default.  See (Doc ID 454927.1).

SOLUTION

Suggested Actions:

- Search the corresponding text version of the listener log located on the database server for the corresponding client connection referenced by the Client address details referenced in the alert log message.

For the message incident below you would search the listener log for the 'Client address' string: 

(ADDRESS=(PROTOCOL=tcp)(HOST=10.xxx.yy.117)(PORT=1092))

The search of the listener log should find the most recent connection before the time reference displayed in the alert log message, e.g. '22-JAN-2011 21:48:23'.

-Corresponding listener log entry:

22-JAN-2011 21:20:12 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=AMN11264.us.oracle.com)(CID=(PROGRAM=D:\app\mcassady\product\11.2.0\dbhome_1\bin\sqlplus.exe)(HOST=mcassady-lap)(USER=mca
ssady))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.xxx.yy.117)(PORT=1092)) * establish * AMN11264.us.oracle.com * 0

- Alert log entry:
------------

Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for Solaris: Version 11.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production
TCP/IP NT Protocol Adapter for Solaris: Version 11.2.0.1.0 - Production
Time: 22-JAN-2011 21:48:23
Tracing not turned on.
Tns error struct:
ns main err code: 12535

TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505

TNS-00505: Operation timed out
nt secondary err code: 145
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.xxx.yy.117)(PORT=1092))

------------

Note the time of the client corresponding client connection(s) in the listener log.  Here you may find a particular client, set of clients or particular applications that are improperly disconnecting causing the timeout errors to be raised and recorder in the database alert log.

 

See the following for more information and a potential solution where a firewall may be causing this issue:   Note:1628949.1   Alert Log Errors: 12170 TNS-12535/TNS-00505: Operation Timed Out



You may choose to revert from the new Automatic Diagnostic Repository (ADR) method to prevent the Oracle Net diagnostic details from being written to the alert log(s) by setting the following Oracle Net configuration parameters:


To revert to Oracle Net Server tracing/logging, set following parameter in the server's sqlnet.ora :

DIAG_ADR_ENABLED = OFF

Also, to back out the ADR diag for the Listener component, set following parameter in the server's listener.ora:

DIAG_ADR_ENABLED_<listenername> = OFF

   - Where the <listenername> would be replaced with the actual name of the configured listener(s) in the listener.ora configuration file.  For example, if the listener name is 'LISTENER', the parameter would read:

DIAG_ADR_ENABLED_LISTENER = OFF

-Reload or restart the TNS Listener for the parameter change to take effect.

ORA-38760: This database instance failed to turn on flashback database

---

Cause

It is caused by the Flashback logs required are not available, as for example FLB logs was deleted using OS commands. It applied to standby databases as well as RAC. One of the RAC Instances or the single database instance does not come up, its looking for a Flash back log which has been accidently deleted.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

Action

1. Turn off / on Flashback 

SQL> alter database flashback off;
SQL> alter database flashback on;

If it's success, then you can open it.

2. In cases where "Guaranteed Restore Point" is defined, RVWR will still try to write to flashback log even though you have disable it and it will fail again with same error

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
RESTORE POINT ONLY

Step 1

SQL> select * from v$restore_point;
select * from v$restore_point
*
ERROR at line 1:
ORA-38701:

Here we are not able to query v$restore _point to find out the name.

Step 2

Search for restore point name in alert log. If you can find it, then use it.

Step 3

But, in our case, we could not find the name in alert log. So, We dump the controlfile to get the restore point name:

SQL> oradebug setmypid
SQL> alter session set events 'immediate trace name controlf level 9';
SQL> oradebug tracefile_name
'blabla .trc'

Open this 'blabla. trc' file and navigate to 'RESTORE POINT RECORDS'

***************************************************************************
RESTORE POINT RECORDS
***************************************************************************
(size = 212, compat size = 212, section max = 2048, section in-use = 1,
last-recid= 1, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 240, numrecs = 2048)
RESTORE POINT #1:
restore point name: PRE_UPGRADE guarantee flag: 1 incarnation: 2next record
0 <<<< Name of restore point
restore point scn: 0x0000.fbff3d87 12/07/2015 14:56:23

Now we have name of Guaranteed Restore Point, After you find the restore point in Step 1, Step 2 or Step 3, use it to delete restore point and open database:

SQL> Drop restore point PRE_UPGRADE;
SQL> alter database open;

ORA-27102: out of memory O/S-Error: (OS 1455)

ORA-27102: out of memory
 *Cause: Out of memory
 *Action: Consult the trace file for details

Solution is simple :

Increase the swap or decrease the memory parameters you gave.

To decrease memory parameters, follow these steps :

SQL -> Connected to idle instance.

SQL-> create pfile from spfile;

 (change parameter  in pfile)

SQL-> create spfile from pfile;

SQL-> startup;

ORA-16857: standby disconnected from redo source for longer than specified threshold

Solution ID

ORA-16857

Type

Oracle

Arguments

standby disconnected from redo source for longer than specified threshold

---

Cause

The amount of time the standby was disconnected from the redo source database exceeded the value specified by the 'TransportDisconnectedThreshold' database property. It is caused by no network connectivity between the redo source and the standby databases.

Action

Check for gaps on the standby database. If no gap is present, tune the apply services..

 My suggestion is that :

Try setting TransportDisconnectedThreshold higher than the default of 30.

DGMGRL> EDIT DATABASE testfo SET PROPERTY  TransportDisconnectedThreshold='120';

 or if you want to disable alert :

DGMGRL> EDIT DATABASE testfo SET PROPERTY  TransportDisconnectedThreshold='0';

after this you may need to execute this command :

DGMGRL> enable database standbyDB;

ORA -00257 Archiver Error

ORA-00257: archiver error. Connect internal only, until freed.

Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.

Action: Check the archiver trace file for a detailed description of the problem. Also, verify that the device specified in the initialization parameter archive_log_dest is set up properly for archiving.

you may encounter  with ORA-00257 while connecting to the database.
In this case you have probably suffered  a resource problem in the flash recovery area (FRA) or db_recovery_file_dest_size or in the system.  

Firstly, check if there is enough space in disk.

After then; control archive settings and check the system resources with these related settings :

SQL> archive log list;
SQL> show parameter db_recovery_file_dest;
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

After correcting system resources related with these settings you should switch log file:

SQL> alter system switch logfile;

and problem solves..

ORA-00132: syntax error or unresolved network name "string"

Cause: Listener address has syntax error or cannot be resolved.

Action: If a network name is specified, check that it corresponds to an entry in TNSNAMES.ORA or other address repository as configured for your system. Make sure that the entry is syntactically correct.

Try using TNSPING from the command line and check the syntax in TNSNAMES.ORA.


I create pfile from spfile. The entry for LOCAL_LISTENER was modified to have network address something like *.local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))' The spfile was created from this modified pfile. The database was then successfully mounted and opened.

ORA-12537: TNS: connection closed error

The ORA-12537 is an information message and means that the connection has been closed. This can be caused by a number of reasons:
1. Database is shut down (maybe for nightly backup), but connection to database was kept by client.
2. A time-out occurred on the client-connection.
3. When firewall closes idle connections.
3. A configuration problem in sqlnet.ora or listener.ora.
4. You have a path name that is too long for the Oracle TNS client on windows.



This issue is due to the connection string being large enough that the connection packet is being split into two packets and then sent to the listener. There are several workarounds for the issue.

a. Install the exe in a shorter named directory.
b. Modify the TNS Names entry so that it is shorter.
c. Replace domain names with IP Addresses if that shortens and vice versa.
d. Remove any parameters from the entry that are not needed.
e. Patch the Database Listener to the most recent version.
5. Oracle security is enables (for example in Oracle Apps). Solution either:
a) Edit sqlnet.ora file and add your client-ip to tcp.invited_nodes.
b) Remove secutity by editing sqlnet.ora and comment out parameter tcp.validnode_checking = yes by adding # at the beginning of the line.

ORA-01950 No Privileges On Tablespace USERS

Data cannot be inserted if  there is no quota on tablespace.

Solution 1:

SQL-> Alter user <user> quota unlimited on <tablespace_name>;

Solution 2

SQL-> Alter user <user> quota 100M on  <tablespace_name>;

OGG-01223 TCP/IP error 10061 (No connection could be made beacause the target machine actively refused it)

OGG-01223 TCP/IP error 10061 (No connection could be made because the target machine actively refused it)

Solution is too simple  :

  Are you sure that target and source manager processes are started?

GGSCI-> info manager

GGSCI-> start manager

ORA-65005: missing or invalid file name pattern for file ORA-01276: Cannot add file

This situation is valid for Oracle 12c Multitenant architecture, Pluggable databases and OMF. Please read the blog item in detail to understand the cause.



We prepared a template database :

    SQL> select file_name from dba_data_files;
    FILE_NAME
    +DATA/PDBTemplate/system.273.829388755
    +DATA/PDBTemplate/sysaux.272.829388715
    +DATA/PDBTemplate/users.268.829388341
    +DATA/PDBTemplate/apex_space.271.829388439
    +DATA/PDBTemplate/edge_010_data.266.829388045

So we wanted to allow the developers to clone the database to test changes. But we encountered a problem :

    SQL> create pluggable database Developer from PDBTemplate file_name_convert = (Developer, PDBTemplate);
    create pluggable database
    *
    ERROR at line 1:
    ORA-01276: Cannot add file +DATA/PDBTemplate/system.273.829388755.

File has an Oracle Managed Files file name. Therefore it looks like the OMF file naming causes us some problems. We corrected the problem with this :

    SQL> create pluggable database Developer from PDBTemplate file_name_convert =
    (
    ・DATA/PDBTemplate/system.273.829388755',・DATA/Developer/system01.dbf・
    ・DATA/PDBTemplate/users.268.829388341',・DATA/Developer/users01.dbf・
    ・DATA/PDBTemplate/sysaux.272.829388715',・DATA/Developer/sysaux01.dbf・
    ・DATA/PDBTemplate/edge_010_data.266.829388045',・DATA/Developer/edge_01.dbf・
    ・DATA/PDBTemplate/apex_space.271.829388439',・DATA/Developer/apex_space01.dbf・
    ); 2 3 4 5 6 7 8
    create pluggable database olakits from ola file_name_convert =
    *
    ERROR at line 1:
    ORA-65005: missing or invalid file name pattern for file -
    /u00/oracle/oradata/PCDB/OLA/temp01.dbf

But It seems that I have forgotten to add temp file

    SQL> create pluggable database olakits from ola file_name_convert =
    (
    ・DATA/PDBTemplate/system.273.829388755',・DATA/Developer/system01.dbf・
    ・DATA/PDBTemplate/users.268.829388341',・DATA/Developer/users01.dbf・
    ・DATA/PDBTemplate/sysaux.272.829388715',・DATA/Developer/sysaux01.dbf・
    ・DATA/PDBTemplate/edge_010_data.266.829388045',・DATA/Developer/edge_01.dbf・
    ・u00/oracle/oradata/PDBTemplate/temp01.dbf・ ・DATA/Developer/temp01.dbf・
    );

Pluggable database created.

It seems everything is OK

OGG-01950 No Privileges on tablespace ...

OGG-01950 No Privileges on tablespace tablespace_xxx.

Cause: User does not have privileges to allocate an extent in the specified tablespace.

Action: Grant the user the appropriate system privileges or grant the user space resource on the tablespace.

In our case :

alter user aliyagmur quota unlimited on tablespace_xxx;

OGG 02022 Logmining server does not exist on this Oracle database.

 

While Replicating Oracle 12c Pluggable Databases with GoldenGate, you may encounter with this error : OGG-02022 Logmining server does not exist on this Oracle database. This shows that extract needs to be registered for each PDB that the extract will work against. Using GGSCI use the dblogin command to login

 dblogin userid ggs_admin, password oracle

  and then run

 register extract ext_esk database container (dev1, dev2)

DGM-17016: failed to retrieve status for database ORA-16664: unable to receive the result from a database

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

Cause: During execution of a command, a database in the Data Guard broker configuration failed to return a result.

Action: Check Data Guard broker logs for the details of the failure. Fix any possible network problems and try the command again.

DGM-17016: failed to retrieve status for database 

Opening standby database in read only mode causes to error affecting data guard broker configuration. You should open database in "mount" state. If operating system is Windows, then you should edit registry editor for that database is not automatically started.


In Registry editor:

ORA_DBUNIQUENAME_AUTOSTART should be false in "HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_ORACLEHOMENAME".

After  this, you should start database in mount state. I give an example script for that:

   @ECHO off

   set dbuser=sys

   set dbpass=oracle

   set dbtns=orcl

   set primaryDB=PRIMARY

   set physicalSB=PHYSICAL STANDBY

   set telaysServiceName=telays

   reg add "HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb11g_home1" /v "ORA_%dbtns%_AUTOSTART" /t REG_SZ /d "FALSE" /f

   set oracle_sid=%dbtns%

  
   lsnrctl start listener

   rem waits 10 seconds

   ping 199.987.4.4 -n 1 -w 10000 > nul

  
   net start oracleservice%dbtns%

   rem waits 1 second

   ping 199.987.4.4 -n 1 -w 1000 > nul
  
  
   echo  startup mount;> c:\startmount.sql

   echo exit; >> c:\startmount.sql

   sqlplus -s "%dbuser%/%dbpass%@%dbtns%" as sysdba @c:\startmount.sql

  
   echo set pagesize 0; > c:\temp.sql

   echo spool c:\temp.txt; >> c:\temp.sql

   echo select database_role from v$database; >> c:\temp.sql

   echo spool off; >> c:\temp.sql

   echo exit; >> c:\temp.sql

   sqlplus -s "%dbuser%/%dbpass%@%dbtns%" as sysdba @c:\temp.sql

  
   FOR /F "tokens=* delims=" %%a IN (c:\temp.txt) do set src=%%a

   setlocal enabledelayedexpansion

   for /f "tokens=* delims= " %%a in ("%src%") do set src=%%a

   for /l %%a in (1,1,100) do if "!src:~-1!"==" " set src=!src:~0,-1!

   echo."%src%"

  
           if "%src%" == "%primaryDB%" (
               rem start the database
               echo  alter database open;> c:\start.sql

           echo exit; >> c:\start.sql
               sqlplus -s "%dbuser%/%dbpass%@%dbtns%" as sysdba @c:\start.sql
               rem start application server
               net start %telaysServiceName%
               eventcreate /ID 1 /L APPLICATION /T INFORMATION /SO "TELAYS WINDOWS ACILISI" /D "Veritabani ve application server veritabani rolune gore acildi"
           ) else if "%src%" == "%physicalSB%" (
               rem start replication
           echo alter database recover managed standby database disconnect from session; >> c:\start.sql
               echo exit; >> c:\start.sql
           sqlplus -s "%dbuser%/%dbpass%@%dbtns%" as sysdba @c:\start.sql
               eventcreate /ID 1 /L APPLICATION /T INFORMATION /SO "TELAYS WINDOWS ACILISI" /D "Veritabani veritabani rolune gore acildi"
       ) else (
               eventcreate /ID 1 /L APPLICATION /T ERROR /SO "TELAYS WINDOWS ACILISI" /D "Veritabani rolu yanlis"
       )
   del c:\temp.txt
   del c:\temp.sql
   del c:\start.sql
   del c:\startmount.sql
  
   endlocal & exit

Recovering undo tablespace from corrupted undo Datafile

  alter system set undo_management=manual scope=spfile;
  shu immediate;
  startup mount;
  alter database datafile 'C:\APP\ADMINISTRATOR\ORADATA\TSTCLONE\UNDOTBS01.DBF' offline drop;
  alter databae open;
  drop tablespace undotbs1;
  create undo tablespace undotbs2 datafile 'C:\app\Administrator\oradata\ANKARA\UNDOTBS01.DBF'  size 100M   REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE  2000M;
  alter system set undo_management=auto scope=spfile;
  alter system set undo_retention = 900 scope = both;
  shu immediate;
  startup;

ORA-00280: change string for thread string is in sequence #string

ORA-00280: change string for thread string is in sequence #string

Cause: This message helps to locate the redo log file with the specified change number requested by other messages.

Action: Use the information provided in this message to specify the required archived redo log files for other errors.

Çözüm :

SQL> startup mount
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

  CANCEL

SQL> ALTER DATABASE open resetlogs

ORA-16532: Data Guard broker configuration does not exist

Cause: A broker operation was requested that required a broker configuration to already be created.

Action: Create a Data Guard broker configuration prior to performing other broker operations.

 Dataguard broker yaratırken standby database'de oluşan hatadır. Çözümü oldukça basit :

  SQL> show parameter dg_

  NAME                                 TYPE        VALUE
  ------------------------------------ ----------- ------------------------------
  dg_broker_config_file1               string
  dg_broker_config_file2               string
  dg_broker_start                      boolean     TRUE

  SQL> alter system set dg_broker_start=false;

  System altered.

  SQL> alter system set dg_broker_config_file1='C:\app\oracle\product\11.2.0\dbhome_1\database\DRESK01.dat';

  System altered.

  SQL> alter system set dg_broker_config_file2='C:\app\oracle\product\11.2.0\dbhome_1\database\DRESK02.dat';

  System altered.

  SQL> alter system set dg_broker_start=true;

  System altered.

Bundan sonra DGMGRL komut satırından tekrar konfigürasyon enable edilir.

  DGMGRL-> enable configuration;

ORA-16829 fast-start failover configuration is lagging

Cause: The fast-start failover target standby database was not within the lag limit specified by the FastStartFailoverLagLimit configuration property. As a result, a fast-start failover could not happen in the event of a primary database failure.

Action: Ensure that the fast-start failover target standby database is running and applying redo data and that the primary database is successfully trasmitting redo data. If this condition persists consider raising the value of the FastStartFailoverLagLimit configuration property.

  
 Lag Limit ile ilgili bir sorun olduğunu göstermekte, yapılacak işlem, diğer hususlarda bir sorun görümüyorsa, LagLimiti biraz artırmak olacaktır :

DGMGRL -> edit configuration set property FastStartFailoverLagLimit =45;