#/bin/ksh
############################################################################
# hotback.scr
#
#  Description:	Perform hot backups on the Oracle databases.
#
#	 Usage:	hotback.scr -i node:instance -c cycle -t #tapes -r rdonlies
#
#	 Input:	-i	node:instance (/usr/ucb/hostname:database instance)
#		-c	backup cycle (daily, weekly, monthly, on demand, ...)
#		-t	number of tape drives to use in this process
#		-s	sleep time in seconds for monitor (default 60)
#		-k	retention period for tapes (String)
#		-r	ALL for all read only tablespaces, or tablespace_name
#			for specific read only tablespaces.
#
#	Output: Backup scripts, backed up database.
#		Log of the run.
#
#	Return:	Exit 1 on failure.
#
############################################################################
# Usage clause
# Prints out the syntax of the command to standard output.
#
# 	 Input:	None
#
# 	Output:	Command syntax.
#
# 	Return:	None
############################################################################
Usage()
{
echo ""
echo "USAGE: hotback.scr"

echo " -i node:sid         = node:instance_id"
echo " -c cycle	           = backup cycle (daily, weekly, monthly, ...)"
echo " -t # tape drives    = number of tape drives/concurrent streams"
echo " -s sleep time       = number of seconds to sleep between monitor cks"
echo " -k retention period = number of days to keep the backup tapes"
echo " -r read only tbspc  = ALL for all read only tablespaces; tablespace name"
}

############################################################################
# ParseArgs:
# Parse the parameters on the input line.
#
#   
# Input:	-i	node:instance (/usr/ucb/hostname:database instance)
#		-c	backup cycle (daily, weekly, monthly, on demand, ...)
#		-t	number of tape drives to use in this process
#		-s	sleep time in seconds for monitor (default 60)
#		-k	retention period for tapes (String)
#		-r	ALL for all read only tablespaces, or tablespace_name
#			for specific read only tablespaces.
#
# Output: 	Node, SID, backup cycle, sleep time, retention period and read 
#		only tablespace list (ALL if specified).
#
# Return:	Exit 1 on failure. 
############################################################################
ParseArgs()
{
LogMsg "ParseArgs: Parse the arguments on the command line."

# Make sure that at least 4 of the required arguments (node:sid, cycle, #tapes
# and read only tablespace list) were entered.

if [ $# -lt 4 ]
then
  echo "hotback.scr: ERROR: You have entered insufficient arguments"
  Usage
  exit 1
fi

all_args=""
RD_ONLY=""

# Make sure that the arguments parameters passed were the correct ones.

set -- `getopt i:c:t:r:s:k: $*`
if [ $? != 0 ]
then
  echo "hotback.scr: ERROR: You have entered incorrect arguments"
  Usage
  exit 1
fi

# Assign each argument to seperate variables.

while [ $1 != -- ]
do
  case $1 in
        -i)     all_args="${all_args}$2 ";      shift 1;;
        -c)     CYCLE="$2";			shift 1;;
        -t)     NO_TAPES="$2";			shift 1;;
        -s)     SLEEP_TIME="$2";		shift 1;;
        -k)     RETAIN="$2";			shift 1;;
        -r)     RD_ONLY="${RD_ONLY}$2 ";	shift 1;;
  esac
  shift 1
done

if [ "$CYCLE" = "" or "$NO_TAPES" = "" or "$RETAIN" = "" ]
then
   echo "hotback.scr: ERROR: cycle, # of tapes, and retention are required."
   Usage
   exit 1
fi

# Parse the -i arguments and seperate the node and sid name(s).

let node_num=1
let max_nodes=0
local_node=`/usr/ucb/hostname`

for arg in ${all_args}
do
   node=`echo ${arg}|cut -f1 -d":"`
   sid=`echo ${arg}|cut -f2 -d":"`
   node[$node_num]=${node}
   sid[$node_num]=${sid}

   if [[ ${local_node} = ${node} ]]
   then
       export ORACLE_SID=${sid[$node_num]}
   fi

   let node_num=node_num+1
   let max_nodes=max_nodes+1

done

if [ "$SLEEP_TIME" = "" ]; then
   SLEEP_TIME=60
fi

# Make sure that the local node was given and that it got an oracle sid.

