 |
 |
**********************************************************************
* Description: Windows batch scripts of Oracle data pump auto refresh
* Date: 05:26 PM EST, 06/06/2018
**********************************************************************
REM ######################################################################################
REM # EML - Test Database Refresh
REM # Author: Amos Geng
REM # Date: 05/21/2018
REM # Description: Refreshing LAPPS and FFACTS schemas daily base for development purpose
REM ######################################################################################
@echo on
goto main
:funct_set_variable
set var_database_name=ccsprod
set var_hostname=%COMPUTERNAME%
set var_filedatetime=%DATE:~4,2%%DATE:~7,2%%DATE:~10,4%
set var_script_name=micore_dev_ccsprod_refresh
set var_script_location=C:\micore\scripts\EML_Refresh
set var_script_log_location=c:\micore\logs
set var_dump_directory_db=EXPDP_DIR
set var_dump_directory_local=E:\expdp
set var_dump_directory_remote=\\EMLNAS01\Oracle_Backups\expdp_backup
set var_client_name=EML
set var_oracle_home=C:\app\product\11.2.0\dbhome_1
set var_dump_retention=3
set var_proactive_notification_email=informative_support@emeralit.com
set var_critical_notification_email=informative_support@emeralit.com
goto:EOF
:funct_db_reboot
set oracle_sid=%var_database_name%
sqlplus / as sysdba @%var_script_location%\db_shutdown.sql
sqlplus / as sysdba @%var_script_location%\db_startup.sql
goto:EOF
:funct_no_dump_alert
echo to: %var_critical_notification_email% > %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
echo from: auto@emeralit.com >> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
echo subject: Critical - %var_client_name%:%var_hostname%:Expdp Dump NOT Detected for Today >> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
echo.>> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
echo Expdp Backup NOT Detected for Today. >> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
echo.>> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
echo Notice sent by %var_script_location%\%var_script_name% >> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
Msmtp -t < %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
exit
goto:EOF
:funct_dump_download
set oracle_sid=%var_database_name%
if exist %var_dump_directory_remote%\expdp_%ORACLE_SID%_%var_filedatetime%.dmp (
copy %var_dump_directory_remote%\expdp_%ORACLE_SID%_%var_filedatetime%.dmp %var_dump_directory_local%
) else (
call :funct_no_dump_alert
)
goto:EOF
:funct_drop_schema
set oracle_sid=%var_database_name%
sqlplus / as sysdba @%var_script_location%\db_drop_schema.sql
goto:EOF
:funct_impdp
set oracle_sid=%var_database_name%
impdp \"/ as sysdba\" schemas=tapps,tfacts directory=%var_dump_directory_db% dumpfile=expdp_%ORACLE_SID%_%var_filedatetime%.dmp logfile=expdp_%ORACLE_SID%_%var_filedatetime%.log
goto:EOF
:function_recompile
set oracle_sid=%var_database_name%
exit|sqlplus / as sysdba @%var_oracle_home%\rdbms\admin\utlrp.sql
goto:EOF
:funct_log_cleanup
forfiles /P %var_dump_directory_local% /M expdp_*.log /D -%var_dump_retention% /C "cmd /c Del @FILE"
forfiles /P %var_dump_directory_local% /M expdp_*.dmp /D -%var_dump_retention% /C "cmd /c Del @FILE"
forfiles /P %var_script_log_location% /M expdp_backup_notification*.txt /D -%var_dump_retention% /C "cmd /c Del @FILE"
goto:EOF
:funct_oraerr_notice
set var_errors_status=no_error
for /f "delims=" %%i in ('findstr /n "ORA-" %var_dump_directory_local%\expdp_%ORACLE_SID%_%var_filedatetime%.log') do set var_errors_status=detected
if %var_errors_status% == detected (
echo to: %var_critical_notification_email% > %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
echo from: auto@emeralit.com >> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
echo subject: Critical - %var_client_name%:%var_hostname%:Refresh Completed with Errors >> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
echo.>> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
type %var_dump_directory_local%\expdp_%ORACLE_SID%_%var_filedatetime%.log >> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
echo.>> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
echo Notice sent by %var_script_location%\%var_script_name% >> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
Msmtp -t < %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
) else (
echo to: %var_proactive_notification_email% > %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
echo from: auto@emeralit.com >> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
echo subject: Informative - %var_client_name%:%var_hostname%:Refresh Completed >> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
echo.>> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
type %var_dump_directory_local%\expdp_%ORACLE_SID%_%var_filedatetime%.log >> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
echo.>> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
echo.>> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
type %var_script_log_location%\expdp_7zip_%ORACLE_SID%_%var_filedatetime%.txt >> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
echo.>> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
type %var_script_log_location%\aws_dmp_shipment_%ORACLE_SID%_%var_filedatetime%.txt >> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
echo.>> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
echo Notice sent by %var_script_location%\%var_script_name% >> %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
Msmtp -t < %var_script_log_location%\notice_%ORACLE_SID%_%var_filedatetime%.txt
)
goto:EOF
:funct_change_password
set oracle_sid=%var_database_name%
sqlplus / as sysdba @%var_script_location%\db_change_password.sql
goto:EOF
:main
call :funct_set_variable
call :funct_dump_download
call :funct_db_reboot
call :funct_impdp
call :function_recompile
call :funct_oraerr_notice
call :funct_log_cleanup
call :funct_change_password
goto:EOF
Your Comments