Release 2 (9.2)
Part Number A96572-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i User-Managed Backup and Recovery Guide Release 2 (9.2) Part Number A96572-01 |
|
If you do not use Recovery Manager (RMAN), then you can make backups of your database files using user-managed methods.
This chapter contains the following sections:
Before making a backup, identify all the files in your database. Then, ascertain what you need to back up.
This section contains these topics:
Before beginning a backup, query the database to determine which files you should back up. Note that backups of Oracle Managed Files are not different from backups of database files that you name manually.
To list datafiles, online redo logs, and control files:
V$DATAFILE to obtain a list of datafiles. For example, enter:
SQL> SELECT NAME FROM V$DATAFILE;
You can also join the V$TABLESPACE and V$DATAFILE views to obtain a listing of datafiles along with their associated tablespaces:
SELECT t.NAME "Tablespace", f.NAME "Datafile" FROM V$TABLESPACE t, V$DATAFILE f WHERE t.TS# = f.TS# ORDER BY t.NAME;
V$LOGFILE view. For example, issue the following query:
SQL> SELECT MEMBER FROM V$LOGFILE;
V$CONTROLFILE view. For example, issue the following query:
SQL> SELECT NAME FROM V$CONTROLFILE;
Note that you only need to back up one copy of a multiplexed control file.
ALTER DATABASE BACKUP CONTROLFILE TO 'filename' statement, then save a list of all datafiles and online redo log files with the control file backup. Because the current database structure may not match the database structure at the time a given control file backup was created, saving a list of files recorded in the backup control file can aid the recovery procedure.To check whether a datafile is part of a current online tablespace backup, query the V$BACKUP view. This view is useful only for user-managed online tablespace backups, not offline tablespace backups or RMAN backups.
The V$BACKUP view is most useful when the database is open. It is also useful immediately after an instance failure because it shows the backup status of the files at the time of the failure. Use this information to determine whether you have left any tablespaces in backup mode.
V$BACKUP is not useful if the control file currently in use is a restored backup or a new control file created after the media failure occurred. A restored or re-created control file does not contain the information Oracle needs to fill V$BACKUP accurately. Also, if you have restored a backup of a file, this file's STATUS in V$BACKUP reflects the backup status of the older version of the file, not the most current version. Thus, this view can contain misleading data about restored files.
For example, the following query displays which datafiles are currently included in a tablespace that has been placed in backup mode:
SELECT t.name AS "TB_NAME", d.file# as "DF#", d.name AS "DF_NAME", b.status FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b WHERE d.TS#=t.TS# AND b.FILE#=d.FILE# AND b.STATUS='ACTIVE' /
Sample output follows:
TB_NAME DF# DF_NAME STATUS ---------------------- ---------- ---------------------- ------------------ TBS_1 3 /oracle/dbs/tbs_11.f ACTIVE TBS_1 4 /oracle/dbs/tbs_12.f ACTIVE
In the STATUS column, NOT ACTIVE indicates that the file is not currently in backup mode (that is, ALTER TABLESPACE ... BEGIN BACKUP), whereas ACTIVE indicates that the file is currently in backup mode.
You can make a whole database backup of all files in a database after the database has been shut down with the NORMAL, IMMEDIATE, or TRANSACTIONAL options. A whole database backup taken while the database is open or after an instance failure or SHUTDOWN ABORT is inconsistent. In such cases, the files are inconsistent with respect to the checkpoint SCN.
You can make a whole database backup if a database is operating in either ARCHIVELOG or NOARCHIVELOG mode. If you run the database in NOARCHIVELOG mode, however, the backup must be consistent; that is, you must shut down the database cleanly before the backup.
The set of backup files that results from a consistent whole database backup is consistent because all files are checkpointed to the same SCN. You can restore the consistent database backup without performing recovery. After restoring the backup files, you can perform additional recovery steps to recover the database to a more current time if the database is operated in ARCHIVELOG mode. Also, you can take inconsistent whole database backups if your database is in ARCHIVELOG mode.
Control files play a crucial role in database restore and recovery. For databases running in ARCHIVELOG mode, Oracle recommends that you back up control files with the ALTER DATABASE BACKUP CONTROLFILE TO 'filename' statement. If you back up the control file with an operating system utility during a closed, consistent whole database backup, then you should only use this control file when restoring the other datafiles taken in the backup. Although a control file backed up with an operating system utility during a consistent backup can sometimes be used for recovery (but only if you specify the USING BACKUP CONTROLFILE clause of the RECOVER statement), Oracle does not recommend this practice because neglecting to specify the USING BACKUP CONTROLFILE clause can cause recovery problems.
| See Also:
"Making User-Managed Backups of the Control File" for more information about backing up control files |
To guarantee that a database's datafiles are consistent, shut down the database with the NORMAL, IMMEDIATE, or TRANSACTIONAL options before making a whole database backup.
To make a consistent whole database backup:
NORMAL, IMMEDIATE, or TRANSACTIONAL options. For example, do one of the following:
SQL> SHUTDOWN NORMAL SQL> SHUTDOWN IMMEDIATE SQL> SHUTDOWN TRANSACTIONAL
Do not make a whole database backup when the instance is aborted or stopped because of a failure. If possible, reopen the database and shut it down cleanly.
CONTROL_FILES parameter of the initialization parameter file. Also, back up the initialization parameter file and other Oracle product initialization files. To find these files, do a search for *.ora starting in your Oracle home directory and recursively search all of its subdirectories.
For example, you can back up the datafiles and control files in the /disk1/oracle/dbs directory to /disk2/backup as follows:
% cp /disk1/oracle/dbs/*.dbf /disk2/backup % cp /disk1/oracle/dbs/*.cf /disk2/backup % cp /disk1/oracle/network/admin/*.ora /disk2/backup % cp /disk1/oracle/rdbms/admin/*.ora /disk2/backup
SQL> STARTUP
| See Also:
Oracle9i Database Administrator's Guide for more information on starting up and shutting down a database |
You can back up all or some of the datafiles of an individual tablespace while the tablespace is offline. All other tablespaces of the database can remain open and available for systemwide use. You must have the DBA privilege or have the MANAGE TABLESPACE system privilege to take tablespaces offline and online.
Note the following guidelines when backing up offline tablespaces:
SYSTEM tablespace or a tablespace with active rollback segments. The following procedure cannot be used for such tablespaces.Primary and its index is in tablespace Index. Taking tablespace Index offline while leaving tablespace Primary online can cause errors when DML is issued against the indexed tables located in Primary. The problem only manifests when the access method chosen by the optimizer needs to access the indexes in the Index tablespace.To back up offline tablespaces:
DBA_DATA_FILES view. For example, assume that you want to back up the users tablespace. Enter the following in SQL*Plus:
SELECT TABLESPACE_NAME, FILE_NAME FROM SYS.DBA_DATA_FILES WHERE TABLESPACE_NAME = 'users'; TABLESPACE_NAME FILE_NAME ------------------------------- ------------------- users /oracle/dbs/users.f
In this example, /oracle/dbs/users.f is a fully specified filename corresponding to the datafile in the users tablespace.
users offline normally:
SQL> ALTER TABLESPACE users OFFLINE NORMAL;
After you take a tablespace offline with normal priority, all datafiles of the tablespace are closed.
users.f:
% cp /disk1/oracle/dbs/users.f /disk2/backup/users.backup
users back online:
ALTER TABLESPACE users ONLINE;
|
Note: If you took the tablespace offline using temporary or immediate priority, then you cannot bring the tablespace online unless you perform tablespace recovery. |
After you bring a tablespace online, it is open and available for use.
ALTER SYSTEM ARCHIVE LOG CURRENT;
You can back up all or only specific datafiles of an online tablespace while the database is open. The procedure differs depending on whether the online tablespace is read/write or read-only.
This section contains these topics:
You must put a read/write tablespace in backup mode to make user-managed datafile backups when the tablespace is online and the database is open. The ALTER TABLESPACE BEGIN BACKUP statement places a tablespace in backup mode.
Oracle stops recording checkpoints to the datafiles in the tablespace when a tablespace is in backup mode. Because a block can be partially updated at the very moment that the operating system backup utility is copying it, Oracle copies whole changed data blocks into the redo stream while in backup mode. After you take the tablespace out of backup mode with the ALTER TABLESPACE ... END BACKUP or ALTER DATABASE END BACKUP statement, Oracle advances the datafile header to the current database checkpoint.
When you restore a datafile backed up in this way, the datafile header has a record of the most recent datafile checkpoint that occurred before the online tablespace backup, not any that occurred during it. As a result, Oracle asks for the appropriate set of redo log files to apply should recovery be needed. The redo logs contain all changes required to recover the datafiles and make them consistent.
To back up online read/write tablespaces in an open database:
DBA_DATA_FILES data dictionary view. For example, assume that you want to back up the users tablespace. Enter the following:
SELECT TABLESPACE_NAME, FILE_NAME FROM SYS.DBA_DATA_FILES WHERE TABLESPACE_NAME = 'users'; TABLESPACE_NAME FILE_NAME ------------------------------- -------------------- USERS /oracle/dbs/tbs_21.f USERS /oracle/dbs/tbs_22.f
In this example, /oracle/dbs/tbs_21.f and /oracle/dbs/tbs_22.f are fully specified filenames corresponding to the datafiles of the users tablespace.
users:
SQL> ALTER TABLESPACE users BEGIN BACKUP;
% cp /oracle/dbs/tbs_21.f /oracle/backup/tbs_21.backup % cp /oracle/dbs/tbs_22.f /oracle/backup/tbs_22.backup
ALTER TABLESPACE with the END BACKUP option. For example, the following statement ends the online backup of the tablespace users:
SQL> ALTER TABLESPACE users END BACKUP;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
When backing up several online tablespaces, you can back them up either serially or in parallel. Use either of the following procedures depending on your needs.
You can simultaneously put all tablespaces requiring backups in backup mode. Note that online redo logs can grow large if multiple users are updating these tablespaces because the redo must contain a copy of each changed data block.
To back up online tablespaces in parallel:
ALTER TABLESPACE statements at once. For example, put tablespaces ts1, ts2, and ts3 in backup mode as follows:
SQL> ALTER TABLESPACE ts1 BEGIN BACKUP; SQL> ALTER TABLESPACE ts2 BEGIN BACKUP; SQL> ALTER TABLESPACE ts3 BEGIN BACKUP;
tbs_ prefix as follows:
% cp /oracle/dbs/tbs_* /oracle/backup
SQL> ALTER TABLESPACE ts1 END BACKUP; SQL> ALTER TABLESPACE ts2 END BACKUP; SQL> ALTER TABLESPACE ts3 END BACKUP;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
You can place all tablespaces requiring online backups in backup mode one at a time. Oracle Corporation recommends the serial backup option because it minimizes the time between ALTER TABLESPACE ... BEGIN/END BACKUP statements. During online backups, more redo information is generated for the tablespace because whole data blocks are copied into the redo log.
To back up online tablespaces serially:
tbs_1 in backup mode enter the following:
SQL> ALTER TABLESPACE tbs_1 BEGIN BACKUP;
% cp /oracle/dbs/tbs_1.f /oracle/backup/tbs_1.bak
SQL> ALTER TABLESPACE tbs_1 END BACKUP;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
This section contains these topics:
The following situations can cause a tablespace backup to fail and be incomplete:
ALTER TABLESPACE ... END BACKUP statement.SHUTDOWN ABORT interrupted the backup before you could complete it.Whenever crash recovery is required (not instance recovery, because in this case the datafiles are open already), if a datafile is in backup mode when an attempt is made to open it, then the system assumes that the file is a restored backup. Oracle will not open the database until either a recovery command is issued, or the datafile is taken out of backup mode.
For example, Oracle may display a message such as the following when you run the STARTUP statement:
ORA-01113: file 12 needs media recovery ORA-01110: data file 12: '/oracle/dbs/tbs_41.f'
If Oracle indicates that the datafiles for multiple tablespaces require media recovery because you forgot to end the online backups for these tablespaces, then so long as the database is mounted, running the ALTER DATABASE END BACKUP statement takes all the datafiles out of backup mode simultaneously.
In high availability situations, and in situations when no DBA is monitoring the database (for example, in the early morning hours), the requirement for user intervention is intolerable. Hence, you can write a crash recovery script that does the following:
ALTER DATABASE END BACKUP statementALTER DATABASE OPEN, allowing the system to come up automaticallyAn automated crash recovery script containing ALTER DATABASE END BACKUP is especially useful in the following situations:
Alternatively, you can take the following manual measures after the system fails with tablespaces in backup mode:
END BACKUP statements altogether.ALTER TABLESPACE ... END BACKUP for each tablespace still in backup mode.You can run the ALTER DATABASE END BACKUP statement when you have multiple tablespaces still in backup mode. The primary purpose of this command is to allow a crash recovery script to restart a failed system without DBA intervention. You can also perform the following procedure manually.
To take tablespaces out of backup mode simultaneously:
SQL> STARTUP MOUNT
V$BACKUP view to list the datafiles of the tablespaces that were being backed up before the database was restarted:
SQL> SELECT * FROM V$BACKUP WHERE STATUS = 'ACTIVE'; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 12 ACTIVE 20863 25-NOV-00 13 ACTIVE 20863 25-NOV-00 20 ACTIVE 20863 25-NOV-00 3 rows selected.
ALTER DATABASE END BACKUP statement to take all datafiles currently in backup mode out of backup mode. For example, enter:
SQL> ALTER DATABASE END BACKUP;
You can use this statement only when the database is mounted but not open. If the database is open, use ALTER TABLESPACE ... END BACKUP or ALTER DATABASE DATAFILE ... END BACKUP for each affected tablespace or datafile.
The ALTER DATABASE END BACKUP statement is not the only way to respond to a failed online backup: you can also run the RECOVER command. This method is useful when you are not sure whether someone has restored a backup, because if someone has indeed restored a backup, then the RECOVER command brings the backup up to date. Only run the ALTER DATABASE END BACKUP or ALTER TABLESPACE ... END BACKUP statement if you are sure that the files are current.
|
Note: The |
To take tablespaces out of backup mode with the RECOVER command:
SQL> STARTUP MOUNT
SQL> RECOVER DATABASE
V$BACKUP view to confirm that there are no active datafiles:
SQL> SELECT * FROM V$BACKUP WHERE STATUS = 'ACTIVE'; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 0 rows selected.
| See Also:
Chapter 4, "Performing User-Managed Media Recovery" for information on recovering a database |
When backing up an online read-only tablespace, you can simply back up the online datafiles. You do not have to place the tablespace in backup mode because the system is permitting changes to the datafiles.
If the set of read-only tablespaces is self-contained, then in addition to backing up the tablespaces with operating system commands, you can also export the tablespace metadata by using the transportable tablespace functionality. In the event of a media error or a user error (such as accidentally dropping a table in the read-only tablespace), you can transport the tablespace back into the database.
| See Also:
Oracle9i Database Administrator's Guide to learn how to transport tablespaces |
To back up online read-only tablespaces in an open database:
DBA_TABLESPACES view to determine which tablespaces are read-only. For example, run this query:
SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE STATUS = 'READ ONLY';
DBA_DATA_FILES data dictionary view. For example, assume that you want to back up the history tablespace. Enter the following:
SELECT TABLESPACE_NAME, FILE_NAME FROM SYS.DBA_DATA_FILES WHERE TABLESPACE_NAME = 'HISTORY'; TABLESPACE_NAME FILE_NAME ------------------------------- -------------------- HISTORY /oracle/dbs/tbs_hist1.f HISTORY /oracle/dbs/tbs_hist2.f
In this example, /oracle/dbs/tbs_hist1.f and /oracle/dbs/tbs_hist2.f are fully specified filenames corresponding to the datafiles of the history tablespace.
% cp /oracle/dbs/tbs_hist*.f /backup
history as follows:
% exp TRANSPORT_TABLESPACE=y TABLESPACES=(history) FILE=/oracle/backup/tbs_hist.dmp
| See Also:
Oracle9i Database Reference for more information about the |
In releases prior to Oracle9i, undo space management was based on rollback segments. This method is called manual undo management mode. In Oracle9i, you have the option of placing the database in automatic undo management mode. With this design, you allocate undo space in a single undo tablespace instead of distributing space into a set of statically allocated rollback segments.
The procedures for backing up undo tablespaces are exactly the same as for backing up any other read/write tablespace. Because the automatic undo tablespace is so important for recovery and for read consistency, you should back it up frequently as you would for tablespaces containing rollback segments when running in manual undo management mode.
If the datafiles in the undo tablespace were lost while the database was open, and you did not have a backup, you could receive error messages when querying objects containing uncommitted changes. Also, if an instance failure occurred, you would not be able to roll back uncommitted transactions to their original values.
| See Also:
Oracle9i Database Administrator's Guide to learn how to manage undo space |
This section contains the following topics:
Some third-party tools allow you to mirror a set of disks or logical devices, that is, maintain an exact duplicate of the primary data in another location, and then split the mirror. Splitting the mirror involves separating the copies so that you can use them independently.
With the SUSPEND/RESUME functionality, you can suspend I/O to the database, then split the mirror and make a backup of the split mirror. By using this feature, which complements the backup mode functionality, you can suspend database I/Os so that no new I/O can be performed. You can then access the suspended database to make backups without I/O interference.
You do not need to use SUSPEND/RESUME to make split mirror backups in most cases, although it is necessary if your system requires the database cache to be free of dirty buffers before a volume can be split.
|
Note: Some RAID devices benefit from suspending writes while the split operation is occurring; your RAID vendor can advise you on whether your system would benefit from this feature. |
The ALTER SYSTEM SUSPEND statement suspends the database by halting I/Os to datafile headers, datafiles, and control files. When the database is suspended, all pre-existing I/O operations can complete; however, any new database I/O access attempts are queued.
The ALTER SYSTEM SUSPEND and ALTER SYSTEM RESUME statements operate on the database and not just the instance. If the ALTER SYSTEM SUSPEND statement is entered on one system in an Oracle Real Application Clusters configuration, then the internal locking mechanisms propagate the halt request across instances, thereby suspending I/O operations for all active instances in a given cluster.
After a successful database suspension, you can back up the database to disk or break the mirrors. Because suspending a database does not guarantee immediate termination of I/O, Oracle recommends that you precede the ALTER SYSTEM SUSPEND statement with a BEGIN BACKUP statement so that the tablespaces are placed in backup mode.
You must use conventional user-managed backup methods to back up split mirrors. RMAN cannot make database backups or copies because these operations require reading the datafile headers. After the database backup is finished or the mirrors are re-silvered, then you can resume normal database operations using the ALTER SYSTEM RESUME statement.
Backing up a suspended database without splitting mirrors can cause an extended database outage because the database is inaccessible during this time. If backups are taken by splitting mirrors, however, then the outage is nominal. The outage time depends on the size of cache to flush, the number of datafiles, and the time required to break the mirror.
Note the following restrictions for the SUSPEND/RESUME feature:
ALTER SYSTEM SUSPEND or ALTER SYSTEM RESUME statements.SHUTDOWN with IMMEDIATE or NORMAL options while the database is suspended.SHUTDOWN ABORT on a database that was already suspended reactivates the database. This operation prevents media recovery or crash recovery from hanging.To make a split mirror backup in SUSPEND mode:
users in backup mode enter:
ALTER TABLESPACE users BEGIN BACKUP;
ALTER SYSTEM SUSPEND;
V$INSTANCE. For example:
SELECT DATABASE_STATUS FROM V$INSTANCE; DATABASE_STATUS ----------------- SUSPENDED
ALTER SYSTEM RESUME;
V$INSTANCE. For example, enter:
SELECT DATABASE_STATUS FROM V$INSTANCE; DATABASE_STATUS ----------------- ACTIVE
users out of backup mode:
ALTER TABLESPACE users END BACKUP;
|
Caution: Do not use the |
| See Also:
Oracle9i Database Administrator's Guide for more information about the |
Back up the control file of a database after making a structural modification to a database operating in ARCHIVELOG mode. To back up a database's control file, you must have the ALTER DATABASE system privilege.
You have these options when backing up the control file:
The primary method for backing up the control file is to use a SQL statement to generate a binary file. A binary backup is preferable to a trace file backup because it contains additional information such as the archived log history, offline range for read-only and offline tablespaces, and backup sets and copies (if you use RMAN). Note that binary control file backups do not include tempfile entries.
To back up the control file after a structural change:
CREATE TABLESPACE tbs_1 DATAFILE 'file_1.f' SIZE 10M;
/oracle/backup/cf.bak:
ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/cf.bak' REUSE;
You can specify the REUSE option to make the new control file overwrite a control file that currently exists.
The TRACE option of the ALTER DATABASE BACKUP CONTROLFILE statement helps you manage and recover the control file. The TRACE option prompts Oracle to write SQL statements to the database's trace file rather than generate a binary backup. The statements in the trace file start the database, re-create the control file, and recover and open the database appropriately.
To back up the control file to a trace file, mount or open the database and issue the following SQL statement:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
If you specify neither the RESETLOGS nor NORESETLOGS option in the SQL statement, then the output is a trace file containing a CREATE CONTROLFILE ... NORESETLOGS statement. As in the case of binary control file backups, tempfile entries are not included in the trace output.
| See Also:
"Recovery of Read-Only Files with a Re-Created Control File" for special issues relating to read-only, offline normal, and temporary files included in |
Assume that you want to generate a script that re-creates the control file for the sales database. The database has these characteristics:
You issue the following statement to create a trace file containing a CREATE CONTROLFILE ... NORESETLOGS statement:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS;
You then edit the trace file to create a script that creates a new control file for the sales database based on the control file that was current when you generated the trace file. To avoid recovering offline normal or read-only tablespaces, edit them out of the CREATE CONTROLFILE statement in the trace file. When you open the database with the re-created control file, the dictionary check code will mark these omitted files as MISSING. You can run an ALTER DATABASE RENAME FILE statement renames them back to their original filenames.
For example, you can edit the CREATE CONTROLFILE ... NORESETLOGS script in the trace file as follows, renaming files labeled MISSING:
# The following statements will create a new control file and use it to open the database. # Log history and RMAN metadata will be lost. Additional logs may be required for media # recovery of offline datafiles. Use this only if the current version of all online logs # are available. STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE SALES NORESETLOGS ARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 2 MAXDATAFILES 32 MAXINSTANCES 16 MAXLOGHISTORY 1600 LOGFILE GROUP 1 '/diska/prod/sales/db/log1t1.dbf', '/diskb/prod/sales/db/log1t2.dbf' ) SIZE 100K GROUP 2 '/diska/prod/sales/db/log2t1.dbf', '/diskb/prod/sales/db/log2t2.dbf' ) SIZE 100K, GROUP 3 '/diska/prod/sales/db/log3t1.dbf', '/diskb/prod/sales/db/log3t2.dbf' ) SIZE 100K DATAFILE '/diska/prod/sales/db/database1.dbf', '/diskb/prod/sales/db/filea.dbf' ; # This datafile is offline, but its tablespace is online. Take the datafile offline # manually. ALTER DATABASE DATAFILE '/diska/prod/sales/db/filea.dbf' OFFLINE; # Recovery is required if any datafiles are restored backups, # or if the most recent shutdown was not normal or immediate. RECOVER DATABASE; # All redo logs need archiving and a log switch is needed. ALTER SYSTEM ARCHIVE LOG ALL; # The database can now be opened normally. ALTER DATABASE OPEN; # The backup control file does not list read-only and normal offline tablespaces so that # Oracle can avoid performing recovery on them. Oracle checks the data dictionary and # finds information on these absent files and marks them 'MISSINGxxxx'. It then renames # the missing files to acknowledge them without having to recover them. ALTER DATABASE RENAME FILE 'MISSING0002' TO '/diska/prod/sales/db/fileb.dbf';
To save disk space in your primary archiving location, you may want to back up archived logs to tape or to an alternative disk location. If you archive to multiple locations, then only back up one copy of each log sequence number.
To back up archived redo logs:
V$ARCHIVED_LOG. For example, run the following query:
SELECT THREAD#,SEQUENCE#,NAME FROM V$ARCHIVED_LOG;
% cp /oracle/dbs/arc_dest/* /disk7/log_backups
| See Also:
Oracle9i Database Reference for more information about the data dictionary views |
A raw device is a disk or partition that does not have a file system. In other words, a raw device can contain only a single file. Backing up files on raw devices poses operating system specific issues. The following sections discuss some of these issues on two of the most common Oracle operating systems: UNIX and Windows NT.
| See Also:
Oracle9i Real Application Clusters Setup and Configuration for a general overview of raw devices as they related to Oracle Real Application Clusters |
When backing up to or from raw devices, the UNIX dd command is the most common backup utility. See your operating system specific documentation for complete details about this utility.
The most important aspect of using dd is determining which options to specify. You need to know the following information.
The information in the preceding table enables you to set the dd options specified in Table 2-1.
Because a raw device can be the input or output device for a backup, you have four possible scenarios for the backup. The possible options for dd depend on which scenario you choose, as illustrated in Table 2-2.
For these examples of dd utility usage, assume the following:
dd block size to 8 KB when a raw device is involved in the copy.In this example, you back up from one raw device to another raw device:
% dd if=/dev/rsd1b of=/dev/rsd2b bs=8k skip=8 seek=8 count=3841
In this example, you back up from a raw device to a file system:
% dd if=/dev/rsd1b of=/backup/df1.dbf bs=8k skip=8 count=3841
In this example, you back up from a file system to a raw device:
% dd if=/backup/df1.dbf of=/dev/rsd2b bs=8k seek=8
In this example, you back up from a file system to a file system, and so can set the block size to a high value to boost I/O performance:
% dd if=/oracle/dbs/df1.dbf of=/backup/df1.dbf bs=1024k
Like UNIX, Windows NT supports raw disk partitions in which Oracle can store datafiles, online logs, and control files. Each raw partition is assigned either a drive letter or physical drive number and does not contain a file system. As in UNIX, each raw partition on NT is mapped to a single file.
NT differs from UNIX in the naming convention for Oracle files. On NT, raw datafile names are formatted as follows:
\\.\drive_letter: \\.\PHYSICALDRIVEdrive_number
For example, the following are possible raw filenames:
\\.\G: \\.\PHYSICALDRIVE3
Note that you can also create aliases to raw filenames. The standard Oracle installation provides a SETLINKS utility that can create aliases such as \\.\Datafile12 that point to filenames such as \\.\PHYSICALDRIVE3.
The procedure for making user-managed backups of raw datafiles is basically the same as for copying files on an NT file system, except that you should use the Oracle OCOPY utility rather than the NT-supplied copy.exe or ntbackup.exe utilities. Alternatively, if you have MKS utilities, then you can use the dd utility. OCOPY supports 64-bit file I/O, physical raw drives, and raw files. Note that OCOPY cannot back up directly to tape.
To display online documentation for OCOPY, enter OCOPY by itself at the Windows NT prompt. Sample output follows:
Usage of OCOPY: ocopy from_file [to_file [a | size_1 [size_n]]] ocopy -b from_file to_drive ocopy -r from_drive to_dir
Note the important OCOPY options described in the following table.
In this example, assume the following:
12 is mounted on the \\.\G: raw partition.C: drive mounts a file system.To back up the datafile on the raw partition \\.\G: to a local file system, you can execute the following command at the NT prompt after placing datafile 12 in backup mode:
OCOPY "\\.G:" C:\backup\datafile12.bak
In this example, assume the following:
\\.\G: is a raw partition containing datafile 7A: drive is a removable disk drive.To back up the datafile onto drive A:, you can execute the following command at the NT prompt after placing datafile 7 in backup mode:
# first argument is filename, second argument is drive OCOPY -b "\\.\G:" A:\
When drive A: fills up, you can use another disk. In this way, you can divide the backup of datafile 1 into multiple files.
Similarly, to restore the backup, take the tablespace containing datafile 7 offline and run this command:
# first argument is drive, second argument is directory OCOPY -r A:\ "\\.\G:"
You should periodically verify your backups to ensure that they are usable for recovery. This section contains the following topics:
The best way to test the usability of backups is to restore them to a separate host and attempt to open the database, performing media recovery if necessary. This option requires that you have a separate host available for the restore procedure.
See Also:
|
The DBVERIFY program is an external command-line utility that performs a physical data structure integrity check on an offline datafile. Use DBVERIFY primarily when you need to ensure that a user-managed backup of a datafile is valid before it is restored or as a diagnostic aid when you have encountered data corruption problems.
The name and location of DBVERIFY is dependent on your operating system. For example, to perform an integrity check on datafile tbs_52.f on UNIX, you can run the dbv command as follows:
% dbv file=tbs_52.f
Sample dbv output follows:
DBVERIFY: Release 9.2.0.0.0 (c) Copyright 2000 Oracle Corporation. All rights reserved. DBVERIFY - Verification starting : FILE = tbs_52.f DBVERIFY - Verification complete Total Pages Examined : 250 Total Pages Processed (Data) : 4 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 15 Total Pages Failing (Index): 0 Total Pages Processed (Other): 29 Total Pages Empty : 202 Total Pages Marked Corrupt : 0 Total Pages Influx : 0
| See Also:
Oracle9i Database Utilities for information about DBVERIFY |
Export and Import are utilities that move Oracle data in and out of Oracle databases. Export writes data from an Oracle database to an operating system file in a special binary format. Import reads Export files and restores the corresponding information into an existing database. Although Export and Import are designed for moving Oracle data, you can use them to supplement physical database backups.
This section describes the Import and Export utilities, and includes the following topics:
| See Also:
Oracle9i Database Utilities for complete documentation describing the Export and Import utilities |
The Export utility can back up logical database objects while the database is open and available for use. It writes a read-consistent view of the database's objects to an operating system file. System audit options are not exported.
Table 2-3 lists available export modes.
The Import utility can restore the database information held in previously created Export files. It is the complement utility to Export.
To recover a database using Export files and the Import utility:
You should always back up initialization parameter files, networking and configuration files, and password files. If a media failure destroys these files, then you may have difficulty re-creating your original environment. For example, if you back up the database and server parameter file but do not back up the networking files (for example, tnsnames.ora and listener.ora), then you can restore and recover the database but will not be able to authenticate users through Oracle Net until you re-create the networking files.
As a general rule, you should back up miscellaneous Oracle files after changing them. For example, if you add or change the net service names that can be used to access the database, then create a new backup of the tnsnames.ora file.
The easiest way to find configuration files is to start in the Oracle home directory and do a recursive search for all files ending in the .ora extension. For example, on UNIX you can run this command:
% find $ORACLE_HOME -name "*.ora" -print
You must use third-party utilities to back up the configuration files. For example, you can use the UNIX cp command to back up the tnsnames.ora and listener.ora files as follows:
% cp $ORACLE_HOME/network/admin/tnsnames.ora /disk2/bkups/tnsnames01-22-01.ora % cp $ORACLE_HOME/network/adminlistener.ora /disk2/bkups/listener01-22-01.ora
You can also use an operating system utility to back up the server parameter file. Although the database does not depend on the existence of a particular version of the server parameter file to be started, you should keep relatively current backups of this file so that you do not lose changes made to the file. Note that if you lose the server parameter file, you can always create a new one or start the instance with a client-side initialization parameter file (PFILE).
| See Also:
Oracle9i Database Administrator's Guide to learn how to manage and export server parameter files |
|
 Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|