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"
Wednesday, February 25, 2009
Tuesday, February 24, 2009
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
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
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
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
Monday, February 23, 2009
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
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
Friday, February 20, 2009
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;
Tuesday, February 17, 2009
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;
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;
Wednesday, February 11, 2009
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'
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.
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.
Tuesday, February 10, 2009
Monday, February 2, 2009
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.
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.
Subscribe to:
Posts (Atom)