Sunday, May 1, 2016

ORA ERRORS

ORA ERRORS

1)      ORA-00060
ORA-00600: internal error code, arguments: [%s], [%s],[%s], [%s], [%s] deadlock detected while waiting for resource

Cause 1: Transactions deadlocked one another while waiting for resources.

Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.
If your ORA-00060 is caused by competing resources, the perpetual embrace happens when the aborted task (Task B) attempts to lock a row which is being held by another task (Task A), which, in-turn, is waiting for task B to release a lock.  To prevent a perpetual wait, Oracle aborts the transaction that caused the deadlock.
 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.

 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.

2)    ORA-00000 normal, successful completion
Cause: An operation has completed normally, having met no exceptions.
Action: No action required.

3 )ORA-12541 TNS no listener error
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.
 Using NETCA tools configure tns entry or check tnsnames.ora file for correct entry.

Resolution:
Now you have to follow below mentioned steps to resolve the ORA-12541 issue

Step-1: First check your listener status.

C:\>lsnrctl status
The above command will show the current status of your listener.

Step-2: Start the listener, if listener is not running.

C:\>lsnrctl start
This command will start all your listener services.

Step-3: Stop the listener, if listener is running.

C:\>lsnrctl stop
This command will stop all your listener services.

Step-4: Now restart the listener.

C:\>lsnrctl reload

This command will restart all your listener services.
Note: If you want to see all the available listener commands. Type lsnrtcl help on command prompt and you will get all available options.
C:\>lsnrctl help
LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 – Production on 02-JUL-2015 20:25:14
Copyright (c) 1991, 2010, Oracle. All rights reserved.
The following operations are available
An asterisk (*) denotes a modifier or extended command:

start                           stop                status
services                     version           reload
save_config              trace               change_password
quit                            exit                  set*
show*

Step-5: One last and very important thing which you have to do.

Open sqlnet.ora file and make the changes as suggested.

Change NTS to NONE
SQLNET.AUTHENTICATION_SERVICES = (NONE)
Now restart the listener services and your listener problem ORA-12541 will be resolved.
If this is not working, do write in comment section. I will try to help you at the earliest.

4 ORA-00020:-
        
ORA-00020: maximum number of processes (1100) exceeded 
ERROR at line 1: 
ORA-01012: not logged on

Action -The ORA-00020 is a serious production error because a user cannot connect. 
The ORA-00020 is caused by two things:  
  1. Disconnected processes:  Rogue “zombie” connections  to Oracle that are idle (not working).  To fix this, use theALTER SYSTEM KILL command.  You may also need to kill session at the OS level with the KILL -9 or theORAKILL command.  
  2. Too few process buckets:  Oracle limits the number of connected processes with the processes parameter, and you may get the ORA-00020 error as the natural result in growth of system usage
To fix this, increase the processes parameter, usually doubling the value to allow for future growth.  
The OERR command shows these details for the ORA-00020 error:
ORA-00020: maximum number of processes (string) exceeded

Cause: All process state objects are in use.

Action: Increase the value of the PROCESSES initialization parameter
Simply increase your processes parameter and you are all set! 

 5 ) ORA-32004

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
How do I fix the ORA-32004 error?
Cause:- One or more obsolete and/or parameters were specified in the SPFILE or the PFILE on the server side.
Action-See alert log for a list of parameters that are obsolete. or deprecated. Remove them from the SPFILE or the server side PFILE.
As we see from the ORA-32004 message, we must go to the alert log to see the names of the parameters that are obsolete.  See here, find the alert log location.  You can also find the obsolete parameters with this SQL*Plus query:
select name from v$obsolete_parameter where isspecified='TRUE';
Once found, we must remove them from the spfile or pfile.  You can remove a parameter from an spfile with the alter system command:
alter system reset sort_area_size scope=spfile sid='*';

6) ORA-01031: insufficient privileges 

 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.

7)  ORA-04031 :Unable to allocate n byte of shared pool
 Cause1The ORA-04031 error has many root causes.  Also, see MOSC notes 146599.1 and 396940.1 for more details for resolving the ORA-04031 error:

