Oracle Database Table is mutating, trigger/function may not see it! : Oracle Database File Repair Tool

Published by Jacob Martin on November 29, 2013

Table is the most important part of the Oracle database. This database works as a RDBMS (Relational Database Management System) and to relate the stored information with each other table is used. In a table all entered information are stored and relate with each other. And if table of any database gets corrupted then all the stored data gets inaccessible or unreachable.

Oracle database has a different trigger concept as MS SQL Server, it has the set based approach. All the rows that are exaggerated by the data alteration like update, insert, delete are stored in the deleted and stored tables. A regular trigger DML is used in MS SQL, it regularly executes after the statement. Before image is stored in the deleted table and after image stored in the inserted table and both are accessed within the trigger. You can also join the deleted and inserted table and used them to update the table.

Recursive trigger error and mutating table error are related in the logic that they are invoked when the table on which the trigger has been call up is customized or accessed by the similar trigger but should not be perplexed with each other. When user doing modification on the similar table in a per statement trigger so that trigger fire recursively again and recursion error occurs. Mutating error occurs when user trying to alter or access the same table in a per row trigger.

Reasons of corruption:

Corruption in oracle database could be either physical or logical and it may occur due to any common problems or errors they are

• Because of hardware error.
• Due to Hardware failure.
• Due to any malware attack.
• Hardware or software malfunctioning can also affect the oracle database.
• Due to improper system shutdown.
• If you close the database when any task is running.
• Unwanted system shut down.

Error Messages Displayed by Oracle Database after Dorruption:

• ORA-00701: object necessary for warmstarting database cannot be altered
• SQL> alter index i_col2 rebuild online; alter index i_col2 rebuild online
• ORA-00701: object necessary for warmstarting database cannot be altered
• ORA-00604: error occurred at recursive SQL level 1
• ORA-01578: ORACLE data block corrupted (file # 1, block # 83848)
• ORA-01110: data file 1: ‘/u01/oracle/oradata/system01.dbf’

How to Repair and Recover?

To repair and recover database corruption oracle provides an inbuilt tool called RMAN, it helps us to detect and repair the corruption. The main task of this inbuilt tool is validation to check for missing data and corrupt blocks. It provides many commands to perform validation like VALIDATE, BACKUP … VALIDATE, RESTORE … VALIDATE etc. RMAN tool can do many tasks such as

Limit of RMAN

This tool are only applicable when corruption is minor, this tool is for the small database it has its own capacity to detect and recover corrupted data and this tool is unable to repair large database. To fix the total number of unmarked corruption acceptable in a file for RMAN backups user can apply “SET MAXCORRUPT” command. And the default is set to zero it means that RMAN doesn’t tolerate unmarked corrupt blocks of any type. No doubt that this tool is very useful for the small corruption but it fails when corruption is bigger or the size of the database is very large.

To fix any big corruption or to repair any large database you can use any third party repair tool because these kinds of toll is very effective and able to repair and recover oracle database without the loss of any data and trigger. If your database or table of the database gets corrupted and display any error message then you can apply these types of tool to repair and recover the corrupted data.

These types of tools are also very efficient and easy to use because it provides very simple use interface so that it doesn’t require to typing any commands on the command line. These tools are designed with very strong program but with simple visual user interface so that it is for any kinds of user. These tools are easily available on the internet download any one and repair or recover your corrupted database. So download oracle database repair tool and apply it to recover your corrupted database table.