Remote DBA Plans
Remote DBA Service

Oracle Backup and Recovery

Oracle Tips by Burleson Consulting

By now you should be aware that Oracle is a complex, interrelated set of files and executables. With the release of Oracle8i and Oracle9i, it hasn’t gotten any simpler. The database files include data segments, redo logs, rollback segments, control files, bfiles, libraries, LOBs, and system areas. None of these files is a separate entity, but is tightly linked to the others. For instance, the datafiles are repositories for all table data; the datafile structure is controlled by the control file, implemented by the system areas, and maintained by a combination of the executables, redo, and rollback segments. Datafiles reference bfiles that are tied to external procedures stored in libraries, which are referenced in procedures stored in datafiles.

This complexity leads to the requirement of a threefold backup recovery methodology to ensure that data recovery can be made. The threefold recovery methodology consists of:

1. Normal backups using system backups, Oracle Backup Manager, Recovery Manager, or a third-party tool that has been tested against Oracle.

2. Exports and imports.

3. Archive logging of redo logs.

Let’s look at each of these procedures and how they are used. Figure 15.1 shows a basic flowchart for determining your backup strategy.

Figure 15.1 Backup recovery strategy flowchart.

Backups

Normal system backups, referred to as either hot or cold backups, are used to protect the system from media failure. Each can and should be used when required.

Cold Backups

A cold backup, that is, one done with the database in a shutdown state, provides a complete copy of the database that can be restored exactly. The procedure for using a cold backup is as follows:

1. Using the shutdown script(s) provided, shut down the Oracle instance(s) to be backed up.

2. Ensure that there is enough backup media to back up the entire database.

3. Mount the first volume of the backup media (9-track, WORM, 4mm, 8mm, etc.) using the proper operating system mount command. For example, On UNIX:

$ umount /dev/rmt0 /tape1

4. Issue the proper operating system backup command to initiate the backup. On UNIX, the backup command to initiate the backup is as follows:

$ tar -cvf /tape1 /ud*/oracle*/ortest1/*

for all Oracle data, log, and trace files, assuming an OFA installation, where:

tar. Short for tape archiver. This is the default backup command on UNIX. RAW volumes may require “dd.”

-cvf. These arguments tell tar to: c: create a new archive, v: tell us what it is doing, and f: use the device specification that follows (we could have not specified a device, and it would default to the default tape drive).

5. Once the backup is complete, be sure all backup volumes are properly labeled and stored, away from the computer. The final volume is dismounted from the tape drive using the appropriate operating system DISMOUNT command. For example, on UNIX:

$ umount /tape1

6. Restart the Oracle instances using the appropriate startup script(s).

Hot Backups

A hot backup, or one taken while the database is active, can only give a read-consistent copy; it doesn’t handle active transactions. You must ensure that all redo logs archived during the backup process are also backed up. The hot backup differs from the cold backup in that only sections of the database are backed up at one time. This is accomplished by using the ALTER command to modify a tablespace’s status to backup. Be sure that you restore the status to normal once the database is backed up or else redo log mismatch and improper archiving/rollbacks can occur.

While it is quite simple (generally speaking) to do a cold backup by hand, a hot backup can be quite complex, hence should be automated. The automated procedure should then be thoroughly tested on a dummy database for both proper operation and the ability to restore prior to its use on the production database(s).

The following are limitations on hot, or online, backups:

* The database must be operating in ARCHIVELOG mode for hot backups to work.

* Hot backups should only be done during off-hours or low-use periods.

* During the hot backups, the entire block containing a changed record, not just the changed record, is written to the archive log, requiring more archive space for this period.

The hot backup consists of three processes:

1. The tablespace datafiles are backed up.

2. The archived redo logs are backed up.

3. The control file is backed up.

The first two parts have to be repeated for each tablespace in the database. For small databases, this is relatively easy. For large, complex databases with files spread across several drives, this can become a nightmare if not properly automated in operating system-specific command scripts. An example of this type of a backup shell script is shown in Source 15.1.

As you can see, this is a bit more complex than a full cold backup and requires more monitoring than a cold backup. Recovery from this type of backup consists of restoring all tablespaces and logs and then recovering. You only use the backup of the control file if the current control file was also lost in the disaster; otherwise, be sure to use the most current copy of the control file for recovery operations.

Tip: In a number of computer facilities, backups are kept close at hand, sometimes in the same room as the computer. What would happen if a site disaster destroyed the computer room? Not only the hardware, but all of the system backups and your data, could be lost. The point is: Store backups in another building or even totally off-site. This assures that come fire, flood, or typhoon, you should be able to get backup one way or another.

SOURCE 15.1 Example of hot backup script for UNIX KORNE shell.

#**************************************************************

# Name : hot_backup

# Purpose : Perform a hot backup of an Oracle Database

# Use : sh hot_backup

# Limitations : Creates a read-consistent image, but doesn't back

# up in-process transactions

# Revision History:

# Date Who What

# --------- ----------- --------------------------------

# June 1993 K. Loney Featured in Oracle Mag. Article

# 29-Jun-93 M. Ault Modified, commented

# 02-Aug-93 M. Ault Converted to UNIX script

# 03-Aug-93 M. Phillips Added error detection

#****************************************************************

ERROR="FALSE"

LOGFILE="$ORACLE_HOME/adhoc/scripts/hot_back_log"

while [ "$error"=FALSE ]

svrmgrl << ending1

connect internal

alter tablespace system begin backup;

exit

ending1

if ( tar cfv /oracle/backup /data/ORA_SYSTEM_1.DBF )

then

else

ERROR="TRUE";

echo "Tar backup failed for ora_system1.dbf" >$LOGFILE

svrmgrl << ending2

connect internal

alter tablespace system end backup;

exit

ending2

dup_it="tar rv /oracle/backup"

svrmgrl << ending3

connect internal

alter tablespace user_tables begin backup;

exit

ending3

if ( $dup_it /data/ora_user_tables_1.dbf )

then

else

ERROR="TRUE";echo "Tar backup failed for ora_user_tables_1.dbf">>$LOGFILE

fi #we must still end backup for tablespaces

svrmgrl << ending4

connect internal

alter tablespace user_tables end backup;

exit

ending4

# force write of all archive logs

svrmgrl << ending5

connect internal

alter system switch logfile;

archive log all;

exit

ending5

if ( cp /usr/oracle/oracle7/db_example.archives/*.arc *.oldarc )

then

else

ERROR="TRUE";echo "Copy of archive logs failed">>$LOGFILE

# Now backup a control file

svrmgrl << ending6

connect internal

alter database example

backup controlfile to

'/usr/oracle/oracle7/db_example/ora_control.bac

reuse;

exit

ending6

if ( $dup_it /usr/oracle/oracle7/db_example/ora_control.bac )

then

else

ERROR="TRUE";echo "Tar backup failed for control file">>$LOGFILE

# now backup all archive logs

if ( $dup_it /usr/oracle/oracle7/db_example.archives/*.oldarc )

then

else

ERROR="TRUE";echo "Tar backup failed for archive files">>$LOGFILE

# Now delete logs

if ( rm /usr/m_oracle/oracle7/db_examples.archives/*.oldarc;* )

then

ERROR="TRUE"

else

ERROR="TRUE";echo "Delete of archive files failed">>$LOGFILE

done

exit

done

One problem with a canned script of the type shown for UNIX hot backup is that it doesn’t automatically reconfigure itself to include new tablespaces—or redo logs. The script in Source 15.2 is an example of how to let Oracle build its own hot backup script using dynamic SQL and the data dictionary. The output from the script for my test database is shown in Source 15.3.

SOURCE 15.2 Example of script to generate a hot backup script on UNIX.

REM Script to create a hot backup script on UNIX

REM Created 6/23/98 MRA

REM

create table bu_temp (line_no number,line_txt varchar2(2000))

storage (initial 1m next 1m pctincrease 0);

truncate table bu_temp;

set verify off embedded off lines 1000 termout off long 1000

define dest_dir=&1;

declare

-- Declare cursors

-- Cursor to get all tablespace names

cursor get_tbsp is

select tablespace_name from Remote DBA_tablespaces;

-- cursor to create BEGIN BACKUP command

cursor bbu_com (tbsp varchar2) is

select

'alter tablespace '||tablespace_name||' begin backup;'

from Remote DBA_tablespaces where tablespace_name=tbsp;

-- Cursor to create HOST backup commands

cursor tar1_com (tbsp varchar2) is

select '! /bin/tar cvf - '||file_name

from Remote DBA_data_files where tablespace_name=tbsp

and file_id=(select min(file_id)from Remote DBA_data_files

where tablespace_name=tbsp);

cursor tar2_com (tbsp varchar2) is

select

file_name

from Remote DBA_data_files where tablespace_name=tbsp

and file_id>(select min(file_id) from Remote DBA_data_files

where tablespace_name=tbsp);

cursor tar3_com (tbsp varchar2) is

select '! /bin/tar cvf - '||file_name

from Remote DBA_data_files where tablespace_name=tbsp

and file_id=(select min(file_id)from Remote DBA_data_files

where tablespace_name=tbsp);

cursor comp_com (tbsp varchar2) is

select

'|compress -c >&&dest_dir/'||tablespace_name||'_'||to_char(sysdate,'dd_mon_yy')||'.Z'||chr(10)

from Remote DBA_tablespaces where tablespace_name=tbsp;

-- Cursor to create END BACKUP command

cursor ebu_com (tbsp varchar2) is

select

'alter tablespace '||tablespace_name||' end backup;' from

Remote DBA_tablespaces

where tablespace_name=tbsp;

-- Cursor to create redo log HOST backup commands

cursor tar1_rdo is

select '! /bin/tar cvf - '

from dual;

cursor tar2_rdo is

select

member||' '

from v$logfile;

cursor comp_rdo is

select

'|compress -c >&&dest_dir/redo_logs_'||to_char(sysdate,'dd_mon_yy')||'.Z'||chr(10)

from dual;

-- Temporary variable declarations

tbsp_name varchar2(64);

line_num number:=0;

line_text varchar2(2000);

fetch_text varchar2(2000);

min_value number;

first_tbsp boolean;

temp_var varchar2(128);

-- Begin build of commands into temporary table

begin

-- first, create script header

line_num := line_num+1;

select 'REM Online Backup Script for '||name||' instance'

into line_text from v$database;

insert into bu_temp values (line_num,line_text);

line_num := line_num+1;

select 'REM Script uses UNIX tar format backup commands'

into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num := line_num+1;

select 'REM created on '||to_char(sysdate, 'dd-mon-yyyy hh24:mi')||' by user '||user

into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num := line_num+1;

select 'REM developed by Mike Ault - 2-May-2001'

into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num := line_num+1;

select 'REM Script expects to be fed backup directory location on execution.'

into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num := line_num+1;

select 'REM Script should be re-run anytime physical structure of database altered.'

into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num := line_num+1;

select 'REM '

into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num := line_num+1;

select 'spool &&dest_dir/log/hot_bu'||to_char(sysdate,'dd_mon_yy')||'.log'

into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num := line_num+1;

-- Now get tablespace names and loop through until all are handled

commit;

open get_tbsp;

first_tbsp:=TRUE;

loop

-- Get name

fetch get_tbsp into tbsp_name;

exit when get_tbsp%NOTFOUND;

-- Add comments to script showing which tablespace

select 'REM' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

select 'REM Backup for tablespace '||tbsp_name into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

select 'REM' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

-- Get begin backup command built for this tablespace

open bbu_com (tbsp_name);

fetch bbu_com into line_text;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

close bbu_com;

-- The actual backup commands are per datafile, open cursor and loop

open tar1_com (tbsp_name);

open tar2_com (tbsp_name);

open tar3_com (tbsp_name);

open comp_com (tbsp_name);

min_value:=1;

line_text:=NULL;

loop

if min_value=1

then

if first_tbsp THEN

fetch tar1_com into fetch_text;

select trim(fetch_text) into line_text from dual;

else

fetch tar3_com into fetch_text;

select trim(fetch_text) into line_text from dual;

end if;

else

fetch tar2_com into fetch_text;

exit when tar2_com%NOTFOUND;

select trim(line_text)||' '||trim(fetch_text) into line_text from dual;

end if;

first_tbsp:=FALSE;

min_value:=min_value+1;

end loop;

fetch comp_com into fetch_text;

select trim(line_text)||' '||trim(fetch_text) into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

close tar1_com;

close tar2_com;

close tar3_com;

close comp_com;

-- Build end backup command for this tablespace

open ebu_com(tbsp_name);

fetch ebu_com into line_text;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

close ebu_com;

end loop;

close get_tbsp;

-- Backup redo logs, normally you won't recover redo logs you

-- will use your current redo logs so current SCN information not lost

-- commands just here for completeness

select 'REM' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

select 'REM Backup for redo logs' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

select 'REM Normally you will not recover redo logs' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

select 'REM' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

-- Create host backup commands for all redo logs

open tar1_rdo;

open tar2_rdo;

open comp_rdo;

min_value:=1;

loop

if min_value=1

then

fetch tar1_rdo into fetch_text;

select trim(fetch_text) into line_text from dual;

else

fetch tar2_rdo into fetch_text;

select trim(line_text)||' '||trim(fetch_text) into line_text from dual;

exit when tar2_rdo%NOTFOUND;

end if;

min_value:=min_value+1;

end loop;

fetch comp_rdo into fetch_text;

select trim(line_text)||' '||trim(fetch_text) into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

close tar1_rdo;

close tar2_rdo;

close comp_rdo;

-- Now get all archive logs, performing a switch to be sure all

-- required archives are written out

select 'REM' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

select 'REM Backup for archive logs' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

select 'REM' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

select 'alter system switch logfile;' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

select 'alter system archive log all;' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

-- The next command builds the actual backup command based on the

-- value of the log_archive_dest initialization parameter, it looks for the

-- last right square bracket in the name and just uses that section with

-- a wildcard

temp_var:=null;

select substr (value,1,instr(value,'/',-1,1)) into temp_var

from v$parameter where name='log_archive_dest';

if temp_var is not null

then

select '! compress '||substr (value,1,instr(value,'/',-1,1))||'/*'

into line_text from v$parameter where name='log_archive_dest';

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

select '! tar cvf - '||substr (value,1,instr(value,'/',-1,1))||'/*.Z'||

'|compress -c >&&dest_dir/'||

substr (value,instr(value,'/',-1,1)+1,length(value))||'_'||to_char(sysdate,'dd_mon_yy')||'.Z'

into line_text from v$parameter where name='log_archive_dest';

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

else

select 'REM no log_archive_dest specified' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

end if;

temp_var:=null;

select substr (value,10,instr(value,'/',-1,1)) into temp_var

from v$parameter where name='log_archive_dest_1';

if temp_var is not null

then

select '! compress '||substr (value,10,instr(value,'/',-1,1))||'/*'

into line_text from v$parameter where name='log_archive_dest_1';

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

select '! tar cvf - '||substr (value,10,instr(value,'/',-1,1))||'/*.Z'||

'|compress -c >&&dest_dir/'||

substr (value,instr(value,'/',-1,1)+1,length(value))||'_'||to_char(sysdate,'dd_mon_yy')||'.Z'

into line_text from v$parameter where name='log_archive_dest_1';

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

else

select 'REM no log_archive_dest_1 specified' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

end if;

-- Next, backup a control file just to be sure

-- we have a good one available that is current with this backup

select 'alter database backup controlfile to '||chr(39)||'&&dest_dir'||'/ora_cnbkp_'||to_char(sysdate,'dd_mon_yy')||'.bac' ||chr(39)||';'

into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

select 'spool off'||chr(10) into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

commit;

end;

rem

rem Now generate output based on bu_temp table contents

rem

set verify off feedback off heading off termout off pages 0

set embedded on lines 1000

column line_no noprint

column dbname new_value db noprint

select value dbname from v$parameter where name='db_name';

spool rep_out/&db/thot_bu.sql

select * from bu_temp order by line_no;

spool off

rem directory syntax for UNIX

rem

! sed '1,$ s/ *$//g' rep_out/&db/thot_bu.sql>rep_out/&db/hot_bu.sql

rem

drop table bu_temp;

set verify on feedback on heading on termout on pages 22

set embedded off lines 80

clear columns

undef dest_dir

SOURCE 15.3 Example output from the hot backup script generator.

REM Online Backup Script for AULTDB1 instance

REM Script uses UNIX tar format backup commands

REM created on 27-nov-2001 11:21 by user SYSTEM

REM developed by Mike Ault - 2-May-2001

REM Script expects to be fed backup directory location on execution.

REM Script should be re-run anytime physical structure of database altered.

REM

spool /opt/backup/aultdb1/log/hot_bu27_nov_01.log

REM

REM Backup for tablespace SYSTEM

REM

alter tablespace SYSTEM begin backup;

! /bin/tar cvf - /ora1/ORACLE/ORADATA/AULTDB1/SYSTEM01.DBF |compress -c >/opt/backup/aultdb1/SYSTEM_27_nov_01.Z

alter tablespace SYSTEM end backup;

REM

REM Backup for tablespace RBS

REM

alter tablespace RBS begin backup;

! /bin/tar cvf - /ora2/ORACLE/ORADATA/AULTDB1/RBS01.DBF |compress -c >/opt/backup/aultdb1/RBS_27_nov_01.Z

alter tablespace RBS end backup;

REM

REM Backup for tablespace USERS

REM

alter tablespace USERS begin backup;

! /bin/tar cvf - /ora3/ORACLE/ORADATA/AULTDB1/USERS01.DBF |compress -c >/opt/backup/aultdb1/USERS_27_nov_01.Z

alter tablespace USERS end backup;

REM

REM Backup for tablespace TEMP

REM

alter tablespace TEMP begin backup;

! /bin/tar cvf - /ora4/ORACLE/ORADATA/AULTDB1/TEMP01.DBF |compress -c >/opt/backup/aultdb1/TEMP_27_nov_01.Z

alter tablespace TEMP end backup;

REM

REM Backup for tablespace TOOLS

REM

alter tablespace TOOLS begin backup;

! /bin/tar cvf - /ora5/ORACLE/ORADATA/AULTDB1/TOOLS01.DBF |compress -c >/opt/backup/aultdb1/TOOLS_27_nov_01.Z

alter tablespace TOOLS end backup;

REM

REM Backup for tablespace INDX

REM

alter tablespace INDX begin backup;

! /bin/tar cvf - /ora5/ORACLE/ORADATA/AULTDB1/INDX01.DBF |compress -c >/opt/backup/aultdb1/INDX_27_nov_01.Z

alter tablespace INDX end backup;

REM

REM Backup for tablespace DRSYS

REM

alter tablespace DRSYS begin backup;

! /bin/tar cvf - /ora1/ORACLE/ORADATA/AULTDB1/DR01.DBF |compress -c >/opt/backup/aultdb1/DRSYS_27_nov_01.Z

alter tablespace DRSYS end backup;

REM

REM Backup for tablespace PERFSTAT

REM

alter tablespace PERFSTAT begin backup;

! /bin/tar cvf - /ora1/ORACLE/ORADATA/AULTDB1/PERFSTAT.DBF |compress -c >/opt/backup/aultdb1/PERFSTAT_27_nov_01.Z

alter tablespace PERFSTAT end backup;

REM

REM Backup for tablespace TEST_2K

REM

alter tablespace TEST_2K begin backup;

! /bin/tar cvf - /ora2/ORACLE/ORADATA/AULTDB1/TEST_2K.DBF |compress -c >/opt/backup/aultdb1/TEST_2K_27_nov_01.Z

alter tablespace TEST_2K end backup;

REM

REM Backup for redo logs

REM Normally you will not recover redo logs

REM

! /bin/tar cvf - /ora6/ORACLE/ORADATA/AULTDB1/REDO011.LOG /ora6/ORACLE/ORADATA/AULTDB1/REDO032.LOG /ora7/ORACLE/ORADATA/AULTDB1/REDO021.LOG

/ora7/ORACLE/ORADATA/AULTDB1/REDO012.LOG /ora8/ORACLE/ORADATA/AULTDB1/REDO031.LOG /ora8/ORACLE/ORADATA/AULTDB1/REDO022.LOG |compress -c >/opt/backup/aultdb1/redo_logs_27_nov_01.Z

REM

REM Backup for archive logs

REM

alter system switch logfile;

alter system archive log all;

host compress /ora9/ORACLE/ORADATA/AULTDB1/ARCHIVE/*

host tar cvrf - *.Z|compress>/tape1/_25_may_99.Z

alter database backup controlfile to '/opt/backup/aultdb1/ora_cnbkp_27_nov_01.bac';

spool off

Similar scripts are provided on the Wiley Web site for both OpenVMS and NT. You will need to verify that the target directories exist, or you will have to modify the scripts before running them. The NT script assumes a backup staging area is being used, which is then backed up to tape.

I suggest generating the backup script at the same time as the recovery script. Source 15.4 shows an example of a recovery script generator for NT.

SOURCE 15.4 Example of recovery script generator for NT.

REM Script to create a hot backup recovery script on NT using ocopy

REM Created 6/23/98 MRA

REM

create table bu_temp (line_no number,line_txt varchar2(2000));

truncate table bu_temp;

set verify off embedded off esc ^

REM &&ora_home &&dest_dir

column dup new_value dup_it noprint

select ''||chr(39)||'&&ora_home'||'\ocopy '||chr(39)||'' dup

from dual;

declare

-- Declare cursors

-- Cursor to get all tablespace names

cursor get_tbsp is

select tablespace_name from Remote DBA_tablespaces;

-- Cursor to create recovery commands

cursor rec_com (tbsp varchar2) is

select

&&dup_it||' '||'&&dest_dir'||'\datafiles\'||tbsp||file_id||'.bck '||file_name

from Remote DBA_data_files where tablespace_name=tbsp;

-- Cursor to create redo log recovery commands

cursor rec_rdo (num number) is

select

&&dup_it||

' '||'&&dest_dir'||'\logs'||substr(member,instr(member,'\LOG',2,1),instr(member,'.',1,1))||' '||

member

from v$logfile order by group#;

-- Temporary variable declarations

tbsp_name varchar2(64);

line_num number:=0;

line_text varchar2(2000);

num number:=0;

-- Begin build of commands into temporary table

begin

-- first, create script header

line_num := line_num+1;

select 'REM Recovery Script for '||name||' instance'

into line_text from v$database;

insert into bu_temp values (line_num,line_text);

line_num := line_num+1;

select 'REM Script uses ocopy - NT format backup commands'

into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num := line_num+1;

select 'REM created on '||to_char(sysdate, 'dd-mon-yyyy hh24:mi')||' by user '||user

into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num := line_num+1;

select 'REM developed for RevealNet by Mike Ault - DMR Consulting 15-Dec-1998'

into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num := line_num+1;

select 'REM '

into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num := line_num+1;

select 'REM Script should be re-run anytime physical structure of database altered.'

into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num := line_num+1;

select 'REM '

into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num := line_num+1;

-- Now get tablespace names and loop through until all are handled

open get_tbsp;

loop

-- Get name

fetch get_tbsp into tbsp_name;

exit when get_tbsp%NOTFOUND;

-- Add comments to script showing which tablespace

select 'REM' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

select 'REM Recovery for tablespace '||tbsp_name into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

select 'REM' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

-- The actual recovery commands are per datafile, open cursor and loop

open rec_com (tbsp_name);

loop

fetch rec_com into line_text;

exit when rec_com%NOTFOUND;

line_num:=line_num+1;

insert into bu_temp values (line_num,line_text);

end loop;

close rec_com;

end loop;

close get_tbsp;

-- Recover redo logs, normally you won't recover redo logs you

-- will use your current redo logs so current SCN information not lost

-- commands just here for completeness uncomment commands below to

-- enable redo log recovery (not advised)

select 'REM' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

select 'REM Recovery for redo logs' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

select 'REM Normally you will not recover redo logs' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

select 'REM' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

-- Create host backup commands for all redo logs

/*open rec_rdo(num);

loop

fetch rec_rdo into line_text;

exit when rec_rdo%NOTFOUND;

num:=num+1;

line_num:=line_num+1;

insert into bu_temp values (line_num,line_text);

end loop;

close rec_rdo;*/