if [[ ${ORACLE_SID} = "" ]]
then
  echo "hotback.scr ERROR: You must supply \"-i <node>:<sid>\" for all nodes"
  Usage
  exit 1
fi

LogMsg "ParseArgs: Command line arguments parsed."
}

##############################################################################
# DirStructure:
# Create the directory structure for this backup run.
#
#	                          IIIIIIIIIIIIIIIIIIIIIIIIIInput:																										FFFFFFFFFFFFFFFFFFFFFFFFFFrequency of this backup (Cycccccccccccccccccccccccccclllllllllllllllllller Name',
                            3,'Pgm OS Pid',
                            4,'Oracle OS Pid',
                            5,'Oracle Session ID',
                            6,'Serial#',
                            7,'Program Name',
                            8,'Server Type',
                            'Server Type') sort_by_text2
from dual; 

set pause on

REM ----------------------------------------------------------------------------
 
col bb heading "OS|User|Name"          format a10
col cc heading "Oracle|User|Name"      format a12
col dd heading "Pgm|OS|Pid"            format a7
col ee heading "Oracle|OS|Pid"         format a7
col ef heading "Oracle|Session|ID"     format 999999
col eg heading "Serial#"               format 999999
col gg heading "Program Name"          format a16
col ii heading "Server|Type"           format a9
col hh heading "Logon|Time"            format a20
col jj heading "Client|Machine"        format a10
ttitle "Report For Oracle All User Sessions|Sort By &sort_by_text_value1, &sort_by_text_value2"
select vs.osuser bb,
       vs.username cc,
       vs.process dd,
       vp.spid ee,
       vs.sid ef,
       vs.serial# eg,
       substr(vs.program,1,16) gg,
       vs.server ii,
       to_char(vs.logon_time,'DD-MON-YYYY HH24:MI:SS') hh,
       vs.machine jj
  from v$process vp, v$session vs
 where vs.paddr = vp.addr
   and not exists ( select 'x'
                     from v$bgprocess vbg
                    where vbg.paddr != '00'
                      and vbg.paddr  = vp.addr)
 order by &sort_by_number_value1, &sort_by_number_value2;

rem exit;
REM ================================ END OF FILE ===============================



ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ/* ************************************************ */
/*                                                  */
/* fnctn:   Build the qep explain plan table        */
/*                                                  */
/* name:    @c:\oracle\sql\qep_1.sql                */
/* date:    16-MAY-95                               */
/* creator: Rick Kupcunas                           */
/*                                                  */
/* ************************************************ */

create table PLAN_TABLE_1
    (statement_id          varchar2(30),
     timestamp             date,
     remarks               varchar2(80),
     operation             varchar2(30),
     options               varchar2(30),
     object_node           varchar2(30),
     object_owner          varchar2(30),
     object_name           varchar2(30),
     object_instance       numeric, 
     object_type           varchar2(30),
     search_columns        numeric,
     id                    numeric,
     parent_id             numeric,
     position              numeric,
     other                 long);

create public synonym plan_table_1 for plan_table_1;
 
commit;




ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿset echo on;
set feedback on;
/* ************************************************ */
/*                                                  */
/* fnctn:   Populate the qep table for the sql      */
/*                                                  */
/* name:    @c:\my_own\oracle\sql\qep_2.sql         */
/* date:    16-MAY-95                               */
/* creator: Rick Kupcunas                           */
/*                                                  */
/* ************************************************ */



delete from PLAN_TABLE_1
where STATEMENT_ID = 'RWK' ;

/* ********************************* */
/* as submitted by business objects: */
/* ********************************* */

explain plan set statement_id = 'RWK'
into PLAN_TABLE_1 for
select count(*) from PS_TC_IMAGE_VW where TC_REMIT_TO_VNDR like 'ABC%';

commit;

set timing on;
select count(*) from PS_TC_IMAGE_VW 
where business_unit like 'SHACK%';

select count(*) from PS_TC_IMAGE_VW 
where business_unit like 'ABC%'
and rownum < 505;

