How to Fix Oracle Database ORA-29913 Error?

By admin News

Published by Jacob Martin on July 31, 2014

oracle_logo-260x300-7251658 The use of External Tables in Oracle is becoming popular these days which has moreover has eliminated the need of SQL loader for loading tables. However you may come across many issues while using the external tables in your database one of which is described below:

When you perform the SELECT operation on the external table (feature is a complement to existing SQL*Loader functionality. It enables you to access data in external sources as if it were in a table in the database) then you may face error those which are similar to the following:

“ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04063: unable to open log file EXTERN_17063.log KUP-04040: file TEST.TXT in TEST_DIR not found

ORA-06512: at “SYS.ORACLE_LOADER”, line 19

”

The most common ORA-29913 error is caused when the Oracle fails to open the OS files that it needs for opening. These files may or may not include in the input files, output files and the log and bad or discard files. The reason behind the occurrence of this error is that Oracle execution has not been granted the permission for accessing the directory properly or the directory itself does not exists in the database or thee files has not been granted the permissions.

You may also come across this error line that the Oracle fails to write the log file then this definitely means that there may be an issue with the directory or with the log files contained in the directory.

So when you make use of the external tables then you need to perform certain checks that are mentioned below:

You need to check the statement carefully that you have used for creating the directory.
You need to check that the directories have been granted the necessary rights or permissions by the Oracle for performing reading and writing.
You even need to perform special checks of ownership and permissions for the network directories.
You also need to check that there is no problem associated with the permissions for the files that are in existence.

You can easily fix this problem by trying out some of the solutions that are follows:

You need to build physical directories at all nodes of cluster and need to copy the data files physically to all the locations.
When you present the storage of data from SAN or NAS then it gets presented in NFS. You also note that this is mandatory to get a separate license from the storage provider.
You can use the Cluster file system at OS level which is visible from all the nodes. However the solution to do so is bit expensive as you need to purchase some additional license to do so.
When you are accessing to the external tables then you need to connect to specific instances but you will be not able to take the advantage of this service.

This is why you need to fix this issue. However whatever solution you use to perform this is needed to be carried out carefully and efficiently that does not lead to further Oracle database issues. In such a situation Oracle Repair Tool can be highly helpful to overcome such a situation. It is powerful and comprehensive data recovery software that is especially designed for recovering corrupted or damaged Oracle database file and the tables and the contents in it that are exported from the external source. This software scans and searches all the errors and damages that are induced in the Oracle database in the system and then shows a preview of recoverable database objects. This software is capable of recovering the from both the database and backup files. Hence the recovered data after going through the process is also gets stored in Transact-SQL script. The recovery tool is very easy to use an even the non-technical people can easily make use of it for recovering their data back from their corrupted and damaged Oracle database.

Steps to Fix Oracle Database ORA-29913 Error

Step 1: Search Initial screen of Stellar Phoenix Oracle Recovery with a pop-up window showing options to select or search corrupt Oracle databases in your computer. 1-4581174

Step 2: Click Scan File to initiate the scan process after selecting the oracle database. The recoverable database objects get listed in left-side pane.

2-6078594

Step 3: Click an object to see its preview.

3-9934433

Step 4: : Click Start Repair in the icon bar to start the repair process. A pop-up window is displayed which show the steps needed to perform further. Click next and continue.

4-1351210