Oracle Database Data: Backup and Restore

Read the article to learn what tools can help you back up or restore a lost Oracle Database – considering both built-in utilities and third-party software. Oracle Database stores all files of a created database in data files. Despite all data being logically kept in tablespaces, in fact it is the contents of files on a computer’s hard disk. For example, every database table is stored as lines in a specific data file. Often a certain database can be recovered by just restoring its data files and importing them into Oracle Database.

Go to view
How to Recover MySQL, MSSql and Oracle Databases ⚕️👨‍💻🖥️

How to Recover MySQL, MSSql and Oracle Databases ⚕️👨‍💻🖥️

Database structure in Oracle Database

In the course of its work, Oracle Database uses several groups of files which should be archived (backed up) for further recovery. They are as follows:

  • Data and tablespace files (*.DBF).

    Names of data and tablespace files as well as the paths to them can be viewed with SQL Plus by executing the following query:

    SELECT t.name “Tablespace”, f.name “Datafile” FROM v$tablespace t, v$datafile f WHERE t.ts# = f.ts# ORDER BY t.name;

    As a result of such query, a detailed report will be generated:

    SQL Command Line
    *.DBF files
  • Database configuration files (*.ora).

    Oracle database configuration files have the extension *.ora and can be found in this folder:
    C:\oraclexe\app\oracle\product\11.2.0\server\dbs

    *.ora files
  • Database control files (*.DBF).

    The easiest way to determine the path to, and the names of control files is to find in the configuration file *.ORA the line control_files, where the control files used by this database will be listed.

    configuration file *.ORA

    Also, you should execute this query in SQL Plus to determine the names of, and paths to the control files:

    SELECT value FROM v$parameter WHERE name = ‘control_files’;

    SQL Command Line. SELECT value FROM v$parameter WHERE name = 'control_files'
  • Transactions log files (*.LOG).

    To learn the names of online transactions logs and paths to them, you should execute this query in SQL Plus:

    SELECT member FROM v$logfile;

    As a result of such query, a detailed report will be generated:

    SQL Command Line. SELECT member FROM v$logfile

    To determine the paths to folders where archive transactions logs are stored, you should execute this query:

    SELECT destination FROM v$archive_dest where status=’VALID’;

    As a result of such query, a report will be generated:

    SQL Command Line. SELECT destination FROM v$archive_dest where status=’VALID'
  • Password files (*.ora).

    As a rule, these are files with the extension *.ora, their names beginning with PWD symbols.
    example: PWDXE.ora

    Path C:\oraclexe\app\oracle\product\11.2.0\server\database

    *.ora Password files

So, to save, archive or backup an Oracle Database file, copies of the abovementioned groups of files should be created, namely:

  • *.DBF – data files, tablespace files and database control files. Their location:
    C:\oraclexe\app\oracle\oradata\XE
  • *.ora – database configuration files and password files.
    Configuration files:
    C:\oraclexe\app\oracle\product\11.2.0\server\dbs
    Password files (PW…ora):
    C:\oraclexe\app\oracle\product\11.2.0\server\database
  • *.LOG – transactions log files:
    C:\oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG

where ХЕ is the database name, in our case.

Creating a backup copy of Oracle Database

A database backup of Oracle Database can be created in two ways:

  • With the means of the operating system.
  • With the built-in tools of Oracle Application Express – Import / Export.

Archiving with the means of the operating system

Archiving with the means of the operating system suggests manual copying of all working database files such as:

  • Tablespace files.
  • Control files.
  • Transactions log files.
  • Configuration files.

In this case, the process of archiving consists is mere copying of the control files, tablespace files, configuration files and archived transactions log files into a backup directory or a backup server. Archiving is performed with the stopped database, and users cannot work with it then.

To restore a database damaged because of a failure, it should be stopped, and backup copies of working files and transaction logs should be written to their previous location.

Archiving and restoring with the tools Export / Import

In Oracle Database, you can archive (backup) and restore a database with the help of Oracle standard tools – Export and Import. To improve data safety, you should perform a complete export operation regularly, depending on how often you work with the database. If changes to the data are made frequently, you should export the database once a week.

