Oracle® Database Administrator's Reference 10g Release 2 (10.2) for hp OpenVMS Part Number B25416-02 |
|
|
View PDF |
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:
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 archivingInformation 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. TheARCHIVELOG
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:
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.
See Also:
Hewlett-Packard's document OpenVMS Guide to Tapes and Devices and Oracle Database Administrator's GuideTo 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 forLOG_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.
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:
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
sid
.ORA
file, or the INIT.ORA
file itself).
Restart the instance.
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 INIT
sid
.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 INIT
sid
.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.
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.
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.
A backup of a closed database is also known as an offline or cold backup.
To back up a closed database, complete the following:
Shut down all instances by 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 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.
Restart the instances.
Note:
You can automate much of the backup procedure through the use of scripts or the Oracle Recovery Manager RMAN.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:
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.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
.
Run SQL*Plus, and enter the following command:
SQL> ALTER TABLESPACE tablespace_name END BACKUP;
Note:
TheBEGIN 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.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 toSYS$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:
OpenVMS Database Utilities for information about using the EXPORT and IMPORT utilities
Oracle Database Recovery Manager Reference or the Oracle Database Backup and Recovery Basics for information about RMAN for backup
This section contains the following topics:
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
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 setRECORDLENGTH
to 512.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.
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.
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:
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.
Make a note of the request number.
Mount the next tape.
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.
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:
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.
Make a note of the request number.
Mount the next tape.
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.
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.
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 failureInstance 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 proceduresAn 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:
Shut down the instance with the SHUTDOWN
command. You must use either the IMMEDIATE
or ABORT
option with the 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.
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:
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.
See Also:
Oracle Database Administrator's GuideNote:
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.
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:
Back up the current database, redo log, and control files with the OpenVMS BACKUP
utility.
Refer to Oracle Database Utilities for further information.