Skip Headers
Oracle® Database Administrator's Reference
10g Release 2 (10.2) for hp OpenVMS

Part Number B25416-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

C Backing Up and Archiving the Database

This appendix describes the procedures for backing up a database. You must complete database backups periodically to be able to recover data if you have a media failure.

This chapter contains the following topics:

C.1 Archiving Redo Log Files

In case a media failure occurs, the extent of database recovery depends on whether or not you archive the redo logs and how often you back up and export the database.

See Also:

Oracle Database Administrator's Guide for more information about archiving

Information in the redo logs is always sufficient to guarantee recovery, regardless of the mode in which the logs are used. However, full media recovery is possible only if you use ARCHIVELOG mode and archive in offline files. If you use NOARCHIVELOG mode, then you must shut down Oracle Database 10g before backing up the database.

When a redo log file fills up, a checkpoint is created. Additional checkpoints can be triggered by reducing the value of the LOG_CHECKPOINT_INTERVAL parameter in the INIT.ORA file. Each checkpoint guarantees that information in the redo log file is written to the database. Frequent writes can speed recovery because there will be less data in the logs to reapply to the database.

Three initial redo logs of 100 MB each are created during installation. These initial logs are created in NOARCHIVELOG mode. You can change them to ARCHIVELOG mode with the ALTER DATABASE command. You can create additional logs with the ALTER DATABASE command. To see the current status of the log files, use the ARCHIVE LOG LIST command.

Note:

When running in Oracle RAC mode, the redo logs for all instances must be archived, or none at all. The ARCHIVELOG keyword of the ALTER DATABASE command affects the entire database, not just the current instance, and must be run only while the database is mounted in Exclusive mode.

This section contains the following topics:

C.1.1 Specifying Archive Destinations

You can archive redo log files to disk. If you want to archive redo logs to tape, then you must first archive them to disk, and then use the OpenVMS BACKUP utility to copy them from disk to tape. You should never archive directly to tape.

To specify a disk file as the archive destination, use the following conventions:

LOG_ARCHIVE_DEST   = diskname:[directory_name]
LOG_ARCHIVE_FORMAT = filename

You must specify a full file name or valid file name format using the variables. This file name is appended to the LOG_ARCHIVE_DEST string to create the archived redo log files in the specified location.

Note:

The value for LOG_ARCHIVE_FORMAT is not enclosed in single quotes on OpenVMS.

All references to LOG_ARCHIVE_DEST must be accompanied by LOG_ARCHIVE_FORMAT and the statements modified as required. For example:

LOG_ARCHIVE_DEST   = DISK$ARC:[ORACLE.V10g.ORADATA.PROD]
LOG_ARCHIVE_FORMAT = MIS_SEQ%s_SCN%c.ARC

For faster failure recovery, the following archive log naming convention is recommended:

LOG_ARCHIVE_FORMAT =  Name_THR%t_SEQ%s_SCN%c.ARC

The disk name, directory name, and prefix for the archived redo log files are specified in this destination command string. The prefix is added to the names of all the redo log files that are archived.

C.1.2 Archiving Automatically

If a database is running with ARCHIVELOG mode enabled, then the redo log files of a given instance must be archived manually or automatically. If the database is also mounted in Oracle RAC mode, then some instances can be archived manually, while others are archived automatically, as long as all instances have their redo log files archived.

To archive redo logs automatically, dedicate a disk drive without any other Oracle files for archiving the files and then complete the following steps:

  1. Shut down the current instance.

  2. Set the value of the LOG_ARCHIVE_START parameter in the INIT.ORA file to TRUE.

  3. Specify the destination of the archived files with the LOG_ARCHIVE_DEST parameter in the same parameter file (either the instance-specific INITsid.ORA file, or the INIT.ORA file itself).

  4. Restart the instance.

  5. If the database is mounted in Oracle RAC mode, and you want other instances to archive automatically, then repeat the preceding steps.

    You can skip steps 2 and 3 if you added the LOG_ARCHIVE_START and LOG_ARCHIVE_DEST parameters in the INIT.ORA file, instead of the INITsid.ORA parameter file of the current instance.

You can also enable automatic archiving for a database instance that is running in ARCHIVELOG mode without changing the INITsid.ORA file by using the SQL*Plus command ARCHIVE LOG as follows:

SQL> ARCHIVE LOG START filename 

The next time an online redo log file must be archived for the current instance, it will be archived automatically until the instance is shut down next. To archive permanently, you must set the LOG_ARCHIVE_START, LOG_ARCHIVE_DEST, and LOG_ARCHIVE_FORMAT parameters in the required parameter file, the INIT.ORA file or the setup-node_sid_INIT.ORA parameter file of the instance.

