Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 4 December 2012

Important ORA- Errors and their Solution

ORA-01034/ ORA-07318: Oracle not available or No such file or directory
In case of PC client while the Oracle instance is shutdown and they are trying to access, restart the instance.
ORA-01033: Initialization and shutdown in progress
Check to see it may be the target database is indeed in the middle of the initialization or shutdown progress or may be the oracle attempting to startup or shutdown and is hanging due to any reason.
Try to go into Administrative Tools->Services and making sure that both your Listener service and the actual Database service are both set to automatic. Turn everything else for Oracle (like the Management Server) off (set to manual). Reboot and see what happens.
Well if the services are set to automatic and they actually are showing as "Started" then perhaps you need to Stop and Restart the services and then try.
ORA-00106 cannot startup/shutdown database when connected to a dispatcher
Cause: An attempt was made to start or shut down an instance while connected to a shared server via a dispatcher.
Action: Reconnect 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.
ORA-00107 failed to connect to ORACLE listener process
Cause: Most likely due to the fact that the network listener process has not been started.
Action: Check for the following:
§         The network configuration file is not set up correctly.
§         The client side address is not specified correctly.
§         The listener initialization parameter file is not set up correctly.
ORA-01031: insufficient privileges
If you are seeing this error on a windows machine when doing 'sqlplus / as sysdba', you might want to try the following:
§         Make sure the oracle user is a member of the dba group (or ora_dba group)
§         Make sure that the sqlnet.ora has the following line in it:
Follow the separate post for detailed description of above related issues:
ORA-00257: Archiver is stuck. Connect internal only
§         The archive destination is probably full, backup the archive log and removes them to free some space of the drive, the archiver will restart.
§         Check the initialization parameter ARCHIVE_LOG_DEST in the initialization file is set correctly.
§         It may be always helpful for more details if you are checking the trace file.
RMAN-06059: expected archived log not found
RMAN attempted to backup an archivelog file, but couldn't find it.
Cause: This can happen for a variety of reasons; the file has been manually moved or deleted, the archive log destination has recently been changed, the file has been compressed etc.
Your options are either to restore the missing file(s), or to perform a crosscheck.
RMAN>change archivelog all crosscheck;
Note: It is advisable to perform a full backup of the database at this point.
When an archive log crosscheck is performed, RMAN checks each archive log in turn to make sure that it exists on disk (or tape). Those that are missing are marked as unavailable. If you have got missing logs, this won't bring them back. It will allow you to get past this error and back-up the database though. For more detail solution follow the separate post in this blog:RMAN-06059: expected archived log not found
ORA-01118: Cannot add any more data file limit exceeded.
When the Database is created the db_file parameter in the initialization file is set to a limit. You can shutdown the database and reset these up to the MAX_DATAFILE as specified in database creation. The default for MAXDATAFILES is 30. If the MAX_DATAFILES is set to low, you will have to rebuild the control file to increase it before proceeding.
The simplest way to recreate the controlfile to change the ‘hard’ value MAXDATAFILES is
Then go to UDUMP destination pick it up and modify the value of MAXDATAFILES
SQL>@(name of edited file);
Finally mount and open the database.
ORA-01537: cannot add data file
An ORA-01537 is thrown when attempting to re-add a missing tempfile to a temporary tablespace:
SQL> select name from v$tempfile;
SQL> alter tablespace TEMP
add tempfile ' D:\oracle\oradata\orcl3\temp01.dbf' reuse;
ERROR at line 1:
ORA-01537: cannot add data file ' D:\oracle\oradata\orcl3\temp01.dbf' - file already part of database.
This can happen if a step has been missed during a database cloning exercise.
Solution: With a temporary tablespace either to drop the missing tempfile and then add a new one or use a different file name and leave the previous file as it is.
You can only drop a tempfile if it is not in use, but our case the temp file doesn't actually exist, so it can't be in use.
Alter tablespace <TEMP_TS_NAME>
Drop tempfile '<FILE_PATH_AND_NAME>';
Alter tablespace <TEMP_TS_NAME>
add tempfile '<FILE_PATH_AND_NAME>' size <FILE_SIZE>;
For example:
SQL> alter tablespace temp
 drop tempfile ' D:\oracle\oradata\orcl3\temp01.dbf';
Tablespace altered.
SQL> alter tablespace TEMP
    add tempfile ' D:\oracle\oradata\orcl3\temp01.dbf'  size 8192m;
