=================================================================================================================================================
Rman Batch Job:
1.weekly.bat
2.hemora.bat
3.rman_Backup.bat
1.weekly.bat
v:
call v:\hemora.bat
call v:\rman_weeklyjob.bat
2.hemora.bat
PATH =C:\app\Administrator\product\11.1.0\db_1\BIN
3.rman_Backup.bat
REM +--------------------------------------------------------------------------+
REM | REMOVE OLD LOG AND RMAN COMMAND FILES. |
REM +--------------------------------------------------------------------------+
set TODAY=%date%
set TODAY=%TODAY:/=-%
set MyLogFile=V:\%ToDAY%_Daily_Backup.log
set CMDFILE=V:\Rman_backup.rcv
del /q %CMDFILE%
REM +--------------------------------------------------------------------------+
REM | WRITE RMAN COMMAND SCRIPT. |
REM +--------------------------------------------------------------------------+
echo backup database plus archivelog delete input; > %CMDFILE%
echo crosscheck backup of database; >> %CMDFILE%
echo crosscheck backup of controlfile; >> %CMDFILE%
echo crosscheck archivelog all; >> %CMDFILE%
echo delete noprompt force obsolete;>> %CMDFILE%
echo delete force noprompt expired backup of database; >> %CMDFILE%
echo delete force noprompt expired backup of controlfile; >> %CMDFILE%
echo delete force noprompt expired archivelog all; >> %CMDFILE%
echo exit; >> %CMDFILE%
REM +--------------------------------------------------------------------------+
REM | PERFORM RMAN BACKUP. |
REM +--------------------------------------------------------------------------+
rman target sys/xxx@oratest nocatalog cmdfile=%CMDFILE% msglog=%MyLogFile%
Hot Backup Batch Job:
REM ==========================================================================
REM Script# 1
REM This is the main backup script to run with Windows Scheduler / at command
REM ==========================================================================
call v:\hemora.bat
set dbconn=sys/xxxx@oratest as sysdba
set admdir=V:\admin\daily\
set logdir=V:\admin\daily\out\
set oradir1=D:\ORADATA\ORATEST\ (datafile location)
set oradir2=G:\ORADATA\ORATEST\ (undo,tem location)
set oradir3=V:\ArchiveLog\ORATEST\ (archivelog location)
set orabckdir=V:\Hot_Backup\ (hotbackup temporary storage)
REM set orabckdir1=V:\Hot_Backup\c\
REM set orabckdir2=V:\Hot_Backup\d\
REM set orabckdir3=V:\Hot_Backup\e\
REM
REM save previous copies of backup logs
REM
del %logdir%backup2.txt
ren %logdir%backup1.txt backup2.txt
ren %logdir%backup.txt backup1.txt
echo "ORATEST overnight.bat backup commenced ..." >>%logdir%backup.txt
date /t >>%logdir%backup.txt
time /t >>%logdir%backup.txt
REM
REM Run full export of the database
REM
REM echo "Exporting ORATEST" ... >> %logdir%backup.txt
REM d:\oracle\ora81\bin\exp.exe parfile=d:\oracle\admin\ORATEST\exp\ORATESTexp.par >> %logdir%backup.txt
REM
REM Compress the export file with winzip command line utility
REM May also put a line to remove the old dump files here
REM
REM c:\progra~1\winzip\wzzip.exe e:\oracle\oradata\ORATEST\export\ORATEST_full.zip
REM e:\oracle\oradata\ORATEST\export\ORATEST_full.dmp
REM
REM Append the database export log to backup.txt
REM
REM type d:\oracle\admin\ORATEST\exp\logs\ORATEST_full.log >> %logdir%backup.txt
REM echo "Finished export of ORATEST" >> %logdir%backup.txt
echo "Generating sql scripts for hot ORATEST backup ..." >> %logdir%backup.txt
sqlplus -s %dbconn% @%admdir%sql\backup.sql >> %logdir%backup.txt
echo "Putting ORATEST in backup mode ..." >> %logdir%backup.txt
REM
REM Put all tablespaces in backup mode
REM
sqlplus -s %dbconn% @%admdir%sql\temp\BEGIN_ORATEST_BACKUP.sql >> %logdir%backup.txt
REM
REM Save the database files' location in ORATEST_files.txt
REM
dir %oradir1% > %orabckdir%ORATEST_files.txt
dir %oradir2% >> %orabckdir%ORATEST_files.txt
REM dir %oradir3% >> %orabckdir%ORATEST_files.txt
REM
REM File listing from sqlplus
REM
sqlplus -s %dbconn% @%admdir%sql\dbfiles.sql >> %orabckdir%ORATEST_files.txt
REM
REM Copy Oracle files to backup directory on disk
REM
C:\app\Administrator\product\11.1.0\db_1\BIN\ocopy %oradir1%* %orabckdir% >> %logdir%backup.txt
C:\app\Administrator\product\11.1.0\db_1\BIN\ocopy %oradir2%* %orabckdir% >> %logdir%backup.txt
rem C:\app\Administrator\product\11.1.0\db_1\BIN\ocopy %oradir3%* %orabckdir3% >> %logdir%backup.txt
REM
REM backup filesystems
REM
REM D: drive is backed up last because it has the archived redo logs
REM
date /T >> %logdir%backup.txt
time /T >> %logdir%backup.txt
echo "Putting ORATEST in normal mode" >> %logdir%backup.txt
sqlplus -s %dbconn% @%admdir%sql\temp\END_ORATEST_BACKUP.sql >> %logdir%backup.txt
echo "Copying Archivelog Files ..." >>%logdir%backup.txt
ocopy %oradir3%* %orabckdir% >> %logdir%backup.txt
del /q %admdir%sql\temp\* >> %logdir%backup.txt
sqlplus -s %dbconn% @%admdir%sql\backupcontrolfile.sql >> %logdir%backup.txt
echo "SAMPLEHOST backup finished ..." >>%logdir%backup.txt
date /t >>%logdir%backup.txt
time /t >>%logdir%backup.txt
echo "ZIPPING backup TO D:\hotbackup_zip Location..." >>%logdir%backup.txt
date /t >>%logdir%backup.txt
time /t >>%logdir%backup.txt
set TODAY=%date%
set TODAY=%TODAY:/=-%
REM set NOW=%time:~0,2%%time:~3,2%%time:~6,2%
set ZIPFile=D:\hotbackup_zip\%ToDAY%_hotBackup.zip
zip -r -m -1 %ZIPFILE% V:\Hot_Backup\* >> %logdir%backup.txt
echo "ORATEST Hot backup Zipping finished ..." >>%logdir%backup.txt
date /t >>%logdir%backup.txt
time /t >>%logdir%backup.txt
backup.sql
-- ==========================================================================--
-- backup.sql
-- SQL script to generate scripts to start and end database backup--
-- ==========================================================================
set pagesize 0 echo off feedback off verify off
spool v:\admin\daily\sql\temp\BEGIN_ORATEST_BACKUP.sql
SELECT 'ALTER SYSTEM SWITCH LOGFILE;' FROM dual;
SELECT 'ALTER TABLESPACE ' || tablespace_name || ' BEGIN BACKUP;' FROM dba_tablespaces where contents != 'TEMPORARY'
order by tablespace_name ;
SELECT 'EXIT;'
FROM dual;
spool off
spool v:\admin\daily\sql\temp\END_ORATEST_BACKUP.sql
SELECT 'ALTER TABLESPACE ' || tablespace_name || ' END BACKUP;'
FROM dba_tablespaces where contents != 'TEMPORARY'
order by tablespace_name ;
SELECT 'ALTER SYSTEM SWITCH LOGFILE;'
FROM dual;
SELECT 'EXIT;'
FROM dual;
spool off
EXIT;
=================================================================================================================================================
EXPORT BATCH JOB
@echo off
REM +--------------------------------------------------------------------------+
REM | Hemesh M. Patel |
REM |--------------------------------------------------------------------------|
REM | Copyright (c) 1998-2009 Hemesh M Patel All rights reserved. |
REM |--------------------------------------------------------------------------|
REM | FILE : export_backup_online_full.bat |
REM | CLASS : WINDOWS Shell Scripts |
REM | PURPOSE : Used to perform a logical backup of an Oracle database |
REM | using the traditional export utility. By default, this |
REM | script performs a full and consistent backup of the |
REM | database using a dynamically created parameter file that |
REM | gets written to a temporary directory and run through |
REM | Oracle export using the "parfile" parameter. |
REM | |
REM | ------------- |
REM | IMPORTANT !!! |
REM | ------------- |
REM | Please note that this script makes use of the command |
REM | utility "forfiles.exe" to list and remove obsolete Oracle |
REM | export dump files and log files. Some Microsoft operating |
REM | system environments do not include this utility by default |
REM | (i.e. Windows XP). A copy of "forfiles.exe" can be |
REM | downloaded from: |
REM | |
REM | http://www.idevelopment.info/data/Supporting_Tools/forfiles.exe |
REM | |
REM | ------------------ |
REM | EMAIL CAPABILITIES |
REM | ------------------ |
REM | This script makes use of the BLAT command line utility to |
REM | send the resulting log file to administrators. For more |
REM | information on configuring BLAT to send email from scripts |
REM | in Windows, see the following article: |
REM | |
REM | http://www.idevelopment.info/data/Oracle/DBA_tips/Microsoft_Windows/WINDOWS_5.shtml
REM | |
REM | -------------------------------------------- |
REM | NEW DATA PUMP UTILITY IN ORACLE DATABASE 10g |
REM | -------------------------------------------- |
REM | Oracle Database 10g users (and higher) should consider |
REM | using the new Data Pump utility in place of the original |
REM | Oracle import/export. The original export utility was |
REM | deprecated in Oracle Database 10g Release 2, and is no |
REM | longer supported for general use as of Oracle Database 11g. |
REM | Going forward, Data Pump export (expdp) will be the sole |
REM | supported means of exporting data from the database. The |
REM | original import utility (imp) still ships with Oracle |
REM | Database 10g and Oracle Database 11g in order to support |
REM | import of legacy dump files. The original import utility |
REM | will be supported forever and will provide the means to |
REM | import dump files from earlier releases (release 5.0 and |
REM | later) that were created with the original export (exp). |
REM | Please note that the original export dump files and the new |
REM | Data Pump dump files are "not" compatible. You cannot read |
REM | an original Oracle export dump file with Data Pump and vice |
REM | versa. Neither client can read dump files created by the |
REM | other. |
REM | |
REM | ---------------------------- |
REM | LABEL SECURITY AND VPD USERS |
REM | ---------------------------- |
REM | When exporting data from an Oracle database that contains |
REM | tables protected by Fine-Grained Access Control (FGAC) |
REM | policies, it is possible to receive EXP-00079 and/or |
REM | EXP-00080 warnings. |
REM | |
REM | Note that Fine-Grained Access Control (FGAC) is a synonym |
REM | for Row-Level Security (RLS) and should not be confused |
REM | with FGA which stands for Fine-Grained Auditing! |
REM | |
REM | This warning is thrown by Oracle export when FGAC is |
REM | enabled on a SELECT statement and indicates that Oracle |
REM | export may not export the entire table because FGAC access |
REM | may rewrite the query. There are two methods used to |
REM | resolve this issue and ensure Oracle export is able to |
REM | access and backup all data: |
REM | |
REM | (1) Use the Direct Path clause of Oracle Export or |
REM | (2) Use a database login that has access to all rows |
REM | regardless of existing FGAC policies. |
REM | |
REM | It is highly recommended that the latter option be used |
REM | especially when exporting tables that contain objects and |
REM | LOBs. Rows in tables that contain objects and LOBs will be |
REM | exported using the Conventional Path method, even if Direct |
REM | Path was specified. If this table has FGAC policies |
REM | enabled, the export will then fail with EXP-00008, |
REM | ORA-00604, and ORA-28112 errors. The recommended method is |
REM | to run Oracle export while connected as a user who has |
REM | access to all rows regardless of existing FGAC policies. |
REM | Only the user SYS (all versions) or any user who has the |
REM | EXEMPT ACCESS POLICY privilege (Oracle9i and higher), can |
REM | select all rows. The recommended convention is to export |
REM | using the database user BACKUP_ADMIN which has been granted |
REM | the EXEMPT ACCESS POLICY privilege: |
REM | |
REM | SQL> GRANT exempt access policy TO backup_admin; |
REM | |
REM | Note that this section does not apply to the |
REM | Oracle Database 10g Data Pump Export utility (expdp). An |
REM | export with the new Oracle Database 10g Data Pump Export |
REM | utility will not give any warning message. |
REM | |
REM | PARAMETERS : DBA_USERNAME Database username EXP will use to login |
REM | to the database. This user must have |
REM | the DBA role. |
REM | DBA_PASSWORD Database password EXP will use to login |
REM | to the database. |
REM | TNS_ALIAS TNS connect string to the target |
REM | database. |
REM | NUM_DAYS_TO_KEEP Number of days worth of Oracle exports |
REM | to retain on the file system. |
REM | USAGE : |
REM | |
REM | export_backup_online_full.bat "DBA_USERNAME" "DBA_PASSWORD" "TNS_ALIAS" "NUM_DAYS_TO_KEEP"
REM | |
REM | NOTE : As with any code, ensure to test this script in a |
REM | development environment before attempting to run it in |
REM | production. |
REM +--------------------------------------------------------------------------+
REM +--------------------------------------------------------------------------+
REM | SCRIPT NAME VARIABLES |
REM +--------------------------------------------------------------------------+
set SCRIPT_NAME_NOEXT=export_backup_online_full
set SCRIPT_NAME_FULL=%SCRIPT_NAME_NOEXT%.bat
set SCRIPT_VERSION=3.8
REM +--------------------------------------------------------------------------+
REM | SET / VALIDATE ENVIRONMENT VARIABLES |
REM +--------------------------------------------------------------------------+
set ORA_EXP_DIR=v:\export\oratest
if (%ORA_EXP_DIR%)==() goto ENV_VARIABLES
REM +--------------------------------------------------------------------------+
REM | EMAIL VARIABLES |
REM +--------------------------------------------------------------------------+
set SMTP_SERVER=localhost
set SMTP_PORT=25
set SMTP_EMAIL_TO=hemesh.patel@volume.co.uk
set SMTP_EMAIL_FROM=dba@volume.co.uk
REM +--------------------------------------------------------------------------+
REM | SET START DATE AND TIME ENVIRONMENT VARIABLES |
REM +--------------------------------------------------------------------------+
set START_DATE=%DATE%
set START_TIME=%TIME%
REM +--------------------------------------------------------------------------+
REM | SHOW SIGNON BANNER |
REM +--------------------------------------------------------------------------+
echo.
echo %SCRIPT_NAME_FULL% - Version %SCRIPT_VERSION%
echo Copyright (c) 1998-2009 Hemesh M Patel All rights reserved.
echo.
REM +--------------------------------------------------------------------------+
REM | VALIDATE COMMAND-LINE PARAMETERS |
REM +--------------------------------------------------------------------------+
if (%1)==() goto USAGE
if (%2)==() goto USAGE
if (%3)==() goto USAGE
if (%4)==() goto USAGE
REM +--------------------------------------------------------------------------+
REM | SET DATE AND TIME ENVIRONMENT VARIABLES FOR FILES |
REM +--------------------------------------------------------------------------+
SETLOCAL
FOR /f "tokens=2-4 skip=1 delims=(-)" %%G IN ('echo.^|date') DO (
FOR /f "tokens=2 delims= " %%A IN ('date /t') DO (
SET v_first=%%G
SET v_second=%%H
SET v_third=%%I
SET v_all=%%A
)
)
SET %v_first%=%v_all:~0,2%
SET %v_second%=%v_all:~3,2%
SET %v_third%=%v_all:~6,4%
ENDLOCAL & SET v_year=%yy%& SET v_month=%mm%& SET v_day=%dd%
SET FILEDATE=%v_year%%v_month%%v_day%
SETLOCAL
FOR /f "tokens=*" %%G IN ('time /t') DO set v_time=%%G
SET v_time=%v_time:~0,2%%v_time:~3,2%%v_time:~6,2%
ENDLOCAL & SET v_time=%v_time%
SET v
SET FILETIME=%v_time%
REM +--------------------------------------------------------------------------+
REM | CUSTOM DIRECTORIES |
REM +--------------------------------------------------------------------------+
set CUSTOM_ORACLE_DIR=v:\export\oratest
set CUSTOM_ORACLE_BIN_DIR=%CUSTOM_ORACLE_DIR%\bin
set CUSTOM_ORACLE_LIB_DIR=%CUSTOM_ORACLE_DIR%\lib
set CUSTOM_ORACLE_LOG_DIR=%CUSTOM_ORACLE_DIR%\log
set CUSTOM_ORACLE_OUT_DIR=%CUSTOM_ORACLE_DIR%\out
set CUSTOM_ORACLE_SQL_DIR=%CUSTOM_ORACLE_DIR%\sql
set CUSTOM_ORACLE_TEMP_DIR=%CUSTOM_ORACLE_DIR%\temp
REM +--------------------------------------------------------------------------+
REM | DECLARE GLOBAL VARIABLES |
REM +--------------------------------------------------------------------------+
set DBA_USERNAME=%1%
set DBA_PASSWORD=%2%
set TNS_ALIAS=%3%
set NUM_DAYS_TO_KEEP=%4%
set ERRORS="NO"
SET LOGDIR=%CUSTOM_ORACLE_LOG_DIR%
SET LOGFILE=%LOGDIR%\%SCRIPT_NAME_NOEXT%_%TNS_ALIAS%_%FILEDATE%_%FILETIME%.log
SET LOGFILE_COPY=%LOGDIR%\%SCRIPT_NAME_NOEXT%_%TNS_ALIAS%.log
set EXP_PARAMETER_FILE_NAME=%CUSTOM_ORACLE_TEMP_DIR%\%SCRIPT_NAME_NOEXT%_%TNS_ALIAS%.parfile
set EXP_DUMP_FILE_NAME=%ORA_EXP_DIR%\%SCRIPT_NAME_NOEXT%_%TNS_ALIAS%_%FILEDATE%_%FILETIME%.dmp
set EXP_DUMP_LOG_FILE_NAME=%CUSTOM_ORACLE_TEMP_DIR%\%SCRIPT_NAME_NOEXT%_%TNS_ALIAS%_EXPLOG.log
REM +--------------------------------------------------------------------------+
REM | HOSTNAME VARIABLES |
REM +--------------------------------------------------------------------------+
set HOSTNAME=%COMPUTERNAME%
REM +--------------------------------------------------------------------------+
REM | ORACLE ENVIRONMENT VARIABLES |
REM +--------------------------------------------------------------------------+
set ORACLE_BASE=C:\app\Administrator
set ORACLE_HOME=%ORACLE_BASE%\product\11.1.0\db_1
set ORACLE_SID=%TNS_ALIAS%
set ORACLE_ADMIN_DIR=%ORACLE_BASE%\admin
REM +--------------------------------------------------------------------------+
REM | WRITE HEADER INFORMATION TO CONSOLE AND LOG FILE. |
REM +--------------------------------------------------------------------------+
echo ======================================================
echo - START TIME : %START_DATE% %START_TIME%
echo ======================================================
echo.
echo ===================================================================================
echo COMMON SCRIPT VARIABLES
echo ===================================================================================
echo Script Name : %SCRIPT_NAME_FULL%
echo Script Version : %SCRIPT_VERSION%
echo Begin Date : %START_DATE%
echo Begin Time : %START_TIME%
echo Host Name : %HOSTNAME%
echo Log File Name : %LOGFILE%
echo Log File Name (Copy) : %LOGFILE_COPY%
echo ===================================================================================
echo CUSTOM SCRIPT VARIABLES
echo ===================================================================================
echo DBA_USERNAME (P1) : %DBA_USERNAME%
echo DBA_PASSWORD (P2) : xxxxxxxxxxxxx
echo TNS_ALIAS (P3) : %TNS_ALIAS%
echo NUM_DAYS_TO_KEEP (P4) : %NUM_DAYS_TO_KEEP%
echo Oracle Export Parameter File Name : %EXP_PARAMETER_FILE_NAME%
echo Oracle Export Dump File Name : %EXP_DUMP_FILE_NAME%
echo Oracle Export Dump Log File Name : %EXP_DUMP_LOG_FILE_NAME%
echo.
echo ====================================================== > "%LOGFILE%"
echo - START TIME : %START_DATE% %START_TIME% >> "%LOGFILE%"
echo ====================================================== >> "%LOGFILE%"
echo. >> "%LOGFILE%"
echo =================================================================================== >> "%LOGFILE%"
echo COMMON SCRIPT VARIABLES >> "%LOGFILE%"
echo =================================================================================== >> "%LOGFILE%"
echo Script Name : %SCRIPT_NAME_FULL% >> "%LOGFILE%"
echo Script Version : %SCRIPT_VERSION% >> "%LOGFILE%"
echo Begin Date : %START_DATE% >> "%LOGFILE%"
echo Begin Time : %START_TIME% >> "%LOGFILE%"
echo Host Name : %HOSTNAME% >> "%LOGFILE%"
echo Log File Name : %LOGFILE% >> "%LOGFILE%"
echo Log File Name (Copy) : %LOGFILE_COPY% >> "%LOGFILE%"
echo =================================================================================== >> "%LOGFILE%"
echo CUSTOM SCRIPT VARIABLES >> "%LOGFILE%"
echo =================================================================================== >> "%LOGFILE%"
echo DBA_USERNAME (P1) : %DBA_USERNAME% >> "%LOGFILE%"
echo DBA_PASSWORD (P2) : xxxxxxxxxxxxx >> "%LOGFILE%"
echo TNS_ALIAS (P3) : %TNS_ALIAS% >> "%LOGFILE%"
echo NUM_DAYS_TO_KEEP (P4) : %NUM_DAYS_TO_KEEP% >> "%LOGFILE%"
echo Oracle Export Parameter File Name : %EXP_PARAMETER_FILE_NAME% >> "%LOGFILE%"
echo Oracle Export Dump File Name : %EXP_DUMP_FILE_NAME% >> "%LOGFILE%"
echo Oracle Export Dump Log File Name : %EXP_DUMP_LOG_FILE_NAME% >> "%LOGFILE%"
echo. >> "%LOGFILE%"
echo ============================================================== >> "%LOGFILE%"
echo - REMOVE TEMPORARY EXPORT LOG AND PARAMETER FILE. >> "%LOGFILE%"
echo ============================================================== >> "%LOGFILE%"
del /q %EXP_PARAMETER_FILE_NAME% >> "%LOGFILE%"
del /q %EXP_DUMP_LOG_FILE_NAME% >> "%LOGFILE%"
echo. >> "%LOGFILE%"
echo Done. >> "%LOGFILE%"
echo. >> "%LOGFILE%"
echo ============================================================== >> "%LOGFILE%"
echo - WRITE EXPORT PARAMETER FILE. >> "%LOGFILE%"
echo ============================================================== >> "%LOGFILE%"
echo userid=%DBA_USERNAME%/%DBA_PASSWORD%@%TNS_ALIAS% > %EXP_PARAMETER_FILE_NAME%
echo buffer=50000000 >> %EXP_PARAMETER_FILE_NAME%
echo file=%EXP_DUMP_FILE_NAME% >> %EXP_PARAMETER_FILE_NAME%
echo compress=n >> %EXP_PARAMETER_FILE_NAME%
echo grants=y >> %EXP_PARAMETER_FILE_NAME%
echo indexes=y >> %EXP_PARAMETER_FILE_NAME%
echo direct=no >> %EXP_PARAMETER_FILE_NAME%
echo log=%EXP_DUMP_LOG_FILE_NAME% >> %EXP_PARAMETER_FILE_NAME%
echo rows=y >> %EXP_PARAMETER_FILE_NAME%
echo consistent=y >> %EXP_PARAMETER_FILE_NAME%
echo full=y >> %EXP_PARAMETER_FILE_NAME%
REM owner=(SCOTT) >> %EXP_PARAMETER_FILE_NAME%
REM echo tables=(EMP, DEPT) >> %EXP_PARAMETER_FILE_NAME%
echo triggers=y >> %EXP_PARAMETER_FILE_NAME%
echo statistics=none >> %EXP_PARAMETER_FILE_NAME%
echo constraints=y >> %EXP_PARAMETER_FILE_NAME%
echo. >> "%LOGFILE%"
echo Done. >> "%LOGFILE%"
echo. >> "%LOGFILE%"
echo ============================================================== >> "%LOGFILE%"
echo - PERFORM EXPORT. >> "%LOGFILE%"
echo ============================================================== >> "%LOGFILE%"
%ORACLE_HOME%\bin\exp parfile=%EXP_PARAMETER_FILE_NAME%
echo. >> "%LOGFILE%"
echo Done. >> "%LOGFILE%"
echo. >> "%LOGFILE%"
echo ============================================================== >> "%LOGFILE%"
echo - DISPLAY EXPORT LOG FILE. >> "%LOGFILE%"
echo ============================================================== >> "%LOGFILE%"
type %EXP_DUMP_LOG_FILE_NAME% >> "%LOGFILE%"
echo. >> "%LOGFILE%"
echo ============================================================== >> "%LOGFILE%"
echo - SCAN LOG FILE FOR ERRORS >> "%LOGFILE%"
echo IGNORE 'EXP-00079: Data in table "[TABLE_NAME]" is protected. >> "%LOGFILE%"
echo - APPLY RETENTION POLICY FOR OBSOLETE EXPORT (DUMP) FILES. >> "%LOGFILE%"
echo ============================================================== >> "%LOGFILE%"
findstr /i "ORA- EXP-" "%LOGFILE%" | findstr /v "EXP-00079" >> "%LOGFILE%"
IF errorlevel 1 (set ERRORS="NO") ELSE (set ERRORS="YES")
IF %ERRORS%=="YES" (
echo. >> "%LOGFILE%"
echo SETTING ERRORS TO %ERRORS% >> "%LOGFILE%"
echo. >> "%LOGFILE%"
echo ----------------------------------------------- >> "%LOGFILE%"
echo Detected known exceptions in the export log >> "%LOGFILE%"
echo file. Retention policy will NOT be enforced. >> "%LOGFILE%"
echo ----------------------------------------------- >> "%LOGFILE%"
) ELSE (
echo. >> "%LOGFILE%"
echo SETTING ERRORS TO %ERRORS% >> "%LOGFILE%"
echo. >> "%LOGFILE%"
echo ----------------------------------------------- >> "%LOGFILE%"
echo Did not detect any known exceptions in the >> "%LOGFILE%"
echo export log file. Applying retention policy. >> "%LOGFILE%"
echo ----------------------------------------------- >> "%LOGFILE%"
echo. >> "%LOGFILE%"
echo ============================================================== >> "%LOGFILE%"
echo - REMOVE OBSOLETE LOG FILES. >> "%LOGFILE%"
echo %LOGDIR%\%SCRIPT_NAME_NOEXT%_%TNS_ALIAS%_*.log >> "%LOGFILE%"
echo ============================================================== >> "%LOGFILE%"
echo. >> "%LOGFILE%"
echo List of log files in %LOGDIR% older than %NUM_DAYS_TO_KEEP% days... >> "%LOGFILE%"
forfiles /P %LOGDIR% /S /D -%NUM_DAYS_TO_KEEP% /M %SCRIPT_NAME_NOEXT%_%TNS_ALIAS%_*.log /C "CMD /C Echo @FILE will be deleted!" >> "%LOGFILE%"
echo. >> "%LOGFILE%"
echo Deleting log files in %LOGDIR% older than %NUM_DAYS_TO_KEEP% days... >> "%LOGFILE%"
forfiles /P %LOGDIR% /S /D -%NUM_DAYS_TO_KEEP% /M %SCRIPT_NAME_NOEXT%_%TNS_ALIAS%_*.log /C "CMD /C del /Q /F @FILE" >> "%LOGFILE%"
echo. >> "%LOGFILE%"
echo ============================================================== >> "%LOGFILE%"
echo - REMOVE OBSOLETE EXPORT DUMP FILES. >> "%LOGFILE%"
echo %ORA_EXP_DIR%\%SCRIPT_NAME_NOEXT%_%TNS_ALIAS%_*.dmp >> "%LOGFILE%"
echo ============================================================== >> "%LOGFILE%"
echo. >> "%LOGFILE%"
echo List of dump files in %ORA_EXP_DIR% older than %NUM_DAYS_TO_KEEP% days... >> "%LOGFILE%"
forfiles /P %ORA_EXP_DIR% /S /D -%NUM_DAYS_TO_KEEP% /M %SCRIPT_NAME_NOEXT%_%TNS_ALIAS%_*.dmp /C "CMD /C Echo @FILE will be deleted!" >> "%LOGFILE%"
echo. >> "%LOGFILE%"
echo Deleting dump files in %ORA_EXP_DIR% older than %NUM_DAYS_TO_KEEP% days... >> "%LOGFILE%"
forfiles /P %ORA_EXP_DIR% /S /D -%NUM_DAYS_TO_KEEP% /M %SCRIPT_NAME_NOEXT%_%TNS_ALIAS%_*.dmp /C "CMD /C del /Q /F @FILE" >> "%LOGFILE%"
)
echo. >> "%LOGFILE%"
echo ============================================================== >> "%LOGFILE%"
echo - REMOVE ANY TEMPORARY FILES. >> "%LOGFILE%"
echo ============================================================== >> "%LOGFILE%"
del /q %EXP_PARAMETER_FILE_NAME% >> "%LOGFILE%"
del /q %EXP_DUMP_LOG_FILE_NAME% >> "%LOGFILE%"
echo. >> "%LOGFILE%"
echo Done. >> "%LOGFILE%"
REM +--------------------------------------------------------------------------+
REM | SET END DATE AND TIME ENVIRONMENT VARIABLES |
REM +--------------------------------------------------------------------------+
set END_DATE=%DATE%
set END_TIME=%TIME%
echo.
echo ======================================================
echo - FINISH TIME : %END_DATE% %END_TIME%
echo ======================================================
echo. >> "%LOGFILE%"
echo ====================================================== >> "%LOGFILE%"
echo - FINISH TIME : %END_DATE% %END_TIME% >> "%LOGFILE%"
echo ====================================================== >> "%LOGFILE%"
echo. >> "%LOGFILE%"
echo ====================================================== >> "%LOGFILE%"
echo - SEND EMAIL TO ADMINISTRATORS >> "%LOGFILE%"
echo ====================================================== >> "%LOGFILE%"
IF %ERRORS%=="YES" (
set EMAIL_STATUS=[%HOSTNAME%] - FAILED: %SCRIPT_NAME_FULL%
) ELSE (
set EMAIL_STATUS=[%HOSTNAME%] - SUCCESSFUL: %SCRIPT_NAME_FULL%
)
blat "%LOGFILE%" -subject "%EMAIL_STATUS%" -to %SMTP_EMAIL_TO% -server %SMTP_SERVER% -f %SMTP_EMAIL_FROM%
echo. >> "%LOGFILE%"
echo ====================================================== >> "%LOGFILE%"
echo - EXITING SCRIPT >> "%LOGFILE%"
echo ====================================================== >> "%LOGFILE%"
echo Making copy of log file [%LOGFILE%] to [%LOGFILE_COPY%] >> "%LOGFILE%"
copy /Y /V %LOGFILE% %LOGFILE_COPY% >> "%LOGFILE%"
REM +--------------------------------------------------------------------------+
REM | END THIS SCRIPT |
REM +--------------------------------------------------------------------------+
goto END
REM +==========================================================================+
REM | *** END OF SCRIPT *** |
REM +==========================================================================+
REM +--------------------------------------------------------------------------+
REM | LABEL DECLARATION SECTION |
REM +--------------------------------------------------------------------------+
:USAGE
echo Usage: %SCRIPT_NAME_FULL% "DBA_USERNAME" "DBA_PASSWORD" "TNS_ALIAS" "NUM_DAYS_TO_KEEP"
echo.
echo DBA_USERNAME = Oracle DBA Username - (Requires DBA Role)
echo DBA_PASSWORD = Oracle DBA Password
echo TNS_ALIAS = Connect String to connect to the database (ex. ORCL)
echo NUM_DAYS_TO_KEEP = Number of days worth of Oracle exports to retain on the file system
goto END
:ENV_VARIABLES
echo ERROR: You must set the following environment variables before
echo running this script or manually set them within the script:
echo.
echo ORA_EXP_DIR = Directory used for export dump files
goto END
:END
@echo on
