This is the second of a series of excerpts from "Oracle DBA automation scripts" by Rajendra Gutta. Click for part one on cold backup under Unix and part three on backup and recovery under Windows NT. You can purchase the book here.
Hot Backup
Listing 3.4 provides the script to perform the hot backup of a database under
the Unix environment. The hot backup script takes two input
parameters—SID and OWNER. SID is the instance to
be backed up, and OWNER is the Unix account under which Oracle is
running.
Figure 3.4 shows the functionality of the hot backup program. Each box
represents a corresponding function in the program:
[IMAGE]
Listing 3.4 hotbackup_ux
Hot Backup Script under Unix Checklist
In the main function, set the correct values for BACKUPDIR,
ORATABDIR, TOOLS, and log_arch_dest variables
highlighted in the script. The default location of ORATABDIR is
different for each flavor of Unix.
Check for existence of the SID instance in the oratab
file. If not already there, you must add the instance.
Check for the existence of the initSID.ora file in the
ORACLE_HOME/dbs directory. If it is in a different location, you must
create a soft link to the ORACLE_HOME/dbs directory.
Pass SID and OWNER as parameters to the program:
main() BACKUPDIR defines the backup location.
ORATABDIR defines the oratab file location. oratab
files maintain the list of instances and their home directories on the machine.
This file is created by default when Oracle is installed. If it is not there,
you must create one. OWNER is the owner of the Oracle software
directories.
funct_get_vars() Make sure that the USER_DUMP_DEST
parameter is set correctly in Init.ora file. I was reluctant to get
LOG_ARCHIVE_DEST from the Init.ora file because there are some
changes between Oracle 7 and 8 in the way the archive destination is defined.
There are a variety of ways that you can define log_archive_dest based
on how many destinations you are using. Consequently, I have given the option to
define log_archive_dest in main function.
funct_temp_backup() Oracle 7 and Oracle 8 support permanent
temporary tablespaces (created with create tablespce tablespace_name ...
temporary). Apart from this, ...
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
Oracle 8I has new features to create
temporary tablespaces that do not need back up (created with create
tablespace temporary...). Data in these temporary tablespaces is
session-specific and gets deleted as soon as the session is disconnected.
Because of the nature of these temporary tablespaces, you do not need to back
them up; in the case of a restore, you can just add the data file for these
temporary tablespaces. The files for these temporary tablespaces are listed
under the dba_temp_files data dictionary view.funct_control_backup() In addition to taking backup of control
file, this function also backs up the trace of the control file. The trace of
the control file will be useful to examine the structure of the database. This
is the single most important piece of information that you need to perform a
good recovery, especially if the database has hundreds of files.
funct_chk_bkup_dir() This function creates backup directories
for data, control, redo log, archivelog, init files, restore files, and backup
log files.
Restore file
The restore file for hot backup looks similar to cold backup. Please refer to
the explanation under the heading restore file for cold backup.
Hot Backup Troubleshooting and Status Check
The important thing here is that the backup log file defined by
(BACKUPLOGFILE) contains detailed information about each step of the backup
process. This is a very good place to start investigating why a backup has
failed or for related errors. This file will also have the start and end time of
the backup.
A single line about the success or failure of a backup is appended to the
SID.log file every time a backup is performed. This file is located
under the directory defined by the LOGDIR variable. This file also has
the backup completion time. A separate file is created for each instance. This
single file maintains the history of the performed backups, their status, and
timing information. The messages for a hot backup are
'HOTBACKUP_FAIL', if the hot backup failed, and
'Hotbackup Completed successfully', if the backup completes
successfully.
The following is an excerpt from the log file:
Export
The export program (see Listing 3.5) performs a full export of the database
under Unix environment. The export script takes two input
parameters—SID and OWNER. SID is the instance to
be backed up, and OWNER is the Unix account under which Oracle is
running. Figure 3.5 shows the functionality of the export and split export
programs. Each box represents a corresponding function in the program.
Figure 3.5 Functions in export and split export scripts for Unix.
Listing 3.5 xport_ux
Export Script under Unix Checklist
In the main function, set the correct values for BACKUPDIR,
ORATABDIR, and TOOLS variables highlighted in the export
script. The default location of ORATABDIR is different for each flavor
of Unix.
Check for existence of SID in the oratab file. If not
already there, you must add the instance.
The funct_build_parfile() function builds the parameter file. By
default, it performs a full export. You can modify the parameters to perform a
user- or table-level export.
Pass SID and OWNER as parameters to the program:
funct_build_parfile() Builds the export.par parameter
file dynamically, based on the information provided in this function. This
function is configured for a full export of the database. To perform a different
type of export (user- or table-level), set the correct parameters.
funct_cleanup() Removes the interim files.
Export Troubleshooting and Status Check
The 'Log' parameter value set in the parameter file will
have detailed information about the status of export. This is a very good place
to start investigating why an export has failed or for related errors.
A single line about the success or failure of export is appended to SID.log
file every time an export is performed. This file is located under the directory
defined by the LOGDIR variable. This file also has the backup
completion time. A separate file is created for each instance. This single file
maintains the history of performed backups, their status, and timing
information. The messages for an export are 'EXPORT_FAIL', if
the export failed, and 'Export Completed successfully', if
the export completes successfully.
The following is an excerpt from a log file:
Split Export
The split export program (see Listing 3.6) performs an export of the
database. Additionally, if the export file is larger than 2GB, the script
compresses the export file and splits into multiple files to overcome the export
limitation of a 2GB file system size. This is the only way to split the export
file prior to Oracle 8i. New features in 8I allow you to split the export file
into multiple files, but it does not compress the files on-the-fly to save
space. The script uses the Unix commands split and compress to
perform splitting and compressing of the files. The functions of the script are
explained in Figure 3.5.
The split export script takes two input parameters—SID and
OWNER. SID is the instance to be backed up, and OWNER
is the Unix account under which Oracle is running.
Export New Features in 8i
In 8i, Oracle introduced two new export parameters called FILESIZE
and QUERY. FILESIZE specifies the maximum file size of each dump file.
This overcomes the 2GB file system limitations of export command operating
systems. By using the QUERY parameter, you can export the subset of a
table data. During an import, when using split export files, you have to specify
the same FILESIZE limit.
Listing 3.6 splitZxport_ux
Split Export Script under Unix Checklist
The checklist of things to verify before the splitZxport is run is
the same as for the export program.
funct_splitcompress_pipe() This function creates two
pipes—one for compressing and another for splitting. The export dump file
is passed to the compress pipe for compression, and the output of the
compress command is passed to the split command for the
split operation. The output of split command is passed to a
file. The split command splits the dump file into pieces of 1000MB.
When the split operation occurs, it appends aa, bb, cc...zz to the name
of the original file to maintain different names for individual pieces.
compress and split are Unix commands.
funct_build_parfile() In building the parameter file, we pass
the pipe name as a filename to the export command. The pipe acts as a
medium to transfer output from one command to another.
Split Import
The split import program (see Listing 3.7) performs an import using the
compressed split export dump files created by the splitZxport program.
The script takes two input parameters—SID and OWNER.
SID is the instance to be backed up, and OWNER is the Unix
account under which Oracle is running.
Listing 3.7 splitZmport_ux
SplitImport Script under Unix Checklist
In the main() function, set the correct values for the
BACKUPDIR, ORATABDIR, and TOOLS variables highlighted
in the import script. The default location of ORATABDIR is different
for each flavor of Unix.
Check for the existence of the SID in the oratab file.
If not already there, you must add the instance.
List all split filenames in the ZFILES variable in the
main() function.
The funct_build_parfile() function builds the parameter file. By
default, it performs a full import. You can modify the settings to perform a
user or table import.
Pass SID and OWNER as parameters to the program:
funct_desplitcompress_pipe() The only trick here is that we need
to split and uncompress the files before we use them as input to import command.
That is accomplished by creating two pipes. Here, we use the cat
command to send output from split files to the split pipe device. The split pipe
device is passed to the uncompress command. The output from the
uncompress command is sent to the Oracle import command.
cat and uncompress are Unix commands. Everything else is the
same as a regular import.
Oracle Software Backup
This section discusses backing up the software directories of Oracle. We have
already discussed how to back up the database. Backing up software is also a
very important part of a backup strategy. The software might not need to be
backed up as often as the database because it does not change quite as often.
But as you upgrade, or before you apply any patches to existing software, it is
important to make a backup copy of it to avoid getting into trouble.
Listing 3.8 contains the script to perform a backup of Oracle software. The
script takes two input parameters—SID and OWNER.
SID is the instance to be backed up, and OWNER is the Unix
account under which Oracle is running.
Listing 3.8 OraSoftware_ux
Oracle Software Backup Script under Unix Checklist
In the main function, set correct values for BACKUPDIR,
ORATABDIR, and TOOLS variables highlighted in the software
backup script. The default location of ORATABDIR is different for each
flavor of Unix.
Check for the existence of the SID in the oratab file.
If not already there, you must add the instance.
If your Oracle software directory structure does not follow OFA
guidelines, set ORA_BASE and ORA_HOME manually in
funct_get_vars().
Pass SID and OWNER as parameters to the program:
funct_software_bkup() This function tars the software
directories of ORACLE_HOME and ORACLE_BASE and compresses the
output using the compress command. Here, we are assuming that the
Oracle software is installed using OFA (optimal Flexible Architecture)
guidelines. If not, you have to manually set ORA_BASE and
ORA_HOME in the funct_get_vars() function.
'nohup' commands submit the tar command at the
server.
main() If the database is running, it shuts down the database
and starts backing up the software directories. When the backup is complete, it
restarts the database.
Troubleshooting and status check:
The important thing here is that the backup log file defined by
BACKUPLOGFILE contains detailed information about each step of the
backup process. This is a very good place to start investigating why a backup
has failed or for related errors. This file will also have the start and end
time of backup.
A single line about the success or failure of backup is appended to
SID.log file every time backup is performed. This file is located under
the directory defined by the LOGDIR variable. The messages for a
software backup are 'SOFTWAREBACKUP_FAIL', if the software
backup failed, and 'Software Backup Completed',
successfully', if the backup completes successfully.
Restoring Oracle Software
The steps to restore the software are as follows:
Shutdown database.
Use restore file from the backup to restore the directories.
Start up the database.
The restore command in the restore file first does a zcat
(uncompress and cat) of the output file and passes it to
tar for extraction. For example,
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.
){kind=link}