In cancel based recuperation process, it prompts you with suggested filename of redo log files of archive. Recovery ends when you press cancel button after when all methods have been applied to data files.
Cancel-based recovery should be performed at following stages:
- Recover database by maintaining back-up.
- Re-establish data files of back-up and if you have current control file, then don’t bring back backup control file.
- Media recovery should be performed on restored database as shown below.
To perform cancel-based recovery:
- Launch SQL*Plus as an administrator and connect to Oracle.
- Begin new illustration and accumulate database
- Start cancel-based recuperation by issuing following command;
“RECOVER DATABASE UNTIL CANCEL”
- Important redo log files are applied by Oracle for reconstructing datafiles. Suspected name is supplied by Oracle and it anticipates locating LOG_ARCHIVE_DEST_1 and makes request either to proceed or to stop.
- Till the last log will be applied to recovered database keep applying the redo log files, then cancel recovery procedure with command “CANCEL”.
If you do not receive any recovery message and you try to open Oracle database then you will notice an error ORA-1113.
- Access your database using RESETLOGS mode. You must always go through the generated online logs after every unfinished recovery or backup control file recovery.
Protecting an Oracle database from block corruption
Block corruption errors in Oracle database can only be detected with the help of dbv, RMAN, analyze or export command etc. You can also proactively monitor the block corruptions in Oracle Database via RMAN and repair these block corruptions using RMAN by applying healthy RMAN or any updated backup. If you want to repair block corruption by applying user managed backup then this process will require to be cataloged in oracle database use catalog datafilecopy ‘datafile_path_name‘
Causes of Block Corruption
Below some causes of Block corruptions are given, but this type of corruption is not limited to the given causes:
- Faulty hard disks and disk controllers-A faulty or error full hard drive can generate block corruption in a healthy Oracle database.
- Faulty memory-Sometimes due to faulty system memory it is seen that oracle database gets damaged or corrupted.
- Bugged network components-Network components are also responsible in making oracle database full of errors. A corrupt or damaged Network component can make the entire database damaged.
- Internal Defects in Oracle Database software-Due to any internal defect in Oracle database software a healthy database gets faulty and start displaying error.
Along with the above-given causes, there are lots of other reasons available that can affect Oracle database and make it damaged. Some other causes are defective firmware, damaged Volume manager, error-full operating system, Defected NFS or any third party software. A healthy database can also be turned into the contaminated database because of operator errors such as copying backups on existing data files or restoring conflicting database backups.
How to identify Corrupt blocks:
With the help of given trick you can identify block corruption:
- The alert log
- Error messages
- Trace files
- With the help of ANALYZE [TABLE | INDEX] commands
- Use dbverify utility
- The V$BACKUP_CORRUPTION & V$COPY_CORRUPTION views list damaged blocks in the backups, not the database itself.
- The V$DATABASE_BLOCK_CORRUPTION lists damaged blocks in the database identified a range of RMAN operations.
Top 10 Reasons for poor Oracle performance
Here top 10 causes are given that are commonly responsible for poor performance of Oracle database:
Poor server optimization A poor server kernel parameters setting and I/O configuration (e.g. direct I/O) settings may leave bad impact on Oracle performance
Bad Design Poor designing or poor normalization of Oracle database are the main crooks of poor performance of Oracle.
Bad disk I/O configuration Wrong use of RAID5, disk channel bottlenecks, and deprived disk striping may also affect its performance.
Poor Optimizer Statistics Previous version of Oracle 10g (automatic statistics), lost/stale CBO statistics and unavailable histograms are general reasons of poor performance
Objects Contention DML performance can be affected by unable to set ASSM, free list or freelist_groups for DML-active tables and indexes.
Under-allocated RAM regions In case, if you are not providing enough RAM for shared_pool_size, pga_aggregate_target and db_cache_size may cause an I/O-bound database.
Non-reentrant SQL All SQL must employ host variables/cursor_sharing=force to make SQL re-usable inside the records cache.
Un-set initialization parameters There are lots of the initialization parameters are created to be set by the DBA (db_file_multiblock_read_count, optimizer_index_caching) and gets unable to set these parameters correctly results in poorly optimized execution plans.
Excessive nested loop joinsGenerally in 64-bit Oracle systems gigabytes available for RAM sorts and hash joins. Unable to set pga_aggregate_target to permit the CBO to select hash joins can slow down the SQL performance.
Human Misfeasance The DBA’s breakdown to observe their database (STATSPACK/AWR), set-up exemption reporting alerts (OEM) and altering their occurrence to match-altering workloads is a major reason of deprived performance.
Most Common Oracle Errors
There are various types of errors display by the oracle database when it get damaged, some of them are given below:
- ORA-00510: internal limit restriction exceeded
- ORA-00600: internal error
- ORA-00604: error occurred at recursive SQL level string
- ORA-00903: invalid table name
- ORA-00904: invalid column name
- ORA-00396: error string required fallback to single-pass recovery
- ORA-00381: cannot use both new and old parameters for buffer cache size specification
- ORA-00321: ORACLE_HOME Redo1.log
- ORA-00313: open failed for members of log group string of thread string
- ORA-00235: controlfile fixed table inconsistent due to concurrent update
- ORA-03114: not connected to ORACLE
- ORA-04043: object string does not exist
- ORA-04052: error occurred when looking up remote object name
- ORA-04076: invalid NEW or OLD specification
- ORA-04098: trigger ‘SYSTEM.LOG_ERRORS_TRIG’ is invalid and failed re-validation
- ORA-06508: PL/SQL: could not find program unit being called
- ORA-06532: Subscript outside of limit
- ORA-06550: line string, column string:string
- ORA-06554: package DBMS_STANDARD must be created before using PL/SQL
- ORA-07445: exception encountered: core dump 
- ORA-10902: disable seghdr conversion for ro operation
- ORA-12154: TNS-12154: TNS:could not resolve service name
- ORA-12203: TNS-12203: TNS:unable to connect to destination
- ORA-03113: end-of-file on communication channel (#5)
- ORA-01000: maximum open cursors exceeded (#23)
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 and recover 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 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.