HOW TO BACKUP DATAFILES ON UNIX (Oracle7 and Oracle8)
-----------------------------------------------------
Whether in ARCHIVELOG mode or NOARCHIVELOG mode, you must regularly take
database backups (aka "saves") to protect your data from loss due to hardware,
software, or user error. If in NOARCHIVELOG mode, then backups can ONLY be
taken when the database/instance is shutdown (using either "shutdown" or
"shutdown immediate").
Within the computer industry, these are termed "cold" backups. When using
Oracle's Recovery Manager (RMAN), these are termed "offline" backups. If in
ARCHIVELOG mode, then "cold" backups or "hot" backups may be taken. "Hot"
(aka "warm") backups are taken while the database/instance is up and running.
In RMAN, these are called "online" backups.
*NOTE: UNIX HOT BACKUPS ARE ONLY GOOD IF THE TABLESPACES ARE PLACED
IN AND OUT OF BACKUP MODE USING THESE COMMANDS:
ALTER TABLESPACE <tablespace name> BEGIN BACKUP;
ALTER TABLESPACE <tablespace name> END BACKUP;
FURTHER, DO NOT USE ANY OF THE FOLLOWING UNIX BACKUP TECHNIQUES IN CONJUNCTION
WITH ORACLE'S RECOVERY MANAGER (RMAN) WHICH USES A PROPRIETARY FILE FORMAT.
(In other words, do not try to use UNIX commands to copy an RMAN backup from
tape to disk.)
The most important files to be saved in a backup are the datafiles (files
with extensions of ".dbf"). The easiest way to identify these is to run this
command from the srvrmgrl prompt:
srvrmgrl> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
The output will be in a ".trc" file in the USER_DUMP_DEST directory. All
datafiles associated with this database will be listed in this trace file.
Additionally, if in ARCHIVELOG mode, all archive logfiles should be saved.
(Refer to init<SID>.ora parameter "LOG_ARCHIVE_FORMAT" for the file extension
and "LOG_ARCHIVE_DEST" for the directory/path of the archived log files.)
Typical database recoveries use the current controlfiles and active redo logs,
so it is not absolutely necessary to back them up. However, in case of the loss
of all controlfiles, either a new one must be created from scratch (a difficult
task) or one can be created from the output of the "ALTER DATABASE BACKUP
CONTROLFILE TO TRACE;" command (impossible if the database is down) or an old
controlfile can be used. Therefore, it is STRONGLY advised to either save the
controlfiles and or regularly backup one to a trace file (see above). In fact,
the latter (backup to trace) should be performed after any structural change to
the database (adding datafiles or log files). Finally, the redo logs should
also be saved if NOARCHIVELOG mode is in use.
A full backup cannot be taken if a database is open or was shutdown because of
an instance failure or abort. To guarantee that a database's datafiles are
consistent, always shutdown the database in normal priority
(either "shutdown normal" or "shutdown immediate") before making a full
database backup. If you do a "shutdown abort", your datafiles will be
inconsistent and out of sync. You will not be able to make a full recovery from
that backup.
The files you select for backing up are termed the "backup set". Once you have
created your list, prepare your database for a backup by either shutting it down
(for a cold backup) or issuing this command (for a hot backup):
ALTER TABLESPACE <tablespace name> BEGIN BACKUP;
For the files associated with the backup set, use the UNIX "tar" command to copy
the data to tape (the most common procedure). Other UNIX tools for copying
files to another location/media are also viable ("cpio", "cp", "rcp", etc.).
These can even be used in combination, such as copying the files to a remote
server with the "rcp" command and then adding the files to tape with a UNIX
"tar". (Please refer to your UNIX vendor's documentation for specifics
regarding these UNIX commands.)
========================================================================
EXAMPLES FOR GETTING DATAFILE, CONTROLFILE, AND OTHER BACKUP INFORMATION
========================================================================
Use "V$" (dynamic performance tables) to find the file names:
To get the datafiles:
SQLDBA> select name from v$datafile;
To get the log files:
SQLDBA> select member from v$logfile;
To get the control files:
SQLDBA> show parameter control_files;
Example (from RDBMS version 7.1.4 on HP-UX):
% sqldba mode=line
SQLDBA> select name from v$datafile;
NAME
-----------------------------------------
/oracle/dbs/systP714.dbf
/oracle/dbs/rbsP714.dbf
/oracle/dbs/tempP714.dbf
/oracle/dbs/toolP714.dbf
/oracle/dbs/usrP714.dbf
SQLDBA> select member from v$logfile;
MEMBER
------------------------------------
/oracle/dbs/log1P714.dbf
/oracle/dbs/log2P714.dbf
/oracle/dbs/log3P714.dbf
3 rows selected.
SQLDBA> show parameter control_files;
NAME TYPE VALUE
-------------------------------------------------
control_files string/oracle/dbs/ctrl1P714.ctl,
/oracle/dbs/ctrl2P714.ctl, /oracle/dbs/ctrl3P714.ctl
========================================================================
As an alternate method to using v$ tables, you can get the information from
the output of a controlfile trace backup (see example below).
rtcsol1% sqlplus system/manager
SQL*Plus: Release 8.1.6.0.0 - Production on Mon Apr 17 14:17:20 2000
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ------- ---------------------------------
control_file_record_keep_time integer 7
control_files string //u02/oradata/V816/control01.ctl,
tl, //u02/oradata/V816/control02.
ctl
SQL> show parameter user_dump
NAME TYPE VALUE
------------------------------------ ------- --------------------------------
user_dump_dest string /u02/app/oracle/admin/V816/udump
SQL> alter database backup controlfile to trace;
Database altered.
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
rtcsol1% ls -l *.ctl
-rw-r----- 1 usupport udba 1730560 Apr 17 14:27 control01.ctl
-rw-r----- 1 usupport udba 1730560 Apr 17 14:27 control02.ctl
rtcsol1% cd /u02/app/oracle/admin/V816/udump
rtcsol1% grep 'CREATE CONTROLFILE' *.trc
v816_ora_21932.trc:CREATE CONTROLFILE REUSE DATABASE "V816" NORESETLOGS
rtcsol1% cat v816_ora_21932.trc
Dump file /u02/app/oracle/admin/V816/udump/v816_ora_21932.trc
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
ORACLE_HOME = /u02/app/oracle/product/8.1.6
System name: SunOS
Node name: rtcsol1
Release: 5.6
Version: Generic_105181-17
Machine: sun4u
Instance name: V816
Redo thread mounted by this instance: 1
Oracle process number: 9
Unix process pid: 21932, image: oracle@rtcsol1 (TNS V1-V3)
*** SESSION ID:(8.93) 2000-04-17 14:26:04.444
*** 2000-04-17 14:26:04.444
# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "V816" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 8
MAXLOGHISTORY 2024
LOGFILE
GROUP 1 '/u02/oradata/V816/redoV81601.log' SIZE 1M,
GROUP 2 '/u02/oradata/V816/redoV81602.log' SIZE 1M,
GROUP 3 '/u02/oradata/V816/redoV81603.log' SIZE 1M,
GROUP 4 '/u02/oradata/V816/redoV81604.log' SIZE 1M,
GROUP 5 '/u02/oradata/V816/redoV81605.log' SIZE 1M,
GROUP 6 '/u02/oradata/V816/redoV81606.log' SIZE 1M,
GROUP 7 '/u02/oradata/V816/redoV81607.log' SIZE 1M
DATAFILE
'/u02/oradata/V816/system01.dbf',
'/u02/oradata/V816/rbs01.dbf',
'/u02/oradata/V816/temp01.dbf',
'/u02/oradata/V816/tools01.dbf',
'/u02/oradata/V816/users01.dbf',
'//u02/oradata/V816/oemrep01.dbf',
'//u02/oradata/V816/indx01.dbf',
'//u02/oradata/V816/drsys01.dbf',
'/u02/home/usupport/imt.dbf',
'/u02/home/usupport/imt2.dbf',
'/u02/oradata/V816/javt01',
'/u02/home/usupport/kting/ts01_01.dbf',
'/u02/home/usupport/kting/ts02_01.dbf',
'/u02/home/usupport/kting/ts03_01.dbf',
'/u02/oradata/V816/probe01.dbf',
'/bugmnt/tar12895390.600/earthlink1.dbf',
'/bugmnt/tar12895390.600/earthlink2.dbf',
'/u02/home/usupport/hdammeye/tc/138655/lgct_dat1.dbf',
'/u02/home/usupport/hdammeye/tc/138655/lgct_tempdat1.dbf',
'/u02/home/usupport/pschieme/tc/138001/tblsxx1.dbf'
CHARACTER SET US7ASCII
;
# Take files offline to match current control file.
ALTER DATABASE DATAFILE '/u02/home/usupport/imt.dbf' OFFLINE DROP;
ALTER DATABASE DATAFILE '/u02/home/usupport/imt2.dbf' OFFLINE DROP;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
# No tempfile entries found to add.
#
========================================================================