ORA-12170: TNS:Connect timeout occurred

When I tried to connect to my database via SQLPLUS, I was getting the error "ORA-12170: TNS:Connect timeout occurred".  I understood that the error could be due to firewall issue or database down or listener down or bad sqlnet.ora parm or even because of network issue. I did check on everything and found the firewall block caused the issue in my Windows server 2008 R2. Now I have to turn off the firewall that blocks the database port.

How to Turn off Firewall for DB Port:

Navigate to Control Panel --> All Control Panel Items --> Windows Firewall, click on Turn Windows Firewall on or off option and check the status of Firewall. If it is On, turn it Off and try connecting to database.

If it works, then the problem is due to firewall blockage. Now turn it On and go back to Windows Firewall page. Click on Advance settings option; create a new rule as shown in the snapshot below.

Follow the below screenshots to know the options to be followed while creating the rule to unblock DB Port. 

Specify the DB port you have given while setting up the DB.

Whatever name and description give here will be reflected in the Inbound Rules page.

 

 

Now try connecting to the database. It worked for me and I hope this will help you as well. If you have any doubts, please feel free to comment on it.

If you find the error is because of slow network or system, then reconfigure one or all of the parameters SQLNET.INBOUND_CONNECT_TIMEOUT,SQLNET.SEND_TIMEOUT,  SQLNET.RECV_TIMEOUT in sqlnet.ora to larger values. If a malicious client is suspected, use the address in sqlnet.log to identify the source and restrict access.  Also verify that your hosts file has a DNS entry. Eg: 102.54.94.97    

In Windows, the hosts file is located at win/system32/driver/etc/ and on UNIX, Linux, it is located in /etc/hosts. 

Query to find Locked Tables in Oracle

Query to find Locked Objects:
 

select a.object_name, b.oracle_username, b.os_user_name,c.sid, c.serial# ,c.terminal, d.sql_text

from sys.dba_objects a, v$locked_object b, v$session c, v$sqltext d      

where a.object_id = b.object_id

and c.sid = b.session_id

and c.sql_hash_value = d.hash_value

(OR)

select a.object_id, b.owner, b.object_name, a.session_id, a.oracle_username, a.process
from v$locked_object a, dba_objects b
where a.object_id = b.object_id;

Query to find m/c & user name having lock:

select sid, username, osuser, process, machine, program
from v$session
where sid = sid_of_hanging_session;

Query to kill Session:

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

Query to find PID of session:

select pid from v$process where addr=
(select paddr from v$session where sid=sid_of_hanging_session);

Whenever there is a lock in the object, if you issue a DML command on the same, it will thrown the below error.

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.

 

ORA-1652: Unable to extend temp segment-RAC

Oracle Error:

ORA-01652: unable to extend temp segment by string in tablespace string 

Eg: ORA-1652: unable to extend temp segment by 128 in tablespace TEMP 

Cause:

In RAC environment, 2 reasons could cause this error.

Case-1:  No space available in the temp table space of which is being used.

Case-2: The second cause may have to do with the local temp segment not being able to extent space even though there is space in other instances.   

Action Plan:

In general, Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated. Normally, you would just add disk to TEMP to avoid the ORA-1652 error, but you can also wait for SMON to clean-up the TEMP segment.

CASE 1:

Find the free space in temp tablespace:

SQL> SELECT tablespace_name, file_name, bytes/1024/1024 size_in_mb from dba_temp_files;

SQL> select sum(free_blocks) from gv$sort_segment where tablespace_name = 'TEMP TABLESPACE NAME';

If the free block reads results '0', it signifies that there is no free space.

Work-around:

Add a new temp file to tablespace or resize the existing temp file.

To add a new file,

SQL> ALTER TABLESPACE tablespace_name ADD DATAFILE|TEMPFILE 'full path and file name' SIZE integer;      

To resize the existing temp file,

SQL> ALTER DATABASE DATAFILE|TEMPFILE ‘/u03/oradata/ temp02. dbf’ RESIZE 200M;

CASE 2:

In RAC, more sort segment space can be used from other instances, which can help resolve ORA-01652 more easily. 

SQL> select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment;

Where total_blocks & used_blocks ---> total temp segment space used &      

free_blocks ---> amount of space allocated to this particular instance.

If used_blocks = total_blocks and free_blocks = 0, it results in ORA-01652 error.

If ORA-01652 is causing the slowdown, SMON will probably not be able to process the sort segment requests. Try one of the following work-around:

1) Increase size of the temp tablespace

SQL> ALTER DATABASE DATAFILE|TEMPFILE ‘/u03/oradata/ userdata02. dbf’ RESIZE 200M;

2) Increase sort_area_size and/or pga_aggregate_target

SQL> alter system set sort_area_size = 32765 scope=both;

SQL> alter session set sort_area_size = 32765 (to modify in the current session itself)

SORT_AREA_SIZE specifies (in bytes) the maximum amount of memory Oracle will use for a sort. After the sort is complete, but before the rows are returned, Oracle releases all of the memory allocated for the sort, except the amount specified by the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned, Oracle releases the remainder of the memory.

SQL> select inst_id, file_id, extents_cached, extents_used from gv$temp_extent_pool order by 1,2;

In RAC, extents are allocated from all temporary files uniformly. There are also changes to file header block during this operation. This is one of the reason, to create many temporary files in RAC. If you have 24 nodes in your RAC cluster, yes, that would imply that you would have to create 24 temp files to the TEMP table space.