When using automatic archiving, errors that occur during archiving and start and stop of the ARCH process are written either to a trace file in the ORA_ROOT:[ADMIN.db_name.BDUMP] directory or to the alert log.

C.1.3 Archiving Manually

To archive redo log files for the current instance manually, use the ARCHIVE LOG command. You must specify the log sequence number of the redo log file group to be archived. If you do not specify the archive destination, then the destination is derived from the INIT.ORA parameter LOG_ARCHIVE_DEST.

  • To archive the first redo log, enter the following command:

    SQL> ARCHIVE LOG log_sequence_number destination 
    
    

    Replace log_sequence_number with the number of the log file you want to be archived.

  • To archive the next file, use the NEXT option as follows:

    SQL> ARCHIVE LOG NEXT destination
    
    
  • To archive all redo log files, use the ALL option as in the following command:

    SQL> ARCHIVE LOG ALL destination
    
    

When archiving manually, errors are written to the terminal.

You can also manually archive using the ARCHIVE LOG clause of the ALTER SYSTEM command. The ARCHIVE LOG clause contains all the capabilities of the ARCHIVE LOG command. You can use it to archive the log files of any instance, not just the current instance.

C.2 Backing Up the Database

A database backup is a block-by-block copy of the database files. If you are the DBA, then you should back up the database regularly, by performing the instructions in the following sections:

Both types of backup restore either all or part of the database to the same condition that existed at the time of backup. To recover any transactions committed after the backup, the DBA must use the redo logs where the transactions were recorded. If you back up files while the database is running, then use the redo log files in ARCHIVELOG mode to maintain a record of transactions occurring during the backup.

To back up database files, use the OpenVMS BACKUP utility. Follow the instructions in Oracle Database Administrator's Guide to back up both open and closed databases. When you are ready to complete the step that instructs you to perform the actual backup, run the OpenVMS BACKUP utility.

C.2.1 Backing Up a Closed Database

A backup of a closed database is also known as an offline or cold backup.

To back up a closed database, complete the following:

  1. Shut down all instances by using the SHUTDOWN NORMAL command.

  2. Run the OpenVMS BACKUP utility to copy all database files, redo log files, and control files by entering the following command:

    $ BACKUP directory:database_filename - 
    [new_directory]new_filename
    
    

    For example, if the database file is named SYSTEM01.DBF and you are copying to a directory named ARCDIR, then enter the following command:

    $ BACKUP ORA_DB:SYSTEM01.DBF DISK$2:[ARCDIR]SYSTEM01.DBF
    
    

    If you have multiple databases, or if the database files do not reside in the ORA_DB directory, then you may need to specify a directory other than the ORA_DB directory.

  3. Restart the instances.

    Note:

    You can automate much of the backup procedure through the use of scripts or the Oracle Recovery Manager RMAN.

C.2.2 Backing Up an Open Database

A backup of an open database is also known as an online or hot backup.

Backing up an open database allows users to have normal access to all online tablespaces during backup.

Caution:

Do not take the tablespace offline or shut down the system until END BACKUP is completed. The backup may not be usable.

If the following warning message is displayed during the backup procedure, then ignore it and continue with the backup:

%BACKUP-W-ACCONFLICT, is open for write by another user

To back up an open database, complete the following tasks:

  1. Run SQL*Plus, and enter the following command:

    SQL> ALTER TABLESPACE tablespace_name BEGIN BACKUP
    
    

    Specify the name of the tablespace that you want to back up. If you have not created additional tablespaces after installing the database, then you can back up only the initial tablespace SYSTEM.

    Note:

    You must perform this step before proceeding. Otherwise, the backup file created in Step 2 will be invalid for recovery purposes.
  2. Run the BACKUP utility to copy all the database files that make up the tablespace by entering the following:

    $ BACKUP/IGNORE=(INTERLOCK,NOBACKUP)- 
    ORA_DB:database_filename -
    [new_directory]new_filename
    
    

    If you have multiple databases or if the database files do not reside in the ORA_DB directory, then you may need to specify a directory location other than ORA_DB.

  3. Run SQL*Plus, and enter the following command:

    SQL> ALTER TABLESPACE tablespace_name END BACKUP;
    
    

    Note:

    The BEGIN BACKUP and END BACKUP commands are vital. Backups are unusable if these commands are not used in the preceding steps.

    Repeat Steps 1 through 3 for all tablespaces that you want to back up.

Note:

You can automate much of the backup procedure through the use of scripts or the Oracle Recovery Manager RMAN.

C.2.3 Backing Up Data Structures and Definitions