Action:-
  •  Heavy fragmentation of the shared pool - This can be fixed by increasing theshared_pool_size or doing a "alter system flush shared pool" or bouncing the instance.
  • Too many pinned packages - If you have pinned lots of packages withdbms_shared_pool.keep, they have not leave enough room for new work.
Cause 2) : More shared memory is needed than was allocated in the shared pool.
·         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 INIT.ORA parameters "shared_pool_reserved_size" and "shared_pool_size". If the large pool is out of memory, increase the INIT.ORA parameter "large_pool_size".

Note-The ORA-04031 error also happens when you are using automatic memory management (sga_max_size), and the memory specific is too small.

8 )ORA 04030:- Out Of Process Memory when trying to allocates n Bytes
Cause- If the error message indicate insufficient memory for a process.
Action:- increase the PGA_Aggregate_Size.
If we are not sure which kind of memory issue is there then increase the size of SGA & PGA                 aggregate in proper ratio.
Note:-- For SGA size change –we must shutdown and restart database for the change to take effect.
              For PGA –No need to restart. 

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

 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.

 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.

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

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

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

11 )  ORA-01019: unable to allocate memory in the user side -
    Most of the docs that I could find online told me the same thing, namely:

Cause: The user side memory allocator returned error
Action: Increase the processes heap size or switch to the old set of calls
However, sometimes the Oracle errors that you receive don’t seem to be that helpful in actually determining what the problem is. The ORA-01019 error is no exception in this case…You will find numerous forums online which tell you to fix the ORA-01019 error by “increasing the processes heap size” on the client and that it’s not to do with any configuration, just resource. I have found differently.
The ORA-01019 Solution
So, what you need to check is whether or not you have multiple ORACLE_HOMES installed on the client machine. The reason I say this is because in our case we did. We had an old Oracle 10g client and a new 11g client. We found that the default client was still the 10g one, so when the client was attempting to connect to the database it was using executables and dlls which we didn’t expect or want it to use.
The way to resolve your ORA-01019: unable to allocate memory in the user side is to ensure that you have the correct default path for the Oracle client. The client was running on Windows so if you type PATH from a command prompt it will show you what the default one is
 Troubleshoot : ORA-02020: too many database links in use

12) Resolve "ORA-02020: too many database links in use" error 

One of my database is used as remote database for local / distributed databases. For one requirement we have created 'n' number of db links. So many queries fired to use the dblink and at that time we found below error:

"ORA-02020: too many database links in use "

  The current session has exceeded the INIT.ORA open_links maximum.

To find:
---------
SQL> show parameter open_links;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_links                           integer         4
open_links_per_instance      integer         4


Action: 
-------
Increase the open_links limit, or free up some open links by committing or rolling back the transaction and canceling open cursors that reference remote databases.

To Change:

----------
SQL> alter system set open_links_per_instance=10 scope=spfile;
SQL> alter system set open_links=10 scope=spfile;

and then, bounce the database.

Note:
> The default value is set to 4.
> If it is set to 0, distributed transactions are not allowed.
> If you are expecting your transactions to have a maximum of 3 database links open concurrently, set this parameter to 3 or higher.
> Do not set it too high, it is better for the application to close database links when no longer in use than to change the parameter to a high number.

If you are not sure how many database links are opened up concurrently by your session's database application, you can query v$dblink.

SQL> select in_transaction, count(*) from v$dblink
     group by in_transaction;

IN_     COUNT(*)
---       ----------
YES          1

Extra Coverage:

Close a db link
To explicitly close the database link , use the command below:

SQL> alter session close database link remotedb;  -- remotedb --> a dblink name
Session altered.

OR

SQL> exec DBMS_SESSION.CLOSE_DATABASE_LINK (dblink_name);

Know your open links


Once you have created and made use of a database link in Oracle,
you might want to keep an eye on the number of concurrent open database links
in your database so you can tune the open_links initialization parameter.

Read more here on limiting concurrent open links.

You will need to query v$dblink to see how many links are open in your session:

SQL> select in_transaction, count(*) from v$dblink
     group by in_transaction;

