C Backing Up and Archiving the Database

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.

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

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

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

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.

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.

This section contains the following topics:

• Section C.2.3.1, "Exporting to Other OpenVMS Systems"

• Section C.2.3.2, "Exporting to Non-OpenVMS Systems"

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

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

$ DISMOUNT tape_device_name 
$ DEALLOCATE tape_device_name 

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

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

$ DISMOUNT tape_device_name 
$ DEALLOCATE tape_device_name 

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.

Repeat this sequence as many times as required.

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.

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

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:

• Section C.4.3.1, "Media Recovery"

• Section C.4.3.2, "Using an Export File for Media Recovery"