A database backup is a physical copy of a database. To copy the data structures and data definitions in a database in a logically organized format, you must use the EXPORT utility. Typically, you will need a logical copy of the database when a user has dropped a table and you want to restore only that table. An Export also permits selective recovery and allows you to transfer a single user's data or a specific set of tables. If a user accidentally drops a table, then you can recover the table from an export file. Image backups do not provide this flexibility.

Note:

IMPORT/EXPORT messages are directed to SYS$ERROR, not SYS$OUTPUT, and can be saved to a file if you use the LOGFILE option.

You can export the entire database or portions of the database. You can also perform incremental exports, which save only tables that changed since the last export. These exports are quicker and more convenient. To recover the export file generated by the EXPORT utility, use the IMPORT utility.

Note:

You can copy export files to tape if you specify a block size of 4096 bytes.

See Also:

This section contains the following topics:

C.2.3.1 Exporting to Other OpenVMS Systems

To export files to tape for transferring to another OpenVMS system, run the following commands:

$ ALLOCATE tape_device_name
$ INIT tape_device_name tape_label
$ MOUNT/BLOCKSIZE=recordlength tape_device_name tape_label
$ EXP username/password

Several prompts are displayed at this point. You must respond to these prompts as required. When prompted to supply the name of the Export file, use the following form:

EXPORT FILE:EXPDAT.DMP > : tape_device_name:EXPDAT.DMP

When the Export session ends, enter the following commands:

$ DISMOUNT tape_device_name 
$ DEALLOCATE tape_device_name 

C.2.3.2 Exporting to Non-OpenVMS Systems

To export files to tape for transfer to a non-OpenVMS system, run the following commands:

$ ALLOCATE tape_device_name 
$ INIT tape_device_name tape_label 
$ MOUNT/FOREIGN/BLOCKSIZE=recordlength tape_device_name 
$ EXP username/password

Several prompts are displayed at this point. You must respond to these prompts as required. When prompted to supply the name of the Export file, use the following form:

EXPORT FILE:EXPDAT.DMP > : tape_device_name:EXPDAT.DMP

When the Export session ends, run the following commands:

$ DISMOUNT tape_device_name 
$ DEALLOCATE tape_device_name 

Note:

If you want to create an export file and move it between systems through FTP, then you should use binary mode and set RECORDLENGTH to 512.

C.3 Exporting to and Importing from Multiple Tapes

It is a good idea to have a copy of files stored on tapes. This section describes how to export to and import from multiple tapes. It includes the following topics:

You must have the OPER privilege to perform the tasks mentioned in the following sections. Additionally, run the command REPLY/ENABLE=TAPES. This command directs the output to the terminal rather than the operator's console.

C.3.1 Exporting with Multi-Reel Files

Multi-reel export files are possible only for OpenVMS tapes. These are tapes that are not mounted with the FOREIGN option. The ANSI standard format used by OpenVMS for tapes mounted with the FOREIGN option does not define multi-reel volumes. You can usually work around this limitation of the ANSI format by using user-level or table-level exports.

C.3.2 Exporting to Multiple Tapes

To export to multiple tapes, run the following commands:

$ INIT tape_device_name tape_label 
$ MOUNT/BLOCK=4096 tape_device_name tape_label 
$ EXP username/password 

At this point the export starts, and you are prompted to enter the name of the export file as shown in the following example:

EXPORT FILE:EXPDAT.DMP > tape_device_name:filename 

The export proceeds to the end of the reel.

In the computer room where the tapes are kept, perform the following steps:

  1. Ensure that a tape drive is allocated.

    The tape rewinds and dismounts by itself. A request number and a message instructing you to mount the second tape is displayed.

  2. Make a note of the request number.

  3. Mount the next tape.

  4. Enter the following command:

    $ REPLY/TO=request_number 
    
    

    In this command, replace request_number with the request number you noted in Step 2.

Repeat this procedure as many times as required.

C.3.3 Importing from Multiple Tapes

To import from multiple tapes, the import tape label must be the same as the one for first export tape. In addition, you must have OPER privileges to perform the tasks described in this section.

To direct the output to the terminal rather than the operator's console, run the REPLY/ENABLE=TAPES command.

To import from multiple tapes, run the following commands:

$ MOUNT/BLOCK=4096 tape_device_name tape_label
$ IMP username/password

At this point, the import starts and you are prompted to enter the import file name as follows:

IMPORT FILE:  EXPDAT.DMP > tape_device_name:filename

The import proceeds to the end of the reel.

In the computer room where the tapes are kept, perform the following steps:

  1. Ensure that a tape drive is allocated.

    The tape rewinds and dismounts by itself. A request number and a message instructing you to mount the second tape is displayed.

  2. Make a note of the request number.

  3. Mount the next tape.

  4. Enter the following command:

    $ REPLY/TO=request_number 
    
    

    In this command, replace request_number with the request number you noted in the Step 2.

Caution:

Initializing the tape destroys the export.

Repeat this sequence as many times as required.

C.4 Recovering Data

If the server is interrupted by a hardware failure, an operating system error, or an unexpected process termination, then the result can be damaged files or a database that contains inconsistent data. Recovery is then needed to reconstruct the database in such a way that no committed transactions are lost and no uncommitted changes are retained.

This section describes the procedures for recovering data if media, software, or system fails. In the event of a media failure, you must complete database backups periodically to be able to recover data.

C.4.1 Overview of Data Recovery

Recovering an Oracle Database 10g database is the process of restoring normal Oracle Database 10g operations when they are interrupted by operating system error, hardware failure, or process termination. Recovery procedures should ensure that no transactions are lost and that no data is written incorrectly. Consequently, you must back up the database regularly.

The first step in recovering normal Oracle Database 10g operation is to determine the type of failure that has occurred. There are four types of failure, but only two require action:

  • Instance failure

  • Media failure

When either instance or media failure occurs, you need to complete instance or media recovery.

The other two types of failure, statement failure and process failure, result in automatic recovery.

See Also:

Oracle Database Administrator's Guide for more information about statement and process failure

Instance recovery is done automatically whenever an instance is started. It can be performed after instance failure by shutting down and then restarting the instance. Media recovery is similar to instance recovery, but requires the use of database backups or archived redo logs.

Both instance and media recovery consist of the following two tasks:

  • Rolling transactions forward, to redo work that was performed just before the failure

  • Rolling transactions backward, to undo work that was performed but not committed before the failure

See Also:

Oracle Database Administrator's Guide and Oracle Database Utilities for information about the Oracle Database 10g utilities used in recovery procedures

C.4.2 Recovering from Instance Failure

An instance has failed when the work that is run within the instance has stopped, meaning that read and write transactions are no longer being processed. Instance failure can be caused by loss of power, system malfunction, an operating system failure, or another hardware or software problem. You can diagnose instance failure by checking if one or more of the detached processes have terminated, or if work in the instance seems to be suspended.

To recover from instance failure, simply restart the failed instance to restore it to the working state that existed immediately before it failed. Whenever an instance is started, the following events occur:

  • Both committed and uncommitted transactions recorded in the redo logs are rolled forward.

  • Uncommitted transactions are rolled back.

  • All locks on Oracle Database 10g resources are released.

To restart an instance after it has failed, perform the following steps:

  1. Shut down the instance with the SHUTDOWN command. You must use either the IMMEDIATE or ABORT option with the command.

  2. Restart the instance with the STARTUP command.

When the instance is restarted, check the trace files generated in the dump directory by the detached processes. Sometimes, the failure of one or more of the detached processes causes instance failure. If possible, the problem that caused process failure should be diagnosed and corrected to avoid its recurrence.

On OpenVMS Clusters where multiple instances reside on different nodes, a failed instance will be recovered by one of the remaining functional instances within the cluster. However, you must still restart the failed instance.

C.4.3 Recovering from Media Failure

A media failure occurs when a nonrecoverable error occurs during a read or write transaction involving one or more of the database files. For example, a disk head failure that causes the loss of any one of the log files, control file, and database files associated with a particular database constitutes media failure. If you are prepared for media failure properly, then you can recover both the system tablespace data files and the non-system tablespace data files.

This section contains the following topics:

C.4.3.1 Media Recovery

Media recovery achieves the same results as instance recovery. However, because media failure usually involves loss of data in the database files, media recovery usually requires the use of database backups and archived redo logs. Consequently, you cannot complete a full media recovery automatically as these backups and archived logs are kept offline. Full media recovery requires rather extensive preparation before media failure actually occurs.

Note:

If the system is in Oracle RAC mode, then you must shut down all instances and start up only one instance in Exclusive mode to perform a media recovery operation.

If media failure occurs, then it is unlikely that any of the instances are still operational.

If you need to use an archived redo log file during any of these procedures, then use the OpenVMS BACKUP utility to copy the archived file from the archive destination. When prompted to supply the log file sequence number, provide the file specification. Provide the full specification if the location is other than the current device and directory. Wildcard characters are not accepted.

C.4.3.2 Using an Export File for Media Recovery

If you decide to import from an export file as part of media recovery, then you need to re-create the database using the SQL*Plus utility before importing the export file. The procedure is as follows:

See Also:

Oracle Database Utilities for information about using an export file for media recovery
  1. Back up the current database, redo log, and control files with the OpenVMS BACKUP utility.

  2. Refer to Oracle Database Utilities for further information.