🎡There may be a situation where a sensitive database gets us into trouble due to disk block corruption or lack of a recoverable backup.
That is why as a database administrator, it is at the forefront of all tasks to ensure that the backup is correct and usable.
Alireza Kamrani
03/March/2025
🚧🚑 So, if this critical situation occurs and we do not have a backup, what methods do you know?
These are sometimes cases for which some People demand a lot of money from the companies involved in the problem.
Despite how effective these methods can be useful in retrieving healthy data in critical situations,
Today, I want to mention a few of these techniques. In fact, suppose that due to lack of backup, we are looking for a tool that can recover at least part of our data.
🧱On Linux systems, there are tools and methods that can help read Oracle datafiles and attempt to recover healthy blocks into a new datafile without relying on RMAN. These methods usually involve working at the raw file system level to extract the available data, bypassing the Oracle database's normal recovery mechanisms.
Here are some Linux-based tools and techniques that can help you achieve this:
1. Use PRM-DUL (Data Unloader)
As previously mentioned, PRM-DUL (Data Unloader) is a tool specifically designed to extract data from Oracle datafiles, even if the Oracle instance cannot be started or if a datafile is corrupted.
• How it works: PRM-DUL reads Oracle's raw database files (i.e., .dbf files) and attempts to extract all healthy blocks, skipping over corrupted ones.
• Key Features:
• Works without RMAN backups.
• Can handle corrupted or missing datafiles.
• Supports extraction of data from raw Oracle database files.
• Works on Linux and other platforms.
• Usage: You would point the tool to your Oracle datafiles, and it would attempt to recover healthy blocks and write them to a new file.
Official Website: PRM-DUL
2. Use dd Command for Raw Data Access (Manual Method)
On Linux, you can use the dd command to perform low-level operations on raw datafiles. This command can be helpful when you want to copy Oracle datafiles block-by-block, and it may allow you to isolate healthy blocks from a corrupted datafile.
• How it works: The dd command copies raw bytes from one file to another. You can use this to copy a corrupted .dbf file to a new file, potentially skipping the corrupted blocks manually by using block offsets and seeking specific areas of the file.
Example usage:
dd if=/path_to_corrupted_datafile of=/path_to_new_datafile bs=8192 seek=0 count=1000
• if=: Input file (source corrupted datafile).
• of=: Output file (destination where healthy blocks will be copied).
• bs=: Block size (typically 8192 bytes for Oracle datafiles).
• seek=: Specifies the number of blocks to skip in the output file.
• count=: Specifies the number of blocks to copy.
You can use this technique to copy blocks manually from the corrupted datafile into a new one. It’s a bit rudimentary and requires identifying which blocks are healthy, but it may help extract usable data.
3. Use hexedit or xxd to Inspect Datafiles
If you suspect that the corruption is localized to specific sections of the datafile and you want to inspect the raw contents, you can use tools like hexedit or xxd to view the datafile in hexadecimal format.
• hexedit: A simple hex editor to view the contents of binary files.
hexedit /path_to_datafile
• xxd: A command-line hex dump utility.
xxd /path_to_datafile
You can search through the raw data in the file to see where the corruption might be and manually extract the healthy blocks. This is a tedious and difficult process, but it can sometimes help identify healthy data in an otherwise corrupt file.
4. Manual Recovery with Oracle Tools (DBMS_REPAIR, Offline Recovery)
While you may not be using RMAN, Oracle's DBMS_REPAIR package can help identify and manage corrupted objects at the block level. You can mark corrupt blocks as "bad" and work with Oracle to extract the rest of the data.
• Steps:
• Identify the corrupt blocks using the DBMS_REPAIR package.
• Mark corrupt objects or blocks so Oracle avoids them.
• Extract healthy data using traditional export/import methods or Data Pump.
• Optionally, recreate the tablespace or restore the tables if the corruption is localized.
This requires that the Oracle instance is still running and accessible, but it allows you to bypass the corrupted parts and extract healthy data.
5. Oracle Datafile Recovery with dbv (Database Verification)
Oracle provides the DBV (Database Verification) utility, which can be used to scan datafiles for corruption. This tool does not perform recovery but can help identify blocks that are corrupt.
• How to use it: dbv FILE=/path_to_datafile
While DBV does not perform recovery, it can give you detailed information about the extent of corruption and identify corrupt blocks, which may help in manual recovery processes (like using dd or PRM-DUL).
6. Use Third-Party Tools (e.g., Stellar Repair for Oracle)
There are several third-party tools that can scan corrupted Oracle datafiles and attempt to recover the healthy blocks into new datafiles. These tools typically work by reading the raw Oracle datafiles and bypassing the corrupted blocks, much like PRM-DUL.
• Stellar Repair for Oracle: A tool that can repair corrupted datafiles and export the healthy blocks to new files. It provides an easy-to-use interface and automated recovery.
• DataNumen Oracle Recovery: Another third-party tool that supports recovery of corrupted or deleted datafiles.
These tools often work by extracting valid blocks and creating new datafiles for Oracle, making them an excellent option when RMAN backups are not available.
7. Using Oracle SQL*Plus to Perform Recovery (If Applicable)
If the datafile is not completely lost and the corruption is minor, SQL*Plus can be used to perform basic recovery by clearing corrupted segments or tablespaces. If the corruption is isolated to specific tables, you can drop and recreate them, then reimport data if necessary.
8. Use Oracle Flashback Technology (If Enabled)
If Oracle Flashback is enabled on your database, you may be able to flashback to a point in time before the corruption occurred, recovering the datafile to an earlier state. This may not recover the deleted or corrupted blocks but can help if the corruption was recent.
FLASHBACK DATABASE TO SCN <scn_number>;
Flashback allows you to restore the database to a state where the datafile was not corrupted, although it may not help with block-level corruption.
🚒Summary
While RMAN is the typical solution for datafile corruption recovery, here are some Linux tools and methods that can help recover healthy blocks from Oracle datafiles without RMAN:
• PRM-DUL (Data Unloader) – A third-party tool that can extract healthy data from corrupted or deleted Oracle datafiles.
• dd Command – Low-level data copying tool that can be used to manually recover healthy blocks from corrupted datafiles.
• hexedit / xxd – Hex editors that can be used to inspect raw datafile contents for manual block recovery.
• DBMS_REPAIR – Oracle's built-in tool for managing corrupted objects and blocks within a database.
• dbv (Database Verification) – Oracle tool for identifying corrupt blocks in datafiles.
• Third-Party Tools – Tools like Stellar Repair for Oracle or DataNumen Oracle Recovery that can automatically recover healthy blocks into new files.
• Oracle Flashback – If enabled, Flashback can help restore the database to a prior point in time before the corruption occurred.
Each of these methods requires specific technical expertise and tools, so the approach will depend on the level of corruption and available resources.
🏛And of course, I don't need to say that having multiple backups in safe locations, as well as maintaining log archives after each backup and using Rman, is the best and most useful method for recovery in critical situations.🚒
Alireza Kamrani
No comments:
Post a Comment