Oracle backup and recovery scripts, part III: Backup and recovery under Windows NT

Products & Services‎ > ‎Oracle‎ > ‎Backup & Recovery‎ > ‎

This section presents and explains the scripts for taking a backup and recovering a database in the Windows NT environment. We use the DOS Shell batch programming techniques to automate the backup process. After the backup is complete, it is important to check the backup status by reviewing log and error files generated by the scripts.

Cold Backup

Listing 3.9 performs a cold backup of a database under the Windows NT environment. The cold backup script takes SID, the instance to be backed up, as the input parameter. The general steps to write a backup script in Unix and Windows NT are the same. The only difference is that we will be using commands that are understood by Windows NT. Figure 3.6 shows the functionality of a cold backup program under Windows NT. Each box represents a corresponding section in the program. For example, the Parameter Checking section checks for the necessary input parameters and also checks for the existence of the backup directories.

Listing 3.9 `coldbackup_nt`.bat

@echo off
REM ##############################################################
REM PROGRAM NAME: coldbackup_nt.bat

REM PURPOSE: This utility performs cold backup of
REM        the database on Windows NT
REM USAGE: c:\>coldbackup_nt.bat SID 

REM INPUT PARAMETERS: SID (Instance name)
''
REM ###############################################################

REM ::::::::::::::::::::Begin Declare Variables Section

 set ORA_HOME=c:\oracle\ora81\bin
 set CONNECT_USER="/ as sysdba"
 set ORACLE_SID=%1
 set BACKUP_DIR=c:\backup\%ORACLE_SID%\cold
 set INIT_FILE=c:\oracle\admin\orcl\pfile\init.ora set 
TOOLS=c:\oracomn\admin\my_dba
 set LOGDIR=%TOOLS%\localog
 set LOGFILE=%LOGDIR%\%ORACLE_SID%.log

 set CFILE=%BACKUP_DIR%\log\coldbackup.sql
 set ERR_FILE=%BACKUP_DIR%\log\cerrors.log
 set LOG_FILE=%BACKUP_DIR%\log\cbackup.log
 set BKP_DIR=%BACKUP_DIR%

REM :::::::::::::::::::: End Declare Variables Section

REM :::::::::::::::::::: Begin Parameter Checking Section 

if "%1" == " goto usage

REM Create backup directories if already not exist 
if not exist %BACKUP_DIR%\data  mkdir %BACKUP_DIR%\data
if not exist %BACKUP_DIR%\control  mkdir %BACKUP_DIR%\control
if not exist %BACKUP_DIR%\redo mkdir %BACKUP_DIR%\redo
if not exist %BACKUP_DIR%\log mkdir %BACKUP_DIR%\log
if not exist %LOGDIR%        mkdir %LOGDIR%

REM Check to see that there were no create errors 
if not exist %BACKUP_DIR%\data goto backupdir
if not exist %BACKUP_DIR%\control goto backupdir
if not exist %BACKUP_DIR%\redo goto backupdir
if not exist %BACKUP_DIR%\log goto backupdir

REM Deletes previous backup. Make sure you have it on tape.
del/q  %BACKUP_DIR%\data\* 
del/q  %BACKUP_DIR%\control\*
del/q  %BACKUP_DIR%\redo\* 
del/q %BACKUP_DIR%\log\*

echo. > %ERR_FILE%
echo. > %LOG_FILE%
(echo Cold Backup started & date/T & time/T) >> %LOG_FILE%

echo Parameter Checking Completed >> %LOG_FILE%
REM :::::::::::::::::::: End Parameter Checking Section 

