Maintenance Scripts 01

### Note: Kindly Verify the scripts in TEST environment before using in PROD Instance
### Script to extract files changed last 1 Day
############ Script STARTS Here ##############
echo " "
echo " system_time $HOSTNAME "
echo "++++++++++++++++++++++++++++++"
date
echo "-----------------------------------------------------------"
echo " users currently logged in terminal "
echo "++++++++++++++++++++++++++++++++++++"
who
echo "-----------------------------------------------------------"
echo " File from OA_HTML "
echo "+++++++++++++++++++"
cd $OA_HTML
find . -type f -mtime -1 -ls
echo "-----------------------------------------------------------"
echo " file from common_top "
echo "++++++++++++++++++++++"
cd $COMMON_TOP
find . -type f -mtime -1 -ls
echo "-----------------------------------------------------------"
echo " files from appl_top "
echo "+++++++++++++++++++++"
cd $APPL_TOP
find . -type f -mtime -1 -ls
echo "+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"
############ Script ENDS Here ##############


### List File modified on Particular Date
find . -type f -newermt 2019-03-12 ! -newermt 2019-03-13 -ls
 
### List File modified from Specified Date
find . -type f -newermt 2019-03-12 -ls

### Script to compile invalid objects based on count
### It will spool the output for every run time
### Crontab Entry 
### */30 5-17 * * 0-4 $HOME/compile_object.sh > /dev/null 2>&1

############ Script STARTS Here ##############
. /u01/test/db_home/12.1.0/TEST_testdb.env

sqlplus -s / as sysdba <<EOF
set serveroutput on;
spool compile_object.log
DECLARE
invd_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO invd_count FROM dba_objects WHERE status='INVALID';
IF invd_count > 0 THEN
  dbms_output.put_line ('Invalid Object Count is :'||invd_count );
  dbms_output.put_line ('LOOP Started' );
  execute immediate 'ALTER MATERIALIZED VIEW apps.XX_OBJECT_mv COMPILE';
  utl_recomp.recomp_parallel(2);
  dbms_output.put_line ('LOOP Ended' );
END IF;
 SELECT COUNT(*) INTO invd_count FROM dba_objects WHERE status='INVALID';
 dbms_output.put_line ('Invalid Object Count is :'||invd_count );
END;
/
spool off;
EOF
############ Script ENDS Here ##############



### Generate tar script for run filesystem

select 'tar -cvzf /u02/EBS_BKP/R122_PROD_'|| SUBSTR(SUBSTR(PATH,0,INSTR(PATH,'/inst',1)),-4,3)
|| TO_CHAR(SYSDATE, '_DD_MON_YYYY')
||'.tar.gz '||
SUBSTR(PATH,0,INSTR(PATH,'/inst',1))||'EBSapps' tar_cmd from fnd_oam_context_files
where
NAME NOT IN ('TEMPLATE','METADATA')
and CTX_TYPE='A'
and status='S'
and text like '%<file_edition_type oa_var="s_file_edition_type">run</file_edition_type>%';


### Update the password for EBS user from backend
declare
cursor c1 is
Select * from fnd_user where user_id=8774;
begin
for cr1 in c1 loop
BEGIN
  FND_USER_PKG.UPDATEUSER(x_user_name             => cr1.user_name
                         ,x_owner                 => NULL
                         ,x_unencrypted_password  => 'welcome'
                         ,x_password_date         => SYSDATE
                         );
END;
end loop;
end;
/
commit;