How to find Components version in R12

Apache Version

$IAS_ORACLE_HOME/Apache/Apache/bin/httpd -v



Perl Version

$IAS_ORACLE_HOME/perl/bin/perl -v|grep built


Java Version

sh -c "`awk -F= '$1 ~ /^JSERVJAVA.*$/ {print $2}' $ADMIN_SCRIPTS_HOME/java.sh` -version;"

Jre version
cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version| cut -c 1-35

Forms Version

$ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version

Plsql Version

$ORACLE_HOME/bin/frmcmp_batch|grep PL/SQL|grep Version

Forms Communication mode

cat $FORMS_WEB_CONFIG_FILE|grep serverURL=
echo "If the serverURL parameter has no value then Forms is implemented in socket mode else it is servlet"

OATM Conversion Steps

http://www.scribd.com/doc/6813095/OATM-Conversion

Basic Unix Shell scritps for DBA

http://www.bijoos.com/ora7/oracle_unix.htm

Following are the shell scripts available:

1. Alert Log
2. To ping other servers on the network
3. To tnsping all Oracle databases on the network
4. To compare and verify if all instances mentioned in /etc/oratab is up and running
5. Alert for potential space problems and invalid objects, triggers
6. Script to identify the top 20 longest running oracle sessions
7. Useful if you have multiple versions of oracle on the same box
8. This script may be used as a cronjob to coalesce the tablespaces periodically

and many more

Basic Unix commands 4 DBA

http://www.bijoos.com/oracle/unix4dba1.htm

Queries on maintenance mode

Query to know status of maintenance mode:
select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual;



Query to ENABLE of maintenance mode:
sqlplus -s &un_apps/***** @/tcme3i/applmgr/1159/ad/11.5.0/patch/115/sql/adsetmmd.sql ENABLE


Query to DISABLE of maintenance mode:
sqlplus -s &un_apps/***** @/tcme3i/applmgr/1159/ad/11.5.0/patch/115/sql/adsetmmd.sql DISABLE

Steps to resolve locks on database

Error Message:Could Not Reserve Records Due to Database Record Lock

1. Identify the Oracle serial ID, SID ID and terminate without shutting the
database down.

1.1 Make sure that the user is logged off.

1.1.1 Type ps -ef |grep

1.1.2 Kill all processes related to that user.

1.2 Identify SID, serial#

select distinct
acc.object, ses.osuser, ses.process,
ses.sid, ses.serial#
from v$access acc,
v$session ses
where (acc.owner != 'SYS'
or acc.object = 'PLAN_TABLE')
and acc.sid = ses.sid
and ses.status != 'INACTIVE'
and ses.type != 'BACKGROUND'
and acc.object not in ('V$ACCESS','V$SESSION')
and ses.audsid != userenv('SESSIONID')
order by 1,2,3
/

1.3. Double-check the identified SID and serial ID:

SELECT osuser,
username,
process,
sid,
serial#,
status,
to_char(logon_time,'DD-MON HH24:MI:SS') logon_time,
machine,
program
FROM v$session
WHERE sid = &SID_NUM
/

1.4. ALTER SYSTEM KILL SESSION '&SID_NUM,&SERIAL_NUM';
Alternatively use the following scripts to identify the blocking session:

-- check for locked tables
select a.object_id, a.session_id, substr(b.object_name, 1, 40)
from v$locked_object a,
dba_objects b
where a.object_id = b.object_id
order by b.object_name ;

--find_blocked.sql
select sid,
decode(block ,0,'NO','YES') BLOCKER,
decode(request,0,'NO','YES') WAITER
from v$lock
where request > 0 or block > 0 order by block desc
/

SELECT 'alter system kill session '''||vs.sid||','||vs.serial#||'''' ,al.
object_name, al.object_type, vs.status,
fu.user_name,vs.process,vs.osuser,vs.username,
to_char(vs.logon_time,'DD-MON HH24:MI:SS') logon_time, vs.program
FROM fnd_logins fl, fnd_user fu, all_objects al, v$lock vl, v$session vs
WHERE fl.pid = vl.sid
AND vl.id1 = al.object_id (+)
AND fl.user_id = fu.user_id
AND to_char (start_time, 'DD-MON-RR') = to_char (sysdate, 'DD-MON-RR')
and vs.sid=vl.sid
and vl.sid = &sid

'ALTERSYSTEMKILLSESSION'''||VS.SID||','||VS.SERIAL#||''''


Reference :

Metalink Note ID: 140969.1

Query to find free space in temporary tablesapce:

SELECT tablespace_name,SUM(bytes_used),SUM(bytes_free) FROM V$temp_space_header GROUP BY tablespace_name;

Script to find tablespace free space in a database

SELECT a.tablespace_name, a.file_name, a.bytes allocated_bytes,
b.free_bytes
FROM dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE a.file_id=b.file_id
and a.tablespace_name='SYSTEM'
ORDER BY a.tablespace_name;

Script to find Table size in a database

Script to find Table size in a database.

select sum(BYTES/1024/1024) as TOTAL_GIG from user_segments where
SEGMENT_NAME = 'TABLE_NAME';

Note: Need to execute as owner of the table.

(OR)

select sum(BYTES/1024/1024) as TOTAL_GIG from dba_segments where SEGMENT_NAME='FND_TS_MIG_CMDS'

Default Permissions in Unix

Default Permissions in Unix:

1. For a File -- 644

2. For a Directory -- 755

3. user home directory at the time user creation -- 700

eg: useradd -g dba -d /home/user1

Here directory "user1" have 700 permissions by default.

Best site for DBA Monitoring Scripts

http://www.oracle-base.com/dba/DBACategories.php

Diff bet ad_bugs and ad_appiled_patches tables

Diff bet ad_bugs & ad_appiled_patches tables

AD_BUGS - AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (ie.patched) in the Oracle Applications installation.

AD_APPLIED_PATCHES - gives information only abt the bugs/patches those we applied after installation
- holds information about the "distinct" Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content (eg. "merged" patches), then they are considered distinct and this table will therefore hold 2 records.