REM :::::::::::::::::::: Begin Create Dynamic files Section 
echo.    >%CFILE%
echo set termout off  heading off  feedback off     >>%CFILE%
echo set linesize 300 pagesize 0    >>%CFILE%
echo set serveroutput on size 1000000    >>%CFILE%
echo.    >>%CFILE%
echo spool %BACKUP_DIR%\log\coldbackup_list.bat  >>%CFILE%
echo.    >>%CFILE%
echo exec dbms_output.put_line('@echo off' );    >>%CFILE%
echo.    >>%CFILE%
echo   exec dbms_output.put_line('REM ******Data files' );  >>%CFILE%
echo   select 'copy '^|^| file_name^|^| '  %BKP_DIR%\data  '  
>>%CFILE%
echo   from dba_data_files order by tablespace_name;  >>%CFILE%
echo.    >>%CFILE%
echo   exec dbms_output.put_line('REM ******Control files' );  >>%CFILE%
echo   select 'copy '^|^| name^|^| '  %BKP_DIR%\control  '   >>%CFILE%
echo   from v$controlfile order by name;     >>%CFILE%
echo.    >>%CFILE%
echo   exec dbms_output.put_line('REM ******Init.ora file ' );  
>>%CFILE%
echo   select ' copy  %INIT_FILE%  %BKP_DIR%\control  '  >>%CFILE%
echo   from dual;    >>%CFILE%
echo exec dbms_output.put_line('exit;');    >>%CFILE%
echo spool off    >>%CFILE%
echo exit    >>%CFILE%

echo Dynamic files Section Completed >> %LOG_FILE%
REM :::::::::::::::::::: End Create Dynamic files Section 


REM :::::::::::::::::::: Begin ColdBackup Section 

 %ORA_HOME%\sqlplus -s %CONNECT_USER% @%CFILE%
 %ORA_HOME%\sqlplus -s %CONNECT_USER% @shutdown_i_nt.sql
 %ORA_HOME%\sqlplus -s %CONNECT_USER% @startup_r_nt.sql
 %ORA_HOME%\sqlplus -s %CONNECT_USER% @shutdown_n_nt.sql

REM Copy the files to backup location
 start/b %BACKUP_DIR%\log\coldbackup_list.bat 1>> %LOG_FILE% 2>> %ERR_FILE%
 %ORA_HOME%\sqlplus -s %CONNECT_USER% @startup_n_nt.sql

(echo ColdBackup Completed Successfully & date/T & time/T) >> %LOG_FILE%
(echo ColdBackup Completed Successfully & date/T & time/T) >> %LOGFILE%
goto end

REM :::::::::::::::::::: End ColdBackup Section 


REM :::::::::::::::::::::::::::: Begin Error handling section

:usage
 echo Error, Usage: coldbackup_nt.bat SID
 goto end

:backupdir
 echo Error creating Backup directory structure >> %ERR_FILE%
 (echo COLDBACKUP_FAIL:Error creating Backup directory structure 
 & date/T & time/T) >> %LOGFILE%
REM :::::::::::::::::::: End Error handling section

REM :::::::::::::::::::: Cleanup Section
:end
set ORA_HOME=
set ORACLE_SID=
set CONNECT_USER=
set BACKUP_DIR=
set INIT_FILE=
set CFILE=
set ERR_FILE=
set LOG_FILE=
set BKP_DIR=

Cold Backup Script for Windows NT Checklist

Check to see that ORA_HOME, BACKUP_DIR, and TOOLS are set to correct values according to your directory structure. These variables are highlighted in the script.
Verify that CONNECT_USER is set to correct the username and password.
Define the INIT_FILE variable to the location of the Init.ora file.
Be sure that the user running the program has Write access to backup directories.
When you run the program, pass SID as a parameter.

Cold Backup under Windows NT Troubleshooting and Status Check

Backup log files defined by LOG_FILE contain 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. ERR_FILE has error information.

A single line about the success or failure of backup is appended to the SID.logfile every time a backup is performed. This file is located under the directory defined by the LOGDIR variable. The messages for a cold backup are'COLDBACKUP_FAIL', if the cold backup failed, and 'Cold Backup Completed successfully', if the backup completes successfully.

You can schedule automatic backups using the 'at' command, as shown in the following:

at  23:00 "c:\backup\coldbackup_nt.bat ORCL"
Runs at 23:00 hrs on current date.

at 23:00 /every:M,T,W,Th,F "c:\backup\coldbackup_nt.bat ORCL "

This command runs a backup at 23:00 hours every Monday, Tuesday, Wednesday, Thursday, and Friday.

