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