Home > Articles > Data > Oracle

Oracle DBA: Backup and Recovery Scripts

Dec 27, 2002

📄 Contents

␡

⎙ Print

< Back Page 4 of 7 Next >

This chapter is from the book 

Oracle DBA Automation Scripts

Learn More Buy

Backup and Recovery under Windows NT

Before reading through this section, I strongly recommend that you go through the Windows NT programming section in Chapter 2. This section presents and explains the scripts for taking a backup and recovering a database in the Windows NT environment. Here, 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.

Figure 3.6 Functions in cold backup script for Windows NT.

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.log file 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 sample coldbackup.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.log file 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 the hotbackup.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, and Init.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.log file 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.

< Back Page 4 of 7 Next >

🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Privacy Notice

Overview

Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security

Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children

This site is not directed to children under the age of 13.

Marketing

Pearson may send or direct marketing communications to users, provided that

Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
Such marketing is consistent with applicable law and Pearson's legal obligations.
Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out

Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

As required by law.
With the consent of the individual (or their parent, if the individual is a minor)
In response to a subpoena, court order or legal process, to the extent permitted or required by law
To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
To investigate or address actual or suspected fraud or other illegal activities
To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links

This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020

Email Address