The "Create Dynamic Files" section in the coldbackup_nt.bat program creates the coldbackup.sql file (see Listing 3.10) under the log directory.coldbackup.sql is called from coldbackup_nt.bat and generates a list of data, control, and redo log files to be backed up from the database. A samplecoldbackup.sql is shown in Listing 3.10 for your understanding. The contents of this file are derived based on the structure of the database.

Listing 3.10 `coldbackup.sql`

set termout off  heading off  feedback off
set linesize 300 pagesize 0
set serveroutput on size 1000000

spool c:\backup\orcl\cold\log\coldbackup_list.bat

 exec dbms_output.put_line('@echo off' );

   exec dbms_output.put_line('REM ******Data files' );
   select 'copy '|| file_name|| '  c:\backup\orcl\cold\data   '
   from dba_data_files order by tablespace_name;

   exec dbms_output.put_line('REM ******Control files' );
   select 'copy '|| name|| '  c:\backup\orcl\cold\control   '
   from v$controlfile order by name;

  exec dbms_output.put_line('REM ******Init.ora file ' );
  select ' copy  c:\oracle\admin\orcl\pfile\init.ora
c:\backup\orcl\cold\control '
  from dual;
exec dbms_output.put_line('exit;');
spool offexit

When the coldbackup.sql file is called from the coldbackup_nt.bat program, it spools output to the coldbackup_list.bat DOS batch file (see Listing 3.11). This file has the commands necessary for performing the cold backup.

This is only a sample file. Note that in the contents of file data, control, redo log, and Init.ora files are copied to respective backup directories.

Listing 3.11 `coldbackup_list.bat`

@echo off

REM ******Data files
copy C:\ORADATA\DSGN01.DBF    c:\backup\orcl\cold\data
copy C:\ORADATA\INDX01.DBF    c:\backup\orcl\cold\data
copy C:\ORADATA\OEM01.DBF     c:\backup\orcl\cold\data 
copy C:\ORADATA\RBS01.DBF     c:\backup\orcl\cold\data
copy C:\ORADATA\SYSTEM01.DBF c:\backup\orcl\cold\data
copy C:\ORADATA\TEMP01.DBF    c:\backup\orcl\cold\data
copy C:\ORADATA\USERS01.DBF   c:\backup\orcl\cold\data


REM ******Control files
copy C:\ORADATA\CONTROL01.CTL c:\backup\orcl\cold\control
copy C:\ORADATA\CONTROL02.CTL c:\backup\orcl\cold\control


REM ******Init.ora file
copy c:\oracle\admin\orcl\pfile\init.ora c:\backup\orcl\cold\control
exit;

Hot Backup

The hot backup program (see Listing 3.12) performs a hot backup of a database under the Windows NT environment. The hot backup script takes SID, the instance to be backed up, as the input parameter.

Listing 3.12 `hotbackup_nt.bat`

@echo off
REM #####################################################################
REM PROGRAM NAME: hotbackup_nt.bat

REM PURPOSE:  This utility performs hot backup of
REM the database on Windows NT
REM USAGE: c:\>hotbackup_nt.bat SID 

REM INPUT PARAMETERS: SID (Instance name)
''
REM #####################################################################

REM :::::::::::::::::::: Begin Declare Variables Section 

 set ORA_HOME=c:\oracle\ora81\bin
 set CONNECT_USER="/ as sysdba"
 set ORACLE_SID=%1
 set BACKUP_DIR=c:\backup\%ORACLE_SID%\hot
 set INIT_FILE=c:\oracle\admin\orcl\pfile\init.ora
 set ARC_DEST=c:\oracle\oradata\orcl\archive

 set TOOLS=c:\oracomn\admin\my_dba
 set LOGDIR=%TOOLS%\localog
 set LOGFILE=%LOGDIR%\%ORACLE_SID%.log

 set HFILE=%BACKUP_DIR%\log\hotbackup.sql
 set ERR_FILE=%BACKUP_DIR%\log\herrors.log
 set LOG_FILE=%BACKUP_DIR%\log\hbackup.log
 set BKP_DIR=%BACKUP_DIR%