Tablespace altered.
ORA-00055: Maximum Number of DML locks exceeded
The number of DML Locks is set by the initialization parameter DML_LOCKS. If this value is set to low (which it is by default) you will get this error. Increase the value of DML_LOCKS. If you are sure that this is just a temporary problem, you can have them wait and then try again later and the error should clear. Some times it occurs at the moment of peak usage of database.
Change in the parameter file (initSID.ora) For Example: dml_locks = 200
If you set the dml lock limit to 200 that means:
§         200 people could each be updating one table at a time.
§         20 people could all be updating 10 table at a time.
§         Or 1 user could be doing an account number rename and using 100 tables while 10 others people could be updating 10 tables.
ORA-00283: ORA-00314: ORA-00312:
While trying to restore controlfile from backup, while recovering got the following error 
ORA-00283: recovery session canceled due to errors
ORA-00314: log 2 of thread 1, expected sequence# 2 doesn't match 11
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/jay/redo02.log'
Cause: In any case, the archivelog must be backed up else, a RESTORE alone cannot do a RECOVER. If your database backup did not include the Archivelogs, then the backup you created does not have the Redo information that Oracle must apply to the Database Backup. That is why you got the "unknown log'. Also, if the controlfile backup is before the archivelog backup, the controlfile, even when restored, is not aware of the archivelogs in the backup created subsequent to it.
RMAN can still do a RECOVER, implicitly using the "BACKUP CONTROLFILE" and doing a rollforward but it needs to have to restore the Archivelog first -- and the information about which Backupset contains the ArchiveLog is not available to it. You would need to CATALOG the Archivelog Backupset and then restore the archivelogs from there.
(If you use an RMAN Recovery Catalog database, then of course, the Catalog has information about the ArchiveLogs and the Backupsets containing the Archivelogs so RMAN queries the Catalog to identify the Backupsets and extracts the necessary Archivelogs from the Backupsets).
SQL> archive log list;  
Database log mode              Archive Mode  
Automatic archival             Enabled  
Archive destination            USE_DB_RECOVERY_FILE_DEST  
Oldest online log sequence     11  
Next log sequence to archive   13  
Current log sequence           13  
SQL>alter database clear logfile 'D:\oracle\oradata/orcl3/redo02.log';   
Database altered. 
SQL>alter database open;   
SQL>recover database until cancel;  
Specify log: {<ret>=suggested | filename | AUTO | CANCEL}  
Media recovery cancelled.  
SQL> alter database open resetlogs;  
Imp-00037 - Character set marker unknown
Cause: This usually means that the export file is corrupted.
§         If you had previously compressed the dump file, make sure that you unzip it before importing it.
§         Check the NLS setting of both side and make sure that the client server has the same NLS setting as the production server (you can set only for the import session too).
§         Check the export and import utility version. It can also cause due to different version export is imported through different version import. IMP HELP = Y will display utility version along with other information or use import with SHOW = Y option for the same.
§         If the export file is not corrupted, report this as an import internal error and submit the export file to customer support.
OSD-04008: Write File() failure, unable to write to file (OS 33)
Generally it happens on windows server when another process/user/something other than oracle access the same database file.
Errors in file D:\oracle\admin\orcl3\bdump\orcl3_ckpt_1144.trc:
ORA-00221: error on write to controlfile
ORA-00206: error in writing (block 3, # blocks 1) of controlfile
ORA-00202: controlfile: 'D:\oracle\oradata\orcl3\control01.ctl'
ORA-27072: skgfdisp: I/O error
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 33) The process cannot access the file because another process has locked a portion of the file.
CKPT: terminating instance due to error 221
Errors in file D:\oracle\admin\orcl3\bdump\orcl3_pmon_1132.trc:
ORA-00221: error on write to controlfile
If you are experiencing this problem and it is happening at seemingly random times, I'd check for the presence of anti-virus software. If you have some installed, configure it not to scan the databases data files.
If the problem is occurring at roughly the same time everyday, and that time just happens to fall during the host backup, then that is a likely problem in the backup utility that locks the file. Check the scheduled backup job. For more detail about this issue check metalink note: 130871.1 and doc_id: 352819.999
ORA-19809: limit exceeded for recovery files
The flash recovery area is full:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.   
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 10150912 bytes disk space from 2147483648 limit
ARC0: Error 19809 Creating archive log file to
ARC0: Failed to archive thread 1 sequence 444 (19809)
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance orcl3 - Archival Error
ORA-16038: log 2 sequence# 444 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: 'D:\oradata\orcl3\redo02.log'
ORA-16038: log 2 sequence# 444 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 2 thread 1: ' D:\oradata\orcl3\redo02.log'
Thread 1 cannot allocate new log, sequence 446
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 444 (4)
ARCH: Archival stopped, error occurred. Will continue retrying
ORA-16014: log 2 sequence# 444 not archived, no available destinations
The following query will show the size of the recovery area and how full it is:
select    name,  floor(space_limit / 1024 / 1024) "Size MB"‎‎,    ceil(space_used  / 1024 / 1024) "Used MB"
from    v$recovery_file_dest order by name;
To fix the problem, you need to either make the flash recovery area larger, or remove some files from it. If you have the disk space available, make the recovery area larger and bounce the instance back to take effect this change:
Alter system set db_recovery_file_dest_size=<size> scope=both
To remove files you must use RMAN. Manually moving or deleting files will have no effect as oracle will be unaware.
The obvious choice is to backup and remove some archive log files. However, if you usually write your RMAN backups to disk, this could prove tricky. RMAN will attempt to write the backup to the flash recovery area which is full. You could try sending the backup elsewhere using a command such as this:
rman target sys/oracle@orcl3 catalog catalog/catalog@rman
run {
allocate channel t1 type disk;
backup archivelog all delete input format 'D:\temp_location\arch_%d_%u_%s';
release channel t1;
This will backup all archive log files to a location of your choice and then remove them. 
For this purpose you can also consider changing rman retention policy and rman archivelog deletion policy. For Example if you have rman retention policy 3 you can limit it 2 same as you can limit archivelog deletion policy to the weekly instead of monthly in your scheduled backup. More detailed solution you can click on the deperate post for this error: ora-19815
ORA-32021: parameter value longer than 255 characters
It is in fact possible to set parameter values larger than 255 characters. To do so you need to split the parameter up into multiple smaller strings, like this:
Alter system set <parameter> = 'string1','string2' scope=both;
ORA-16654: Fast-Start Failover is enabled
I recently received this error after performing a 'flashback database' on a primary database that was part of a data guarded pair. I needed to open the database with resetlogs, but because dataguard was configured for fast-start failover, the broker wouldn't allow it. Normally, I would simply stop the broker momentarily, but when I tried to, this happened:
SQL> alter system set dg_broker_start=false;
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16654: Fast-Start Failover is enabled
The solution in this case was to disable fast-start failover using dgmgrl, stop the broker, open the database resetlogs, and then re-enable fast-start failover afterwards:
oracle@bloo$ dgmgrl /
DGMGRL for Linux: Version - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> disable fast_start failover
DGMGRL> stop observer
DGMGRL> exit
oracle@bloo$ sqlplus / as sysdba
SQL*Plus: Release - Production on Sat Apr 21 10:37:59 2007
Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter system set dg_broker_start=false;
System altered.
SQL> alter database open resetlogs;
Database opened.
SQL> alter system set dg_broker_start=true;
System altered.
SQL> exit
oracle@bloo$ dgmgrl /
DGMGRL> enable fast_start failover
DGMGRL> start observer
ORA-00056 DDL lock on object 'string.string' is already held in an incompatible mode
Cause: The attempted lock is incompatible with the DDL lock already held on the object. This happens if you attempt to drop a table that has parse locks.
Action: Before attempting to drop a table, check that it has no parse locks. Wait a few minutes before retrying the operation.
ORA-00057 maximum number of temporary table locks exceeded
Cause: The number of temporary tables equals or exceeds the number of temporary table locks. Temporary tables are often created by large sorts.
Action: Increase the value of the TEMPORARY_TABLE_LOCKS initialization parameter and restart Oracle.
ORA-00058 DB_BLOCK_SIZE must be string to mount this database (not string)
Cause: The value of the DB_BLOCK_SIZE initialization parameter used to start this database does not match the value used when that database was created.
Potential reasons for this mismatch are:
§         mounting the wrong database
§         using the wrong initialization parameter file
§         the value of the DB_BLOCK_SIZE parameter was changed
Action: For one of the above causes, either:
§         mount the correct database
§         use the correct initialization parameter file
§         correct the value of the DB_BLOCK_SIZE parameter
SQL> show user;
SQL>select * from v$version;
SQL>show parameter db_files
SQL>alter system set db_files = 256 scope = spfile;
SQL>shutdown immediate;
SQL>show parameter db_files;
ORA-00060 deadlock detected while waiting for resource
Cause: Your session and another session are waiting for a resource locked by the other. This condition is known as a deadlock. To resolve the deadlock, one or more statements were rolled back for the other session to continue work.
Action: Either:
§         Enter a ROLLBACK statement and re-execute all statements since the last commit or
§         Wait until the lock is released, possibly a few minutes, and then re-execute the rolled back statements.
For more detailed solution description follow the separate post: ORA-00060 (DEADLOCKS)
ORA-00104 deadlock detected; all public servers blocked waiting for resources
Cause: All available public servers are servicing requests that require resources locked by a client which is unable to get a public server to release the resources.
Action: Increase the limit for the system parameter MAX_SHARED_SERVERS as the system will automatically start new servers to break the deadlock until the number of servers reaches the value specified in MAX_SHARED_SERVERS.
ORA-00063 maximum number of LOG_FILES exceeded
Cause: The value of the LOG_FILES initialization parameter was exceeded.
Action: Increase the value of the LOG_FILES initialization parameter and restart Oracle. The value of the parameter needs to be as large as the highest number of log files that currently exist rather than just the count of logs that exist.
ORA-00092 LARGE_POOL_SIZE must be greater than LARGE_POOL_MIN_ALLOC
Cause: The value of LARGE_POOL_SIZE is less than the value of LARGE_POOL_MIN_ALLOC.
Action: Increase the value of LARGE_POOL_SIZE past the value of LARGE_POOL_MIN_ALLOC.
Typically a size of 64MB is sufficient for most large pools, but if this is not enough and gets errors like this:
ORA-04031: unable to allocate 65704 bytes of shared memory ("large pool","unknown object","large pool","PX msg pool")
ORA-12853: insufficient memory for PX buffers: current 65400K, max needed 1512000K
Then you must configure "Large pool" size with a more accuracy.
SELECT nvl(name, 'large_pool') name, round(SUM(bytes)/1024/1024,2) size_mb
FROM V$SGASTAT WHERE pool='large pool'
If you run out of free memory in the large pool then increase it (for example the statement)
Alter system set large_pool_size = 389539635 scope=both;
ORA-00116 SERVICE_NAMES name is too long
Cause: The service name specified in the SERVICE_NAMES initialization parameter is too long.
Action: Use a shorter name for the SERVICE_NAMES value (less than or equal to 255 characters).
ORA-00132 syntax error or unresolved network name 'string'
Cause: Listener address has syntax error or cannot be resolved.
Action: If a network name is specified, check that it corresponds to an entry in tnsname.ora or other address repository as configured for your system. Make sure that the entry is syntactically correct.
ORA-01652: unable to extend temp segment by string in tablespace string
For more detailed solution check the separate post: ORA-01652: unable to extend temp segment by string in tablespace string
DIM-00014: Cannot open the Windows NT Service Control Manager. What can be the possible cause for it?
While creating the oracle service on windows environment using “oradim” utility getting the following error:
  1. User access control is enable
  2. Owner which ran the command is not the owner of the oracle software.
  1. Disable user access control by:
·        Click Start, and then click Control Panel.
·        In Control Panel, click User Accounts.
·        In the User Accounts window, click User Accounts.
·        In the User Accounts tasks window, click Turn User Account Control ON or OFF
  1. Run the command prompt by logging to the owner of the software
  2. Start -> Accessories -> Right click on Command Prompt and select "Run as Administrator".
ORA-01940: cannot drop a user that is currently connected
scott> drop user shahid1;
drop user shahid1
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
scott> select sid, serial# from v$session where username = 'shahid1';
-----  --------
17         37
scott> alter system kill session '17,37;
System altered.
scott> drop user shahid1;
User dropped.
scott> select username from dba_users
where username = 'shahid1'
ORA-19705: tag value exceeds maximum length of string characters
During a backup or copy operation, the user supplied a tag value too long to fit in the file header.
Action: Supply a shorter tag and retry the operation. Maximum length for tag is of 31 characters. This error occurs while running the backup.
Solution: Supply a shorter tag in the backup script and retry the operation. 
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'
While trying to import a schema in oracle database 11g this error occurs.
Basically huge transaction may lead to this error, so it is advisable to break transaction to smaller units by using bulk collect and update.
  1. Index maintenance activity will be performed when impdp import is in progress, so disable the primary key constraint in the table, then undo generation will be less and import will be successful.
  2. In case if import is still failure after doing the above procedure then try to add some space in UNDO tablespace.


  1. Did you know that you can earn cash by locking special areas of your blog / site?
    Simply open an account on AdWorkMedia and embed their Content Locking plug-in.

  2. Searching for the Ultimate Dating Site? Create an account and find your perfect match.

  3. Bluehost is ultimately the best website hosting provider with plans for all of your hosting needs.