1.1. ORA-00001: unique constraint (string.string) violated
1.2. ORA-00018: maximum number of sessions exceeded
1.3. ORA-00020: maximum number of processes (string) exceeded
1.4. ORA-00026: missing or invalid session ID
1.5. ORA-00036: maximum number of recursive SQL levels (string) exceeded
1.6. ORA-00054: resource busy and acquire with NOWAIT specified
1.7. ORA-00058: DB_BLOCK_SIZE must be string to mount this database (not string)
1.8. ORA-00059: maximum number of DB_FILES exceeded
1.9. ORA-00100: no data found
1.10. ORA-00106: cannot startup/shutdown database when connected to a dispatcher
1.11. ORA-00204 / ORA-00205 / ORA-00206 / ORA-00207 : error in reading (block string, # blocks string) of control file
1.12. ORA-00210: cannot open the specified control file
1.13. ORA-00221: error on write to control file
1.14. ORA-00227: corrupt block detected in control file.
1.15. ORA-00230: operation disallowed: snapshot control file enqueue unavailable
1.16. ORA-00265: instance recovery required, cannot set ARCHIVELOG mode
1.17. ORA-00272: error writing archive log string
1.18. ORA-00371: not enough shared pool memory, should be atleast string bytes
1.19. ORA-00381: cannot use both new and old parameters for buffer cache size specification
1.20. ORA-0470 LGWR process terminated with error
1.21. ORA-00483: During shutdown a process abnormally terminated
1.22. ORA-00724: ALTER DATABASE CONVERT command has been de-supported
1.23. ORA-600[KELTNFY-LDMINIT]
1.24. ORA-601: cleanup lock conflict
1.25. ORA-1031 Insufficient privileges
1.26. OERR: ORA 1092 Oracle instance terminated
1.27. ORA-01034 Oracle not available
1.28. ORA-01124: cannot recover data file 1 - file is in use or recovery
1.29. ORA-12547 tns lost contact
1.30. ORA-20854
1.31. ORA-01501: CREATE DATABASE failed
1.32. ORA-01502: index 'string.string' or partition of such index is in unusable
1.33. ORA-01506: missing or illegal database name
1.34. ORA-01507: database not mounted
1.35. ORA-01523: cannot rename data file to 'string' - file already part of database
1.36. ORA-27102: out of memory
1.37. ORA-29285: file write error
1.38.
1.1.
ORA-00001: unique constraint (string.string) violated
Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
Action: Either remove the unique restriction or do not insert the key.
To determine if duplicate rows exist in the table:
Set Up Data:-
SQL> create table dupe_row( id number(2) );
Table created.
declare
i integer:=1;
begin
while ( i <= 20 ) loop insert into dupe_row values ( i ); i:=i+1; end loop; end; / commit; Run the PL/SQL script couple of times to generate your data. Verify that there is enough data to work with. SELECT COUNT(1) FROM dupe_row; -- insert one more row insert into dupe_row values ( 99 ) ; commit; METHOD 1 SELECT rowid, id , count(1) FROM tbl_nm GROUP BY id HAVING COUNT(1) > 1;
To Delete duplicates:
DELETE FROM tbl_nm WHERE rowid in (
SELECT rowid
FROM tbl_nm
GROUP BY id HAVING COUNT(1) > 1 );
METHOD 2
Delete From dupe_row A
Where A.Rowid > ANY (Select B.Rowid From dupe_row B Where B.id = A.id);
--20 rows deleted
METHOD 3
delete from dupe_row where rowid in ( select rowid from dupe_row
group by rowid, id
minus
select min(rowid) from from dupe_row group by id );
--20 rows deleted.
METHOD 4
DELETE dupe_row
WHERE rowid IN
( SELECT LEAD(rowid) OVER
( PARTITION BY id
ORDER BY id )
FROM dupe_row );
--20 rows deleted.
Please note that id is the key, it could be a single value or a composite key.
1.2. ORA-00018: maximum number of sessions exceeded
If you get this error then increase the number of sessions in init.ora file.
ALTER SYSTEM SET SESSIONS=300 scope=both;
Sessions are different from processes.
1.3. 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.
This happens when there are more users connecting to the database.
One way to solve this is to create a profile for a user and set the idle limit
on it.
This could also be caused by a runaway java connection pool.
1.4. ORA-00026: missing or invalid session ID
Cause: Missing or invalid session ID string for ALTER SYSTEM KILL SESSION.
Action: Retry with a valid session ID.
Ensure that you are using the right session id.
SELECT * FROM V$SESSION WHERE SID=
In a RAC environment you have to specify the right session id or you would be
killing another user's session.
1.5. ORA-00036: maximum number of recursive SQL levels (string) exceeded
Cause: An attempt was made to go more than the specified number of recursive
SQL levels.
Action: Remove the recursive SQL, possibly a recursive trigger.
This can be caused if a routine calls itself recursively.
PSEUDO CODE:-
function fn() begin fn end; #-- This will cause ORA-00036.
1.6. ORA-00054: resource busy and acquire with NOWAIT specified
Cause: Resource interested is busy.
Action: Retry if necessary.
SELECT * FROM V$LOCKED_OBJECT; -- To determine which user is holding the lock
on the object.
1.7. ORA-00058: DB_BLOCK_SIZE must be string to mount this database (not string)
Cause: DB_BLOCK_SIZE initialization parameter is wrong for the database
being mounted. It does not match the value used to create the database.
Action: Fix the value of the DB_BLOCK_SIZE parameter or mount a
database that matches the value.
1.8. ORA-00059: maximum number of DB_FILES exceeded
Cause: The value of the DB_FILES initialization parameter was exceeded.
Action: Increase the value of the DB_FILES parameter and warm start.
SQL> show parameter db_files
NAME TYPE VALUE
------------------------------------ -----------
db_files integer 200
SQL> alter system set db_files=250 scope=spfile;
System altered.
Please note that this is different from MAXDATAFILES which is a controlfile
property, in order to incrase maxdatafiles we have to recreate the control
file.
1.9. ORA-00100: no data found
Cause: An application made reference to unknown or inaccessible data.
Action: Handle this condition within the application or make
appropriate modifications to the application code. NOTE: If the
application uses Oracle-mode SQL instead of ANSI-mode SQL, ORA-01403
will be generated instead of ORA-00100.
This is how a ORA-100 is handled in a plsql block.
DECLARE
ln_no number(2):=0;
BEGIN
SELECT 1 INTO ln_no FROM dual where 1=2;
WHEN NO_DATA_FOUND THEN
null; --do something.
END;
1.10. ORA-00106: cannot startup/shutdown database when connected to a dispatcher
Cause: An attempt was made to startup/shutdown database when connected to
a shared server via a dispatcher.
Action: Re-connect as user INTERNAL without going through the
dispatcher. For most cases, this can be done by connect to INTERNAL
without specifying a network connect string.
You have to be connected as dedicated to the server.
SELECT server FROM v$session where sid=120;
This should display as dedicated;
1.11. ORA-00204 / ORA-00205 / ORA-00206 / ORA-00207 : error in reading (block string, # blocks string) of control file
Cause: A disk I/O failure was detected on reading the control file.
Action: Check if the disk is online, if it is not, bring it online and
try a warm start again. If it is online, then you need to recover the
disk.
Ensure that the disk is available, this probably indicates that Oracle was
unable to read the disk. It could lead to media corruption.
Check the alert log and log an SR with Oracle.
1.12. ORA-00210: cannot open the specified control file
Cause: Cannot open the control file.
Action: Check to make sure the control file exists and is not locked
by some other program.
As the error indicates check if the control file exists.
1.13. ORA-00221: error on write to control file
Cause: An error occurred when writing to one or more of the control files.
Action: See accompanying messages.
Check if the control file is available. Check alert log.
1.14. ORA-00227: corrupt block detected in control file.
Cause: A block header corruption or checksum error was detected on reading
the control file.
Action: Use the CREATE CONTROLFILE or RECOVER DATABASE USING BACKUP
CONTROLFILE command.
1.15. ORA-00230: operation disallowed: snapshot control file enqueue unavailable
Cause: The attempted operation cannot be executed at this time because
another process currently holds the snapshot control file enqueue.
Action: Retry the operation after the concurrent operation that is
holding the snapshot control file enqueue terminates.
As the error indicates another process is holding the enqueue on the control
file. Determine which process is holding the CF enqueue.
SELECT * FROM V$ENQUEUE_LOCK;
1.16. ORA-00265: instance recovery required, cannot set ARCHIVELOG mode
Cause: The database either crashed or was shutdown with the ABORT option.
Media recovery cannot be enabled because the online logs may not be
sufficient to recover the current datafiles.
Action: Open the database and then enter the SHUTDOWN command with the
NORMAL or IMMEDIATE option.
If you want to set the database mode to archivelog you have to do a shutdown
immediate or shutdown normal.
1.17. ORA-00272: error writing archive log string
Cause: An I/O error occurred while archiving a redo log file.
Action: Check that the output device is still available and correct
any device errors that may have occurred. Also, make certain that
sufficient space for archiving is available on the output device.
Check the device disk space on the archive log destinatino directory.
1.18. ORA-00371: not enough shared pool memory, should be atleast string bytes
Cause: Init.ora parameter shared_pool_size is too small
Action: Increase the parameter value
Increase the size of the shared pool size.
1.19. ORA-00381: cannot use both new and old parameters for buffer cache size specification
Cause: User specified one or more of { db_cache_size ,
db_recycle_cache_size, db_keep_cache_size, db_nk_cache_size (where n is
one of 2,4,8,16,32), db_cache_advice } AND one or more of {
db_block_buffers, buffer_pool_keep , buffer_pool_recycle }. This is
illegal.
Action: Use EITHER the old (pre-Oracle_8.2) parameters OR the new
ones. Don't specify both. If old size parameters are specified in the
parameter file, you may want to replace them with new parameters since
the new parameters can be modified dynamically and allow you to
configure additional caches for additional block sizes. Cache advisory
can only be enabled with the new cache parameters.
db_cache_size and db_block_buffers are mutually exclusive you can use only
one, cannot specify both.
This error is common when you are using /3GB switch in Windows.
1.20. ORA-0470 LGWR process terminated with error
Normally ORA-470 signals that the LGWR process terminated due to an error. And
hence PMON terminated the instance due to error ORA-470.
Failure in one background process causes failure in all of them and ultimately
the instance will terminate.
Look for errors in the alert
One has to look at all the error log trace files to determine the cause
of the problem. DBWR, LGWR, PMON, SMON and so on. Look for the trace files
in the udump directory.
If there are no files written then the error could be at the OS level
check for NFILE kernel parameter in Unix. Call your sysadmin to
look at the OS specific errors.
1.21. ORA-00483: During shutdown a process abnormally terminated
Cause: One of the background processes did not exit normally at or near
the time of shutdown.
Action: Use shutdown abort.
Check the alert log for more information.
1.22. ORA-00724: ALTER DATABASE CONVERT command has been de-supported
Cause: ALTER DATABASE CONVERT command has been de-supported since Oracle
10i.
Action: No action required.
1.23.1. ORA-00823: Specified value of sga_target greater than sga_max_size
Cause: The specified value of sga_target is greater than sga_max_size.
Action: Increase sga_max_size to match up with sga_target or decrease
sga_target to match up with sga_maxsize.
1.23.2. ORA-00825: cannot set db_block_buffers if sga_target set
Cause: sga_target set with db_block_buffers set.
Action: Do not set sga_target or use new cache parameters and do not
use db_block_buffers which is a old cache parameter.
Ensure that you are using db_cache instead of db_block_buffers.
1.23. ORA-600[KELTNFY-LDMINIT]
This was a error which took lot of googling with no results.
The final solution was to make appropriate entries into /etc/hosts file
my current /etc/hosts file looks like this :-
127.0.0.1 localhost
127.0.0.1 localhost.localdomain
1.24. ORA-601: cleanup lock conflict
Look in the alert log, this could be a bug with Oracle.
1.25. ORA-1031 Insufficient privileges
cd /u02/app/oracle/product/10.2.0/db_1/bin
or
cd $ORACLE_HOME/
ls -ltr
if the files do not belong to the dba group then change the owner to
dba as follows:-
su -
chown -R oracle:dba ./*
ls -l
now the listing should look like
-rwxr-x--x 1 oracle dba 1125429 Jun 3 17:21 hsodbc
-rwxr-xr-x 1 oracle dba 437 Jun 3 17:21 commonenv
-rwx------ 1 oracle dba 2931 Jun 3 17:21 onsctl
-rwxr-x--- 1 oracle dba 676324 Jun 3 17:21 ldapbind
-rwxr-xr-x 1 oracle dba 1722 Jun 3 17:21 emca
-rwxr-xr-x 1 oracle dba 5401 Jun 3 17:21 dbca
sqlplus /nolog
sql>connect sys/passwd as sysdba
sql>startup;
1.26. OERR: ORA 1092 Oracle instance terminated
Look for error messages in the alert
sqlplus: error while loading shared libraries: libsqlplus.so: cannot open
shared object file: No such file or directory
Ensure that all directories are readable by the user.
chmod -R 755 $ORACLE_HOME/
Also check the environment variable
LD_LIBRARY_PATH to $ORACLE_HOME/lib:/usr/lib
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
1.27. ORA-01034 Oracle not available
"Oracle Not Available" is a general indication that your database is not
running. Please follow the below steps and try to connect again:
WINDOWS
1. net start oracleService
2. C:\> set ORACLE_SID=
3. C:\> set ORACLE_HOME=
4. C:\> sqlplus "sys/password as sysdba"
UNIX /Linux
1. set ORACLE_SID=
2. sqlplus " / as sysdba"
3. startup;
4. note any error messages in alert
If error still persists, check the contents of Alert log file
(background_dump_dest parameter in your INIT.ORA file). It shall give a clear
message about the problem you are facing.
1.28. ORA-01124: cannot recover data file 1 - file is in use or recovery
Please check if the database is already running.
1.29. ORA-12547 tns lost contact
This is a broad error. Ensure that all OS dependencies are
met before you even go ahead in solving the problem.
Linux
Ensure that Libaio is of correct version.
HP tru64
Ensure that all dependencies are met. Kernel parameters.
1.30. ORA-20854
http://forums.oracle.com/forums/search.jspa?objID=f61&q=ORA-02085+
http://www.dbasupport.com/oracle/ora9i/naming02.shtml
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:633023740029
1.31. ORA-01501: CREATE DATABASE failed
Cause: An error occurred during create database
Action: See accompanying errors.
Check the alert.log
1.32. ORA-01502: index 'string.string' or partition of such index is in unusable
state
Cause: An attempt has been made to access an index or index partition that
has been marked unusable by a direct load or by a DDL operation
Action: DROP the specified index, or REBUILD the specified index, or
REBUILD the unusable index partition
Rebuild the partition.
1.33. ORA-01506: missing or illegal database name
Cause: No db_name INIT.ORA aprameter was specified.
Action: The database name must be given in the db_name INIT.ORA
parameter.
Check the db name in the initSID.ora or spfile .
1.34. ORA-01507: database not mounted
Cause: A command was attempted that requires the database to be mounted.
Action: If you are using the ALTER DATABASE statement via the SQLDBA
startup command, specify the MOUNT option to startup; else if you are
directly doing an ALTER DATABASE DISMOUNT, do nothing; else specify
the MOUNT option to ALTER DATABASE. If you are doing a backup or copy,
you must first mount the desired database. If you are doing a
FLASHBACK DATABASE, you must first mount the desired database.
SQL>ALTER DATABASE MOUNT;
1.35. ORA-01523: cannot rename data file to 'string' - file already part of database
Cause: During ALTER DATABASE RENAME or ALTER TABLESPACE RENAME, the new
name of a file is already present in the control file.
Action: Rename the file to a name not already being used as part of
the database.
Check if the file name is already present.
1.36. ORA-27102: out of memory
Cause: Out of memory
Action: Consult the trace file for details
Solaris 10
1.37. ORA-29285: file write error
Cause: Failed to write to, flush, or close a file.
Action: Verify that the file exists, that it is accessible, and that it is
open in write or append mode.
You may need to supply a value for the max line length default in the FOPEN
call:
UTL_FILE.FOPEN (
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER)
RETURN file_type;
max_linesize = Maximum number of characters per line, including the newline
character, for this file. (minimum value 1, maximum value 32767).
The default is approximately 1000 bytes.
1.38.
ORA-39700: database must be opened with UPGRADE option
Cause: A normal database open was attempted, but the database has not been
upgraded to the current server version.
Action: Use the UPGRADE option when opening the database to run catupgrd.sql
(for database upgrade), or to run catalog.sql and catproc.sql (after initial
database creation).
The error probably means that there is a mismatch between the Datafile version
and the oracle binary version.
One example
Database / Datafile => 10.1.0.1
Oracle Binary => 10.1.0.2
In that case you will get the error indicating you to upgrade the Datafile /
database.
Having said that. There are couple of things we can do:-
1. if you feel adventurous you can start the database using the upgrade
option.
SQL>startup upgrade;
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10763/upgrade.htm#sthref136
2. Else, figure out the datafile version and try to put all the datafiles in
the same oracle version in which they were created and open the database.
Please take this with a grain of salt and use your discretion.
No comments:
Post a Comment