set timing off;
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿßßßÿÿÿÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀÀ€€€   ßßßÿÿÿÀÀÀÀÀÀÀÀÀ€€€   ÿÿÿÿÿÿÿÿÿÿÿÿ€€€ÿÿÿÿÿ ÿÿ ÿÿ ÿÿ/* ************************************************ */
/*                                                  */
/* fnctn:   Populate the qep table for the sql      */
/*                                                  */
/* name:    @c:\my_own\oracle\sql\qep_2.sql         */
/* date:    16-MAY-95                               */
/* creator: Rick Kupcunas                           */
/*                                                  */
/* ************************************************ */

delete from PLAN_TABLE
where STATEMENT_ID = 'RWK' ;

/* ********************************* */
/* as submitted by business objects: */
/* ********************************* */

explain plan set statement_id = 'RWK'
into PLAN_TABLE for;

set timing on;
select /*+ CHOOSE */ count(*) 
from ps_tc_image_vw2
where voucher_id like 'ABC%';

set timing off





ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ/* ************************************************ */
/*                                                  */
/* fnctn:   Format the qep for the sql statement    */
/*                                                  */
/* name:    @c:\oracle\sql\qep_3.sql                */
/* date:    16-MAY-95                               */
/* creator: Rick Kupcunas                           */
/*                                                  */
/* ************************************************ */

select LPAD(' ',1*(LEVEL-1))||operation||' '||options||' '||object_name
||' '||DECODE(id, 0 , 'Cost = '||position) "Query Plan"
        from RWK_PLAN_TABLE
        start with id =0 and 
                   statement_id = 'RWK'
        connect by prior id = parent_id and 
                   statement_id = 'RWK';












ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ Associates\McAfee /* ************************************************ */
/*                                                  */
/* fnctn:   Format the qep for the sql statement    */
/*                                                  */
/* name:    @c:\oracle\sql\qep_3a.sql               */
/* date:    16-MAY-95                               */
/* creator: Rick Kupcunas                           */
/*                                                  */
/* ************************************************ */

spool qep_3a.rslt;

select decode(id,0, '',
    LPAD(' ',2*(LEVEL-1))||level||'.'||position)||' '||
    operation||' '||options||' '||object_name||' '||
    object_type||' '||
    DECODE(id, 0 ,'Cost = '||position) "Query Plan"
        from PLAN_TABLE_1
    connect by prior id = parent_id and 
        statement_id = 'RWK'
    start with id = 0 and statement_id = 'RWK';
        

spool off;










  ÿÿÿ   ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ   ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿSELECT B.LEDGER,B.ACCOUNT,B.DEPTID,B.PRODUCT,B.PROJECT_ID,
    B.AFFILIATE, B.CURRENCY_CD,B.CURRENCY_CD,B.FOREIGN_CURRENCY,
    SUM(B.FOREIGN_AMOUNT), B.JRNL_LN_REF,SUM(B.MONETARY_AMOUNT) 
FROM PS_OPENITM_GL_TMP A,
    PS_JRNL_LN B,
    PS_JRNL_HEADER C 
WHERE C.BUSINESS_UNIT=B.BUSINESS_UNIT 
AND C.JOURNAL_ID=B.JOURNAL_ID 
AND C.JOURNAL_DATE=B.JOURNAL_DATE 
AND C.UNPOST_SEQ=B.UNPOST_SEQ 
AND B.LEDGER=A.LEDGER 
AND B.ACCOUNT=A.ACCOUNT 
AND B.DEPTID=A.DEPTID 
AND B.PRODUCT=A.PRODUCT 
AND B.PROJECT_ID=A.PROJECT_ID 
AND B.AFFILIATE=A.AFFILIATE 
AND B.CURRENCY_CD=A.CURRENCY_CD 
AND B.JRNL_LN_REF=A.JRNL_LN_REF 
AND C.BUSINESS_UNIT='SHACK' 
AND C.JRNL_HDR_STATUS IN ('P','U') 
AND B.BUSINESS_UNIT='SHACK' 
AND B.OPEN_ITEM_STATUS='O' 
AND A.PROCESS_INSTANCE=0000008597 
GROUP BY B.LEDGER,B.ACCOUNT,B.DEPTID,B.PRODUCT,B.PROJECT_ID,
B.AFFILIATE,B.CURRENCY_CD,B.CURRENCY_CD,B.FOREIGN_CURRENCY,
B.JRNL_LN_REF;
€ €€ €€ ÿÿ       ÿÿÿÀÀÀ   ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ   ÿÿÿ/* ************************************************ */
/*                                                  */
/* fnctn:   Produce the report of those users found */
/*          accessing the database via a query tool */
/*          and not being limited by a database usr.*/
/*                                                  */
/* name:    @c:\oracle\sql\qry_scrp.sql             */
/* date:    12-MAY-95                               */
/* creator: Rick Kupcunas (Oracle)                  */
/*                                                  */
/* ************************************************ */