To do it:

  • Open Oracle Application Express and select the menu Application Builder / Export

    Oracle Application Express. Application Builder / Export
  • Specify the export type: the entire workspace or one of its components

    Oracle Application Express. Specify the export type
  • Set the file format to export the data into, and click on Export Workspace (it’s on the right)

    Oracle Application Express. Export Workspace
  • After you specify the location where the data export file will be saved, it is saved into a SQL file.

Similarly, you can import a file or a previously created archive:

  • Open Oracle Application Express and select the menu Application Builder / Import

    Oracle Application Express. Application Builder / Import
  • Select the file to import and specify its type

    Oracle Database Data: Backup and Restore
  • Install the imported database

    Oracle Application Express. Select the file to import and specify its type

Restoring a lost database in Oracle Database

If a database from Oracle Database is deleted or lost for any reason, it can be restored by recovering its files with Hetman Partition Recovery and then restore them as described in the part «Archiving with the means of the operating system».

The tool recovers data from any devices, regardless of the cause of data loss.

To do it:

  • Run Hetman Partition Recovery and scan the disk where the database was stored

    Hetman Partition Recovery. Type of analysis
  • Wait until the process is finished and use the program to go to the folder where the necessary Oracle Database files are located.

    Hetman Partition Recovery. Result of analysis
  • Recover them

    Hetman Partition Recovery. Recovery
  • Replace the existing Oracle Database files with the recovered ones.

As an example of recovering database files, here is how *.DBF files can be recovered. However, take into account that to recover all data in an operable base you should also recover the corresponding *.ORA and *.LOG files.

Database backup and restore with Oracle Recovery Manager (RMAN)

Oracle Recovery Manager (RMAN) is one more tool for creating a backup database copy in Oracle Database. It differs from other tools by its ability to create an entire copy of the database rather than copy only the data from there. What is more important, Oracle Recovery Manager combines the functions of SQL Command Line while making the user independent from its commands. This tool is installed at the same time as Oracle Database.

To create a database backup copy with Oracle Recovery Manager (RMAN):

  • Run the file Backup.bat in the folder
    C:\oraclexe\app\oracle\product\11.2.0\server\bin

    Run the file Backup.bat

    or select Backup Database among other applications in the Start menu

    Select Backup Database among other applications in the Start menu
  • Wait until the backup by RMAN tool is complete

    cmd. RMAN tool
  • As a result, a backup file with the extension *.BKP will be created in the folder with the name which is the date of creating the backup file

    backup file .BKP

To restore a database from a backup copy with Oracle Recovery Manager (RMAN):

  • Run the file Restore.bat in the folder
    C:\oraclexe\app\oracle\product\11.2.0\server\bin

    Run the file Restore.bat

    or select Restore Database among other applications in the Start menu

    Select Restore Database among other applications in the Start menu
  • Wait until restore operation from a previously created backup by RMAN tool is complete

    cmd. RMAN tool

By the way, in case of losing or deleting a backup file of Oracle Database, the *.BKP backup file can also be recovered with Hetman Partition Recovery, and then restored, as we have shown above, with the use of Oracle Recovery Manager (RMAN).

Hetman Partition Recovery
Vladimir Artiukh

Author: , Technical Writer

Vladimir Artiukh is a technical writer for Hetman Software, as well as the voice and face of their English-speaking YouTube channel, Hetman Software: Data Recovery for Windows. He handles tutorials, how-tos, and detailed reviews on how the company’s tools work with all kinds of data storage devices.

Oleg Afonin

Editor: , Technical Writer

Oleg Afonin is an expert in mobile forensics, data recovery and computer systems. He often attends large data security conferences, and writes several blogs for such resources as xaker.ru, Elcomsoft and Habr. In addition to his online activities, Oleg’s articles are also published in professional magazines. Also, Oleg Afonin is the co-author of a well-known book, Mobile Forensics - Advanced Investigative Strategies.

Recommended For You

Hello! This is AI-based Hetman Software virtual assistant, and it will answer any of your questions right away.
Start Chat