BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

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 ]

do

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

     fi

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

fi

# 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

fi

# 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

fi

# 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

fi

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.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

   
 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter