Friday, 11 December 2009

BATCH JOBS

=================================================================================================================================================

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

1 comment:

Blogger said...

DreamHost is ultimately one of the best hosting company for any hosting services you require.