Show any logins whose password is NOT SET to expire in 30 days

SELECT user_id, user_name, password_lifespan_days
FROM apps.fnd_user
WHERE
nvl(password_lifespan_days, 0) != 30

Script to see Terminated employees who still have an active login to Oracle Apps

COL emp_num format a7
COL per_serv_id format 999999
-- Check for terminated employees
PROMPT Terminated employees who still have an active login to Oracle Apps
SELECT DISTINCT(fu.user_name)
, papf.employee_number emp_num
, to_char(ppos.actual_termination_date, 'DD-Mon-YY') term_date
, period_of_service_id per_serv_id
FROM apps.fnd_user fu
, apps.per_all_people_f papf
, apps.per_periods_of_service ppos
WHERE fu.employee_id = papf.person_id
AND papf.person_id = ppos.person_id
AND fu.end_date IS NULL
AND ppos.period_of_service_id = (SELECT MAX(period_of_service_id)
FROM per_periods_of_service
WHERE person_id = papf.person_id)
AND ppos.actual_termination_date < TRUNC(SYSDATE)
/

Stop Script Oracle iAS 10g application server Environment is AIX 5.3 and Oracle iAS 10g

# Author Rafakat Hussain
# Date 15/08/2007
# shutdown script for Oracle Application Server 10g
# -------------------------------------------------
# =============================
# Shutdown 10g Application Tier
# =============================

export ORACLE_SID=infra
export ORACLE_HOME=/ora111/dev5/10giAS/portal

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin

$ORACLE_HOME/opmn/bin/opmnctl stopall

$ORACLE_HOME/bin/emctl stop iasconsole

# ===========================
# Shutdown 10g Infrastructure
# ===========================

export ORACLE_SID=infra
export ORACLE_HOME=/ora111/dev5/10giAS/infra

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin

$ORACLE_HOME/opmn/bin/opmnctl stopall

$ORACLE_HOME/bin/emctl stop dbconsole

$ORACLE_HOME/bin/emctl stop iasconsole

SQLDBA="$ORACLE_HOME/bin/sqlplus /nolog"

$SQLDBA <connect / as sysdba
shutdown immediate
EOF

$ORACLE_HOME/bin/lsnrctl stop

Start Script Oracle iAS 10g application server Environment is AIX 5.3 and Oracle iAS 10g

# Author Rafakat Hussain
# Date 15/08/2007
# start script for Oracle Application Server 10g
# ----------------------------------------------
# =============================
# Start Infrastructure Metadata
# =============================

export ORACLE_SID=infra
export ORACLE_HOME=/ora111/dev5/10giAS/infra

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin

$ORACLE_HOME/bin/lsnrctl start

SQLDBA="$ORACLE_HOME/bin/sqlplus /nolog"

$SQLDBA <connect / as sysdba
startup
EOF

# Starting Oracle Enterprise Manager 10g Database Control
$ORACLE_HOME/bin/emctl start dbconsole

$ORACLE_HOME/opmn/bin/opmnctl startall

$ORACLE_HOME/bin/emctl start iasconsole

# =============================
# Start 10g Application Tier
# =============================

unset CLASSPATH

export ORACLE_SID=infra
export ORACLE_HOME=/ora111/dev5/10giAS/portal

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin

$ORACLE_HOME/opmn/bin/opmnctl startall
## after you modify opmn.xml file
# $ORACLE_HOME/opmn/bin/opmnctl reload

$ORACLE_HOME/bin/emctl start iasconsole

How to extract a cpio file

for Example the file is as_ibm_aix_101202_disk1.cpio

to extract to the same directory use command as follows

cpio -icvdu < as_ibm_aix_101202_disk1.cpio

Oracle KEEP Pool

The placement criteria for tables and indexes into the KEEP buffer are straightforward,High buffer residency Any table that has more than 80% of its blocks in the data buffer should be cached in the KEEP pool.The approach to identifying tables for the KEEP pool is simple. All objects that have more than 80% of their data blocks in the buffer should be assigned to the KEEP pool.

- Use the KEEP pool if the object consumes more than 10% of the total size of the data buffer.
- Use the KEEP pool if more than 50% of the objects blocks already resides in the data buffer.

The following script can be run every hour via dbms_job and will automate the monitoring of KEEP pool candidates. Every time it finds a candidate, the DBA will execute the syntax and adjust the total KEEP pool size to accommodate the new object.

Spool buffer_keep_pool

drop table t1
create table t1 as
select
o.owner owner,
o.object_name object_name,
o.subobject_name subobject_name,
o.object_type object_type,
count(distinct file# || block#) num_blocks
from
dba_objects o,
v$bh bh
where
o.data_object_id = bh.objd
and
o.owner not in ('SYS','SYSTEM')
and
bh.status != 'free'
group by
o.owner,
o.object_name,
o.subobject_name,
o.object_type
order by
count(distinct file# || block#) desc
;
select
'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||' storage (buffer_pool keep);'
from
t1,
dba_segments s
where
s.segment_name = t1.object_name
and
s.owner = t1.owner
and
s.segment_type = t1.object_type
and
nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
and
buffer_pool <> 'KEEP'
and
object_type in ('TABLE','INDEX')
group by
s.segment_type,
t1.owner,
s.segment_name
having
(sum(num_blocks)/greatest(sum(blocks), .001))*100 > 80
;
Spool off

How To Find Oracle Application File Versions

Commands you can use to get header information of Oracle Application Files.

adident Header report.rdf

strings -a report.rdf | grep Header