REM :::::::::::::::::::: End Declare Variables Section 

REM :::::::::::::::::::: Begin Parameter Checking Section 

if "%1" == " goto usage

REM   Create backup directories if already not exist 
if not exist %BACKUP_DIR%\data  mkdir %BACKUP_DIR%\data
if not exist %BACKUP_DIR%\control  mkdir %BACKUP_DIR%\control
if not exist %BACKUP_DIR%\arch mkdir %BACKUP_DIR%\arch
if not exist %BACKUP_DIR%\log mkdir %BACKUP_DIR%\log
if not exist %LOGDIR%        mkdir %LOGDIR%

REM  Check to see that there were no create errors 
if not exist %BACKUP_DIR%\data goto backupdir
if not exist %BACKUP_DIR%\control goto backupdir
if not exist %BACKUP_DIR%\arch goto backupdir
if not exist %BACKUP_DIR%\log goto backupdir

REM Deletes previous backup. Make sure you have it on tape.
del/q  %BACKUP_DIR%\data\*
del/q  %BACKUP_DIR%\control\*
del/q  %BACKUP_DIR%\arch\*
del/q  %BACKUP_DIR%\log\*

echo. > %ERR_FILE%
echo. > %LOG_FILE%
(echo Hot Backup started & date/T & time/T) >> %LOG_FILE%
echo Parameter Checking Completed >> %LOG_FILE%
REM :::::::::::::::::::: End Parameter Checking Section 

REM :::::::::::::::::::: Begin Create Dynamic files Section 
echo.    >%HFILE%
echo set termout off  heading off  feedback off    >>%HFILE%
echo set linesize 300 pagesize 0    >>%HFILE%
echo set serveroutput on size 1000000    >>%HFILE%
echo spool %BACKUP_DIR%\log\hotbackup_list.sql  >>%HFILE%

echo Declare    >>%HFILE%
echo  cursor c1 is select distinct tablespace_name from dba_data_files 
 order by tablespace_name;  >>%HFILE%
echo  cursor c2( ptbs varchar2) is select file_name from dba_data_files
 where tablespace_name = ptbs order by file_name;  >>%HFILE%
echo Begin    >>%HFILE%
echo   dbms_output.put_line('set termout off heading off feedback off');
 >>%HFILE%

echo.     >>%HFILE%
echo   dbms_output.put_line(chr(10) );     >>%HFILE%
echo   dbms_output.put_line('host REM ******Data files' );   >>%HFILE%
echo   for tbs in c1 loop   >>%HFILE%
echo  dbms_output.put_line(' alter tablespace '^|^| tbs.tablespace_name
 ^|^|' begin backup;');  >>%HFILE%
