Common ORA - errors - related to DBA

Common 'ORA-' errors found in PROD envs:

ORA-12154: TNS:could not resolve the connect identifier specified

Cause : This error is, almost appropriately, often the very first one you get on trying to establish a connection to your database. Simply put, it means that you have named the database you wish to be connected to and Oracle doesn’t know who the heck you’re talking about. This error is often caused by typos, maybe in your connection string, but possibly in your tnsnames.ora file. It is also possible that the tnsnames.ora file is not accessible or does not even exist. If it does, ensure that it contains the service name you are using; also go over it with a fine toothcomb to make sure it doesn’t have any unpaired parentheses or such.

Action : Cross Check tns entry, Check firwall access to connecting server

ORA-00600: internal error code, arguments: [%s], [%s],[%s], [%s], [%s]

Cause : This is a generic error; it means something serious has gone wrong and you are going to need to roll up your sleeves, dig in, and find out what. But you are not without clues. Your alert.log file will contain the path to your trace file. You will want to look in it, as a record is written to the trace file every time an ORA-00600 error occurs.
Take the information you get from your trace file and the first argument in the square brackets (the internal message number), and head over to My Oracle Support (Metalink). There you will find a handy ORA-0600 lookup tool (Note 153788.1) that will direct you to additional information that will help you solve your problem.

Action : Check with Oracle support as per your version and arguments

Note: There are lots of reason for ORA-00600, Based of arguments and oracle document you can fix it. So may bugs are described in oracle document for ORA-00600.

ORA-03113: end-of-file on communication channel

This error pretty much means that your connection has clutched its chest and died. For some reason, your client machine and the database server are acting like an old married couple and have stopped talking to each other. That reason could be one of a rather long list: has your server crashed? has someone pulled out your network cable? was your process killed at the O/S level? is your Windows log full? or maybe there is an Oracle internal error?

Do not overlook the obvious. This error is sometimes caused by the simplest of things. If, however, it is caused by an Oracle internal error, look to your alert log for further information.

ORA-01555 Snapshot Too Old

The ORA-01555 is caused by Oracle read consistency mechanism.  If you have a long running SQL that starts at 10:30 AM, Oracle ensures that all rows are as they appeared at 10:30 AM, even if the query runs until noon! Oracles does this by reading the "before image" of changed rows from the online undo segments.  If you have lots of updates, long running SQL and too small UNDO, the ORA-01555 error will appear.

From the docs we see that the ORA-01555 error relates to insufficient undo storage or a too small value for the undo_retention parameter:

ORA-01555: snapshot too old: rollback segment number string with name "string" too small

Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.

Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments.

Click here to read : how to set optimal value for undo retention and undo tablespace size

If you are facing following error for the below query, then do the my suggested action plan. I rectified the issue in same way.

ERROR at line ..:
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too
small ...

Query to execute to find issued tablespace:

select * from(
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
 , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
 , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
 - greatest(e.block_id, c.block#) + 1 blocks_corrupted
 , null description
 FROM dba_extents e, v$database_block_corruption c
 WHERE e.file_id = c.file#
 AND e.block_id <= c.block# + c.blocks - 1
 AND e.block_id + e.blocks - 1 >= c.block#
 UNION
 SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 , header_block corr_start_block#
 , header_block corr_end_block#
 , 1 blocks_corrupted
 , 'Segment Header' description
 FROM dba_segments s, v$database_block_corruption c
 WHERE s.header_file = c.file#
 AND s.segment_name is NOT NULL
 AND s.header_block between c.block# and c.block# + c.blocks - 1
 UNION
 SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 , greatest(f.block_id, c.block#) corr_start_block#
 , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
 , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 - greatest(f.block_id, c.block#) + 1 blocks_corrupted
 , 'Free Block' description
 FROM dba_free_space f, v$database_block_corruption c
 WHERE f.file_id = c.file#
 AND f.block_id <= c.block# + c.blocks - 1
 AND f.block_id + f.blocks - 1 >= c.block#
 order by file#, corr_start_block#) t where segment_name IS NOT NULL; 

Solution with workaround:
Force an extension of the SYSTEM rollback segment to occur using the following :

connect / as sysdba
alter system set undo_management = MANUAL scope=spfile ;
shutdown immediate ;
startup ;

create table sample (c1 number, c2 varchar2(10));
 
begin
for i in 1.. 400000 loop
insert into sample values (i,'ABCDEFGH');
end loop;
end;
/

delete sample;
commit;

alter system set undo_management = AUTO scope=spfile;

shutdown immediate
startup 

Then monitor the database again.
ORA-07445 - exception encountered: core dump

Cause : An operating system exception occurred which should result in the creation of a core file. This is an internal error.

Action : Contact Oracle Customer Support.

Common precipitators of the ORA-07445 include:
-- High volume user transactions
-- Software bugs (i.e. Bug 4098853).  See note 342443.1 on MOSC.
-- Too-small RAM regions (shared_pool_size, java pool, large_pool_size), and a too-small application memory stack (e.g. PL/SQL array too small)
-- Too small undo and temp segments
-- Program errors (addressing outside of RAM region), e.g. S0C4. Improper NLS parameter settings
-- Hardware errors
-- Oracle block corruption and a host of other related issues.
-- When Oracle internal job failed with specific exception

Note: There are lots of reason for ORA-07445, Based of arguments and oracle document you can fix it. So may bugs are described in oracle document for ORA-07445.

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

Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.

Action: Use ALTER TABLESPACE ADD TEMPFILE statement to add one or more files to the tablespace indicated.

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 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.

ORA-01031: insufficient privileges 

Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges. This error may occur if the user was granted the necessary privilege at a higher label than the current login.

Action: Ask the database administrator to perform the operation or grant the required privileges. For Trusted Oracle users getting this error although granted the the appropriate privilege at a higher label, ask the database administrator to regrant the privilege at the appropriate label.

For the DBA, the ORA-01031 can happen if the target OS executables do not have read and execute permissions (e.g. (770) in UNIX/Linux), and ensure that the oracle user is a member of the dba group (e.g. /etc/group).  There are similar permission in the Windows registry.

ORA-00918: column ambiguously defined

Cause :A column name used in a join exists in more than one table and is thus referenced ambiguously. In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced. The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN. For example, if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.

Action: Prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.), as in the examples above.

When ORA-00918 is thrown, you have a column which has been ambiguously defined.  If a column name in a join is referenced ambiguously, it exists in multiple tables.
-- Column names which occur in multiple tables should be prefixed when it is referenced by its table name.
-- Columns must be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUM .  Oracle documentation which reference ORA-00918 give the following example:
-- If tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.

To correct ORA-00918, references should be prefixed to column names existing in multiple tables (either with the table name or table alias and a period)

ORA-00054: resource busy and acquire with NOWAIT specified

Cause : This error usually comes when you are changing DDL but object is currently using by other user. It is not a critical error.

Action : Redefine your object using ONLINE key word where ever possible.

ORA-04031 : Unable to allocate x bytes of shared memory

Cause :
ORA-04031 - deals with shared memory issues. It's lack of allocation of contiguous memory as and when requested by the current executing sql i.e., when process attempts to allocate a large piece of contiguous memory in the shared pool fails. 

Action:
 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.
More ORA errors...
Click here to read more for best practice.

ORA-01442
ORA-00910
ORA-17629
ORA-16191
ORA-27069
ORA-00333
ORA-00600
ORA-22992
ORA-12018
ORA-07445
ORA-02020 

Please feel free to post comment....