Restoring the oracle database manually from a hot backup
- Use backup location to copy table space files along with the control file back-up and paste it to the directory of database example.
- Rename it as CONTROL01.CTL.
Note- If you have one more copy of control file with name CONTROL02.CTL, then another copy and rename as CONTROL02.CTL
- Don’t restore or copy REDO logs.
- Re-launch database in mount mode
Query: startup mount;
- Use following method to recover your database
Query: Recover database until cancel using backup controlfile;
Note- oracle system will propose an ‘archive log file name’ that could be used for recovery, if you have made copy of backup logs to similar site which can used for database log storage, and keep hitting enter meanwhile. Or assign complete path to log file.
- After applying all logs that you utilized for backup, then mark cancel on prompt and hit enter.
- The logs of transaction have been applied by far. Execute the subsequent inquiry to access the transactions database.
Query: Alter database open resetlogs;
Restoration is complete.
Configuring Oracle Database Block-Corruption Parameters
To identify or detect block corruption in Oracle Database it provides two instance parameters that can help you to detect block corruptions:
With the help of this block-corruption parameter, one can easily recognize any damage or harm caused by a problem present in the disk subsystem. Every data block contains an equivalent header that includes a value also called as a checksum. Via this checksum you can detect block corruption. DB_BLOCK_CHECKSUM parameter calculates a checksum for the available block that is to be written to the disk and after it, the calculated value of this checksum is matched with that contained in the block header.
Through this parameter, you can confirm the consistency of a data block each time it is called from the buffer in the memory. This may acquire major performance overhead up to 10%.
You can also use ‘DB_LOST_WRITE_PROTECT’ parameter in the manner to make easily lost write detection. A lost write occurs when the I/O subsystem suppose that the write is finished, but certainly the write never completed in real. You can set this parameter to ‘TYPICAL’, the read-write tablespaces in the redo log are inspected by the instance logs buffer cache to spot lost writes.
In the manner to achieve the best configuration set these parameters to the following:
DB_BLOCK_CHECKING=FULL or MEDIUM
Difference between logical and physical corruption
In Oracle Database, there are mainly two types of corruption physical and logical. There are lots of difference between both the errors. Generally, Oracle Corruption will not affect your work until you try to fetch the damaged block, to know more you must have to read the difference between logical and physical Corruption:
1- Physical Corruption (Also known as Media Corruption)
2- Logical Corruption (Also known as Soft Corruption)
Physical corruption generally occurs because of I/O Problems, Memory Failure, Server Controller Regarding to Oracle documentation the Oracle corruption could occur by:
- Bad header: the start of the block (cache header) is damaged with invalid values.
- Block is Fractured/Incomplete: Fetched information from the block header doesn’t match the block tail
- Invalid Block checksum
- Misplaced or lost Block
- Zeroed out blocks / ORA-8103
You can apply DB_BLOCK_CHECKSUM parameter to recognize if the block was modified by any external thing. This parameter is very simple to execute, it determines a checksum for the obtainable block that is to be written to the disk and after it, it match the calculated value of this checksum with that stored in the block header.
This type of corruption occurs when the block contains a valid checksum but the structure below the starting of the block is damaged. It may reason diverse ORA-600 errors.
You cannot print the complete corruption descriptions for Logical Corruptions in the alert.log. To know what is logically damaged in the block DBVerify is used.
Some examples of Corruption are:
- Row protected by non-existent transaction – ORA-600 ,etc
- The Amount of space used and block size is not equal
- avsp bad
In case you enable the db_block_checking then it may give the internal errors ORA-600 [kddummy_blkchk] or ORA-600 [kdBlkCheckError].
Oracle Database Corruption; Identify and Correct
We know that corruption can occur because of numerous different reasons such as hardware failure, software error, user error and more. Below some method are given to properly identify and rescue usable data.
Detect and Correct Corruption in Oracle
With the help of DBVerify utility you can validate the offline and online data files, it is an external utility. Along with validation you can also verify the validity of backup data files.
C:\>dbv file=C:\Oracle\oradata\TSH1\system01.dbf feedback=10000 blocksize=8192
But you can’t use this utility for controlfiles or redo logs.
ANALYZE .. VALIDATE STRUCTURE
This command can be used to confirm each data block in the examined object. If it found any corruption then rows are added to the INVALID_ROWS table.
— Create the INVALID_ROWS tableSQL> @C:\Oracle\901\rdbms\admin\UTLVALID.SQL — Validate the table structure.SQL> ANALYZE TABLE scott.emp VALIDATE STRUCTURE; — Validate the table structure along with all it’s indexes.SQL> ANALYZE TABLE scott.emp VALIDATE STRUCTURE CASCADE; — Validate the index structure.SQL> ANALYZE INDEX scott.pk_emp VALIDATE STRUCTURE;
When you set the parameters of DB_BLOCK_CHECKING to [TRUE|HIGH] then Oracle performs a walkthrough of the data in the block to verify it is self-consistent. Unluckily this verification can add between 1 and 10% overhead to the server. Other allowable values are [OFF|FALSE], LOW, MEDIUM, [HIGH|TRUE].
RMAN (BACKUP VALIDATE, RESTORE VALIDATE, VALIDATE)
RMAN or Oracle Recovery Manager can used to validate the database using the BACKUP VALIDATE command.
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
The process gives the outputs as same information you would see while a backup, but no backup is created. Unfortunately this command only checks for physical corruption. You have to add the CHECK LOGICAL clause to add checks for logical corruption.
Block Media Recovery (BMR)
BMR or Block Media Recovery allows particular blocks to be restored without affecting the entire data file.
About Oracle Repair and recovery Application
A professional Oracle Repair and recovery tool is the best way to get back your inaccessible data from an error showing or corrupt oracle database. This recovery application is also suggested by lots of professional because it is easy to use and effective solution to deal with a completely corrupt Oracle Database. This tool is designed with strong algorithm and has effective scanning and recovery technology. This recovery tool provides a complete assurance of zero percent data loss on restoring your data. It repairs damaged or corrupt. DBF file and restores your important data with ease.
This tool searches the entire Oracle databases that present in the computer system, it also display the preview of recoverable database objects after scanning corrupt file. You can also do selective recovery of DBF components. This recovery tool is compatible with Windows 7, Vista, XP, 2008, 2003 and 2000. If you don’t have deep knowledge about oracle database but want to restore data from corrupt Oracle 11g Database then this recovery application is best for you, its simple user interface allows the normal user to operate it and get back their data. You also don’t need backup of your database to repair damaged oracle database. So, it is recommended to try this recovery application once and get back your precious data.
Key features of Oracle Database Repair Tool
Selectively Restores Components of Oracle Database
You can recover particular database object from an entire database. Simply checkmark and restore them from the corrupt database.
Automatically Generates Log of Entire Recovery Process
It also creates a log file after finishing the repairing process, the generated log file by this DBF repair software contains the report of the entire recovery process. It stores all the informative data, such as process start date, end date, time, records and objects restored in the process.
“Find Items” Option to Search Objects in a Large Database
If you want to recover an object from a large database then this software become useful for you, use ‘Find Items’ option which helps in searching any specific object easily.
Get Back Queries of Database Components in a Text File
You can also recover the queries of views, procedures, triggers, functions, package, package body, and database links in text files.
See the Previews of Database Components before Recovery
This Oracle Recovery scans the damaged DBF file and displays all the recoverable objects in a tree structure. If you want to see the preview of any object then select any component from the tree, the preview will appear right-hand side of the screen.
Recover Oracle Database Objects & DBF File
This tool has been programmed to restore the unreachable or damaged Oracle database file (.DBF) and thus helps in restoring the Tables, Tablespaces, Nested Object Types, Nested Tables, Nested Array Types, User Names (schemas), Materialized Views, Triggers, Roles, Various privileges for Roles, Indexes, Stored procedures, Clusters, User-defined functions, Queries of Database Links, Packages, Package Body etc.
Steps to use Oracle file repair tool to repair and restore Oracle dmp file
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.
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.
Step 3: Click an object to see its preview.
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.
Step 5: Give the user name, password and path of the blank database where you want to save the repaired database objects.
Step 6: Repairing and restoring various database objects after establishing a connection with blank oracle database.
Some tricks to secure your oracle database!
Trick to Secure User accounts
- Strictly limit the SYSTEM and OBJECT privileges.
- Avoid giving needless access to database users.
- Limit the number of users who can create, alter or delete database object.
- Provide CREATE ANY JOB, EXP_FULL_DATABASE and BECOME USER privilege only to the needy users.
- The CREATE PUBLIC SYNONYM and DROP PUBLIC SYNONYM are provided only to the trusted users.
Tricks to Secure Data
- Defend the data dictionary because any alteration is done in it, then it can harshly affect the database performance.
- Protect data dictionary by the help of, set 07_DICTIONARY_ACCESSIBILITY = FALSE.
- Always set admin password on your system.
- Provide the path or file to the database carefully to avoid any modification from anyone.
- Files and their path accessibility must be given only to DBA.
Tricks to Secure Roles
- Make global roles for enterprise users.
- Programmers must not be given user roles.
- Categorize roles as per to the user job accountability.
Tricks to Secure the Network
- Always use Firewall, it will help in avoiding database from external access.
- Set the firewall to recognize data only from trusted or safe sources.
- Utilize Enterprise Manager Database Control at the time monitoring listener action.
- Avoid setting password in the listener.ora file.
- Pass up Oracle listener from illegal access.