ORA-03113: end-of-file on communication channel error. (Oracle Database)

ORA-03113:end-of-file on communication channel error.
Problem: An ORA-03113 end-of-file on communication channel error is a very generic error that indicates that the connection has been lost. ORA-03113 is reported by the oracle client processes due to many reasons. ORA-03113 by itself does not indicate the cause of the issue and is accompanied by other messages that are either displayed on the client side or in the oracle database alertlog.


ORA-03113 issues are primarily caused by :
Network Issues or Problems
Forceful Disconnection of a Server Session
Oracle Database Crash
Oracle Server Crash
Oracle internal errors like ORA-00600 or ORA-07445 causing aborts Oracle Client or TNS layer inability to handle the connections
and so on...


An example of this error is shown below. In this example, the ORA-3113 was generated by unavailability of Oracle as shown in ORA-01034 which indicates that either the oracle database session has been killed or the oracle database instance is down or has just crashed. 


ORA-03113: end-of-file on communication channel
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist


You as the dba or the user should be aware that this error can be very misleading and is also a catch-all error in the oracle software. Similar errors include the ORA-00600 and you should realize that oracle can just report this error sometimes when ever it has an internal issue. This error requires analysis as a whole based on the accompanying error messages. 


Impact: The connection between Client and Server process is broken and the database is not available to the users. 


Solution:There was a communication error that requires further investigation.First, check for network problems and review the SQL*Net setup. Also,look in the alert.log file for any errors. Finally, test to see whether the server process is dead and whether a trace file was generated at failure time. 

ORA-12514 Listener Connection Problems (Oracle Database)

ORA-12514:TNS:listener does not currently know of service requested in the connect descriptor.


Problem: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.


Impact: The client request trying to connect to a database is denied and the user is not able to connect to the database.


Solution: Wait a moment and try to connect a second time.
- Check which services are currently known by the listener by executing: lsnrctl services <listener name>.


- Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.


- If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.


- Check for an event in the listener.log file.

Oracle Database- System Tablespace Space Problems.

Problem: System Tablespace Space Problems. The System tablespace is one of the most important factors in the smooth functioning of the database. If it gets full with unnecessary data which is not required in the system tablespace, it severely affects the performance of the database. 


Impact: Degrades overll performance of the database.


Solution: The Audit table AUD$ is stored in the system tablespace by default if the AUDIT_TRAIL parameter is set to DB. It consumes a lot of space in the system tablespace. Since in the system tablespace only tables and indexes from sys and system should be there, all the other data that is unnecessarily occupying space in the system tablespace should be moved to another tablespace. In this case the AUD$ table should be moved to another tablespace to free up space in the system tablespace.

Pessimistic Locking Problem

Problem: Pessimistic Locking Problems in Oracle.
Pessimistic locking is the technique by which the data to be updated is locked in advance.  This is achieved using the familiar  SELECT…FOR UPDATE syntax. Once the data to be updated has been locked, the application can make the required changes, and then commit or rollback - during which the lock is automatically dropped. If anyone else attempts to acquire a lock of the same data during this process, they will be forced to wait until the first transaction has completed. This approach is called pessimistic because it assumes that another transaction might change the data between the read and the update.  In order to prevent that change from happening - and the data inconsistency that would result - the read statement locks the data to prevent any other transaction from changing it.


Looking very simple and safe, this approach has two major problems....
The Lockout - An application user selects a record for update, and then leaves for lunch without finishing or aborting the transaction.  All other users that need to update that record are forced to wait until the user returns and completes the transaction, or until the DBA kills the offending transaction and releases the lock.


The Deadlock - Users A and B are both updating the database at the same time.  User A locks a record and then attempt to acquire a lock held by user B - who is waiting to obtain a lock held by user A. Both transactions go into an infinite wait state - the so-called deadly embrace or deadlock.


Impact: If the lockout problem occurs, all the other users needing to update the locked records have to wait until the transaction completes or the DBA (Database Administrator) intervenes to kill a blocking transaction. 


If a Deadlock happens, it results in all the sessions being blocked. 


Solution: Using Optimistic locking with proper concurrency control can solve the problem of lockout due to pessimistic locking. Any command that begins with  UPDATE…SET that is not preceded by a SELECT…FOR UPDATE is an example of optimistic locking. The most common technique to lock a record for update without pessimistic locking is the use of a modification time-stamp. 


For the Deadlock problem, Oracle automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock. Typically, deadlocks are caused by poorly implemented locking in application code. 

ORA-04031 Shared Memory Problems In Oracle

Problem: ORA-04031- Shared Memory Problems. The shared pool is out of memory and the memory pool in the SGA are comprised of memory chunks in various sizes.When the database starts, a large chunk of memory is allocated in the various pools and tracked in free list hash buckets. Over time, as memory is allocated and deallocated, the memory chunks are moved around into different free list buckets inside the pool according to their size. 
An ORA-04031 error occurs in any of the memory pools in the SGA when Oracle cannot find a memory chunk large enough to satisfy an internal allocation request on behalf of a user's operation.


Impact: The shared memory problem can impact in a lot of ways. A request to perform an operation could result in an error if sufficient memory could not be allocated in the shared pool. The SGA is used by all the server and background processes and so it could potentially be a persistent problem for the user's.


Solution: If the shared pool is out of memory, either use the DBMS_SHARED_POOL package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE.


OR Use Automatic Memory Management by setting SGA_TARGET. There is no need to manually set the value of shared_pool_size in this case, Oracle will internally transfer memory between the SGA components.

ORA-00020 Error- Maximum number of processes exceeded

Problem: ORA-00020 Error- Maximum number of processes exceeded.


Impact: Since the maximum number of processes have been reached, Oracle will not process any new requests i.e any new application cannot not be started. This happens because Oracle would not be able to start any new processes to start the application and would give an error. Any new user would not be able to connect to the database. Any long running batch processes would hang in between. These problems can have serious consequences.


Solution: Shut down Oracle, increase the PROCESSES parameter in the initialization parameter file, and restart Oracle.


shut immediate;
startup mount;
alter system set processes=500 scope=spfile;
alter database open;
startup;


OR use the 'ps' command (ps -ef | grep ora) to identify some of the oldest server processes, and kill the 3-5 oldest processes which will free some resources (ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;) and then quickly try connecting to the database. Once you are connected to the database, you can look at things like V$SESSION to try and determine which user(s) is (are) consuming so many connections, and then go from there.

Tablespace reached it's max extents- Oracle Database

Problem: Tablespace reached it's max extents.
A maximum extents reached error occurs when the number of extents in various database objects exceeds the maximum extents that you have specified for the object. This includes errors such as ORA-1628 (Max # of extents reached for rollback segment), and ORA-1631 ORA-1654 (Max # of extents reached in Table or Index).


Impact: During large data loads or long running queries, which can run for hours and then if this error occurs then it makes the entire transaction to rollback, because the transaction could not complete due to space problems in the tablespace. It can create lots of problems if it happens with the temporary tablespace.


Solution: In this case, the object has a MAX_EXTENTS value assigned to it, which has been reached. The solution to this problem is to increase the MAX_EXTENTS setting for the object in question.


Increase the maximum number of extents allowed for the table, using alter table command:
alter table <table-name> storage (maxextents 500);
or alternatly remove the limit on the number of extents, i.e.:
alter table <table-name> storage (maxextents unlimited);


For Rollback segments setting the maxextents to unlimited is not recommended.