whenever sqlerror exit 

set pagesize 2000
set verify off
set termout off

column today noprint new_value cur_date

spool  qry_scrp.rslt

ttitle left 'The following non-MIS users were found to be logged on to a' -
      skip 1 left 'query tool (Data Browser, QMX, SQL*Plus) within the ' -
      skip 1 left 'previous 24 hour period and do not have a profile limiting' -
      skip 1 left 'their access.  At the time of this report, the following' -
      skip 1 left 'users were added to a limiting profile.' -
      skip 1 -
      skip 1 left 'Report Date: ' cur_date

ttitle off

select to_char(sysdate,'MM-DD-YYYY HH24:MI') today from dual;

column username format A8 heading 'User Id'
column osuser format A9 heading 'OS User'
column userfullname format A23 heading 'User Full Name'
column tool format A9 heading 'Tool'
column runtimes format A11 heading 'Start Time|End Time'
column logicalreads format 9,999,999,999 heading 'Logical Reads'

ttitle on

select a.username username,
       a.osuser osuser,
       upper(substr(b.fname,1,1))||lower(substr(b.fname,2,7))||' '||
           upper(substr(b.lname,1,1))||lower(substr(b.lname,2,15)) userfullname,
       'Browser' tool,
       min(to_char(a.query_date,'MM/DD HH24:MI'))||
       max(to_char(a.query_date,'MM/DD HH24:MI')) runtimes,
       max(a.block_gets + a.consistent_gets) logicalreads
  from sys.session_history a, a_fin_es.esd_employees b, dba_users c
 where substr(a.username,2,6) = substr(to_char(b.emp_num(+),'099999'),2,6)
   and a.username = c.username
   and b.mail_stop_id not like 'ISD%'
   and c.profile = 'DEFAULT'
   and exists
        (select 'x'
           from sys.session_history d
          where d.query_date > sysdate - 1 
            and upper(d.program) like '%BROW%'
            and d.username like 'U%'
            and d.username = a.username
            and d.osuser = a.osuser
            and d.process = a.process
            and d.sid = a.sid)
 group by a.username, a.osuser, b.lname, b.fname
union
select a.username username,
       a.osuser osuser,
       upper(substr(b.fname,1,1))||lower(substr(b.fname,2,7))||' '||
           upper(substr(b.lname,1,1))||lower(substr(b.lname,2,15)) userfullname,
       'QMX' tool,
       min(to_char(a.query_date,'MM/DD HH24:MI'))||
       max(to_char(a.query_date,'MM/DD HH24:MI')) runtimes,
       max(a.block_gets + a.consistent_gets) logicalreads
  from sys.session_history a, a_fin_es.esd_employees b, dba_users c
 where substr(a.username,2,6) = substr(to_char(b.emp_num(+),'099999'),2,6)
   and a.username = c.username
   and b.mail_stop_id not like 'ISD%'
   and c.profile = 'DEFAULT'
   and exists
        (select 'x'
           from sys.session_history d
          where d.query_date > sysdate - 1 
            and upper(d.program) like '%QMX%'
            and d.username like 'U%'
            and d.username = a.username
            and d.osuser = a.osuser
            and d.process = a.process
            and d.sid = a.sid)
 group by a.username, a.osuser, b.lname, b.fname
union
select a.username username,
       a.osuser osuser,
       upper(substr(b.fname,1,1))||lower(substr(b.fname,2,7))||' '||
           upper(substr(b.lname,1,1))||lower(substr(b.lname,2,15)) userfullname,
       'SQL*Plus' tool,
       min(to_char(a.query_date,'MM/DD HH24:MI'))||
       max(to_char(a.query_date,'MM/DD HH24:MI')) runtimes,
       max(a.block_gets + a.consistent_get