6
Backing Up and Archiving Your Database

If the server is interrupted by a hardware failure, an operating system error, or an unexpected process termination, 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 chapter describes the procedures for backing up the database. You must complete database backups periodically to be able to recover data if you have a media failure.

This chapter contains the following major sections:

Archiving Redo Log Files

How much of the database you can recover if media failure occurs depends upon whether you archive the redo logs and how often you back up and export the database. Refer to the Oracle8 Server Administrator's Guide for more information on archiving.

Information in the redo logs is always sufficient to guarantee instance 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, be sure to shut down Oracle8 before backing up the database.

When a redo log file has filled, a checkpoint occurs. Additional checkpoints can be triggered by reducing the value of the INIT.ORA parameter LOG_CHECKPOINT_INTERVAL. 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.

Two initial redo logs of 2000 Kb each are created during the installation procedure; you can create additional logs with the ALTER DATABASE command. These initial logs are created in NOARCHIVELOG mode; you can change them to ARCHIVELOG mode with the ALTER DATABASE command. To see the current status of your log files, use the command ARCHIVE LOG LIST. Refer to the Oracle8 Server Administrator's Guide for more information.

Note:
When running in parallel 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 only be issued while the database is mounted in exclusive mode.

Specifying Archive Destinations

You can archive redo log files to disk. If you wish to archive redo logs to tape, you must first archive them to disk, and then use the Alpha OpenVMS BACKUP utility to copy them from disk to tape. You should never archive directly to tape. Refer to Compaq's document VMS Guide to Tapes and Devices, and the Oracle8 Server Administrator's Guide for more information.

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 appropriately. For example:
LOG_ARCHIVE_DEST = DISK$ARC:[ORACLE.V8.DB_MIS] LOG_ARCHIVE_FORMAT = MIS_SEQ%s_SCN%c.ARC

For faster crash 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 all the redo log files names that are archived.

Archiving Automatically

If a database is running with ARCHIVELOG mode enabled, the redo log files of a given instance must be archived manually or automatically. If the database is also mounted in parallel mode, 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 your files and then complete the following steps:

Shut down the current instance.
Set the value of the LOG_ARCHIVE_START parameter in the INIT.ORA file to TRUE.
Specify the destination of the archived files with the LOG_ARCHIVE_DEST parameter in the same parameter file (either the instance-specific INIT.ORA file, or INIT.ORA itself).
Restart the instance.
If the database is mounted in parallel mode, and you want other instances to archive automatically, repeat the steps above, skipping step2, if you added the LOG_ARCHIVE_START and LOG_ARCHIVE_DEST parameters to INIT.ORA (rather than the current instance's <setup_node>_<sid>_INIT.ORA parameter file).

You can also enable automatic archiving for a database instance that is running in ARCHIVELOG mode without changing INIT.ORA by using the Server Manager command ARCHIVE LOG as in the following command:

SVRMGR> ARCHIVE LOG START <filename>

The next time an online redo log file needs to be archived for the current instance, it will be archived automatically until the instance is next shut down. To make archiving permanent, you must set the LOG_ARCHIVE_START, LOG_ARCHIVE_DEST, and LOG_ARCHIVE_FORMAT parameters in the appropriate parameter file (INIT.ORA or the instance's <setup_node>_<sid>_INIT.ORA parameter file).

When using automatic archiving, errors that occur during archiving and start and stop times of the ARCH process are written to a trace file in the ORA_DUMP directory.

Archiving Manually

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

To archive the first redo log, enter the following command:
```
SVRMGR> 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 to be archived, use the NEXT option as in the following command:
```
SVRMGR> ARCHIVE LOG NEXT <destination>  
```
To archive all redo log files, use the ALL option as in the following command:
```
SVRMGR> ARCHIVE LOG ALL <destination>  
```

When archiving manually, errors are written to your 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.

Backing Up the Database

A database backup is a block-by-block copy of the database files. If you are the DBA, you should make backups of the database regularly. You can do one of the following:

Backing Up a Closed Database (offline or cold backup)
Backing Up an Open Database (online or hot backup)

Both types of backup will restore either all or part of the database to the same condition that existed at the time of backup. To restore any transactions committed after the backup, the DBA must use the redo logs where those transactions were recorded. If you back up files while the database is running, 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 Alpha OpenVMS utility BACKUP. The Oracle8 Server Administrator's Guide describes the steps for backing 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.

Backing Up a Closed Database

To back up a closed database, complete the following:

Shutdown all instances using the SHUTDOWN NORMAL command.
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 your database file is named ORA_SYSTEM.DBS and you are copying to a directory named ARCDIR you would enter the following:

$ BACKUP ORA_DB:ORA_SYSTEM.DBS  - 								
		DISK$2:[ARCDIR]ORA_SYSTEM.DBS

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

Restart the instances.

Attention:
You can automate much of the backup procedure through the use of scripts.
See the file ORA_RDBMS:READMEVMS.DOC for information about accessing sample scripts.

Backing Up an Open Database

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

Note:
Do not take the tablespace offline or shut down your system until END BACKUP is completed; the backup might not be useable. If the following warning message occurs during the backup procedure, 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:

Run Server Manager, and enter the following command:

SVRMGR> 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, you can only back up the initial tablespace SYSTEM.

Note:
You must perform this step before proceeding, or else the backup file created in step 2 will be invalid for recovery.

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 your database files do not reside in the ORA_DB directory, you might need to specify a directory location other than ORA_DB.

Run Server Manager and enter the following command:

SVRMGR> ALTER TABLESPACE <tablespace_name> END BACKUP;

Note:
The BEGIN BACKUP and END BACKUP are vital. Your backups will be corrupted if these commands are not used in the steps listed above.

Repeat steps 1 - 3 for all tablespaces you want to back up.

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. Normally, you will need a logical copy of the database when a user has dropped a table and you want to restore only that table. Exports also allow selective recovery and let you transfer a single user's data or a specific set of tables. If a user accidentally drops a table, you can recover the table from an export. Image backups do not provide this flexibility.

Note:
Import/Export messages go to SYS$ERROR, not SYS$OUTPUT and can be saved to 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 restore the export file generated by the Export utility, use the Import utility. For information about using these utilities, refer to the Oracle8 Server Utilities.

Note that under Alpha OpenVMS, you can copy export files to tape if you specify a block size of 4096 bytes.

Exporting to OpenVMS Machines

To export files to tape for transfer to an OpenVMS machine, use the following procedure:

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

Several prompts appear at this point; respond as appropriate. 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 has completed, enter the following commands:

$ DISMOUNT <tape_device_name> 
$ DEALLOCATE <tape_device_name>

Exporting to Non-OpenVMS Machines

To export files to tape for transfer to a non-OpenVMS machine, enter 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 appear at this point; respond as appropriate. 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 has completed, enter the following commands:

$ DISMOUNT <tape_device_name> 
$ DEALLOCATE <tape_device_name>

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

Exporting to and Importing from Multiple Tapes

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

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

Exporting with Multi-Reel Files

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

Exporting to Multiple Tapes

To export to multiple tapes, enter 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 export filename as 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:

Make sure a tape drive is allocated.
The tape rewinds and dismounts by itself.
A message flashes onto the operator's terminal instructing to mount the second tape. A request number is provided.
The operator mounts the next tape and enters the following statement:
```
$ REPLY/TO=<request_number>  
```

Repeat this sequence as many times as necessary.

Importing from Multiple Tapes

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

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

To import from multiple tapes, enter 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 filename as in the following example:

Import file:  EXPDAT.DMP > <tape_device_name>:<filename>

The import proceeds to the end of the reel.