SELECT user_id, user_name, password_lifespan_days
FROM apps.fnd_user
WHERE
nvl(password_lifespan_days, 0) != 30
Friday, 24 August 2007
Thursday, 23 August 2007
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)
/
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)
/
Wednesday, 15 August 2007
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
# 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 <
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
# 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 <
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
Monday, 6 August 2007
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
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
- 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
Wednesday, 1 August 2007
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
adident Header report.rdf
strings -a report.rdf | grep Header
Subscribe to:
Posts (Atom)
Profile - Oracle 11i AppsDBA
Over 7 years of experience as an Oracle Applications DBA managing Oracle Public Sector HRMS 11.5.10 and Oracle Financials 11i in Unix and Windows NT environments. Strong skills and experience with installing, patching, cloning and troubleshooting various Oracle E-Business Suite products in Oracle database 9i and 10g. Proactive in the different phases of architecture design, development, testing, maintenance, performance enhancement and postproduction support. Currently working as Oracle Application DBA at Warwickshire County Council.
------------------------------------------------------------------------------------------------------------------------------
Over 7 years of experience as an Oracle Applications DBA managing Oracle Public Sector HRMS 11.5.10 and Oracle Financials 11i in Unix and Windows NT environments. Strong skills and experience with installing, patching, cloning and troubleshooting various Oracle E-Business Suite products in Oracle database 9i and 10g. Proactive in the different phases of architecture design, development, testing, maintenance, performance enhancement and postproduction support. Currently working as Oracle Application DBA at Warwickshire County Council.
------------------------------------------------------------------------------------------------------------------------------