REM ###########################################################################
REM # Windows Purge table SYS.AUD$
REM # Author: Amos Geng
REM # Date: 05/29/2015
REM # Functionality: -> Expdp the table SYS.AUD$ for backup purpose
REM #                -> Zip the expdp dump file
REM #                -> Check if any errors were detected in the expdp log
REM #                -> Truncate the table SYS.AUD$
REM #                -> Delete the zip dump file by retention
REM # Parameters Configuration:
REM #                -> DUMP directory
REM #                -> Main function: env file location
REM #                -> dump location 
REM #                -> LOG_LOCATION: MiCORE ENV File
REM ###########################################################################

@echo on

goto main

:set_variable
call C:\micore\scripts\micore_env.bat
set script_name=backup_purge_sysaud.bat 
set script_ver=1.0
set filedatetime=%DATE:~10,4%_%DATE:~4,2%_%DATE:~7,2%_%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%
set filedatetime=%filedatetime: =%
set dump_location=C:\Users\ageng\Documents
set mail_file=%LOG_LOCATION%\mail_backup_purge_sysaud_%filedatetime%.log
set logerr=error
set error_expdp=0

echo ======================================================= > %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
echo * MiCORE SYS.AUD$ Table Clean Up Script                 >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
echo * Date/Time: %filedatetime%                             >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
echo ======================================================= >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
echo.>> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
goto:EOF


:func_expdp_zip_table
set oracle_sid=%~1
echo %DATE:~10,4%_%DATE:~4,2%_%DATE:~7,2%_%TIME:~0,2%%TIME:~3,2%%TIME:~6,2% Exporting and Zipping the database %~1 SYS.AUD$ table... >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
exp "'/ as sysdba'" file=%dump_location%\sysaud_%~1_%filedatetime%.dmp tables=sys.aud$ log=%dump_location%\sysaud_%~1_%filedatetime%.log
type %dump_location%\sysaud_%~1_%filedatetime%.log >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log

for /f "delims=" %%i in ('findstr /n "successfully without warnings" %dump_location%\sysaud_%~1_%filedatetime%.log') do set logerr=noerror
::findstr /m "successfully without warnings" %dump_location%\sysaud_%~1_%filedatetime%.log

::if %errorlevel% == 0 (
if %logerr% == noerror (
	echo. >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
	echo %DATE:~10,4%_%DATE:~4,2%_%DATE:~7,2%_%TIME:~0,2%%TIME:~3,2%%TIME:~6,2% Zipping the dump file %dump_location%\sysaud_%~1_%filedatetime%.dmp... >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
	zip -9 -m -o %dump_location%\sysaud_%~1_%filedatetime%.dmp.zip %dump_location%\sysaud_%~1_%filedatetime%.dmp >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
	echo. >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
	echo %DATE:~10,4%_%DATE:~4,2%_%DATE:~7,2%_%TIME:~0,2%%TIME:~3,2%%TIME:~6,2% Exported and Zipped the database set %~1 SYS.AUD$ table successfully. >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
	echo. >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
	echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
	echo. >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
) else (
	set /a error_expdp+=1
	echo.>> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
	echo %DATE:~10,4%_%DATE:~4,2%_%DATE:~7,2%_%TIME:~0,2%%TIME:~3,2%%TIME:~6,2% Errors were detected during the export for database %~1, the dump file did not got zipped. >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
	echo. >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
	echo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
	echo. >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
)
goto:EOF


:func_truncate_sysaud
set oracle_sid=%~1
echo. >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
echo %DATE:~10,4%_%DATE:~4,2%_%DATE:~7,2%_%TIME:~0,2%%TIME:~3,2%%TIME:~6,2% Truncating database %~1 SYS.AUD$ table... >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
sqlplus -s / as sysdba @%SCRIPT_LOCATION%\sysaud_trucate.sql >> %LOG_LOCATION%\backup_purge_sysaud_%%i_%filedatetime%.log
echo %DATE:~10,4%_%DATE:~4,2%_%DATE:~7,2%_%TIME:~0,2%%TIME:~3,2%%TIME:~6,2% Truncation completed. >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
echo. >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
goto:EOF


:main
REM call C:\micore\scripts\micore_env.bat
call :set_variable

for %%i in (%USER_INSTANCES%) do (
	call :func_expdp_zip_table %%i
)

if %error_expdp% == 0 (
	for %%f in (%USER_INSTANCES%) do (
		REM echo %DATE:~10,4%_%DATE:~4,2%_%DATE:~7,2%_%TIME:~0,2%%TIME:~3,2%%TIME:~6,2% Truncating database %%f sys.aud$ table... >> %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log
		call :func_truncate_sysaud %%f
	)
	
	echo to: %PROACTIVE_EMAIL%, email_01@emeralit.com, email_02@emeralit.com > %mail_file%
	echo from: notifications@emeralit.net >> %mail_file%
	echo subject: Proactive - emeralit - %computername% : SYS.AUD$ cleanning up successfully. >> %mail_file%
	echo.>>%mail_file%
	type %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log >> %mail_file%
	echo This email was sent by MiCORE monitoring script %script_name% version %script_ver% >>%mail_file%
	Msmtp -t < %mail_file%
) else (
	echo to: %PROACTIVE_EMAIL%, email_01@emeralit.com, email_02@emeralit.com > %mail_file%
	echo from: notifications@emeralit.net >> %mail_file%
	echo subject: Proactive - emeralit - %computername% : Errors were detected during SYS.AUD$ cleanning up, dump file was not zipped and table was not truncated. >> %mail_file%
	echo.>>%mail_file%
	type %LOG_LOCATION%\backup_purge_sysaud_%filedatetime%.log >> %mail_file%
	echo This email was sent by MiCORE monitoring script %script_name% version %script_ver% >>%mail_file%
	Msmtp -t < %mail_file%	
)

forfiles /p "%dump_location%" /m *.zip /d -60 /c "CMD /C del @FILE"
forfiles /p "%dump_location%" /m *.log /d -60 /c "CMD /C del @FILE"
goto:EOF

	
	

Your Comments