ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle script summary

Oracle script summary

原创 Linux操作系统 作者:BenX22 时间:2012-06-13 20:32:17 0 删除 编辑

sqlplus -S '/as sysdba' <  set echo on
  set serveroutput on
  alter database backup controlfile to trace as '/home/oracle/controlbak.ctl';
  exit
EOS

 cat M_post_upgrade_stat.ksh
#!/bin/ksh

#
# Function to update index statistics and recompile for each db
#

sub01 () {
OUT_TMP="${DATE_FOLDER}/post_upgrade_tmp"
OUT_FILE_SUB="${DATE_FOLDER}/post_upgrade_$1_updind_recomp.log"
SQL_FILE="${DATE_FOLDER}/post_upgrade_$1_updind_recomp.sql"
isql -b -U${ASE_USER} -o ${OUT_TMP} <`echo $ASE_PASS`
use $1
go
print "go"
go
print "select getdate()"
go
print "go"
go
print "use $1"
go
print "go"
go
select
"update index statistics "+name+char(10)+"go"
from sysobjects
where type = "U"
go
print "go"
go
print "select getdate()"
go
print "go"
go
EOF
cat $OUT_TMP | grep -v "affected" > $SQL_FILE
nohup isql -w1000 -U${ASE_USER} -P${ASE_PASS} -i ${SQL_FILE} -o ${OUT_FILE_SUB} &
}

#
# Main program
#
#. ~/.profile
. $SYBASE/SYBASE.sh

#export DSQUERY=DHKFXEA01
export DSQUERY=UHKFRMA01
BASE_FOLDER="/home/sybase/ben/scripts"

ASE_USER="sa"
ASE_PASS="Asdf1234"
#ASE_PASS=`crypt key < ${BASE_FOLDER}/.pw/.${ASE_USER}`
echo $ASE_PASS

RUN_TIME=`date +"%Y%m%d-%H%M"`
DATE_FOLDER="${BASE_FOLDER}/logs/`echo ${RUN_TIME} | cut -b1-8`"
if [ ! -d $DATE_FOLDER ];
then
        mkdir ${DATE_FOLDER}
fi

OUT_TMP="$DATE_FOLDER/post_upgrade_tmp"
OUT_DBLST="$DATE_FOLDER/post_upgrade_dblst"
OUT_FILE="$DATE_FOLDER/post_upgrade.log"

#
# To get the db list
#
isql -b -U${ASE_USER} -o ${OUT_TMP} <`echo $ASE_PASS`
select name from master..sysdatabases
where name in ('ctrldb','gbrdb','ISIS','DPdb','HBUS')
go
EOF

cat $OUT_TMP | grep -v "rows affected" > $OUT_DBLST

for db in `cat $OUT_DBLST`
do
echo `date` - $db >> $OUT_FILE
sub01 $db
done

cat M_loaddb.sh
#!/bin/ksh
set -x

echo " =================================================================================================="
echo " LOAD database in dblist.lst under current dir,please check log files under LOGDIR for more detail!"
echo " =================================================================================================="
cd /opt/sybase/DBAase025
. ./ASE150.sh

BASEDIR=/home/sybase/ben
DBLIST=$BASEDIR/dblist
DATE=`date +"%Y%m%d"`
SCRIPTDIR=$BASEDIR/Loadscripts
LOGDIR=$BASEDIR/Loadlogs

LOADDIR="$1"
DUMPDATE="$2"

for i in `cat $DBLIST`
do
echo "use master" >$SCRIPTDIR/${i}_${DUMPDATE}.load.sql
echo "go" >>$SCRIPTDIR/${i}_${DUMPDATE}.load.sql
echo "exec sp_kill_db_processes ${i} " >>$SCRIPTDIR/${i}_${DUMPDATE}.load.sql
echo "go" >>$SCRIPTDIR/${i}_${DUMPDATE}.load.sql
echo "print \" Starting restore DB ${i} ......\"" >>$SCRIPTDIR/${i}_${DUMPDATE}.load.sql
echo "select getdate()" >>$SCRIPTDIR/${i}_${DUMPDATE}.load.sql
echo "go"  >>$SCRIPTDIR/${i}_${DUMPDATE}.load.sql
echo "load database ${i} from \"compress::${LOADDIR}/${i}.${DUMPDATE}.01.cmp\" " >>$SCRIPTDIR/${i}_${DUMPDATE}.load.sql
echo "               stripe on\"compress::${LOADDIR}/${i}.${DUMPDATE}.02.cmp\" " >>$SCRIPTDIR/${i}_${DUMPDATE}.load.sql
echo "               stripe on\"compress::${LOADDIR}/${i}.${DUMPDATE}.03.cmp\" " >>$SCRIPTDIR/${i}_${DUMPDATE}.load.sql
echo "               stripe on\"compress::${LOADDIR}/${i}.${DUMPDATE}.04.cmp\" " >>$SCRIPTDIR/${i}_${DUMPDATE}.load.sql

echo "online database ${i}" >>$SCRIPTDIR/${i}_${DUMPDATE}.load.sql
echo "go"  >>$SCRIPTDIR/${i}_${DUMPDATE}.load.sql
echo "select getdate()" >>$SCRIPTDIR/${i}_${DUMPDATE}.load.sql
echo "go"  >>$SCRIPTDIR/${i}_${DUMPDATE}.load.sql
echo "print \"Restore DB ${i} completed\" " >>$SCRIPTDIR/${i}_${DUMPDATE}.load.sql
nohup isql -Usybadmin -Phkdba123 -SUHKFRMA03 -i $SCRIPTDIR/${i}_${DUMPDATE}.load.sql -o $LOGDIR/${i}.${DUMPDATE}.log &

done

---@Kill process in DB

CREATE proc sp_kill_db_processes
(@dbname varchar(20))
as

Declare @dbid int,
@spid int,
@str nvarchar(128)
select @dbid = dbid from master..sysdatabases
where name = @dbname
declare spidcurs cursor for
select spid from master..sysprocesses where dbid = @dbid
open spidcurs
fetch next from spidcurs into @spid
While @@fetch_status = 0
Begin
Select @str = 'Kill '+convert(nvarchar(30),@spid)
exec(@str)
--print @str
fetch next from spidcurs into @spid
End
Deallocate spidcurs


exec sp_kill_db_processes('DBNAME')


---@ ORACLE sqlplus SET

SQL>set colsep' ';     //-域输出分隔符
SQL>set echo off;     //显示start启动的脚本中的每个sql命令,缺省为on
SQL> set echo on               //设置运行命令是是否显示语句
SQL> set feedback on;       //设置显示“已选择XX行”
SQL>set feedback off;      //回显本次sql命令处理的记录条数,缺省为on
SQL>set heading off;     //输出域标题,缺省为on
SQL>set pagesize 0;      //输出每页行数,缺省为24,为了避免分页,可设定为0。
SQL>set linesize 80;      //输出一行字符个数,缺省为80
SQL>set numwidth 12;     //输出number类型域长度,缺省为10
SQL>set termout off;     //显示脚本中的命令的执行结果,缺省为on
SQL>set trimout on;   //去除标准输出每行的拖尾空格,缺省为off
SQL>set trimspool on;  //去除重定向(spool)输出每行的拖尾空格,缺省为off
SQL>set serveroutput on;  //设置允许显示输出类似dbms_output
SQL> set timing on;          //设置显示“已用时间:XXXX”
SQL> set autotrace on-;    //设置允许对执行的sql进行分析

set verify off                     //可以关闭和打开提示确认信息old 1和new 1的显示.

--!rename tablespace   --after 10G

alter tablespace ts_name rename to new_ts_name;

--!Resize datafile

alter database  datafile '/u01/app/oracle/oradata/rmanrep/users01.dbf' resize 15m;

--!Rename datafile

select name from v$datafile;

-offline tablespace
 
1)alter tablespace ts01 offline;
2)!cp datafile
3)alter tablesapce ts01 rename datafile '' to '';
4)alter tablespace ts01 online;

-Shutdown and startup mount;

1)shutdown database;
2)cp datafile to new path
3)startup mount;
4)alter database rename file '' to '';
5)alter database open;

--!Get tablespace USAGAE

col maxbytes for a999999999999
select    a.TABLESPACE_NAME,
ROUND(a.BYTES/1024000) As Used_From_Allocate_size,
ROUND(b.BYTES/1024000) as Free_From_Allocate_size,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2)  "%USED__Allocate_size"
from
(
select  TABLESPACE_NAME,
sum(BYTES) BYTES
from    dba_data_files
group   by TABLESPACE_NAME
)
a,
(
select  TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from    dba_free_space
group   by TABLESPACE_NAME
)
b
where    a.TABLESPACE_NAME=b.TABLESPACE_NAME
and a.TABLESPACE_NAME like 'YOLUS_SEDHK_TN%'
order      by ((a.BYTES-b.BYTES)/a.BYTES) desc ;

 

 

*****GOOD SQL BELOW******

set linesize 180
col tablespace_name     heading 'Tablespace'      format a30 truncate
col total_maxspace_mb   heading 'Megs|Max Size'   format 9G999G999
col total_allocspace_mb heading 'Megs|Allocated'  format 9G999G999
col used_space_mb       heading 'Megs|Used'       format 9G999G999D99
col free_space_mb       heading 'Megs|Free Till Max'       like used_space_mb
col free_space_ext_mb       heading 'Megs|Free Till Ext'       like used_space_mb
col pct_used            heading '%|Used'          format 999D99
col pct_free            heading '%|Free'          like pct_used

break on report
compute sum label "Total Size:" of total_maxspace_mb total_allocspace_mb used_space_mb -
free_space_mb (used_space_mb/total_maxspace_mb)*100 on report

select /*+ALL_ROWS */
  alloc.tablespace_name,
  alloc.total_maxspace_mb,
  alloc.total_allocspace_mb,
  (alloc.total_allocspace_mb-free.free_space_mb) used_space_mb,
  free.free_space_mb+(alloc.total_maxspace_mb-alloc.total_allocspace_mb) free_space_mb,
  free.free_space_mb free_space_ext_mb,
  ((alloc.total_allocspace_mb-free.free_space_mb)/alloc.total_maxspace_mb)*100 pct_used,
  ((free.free_space_mb+(alloc.total_maxspace_mb-alloc.total_allocspace_mb))/alloc.total_maxspace_mb)*100 pct_free
