Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2) Part Number B14191-02 |
|
|
View PDF |
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 activitiy 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 indx
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
views