How to handle corruption in Oracle Database using DBVERIFY

If you have encountered Oracle database corruption problems then you can use 'DBVERIFY' command-line utility to fix corruption. DBVERIFY performs a physical data structure integrity check to find corruption in oracle database. It is an external command-line utility that can be use on offline or online databases. We can only DBVERIFY with datafiles, it is not work against control files. Here are some examples of common Oracle Database Corruption Error messages & Solutions:  

• The file does not exist or was incorrectly specified. In this case you should first check that the specified file exists.
• The datafile to be verified must end with ‘.dbf’. In this case, you should try renaming the file  
• The file is in use. In this case, you should shutdown Oracle and try again.
• If the database gets sudden shutdown then in this case you should try to stop the Oracle service ‘OracleService<SID>.

These are some general solutions which may work or not. Through DBVERIFY utility, you can fix most of the corruption error messages & can also prevent and manage block corruption.

How it works: DBVERIFY works in to two command-line interfaces: 

1. In the first interface, you specify disk blocks of a single datafile for checking: In this interface, DBVERIFY scans single datafile disk blocks & then performs page checks.

Syntax: 

The mean of all Parameters in this diagram are: 

USERID	As you can also see in the diagram that USERID=username+password. It is only necessary if the files to verify are Oracle ASM files.
FILE	The database file name to verify.
START	The starting block address to verify.
END	The ending blocks address to verify.
BLOCKSIZE	It is only required only if the file block size to be verified is not have 2 KB.
HIGH_SCN	This parameter is optional.
LOGFILE	Specifies the name of file to which logging information should be written.
FEEDBACK	Causes DBVERIFY to send a progress display to the terminal.
HELP	It provides online help.
PARFILE	Specifies the parameter file name to use.

Sample use of DBVERIFY Command-Line Interface for this mode: 

% dbv FILE=t_db1.dbf FEEDBACK=100

2. In the second interface, you specify a segment for checking: In this interface, DBVERIFY specify a table or index segment for verification. Through this check, it make sure that a row chain pointer is within the segment being verified.

Syntax:

 All parameters are same meaning except SEGMENT_ID. 

SEGMENT_ID

Specifies the segment to verify.

Sample use of DBVERIFY Command-Line Interface for this mode: 

dbv USERID=username/password SEGMENT_ID=tsn.segfile.segblock

Sample DBVERIFY Output for both mode: 

•Total Pages Examined– The number of blocks.

•Total Pages Processed (Data)–The number of blocks that contains table data.

•Total Pages Failing (Data) – The number of table blocks that have corruption.

•Total Pages Processed (Index)-The number of blocks inspected that contains index data.

•Total Pages Failing (Index) – The number of index blocks that are corrupted.

•Total Pages Processed (Seg) – The command to specify a segment that spans multiple files.

•Total Pages Failing (Seg) – The number of segment data blocks that are corrupted.

•Total Pages Empty– Number of unused blocks discovered in the file.

•Total Pages Marked Corrupt– It shows the number of corrupt blocks discovered during the scan.

•Total Pages Influx– The number of pages that were re-read due to the page being in use.  

If the corruption is not fixed by this utility then, the file gets severely corrupted. For severely corrupted database, you should follow steps in this given article.

»»  Read More...

How to resolve Error ORA-01079

Image via Wikipedia

I was about to mount system database but suddenly I got an error message on PC screen this stuck my mind for a minute. The error message was look like it:

 

ORA-01079 Oracle database was not properly created, operation aborted

 

Then I have decided to read about the error on Oracle official site & oracle database related forums. This is the information which I found through oracle official site & forums:

ORA-01079:

ORACLE database was not properly created, operation aborted Cause: There was an error when the database or control file was created. Action: recreate the database or a new control file.

Now it was clear that the error is related to database or controlfile creation. As the solution mention, I have to recreate controlfile or database. The query for recreating controlfile is as follows:

 

create controlfile database name-of-database

 

When you recreated controlfile then you can not open database only way You have to create new database. But you can not recover your old database.

 

There is an alternative way to the CREATE CONTROLFILE statement is ALTER DATABASE BACKUP CONTROLFILE TO TRACE. It generates a SQL script in the trace file to re-create controlfile. If the database contains read-only or temporary tablespaces, then that SQL script will also contain all the necessary SQL statements to add those files back into the oracle database. So this helps you to recreating controlfile, also helps to restore old database information.

 

But this command also has a some shortcomings like it can recover only read-only or temporary tablespaces. It is not able to add large files back into the database. So if your data is important for you & you want to recover it in any cost, you should use Oracle Recovery Software.

 

This software resolves most of the oracle database corruption errors & recover your data in few clicks. This software recovers most of components of oracle database.

»»  Read More...

Recover Oracle database with the safest ever utility

Among many available database applications of the recent time, Oracle is the most widely used relational database application. Many leading organizations as well as individual users use Oracle to store their business critical data. But, sometimes, while working on Oracle, we encounter error messages, which result in the inaccessibility of the data in the database. The loss or inaccessibility of the Oracle database can cause huge loss in terms of money, effort and business. For such situations, when your failed to resolve any typical Oracle error, you can take the help of any Oracle recovery software to recover your data back.

You were working on a project with Oracle spatial. When you fired a distributed query, Oracle displayed an error message and the connection to the database disconnected. The error message that you received may be written as below:

ORA-03113: end-of-file on communication channel

Cause:

The prime cause behind the above error message is that the communication path in between the Client and Server has broken. Though, this is a communication error, it still needs further investigation. You can also more information on the error by using the 'oerr utility'.

This sudden Oracle error may signal several things with a single note. It may be a network problem, crashing of the server system, Oracle internal errors etc. Apart from this, the reason can be the killing of the server process by the operating system, aborting server processes or incorrect handling of multiple connections.

Resolution:

• Check out the Client-Server network connection, whether that is working or not.
• If everything is alright with the hardware settings, check the 'alert.log' file any error logs.
• If you failed to get any clue out of the above two, see whether the server process is dead. Check for any clue in the generated trace file and follow the appropriate command.

If you failed to resolve the issue and all your data in the Oracle database is still inaccessible, the best possible way of getting them back is to restore them from the backup server. If you have taken backup of your data and there is a valid backup available, you can easily restore the database. However, if you do not have any recent backup or failed in the restoration process, you have to take the help of any good Oracle database recovery utility.
 

Stellar Phoenix Oracle Recovery is a powerful dbf recovery software, which can help you retrieve all your valuable data back under almost any instance or Oracle database trouble. Moreover, the software is completely risk-free and absolutely easy-to-use.

»»  Read More...