FROM (SELECT tablespace_name,
        ROUND(SUM(CASE WHEN maxbytes = 0 THEN bytes ELSE maxbytes END)/1048576) total_maxspace_mb,
        ROUND(SUM(bytes)/1048576) total_allocspace_mb
     FROM dba_data_files
     WHERE file_id NOT IN (SELECT FILE# FROM v$recover_file)
     GROUP BY tablespace_name) alloc,
     (SELECT tablespace_name,
        SUM(bytes)/1048576 free_space_mb
      FROM dba_free_space
      WHERE file_id NOT IN (SELECT FILE# FROM v$recover_file)
      GROUP BY tablespace_name) free
WHERE alloc.tablespace_name = free.tablespace_name (+)
      --and alloc.tablespace_name like ' WIN_INDEX%'
ORDER BY pct_used DESC
/


---!create_user.sql

set linesize 500
set head off
set feedback off
set trims on
set echo off
set veri off
set termout off

column vusername new_value vusername
column vfilename new_value vfilename
column vlogname new_value vlogname

select upper('&&1') vusername from dual;
select 'CREATE_USER_&&vusername'||'_'||sysdate||'.sql' vfilename from dual;
select 'CREATE_USER_&&vusername'||'_'||sysdate||'.lst' vlogname from dual;

spool &&vfilename

select 'set echo on' from dual;
select 'set timing on ' from dual;
select 'spool &&vlogname' from dual;


select 'drop user &&vusername cascade;' from dual;

select to_char(dbms_metadata.get_ddl('USER','&&vusername'))||';' from dual
;
select 'alter user '||username||' quota '||
decode ( max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', max_bytes)||
' on '||tablespace_name ||';'
from dba_ts_quotas
where username = '&&vusername'
;
select 'grant '||granted_role||' to '||grantee||';'
from DBA_ROLE_PRIVS
where grantee = '&&vusername'
;

select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';'
from DBA_TAB_PRIVS
where grantee = '&&vusername'
;

select 'grant '||privilege||' to '||grantee||';'
from DBA_SYS_PRIVS
where grantee = '&&vusername'
;

select 'spool off' from dual;
spool off


---!create_user_stage2_grant_privilege.sql

set linesize 500
set head off
set feedback off
set trims on
set echo off
set veri off
set termout off

column vusername new_value vusername
column vfilename new_value vfilename
column vlogname new_value vlogname

select upper('&&1') vusername from dual;
select 'CREATE_USER_STAGE2_&&vusername'||'_'||sysdate||'.sql' vfilename from dual;
select 'CREATE_USER_STAGE2_&&vusername'||'_'||sysdate||'.lst' vlogname from dual;

spool &&vfilename

select 'set echo on' from dual;
select 'set timing on ' from dual;
select 'spool &&vlogname' from dual;

select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';'
from DBA_TAB_PRIVS
where grantor = '&&vusername'
;

select 'spool off' from dual;
spool off

expdp parfile=expdpTLMDBO.par

expdpTLMDBO.par

 userid='/ as sysdba'"
 directory=EXPDP \
 dumpfile=$EXP_PIPE%U \
 schemas=has_admin \
 parallel=4 \
 logfile=${RUN_LOG} \
 flashback_time="to_timestamp(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')"


nohup expdp "'/ as sysdba'" parfile=expdpHAS_ADMIN.par &
 
directory=EXPDP
dumpfile=20110714_has_admin.dmpdp
schemas=has_admin
logfile=20110714_has_admin.dmpdp.log
exclude=TABLE:"IN('MLOG$_MS_BUFFER_REGISTRY','MS_BUFFER_TRACK','EXT_WID_WRK_ITM_DTLS',
'NBM_NOTIF_BRDCST_MSG','WID_WRK_ITM_DTLS','MS_LINKED_MESSAGES',
'WIR_WRK_ITM_RMKS','CENTRAL_AUD','ANC_AUD')"

--!bk_export_expdp_full.ksh

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

#
# generic setting
#
ORACLE_TARGET="UHAS01"
SCP_BASE="/u01/in/UHAS01/oracle/admin/UHAS01/scripts"
#LOG_BASE="$SCP_BASE/logs"
#DMP_BASE="/u99/oradata/UHAS01/backups/UHAS01/exports"

PWFILE="$SCP_BASE/.pw/.ORCLADMIN"
VALUE=`cat $PWFILE`
CRYPT="$SCP_BASE/src/crypt"
PASSWORD=`$CRYPT -k key -d -v $VALUE`


#
# PATROL setting
#
#PATROL_SCRIPT="$SCP_BASE/src/PATROL_backup.ksh"

#
# source Oracle environment variable
#
if [ -f $HOME/.profile-ORACLE-UHAS01 ];
then
        export ORAENV_ASK=NO
        . $HOME/.profile-ORACLE-UHAS01
fi

#
# other Oracle variable
#
export NLS_DATE_FORMAT='YYYY-MON-DD HH24:MI:SS'

#
# get script. run date
#
DATE=`date +"%Y%m%d"`

#
# command and run log
#
RUN_LOG=$DATE-EXPORT-HAS_ADMIN.log

EXP_PIPE=$DATE-HAS_ADMIN.dmp
mknod $EXP_PIPE p
chmod 660 $EXP_PIPE
gzip < $EXP_PIPE | cat > $EXP_PIPE.gz &

#
#
#
echo "orcladmin/$PASSWORD" | \
expdp \
 directory=EXPDP \
 dumpfile=$EXP_PIPE \
 schemas=has_admin \
 parallel=4 \
 logfile=${RUN_LOG} \
 flashback_time=\"to_timestamp\(to_char\(sysdate,\'yyyy-mm-dd hh24:mi:ss\'\),\'yyyy-mm-dd hh24:mi:ss\'\)\"

 

alias rm="rm"

---@Recompile Schema

exec dbms_utility.compile_schema(schema=>'HORIZONDEVA1')


---@dbms_stat update statistics


beign

dbms_stats.gather_schema_stats(
  ownname          => 'TLMDBO',
  estimate_percent => dbms_stats.auto_sample_size,
  method_opt       => 'for all columns size repeat',
  degree           => 7
);
end;
/

---@Get active instance

ps -ef|grep pmon|grep -v 'grep'|awk {'print $9'}|cut -f3 -d'_'

 

---@Get Sequence DDL

select sequence_name,last_number+cache_size,max_value from dba_sequences WHERE SEQUENCE_OWNER='HAS_ADMIN';


select 'Create Sequence '||sequence_name||
       ' increment by '||increment_by||
       ' start with '||last_number||+cache_size||
       ' maxvalue '||max_value||
       decode(cycle_flag,'N',' NOCYCLE ',' CYCLE ')||
       decode(cache_size,0,'NOCACHE ','CACHE '||cache_size)
from dba_sequences WHERE SEQUENCE_OWNER='HAS_ADMIN';

 

---@Full export

#!/usr/bin/ksh

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

#
# generic setting
#
ORACLE_TARGET="UHAS01"
SCP_BASE="/u01/in/UHAS01/oracle/admin/UHAS01/scripts"
LOG_BASE="$SCP_BASE/logs"
DMP_BASE="/u99/oradata/UHAS01/backups/UHAS01/exports"

PWFILE="$SCP_BASE/.pw/.ORCLADMIN"
VALUE=`cat $PWFILE`
CRYPT="$SCP_BASE/src/crypt"
PASSWORD=`$CRYPT -k key -d -v $VALUE`

#
# PATROL setting
#
#PATROL_SCRIPT="$SCP_BASE/src/PATROL_backup.ksh"

#
# source Oracle environment variable
#
if [ -f $HOME/.profile-ORACLE-UHAS01 ];
then
        export ORAENV_ASK=NO
        . $HOME/.profile-ORACLE-UHAS01
fi

#
# other Oracle variable
#
export NLS_DATE_FORMAT='YYYY-MON-DD HH24:MI:SS'

#
# get script. run date
#
DATE=`date +"%Y%m%d"`

#
# command and run log
#
RUN_LOG=${LOG_BASE}/${DATE}-EXPORT-FULL.log

EXP_PIPE=${DMP_BASE}/${DATE}-FULL.dmp
mknod $EXP_PIPE p
chmod 660 $EXP_PIPE
gzip < $EXP_PIPE | cat > $EXP_PIPE.gz &

#
#
#
echo "orcladmin/$PASSWORD" | \
exp \
 buffer=419430400 \
 consistent=y \
 direct=y \
 full=y \
 file=$EXP_PIPE \
 log=${RUN_LOG}

alias rm="rm"
#rm -f $EXP_PIPE

#
# PATROL checking
#
#$PATROL_SCRIPT. EXP $RUN_LOG ${TABLESPACE}

#
#
#
#gzip ${DMP_BASE}/${DATE}-FULL.dmp > /dev/null 2>&1
find ${DMP_BASE}/*.dmp* ! -mtime -5 -exec ls -la {} \; >>  ${RUN_LOG}
find ${DMP_BASE}/*.dmp* ! -mtime -5 -exec rm -f {}  \; >>  ${RUN_LOG}

 

---@Get which Sid generate the most redo

select * from
(select a.username,a.sid,a.sql_hash_value,b.value
from v$session a,v$sesstat b,v$statname c
where a.sid=b.sid and b.statistic#=c.statistic# and c.name='redo size' order by b.value desc)
where rownum < 11;

SELECT s.sid, s.serial#,s.sql_hash_value, s.username, s.program,
i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc;


SELECT s.sid, s.serial#, s.username, s.program,
t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

 

---@Get current connections

ttitle "dbname Database|UNIX/Oracle Sessions";

set heading off;

select 'Sessions on database '||substr(name,1,8) from v$database;

set heading on;
set linesize 180
col box for a20
col program for a30
col os_user for a15
select
       a.spid pid,
       b.sid sid,
       b.serial# ser#,
       b.machine box,
       b.username username,
--       b.server,
       b.osuser os_user,
       b.program program,
       b.status
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;

ttitle off;


---@Add logfile/group

set linesize 180
select * from v$log;

col member for a50
select * from v$logfile;

alter database add logfile group 3 ('/u04c/oradata/UOMEG02/redo03A.rdo','/u03c/oradata/UOMEG02/redo03B.rdo','/u05c/oradata/UOMEG02/redo03C.rdo') size 100M reuse ;

alter database add logfile group 2 ('/u04c/oradata/UOMEG02/redo02A.rdo','/u03c/oradata/UOMEG02/redo02B.rdo','/u05c/oradata/UOMEG02/redo02C.rdo') size 100M reuse;

alter database add logfile group 1 ('/u04c/oradata/UOMEG02/redo01A.rdo','/u03c/oradata/UOMEG02/redo01B.rdo','/u05c/oradata/UOMEG02/redo01C.rdo') size 100M reuse;

alter database drop logifle group 4;

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

alter database drop logfile member '/u01/oracle/ica/log11.ora';
alter database add logfile member '/u01/oracle/ica/log11.ora' to group 1;
alter database rename file '/u01/oracle/ica/log1.ora’ to ‘/u02/oracle/ica/log2.ora';


---@Kill couples of sessions

begin         
ALTER SYSTEM ENABLE RESTRICTED SESSION;     
for x in (   select Sid, Serial#, machine, program  from v$session              
   where   machine <> 'MyDatabaseServerName')
loop          
   execute immediate 'Alter System Kill Session '''|| x.Sid || ',' || x.Serial# || ''' IMMEDIATE';      
end loop;  
end;  `


set heading off
set feedback off
set echo off
set timing off
spool killsession.sql
select 'Alter System Kill Session '''|| Sid || ',' || Serial# || ''''||';' from v$session where username='TLMDBO';
spool off

---check component usage:

 select NAME,VERSION,DETECTED_USAGES,FIRST_USAGE_DATE,LAST_USAGE_DATE,LAST_SAMPLE_DATE from DBA_FEATURE_USAGE_STATISTICS where DETECTED_USAGES<>0 order by NAME;


---Get current spid

select spid
from v$process
where addr=(select paddr from v$session where sid=(SELECT sys_context('userenv','SID') FROM DUAL));

----Check connections
set linesize 240
col machine  for a30
col program for a30
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select sid,serial#,username,status,MACHINE,program,logon_time from v$session order by MACHINE,LOGON_TIME;


---@imp with compressed imp dump file

dumpfile: exp_tlm01uo1_20110916_1313.dmp.gz


oracle@HKSHAD04:/u99/oradata/DTLM09/backups> cat imp.ksh
#! /bin/ksh

FILE=exp_tlm01uo1_20110916_1313.dmp

mknod $FILE p

gunzip < $FILE.gz > $FILE &

echo '/ as sysdba' | \
imp \
file=$FILE \
log=$FILE.log \
fromuser=tlm01ua1 \
touser=TLMDBO \
grants=n \
buffer=1000000000


---@ check whether component is in use

select  *  from DBA_FEATURE_USAGE_STATISTICS

 

----@Check TS growth trend

select b.name, a. rtime, trunc(a.tablespace_usedsize*c.block_size/1024/1024) tablespace_usedsize
from DBA_HIST_TBSPC_SPACE_USAGE a, v$tablespace b, dba_tablespaces c
where a.tablespace_id = ts#
and b.name = c.tablespace_name
and name like 'GRT%'
order by name, rtime


----@Chech implicit parameter

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.inst_id = USERENV ('Instance')
AND y.inst_id = USERENV ('Instance')
AND x.indx = y.indx
AND x.ksppinm LIKE '%&par%'
/

_optimizer_compute_index_stats
/

 

---@Check given table's foregin key tables

select table_name, constraint_name, status, owner
from all_constraints
where r_owner = :r_owner
and constraint_type = 'R'
and r_constraint_name in 
(    select constraint_name
     from all_constraints   
     where constraint_type in ('P', 'U')   
      and table_name = :r_table_name   
      and wner = :r_owner  )
order by table_name, constraint_name;

 


---@Kill those marked "KILLED" process

SQL> select username, status, logon_time from v$session where status = 'KILLED';


SQL> SELECT spid,username,terminal,program from v$process where not exists (select 1 from v$session
where paddr = addr);

kill -9 spid


cat replaceScripts.ksh
ORACLE_SID="UMODA01"
HOSTNAME="hkmodd11.it.hk.hibm.Ben"
HOSTNAME2="HKMODD11"
ORACLE_BASE="\/u01\/in\/$ORACLE_SID\/oracle"
ORACLE_HOME="$ORACLE_BASE\/db\/11.2.0"
ORACLE_SERVICE_NAME="$ORACLE_SID.HK.HIBM.Ben"
rm -Rf temp.txt
for FILENAME in $(find . -type f -print | sed 's/^\.\///')
do
    if [ $FILENAME != replaceScripts.ksh ] ; then

        echo $FILENAME

        sed "s//$HOSTNAME/g" $FILENAME > temp.txt;
        mv temp.txt $FILENAME;

        sed "s//$HOSTNAME2/g" $FILENAME > temp.txt;
        mv temp.txt $FILENAME;

        sed "s//$ORACLE_SERVICE_NAME/g" $FILENAME > temp.txt;
        mv temp.txt $FILENAME;

        sed "s//$ORACLE_SID/g" $FILENAME > temp.txt;
        mv temp.txt $FILENAME;

        sed "s//$ORACLE_HOME/g" $FILENAME > temp.txt;
        mv temp.txt $FILENAME;

        sed "s//$ORACLE_BASE/g" $FILENAME > temp.txt;
        mv temp.txt $FILENAME;

    fi
done
export ORACLE_BASE=/u01/in/$ORACLE_SID/oracle
export ORACLE_HOME=$ORACLE_BASE/db/11.2.0
~
 cat Ben_passverify.sql

CREATE OR REPLACE FUNCTION Ben_PASSVERIFY
(username in varchar2,password in varchar2,old_password in varchar2)
  RETURN boolean IS

   n boolean;
   m integer;
   differ integer;
   digit_count integer;
   char_count integer;
   isdigit boolean;
   ischar  boolean;
   ispunct boolean;
   digitarray varchar2(20);
   punctarray varchar2(25);
   chararray varchar2(52);

  BEGIN

   digitarray:= '0123456789';
   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
   punctarray:='!"#$%&()``*+,-/:;<=>?_';

   -- check if the password is same as the username

   IF NLS_LOWER(password) = NLS_LOWER(username) THEN
        raise_application_error(-20001, 'Password can not be same as the username');
   END IF;

   -- check for the minimum length of the password

   IF length(password) < 6 THEN
        raise_application_error(-20002, 'minimum length of passwords is 6');
   END IF;

   -- check if the password is too simple.
   -- a dictionary of words may be maintained and a
   -- check may be made so as not to allow the words
   -- that are too simple for the password

   IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN
      raise_application_error(-20002, 'Password is too simple');
   END IF;

   -- check if the password contains at least 2 letter
   -- and 2 digit
   -- 1. Check for the digit

   digit_count:=0;
   char_count :=0;
   isdigit:=FALSE;
   m := length(password);
   FOR i IN 1..10 LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(digitarray,i,1) THEN
            digit_count:= digit_count + 1;
            if digit_count = 2 then
            isdigit:=TRUE;
             GOTO findchar;
            end if;
         END IF;
      END LOOP;
   END LOOP;
   IF isdigit = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least 2 letter and 2 digit');
   END IF;

   -- 2. Check for the character

   <>
   ischar:=FALSE;
   FOR i IN 1..length(chararray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(chararray,i,1) THEN
           char_count := char_count + 1;
           if char_count =2 then
            ischar:=TRUE;
            end if;
         END IF;
      END LOOP;
   END LOOP;
   IF ischar = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least 2 letter and 2 digit');
   END IF;
   <>

   -- check if the password differs from the previous
   -- password by at least 3 letters
   -- NOT working due to bug 566824
   -- should use profile setting such as
   -- password_resue_time or password_reuse_mx
   -- to control at this moment

   IF old_password IS NOT NULL THEN
     differ := length(old_password) - length(password);
   IF abs(differ) < 3 THEN
       IF length(password) < length(old_password) THEN
         m := length(password);
       ELSE
         m := length(old_password);
   END IF;
   differ := abs(differ);
   FOR i IN 1..m LOOP
         IF substr(password,i,1) != substr(old_password,i,1) THEN
           differ := differ + 1;
         END IF;
       END LOOP;
       IF differ < 3 THEN
         raise_application_error(-20004, ' Check if the password differs from the previous password by at least 3 letters');
       END IF;
     END IF;
   END IF;
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);
END;
/

Ben_dbai_passwd_complexity.sql
 CREATE OR REPLACE FUNCTION "SYS"."DBAI_PASSWD_COMPLEXITY"

    ( username     varchar2,
      new_password varchar2,
      old_password varchar2
    )
    return boolean
  IS
   type t_company IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
   type t_monthday IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;

   countnum integer;
   iscompanyname boolean;
   ismonthdayname boolean;
   digitarray varchar2(20);
   punctarray varchar2(30);
   lowerarray varchar2(26);
   upperarray varchar2(26);
   company t_company;
   monthday t_monthday;
BEGIN
   -- Declare the array values
   digitarray := '0123456789';
   lowerarray := 'abcdefghijklmnopqrstuvwxyz'

;
   upperarray := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
   punctarray :='!"#$%&()`*+,-/:;<=>?.^@_{}[]\|';
   company(1) := 'Ben';
   company(2) := 'OPSCO';
   company(3) := 'GROUP';
   company(4) := 'NEWYORK';
   company(5) := 'BUFFALO';
   company(6) := 'LONDON';
   company(7) := 'HONGKONG';
   company(8) := 'TEMP';
   company(9) := 'BASING';
   company(10) := 'GENEVA';


   monthday(1)  := 'JANUARY';
   monthday(2)  := 'FEBRUARY';
   monthday(3)  := 'MARCH';
   monthday(4)  := 'APRIL';
   monthday(5)  := 'MAY';
   monthday(6)  := 'JUNE';
   monthday(7)  := 'JULY';
   monthday(8)  := 'AUGUST';
   monthday(9)  := 'SEPTEMBER';
   monthday(10) := 'OCTOBER';
   monthday(11) := 'NOVEMBER';
   monthday(12) := 'DECEMBER';

   monthday(13) := 'MONDAY';
   monthday(14) := 'TUESDAY';
   monthday(15) := 'WEDNESDAY';
   monthday(16) := 'THURSDAY';
   monthday(17) := 'FRIDAY';
   monthday(18) := 'SATURDAY';
   monthday(19) := 'SUNDAY';
   monthday(20) := 'MON';
   monthday(21) := 'TUE';
   monthday(22) := 'TUES';
   monthday(23) := 'WED';
   monthday(24) := 'THU';
   monthday(25) := 'THUR';

   monthday(26) := 'FRI';
   monthday(27) := 'SAT';
   monthday(28) := 'SUN';
   monthday(29) := 'JAN';
   monthday(30) := 'FEB';
   monthday(31) := 'MAR';
   monthday(32) := 'APR';
   monthday(33) := 'JUN';
   monthday(34) := 'JUL';
   monthday(35) := 'AUG';
   monthday(36) := 'SEP';
   monthday(37) := 'OCT';
   monthday(38) := 'NOV';

   monthday(39) := 'DEC';

   -- Check the password starts with a character

   IF instr(lowerarray, substr(new_password,1,1), 1, 1 ) = 0 AND
      instr(upperarray, substr(new_password,1,1), 1, 1 ) = 0 THEN
     raise_application_error(-20001, 'New Password has to start with a character.');
   END IF;


   -- Check the new password has any space in it.


   IF instr(new_password, ' ', 1, 1 ) > 0 THEN
     raise_application_error(-20002, 'Password can not contain any space.');
   END IF;

   -- Check the length of new password

   IF length(new_password) < 8 or length(new_password) > 16 THEN
     raise_application_error(-20003, 'New Password must be at least 8 characters long.');
   END IF;

   -- Check if the new password contains username

   IF instr(upper(new_password), upper(username), 1, 1 ) > 0 THEN
     raise_application_error(-20004, 'New Password can not contain Username.');
   END IF;

   -- Check if the new password contains company names or other references


   iscompanyname := FALSE;
   FOR i IN 1..company.COUNT LOOP
     IF instr(upper(new_password), company(i), 1, 1 ) > 0 THEN
       iscompanyname := TRUE;
       GOTO screen_companyname;
     END IF;
   END LOOP;

   <>
   IF iscompanyname = TRUE THEN
     raise_application_error(-20005, 'New Password can not contain Company names or references.');
   END IF;

   -- Check if the new password contains months or days of the week

   ismonthdayname:= FALSE;
   FOR i IN 1..monthday.COUNT LOOP
     IF instr(upper(new_password), monthday(i), 1, 1 ) >0 THEN
       ismonthdayname := TRUE;
       GOTO screen_monthdayname;
     END IF;

   END LOOP;

   <>
   IF ismonthdayname = TRUE THEN
     raise_application_error(-20006, 'New Password can not contain months of the year and days of the week.');
   END IF;

   -- Check if the password contains at least one digit

   countnum := 0;
   FOR i IN 1..length(new_password) LOOP

     IF instr(digitarray,substr(new_password,i,1),1,1) > 0 THEN
       countnum := countnum + 1;
     END IF;
   END LOOP;

   IF countnum < 1  THEN
     raise_application_error(-20007, 'New Password must contain at least one numeric character.');
   END IF;

   -- Check if the password contains at least one uppercase letter

   countnum := 0;
   FOR i IN 1..length(new_password) LOOP
     IF instr(upperarray,substr(new_password,i,1),1,1) > 0 THEN
       countnum := countnum + 1;
     END IF;
   END LOOP;

   IF countnum < 1  THEN
      raise_application_error(-20008, 'New Password must contain at least one uppercase alpha character');
   END IF;

   -- Check if the password contains at least one lowercase letter

   countnum := 0;
   FOR i IN 1..length(new_password) LOOP
     IF instr(lowerarray,substr(new_password,i,1),1,1) > 0 THEN
       countnum := countnum + 1;
     END IF;
   END LOOP;


   IF countnum < 1  THEN
      raise_application_error(-20008, 'New Password must contain at least one lowercase alpha character');
   END IF;

--   -- 3. Check for the punctuation character
--
--   countnum := 0;
--   FOR i IN 1..length(new_password) LOOP
--     IF instr(punctarray,substr(new_password,i,1),1,1) > 0 THEN

--       countnum := countnum + 1;
--     END IF;
--   END LOOP;
--
--   IF countnum < 1  THEN
--      raise_application_error(-20010, 'New Password must contain at least one punctuation character');
--   END IF;

   RETURN(TRUE);
END;
/

 cat Ben_dbacc.ksh
#!/sbin/sh

#
# Step 1: lock all default database accounts
# Step 2: change password for all default database accounts
# Step 3: assign profile USR to all default database accounts
#

echo "spool Ben_dbacc2.sql"    >  Ben_dbacc1.sql
echo "set termout off"          >> Ben_dbacc1.sql
echo "set feedback off"         >> Ben_dbacc1.sql
echo "set head off"             >> Ben_dbacc1.sql
echo "set echo off"             >> Ben_dbacc1.sql

echo "select 'spool Ben_dbacc.log' from dual;"                                 >> Ben_dbacc1.sql
echo "select 'ALTER USER ' || username || ' ACCOUNT LOCK;' from dba_users;"     >> Ben_dbacc1.sql
echo "select 'ALTER USER ' || username || ' IDENTIFIED BY $1;' from dba_users;" >> Ben_dbacc1.sql
echo "select 'ALTER USER ' || username || ' PROFILE USR;' from dba_users;"      >> Ben_dbacc1.sql
echo "select 'spool off' from dual;"                                            >> Ben_dbacc1.sql
echo "spool off"                                                                >> Ben_dbacc1.sql

sqlplus /nolog << MARKER

connect / as sysdba
spool run.log
@Ben_dbacc1.sql
@Ben_dbacc2.sql

cat monitor.tempusage.sql
SELECT DISTINCT s.SID,
                s.serial#,
                blocks,
                blocks * blocks * (SELECT VALUE
                                     FROM v$parameter
                                    WHERE NAME = 'db_block_size') / 1024
                / 1024 mb,
                TABLESPACE,
                CONTENTS,
                segtype,
                schemaname,
                machine,
                program,
                sq.sql_text
           FROM v$tempseg_usage tu,
                v$session s,
                v$sql sq
          WHERE tu.session_addr = s.saddr
            AND tu.session_num = s.serial#
            AND sq.sql_id = tu.sql_id(+)
            AND sq.hash_value = tu.sqlhash(+)
/


set linesize 120
set pagesize 1000
set feed off
set echo off
set verify off
set head off
set termout off
set escape \

spo &1
select 'spo &1\.out' from DUAL;
SELECT 'PURGE TABLESPACE ' || tablespace_name || ' USER ' || owner ||';'
FROM (SELECT distinct tablespace_name, owner from dba_segments where wner = '&2');
select 'drop ' || object_type || ' ' || owner || '."' || object_name
  || decode(object_type,
       'CLUSTER', '" including tables cascade constraints;',
       'TABLE', '" cascade constraints PURGE;',
       'TYPE','" FORCE;',
       '";')
from dba_objects
where object_type in (
  'CLUSTER', 'TABLE', 'VIEW', 'SEQUENCE', 'SYNONYM', 'FUNCTION',
  'PROCEDURE', 'PACKAGE', 'TYPE'
)
AND wner = '&2'
AND object_name not in (SELECT object_name from dba_recyclebin WHERE wner = '&2');
select 'spo off' from DUAL;
select 'exit' from DUAL;
spo off
exit

set head off
set feed off
set echo off
set lines 9999
set verify off
set escape '\'
spo &1
select 'spo &1\.out' from DUAL;
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''';'
FROM v$session
WHERE username like '%&2%';
select 'spo off' from dual;
select 'exit' from dual;
spo off
exit


set linesize 120
set pagesize 1000
set feed off
set echo off
set verify off
set head off
set termout off
set escape \

spo &1

select 'grant select on ' ||  ob.owner || '.' || ob.object_name || ' to RO_CALYPSO_GPT ;'
from dba_objects ob
where ob.object_type='TABLE' and ob.owner='CALYPSO_GPT'
union
select 'grant insert,update,delete,select on ' ||  ob.owner || '.' || ob.object_name || ' to APP_CALYPSO_GPT ;'
from dba_objects ob
where ob.object_type='TABLE' and ob.owner='CALYPSO_GPT'
union
select 'grant execute on ' || ob.owner || '.' || ob.object_name || ' to APP_CALYPSO_GPT;'
from dba_objects ob
where ob.object_type='PROCEDURE' and ob.owner='CALYPSO_GPT';
select 'spo off' from DUAL;
select 'exit' from DUAL;
spo off
exit


#!/usr/bin/ksh
#
# source Oracle environment variable
#
if [ -f $HOME/.profile-ORACLE-UMODA01 ];
then
        export ORAENV_ASK=NO
        . $HOME/.profile-ORACLE-UMODA01
fi

#
# other Oracle variable
#
export NLS_DATE_FORMAT='YYYY-MON-DD HH24:MI:SS'

#
# get script. run date
#
DATE=`date +"%Y%m%d"`
export SOURCE_SCHEMA=CALYPSO_PROD
#DEST_SCHEMA="$2"

PROD_HOST=hkmodpora01.hk.hibm.Ben
PROD_BDIR=/u99/oradata/LMODA01/backups/autorefresh

APP_HOST=hkmodd01.it.hk.hibm.Ben
APP_LOGBASE=/appvol/moda/daily_refresh/logs

export ORACLE_SID=UMODA01

DIR_BASE=$ORACLE_BASE/admin/scripts/autorefresh
DIR_BASE_SRC=$DIR_BASE/src
#DIR_BASE_SQL=$DIR_BASE/sql
DIR_BASE_BAK=/u99/oradata/$ORACLE_SID/backups
DIR_BASE_BAK_EXP=$DIR_BASE_BAK/autorefresh
DIR_BASE_LOG=$DIR_BASE/logs
FIL_LOG=$DIR_BASE_LOG/$DATE-1_ProdCopy.log


####################################################################
# Check# : Copy CALYPSO_PROD file from prod
####################################################################
echo "">${FIL_LOG}
echo "**********************************************************************" >>${FIL_LOG}
echo "#### 1.1 Copy ${SOURCE_SCHEMA} dumpfile from ${PROD_HOST} -START***** " >>${FIL_LOG}


echo `date ` :"Starting copy ${SOURCE_SCHEMA} dumpfile from ${PROD_HOST}:${PROD_BDIR}*****" >>${FIL_LOG}

cd ${DIR_BASE_BAK_EXP}

scp ${PROD_HOST}:${PROD_BDIR}/${DATE}-${SOURCE_SCHEMA}*.dp.dmp .

if [ $? -ne 0 ];then

  echo `date +%Y%m%d%H%M` " ***** ERROR: "There might be conection issue to PROD, please check! " *****" >>${FIL_LOG}
echo "#### 1.1 Copy ${SOURCE_SCHEMA} dumpfile from ${PROD_HOST} -END FAILURE***** " >>${FIL_LOG}
echo "****************************************************************************" >>${FIL_LOG}
  exit 99
fi

echo "#### 1.1 Copy ${SOURCE_SCHEMA} dumpfile from ${PROD_HOST} -END SUCCESS***** " >>${FIL_LOG}
echo "****************************************************************************" >>${FIL_LOG}

echo "">>${FIL_LOG}
echo "*********************************************************************" >>${FIL_LOG}
echo "#### 1.2 Confirmed dump file copy from PROD ${PROD_HOST}-START***** " >>${FIL_LOG}

echo "***********************************">>${FIL_LOG}
echo "Dump file list below: ">>${FIL_LOG}
ls -lth ${DIR_BASE_BAK_EXP}/${DATE}-${SOURCE_SCHEMA}*  >>${FIL_LOG}

echo `date +%Y%m%d%H%M` " Copy ${FIL_LOG} to ${APP_HOST} ${APP_LOGBASE}" >>${FIL_LOG}
hostname >>${FIL_LOG}

if [ $? -ne 0 ];then

  echo `date +%Y%m%d%H%M` " ***** ERROR: "There might be conection issue to APP Server, please check! " *****" >>${FIL_LOG}
echo "#### 1.2 Confirmed dump file copy from PROD ${PROD_HOST} -END FAILURE***** " >>${FIL_LOG}
echo "***************************************************************************" >>${FIL_LOG}
  exit 99
fi

echo "#### 1.2 Confirmed dump file copy from PROD ${PROD_HOST}-END SUCCESS***** " >>${FIL_LOG}
echo "**************************************************************************" >>${FIL_LOG}

scp ${FIL_LOG}  ${APP_HOST}:${APP_LOGBASE}
ssh ${APP_HOST} chmod 755 ${APP_LOGBASE}/${DATE}-1_ProdCopy.log

if [ $? -ne 0 ];then

  echo `date +%Y%m%d%H%M` " ***** ERROR: "There might be conection issue to APP Server, please check! " *****" >>${FIL_LOG}
  exit 99
fi

 

#!/usr/bin/ksh
#
# source Oracle environment variable
#
if [ -f $HOME/.profile-ORACLE-UMODA01 ];
then
        export ORAENV_ASK=NO
        . $HOME/.profile-ORACLE-UMODA01
fi

#
# other Oracle variable
#
export NLS_DATE_FORMAT='YYYY-MON-DD HH24:MI:SS'

#
# get script. run date
#
DATE=`date +"%Y%m%d"`
SOURCE_SCHEMA="$1"
DEST_SCHEMA="$2"

#PROD_HOST=hkmodpora01.hk.hibm.Ben
#PROD_BDIR=/u99/oradata/LMODA01/backups/autorefresh


export ORACLE_SID=UMODA01

DIR_BASE=$ORACLE_BASE/admin/scripts/autorefresh
DIR_BASE_SRC=$DIR_BASE/src
DIR_BASE_SQL=$DIR_BASE/sql
DIR_BASE_BAK=/u99/oradata/$ORACLE_SID/backups
DIR_BASE_BAK_EXP=$DIR_BASE_BAK/autorefresh
DIR_BASE_LOG=$DIR_BASE/logs
FIL_SQL_DRP=drop_all_objects.sql
FIL_SQL_DRP_CHK=drop_all_objects_chk.sql
FIL_SQL_KIL=kill_db_users.sql
FIL_SQL_REGRANT=regrant_${DEST_SCHEMA}.sql
FIL_SQL_KIL_CHK=kill_db_users_chk.sql
FIL_LOG=$DIR_BASE_LOG/$DATE-2_Mirror1AutoRefresh.log

APP_HOST=hkmodd01.it.hk.hibm.Ben
APP_ScrambleSriptDir=/appvol/moda/daily_refresh/scramble_script
APP_LOGBASE=/appvol/moda/daily_refresh/logs
SRAMBLE_BASE="/u01/in/$ORACLE_SID/oracle/admin/scripts/autorefresh/sql" 

####################################################################
# Check# : CHECK if CALYPSO_PROD export file exists
####################################################################
# variables
echo "##################################################">${FIL_LOG}
echo "${DEST_SCHEMA} REFRESH WILL BE PERFORMED ........">>${FIL_LOG}
echo "START TIME IS:" `date +%Y%m%d%H%M`  >> ${FIL_LOG}
echo "##################################################">>${FIL_LOG}

echo "*****************************************************">>${FIL_LOG}
echo "## 2.1 PRE-Refresh tasks before refresh !-START *****" >>${FIL_LOG}

echo "">>${FIL_LOG}

echo "*****************************************************************************************">>${FIL_LOG}
echo "#### 2.1.1 Copy ${SRAMBLE_BASE}/Moda_Scrambler_Script.sql from APP hkmodd01! -START***** " >>${FIL_LOG}

scp ${APP_HOST}:${APP_ScrambleSriptDir}/Moda_Scrambler_Script.sql ${SRAMBLE_BASE}

if [ $? -ne 0 ];then

echo `date +%Y%m%d%H%M` "***** ERROR: "There might be conection issue or filename issue to hkmodd01, please check! "-Sramble copy ERROR *****" >> ${FIL_LOG}
echo "#### 2.1.1 Copy ${SRAMBLE_BASE}/Moda_Scrambler_Script.sql from APP hkmodd01! -END FAILURE!***** " >>${FIL_LOG}
echo "****************************************************************************************i*******">>${FIL_LOG}

  exit 99
fi
 
echo "">>${FIL_LOG}

echo "#### 2.1.1 Copy ${SRAMBLE_BASE}/Moda_Scrambler_Script.sql from APP hkmodd01! -END SUCCESS***** " >>${FIL_LOG}
echo "***********************************************************************************************">>${FIL_LOG}

echo "">>${FIL_LOG}

echo "******************************************************************">>${FIL_LOG}
echo "#### 2.1.2 Check if ${SOURCE_SCHEMA} Dump file exists! -START*****" >>${FIL_LOG}


FILE_FTPEXP=${DIR_BASE_BAK_EXP}/${DATE}-${SOURCE_SCHEMA}04.dp.dmp

# Check
echo `date +%Y%m%d%H%M` " **** Checking if ${FILE_FTPEXP} export files are existing *****" >> ${FIL_LOG}
cnt=0
while  [ ! -f ${FILE_FTPEXP} ];
do
   if [ ${cnt} -eq 3 ]; then
        MSG_TEXT="No export file found ${FILE_FTPEXP}"
        #${PATROL_CALLMSEND} ${Ben_APP} ${Ben_INF} ${LOGFILE} CRITICAL "${MSG_TEXT}"
        echo `date +%Y%m%d%H%M` " ***** ERROR: " ${MSG_TEXT} " *****" >> ${FIL_LOG}
echo "#### 2.1.2 Check if ${SOURCE_SCHEMA} Dump file exists! -END FAILURE*****" >>${FIL_LOG}
echo "************************************************************************">>${FIL_LOG}
        exit 99
   fi
   echo ${cnt} "-" `date +%Y%m%d%H%M` " No export file found -ERROR *****" >> ${FIL_LOG}
   cnt=`expr ${cnt} + 1`
   #sleep 120
   sleep 5
done
echo "">>${FIL_LOG}

echo "#### 2.1.2 Check if ${SOURCE_SCHEMA} Dump file exists!-END SUCCESS " >>${FIL_LOG}
echo "*******************************************************************">>${FIL_LOG}
####################################################################
# Action  : kill all CALYPSO_EOD connections
####################################################################
echo "************************************************************************************">>${FIL_LOG}
echo "#### 2.1.3 Kill all connected session of  ${DEST_SCHEMA} BEFORE Refresh!-START***** " >>${FIL_LOG}
SEARCH_STR=${DEST_SCHEMA}
sqlplus '/ as sysdba'  @${DIR_BASE_SQL}/${FIL_SQL_KIL} ${DIR_BASE_LOG}/${DATE}-${SEARCH_STR}-${FIL_SQL_KIL} ${SEARCH_STR}  >> ${FIL_LOG}
sqlplus '/ as sysdba'  @${DIR_BASE_LOG}/${DATE}-${SEARCH_STR}-${FIL_SQL_KIL} >> ${FIL_LOG}
if [ $? -ne 0 ];then

  echo `date +%Y%m%d%H%M` " ***** ERROR: "Please check if the sql exists or correct! " *****" >>${FIL_LOG}
echo "#### 2.1.3 Kill all connected session of  ${DEST_SCHEMA} BEFORE Refresh!-END FAILURE!***** " >>${FIL_LOG}
echo "*******************************************************************************************">>${FIL_LOG}
  exit 99
fi

echo "">>${FIL_LOG}
echo "#### 2.1.3 Kill all connected session of  ${DEST_SCHEMA} BEFORE Refresh!-END SUCCESS***** " >>${FIL_LOG}
echo "#### 2.1.3 Check log file  ${DIR_BASE_LOG}/${DATE}-${SEARCH_STR}-${FIL_SQL_KIL}.out for step detail!">>${FIL_LOG}
echo "****************************************************************************************************">>${FIL_LOG}
####################################################################
# Action   : Drop all objects
####################################################################
echo "*************************************************************************">>${FIL_LOG}
echo "#### 2.1.4 Drop all objects of ${DEST_SCHEMA} BEFORE refresh -START***** " >>${FIL_LOG}
# for CALYPSO_EOD
##############################
SCHEMA=${DEST_SCHEMA}
sqlplus '/ as sysdba'  @${DIR_BASE_SQL}/${FIL_SQL_DRP} ${DIR_BASE_LOG}/${DATE}-${SCHEMA}-${FIL_SQL_DRP} ${SCHEMA} >> ${FIL_LOG}
sqlplus '/ as sysdba'  @${DIR_BASE_LOG}/${DATE}-${SCHEMA}-${FIL_SQL_DRP} >> ${FIL_LOG}
echo `date +%Y%m%d%H%M` " ***** Dropping all ${SCHEMA} objects - END *****" >> ${FIL_LOG}
##############################
# Check# 4  : CHECK whether all objects have been dropped
##############################
echo `date +%Y%m%d%H%M` " ***** Checking if all objects have been dropped for ${SCHEMA} *****" >> ${FIL_LOG}
sqlplus '/ as sysdba' @${DIR_BASE_SQL}/${FIL_SQL_DRP_CHK} ${DIR_BASE_LOG}/${DATE}-${SCHEMA}-${FIL_SQL_DRP_CHK}.out ${SCHEMA} >> ${FIL_LOG}
if [ `cat ${DIR_BASE_LOG}/${DATE}-${SCHEMA}-${FIL_SQL_DRP_CHK}.out | wc -l` -gt 0 ]; then
        MSG_TEXT="${SCHEMA} objects cannot be dropped"
        echo `date +%Y%m%d%H%M` " ***** ERROR: " ${MSG_TEXT} " *****" >> ${FIL_LOG}
echo "#### 2.1.4 Drop all objects of ${DEST_SCHEMA} BEFORE refresh -END FAILURE!***** " >>${FIL_LOG}
echo "********************************************************************************">>${FIL_LOG}
        exit 99
fi
echo "#### 2.1.4 Drop all objects of ${DEST_SCHEMA} BEFORE refresh  -END SUCCESS *****" >> ${FIL_LOG}
echo "#### 2.1.4 Check log file ${DIR_BASE_LOG}/${DATE}-${SCHEMA}-${FIL_SQL_DRP}.out for step detail!">>${FIL_LOG}
echo "***********************************************************************************************">>${FIL_LOG}

####################################################################
# import CALYPSO_PROD schema
####################################################################
echo "">>${FIL_LOG}
echo "## 2.1 PRE-Refresh tasks before refresh !-END SUCCESS ****" >>${FIL_LOG}
echo "********************************************************">>${FIL_LOG}

echo "">>${FIL_LOG}

echo "*********************************************************************************">>${FIL_LOG}
echo "## 2.2 Perform. ${SOURCE_SCHEMA} to ${DEST_SCHEMA} refresh procedure! -START***** " >>${FIL_LOG}

echo "">>${FIL_LOG}

echo $DIR_BASE_SRC >>${FIL_LOG}
. $DIR_BASE_SRC/prod2mirror1_impdp.ksh ${SOURCE_SCHEMA} ${DEST_SCHEMA} ${DATE}

echo "">>${FIL_LOG}

echo "## 2.2 Perform. ${SOURCE_SCHEMA} to ${DEST_SCHEMA} refresh procedure! -END SUCCESS***** " >>${FIL_LOG}
echo "***************************************************************************************">>${FIL_LOG}
####################################################################
# regrant privilege CALYPSO_PROD schema
####################################################################
echo "">>${FIL_LOG}

echo "********************************************************************">>${FIL_LOG}
echo "## 2.3 Post-refresh ${SOURCE_SCHEMA} to ${DEST_SCHEMA} -START ***** " >>${FIL_LOG}

echo "">>${FIL_LOG}

echo "********************************************************************************************************">>${FIL_LOG}
echo "#### 2.3.1 Grant privilge and create synonym for ${DEST_SCHEMA}_USER and ${DEST_SCHEMA}_RO -START ***** " >>${FIL_LOG}
sqlplus '/ as sysdba' @${DIR_BASE_SQL}/${FIL_SQL_REGRANT} ${DIR_BASE_LOG}/${DATE}-${FIL_SQL_REGRANT}
sqlplus '/ as sysdba' @${DIR_BASE_LOG}/${DATE}-${FIL_SQL_REGRANT}

if [ $? -ne 0 ];then

  echo `date +%Y%m%d%H%M` " ***** ERROR: "Please check if the sql exists or correct! " *****" >>${FIL_LOG}
echo "#### 2.3.1 Grant privilge and create synonym for ${DEST_SCHEMA}_USER and ${DEST_SCHEMA}_RO -END FAILURE!***** " >>${FIL_LOG}
echo "**************************************************************************************************************">>${FIL_LOG}
  exit 99
fi

echo "">>${FIL_LOG}

echo "#### 2.3.1 Grant privilge and create synonym for ${DEST_SCHEMA}_USER and ${DEST_SCHEMA}_RO -END SUCCESS ***** " >>${FIL_LOG}
echo "#### 2.3.1 Check log file ${DIR_BASE_LOG}/${DATE}-${FIL_SQL_REGRANT}.out for step detail!">>${FIL_LOG}
echo "**************************************************************************************************************">>${FIL_LOG}

####################################################################
#re-compile CALYPSO_MIRROR1 schema
#################################################
echo "">>${FIL_LOG}

echo "*****************************************************************************************************">>${FIL_LOG}
echo "#### 2.3.2 Re-compile USER ${DEST_SCHEMA} and ${DEST_SCHEMA}_USER and ${DEST_SCHEMA}_RO -START ***** " >>${FIL_LOG}

sqlplus '/ as sysdba' @${DIR_BASE_SQL}/recompile-schema.sql ${DEST_SCHMEA}           
sqlplus '/ as sysdba' @${DIR_BASE_SQL}/recompile-schema.sql ${DEST_SCHMEA}_RO
sqlplus '/ as sysdba' @${DIR_BASE_SQL}/recompile-schema.sql ${DEST_SCHMEA}_APP

echo "">>${FIL_LOG}

echo "#### 2.3.2 Re-compile USER ${DEST_SCHEMA} and ${DEST_SCHEMA}_USER and ${DEST_SCHEMA}_RO -END SUCCESS ***** " >>${FIL_LOG}
echo "***********************************************************************************************************">>${FIL_LOG}

####################################################################
#Housekeep CALYPSO_MIRROR1 dump for 8 days
#################################################
echo "">>${FIL_LOG}

echo "************************************************************************">>${FIL_LOG}
echo "#### 2.3.3 Housekeep dump file of ${DEST_SCHEMA} to 8 days -START ***** " >>${FIL_LOG}

/usr/bin/find ${DIR_BASE_BAK_EXP}/*.dp.dmp -mtime +8 -exec ls -la {} \; >> ${FIL_LOG}
/usr/bin/find ${DIR_BASE_BAK_EXP}/*.dp.dmp -mtime +8 -exec rm -f {} \; >> ${FIL_LOG}

if [ $? -ne 0 ];then

  echo `date +%Y%m%d%H%M` " ***** ERROR: "Please check if the sql exists or correct! " *****" >>${FIL_LOG}
echo "#### 2.3.3 Housekeep dump file of ${DEST_SCHEMA} to 8 days -END FAILURE!***** " >>${FIL_LOG}
echo "******************************************************************************">>${FIL_LOG}
  exit 99
fi

echo "">>${FIL_LOG}

echo "#### 2.3.3 Housekeep dump file of ${DEST_SCHEMA} to 8 days -END SUCCESS ***** " >>${FIL_LOG}
echo "******************************************************************************">>${FIL_LOG}

echo "">>${FIL_LOG}

echo "*********************************************************">>${FIL_LOG}
echo "## 2.4 Remove dump file of ${SOURCE_SCHEMA} -START ***** " >>${FIL_LOG}

# variables

FILE_FTPEXP=${DIR_BASE_BAK_EXP}/${DATE}-${SOURCE_SCHEMA}04.dp.dmp

cd ${DIR_BASE_BAK_EXP}

rm -f ${DATE}-${SOURCE_SCHEMA}*.dp.dmp


if [ $? -ne 0 ];then

  echo `date +%Y%m%d%H%M` " ***** ERROR: "There might be dumpfile name issue, please check! " *****" >> ${FIL_LOG}
echo "## 2.4 Remove dump file of ${SOURCE_SCHEMA} -END FAILURE! ***** " >>${FIL_LOG}
echo "****************************************************************">>${FIL_LOG}
  exit 99
fi

echo "">>${FIL_LOG}

echo "## 2.4 Remove dump file of ${SOURCE_SCHEMA} -END SUCCESS ***** " >>${FIL_LOG}
echo "***************************************************************">>${FIL_LOG}

echo "*************************************************************************************************">>${FIL_LOG}
echo "## 2.5 Copy refresh log file ${DATE}-2_Mirror1AutoRefresh.log to APP Server hkmodd01 -START *****">>${FIL_LOG}

scp ${FIL_LOG} ${APP_HOST}:${APP_LOGBASE}
ssh ${APP_HOST} chmod 755 ${APP_LOGBASE}/${DATE}-2_Mirror1AutoRefresh.log

if [ $? -ne 0 ];then

  echo `date +%Y%m%d%H%M` " ***** ERROR: "There might be conection issue or filename issue to hkmodd01, please check! " *****" >> ${FIL_LOG}
echo "## 2.5 Copy refresh log file ${DATE}-2_Mirror1AutoRefresh.log to APP Server hkmodd01 -END FAILURE! *****">>${FIL_LOG}
echo "********************************************************************************************************">>${FIL_LOG}
  exit 99
fi

echo "## 2.5 Copy refresh log file ${DATE}-2_Mirror1AutoRefresh.log to APP Server hkmodd01 -END SUCCESS *****">>${FIL_LOG}
echo "*******************************************************************************************************">>${FIL_LOG}


scp ${FIL_LOG} ${APP_HOST}:${APP_LOGBASE}
ssh ${APP_HOST} chmod 755 ${APP_LOGBASE}/${DATE}-2_Mirror1AutoRefresh.log

-----------

#!/usr/bin/ksh

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

#
# generic setting
#
TIME=`date +"%Y%m%d%H"`
ORACLE_TARGET=UMODA01

#App server info

#APP_HOST=hkmodd01.it.hk.hibm.Ben
#APP_ScrambleSriptDir=/appvol/moda/daily_refresh/scramble_script
#APP_LOGBASE=/appvol/moda/daily_refresh/logs

LOG_BASE="/u01/in/$ORACLE_TARGET/oracle/admin/scripts/autorefresh/logs"
DMP_BASE="/u99/oradata//${ORACLE_TARGET}/backups/autorefresh"
SQL_BASE="/u01/in/$ORACLE_TARGET/oracle/admin/scripts/autorefresh/sql"
SRAMBLE_BASE="/u01/in/$ORACLE_TARGET/oracle/admin/scripts/autorefresh/sql"

#FROMSCHEMA="$1"
#TOSCHEMA="CALYPSO_MIRROR1"
TOSCHEMA="$1"
DATE=`date +"%Y%m%d"`
#PARALLEL="$4"

APP_HOST=hkmodd01.it.hk.hibm.Ben
APP_LOGBASE=/appvol/moda/daily_refresh/logs


FIL_LOG=${LOG_BASE}/${DATE}-3_DumpFilterMirror1.log

EXPDP_DMPDIR=EXPDP_DIR
EXPDP_LOGDIR=EXPDP_LOG

# other Oracle variable
#
export NLS_DATE_FORMAT='YYYY-MON-DD HH24:MI:SS'

#
# get script. from hkmodd01
#
#echo "================Copy sramble scripts from ${APP_HOST}============" >${FIL_LOG}
#scp ${APP_HOST}:/${APP_ScrambleSriptDir}/Moda_Scrambler_Script.sql ${SRAMBLE_BASE}

#if [ $? -ne 0 ];then

#  echo `date +%Y%m%d%H%M` " ***** ERROR: "There might be conection issue to hkmodd01, please check! " *****" >> ${FIL_LOG}
#  exit 99
# else
#
#echo "MIRROR1 scramble scripts Moda_Scrambler_Script.sql has been copied to ${SRAMBLE_BASE} completely" >>${FIL_LOG}
#fi

#
# command and run log
#

#/usr/bin/gunzip ${DMP_BASE}/${DATE}-${FROMSCHEMA}*.dp.dmp.gz
echo "">${FIL_LOG}

echo "*************************************************************************" >>${FIL_LOG}
echo "#### 3.1 Create DUMP DIRECTORY EXPDP_DIR AND EXPDP_LOG in DB -START***** " >>${FIL_LOG}

sqlplus '/ as sysdba' @${SQL_BASE}/dp01_create_directory.sql ${EXPDP_DMPDIR} ${DMP_BASE}
sqlplus '/ as sysdba' @${SQL_BASE}/dp01_create_directory.sql ${EXPDP_LOGDIR} ${LOG_BASE}

if [ $? -ne 0 ];then

  echo `date +%Y%m%d%H%M` " ***** ERROR: "Please check if the sql exists or correct! " *****" >>${FIL_LOG}
echo "#### 3.1 Create DUMP DIRECTORY EXPDP_DIR AND EXPDP_LOG in DB -END FAILURE***** " >>${FIL_LOG}
echo "*******************************************************************************" >>${FIL_LOG}

  exit 99
fi

echo "">>${FIL_LOG}

echo "#### 3.1 Create DUMP DIRECTORY EXPDP_DIR AND EXPDP_LOG in DB -END SUCCESS***** " >>${FIL_LOG}
echo "*******************************************************************************" >>${FIL_LOG}

# variables
echo "">>${FIL_LOG}
echo "**********************************************************" >>${FIL_LOG}
echo "#### 3.2 Take dump of ${TOSCHEMA} with filter -START***** " >>${FIL_LOG}


echo "*****Check if dumpfile ${DATE}-${TOSCHEMA}.dp.dmp already exists first!*****" >>${FIL_LOG}

FILE_FTPEXP=${DMP_BASE}/${DATE}-${TOSCHEMA}.dp.dmp

# Check
echo `date +%Y%m%d%H%M` " **** Checking if ${FILE_FTPEXP} export files is already existing *****" >> ${FIL_LOG}

if [ -f ${FILE_FTPEXP} ]; then
        MSG_TEXT="Already exists dump file ${FILE_FTPEXP},it will be removed for new dump!"
        #${PATROL_CALLMSEND} ${Ben_APP} ${Ben_INF} ${LOGFILE} CRITICAL "${MSG_TEXT}"
        echo `date +%Y%m%d%H%M` " ***** WARNING: " ${MSG_TEXT} " *****" >> ${FIL_LOG}
        rm -rf ${FILE_FTPEXP}
 else
        echo "${FILE_FTPEXP} export files is not exist, will proceed the export...">>${FIL_LOG}

fi
echo "****Starting export ${TOSCHEMA} with filter......">>${FIL_LOG}
expdp "'/ as sysdba'" \
dumpfile=${EXPDP_DMPDIR}:${DATE}-${TOSCHEMA}.dp.dmp \
logfile=${EXPDP_LOGDIR}:${DATE}-EXPORT-${TOSCHEMA}.log \
query=${TOSCHEMA}.BO_AUDIT:\"where modif_date\>to_date\(\'${DATE}\',\'yyyymmdd\'\)-7\",${TOSCHEMA}.BO_TASK:\"where task_datetime\>to_date\(\'${DATE}\',\'yyyymmdd\'\)-7\" \
schemas=${TOSCHEMA} \


sleep 5

if [ `grep "successfully completed" ${LOG_BASE}/${DATE}-EXPORT-${TOSCHEMA}.log | wc -l` -ne 1 ]; then
        MSG_TEXT="${TOSCHEMA} dump might FAILED!"
        echo `date +%Y%m%d%H%M` " ***** ERROR: " ${MSG_TEXT} " *****" >> ${FIL_LOG}
echo "#### 3.2 Take dump of ${TOSCHEMA} with filter -END FAILURE***** " >>${FIL_LOG}
echo "****************************************************************" >>${FIL_LOG}
  exit 99
fi

cat ${LOG_BASE}/${DATE}-EXPORT-${TOSCHEMA}.log >>${FIL_LOG}


echo "">>${FIL_LOG}

echo "#### 3.2 Take dump of ${TOSCHEMA} with filter -END SUCCESS***** " >>${FIL_LOG}
echo "#### 3.2 Check log file ${LOG_BASE}/${DATE}-EXPORT-${TOSCHEMA}.log for step detail! " >>${FIL_LOG}
echo "************************************************************************************" >>${FIL_LOG}

##Drop directory
echo "">>${FIL_LOG}

echo "***********************************************************************" >>${FIL_LOG}
echo "#### 3.3 Drop DUMP DIRECTORY EXPDP_DIR AND EXPDP_LOG in DB -START***** " >>${FIL_LOG}

sqlplus '/ as sysdba' @${SQL_BASE}/dp02_drop_directory.sql ${EXPDP_DMPDIR} ${DMP_BASE}
sqlplus '/ as sysdba' @${SQL_BASE}/dp02_drop_directory.sql ${EXPDP_LOGDIR} ${LOG_BASE}

if [ $? -ne 0 ];then

  echo `date +%Y%m%d%H%M` " ***** ERROR: "Please check if the sql exists or correct! " *****" >>${FIL_LOG}
echo "#### 3.3 Drop DUMP DIRECTORY EXPDP_DIR AND EXPDP_LOG in DB -END FAILURE***** " >>${FIL_LOG}
echo "*******************************************************************************" >>${FIL_LOG}

  exit 99
fi

echo "">>${FIL_LOG}

echo "#### 3.3 Drop DUMP DIRECTORY EXPDP_DIR AND EXPDP_LOG in DB -END SUCCESS***** " >>${FIL_LOG}
echo "*****************************************************************************" >>${FIL_LOG}

echo "*********************************************************************************************">>${FIL_LOG}
echo "#### 3.4 Copy refresh log file ${DATE}-3_DumpFilterMirror1.log to APP Server hkmodd01 -START *****">>${FIL_LOG}

scp ${FIL_LOG} ${APP_HOST}:${APP_LOGBASE}
ssh ${APP_HOST} chmod 755 ${APP_LOGBASE}/${DATE}-3_DumpFilterMirror1.log

if [ $? -ne 0 ];then

  echo `date +%Y%m%d%H%M` " ***** ERROR: "There might be conection issue or filename issue to hkmodd01, please check! " *****" >> ${FIL_LOG}
echo "#### 3.4 Copy refresh log file ${DATE}-3_DumpFilterMirror1.log to APP Server hkmodd01 -END FAILURE! **">>${FIL_LOG}
echo "****************************************************************************************************">>${FIL_LOG}
  exit 99
fi

echo "#### 3.4 Copy refresh log file ${DATE}-3_DumpFilterMirror1.log to APP Server hkmodd01 -END SUCCESS **">>${FIL_LOG}
echo "***************************************************************************************************">>${FIL_LOG}


scp ${FIL_LOG} ${APP_HOST}:${APP_LOGBASE}
ssh ${APP_HOST} chmod 755 ${APP_LOGBASE}/${DATE}-3_DumpFilterMirror1.log
--------------

 

#!/usr/bin/ksh

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

#
# generic setting
#
TIME=`date +"%Y%m%d%H"`
DATE=`date +"%Y%m%d"`
SOURCE_SID=UMODA01
DEST_SID=UMODA02
TOSCHEMA="$1"
#TOSCHEMA=CALYPSO_MIRROR1

LOG_BASE="/u01/in/$SOURCE_SID/oracle/admin/scripts/autorefresh/logs"
DMP_BASE="/u99/oradata/${SOURCE_SID}/backups/autorefresh"
#SQL_BASE="/u01/in/$ORACLE_TARGET/oracle/admin/scripts/autorefresh/sql"
FIL_LOG=${LOG_BASE}/${DATE}-4_Mirror1DumpCopy2Dev.log

DEST_HOST=HKMODDORA02
DEST_BDIR="/u99/oradata/${DEST_SID}/backups/autorefresh/"
#DEST_LOGDIR="/u01/in/${DEST_SID}/oracle/admin/scripts/autorefresh/logs"
DUMPLIST=${LOG_BASE}/${DATE}-4_DEV-MIRROR1-DumpList.log

APP_HOST=hkmodd01.it.hk.hibm.Ben
APP_LOGBASE=/appvol/moda/daily_refresh/logs


#
export NLS_DATE_FORMAT='YYYY-MON-DD HH24:MI:SS'

#Check if Mirror dump generate

####################################################################
# Check# : Confirm if CALYPSO_MIRROR1 export file exists and scp to hkmodd12
####################################################################
# variables
echo "">${FIL_LOG}
echo "*******************************************************************************" >>${FIL_LOG}
echo "#### 4.1 Copy dumpfile ${DATE}-${TOSCHEMA}.dp.dmp to ${DEST_HOST}  -START***** " >>${FIL_LOG}


FILE_FTPEXP=${DMP_BASE}/${DATE}-${TOSCHEMA}.dp.dmp

#Check
echo `date +%Y%m%d%H%M` " **** Checking if ${FILE_FTPEXP} export files are existing *****" >> ${FIL_LOG}
cnt=0
while  [ ! -f ${FILE_FTPEXP} ];
do
   if [ ${cnt} -eq 3 ]; then
        MSG_TEXT="No export file found ${FILE_FTPEXP}"
        #${PATROL_CALLMSEND} ${Ben_APP} ${Ben_INF} ${LOGFILE} CRITICAL "${MSG_TEXT}"
        echo `date +%Y%m%d%H%M` " ***** ERROR: " ${MSG_TEXT} " *****" >> ${FIL_LOG}
echo "#### 4.1 Copy dumpfile ${DATE}-${TOSCHEMA}.dp.dmp to ${DEST_HOST} -END FAILURE***** " >>${FIL_LOG}
echo "************************************************************************************" >>${FIL_LOG}
        exit 99
   fi
   echo ${cnt} "-" `date +%Y%m%d%H%M` " No export file found" >> ${FIL_LOG}
   cnt=`expr ${cnt} + 1`
   #sleep 120
   sleep 5
done
echo " ${FILE_FTPEXP} export file is existing, will copy to ${DEST_HOST} right away ......">>${FIL_LOG}

#SCP TO HKMODD12

cd ${DMP_BASE}
scp ${FILE_FTPEXP} ${DEST_HOST}:${DEST_BDIR}

if [ $? -ne 0 ];then

  echo `date +%Y%m%d%H%M` " ***** ERROR: "There might be conection issue to hkmodd12, please check! " *****" >> ${FIL_LOG}
echo "#### 4.1 Copy dumpfile ${DATE}-${TOSCHEMA}.dp.dmp to ${DEST_HOST} -END FAILURE***** " >>${FIL_LOG}
echo "************************************************************************************" >>${FIL_LOG}
  exit 99
fi
echo "MIRROR1 Dump ${DATE}-${TOSCHEMA}.dp.dmp have been copied to ${DEST_HOST}:${DEST_BDIR} completely" >>${FIL_LOG}

echo "">>${FIL_LOG}
echo "#### 4.1 Copy dumpfile ${DATE}-${TOSCHEMA}.dp.dmp to ${DEST_HOST} -END SUCCESS***** " >>${FIL_LOG}
echo "************************************************************************************" >>${FIL_LOG}

#HOUSEKEEP hkmodd12 for 8 days

echo "">>${FIL_LOG}
echo "************************************************************************************" >>${FIL_LOG}
echo "#### 4.2 HouseKeep dumpfile ${DATE}-${TOSCHEMA}.dp.dmp for 8 days -START***** " >>${FIL_LOG}

echo "MIRROR1 Dump ${DATE}-${TOSCHEMA}.dp.dmp housekeep for 8 days in ${DEST_HOST}" >>${FIL_LOG}
ssh ${DEST_HOST} ls -lth  /u99/oradata/UMODA02/backups/autorefresh/ >>${FIL_LOG}
ssh ${DEST_HOST} "find /u99/oradata/UMODA02/backups/autorefresh/  -type f -mtime +8 -exec rm {} \;"

if [ $? -ne 0 ];then

  echo `date +%Y%m%d%H%M` " ***** ERROR: "There might be conection issue to hkmodd12, please check! " *****" >> ${FIL_LOG}
echo "#### 4.2 HouseKeep dumpfile ${DATE}-${TOSCHEMA}.dp.dmp for 8 days -END FAILURE***** " >>${FIL_LOG}
echo "************************************************************************************" >>${FIL_LOG}
  exit 99
fi

ssh ${DEST_HOST} ls -lth  /u99/oradata/UMODA02/backups/autorefresh/ >>${FIL_LOG}

echo "">>${FIL_LOG}
echo "#### 4.2 HouseKeep dumpfile ${DATE}-${TOSCHEMA}.dp.dmp for 8 days -END SUCCESS***** " >>${FIL_LOG}
echo "************************************************************************************" >>${FIL_LOG}

#Copy Dumpfile list file to App server hkmodd01
echo "">>${FIL_LOG}
echo "*********************************************************" >>${FIL_LOG}
echo "#### 4.3 Genetate dumplist file ${DUMPLIST}  -START***** " >>${FIL_LOG}

ssh ${DEST_HOST} hostname  > ${DUMPLIST}
ssh ${DEST_HOST} ls -lth ${DEST_BDIR} >> ${DUMPLIST}

if [ $? -ne 0 ];then

  echo `date +%Y%m%d%H%M` " ***** ERROR: "There might be conection issue to hkmodd12, please check! " *****" >> ${FIL_LOG}
echo "#### 4.3 Genetate dumplist file ${DUMPLIST} -END FAILURE***** " >>${FIL_LOG}
echo "**************************************************************" >>${FIL_LOG}
  exit 99

fi

echo "#### 4.3 Genetate dumplist file ${DUMPLIST} -END SUCCESS***** " >>${FIL_LOG}
echo "**************************************************************" >>${FIL_LOG}

echo "">>${FIL_LOG}
echo "*****************************************************************************************************************************************" >>${FIL_LOG}
echo "#### 4.4 Copy logfile ${DATE}-4_Mirror1DumpCopy2Dev.log and dumplist file ${DATE}-4_DEV-MIRROR1-DumpList.log to APP hkmodd01 -START***** " >>${FIL_LOG}

scp ${DUMPLIST}  ${APP_HOST}:${APP_LOGBASE}
ssh ${APP_HOST} chmod 755 ${APP_LOGBASE}/${DATE}-4_DEV-MIRROR1-DumpList.log
scp ${FIL_LOG} ${APP_HOST}:${APP_LOGBASE}
ssh ${APP_HOST} chmod 755 ${APP_LOGBASE}/${DATE}-4_Mirror1DumpCopy2Dev.log

if [ $? -ne 0 ];then

  echo `date +%Y%m%d%H%M` " ***** ERROR: "There might be conection issue to hkmodd01, please check! " *****" >> ${FIL_LOG}
echo "#### 4.4 Copy logfile ${DATE}-4_Mirror1DumpCopy2Dev.log and dumplist file ${DATE}-4_DEV-MIRROR1-DumpList.log to APP hkmodd01 -END FAILURE***** " >>${FIL_LOG}
echo "***********************************************************************************************************************************************" >>${FIL_LOG}
  exit 99

fi

echo `date +%Y%m%d%H%M` " ***** Dumpfile list ${DUMPLIST} copy to ${APP_HOST}:${APP_LOGBASE} successfully !" >>${FIL_LOG}
echo `date +%Y%m%d%H%M` " ***** Dumpfile list ${FIL_LOG} copy to ${APP_HOST}:${APP_LOGBASE} successfully !" >>${FIL_LOG}
echo "#### 4.4 Copy logfile ${DATE}-4_Mirror1DumpCopy2Dev.log and dumplist file ${DATE}-4_DEV-MIRROR1-DumpList.log to APP hkmodd01 -END SUCCESS***** " >>${FIL_LOG}
echo "***********************************************************************************************************************************************" >>${FIL_LOG}

scp ${DUMPLIST}  ${APP_HOST}:${APP_LOGBASE}
ssh ${APP_HOST} chmod 755 ${APP_LOGBASE}/${DATE}-4_DEV-MIRROR1-DumpList.log
scp ${FIL_LOG} ${APP_HOST}:${APP_LOGBASE}
ssh ${APP_HOST} chmod 755 ${APP_LOGBASE}/${DATE}-4_Mirror1DumpCopy2Dev.log
-------------


#!/usr/bin/ksh

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

#
# generic setting
#
TIME=`date +"%Y%m%d%H"`
ORACLE_TARGET=UMODA01

#App server info

#APP_HOST=hkmodd01.it.hk.hibm.Ben
#APP_ScrambleSriptDir=/appvol/moda/daily_refresh/scramble_script
#APP_LOGBASE=/appvol/moda/daily_refresh/logs

LOG_BASE="/u01/in/$ORACLE_TARGET/oracle/admin/scripts/autorefresh/logs"
DMP_BASE="/u99/oradata//${ORACLE_TARGET}/backups/autorefresh"
SQL_BASE="/u01/in/$ORACLE_TARGET/oracle/admin/scripts/autorefresh/sql"
SRAMBLE_BASE="/u01/in/$ORACLE_TARGET/oracle/admin/scripts/autorefresh/sql"

FROMSCHEMA="$1"
TOSCHEMA="$2"
DATE=$3
#PARALLEL="$4"

FIL_LOG=${LOG_BASE}/${DATE}-2_Mirror1AutoRefresh.log

EXPDP_DMPDIR=EXPDP_DIR
EXPDP_LOGDIR=EXPDP_LOG

# other Oracle variable
#
export NLS_DATE_FORMAT='YYYY-MON-DD HH24:MI:SS'

#
# get script. from hkmodd01
#
#echo "================Copy sramble scripts from ${APP_HOST}============" >${FIL_LOG}
#scp ${APP_HOST}:/${APP_ScrambleSriptDir}/Moda_Scrambler_Script.sql ${SRAMBLE_BASE}

#if [ $? -ne 0 ];then

#  echo `date +%Y%m%d%H%M` " ***** ERROR: "There might be conection issue to hkmodd01, please check! " *****" >> ${FIL_LOG}
#  exit 99
# else
#
#echo "MIRROR1 scramble scripts Moda_Scrambler_Script.sql has been copied to ${SRAMBLE_BASE} completely" >>${FIL_LOG}
#fi

#
# command and run log
#

#/usr/bin/gunzip ${DMP_BASE}/${DATE}-${FROMSCHEMA}*.dp.dmp.gz
echo "">>${FIL_LOG}

echo "***************************************************************************" >>${FIL_LOG}
echo "#### 2.2.1 Create DUMP DIRECTORY EXPDP_DIR AND EXPDP_LOG in DB -START***** " >>${FIL_LOG}
echo "***************************************************************************" >>${FIL_LOG}

sqlplus '/ as sysdba' @${SQL_BASE}/dp01_create_directory.sql ${EXPDP_DMPDIR} ${DMP_BASE}
sqlplus '/ as sysdba' @${SQL_BASE}/dp01_create_directory.sql ${EXPDP_LOGDIR} ${LOG_BASE}

if [ $? -ne 0 ];then

  echo `date +%Y%m%d%H%M` " ***** ERROR: "Please check if the sql exists or correct! " *****" >>${FIL_LOG}
echo "*********************************************************************************" >>${FIL_LOG}
echo "#### 2.2.1 Create DUMP DIRECTORY EXPDP_DIR AND EXPDP_LOG in DB -END FAILURE***** " >>${FIL_LOG}
echo "*********************************************************************************" >>${FIL_LOG}

  exit 99
fi


echo "">>${FIL_LOG}

echo "*********************************************************************************" >>${FIL_LOG}
echo "#### 2.2.1 Create DUMP DIRECTORY EXPDP_DIR AND EXPDP_LOG in DB -END SUCCESS***** " >>${FIL_LOG}
echo "*********************************************************************************" >>${FIL_LOG}

echo "">>${FIL_LOG}

echo "*************************************************************" >>${FIL_LOG}
echo "#### 2.2.2 Import ${TOSCHEMA} from ${FROMSCHEMA} -START***** " >>${FIL_LOG}
echo "*************************************************************" >>${FIL_LOG}

echo "*****Starting ${TOSCHEMA} import.......">>${FIL_LOG}

impdp "'/ as sysdba'" \
dumpfile=${EXPDP_DMPDIR}:${DATE}-${FROMSCHEMA}%U.dp.dmp \
logfile=${EXPDP_LOGDIR}:${DATE}-IMPORT-${FROMSCHEMA}.log \
remap_schema=${FROMSCHEMA}:${TOSCHEMA} \
remap_tablespace=${FROMSCHEMA}_DATA:${TOSCHEMA}_DATA \
remap_tablespace=${FROMSCHEMA}_INDEX:${TOSCHEMA}_INDEX \
exclude=GRANT \
table_exists_action=replace \
parallel=4

sleep 5

if [ `grep "ORA" ${LOG_BASE}/${DATE}-IMPORT-${FROMSCHEMA}.log | wc -l` -gt 1 ]; then
        MSG_TEXT="${TOSCHEMA} refresh have more than 2 ORA errors,FAILED!"
        echo `date +%Y%m%d%H%M` " ***** ERROR: " ${MSG_TEXT} " *****" >> ${FIL_LOG}
echo "*******************************************************************" >>${FIL_LOG}
echo "#### 2.2.2 Import ${TOSCHEMA} from ${FROMSCHEMA} -END FAILURE***** " >>${FIL_LOG}
echo "*******************************************************************" >>${FIL_LOG}
        exit 99
fi

cat ${LOG_BASE}/${DATE}-IMPORT-${FROMSCHEMA}.log >>${FIL_LOG}

echo "">>${FIL_LOG}

echo "*****************************************************************************************" >>${FIL_LOG}
echo "####  2.2.2 Import ${TOSCHEMA} from ${FROMSCHEMA} -END SUCCESS***** " >>${FIL_LOG}
echo "####  2.2.2 Check log file ${LOG_BASE}/${DATE}-IMPORT-${FROMSCHEMA}.log for detail!***** " >>${FIL_LOG}
echo "*****************************************************************************************" >>${FIL_LOG}

## Run scamble scripts
echo "">>${FIL_LOG}

echo "*************************************************************" >>${FIL_LOG}
echo "#### 2.2.3 Apply scramble script. on ${TOSCHEMA}  -START***** " >>${FIL_LOG}
echo "*************************************************************" >>${FIL_LOG}

cd ${LOG_BASE}
sqlplus '/ as sysdba' @${SRAMBLE_BASE}/Moda_Scrambler_Script.sql ${TOSCHEMA}


if [ `grep "ORA" ${LOG_BASE}/Moda_Scrambler_Script.log | wc -l` -gt 1 ]; then
   MSG_TEXT="Execute sramble script. Moda_Scrambler_Script.sql exits ORA errors,FAILED!"
  echo `date +%Y%m%d%H%M` " ***** ERROR: "${MSG_TEXT},Please check if the sql exists or correct! " *****" >>${FIL_LOG}
echo "#### 2.2.3 Apply scramble script. on ${TOSCHEMA}  -END FAILURE***** " >>${FIL_LOG}
echo "*******************************************************************" >>${FIL_LOG}
  exit 99
fi

cat ${LOG_BASE}/Moda_Scrambler_Script.log >>${FIL_LOG}
echo "">>${FIL_LOG}

echo "*********************************************************************************" >>${FIL_LOG}
echo "#### 2.2.3 Apply scramble script. on ${TOSCHEMA}  -END SUCCESS***** " >>${FIL_LOG}
echo "#### 2.2.3 Check log file ${LOG_BASE}/Moda_Scrambler_Script.log for step detail! " >>${FIL_LOG}
echo "*********************************************************************************" >>${FIL_LOG}

##Drop directory
echo "">>${FIL_LOG}

echo "*************************************************************************" >>${FIL_LOG}
echo "#### 2.2.4 Drop DUMP DIRECTORY EXPDP_DIR AND EXPDP_LOG in DB -START***** " >>${FIL_LOG}
echo "*************************************************************************" >>${FIL_LOG}

sqlplus '/ as sysdba' @${SQL_BASE}/dp02_drop_directory.sql ${EXPDP_DMPDIR} ${DMP_BASE}
sqlplus '/ as sysdba' @${SQL_BASE}/dp02_drop_directory.sql ${EXPDP_LOGDIR} ${LOG_BASE}

if [ $? -ne 0 ];then

  echo `date +%Y%m%d%H%M` " ***** ERROR: "Please check if the sql exists or correct! " *****" >>${FIL_LOG}
echo "*******************************************************************************" >>${FIL_LOG}
echo "#### 2.2.4 Drop DUMP DIRECTORY EXPDP_DIR AND EXPDP_LOG in DB -END FAILURE***** " >>${FIL_LOG}
echo "*******************************************************************************" >>${FIL_LOG}
  exit 99
fi

echo "">>${FIL_LOG}

echo "*******************************************************************************" >>${FIL_LOG}
echo "#### 2.2.4 Drop DUMP DIRECTORY EXPDP_DIR AND EXPDP_LOG in DB -END SUCCESS***** " >>${FIL_LOG}
echo "*******************************************************************************" >>${FIL_LOG}

 

------------

cat Mon_DBAccount_M.ksh
#!/usr/bin/ksh
###############################################################################
# Filename     :  Mon_DBAccount_M.ksh
# Clearcase    :  /dba/Dmg/Oracle/Scripts/Control_M/src
# Owner        :  DMG Team, Ben London
# Purpose      :  Monitor Account Lock Status
# Build Date   :  09 March 2012
# Last Revision:  09 March 2012
# Usage        :  File_name (-s SID_NAME | -d RAC_DB_NAME) ( -l Patrol_Application_Group )
# VERSION      :  1.0
# LIB VERSION  :  1.0
# Notes        :  Mon_DBAccount_M.ksh should be placed in /u01/app/oracle/OTK/bin
#                 MON_DBACCOUNT_M__EXCLUSION.PAR
#------------------------------------------------------------------------------
# Revision History:
#
# Ver  Date          Name           Change
# 1.0  09 Mar 2012   Eddie Choi     Initial Build
###############################################################################

#==========================================================================
# Functions
#==========================================================================

#--------------------------------------------------------------------------
# Description : Trigger SMS to ISR+Application Support
#--------------------------------------------------------------------------
_patrolLogger_()
{
  Ben_INF=SSO
  MSGTEXT="$1"
  LOGGER="/bin/logger -p user.error -t root "
  $LOGGER "[${USELOGGERAPP}] -M- <${Ben_INF}> ${MSGTEXT}"
}
#--------------------------------------------------------------------------
# Description : Checks the DB Account Lock Status
#--------------------------------------------------------------------------
_checkAccount_()
{
  # Call in Parameters
  Get_Config_Param PARFILE_DIRECTORY
  Exclusion_File=${PARFILE_DIRECTORY}/MON_DBACCOUNT_M_${INPUT_SID}_EXCLUSION.PAR

  Log_Msg -BREAK "Checking Account Status"
  ExcludeList=""

  if [ -f "${Exclusion_File}" ]; then
      Log_Msg -TS "Using Exclusion File:"
      Log_Msg -TS "${Exclusion_File}"
      cat ${Exclusion_File} | while read USER;
      do
        ExcludeList=\'$USER\',${ExcludeList}
        Log_Msg -TS "$USER"
      done
      ExcludeList=,${ExcludeList}
      ExcludeList=${ExcludeList%?}

      Log_Msg -NTS ""
  fi
  Proc_Name="checkAccount"
  export Proc_Name

  TEMP_RUN_LOG=/tmp/${Proc_Name}_$$.tmp

sqlplus -s "/nolog" <

WHENEVER SQLERROR EXIT 1
WHENEVER OSERROR EXIT 1

conn / as sysdba
set termout on
set feedback off
set linesize 400
set newpage none
set pagesize 100
set underline =
spool $TEMP_RUN_LOG
column USERNAME format a30
column CMD format a16
column ACCOUNT_STATUS format a30
select 'RESULT' "CMD",username, account_status
from dba_users
where account_status like '%LOCK%'
and username not like '%TS'
and username not in
(
'OUTLN'
,'XS\$NULL'
,'DIP'
,'ORACLE_OCM'
,'CTXSYS'
,'MDSYS'
,'ORDDATA'
,'ORDPLUGINS'
,'ORDSYS'
,'SI_INFORMTN_SCHEMA'
,'APPQOSSYS'
,'ANONYMOUS'
,'EXFSYS'
,'WMSYS'
,'XDB'
,'SYSTEM'
,'SYS'
,'OTK'
,'TSMSYS'${ExcludeList}
)
order by username
;
spool off
exit

EOF
  Log_Msg -NTS ""
  grep "RESULT" $TEMP_RUN_LOG | while read _DUMMIE_ _USERNAME_ _STATUS_ ;
  do
    ERR_MSG="DB Server [${INPUT_SID}]: $_USERNAME_ is locked"
    if [ ! -z "$USELOGGERAPP" ]; then
       Log_Msg -BREAK "LOGGER: ${ERR_MSG}"
       _patrolLogger_ "${ERR_MSG}"
    else
       Alert_BMC 1 100
    fi
  done

  if [ `grep ORA- ${TEMP_RUN_LOG}|wc -l` -gt 0 ]; then
    Chk_Status -CRITICAL 115 1
  fi
  # Remove Temporary log
  Log_Msg -NTS ""
  Remove_File ${TEMP_RUN_LOG}

  Log_Msg -BREAK "Finished DB Account Check "
}

#========================================================================
# main body
#========================================================================
# Set initial required Environment Parameters

. ~oracle/.profile_jobs

# Source Util file so that all the procs are in the memory
. ${OTK_BASE}/lib/ORA_Util.ksh

USAGE="Usage: ${0} (-s SID_NAME | -d RAC_DB_NAME) [ -l ]"
while getopts s:d:l: option
do
    case ${option} in
        s)   INPUT_SID=${OPTARG};;
        d)   INPUT_SID=${OPTARG}
             RAC=1;;
        l)   USELOGGERAPP=${OPTARG};;
        [?]) echo ${USAGE}
             Chk_Status -CRITICAL 104 1 TERMINAL
    esac
done

# Check at least Sid has been Specified
if [ -z ${INPUT_SID} ]
then
  echo ${USAGE}
  exit
fi

# Setup Environment
Set_Environment ${INPUT_SID} ${0} ${RAC}

#####################################################################
# Check Account status
#####################################################################
_checkAccount_

# End Script
Finish_Up
-------------


cat oraenv
#!/usr/bin/ksh
###############################################################################
# Filename     :  oraenv
# Clearcase    :  /dba/Dmg/Oracle/Scripts/Control_M/src
# Owner        :  DMG Team, Ben London
# Purpose      :  Set Oracle Environment
# Build Date   :  01 July 2008
# Last Revision:  01 July 2008
# Usage        :  . oraenv (SID_NAME|DB_NAME)
#
# VERSION      :  0.4
# LIB VERSION  :  2.3
# Notes        :  oraenv should be placed in /u01/app/oracle/admin/scripts/src
#                 Directory for config files: Set in Config File (PARFILE_DIRECTORY).
#                 If no parameters are supplied, use ORACLE_SID or DB_NAME to set env
#------------------------------------------------------------------------------
# Revision History:
#
# Ver  Date          Name           Change
# 0.1  01 Jul 2008   Gavin Orr      Initial Pre-Release Version
# 0.2  22 Jul 2008   Gavin Orr      Add promting for ORACLE_SID as per Oracle version
# 0.3  21 Jul 2008   Gavin Orr      Various fixes
# 0.4  04 Aug 2008   Gavin Orr      Fixed alias for logs
###############################################################################


#==============================================================================
# Main Body
#==============================================================================

# Set initial parameters
DEBUG=0

# Check if SID/DB_NAME supplied as a parameter
if [ "${1:-}" != "" ] ; then
    INIT_PARAM=${1}
    shift
fi

OTK_BASE=${OTK_BASE:-/u01/app/oracle/OTK}
. ${OTK_BASE}/lib/ORA_Util.ksh

if [ ${DEBUG} -eq 1 ] ; then
    echo "INIT_PARAM=${INIT_PARAM}"
fi

# Determine how to suppress newline with echo command.
N=
C=
if echo "\c" | grep c >/dev/null 2>&1; then
    N='-n'
else
    C='\c'
fi

if [ "${INIT_PARAM:-}" = "" ] ; then
    case ${ORAENV_ASK:-""} in
        #ORAENV_ASK suppresses prompt when set
        NO) ;;
        *)  case "${ORACLE_SID:-}" in
                "") INIT_PARAM="DUMMY" ;;
                *) INIT_PARAM=${ORACLE_SID} ;;
            esac
            echo $N "ORACLE_SID = [$INIT_PARAM] ? $C"
            read NEWSID
            case "$NEWSID" in
                "") ;;
                *)  INIT_PARAM="$NEWSID" ;;
            esac ;;
    esac
fi

fn_oraenv
RTN=$?

unset INIT_PARAM
return $RTN
------------

cat DB_Stats_M.ksh
#!/usr/bin/ksh
###############################################################################
# Filename     :  DB_Stats_M.ksh
# Clearcase    :  /dba/Dmg/Oracle/Scripts/Control_M/src
# Owner        :  DMG Team, Ben London
# Purpose      :  Generate Statistics for each Schema, by running a DBMS_STATS
#                 statement within a PL/SQL Cursor.
# Build Date   :  13 June 2002
# Last Revision:  28 September 2006
# Usage        :  File_name (-s SID_NAME | -d RAC_DB_NAME) [-f PARFILE NAME | -m ]
#
# VERSION      :  3.2
# LIB VERSION  :  2.3
# Notes        :  DB_Stats_M.ksh should be placed in /u01/app/oracle/admin/scrips/src
#                 Following Environment Variables should be defined in ~oracle/.profile_jobs.
#                   ORA_CONFIG_FILE=/u01/app/oracle/admin/scrips/lib/ORA_Config.ksh
#                   ORA_ERR_CODES=/u01/app/oracle/admin/scrips/lib/ORA_Error_Messages.list
#                   ORA_UTIL_FILE=/u01/app/oracle/admin/scrips/lib/ORA_Util.ksh
#                 If using a parfile, it must contain only one schema per line with the
#                 following colon delimited fields
#                 SCHEMA_NAME  - Name of the schema to use.
#                 TRUE | FALSE - TRUE gathers empty and stale stats only.
#                 TRUE | FALSE - If using monitor, turn on table monitoring for tables
#                                currently in no-monitoring mode.
#                 value | NULL - Compute (NULL) or use a specified estimate.
#                 TRUE | FALSE - Generate histograms for indexed columns (254 buckets).
#                 Steps executed:
#                   1. Set Environment.
#                   2. Run DBMS_STATS.GATHER_SCHEMA_STATS for all relevant schemas
#                   3. House Keep Output Logs.
#------------------------------------------------------------------------------
# Revision History:
#
# Ver  Date          Name           Change
# 1.0  13 Jun 2002   Bill Halsey    Initial Build
# 2.0  13 Jun 2002   Lee Payne      Major re-build adding Control M mods including log file creation.
# 2.1  17 Jun 2002   Lee Payne      Modify to use common functions and additional
#                                   Parameters along with Timings.
# 2.2  25 Jun 2002   Lee Payne      Changed to run DBMS_STATS within the cursor
#                                   removing the need for a script. or file tidy.
# 2.3  22 Aug 2002   Lee Payne      Amend to comply with new Control M environment
# 2.4  08 Nov 2002   Lee Payne      Add in new functions and error checking along
#                                   with setting SORT_AREA_SIZE for the session.
# 2.5  28 Nov 2002   Lee Payne      Exclude additional schemas.
# 2.6  12 Jun 2003   Lee Payne      Add in additional functionality to deal with table monitoring
#                                   estimate and histograms.
# 3.0  01 Oct 2003   Lee Payne      Extensive release 2.1 changes including;
#                                   Use getopts and new Set_Environment structure.
#                                   Output PL/SQL via tee.
#                                   Allow parfile use to only analyze specific schemas.
#                                   Increase Output Buffer.
#                                   Ensure Error Handling Works Correctly.
#                                   Enable Table Monitoring to be Automatically Switched on.
#                                   Don't include Temporary tables in non-monitored.
#                                   General Tidy.
# 3.1 28 Jan 2004    Lee Payne      Add in additional functionality via the parfile and move
#                                   PL/SQL Block into dbatools package.
# 3.2 28 Sep 2006    Lee Payne      Overhaul for 10G, RAC and latest DMG Standards.
# 3.3 10 Nov 2006    Chris Denney   Remove Leading spaces from parfile name, due to way dbms_scheduler calls script
#     11 May 2008    Gavin Orr      Updated Library to V2.3
###############################################################################

#==========================================================================
# Functions
#==========================================================================
#--------------------------------------------------------------------------
# Description : Makes the actual call to the stats package.
# Usage       : Generate_Stats
#--------------------------------------------------------------------------
 Generate_Stats()
 {
  # Set up Return Code File
  trap "rm -f /tmp/rc_$$.tmp" 0 1 2 15

(
  sqlplus -s /nolog <

  variable ret_val number

  conn / as sysdba

  set head off
  set feedback off
  set termout off
  set lines 100 pages 50
  set serveroutput on

  ALTER SESSION SET SORT_AREA_SIZE=512000;

  BEGIN
    DBATOOLS.PR_STATS(${SQLSTATEMENT});
  END;
/
  exit :ret_val;
EOF

  echo ${?} >/tmp/rc_$$.tmp ) | tee -a ${OUTPUT_FILE}

  ERROR_COUNT=$(expr ${ERROR_COUNT} + $(cat /tmp/rc_$$.tmp))
  export ERROR_COUNT
}
#--------------------------------------------------------------------------
# Description : Generate Statistics for Relevant Schemas.
# Usage       : DB_Stats
#--------------------------------------------------------------------------
DB_Stats()
{
  Proc_Name=DB_Stats
  export Proc_Name

  Log_Msg -NTS ""
  Log_Msg -BREAK "Begin Running DBMS_STATS "

  ERROR_COUNT=0
  export ERROR_COUNT

  # First check if a parfile name was supplied or not
  if [ -z ${PARFILE_NAME} ]
  then
    # No Parfile.
    Log_Msg -TS "No parfile name supplied. Gather stats on all schemas within the DB."
    SQLSTATEMENT="powner=>NULL,pmonitored=>${MONITORED_ONLY},preturn=>:ret_val"
    Generate_Stats
  else
    Log_Msg -TS "Parfile name supplied. Gathering stats on specified schemas only."
    Log_Msg -TS "Parfile: ${PARFILE_NAME} "
    Log_Msg -TS "Parfile Contents are: \n "
    cat ${PARFILE_NAME} | tee -a ${OUTPUT_FILE}
    Log_Msg -NTS ""
    cat ${PARFILE_NAME} | while read line
    do
      SQLSTATEMENT="powner=>'$(echo ${line} | cut -d: -f1)',pmonitored=>$(echo ${line} | cut -d: -f2),
                    pSetMonOn=>$(echo ${line} | cut -d: -f3),pEstimate=>$(echo ${line} | cut -d: -f4),
                    pHistograms=>$(echo ${line} | cut -d: -f5),preturn=>:ret_val"
      Generate_Stats
    done
  fi

  Chk_Status -MINOR 270 ${ERROR_COUNT}

  Log_Msg -BREAK "Finished running DBMS_STATS "
}

#========================================================================
# main body
#========================================================================
# Set initial required Environment Parameters
. ~oracle/.profile_jobs

# Source Util file so that all the procs are in the memory
. ${OTK_BASE}/lib/ORA_Util.ksh

# Initialize variable
MONITORED_ONLY=FALSE

USAGE="Usage: ${0} (-s SID_NAME | -d RAC_DB_NAME) [-f parfile | -m ]"
while getopts s:d:mf: option
do
    case ${option} in
        s)   INPUT_SID=${OPTARG};;
        d)   INPUT_SID=${OPTARG}
             RAC=1;;
        m)   MONITORED_ONLY=TRUE;;
        f)   PARFILE_NAME=${OPTARG};;
        [?]) echo ${USAGE}
             Chk_Status -CRITICAL 104 1 TERMINAL
    esac
done

# Check if Sid has been Specified
if [ -z ${INPUT_SID} ]
then
  echo ${USAGE}
  Chk_Status -CRITICAL 104 1 TERMINAL
fi

# Check if Correct arguments have been supplied
if [ ${MONITORED_ONLY} = "TRUE" ] && [ ! -z ${PARFILE_NAME} ]
then
  echo ${USAGE}
  Chk_Status -CRITICAL 104 1 TERMINAL
fi

# Setup Environment
Set_Environment ${INPUT_SID} ${0} ${RAC}

# Call in Parameters
Get_Config_Param AUTO_MONITOR

# Check if a parfile is specified and if it exists.
if [ ! -z ${PARFILE_NAME} ]
then
  # Remove Leading Spaces from PARFILE_NAME
  PARFILE_NAME=`echo $PARFILE_NAME`

  Log_Msg -TS "Checking for Parfile..."
  Get_Config_Param PARFILE_DIRECTORY
  PARFILE_NAME="${PARFILE_DIRECTORY}/${PARFILE_NAME}"
  if [ ! -f ${PARFILE_NAME} ]
  then
  # Parfile missing raise error
    Chk_Status -CRITICAL 122 1
  fi
  Log_Msg -TS "OK..."
fi

# Run Schema Stats on all relevant schemas
DB_Stats

# End Script
Finish_Up
------------------
#==============================================================================
# Functions
#==============================================================================
#------------------------------------------------------------------------------
# Description : Rman_Bkup. This function carries out the Rman backup.
#               Connects to the rman catalog and then runs a rman command file.
# Usage       : Rman_Bkup
#------------------------------------------------------------------------------
Rman_Bkup ()
{
  Proc_Name=Rman_Bkup
  export Proc_Name

  Log_Msg -NTS ""
  Log_Msg -BREAK "Begin Running RMAN Backup "

  # Set up Return Code File
  trap "rm -f /tmp/ora_util_rc_$$.tmp" 0 1 2 15
  if [ -z ${NOCATALOG} ]
  then
        # Get Credentials to connect to RMAN Repository
    Get_Credentials ${CATUSER}

    Log_Msg -TS "RMAN Backup of ${DB_NAME} using RMAN Catalog"
    Log_Msg -TS "Catalog: ${OTK_CREDNATIVEURL}"
    Log_Msg -TS "User   : ${OTK_CREDUSERNAME}"
    Log_Msg -NTS ""
    (

    rman target / catalog ${OTK_CREDUSERNAME}/${OTK_CREDPASSWORD}@${OTK_CREDNATIVEURL} @${PARFILE_NAME} ;
    echo ${?} >/tmp/ora_util_rc_$$.tmp ) | tee -a ${OUTPUT_FILE}
    Chk_Status -CRITICAL 302 $(cat /tmp/ora_util_rc_$$.tmp)
    ## echo Catalog Password to prevent capture via the ps command.
    # echo ${CATPWD} | rman target / catalog ${CATUSER}@${RCVCAT_DB} @${PARFILE_NAME} ;
    # echo ${?} >/tmp/ora_util_rc_$$.tmp ) | tee -a ${OUTPUT_FILE}
    # Chk_Status -CRITICAL 302 $(cat /tmp/ora_util_rc_$$.tmp)
  else
    Log_Msg -TS "RMAN Backup of ${DB_NAME} No Catalog"
    Log_Msg -NTS ""
    (
    rman target / nocatalog  @${PARFILE_NAME} ;
    echo ${?} >/tmp/ora_util_rc_$$.tmp ) | tee -a ${OUTPUT_FILE}
    Chk_Status -CRITICAL 302 $(cat /tmp/ora_util_rc_$$.tmp)
  fi

  Log_Msg -BREAK "Finished Running RMAN Backup "
}

#------------------------------------------------------------------------------
# Description : Compress_Rman_Bkup. Function used to compress the Rman backups.
# Usage       : Compress_Rman_Bkup
#------------------------------------------------------------------------------
Compress_Rman_Bkup ()
{
  Proc_Name=Compress_Rman_Bkup
  export Proc_Name

  Log_Msg -NTS ""
  Log_Msg -BREAK "Begin Compressing RMAN Backups"
  Log_Msg -TS "Backup Area : ${RMAN_BASEDIR}/${DB_NAME}/rman"
  Log_Msg -TS "Compressing all uncompressed files ...."

  # Compress all currently uncompressed files.
  find ${RMAN_DIRECTORY} \( -name "${DB_NAME}*[!.gz]" -a -name "${DB_NAME}*[!.Z]" \) | while read line
  do
    Compress_File ${line}
  done

  Log_Msg -BREAK "Finished Compressing RMAN Backups"
}

#------------------------------------------------------------------------------
# Description : Delete old Rman Backups. RMAN_KEEP_BKUPS define how old backups to delete.
# Usage       : HseKeep_Rman_Bkups
#------------------------------------------------------------------------------
HseKeep_Rman_Bkups ()
{
  Proc_Name=HseKeep_Rman_Bkups
  export Proc_Name

  Log_Msg -NTS ""
  Log_Msg -BREAK "Begin Deleting Old RMAN Backups "
  Log_Msg -TS "Backup Area : ${RMAN_BASEDIR}/${DB_NAME}/rman"
  Log_Msg -TS "Deleting Backups older than ${RMAN_KEEP_BKUPS} days ...."

  # Remove all the rman backups older than $RMAN_KEEP_BKUPS days.
  find ${RMAN_DIRECTORY} -name "${DB_NAME}*" -mtime +${RMAN_KEEP_BKUPS} | while read line
  do
    Remove_File ${line}
  done

  Log_Msg -BREAK "Finished Deleting Old RMAN Backups "
}

#==============================================================================
# Main Body
#==============================================================================
# Set initial required Environment Parameters
. ~oracle/.profile_jobs

# Source Util file so that all the procs are in the memory
. ${OTK_BASE}/lib/ORA_Util.ksh

USAGE="Usage: ${0} (-s SID_NAME | -d RAC_DB_NAME) -f PARFILE NAME [-n] [-c] [-h]"
while getopts s:f:d:cnh option
do
    case ${option} in
        s)   INPUT_SID=${OPTARG};;
        d)   INPUT_SID=${OPTARG}
                 RAC=1;;
        f)   PARFILE_NAME=${OPTARG};;
        c)   COMPRESSED=TRUE;;
        n)   NOCATALOG=TRUE;;
        h)   HOUSEKEEPING=TRUE;;
        [?]) echo ${USAGE}
             Chk_Status -CRITICAL 104 1 TERMINAL
    esac
done

# Check at least Sid has been Specified
if [ -z ${INPUT_SID} ] || [ -z ${PARFILE_NAME} ]
then
  echo ${USAGE}
  Chk_Status -CRITICAL 104 1 TERMINAL
fi

# Remove Leading Spaces from PARFILE_NAME
PARFILE_NAME=`echo $PARFILE_NAME`

# Setup Environment
Set_Environment ${INPUT_SID} ${0} ${RAC}

# Check that the specified parfile exists.
Log_Msg -TS "Checking for Parfile..."
Get_Config_Param PARFILE_DIRECTORY
PARFILE_NAME="${PARFILE_DIRECTORY}/${PARFILE_NAME}"
Log_Msg -TS "Parfile: ${PARFILE_NAME}"
if [ ! -f ${PARFILE_NAME} ]
then
# Parfile missing raise error
  Chk_Status -CRITICAL 122 1
fi
Log_Msg -TS "OK..."

# Check that the specified RMAN directory exists.
Log_Msg -TS "Checking for RMAN Backup Directory..."

Get_Config_Param SUPERSTACK
Get_Config_Param RMAN_BASEDIR
if [ ${SUPERSTACK} -eq 1 ] ; then
    RMAN_DIRECTORY="${RMAN_BASEDIR}/${DB_NAME}/backup/rman"
else
    RMAN_DIRECTORY="${RMAN_BASEDIR}/${DB_NAME}/rman"
fi

Log_Msg -TS "Directory: ${RMAN_DIRECTORY}"
if [ ! -d ${RMAN_DIRECTORY} ]
then
# Directory missing raise error
  Chk_Status -CRITICAL 301 1
fi

Log_Msg -TS "OK..."

# If using a catalog Check that the password file exist. If not, exit the program.
if [ -z ${NOCATALOG} ]
then
  if [ ! -s "${ORACLE_BASE}/admin/.pw/.RMANpwf" ]
  then
      Chk_Status -CRITICAL 113 1
  fi
  # Read the contents of the password file and get Catalog DB and user.
  CATPWD=`cat "${ORACLE_BASE}/admin/.pw/.RMANpwf"`
  Get_Config_Param RCVCAT_DB
  # Select correct RMAN user, based on DB Version.
  case `basename ${ORACLE_HOME}` in
    9.*)  Get_Config_Param RMAN9_CATUSER
          CATUSER=${RMAN9_CATUSER}
          ;;
    10.*) Get_Config_Param RMAN10_CATUSER
          CATUSER=${RMAN10_CATUSER}
          ;;
    11.*) Get_Config_Param RMAN11_CATUSER
          CATUSER=${RMAN11_CATUSER}
          ;;
    *)    # Default to RMAN10
          Get_Config_Param RMAN10_CATUSER
          CATUSER=${RMAN10_CATUSER}
  esac
fi

# Call in additional Parameters
Get_Config_Param LOG_DIR
Get_Config_Param RMAN_KEEP_BKUPS


# Run The RMAN Backup
Rman_Bkup

# If -h option is selected then don't Housekeep old RMAN Backups
if [ ${HOUSEKEEPING} ]
then
  HseKeep_Rman_Bkups
fi

# If -c option is selected then compress backups.
if [ ${COMPRESSED} ]
then
  Compress_Rman_Bkup
fi

# End Script
----------------
cat RMAN102_LVL0_DISK.PAR
run {
host 'date' ;
#
# Allocate channels
#
allocate channel d1 type DISK ;
allocate channel d2 type DISK ;
allocate channel d3 type DISK ;
allocate channel d4 type DISK ;
#
# Set Policies. Policy needs to be consistent for complete backup
#               - not just individual components
#
configure retention policy to recovery window of 35 days ;
#
# Turn off autobackup as we manually backup controlfile & spfile
# to avoid timeouts in netbackup.
#
configure controlfile autobackup off ;
#
# Display Configuration parameters
#
show all ;
#
# Backup archivelogs first.
#   A separate job should be scheduled prior to this to ensure retention
#   is appropriate.
#
# This is also to help ensure we don't fill filesystems on large backups.
#
backup
    archivelog all
    format '/u99/oradata/%d/backup/rman/%d_ARCH_%U'
    tag ARCHIVE_LOGS
    delete input;
host 'date' ;
#
# ---------------
# Backup database
# ---------------
#
backup
  as compressed backupset incremental level 0
  cumulative
    database
      tag INCREMENTAL_0
      format '/u99/oradata/%d/backup/rman/%d_LVL0_%U';
host 'date' ;
#
# Force a log switch and backup archivelogs to ensure backupset is complete
# Backup any archivelogs before switching to ensure we are not in a full
# archivelog disk situation after the backup
#
backup
    archivelog all
    format '/u99/oradata/%d/backup/rman/%d_ARCH_%U'
    tag INCREMENTAL_0
    delete input;
host 'date' ;
#
#
# Backup Controlfile
#
backup
    current controlfile
    format '/u99/oradata/%d/backup/rman/%d_CNTRL_%U'
    tag INCREMENTAL_0 ;
host 'date' ;
#
# Backup spfile
#
backup
    spfile
    format '/u99/oradata/%d/backup/rman/%d_SPFILE_%U'
    tag INCREMENTAL_0 ;
host 'date' ;
}

----------
CREATE OR REPLACE PACKAGE ORCLADMIN.orautil
AS
   PROCEDURE pr_issue_grants_syns (p_schema_in IN dba_users.username%TYPE default NULL);

   PROCEDURE start_session_trace;

   PROCEDURE set_session_parameter;
END orautil;
/
CREATE OR REPLACE PACKAGE BODY ORCLADMIN.orautil
AS
   PROCEDURE pr_issue_grants_syns (p_schema_in IN dba_users.username%TYPE default NULL)
   AS
      v_owner   VARCHAR2 (30) := nvl(p_schema_in,user);
   BEGIN
      FOR r IN (SELECT *
                  FROM dba_objects a
                 WHERE wner = v_owner
                   AND NOT EXISTS (
                          SELECT 1
                            FROM dba_recyclebin b
                           WHERE a.owner = b.owner
                             AND a.object_name = b.object_name))
      LOOP
         BEGIN
            IF r.object_type IN ('TABLE', 'VIEW', 'SEQUENCE')
            THEN
               EXECUTE IMMEDIATE (   'GRANT SELECT ON '
                                  || v_owner
                                  || '.'
                                  || r.object_name
                                  || ' TO '
                                  || 'RO_'
                                  || v_owner
                                 );

               EXECUTE IMMEDIATE (   'GRANT DELETE, INSERT, SELECT, UPDATE ON '
                                  || v_owner
                                  || '.'
                                  || r.object_name
                                  || ' TO '
                                  || 'APP_'
                                  || v_owner
                                 );
            ELSIF r.object_type IN
                                 ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'TYPE')
            THEN
               EXECUTE IMMEDIATE (   'GRANT EXECUTE ON '
                                  || v_owner
                                  || '.'
                                  || r.object_name
                                  || ' TO '
                                  || 'APP_'
                                  || v_owner
                                 );
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (   SQLERRM
                                     || ':'
                                     || v_owner
                                     || '.'
                                     || r.object_name
                                    );
         END;
      END LOOP;
   END;

   PROCEDURE start_session_trace
   AS
      v_sid            v$session.SID%TYPE;
      v_serial#        v$session.serial#%TYPE;
      v_enable_trace   user_logon_params.enable_trace%TYPE   := 'N';
   BEGIN
      SELECT SID,
             serial#
        INTO v_sid,
             v_serial#
        FROM v$session
       WHERE SID = SYS_CONTEXT ('USERENV', 'SID')
         AND audsid = SYS_CONTEXT ('USERENV', 'SESSIONID');

      BEGIN
         SELECT enable_trace
           INTO v_enable_trace
           FROM user_logon_params
          WHERE username = USER;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            NULL;
      END;

      IF v_enable_trace = 'Y'
      THEN
         EXECUTE IMMEDIATE (   'alter session set tracefile_identifier='''
                            || TO_CHAR (SYSDATE, 'yyyymmddhh24mi')
                            || '.'
                            || USER
                            || ''''
                           );

         DBMS_OUTPUT.put_line (v_sid || ',' || v_serial#);
         SYS.DBMS_SYSTEM.set_bool_param_in_session (v_sid,
                                                    v_serial#,
                                                    'timed_statistics',
                                                    TRUE
                                                   );
         SYS.DBMS_SYSTEM.set_ev (v_sid, v_serial#, 10046, 12, '');
      END IF;
   END;

   PROCEDURE set_session_parameter
   AS
   BEGIN
      FOR r IN (SELECT *
                  FROM user_logon_params
                 WHERE username = USER)
      LOOP
         DBMS_OUTPUT.put_line ('ALTER SESSION SET CURRENT_SCHEMA ='
                               || r.SCHEMA
                              );

         EXECUTE IMMEDIATE ('ALTER SESSION SET CURRENT_SCHEMA =' || r.SCHEMA
                           );
      END LOOP;

      FOR r IN (SELECT *
                  FROM user_session_params
                 WHERE username = USER)
      LOOP
         DBMS_OUTPUT.put_line (   'ALTER SESSION SET '
                               || r.parameter
                               || '='
                               || r.VALUE
                              );

         EXECUTE IMMEDIATE ('ALTER SESSION SET ' || r.parameter || '='
                            || r.VALUE
                           );
      END LOOP;
   END;
END orautil;
/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27064837/viewspace-732732/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: Imp Buffer
请登录后发表评论 登录
全部评论

注册时间:2012-06-13

  • 博文量
    74
  • 访问量
    93712