Guide To Repair Oracle Database with RMAN, DBPITR & Flashback

Oracle is one of most trusted relational DB. It corrupts due to some reasons. In this article, we will discuss about structure of Oracle database, common errors & resolutions, and various methods to repair Oracle database.

Introduction to Oracle Database

Oracle Database is often called as RDBMS (relational database management system) introduced by Oracle Corporation. It is widely used in small as well as large organization to store, organize, and retrieve data. The application has well-defined objects that store the data which can further be manipulated and rebuilt according to a set of defined actions.

Structure of Oracle Database

The database is made up of logical and physical structure. Logical structure comprises of tablespaces, schema objects, data blocks, extents and segments. On the other hand, physical structures contains data files, control files, redo log files, archived redo log files, parameter files, alert and trace log files, and backup files. In below segments, both are described in brief. Further, Oracle logical corruption repair and physical corruption database recovery are explained.

Logical Database Structure

• Tablespaces : Database is divided into multiple tablespaces. Each tablespace creates data files to physically store data.
• Schema objects: It include structures such as tables, views, sequences, stored procedures, indexes, clusters and database links.
• Data Blocks: Specific number of bytes of physical database space on disk.
• Extents: Continuous data blocks used to store specific information.
• Segments: Set of extents allocated for a certain logical structure

Physical Database Structure

• Data Files: It contains all the database data which includes data of logical database structures (tables and index).
• Control Files: It contains entries such as database name, locations & names of data files and redo log files, and timestamp of database creation.
• Online Redo Log Files: It records all the changes made to the database. If failure occur and data become inconsistent then using these files the data can be copied to the database.
• Archive Redo Log Files: It is offline copies of online redo log files which is automatically archived if ARCHIVELOG mode is enabled.
• Alert and Trace Log Files: When an internal error occurs then information about the error get recorded in trace log files which in turn can be used to repair corrupt Oracle database.
• Backup files: It is used to replace the original file by restoring the backup file.

Most Common Oracle Database Errors & Resolution

• ORA-01092 : ORACLE instance terminated. Disconnection forced
  Cause : Improper termination of the process. It was forced to disconnect from the instance.
  Resolution : Check the alert log for the instance detail. And retry the operation.
• ORA-00235: Control file fixed table inconsistent due to concurrent update
  Cause : While updating concurrently on a control file causes inconsistent information to be read.
  Resolution : Retry the process.
• ORA-00231: Snapshot control file has not been named
  Cause : No filename for snapshot control file had been specified when Invocation of cfileMakeAndUseSnapshot or cfileUseSnapshot
  Resolution : Provide a filename for snapshot control file by calling cfileSetSnapshotName
• ORA-00257: Archiver error. Connect internal only, until freed.
  Cause : The error occurs if space is too less to store the redo log file.
  Resolution : Examine Archiver trace file for the issue. Besides, check that the device specified in the initialized parameter ARCHIVE_LOG_DEST is set up correctly.
  

Learn How to Repair Oracle Database

• SMON: SMON, short for System Monitor, is an Oracle background process which is initialized when user starts a database instance. It repairs instance, cleans up after dirty shutdown and combine adjacent free extents into one extent. SMON automatically starts every 5 minutes. If it is not running then instance may get terminated.
• RMAN: RMAN, stands for Recovery Manager, is an Oracle Database client that performs backup and recovery. It is used for backing up, restoring, and repairing Oracle database files. One can perform Oracle RMAN database recovery as entire data or individual data block.
  • Recovering Whole Database with RMAN: Execute the RESTORE DATABASE and RECOVER DATABASE commands to repair the entire Oracle database. The process restores the data from database backup.
    • Mount the database with the following command, the command will terminate the active instance.
      RMAN > STARTUP FORCE MOUNT;
    • Restore the database
      RMAN > RESTORE DATABASE;
    • Repair Oracle database corruption
      RMAN > RECOVER DATABASE;
    • Open the database
      RMAN > ALTER DATABASE OPEN;
  • Recovering Individual Data Blocks with RMAN: Individual corrupted/damaged data file blocks can easily be repaired by using RMAN commands. When RMAN scan a file for a backup, it display all corrupted blocks in V$DATABASE_BLOCK_CORRUPTION
    • Get the block numbers of the corrupted blocks. To locate trace files and the alert log, connect SQL*Plus to the target database. Execute the following command:
      SQL > SELECT NAME, VALUE
      2 FROM V$DIAG_INFO;
    • Start RMAN and connect to the target database
    • To repair Oracle database having corrupted blocks, run the following command: RMAN > RECOVER CORRUPTION LIST;
    • To repair corrupted individual blocks, execute the below command:
      RMAN > RECOVER DATAFILE 1 BLOCK 175, 250 DATAFILE 2 BLOCK 150 to 250;
• Oracle Database point-in-time recovery (DBPITR): It is mainly used to repair unwanted database changes by restoring the database from backup which was created before target time. Thereafter, incremental backups and redo are used to roll forward the Oracle database to the target time. DBPITR is also referred as incomplete recovery because it does not deploy all the redo and does not repair all the unwanted database changes.
  
• Oracle Database Flashback Recovery: It allows you to rewind data back in time to rectify logical data issues and user errors within a flashback window. Unlike DBPITR, this feature can repair Oracle database without having to restore the backup. It provides the best alternative to Database point-in-time recovery. To access Flashback database, run the below command:
  FLASHBACK DATABASE TO RESTORE POINT ‹before-upgrade›;
  FLASHBACK DATABASE TO SCN 202379;

Conclusion

The above article mainly focuses on different methods (SMON, RMAN, DBITR, Flashback Recovery) to repair Oracle database corruption. Besides, its physical and logical structure, common errors, and resolution have been elaborated.