echo       for dbf in c2(tbs.tablespace_name) loop >>%HFILE%
echo         dbms_output.put_line(' host copy '^|^|dbf.file_name^|^|'
  %BKP_DIR%\data 1^>^> %LOG_FILE%  2^>^> %ERR_FILE%'); >>%HFILE%
echo       end loop;    >>%HFILE%
echo    dbms_output.put_line(' alter tablespace '^|^|tbs.tablespace_name 
^|^|' end backup;');  >>%HFILE%
echo   end loop;      >>%HFILE%

echo.     >>%HFILE%
echo    dbms_output.put_line(chr(10) );     >>%HFILE%
echo    dbms_output.put_line('host REM ******Control files ' );
>>%HFILE%
echo  dbms_output.put_line(' alter database backup controlfile to 
  '^|^| ''^|^|'%BKP_DIR% \control\coltrol_file.ctl'^|^|''^|^|';');  
>>%HFILE%
echo  dbms_output.put_line(' alter database backup controlfile to trace;');
    >>%HFILE%

echo.    >>%HFILE%
echo    dbms_output.put_line(chr(10) );    >>%HFILE%
echo    dbms_output.put_line('host REM ******Init.ora file ' );  
>>%HFILE%
echo    dbms_output.put_line(' host copy %INIT_FILE%  %BKP_DIR%\control
 1^>^> %LOG_FILE%  2^>^> %ERR_FILE%');  >>%HFILE%

echo.    >>%HFILE%
echo    dbms_output.put_line(chr(10) );     >>%HFILE%
echo    dbms_output.put_line('host REM ******Archivelog files' ); 
>>%HFILE%
echo    dbms_output.put_line(' alter system switch logfile;'); >>%HFILE% 
echo    dbms_output.put_line(' alter system archive log stop;'); >>%HFILE%
echo    dbms_output.put_line('host move %ARC_DEST%\*  %BKP_DIR%\arch 
   1^>^> %LOG_FILE%  2^>^> %ERR_FILE%' );  >>%HFILE%
echo    dbms_output.put_line(' alter system archive log start;'); 
>>%HFILE%


echo   dbms_output.put_line('exit;');    >>%HFILE%
echo End;    >>%HFILE%
echo /    >>%HFILE%
echo spool off    >>%HFILE%
echo exit;    >>%HFILE%

echo Dynamic files Section Completed >> %LOG_FILE%
REM :::::::::::::::::::: End Create Dynamic files Section 

REM :::::::::::::::::::: Begin HotBackup Section 

%ORA_HOME%\sqlplus -s %CONNECT_USER% @%HFILE%
REM Copy the files to backup location
%ORA_HOME%\sqlplus -s %CONNECT_USER% @%BACKUP_DIR%\log\hotbackup_list.sql

(echo HotBackup Completed Successfully & date/T & time/T) >> %LOG_FILE%
(echo HotBackup Completed Successfully & date/T & time/T) >> %LOGFILE%
goto end

REM :::::::::::::::::::: End HotBackup Section 


REM :::::::::::::::::::: Begin Error handling section

:usage
 echo Error, Usage: hotbackup_nt.bat SID
 goto end

:backupdir
 echo Error creating Backup directory structure >> %ERR_FILE%
 (echo HOTBACKUP_FAIL:Error creating Backup directory structure
& date/T & time/T) >> %LOGFILE%
REM :::::::::::::::::::: End Error handling section

REM :::::::::::::::::::: Cleanup Section
:end
set ORA_HOME=
set ORACLE_SID=
set CONNECT_USER=
set BACKUP_DIR=
set INIT_FILE=
set ARC_DEST=
set HFILE=
set ERR_FILE=
set LOG_FILE=
set BKP_DIR=

Hot backup program functionality can be shown with the similar diagram as for a cold backup. The sections and their purposes in the program are the same as for a cold backup.

Hot Backup Script under Windows NT Checklist

Check to see that ORA_HOME, BACKUP_DIR, and TOOLS are set to the correct values according to your directory structure. These variables are highlighted in the script.
Verify that CONNECT_USER is set to the correct username and password.
Define the INIT_FILE variable to the location of the Init.ora file.
Define the ARC_DEST variable to the location archive destination.
Be sure that the user running the program has Write access to the backup directories.
When you run the program, pass SID as a parameter.

Hot Backup under Windows NT Troubleshooting and Status Check

The backup log file defined by LOG_FILE 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. ERR_FILE has error information.

A single line about the success or failure of backup is appended to the SID.logfile every time a backup is performed. This file is located under the directory defined by the LOGDIR variable. The messages for a hot backup are'HOTBACKUP_FAIL', if a hot backup failed, and 'Hot Backup Completed successfully', if a backup completes successfully.

The "Create Dynamic Files" section, in the hotbackup_nt.bat creates thehotbackup.sql file (see Listing 3.13) under the log directory. This generates a list of tablespaces, data, control, and redo log files from the database. It is called from the hotbackup_nt.bat program.

Listing 3.13 `hotbackup.sql`

set termout off heading off feedback off
set linesize 300 pagesize 0
set serveroutput on size 1000000 
spool c:\backup\orcl\hot\log\hotbackup_list.sql 
Declare
  cursor c1 is select distinct tablespace_name from dba_data_files
 order by tablespace_name;
  cursor c2( ptbs varchar2) is select file_name from dba_data_files 
 where tablespace_name = ptbs order by file_name;
Begin
  dbms_output.put_line('set termout off  heading off  feedback off');

  dbms_output.put_line(chr(10) );
  dbms_output.put_line('host REM ******Data files' );
  for tbs in c1 loop
    dbms_output.put_line(' alter tablespace '|| tbs.tablespace_name ||'
begin backup;');
       for dbf in c2(tbs.tablespace_name) loop
        dbms_output.put_line(' host copy '||dbf.file_name||'
 c:\backup\orcl\hot\data   1>> hbackup.log  2>> herrors.log');
       end loop;
    dbms_output.put_line(' alter tablespace '||tbs.tablespace_name ||
' end backup;');
  end loop;

    dbms_output.put_line(chr(10) );
    dbms_output.put_line('host REM ******Control files ' );
    dbms_output.put_line(' alter database backup controlfile to '||   
              ''||'c:\backup\orcl\hot\control\coltrol_file.ctl
              '||''||';');
    dbms_output.put_line(' alter database backup controlfile to trace;');

    dbms_output.put_line(chr(10) );
    dbms_output.put_line('host REM ******Init.ora file ' ); 
   dbms_output.put_line('host copy 
c:\oracle\admin\orcl\pfile\init.orac:\backup\orcl\hot\control
1>> hbackup.log  2>> herrors.log');

    dbms_output.put_line(chr(10) ); 
    dbms_output.put_line('host REM ******Archivelog files' );
    dbms_output.put_line(' alter system switch logfile;');
    dbms_output.put_line(' alter system archive log stop;');
    dbms_output.put_line('host move c:\oracle\oradata\orcl\archive\*
 c:\backup\orcl\hot\arch   1>> hbackup.log  2>> herrors.log' );
    dbms_output.put_line(' alter system archive log start;');
  dbms_output.put_line('exit;');
End;
/
spool off
exit;

The hotbackup.sql file is called from hotbackup_nt.bat and it spools output to the hotbackup_list.sql SQL file (see Listing 3.14). This file has the commands necessary for performing a hot backup.

This is only a sample file. Note in the file that the data, control, archive log, andInit.ora files are copied to their respective backup directories. First, it puts the tablespace into Backup mode, copies the corresponding files to backup location, and then turns off the Backup mode for that tablespace. This process is repeated for each tablespace, and each copy command puts the status of the copy operation to hbackup.log and reports any errors to the herrors.log file.

Listing 3.14 is generated based on the structure of the database. In a real environment, the database structure changes as more data files or tablespaces get added. Because of this, it is important to generate the backup commands dynamically, as shown in hotbackup_list.sql. It performs the actual backup and is called from hotbackup_nt.bat.

Listing 3.14 `hotbackup_list.sql`

set termout off heading off feedback off

host REM ******Data files
alter tablespace DESIGNER begin backup; host copy C:\ORADATA\DSGN01.DBF
c:backup\orcl\hot\data
1>> hbackup.log  2>> herrors.logalter tablespace DESIGNER end backup;
alter tablespace DESIGNER_INDX begin backup;
host copy C:\ORADATA\DSGN_INDX01.DBF   c:backup\orcl\hot\data
 1>> hbackup.log  2>> herrors.log
alter tablespace DESIGNER_INDX end backup;
alter tablespace INDX begin backup;
host copy C:\ORADATA\INDX01.DBF   c:backup\orcl\hot\data 1>> hbackup.log
2>> herrors.log
alter tablespace INDX end backup;
alter tablespace OEM_REPOSITORY begin backup;
host copy C:\ORADATA\OEMREP01.DBF    c:backup\orcl\hot\data
1>> hbackup.log  2>> herrors.log
alter tablespace OEM_REPOSITORY end backup;

host REM ******Control files
alter database backup controlfile to 'c:\hot\control\coltrol_file.ctl';
alter database backup controlfile to trace;

host REM ******Init.ora file
host copy c:\oracle\admin\orcl\pfile\init.ora  c:backup\orcl\hot\control
 1>> hbackup.log2>> herrors.log

host REM ******Archivelog files
alter system switch logfile;
alter system archive log stop;
host move c:\oracle\oradata\orcl\archive\*  c:\backup\orcl\hot\arch
 1>> hbackup.log  2>> herrors.log
alter system archive log start;
exit;

Export

The export program (see Listing 3.15) performs a full export of the database under a Windows NT environment. The export script takes SID, the instance to be backed up, as the input parameter.

Listing 3.15 `export_nt.bat`

@echo off
REM #####################################################################
REM PROGRAM NAME:        export_nt.bat

REM PURPOSE:   This utility performs a full export of
REM                         database on Windows NT
REM USAGE:                 c:\>export_nt.bat  SID

REM INPUT PARAMETERS: SID (Instance name)
''
REM #####################################################################

REM :::::::::::::::::::: Begin Declare Variables Section

 set ORA_HOME=c:\oracle\ora81\bin
 set ORACLE_SID=%1
 set CONNECT_USER=system/manager
 set BACKUP_DIR=c:\backup\%ORACLE_SID%\export

 set TOOLS=c:\oracomn\admin\my_dba
 set LOGDIR=%TOOLS%\localog
 set LOGFILE=%LOGDIR%\%ORACLE_SID%.log

REM :::::::::::::::::::: End Declare Variables Section 

REM :::::::::::::::::::: Begin Parameter Checking Section

if "%1" == " goto usage

REM Create backup directories if already not exist
if not exist %BACKUP_DIR%  mkdir %BACKUP_DIR%
if not exist %LOGDIR%        mkdir %LOGDIR%


REM Check to see that there were no create errors
if not exist %BACKUP_DIR% goto backupdir

REM Deletes previous backup. Make sure you have it on tape.
del/q  %BACKUP_DIR%\*

REM :::::::::::::::::::: End Parameter Checking Section

REM :::::::::::::::::::: Begin Export Section 

%ORA_HOME%\exp %CONNECT_USER% parfile=export_par.txt
(echo Export Completed Successfully & date/T & time/T) >> %LOGFILE%
goto end

REM :::::::::::::::::::: End Export Section 


REM :::::::::::::::::::: Begin Error handling section

:usage
 echo Error, Usage: coldbackup_nt.bat SID
 goto end

:backupdir
 echo Error creating Backup directory structure
 (echo EXPORT_FAIL:Error creating Backup directory structure
& date/T & time/T) >> %LOGFILE%

REM :::::::::::::::::::: End Error handling section


REM ::::::::::::::::::::Cleanup Section
:end
set ORA_HOME=
set ORACLE_SID=
set CONNECT_USER=
set BACKUP_DIR=

This program performs an export of the database by using the parameter file specified by export_par.txt. In Listing 3.16 is a sample parameter file that performs a full export of the database. You can modify the parameter file to suit to your requirements.

Listing 3.16 `export_par.txt`

file= %BACKUP_DIR%\export.dmp
log= %BACKUP_DIR%\export.log
full=y 
compress=n
consistent=y

Export Script under Windows NT Checklist

Check to see that ORA_HOME and BACKUP_DIR, TOOLS are set to correct values according to your directory structure. These variables are highlighted in the program.
Verify that CONNECT_USER is set to the correct username and password.
Be sure that the user running the program has Write access to the backup directories.
Edit the parameter file to your specific requirements. Specify the full path of the location of your parameter file in the program.
When you run the program, pass SID as a parameter.

Export under Windows NT Troubleshooting and Status Check

The log file specified in the parameter file contains detailed information about each step of the export process. 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 the SID.logfile every time an export is performed. This file is located under the directory defined by the LOGDIR variable. The messages for an export are 'EXPORT_FAIL', if the export failed, and 'Export Completed successfully', if the export completes successfully.

Reader Feedback:

Frank P. writes: Your article on Windows backups for Oracle is incorrect. In a hot backup, Oracle recommends the use of the supplied OCOPY utility, NOT Windows copy. See Metalink note #139327.1. Also, I believe that the scripts are using O/S authentication. This has major security risks and should be noted. Otherwise, good tip.

Source : http://searchoracle.techtarget.com/tip/Oracle-backup-and-recovery-scripts-part-III-Backup-and-recovery-under-Windows-NT

Comments

Menu

Recent site activity