-- Now recover all archive logs

line_num:=line_num+1;

select 'REM' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

select 'REM Recovery for archive logs' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

select 'REM' into line_text from dual;

insert into bu_temp values (line_num,line_text);

line_num:=line_num+1;

-- The next command builds the actual recovery command based on the

-- value of the log_archive_dest initialization parameter, it looks for the

-- last right square bracket in the name and just uses that section with

-- a wildcard

select &&dup_it||' '||'&&dest_dir'||'\archives\*.* '||value||'\*.*'

into line_text from v$parameter where name='log_archive_dest';

line_num:=line_num+1;

insert into bu_temp values (line_num,line_text);

end;

rem

rem Now generate output based on bu_temp table contents

rem

set verify off feedback off heading off termout off pages 0

set embedded on lines 132

column db_name new_value db noprint

column line_no noprint

select name db_name from v$database;

spool rep_out\&&db\rec_db.bat

select * from bu_temp order by line_no;

spool off

rem

rem get rid of bu_temp table

rem

drop table bu_temp;

set verify on feedback on heading on termout on pages 22

set embedded off lines 80 esc \

clear columns

undef ora_home

undef dest_dir

exit

A script for UNIX is also provided on the Wiley Web site. Once you have generated the scripts to generate the online backup and recovery files, document them. The next section presents an example of the documentation procedure for the NT online backup and recovery scripts.

Example of Documentation Procedure for NT Online Backup and Recovery Scripts

This section shows a sample set of procedures for using the NT Oracle hot backup and recovery scripts.

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.