MyPage is a personalized page based on your interests.The page is customized to help you to find content that matters you the most.


I'm not curious

Oracle Database Data: Backup and Restore

Published on 28 January 18
0
0

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.

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:

    Oracle Database Data: Backup and Restore - Image 1

    Oracle Database Data: Backup and Restore - Image 2
  • 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

    Oracle Database Data: Backup and Restore - Image 3
  • 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.

    Oracle Database Data: Backup and Restore - Image 4

    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’;

    Oracle Database Data: Backup and Restore - Image 5
  • 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:

    Oracle Database Data: Backup and Restore - Image 6

    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:

    Oracle Database Data: Backup and Restore - Image 7
  • 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

    Oracle Database Data: Backup and Restore - Image 8

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 Database Data: Backup and Restore - Image 9
  • Specify the export type: the entire workspace or one of its components

    Oracle Database Data: Backup and Restore - Image 10
  • Set the file format to export the data into, and click on Export Workspace (it’s on the right)

    Oracle Database Data: Backup and Restore - Image 11
  • 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 Database Data: Backup and Restore - Image 12
  • Select the file to import and specify its type

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

    Oracle Database Data: Backup and Restore - Image 14

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».

To do it:

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

    Oracle Database Data: Backup and Restore - Image 15
  • Wait until the process is finished and use the program to go to the folder where the necessary Oracle Database files are located.

    Oracle Database Data: Backup and Restore - Image 16
  • Recover them

    Oracle Database Data: Backup and Restore - Image 17
  • 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

    Oracle Database Data: Backup and Restore - Image 18

    or select Backup Database among other applications in the Start menu

    Oracle Database Data: Backup and Restore - Image 19
  • Wait until the backup by RMAN tool is complete

    Oracle Database Data: Backup and Restore - Image 20
  • 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

    Oracle Database Data: Backup and Restore - Image 21

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

    Oracle Database Data: Backup and Restore - Image 22

    or select Restore Database among other applications in the Start menu

    Oracle Database Data: Backup and Restore - Image 23
  • Wait until restore operation from a previously created backup by RMAN tool is complete

    Oracle Database Data: Backup and Restore - Image 24

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).

Oracle Database Data: Backup and Restore - Image 25
Source: https://hetmanrecovery.com/recovery_news/backing-up-and-restoring-the-database-oracle-database.htm
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.

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:

    Oracle Database Data: Backup and Restore - Image 1

    Oracle Database Data: Backup and Restore - Image 2
  • 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

    Oracle Database Data: Backup and Restore - Image 3
  • 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.

    Oracle Database Data: Backup and Restore - Image 4
    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’;

    Oracle Database Data: Backup and Restore - Image 5
  • 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:

    Oracle Database Data: Backup and Restore - Image 6
    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:

    Oracle Database Data: Backup and Restore - Image 7
  • 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

    Oracle Database Data: Backup and Restore - Image 8
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 Database Data: Backup and Restore - Image 9
  • Specify the export type: the entire workspace or one of its components

    Oracle Database Data: Backup and Restore - Image 10
  • Set the file format to export the data into, and click on Export Workspace (it’s on the right)

    Oracle Database Data: Backup and Restore - Image 11
  • 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 Database Data: Backup and Restore - Image 12
  • Select the file to import and specify its type

    Oracle Database Data: Backup and Restore - Image 13

  • Install the imported database

    Oracle Database Data: Backup and Restore - Image 14
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».

To do it:

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

    Oracle Database Data: Backup and Restore - Image 15
  • Wait until the process is finished and use the program to go to the folder where the necessary Oracle Database files are located.

    Oracle Database Data: Backup and Restore - Image 16
  • Recover them

    Oracle Database Data: Backup and Restore - Image 17
  • 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

    Oracle Database Data: Backup and Restore - Image 18
    or select Backup Database among other applications in the Start menu

    Oracle Database Data: Backup and Restore - Image 19
  • Wait until the backup by RMAN tool is complete

    Oracle Database Data: Backup and Restore - Image 20
  • 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

    Oracle Database Data: Backup and Restore - Image 21
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

    Oracle Database Data: Backup and Restore - Image 22
    or select Restore Database among other applications in the Start menu

    Oracle Database Data: Backup and Restore - Image 23
  • Wait until restore operation from a previously created backup by RMAN tool is complete

    Oracle Database Data: Backup and Restore - Image 24
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).

Oracle Database Data: Backup and Restore - Image 25

Source: https://hetmanrecovery.com/recovery_news/backing-up-and-restoring-the-database-oracle-database.htm

This blog is listed under Development & Implementations and Data & Information Management Community

Post a Comment

Please notify me the replies via email.

Important:
  • We hope the conversations that take place on MyTechLogy.com will be constructive and thought-provoking.
  • To ensure the quality of the discussion, our moderators may review/edit the comments for clarity and relevance.
  • Comments that are promotional, mean-spirited, or off-topic may be deleted per the moderators' judgment.
You may also be interested in
 
Awards & Accolades for MyTechLogy
Winner of
REDHERRING
Top 100 Asia
Finalist at SiTF Awards 2014 under the category Best Social & Community Product
Finalist at HR Vendor of the Year 2015 Awards under the category Best Learning Management System
Finalist at HR Vendor of the Year 2015 Awards under the category Best Talent Management Software
Hidden Image Url