• Oracle backup. Oracle database backup with Bacula Enterprise. Universal backup software for PCs and servers

    Read, What tools can you use to create a backup or restore a lost Oracle Database?. Let's consider both tools built into the database and third-party applications. Oracle Database stores all files of the created database in data files. Often, to restore data from a specific database, it is enough to restore its data files and import them into Oracle Database.

    Content:

  • Oracle Database Structure


    During operation, a database instance Oracle data The Database uses several groups of files that should be archived for later recovery. This:

    So, to save, archive or backup an Oracle Database, copies of exactly the specified groups of files should be created, and this is:

    • *.DBF– data files, table spaces and database control files. Located:
      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– transaction log files:
      C:\oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG

    where, XE is the name of the database in our case.

    Oracle Database Backup

    There are two ways to back up an Oracle Database:

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

    Archiving using the operating system

    Archiving using the operating system means “manual” copying of all working database files, such as:

    • Tablespace files.
    • Control files.
    • Transaction log files.
    • Configuration files.

    IN in this case, the archiving process consists of simple copying control files, table space files, configuration files, archived transaction logs to a backup directory or to a backup server. Archiving is performed when the database instance is stopped, and users are unable to work with it.

    To restore a database damaged during a crash, it must be stopped and rewritten backups working files and transaction logs to their original location.

    Backup and restore using Export / Import tools

    Archiving and restoring an Oracle Database can be done using standard Export and Import mechanisms in Oracle. To increase the reliability of data safety, it is necessary to periodically, depending on the intensity of work with the database, perform a full export. If changes to data are made quite intensively, it is necessary to export once a week.

    To do this:


    Importing a file created earlier in the archive is carried out in the same way:

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

      Select the file to import and specify its type

    • Install the imported database


      • Recovering a Lost Oracle Database

      If the Oracle Database is deleted or lost for some reason, it can be restored by restoring files using Hetman Partition Recovery and restore them using the method described in section "Archiving using the operating system".

      To do this:


      For example, restoring database files, the process of restoring *.DBF files is described. But keep in mind that to restore all data to a working database, you also need to restore the corresponding *.ORA and *.LOG files.

      Backing up and restoring a database using Oracle Recovery Manager (RMAN)

      Oracle Recovery Manager(RMAN) is another tool for creating a backup copy of an Oracle Database. It differs from other tools in that it creates full copy the entire database, not just the data from it. And also, importantly, Oracle Recovery Manager combines the functionality of the SQL Command Line while simultaneously freeing the user from complete dependence on its commands. This tool is installed on the computer simultaneously and together with Oracle installation Database.

      To create a database backup from using Oracle Recovery Manager (RMAN):


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


      By the way, in case of loss or deletion of the Oracle Database backup file, *.BKP The backup file can also be restored using Hetman Partition Recovery, then restore in the database using the method described above using Oracle Recovery Manager (RMAN).


    • Backup and restore operations in Oracle can be divided into three types:

    1. Logical backup - performed using the exp utility included in Oracle, which allows you to export the entire database, specified schemas or tables. In the case of exporting the entire database, a so-called full export is performed (all database tables are exported) or incremental (tables that have changed since the last export are unloaded). For Oracle 10g XE, in which the database size does not exceed 4 GB, you can use full export.

    2. Physical backup - performed after installing the database and involves copying data files, control files, operational redo logs and the init.ora file with database settings.

    3. Online backup - carried out in a database operating in ARCHIVELOG mode. This mode archives operational redo logs and maintains a log of all transactions.

    For small training databases, the simplest and most reliable option is a full logical backup and a physical backup. Logical backup is performed using the exp.exe utility located in the oraclexe\app\oracle\product\10.2.0\server\BIN\ folder. The utility is a console application that receives parameters via the command line. Since there are usually many parameters (5-10 pieces), it is convenient to create a profile with parameters and then pass it to the export utility using the parfile parameter.

    Let's look at an example of typical profiles. First, let's solve the most common problem - creating a backup copy of one or more schemes. As an example, consider copying the STUDENT diagram with a training example. To do this, create a text file exp_stud.prm containing the following lines:

    USERID = username/password
    LOG = oralOstud.log FILE = oralOstud.dmp 0WNER= STUDENT

    Then we will export by executing the command exp parfile=exp_stud.prm, which will create the file ora10stud.dmp containing a backup copy of the STUDENT schema. This file has a binary format and is compressed very well by any archiver, so to automate the procedure backup It is convenient to create a BAT file containing the export command and calling the archiver to compress the resulting dump.

    In our case, the USERID parameter contains the name and password for accessing the database, the LOG parameter specifies the name of the file in which the operation log is written, the FILE parameter specifies the name of the backup file, OWNER - one or more exported schemas (if several schemas are specified, then they separated by commas).

    To perform a full export, the profile will change slightly:

    USERID = username/password
    LOG = oralOfull.log FILE = oralOfull.dmp FULL = Y

    The important point is that exporting a specific schema can be performed on behalf of its owner, but to do a full export you must have the DBA role, otherwise the attempt to do a full export will fail with error EXP-00023 with the message “Must be a DBA to do Full Database or Tablespace export " The dump size in the case of a complete export of an empty Oracle 10g XE database is 43 MB (9 MB after WinRar compression). It is strongly recommended to perform periodic backups even on a training database - there are dozens and hundreds of cases where, during the course of studying Oracle, the database is damaged, a user is deleted, or another operation leads to the loss of created objects.

    Logical import is a mirror operation to export and is performed using the IMP utility. During import, it is not necessary to import all the information available in the dump - you can import specified schemas or tables. It is convenient to place the parameters of the IMP utility in profiles; for example, to import the STUDENT schema, you can use the following profile:

    USERID = student/student LOG = oralOstudimp.log FILE = oralOstud.dmp
    ROWS = Y
    GRANTS = Y
    INDEXES = Y
    FR0MUSER= STUDENT
    T0USER= STUDENT

    The FROMUSER parameter specifies which accounts in the dump the information is taken from, and the TOUSER parameter specifies which accounts it is imported into. This is a very convenient feature of the import utility because it allows you to import data from one schema into another.

    The ROWS (table rows), GRANTS (object permissions), INDEXES (indexes) parameters indicate which types of objects are imported.

    Let's consider several typical situations encountered in practice:

    you need to import STUDENT account objects into account STUDENT1. In this case, you should set the parameters FROMUSER=STUDENT and TOUSER= STUDENT1;

    Before importing, you must remove all objects from the schema, otherwise the import process will generate errors IMP-00015 for each imported table (data will not be imported in this case). If for some reason it is necessary to load data into an existing table, then you can use the IGNORE=Y parameter. which will cause errors to be ignored when creating objects and data import to continue. However, if you use the IGNORE=Y parameter, you must take into account that in tables without primary key Duplication of records may occur (since each import operation loads new data, but the old ones are not destroyed).

    IMP has one interesting feature - instead of executing commands in the database, this utility outputs them to the log, thereby generating scripts containing DML statements. To enable this feature, you must specify the SHOW=Y parameter.

    Oracle backup must be performed efficiently and quickly, since Oracle database servers are usually used in the most loaded operations in the commercial segment. Handy Backup organizes backup of Oracle backups through standard utility Oracle backup RMAN.

    Benefits of Using Oracle RMAN Backup with Handy Backup

    A specialized Oracle backup plugin in Handy Backup acts as a graphical add-on (GUI) over standard program Backup RMAN, preserving all the functions and capabilities provided by the RMAN Backup Oracle utility.

    Backup of an Oracle database in Handy Backup can be performed according to a schedule in specified time and at specified intervals (from one minute to several months inclusive), as well as upon the occurrence of a system event (user login, connection of USB storage).

    Various techniques for backing up and restoring Oracle from a backup

    With Handy Backup, you can perform a mixed, differential or incremental Oracle backup, save multiple versions of an Oracle database backup from one source data set, encrypt or compress the resulting RMAN backup, etc.

    Storage for Oracle database backup

    Using Handy Backup, you can configure the RMAN utility to back up backups to a variety of data storage facilities, including such modern storage media as:

    • Local drives, USB storage devices, attached network drives;
    • General network folders, NAS devices, FTP servers, SFTP, FTPS, private clouds;
    • Cloud services Yandex Disk, Google Drive, Amazon S3, Dropbox, OneDrive and others;
    • Specialized secure storage HBDrive, suitable for Oracle database backups.

    Note: server solutions Handy Backup Office Expert and Handy Backup Server Network contain out of the box all available information storage plugins, allowing you to organize an Oracle database backup into the most convenient and reliable data storage for a specific task.

    Why is it convenient to use RMAN backup via Handy Backup?

    The interface and set of functions of Handy Backup allow you to use the RMAN backup utility as one of the program plugins under the control of a single interface. You can not only easily create or modify Oracle database backup tasks, but also manage them:

    • Run other applications before and after the task, effectively replacing the typical Oracle PL SQL backup script with a convenient visualized solution.
    • Receive reports on backup tasks and view work logs in the program window or by email, with customizable sending frequency.

    • Use it network solutions Handy Backup for Oracle backup on remote machines from a central administrative panel.

    How to make an Oracle backup using Handy Backup

    Here you will find brief instructions on creating a backup of an Oracle database via Handy Backup. The program takes care of all the work of interacting with the RMAN backup utility, allowing you to backup the Oracle database and restore the database from the backup automatically.

    1. to work with backup functions.
    2. Create a new task in Handy Backup using the "New task" menu or button.
    3. In Step 1, select a backup task. Click Next.
    4. In Step 2, select the Oracle plugin from the Database group in the left pane.

    1. Double click on the plugin name or click ">>".
    2. The data selection dialog will open. Check the boxes next to the databases for Oracle backup.

    1. Click OK and return to Step 2. Continue with the DB:

    • Select the appropriate storage in Step 3.
    • Choose to create a full/incremental Oracle backup in Step 4.

    • Configure compression and/or encryption in Step 5.
    • Set the operation schedule by time or by event in Step 6.
    • Run other programs, commands, or packages before and/or after the task in Step 7.
    • In Step 8, name your task and complete the New Task Wizard.

    Note: you can subsequently change any details of any task using the tab system in the task view window. Each tab corresponds to a specific step in the New Task Wizard.

    Restoring Oracle from a backup

    To restore a database from a backup for Oracle, you need to create a task automatic recovery data. Launch the New Task Wizard and use the following instructions:

    1. Select a recovery task in Step 1. Go to Step 2.
    2. Open the data warehouse, find the Oracle database backup directory, and in it the file backup.hbi.

    1. Double-click the file name or click Next.
    2. If you want to let Oracle restore the database from the backup to its original location, simply click the "Next" button until the end of the task wizard.
    3. If you want to change the restore location, click on the "Change Location" button.

    In the dialog for selecting a recovery location, specify the server to which you are going to restore Oracle from the backup. This way you can clone and replicate Oracle backups.

    Try the Oracle RMAN backup plugin in action by downloading and installing Handy Backup right now with all available features and plugins for a 30-day free trial!

    Blah, blah, blah. You always need to make backups, otherwise it will be like in the picture “He dropped the database and didn’t make backups.”

    Backups should be performed automatically, according to established rules. The administrator should intervene if something goes wrong, and not every time a backup is needed.

    Backups should be stored on a different server, preferably not in the same premises. If this is not possible, then it should be stored on some other disk other than where the database files are stored.

    Backing up Oracle databases involves backing up data files, control files, and archived log files. In addition, the spare set may include spfile, init.ora, listener.ora, and tnsnames.ora

    Backup is performed:

    • Using the operating system.
    • Using RMAN (Recovery Manager) tools.

    For centralized storage of backups large quantity databases, Oracle suggests using Oracle Catalog - another database created specifically for backups (I can’t say what is stored in it yet. I’ve never used it). For some reason I thought that backups were stored in it. But for some reason I began to doubt this.

    In addition to backups, you can export the desired scheme to a file. Then, if desired, you can also import it. This does not require any other files except the dump file itself.

    ARCHIVELOG and NOARCHIVELOG modes

    Oracle records all changes that are made to in-memory data blocks in online redo logs, usually before writing them to the database files. During the recovery process, Oracle uses the changes recorded in these log files to bring the database up to date. Oracle supports two modes for managing such files.

    • Log archiving mode (ARCHIVELOG). In this mode, Oracle saves (archives) completed redo logs. Therefore, no matter how long ago the backup was performed, if ARCHIVELOG mode is used, the database can always be restored to any point in time using archived logs.
    • Mode without archiving logs (NOARCHIVELOG). In this mode, full redo logs are overwritten rather than saved. This therefore means that if you use NOARCHIVELOG mode, you can only restore from the backup copy and that any other changes that were made to the database after the backup was performed will be lost. This mode ensures that recovery can only be performed after a database instance fails. In the event of problems with the media (for example, loss of a disk), a database operating in NOARCHIVELOG mode can only be restored from a backup copy and, of course, with the loss of all changes that were made to it after the creation of this backup copy.

    Almost all production databases operate in ARCHIVELOG mode. NOARCHIVELOG mode is used only when it is possible to restore data from other sources, or when the database is still only at the development or testing stage and therefore does not need the ability to restore its data with minute accuracy.

    Backup all or part of the database

    You can back up either the entire database or only part of it, such as a table space or data file included in it. Note that when the database is running in NOARCHIVELOG mode, you cannot perform a partial database backup, also known as a partial database backup, unless all of the table spaces and files that are being backed up are are not read-only. You can perform a full database backup, also called a whole database backup, in either ARCHIVELOG mode or NOARCHIVELOG mode.

    Most often, a full backup is performed. It involves copying not only all data files, but also one more important file- manager. Without a control file, Oracle will not open the database, so to restore, in addition to backing up all data files, you must also have a recent backup of the control file.

    Consistent and inconsistent backups

    Consistent backup creates consistent backups and does not require a restore process. When using a backup to restore a database or part of it (for example, a tablespace or data file), you usually first need to perform a restore of the data from the backup (i.e., a RESOTRE procedure), and then restore the functionality of the database (i.e. . RECOVER procedure). In the case of a consistent backup, none of these recovery steps are required. In the case of an inconsistent backup, performing these recovery steps is always mandatory.

    Oracle assigns each transaction a unique System Change Number (SCN). Each fixation, for example, will lead to an increase in this number. Whenever Oracle checkpoints, all changed data in operational file data is written to disk. And whenever this happens. Oracle is performing an update control point thread checkpoint in the control file. During this update, Orale ensures that all readable and writable data files and control files agree to the same SCN number. A database is considered consistent when the SCN numbers stored in the headers of all data files are identical and match the data file header information contained in the control files. The main thing to remember is that the same SCN number must be present in all data files and the control file (or files). The presence of an identical SCN number means that the data files contain data for the same period of time. If the data is consistent, no recovery steps will be necessary after returning (or copying) the set of backup files to their original location.

    To create a consistent backup, the database must be either shut down (using the regular SHUTDOWN or SHUTDOWN TRANSACTIONAL command, but not the SHUTDOWN ABORT command) or stopped (using the graceful shutdown command) and started again in mount mode.

    When you perform an inconsistent backup, the backup files contain data from different periods of time. The fact is that most production systems cannot be interrupted to allow for a coordinated backup. Instead, these databases need to be running 24 hours a day, 7 days a week. This therefore means that these databases must be backed up in operational mode, i.e. as long as they remain open for transactions. Changing data files by users during a backup is what results in inconsistent backups. Performing an inconsistent backup does not mean obtaining any incorrect backups. However, during recovery, simply returning such backups to their original location is not enough. In addition to returning them to their original location, you must also make sure to apply all the archived and online redo logs that were created between the time the backup was performed and the point to which you want to restore the database. Oracle will read these files and automatically apply any necessary changes to the backup files.

    Because an open database can only perform inconsistent backups, most production databases use strategies with inconsistent backup procedures at their core.

    Backup open and closed database

    Open backup, also called online backup or hot/warm backup, involves creating backup copies while the database is open and accessible to users. You can perform an online backup of an entire database (or just a table space or data file that belongs to it) only if the database is running in ARCHIVELOG mode. It cannot be carried out when the database is operating in NOARCHIVELOG mode.

    Closed database backup, also called cold backup, involves creating backup copies while the database is closed (stopped). This backup always results in consistent backups unless the database is stopped with a SHUTDOWN ABORT command.

    Physical and logical backup

    From a technical point of view, Oracle backup procedures can be divided into logical and physical. Logical backup refers to the creation of backup copies using the Data Pump Export utility that contain logical objects such as tables and procedures. These backups are stored in a special binary format and can only be retrieved using the Data Pump Import utility.

    Physical backup refers to the creation of backup copies key files Oracle databases, i.e. data files, archived redo log files, and control files. These backups can be stored on either disk or tape drives

    Backup levels

    The following are the levels at which Oracle databases can be backed up:

    • Entire database level. This level involves backing up all files, including the control file. You can perform database-wide backups in either ARCHIVELOG mode or NOARCHIVELOG mode.
    • Table space level. This level involves performing a backup of all data files belonging to a specific tablespace. Performing backups at this level is only allowed when using ARCHIVELOG mode.
    • Data file level. This level involves backing up one single data file. Performing backups at this level is only allowed when using ARCHIVELOG mode.

    Hello, dear readers of the blog site! I present to your attention an article about backup and restoration of the Oracle database. I think this material will be useful for administrators performing backups and restores on an Oracle server using the Recovery Manager (RMAN).

    Backup and Restore

    To understand the article, it is advisable to have knowledge in the following areas:

    • Concepts relational databases data and basic administration.
    • The OS environment under which the Oracle database is running.

    In fact, this article is the introductory one in a series of articles devoted to the basic concepts of backup and recovery. I recommend this material for all users responsible for backup and recovery. In general, the article will be of interest to all users, not just those using RMAN. In the future, I plan to tell you more about backup and recovery technologies using the Recovery Manager. Users managing backup and recovery without RMAN will need to familiarize themselves with the concepts in this article and subsequent backup and recovery review posts.

    Thus, the purpose of the proposed set of articles is to make general overview concepts of backup and recovery, review Oracle database files related to backup and recovery, and explore the tools available to backup and restore your database, protect against information loss and other errors.

    So, what is backup and recovery? Backup and recovery implies various strategies and procedures related to protecting your database from information loss and restoring the database after any type of data loss.

    Physical and Logical Backups

    Backup is a copy of data from your database that can be used for recovery. Backups can be divided into physical backups And logical backups.

    Physical backups are backups of physical files used to store and restore your database, such as data files, control files, log file archives. Ultimately, each physical backup is a copy of files that store database information in another location (on disk, on tape, etc.).

    Logical backups contain logical data (for example, tables or stored procedures) exported from the database using the Oracle export utility and stored as binary file– for the subsequent possibility of importing into the database using the Oracle import utility.

    Physical backups are the foundation of any reliable backup and recovery strategy, while logical backups are useful addition to physical backups in many cases, but they are not sufficient to provide satisfactory protection against information loss without physical backups.

    Further, the term “backup” in this article about backup and recovery will mean, first of all, physical backups (if it is not specified which backups we are talking about), and make a backup part or the entire database will mean making one of the types of physical backups. The emphasis in the article is mainly on physical backups.

    Errors and Failures that require Restoring from Backup

    While there are several types of problems that can interrupt normal functioning Oracle database or I/O operations, usually only two of them require administrator intervention (DBA) and media recovery: media failures themselves ( hard drives etc.), and user errors.

    Other failures may require DBA intervention to reload the database (after an instance crash) or allocate additional disk space(after a failure while executing sql sentences due to, for example, full file data), but these situations will not, generally speaking, lead to data loss and will not require restoration from a backup.

    User errors

    User errors occur either due to errors in the application logic, or when deviating from the operating instructions, the data in your database is changed or deleted incorrectly. Information loss due to user error includes mistakes such as deleting important tables or deleting or changing the contents of a table. While user training and careful privilege management can protect against most user errors, your backup strategy will determine how gracefully you recover lost data when user error causes information loss.

    Failure of storage media

    Media failure– this is a failure when reading or writing a file on the disk that is required for the database to work, due to physical problem with disk. For example, head failure. Any database file can be damaged due to media failure.

    The appropriate method for recovering from a media failure depends on what files were affected by the failure, as well as the types of backups available.

    Oracle Backup and Recovery Solutions: RMAN and Custom Backups

    To perform backup and restore based on physical backups, you have two solutions at your disposal:

    • Recovery Manager– tool (works from command line, either from GUI Enterprise Manager), which integrates with sessions running on the Oracle server to perform a number of actions related to backup and recovery, as well as maintaining a history of your backups
    • Traditional custom backup and restore(i.e. user-managed and controlled) where you directly manage the files that make up your database using OS commands and SQL*Plus backup and restore capabilities

    Both methods are supported by Oracle and are fully documented. Recovery Manager, however, is the preferred solution for database backup and recovery. It can perform the same types of backup and restore available with custom backup and restore, but in a much simpler manner, and RMAN also provides a common interface for backup tasks across different operating systems, and also offers a number of backup technologies not available with manual backup methods.

    Most of the article will focus on backup and restore using RMAN. I plan to describe custom backup and recovery methods in future articles on backup and recovery.

    Whichever method you use (RMAN or a custom method), you can supplement your physical backups with logical backups of schema objects, which are carried out through export utilities. Data saved in this way can be later imported to recreate these objects after restoring from a physical backup. However, I will not consider logical backups in this article (then I want to write a separate article about logical backups).

    Thank you for your attention! Don't miss the continuation of the topic about backup and recovery in future articles, which I will try to write in the near future. Good luck!.

    Read more: