 |
 |
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