IN_   COUNT(*)
--- ----------
YES          1

Here are some interesting columns and descriptions of v$dblink: 

db_link               Db link name
owner_id            Owner name
logged_on           Is the database link currently logged on?
protocol              Dblink's communications protocol
open_cursors      Are there any cursors open for the db link ?
in_transaction      Is the db link part of a transaction which has not been commited or rolled back yet ?
update_sent        Was there an update on the db link ?

dba_db_links

To gather information on all database links in your database, query dba_db_links.
You will need dba privileges to see this view, the alternatives are user_db_links and all_db_links.

A user who does not have dba privileges can query all_db_links to see which db links are available to him.

SQL> select * from dba_db_links;

OWNER            DB_LINK       HOST       CREATED
----------           ----------         ----------   ----------
GOURANG      REMOTEDB    remotedb   12-May-2014 18:10:01

Some interesting columns: 

owner           User who owns the db link, will state 'PUBLIC' if it is a public database link.
db_link         Db link name.
username      Username that was specified if it was hardcoded during the create statement, null if not         
                     specified during the create statement.
host              The tnsnames alias specified during the create statement.
created         Date and time of link creation.

13) 

a- ORA-01652 

What is the cause of the ORA-01652 error?
Action:- Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

This sill add space t the TEMP tablespace:

alter tablespace
   temp
add tempfile
'e:\oracle\app\oradata\zoom\temp02.dbf' size 1g
autoextend on;
You can check for held TEMP segments with this query:
select
   srt.tablespace,
   srt.segfile#,
   srt.segblk#,
   srt.blocks,
   a.sid,
   a.serial#,
   a.username,
   a.osuser,
   a.status
from
   see code depot for full scripts
   v$session    a,
   v$sort_usage srt
where
   a.saddr = srt.session_addr
order by
   srt.tablespace, srt.segfile#, srt.segblk#,
   srt.blocks;
First ORA-01652 may occur because there is simply no space available in the temp tablespace of which is being used.  The second cause of ORA-01652 may have to do with the local temp segment not being able to extent space even though there is space in other instances. 
To trouble shoot for ORA-01652,  and find out which of the above scenarios are causing ORA-01652 use this query:
select
   sum(free_blocks)
from
   gv$sort_segment
where
   tablespace_name = '<TEMP TABLESPACE NAME>'
You will know that the first scenario is causing ORA-01652 to be thrown if the free block reads '0' because it signifies that there is no free space.
If there is a good amount of space, you know that there is another cause for ORA-01652, and it is probably the second scenario.  It is important to note that in a non-RAC environment, local instances are not able to extend the temp segments, so in the RAC environment, ORA-01652 has to be handled differently.  If you are experiencing ORA-01652 in a non-RA environment, be aware that every SQL making use of the tablespace can fail

For more option n quick action follow this link
http://onlineappsdba.com/index.php/2009/07/27/ora-1652-unable-to-extend-temp-segment-by-128-in-tablespace-temp/

Scenario 2- I understand ORA-01652 is usually caused by running out of space When I run Oracle parallel query, I keep receiving ORA-01652, why?

Answer: In this case, there was a sort in the parallel query which continues to cause ORA-01652 to be thrown.  Remember, the parallel query coordinator has receives the returned results from the parallel processes as a last step of the OPQ sort.  This being, you should be able to resolve ORA-01652 by increasing TEMP, and perhaps also the sort_area_size.  For advice on this, refer to the statement below:

4 comments:

  1. its very helpful ....... keep it up.....

    ReplyDelete
  2. The most effective method to Solve Oracle ORA-01019 Error Message through Remote DBA Services
    The Oracle ORA-01019 blunder message implies unfit to distribute the memory in client side. The fundamental driver for this issue is the client side memory allocator returned mistake. Well! To tackle this issue you need to expand the procedures pile estimate or likewise you need to change to old arrangement of calls. Be that as it may, on the off chance that you need snappy arrangement with respect to this issue at that point rapidly contact to Database Administration for Oracle or Online Oracle DB Support.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete
  3. Nice explanation in an easy way.....

    ReplyDelete