This chapter describes methods of backing up an Oracle database in a user-managed backup and recovery strategy, that is, a strategy that does not depend on using Recovery Manager (RMAN).
This chapter contains the following sections:
Making User-Managed Backups of Offline Tablespaces and Datafiles
Making User-Managed Backups of Online Tablespaces and Datafiles
Before making a backup, you must identify all the files in your database and decide what to back up. Several V$ views can provide the necessary information.
Use V$DATAFILE, V$LOGFILE and V$CONTROLFILE to identify the datafiles, log files and control files for your database. This same procedure works whether you named these files manually or allowed Oracle Managed Files to name them.
To list datafiles, online redo logs, and control files:
Start SQL*Plus and query 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;
Obtain the filenames of online redo log files by querying the V$LOGFILE
view. For example, issue the following query:
SQL> SELECT MEMBER FROM V$LOGFILE;
Obtain the filenames of the current control files by querying the 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.
If you plan to take a control file backup with the 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, because neither RMAN backups nor offline tablespace backups require the datafiles of a tablespace to be in backup mode.
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 the database needs to populate 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';
The following sample output shows that the tools
and users
tablespaces currently have ACTIVE
status:
TB_NAME DF# DF_NAME STATUS ---------------------- ---------- -------------------------------- ------ TOOLS 7 /oracle/oradata/trgt/tools01.dbf ACTIVE USERS 8 /oracle/oradata/trgt/users01.dbf ACTIVE
In the STATUS
column, NOT
ACTIVE
indicates that the file is not currently in backup mode (that is, you have not executed the ALTER
TABLESPACE
...
BEGIN
BACKUP
or ALTER DATABASE BEGIN BACKUP
statement), 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 further 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.
See Also:
"Making User-Managed Backups of the Control File" for more information about backing up control filesThis section describes how to back up the database with an operating system utility.
To make a consistent whole database backup:
If the database is open, use SQL*Plus to shut down the database with the NORMAL
, IMMEDIATE
, or TRANSACTIONAL
options.
Use an operating system utility to make backups of all datafiles as well as all control files specified by the 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, control files and archived logs to /disk2/backup
as follows:
% cp $ORACLE_HOME/oradata/trgt/*.dbf /disk2/backup % cp $ORACLE_HOME/oradata/trgt/arch/* /disk2/backup/arch
Restart the database. For example, enter:
SQL> STARTUP
See Also:
Oracle Database Administrator's Guide for more information on starting up and shutting down a databaseNote the following guidelines when backing up offline tablespaces:
You cannot offline the SYSTEM
tablespace or a tablespace with active rollback segments. The following procedure cannot be used for such tablespaces.
Assume that a table is in tablespace 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:
Before beginning a backup of a tablespace, identify the tablespace's datafiles by querying the 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/oradata/trgt/users01.dbf
In this example, /oracle/oradata/trgt/users01.dbf
is a fully specified filename corresponding to the datafile in the users
tablespace.
Take the tablespace offline using normal priority if possible because it guarantees that you can subsequently bring the tablespace online without having to recover it. For example:
SQL> ALTER TABLESPACE users OFFLINE NORMAL;
Back up the offline datafiles. For example:
% cp /oracle/oradata/trgt/users01.dbf /d2/users01_'date "+%m_%d_%y"'.dbf
Bring the tablespace online. For example:
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.Archive the unarchived redo logs so that the redo required to recover the tablespace backup is archived. For example, enter:
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.
Note:
You should not back up temporary tablespaces.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. In backup mode, the database copies whole changed data blocks into the redo stream. After you take the tablespace out of backup mode with the ALTER
TABLESPACE
...
END
BACKUP
or ALTER
DATABASE
END
BACKUP
statement, the database advances the datafile header to the current database checkpoint.
When restoring a datafile backed up in this way, the database asks for the appropriate set of redo log files to apply if 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:
Before beginning a backup of a tablespace, identify all of the datafiles in the tablespace with the 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/oradata/trgt/users01.dbf USERS /oracle/oradata/trgt/users02.dbf
Mark the beginning of the online tablespace backup. For example, the following statement marks the start of an online backup for the tablespace users
:
SQL> ALTER TABLESPACE users BEGIN BACKUP;
Caution:
If you do not use BEGIN
BACKUP
to mark the beginning of an online tablespace backup and wait for that statement to complete before starting your copies of online tablespaces, or then the datafile copies produced are not usable for subsequent recovery operations. Attempting to recover such a backup is risky and can return errors that result in inconsistent data. For example, the attempted recovery operation can issue a "fuzzy files" warning, and can lead to an inconsistent database that you cannot open.
Back up the online datafiles of the online tablespace with operating system commands. For example, UNIX users might enter:
% cp /oracle/oradata/trgt/users01.dbf /d2/users01_'date "+%m_%d_%y"'.dbf % cp /oracle/oradata/trgt/users02.dbf /d2/users02_'date "+%m_%d_%y"'.dbf
After backing up the datafiles of the online tablespace, run the SQL statement 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;
Archive the unarchived redo logs so that the redo required to recover the tablespace backup is archived. For example, enter:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Caution:
If you fail to take the tablespace out of backup mode, then Oracle continues to write copies of data blocks in this tablespace to the online logs, causing performance problems. Also, you will receive an ORA-01149
error if you try to shut down the database with the tablespaces still in backup mode.
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 create datafile copies of multiple tablespaces requiring backups in backup mode. Note, however, that by putting all tablespaces in online mode at once, you can generate large redo logs if there is heavy update activity on the affected tablespaces, because the redo must contain a copy of each changed data block in each changed datafile. Be sure to consider the size of the likely redo before using the procedure outlined here.
To back up online tablespaces in parallel:
Prepare all online tablespaces for backup by issuing all necessary ALTER
TABLESPACE
statements at once. For example, put tablespaces users
, tools
, and index
in backup mode as follows:
SQL> ALTER TABLESPACE users BEGIN BACKUP; SQL> ALTER TABLESPACE tools BEGIN BACKUP; SQL> ALTER TABLESPACE indx BEGIN BACKUP;
If you are backing up all tablespaces, you might want to use this command:
SQL> ALTER DATABASE BEGIN BACKUP;
Back up all files of the online tablespaces. For example, a UNIX user might back up datafiles with the *.dbf
suffix as follows:
% cp $ORACLE_HOME/oradata/trgt/*.dbf /disk2/backup/
Take the tablespaces out of backup mode as in the following example:
SQL> ALTER TABLESPACE users END BACKUP; SQL> ALTER TABLESPACE tools END BACKUP; SQL> ALTER TABLESPACE indx END BACKUP;
Again, it you are handling all datafiles at once you can use the ALTER DATABASE command instead of ALTER TABLESPACE:
SQL> ALTER DATABASE END BACKUP;
Archive the online redo logs so that the redo required to recover the tablespace backups will be available for later media recovery. For example, enter:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
You can place all tablespaces requiring online backups in backup mode one at a time. Oracle 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:
Prepare a tablespace for online backup. For example, to put tablespace users
in backup mode enter the following:
SQL> ALTER TABLESPACE users BEGIN BACKUP;
In this case you probably do not want to use ALTER DATABASE BEGIN BACKUP
to put all tablespaces in backup mode simultaneously, because of the unnecessary volume of redo log information generated for tablespaces in online mode.
Back up the datafiles in the tablespace. For example, enter:
% cp /oracle/oradata/trgt/users01.dbf /d2/users01_'date "+%m_%d_%y"'.dbf
Take the tablespace out of backup mode. For example, enter:
SQL> ALTER TABLESPACE users END BACKUP;
Repeat this procedure for each remaining tablespace.
Archive the unarchived redo logs so that the redo required to recover the tablespace backups is archived. For example, enter:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
The following situations can cause a tablespace backup to fail and be incomplete:
The backup completed, but you did not run the ALTER
TABLESPACE
...
END
BACKUP
statement.
An instance failure or SHUTDOWN
ABORT
interrupted the backup.
Whenever crash recovery is required, if a datafile is in backup mode when an attempt is made to open it, then the database will not open the database until either a recovery command is issued, or the datafile is taken out of backup mode.
For example, the database may display a message such as the following at startup:
ORA-01113: file 12 needs media recovery ORA-01110: data file 12: '/oracle/dbs/tbs_41.f'
If the database 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, the requirement for user intervention is intolerable. Hence, you can write a crash recovery script that does the following:
Mounts the database
Runs the ALTER
DATABASE
END
BACKUP
statement
Runs ALTER
DATABASE
OPEN
, allowing the system to come up automatically
An automated crash recovery script containing ALTER
DATABASE
END
BACKUP
is especially useful in the following situations:
All nodes in an Oracle Real Application Clusters (RAC) configuration fail.
One node fails in a cold failover cluster (that is, a cluster that is not a RAC configuration in which the secondary node must mount and recover the database when the first node fails).
Alternatively, you can take the following manual measures after the system fails with tablespaces in backup mode:
Recover the database and avoid issuing END
BACKUP
statements altogether.
Mount the database, then run 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:
Mount but do not open the database. For example, enter:
SQL> STARTUP MOUNT
If performing this procedure manually (that is, not as part of a crash recovery script), query the 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-02 13 ACTIVE 20863 25-NOV-02 20 ACTIVE 20863 25-NOV-02 3 rows selected.
Issue the 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.
Caution:
Do not use ALTER
DATABASE
END
BACKUP
if you have restored any of the affected files from a backup.
The ALTER
DATABASE
END
BACKUP
statement is not the only way to respond to a failed online backup: you can also run the SQL*Plus 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:
TheRECOVER
command method is slow because the database must scan redo generated from the beginning of the online backup.To take tablespaces out of backup mode with the RECOVER command:
Mount the database. For example, enter:
SQL> STARTUP MOUNT
Recover the database as normal. For example, enter:
SQL> RECOVER DATABASE
Use the 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 18, "Performing User-Managed Database Flashback and Recovery" for information on recovering a databaseWhen 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 database is not 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 with 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:
Oracle Database Administrator's Guide to learn how to transport tablespacesTo back up online read-only tablespaces in an open database:
Query the 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';
Before beginning a backup of a read-only tablespace, identify all of the tablespace's datafiles by querying the DBA_DATA_FILES
data dictionary view. For example, assume that you want to back up the history
tablespace:
SELECT TABLESPACE_NAME, FILE_NAME FROM SYS.DBA_DATA_FILES WHERE TABLESPACE_NAME = 'HISTORY'; TABLESPACE_NAME FILE_NAME ------------------------------- -------------------- HISTORY /oracle/oradata/trgt/history01.dbf HISTORY /oracle/oradata/trgt/history02.dbf
Back up the online datafiles of the read-only tablespace with operating system commands. You do not have to take the tablespace offline or put the tablespace in backup mode because users are automatically prevented from making changes to the read-only tablespace. For example:
% cp $ORACLE_HOME/oradata/trgt/history*.dbf /disk2/backup/
Note:
When restoring a backup of a read-only tablespace, take the tablespace offline, restore the datafiles, then bring the tablespace online. A backup of a read-only tablespace is still usable if the read-only tablespace is made read/write after the backup, but the restored backup will require recovery.Optionally, export the metadata in the read-only tablespace. By using the transportable tablespace feature, you can quickly restore the datafiles and import the metadata in case of media failure or user error. For example, export the metadata for tablespace history
as follows:
% exp TRANSPORT_TABLESPACE=y TABLESPACES=(history) FILE=/disk2/backup/hs.dmp
See Also:
Oracle Database Reference for more information about theDBA_DATA_FILES
and DBA_TABLESPACES
viewsBack 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.
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:
Make the desired change to the database. For example, you may create a new tablespace:
CREATE TABLESPACE tbs_1 DATAFILE 'file_1.f' SIZE 10M;
Back up the database's control file, specifying a filename for the output binary file. The following example backs up a control file to /disk1/backup/cf.bak
:
ALTER DATABASE BACKUP CONTROLFILE TO '/disk1/backup/cf.bak' REUSE;
Specify the REUSE
option to make the new control file overwrite one that currently exists.
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 resulting trace file contains versions of the control file for both RESETLOGS
and NORESETLOGS
options. Tempfile entries are included in the output using "ALTER TABLESPACE... ADD TEMPFILE
" statements.
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 inCREATE
CONTROLFILE
statementsAssume that you want to generate a script that re-creates the control file for the sales
database. The database has these characteristics:
Three threads are enabled, of which thread 2 is public and thread 3 is private.
The redo logs are multiplexed into three groups of two members each.
The database has the following datafiles:
/diska/prod/sales/db/filea.dbf
(offline datafile in online tablespace)
/diska/prod/sales/db/database1.dbf
(online in SYSTEM
tablespace)
/diska/prod/sales/db/fileb.dbf
(only file in read-only tablespace)
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:
To determine which archived redo log files that the database has generated, query V$ARCHIVED_LOG
. For example, run the following query:
SELECT THREAD#,SEQUENCE#,NAME FROM V$ARCHIVED_LOG;
Back up one copy of each log sequence number by using an operating system utility. This example backs up all logs in the primary archiving location to a disk devoted to log backups:
% cp $ORACLE_HOME/oracle/trgt/arch/* /disk2/backup/arch
See Also:
Oracle Database Reference for more information about the data dictionary viewsThis 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/O 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. 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 a RAC 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:
In a RAC configuration, you should not start a new instance while the original nodes are suspended.
No checkpoint is initiated by the ALTER
SYSTEM
SUSPEND
or ALTER
SYSTEM
RESUME
statements.
You cannot issue SHUTDOWN
with IMMEDIATE
, NORMAL
, or TRANSACTIONAL
options while the database is suspended.
Issuing SHUTDOWN
ABORT
on a database that was already suspended reactivates the database. This prevents media recovery or crash recovery from hanging.
To make a split mirror backup in SUSPEND mode:
Place the database tablespaces in backup mode. For example, to place tablespace users
in backup mode enter:
ALTER TABLESPACE users BEGIN BACKUP;
If you are backing up all of the tablespaces for your database, you can instead use:
ALTER DATABASE BEGIN BACKUP;
If your mirror system has problems with splitting a mirror while disk writes are occurring, then suspend the database. For example, issue the following:
ALTER SYSTEM SUSPEND;
Check to make sure that the database is suspended by querying V$INSTANCE
. For example:
SELECT DATABASE_STATUS FROM V$INSTANCE; DATABASE_STATUS ----------------- SUSPENDED
Split the mirrors at the operating system or hardware level.
End the database suspension. For example, issue the following statement:
ALTER SYSTEM RESUME;
Check to make sure that the database is active by querying V$INSTANCE
. For example, enter:
SELECT DATABASE_STATUS FROM V$INSTANCE; DATABASE_STATUS ----------------- ACTIVE
Take the specified tablespaces out of backup mode. For example, enter the following to take tablespace users
out of backup mode:
ALTER TABLESPACE users END BACKUP;
Copy the control file and archive the online redo logs as usual for a backup.
Caution:
Do not use the ALTER
SYSTEM
SUSPEND
statement as a substitute for placing a tablespace in backup mode.
See Also:
Oracle Database Administrator's Guide for more information about theSUSPEND
/RESUME
feature, and Oracle Database SQL Reference for more information about the ALTER
SYSTEM
statementA 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 operating systems supporting Oracle: UNIX and Windows.
See Also:
Oracle Real Application Clusters Installation and Configuration Guide for a general overview of raw devices as they relate to Oracle Real Application ClustersWhen 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.
Using dd
effectively requires specifying the correct options, based on your database. Details about your database that affect the options you use for dd are listed in the following table.
Data | Explanation |
---|---|
Block size | You can specify the size of the buffer that dd uses to copy data. For example, you can specify that dd should copy data in units of 8 KB or 64 KB. Note that the block size for dd need not correspond to either the Oracle block size or the operating system block size: it is merely the size of the buffer used by dd when making the copy. |
Raw offset | On some systems, the beginning of the file on the raw device is reserved for use by the operating system. This storage space is called the raw offset. Oracle should not back up or restore these bytes. |
Size of Oracle block 0 |
At the beginning of every Oracle file, the operating system-specific code places an Oracle block called block 0. The generic Oracle code does not recognize this block, but the block is included in the size of the file on the operating system. Typically, this block is the same size as the other Oracle blocks in the file. |
The information in the preceding table enables you to set the dd
options specified in Table 17-1.
Table 17-1 Options for dd Command
This option ... | Specifies ... |
---|---|
|
The name of the input file, that is, the file that you are reading. |
|
The name of the output file, that is, the file to which you are writing. |
|
The buffer size used by |
|
The number of |
|
The number of |
|
The number of blocks on the input raw device for Remember to include block |
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 17-2.
Table 17-2 Scenarios Involving dd Backups
Backing Up from ... | Backing Up to ... | Options Specified for dd Command |
---|---|---|
Raw device |
Raw device |
|
Raw device |
File system |
|
File system |
Raw device |
|
File system |
File system |
|
For these examples of dd
utility usage, assume the following:
You are backing up a 30720 KB datafile.
The beginning of the datafile has a block 0 of 8 KB.
The raw offset is 64 KB.
You set the dd
block size to 8 KB when a raw device is involved in the copy.
In the following 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 the following 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 the following 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 the following 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 supports raw disk partitions in which the database 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 database 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. 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 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.
This option ... | Specifies ... |
---|---|
b |
Splits the input file into multiple output files. This option is useful for backing up to devices that are smaller than the input file. |
r |
Combines multiple input files and writes to a single output file. This option is useful for restoring backups created with the -b option. |
In this example, assume the following:
Datafile 12
is mounted on the \\.\G:
raw partition.
The C:
drive mounts a file system.
The database is open.
To back up the datafile on the raw partition \\.\G:
to a local file system, you can run the following command at the 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 7
The A:
drive is a removable disk drive.
The database is open.
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 7 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 - Verification starting : FILE = users01.dbf DBVERIFY - Verification complete Total Pages Examined : 250 Total Pages Processed (Data) : 1 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 2 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 247 Total Pages Marked Corrupt : 0 Total Pages Influx : 0
See Also:
Oracle Database Utilities to learn about DBVERIFYOracle import and export utilities move Oracle data in and out of Oracle databases. Export utilities write exported database objects to operating system files in an Oracle-proprietary format. Import utilities can read the files produced by export utilities and re-create database objects. Logical exports of data can be a useful supplement to physical database backups in some situations, especially in backing up recovery catalog databases.
There are two sets of Oracle database import and export utilities: Original Import and Export (which were used in previous releases) and Data Pump Import and Export (new for Oracle Database Release 10g). The Data Pump utilities offer better performance and more complete support of features of Oracle Database Release 10g. Whichever export tool you use to export objects to a file, you must use the corresponding import tool to import the objects from the file, that is, you cannot use the Data Pump Import utility to read the output of the Original Export utility, or the Original Import to read the output of the Data Pump Export utility.
See Also:
Oracle Database Utilities for complete documentation of the Oracle import and export utilities, including a comparison of their capabilities.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 environment. For example, if you back up the database and server parameter file but do not back up the networking files, 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 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 /d2/tnsnames'date "+%m_%d_%y"'.ora % cp $ORACLE_HOME/network/admin/listener.ora /d2/listener'date "+%m_%d_%y"'.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
).
One of the most important aspects of user-managed backup and recovery is keeping records of all current database files as well as the backups of these files. For example, you should have records for the location of the following files:
Datafiles and control files
Online and archived redo logs (note that online logs are never backed up)
Initialization parameter files
Password files
Networking-related files
The following useful SQL script displays the location of all control files, datafiles, and online redo log files for the database:
SELECT NAME FROM V$DATAFILE UNION ALL SELECT MEMBER FROM V$LOGFILE UNION ALL SELECT NAME FROM V$CONTROLFILE;
You can determine the location of the default archived log destinations by executing the following SQL script:
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE log_archive_dest% AND VALUE IS NOT NULL; NAME VALUE ---------------------------------- ------------------------------------------- log_archive_dest_1 LOCATION=/oracle/work/arc_dest/arc log_archive_dest_state_1 enable
Determine the format for archived logs by running SHOW
as follows:
SHOW PARAMETER LOG_ARCHIVE_FORMAT
To see a list of all the archived logs recorded in the control file, issue this query:
SELECT NAME FROM V$ARCHIVED_LOG;
It is not enough to merely record the location of backup files: you must correlate the backups with the original files. If possible, name the backups with names derived from the name of the primary file. Whatever naming system you use, keep a table containing the relevant information. For example, you could keep the following table as a record of database file locations in case of a restore emergency.
Datafile # | Tbs | Current Datafiles | Backup Datafiles |
---|---|---|---|
0 (cf) |
0 (cf) |
/oracle/oradata/trgt/control01.dbf |
/d2/control01_10_31_02.dbf |
1 |
SYSTEM |
/oracle/oradata/trgt/system01.dbf |
/d2/system01_10_31_02.dbf |
2 |
undo |
/oracle/oradata/trgt/undo01.dbf |
/d2/undo01_10_31_02.dbf |
3 |
cwmlite |
/oracle/oradata/trgt/cwmlite01.dbf |
/d2/cwmlite01_10_31_02.dbf |
4 |
drsys |
/oracle/oradata/trgt/drsys01.dbf |
/d2/drsys01_10_31_02.dbf |