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