Database block size:select to_number(value) "Block size in bytes"
from sys.v_$parameter
where name = 'db_block_size';
Max number of possible extents:select to_number(value)/16-7 "MaxExtents"
from sys.v_$parameter
where name = 'db_block_size';
Min extent size:select to_number(a.value) * to_number(b.value) / 1024 "Min extent size in K"
from sys.v_$parameter a, sys.v_$parameter b
where a.name = 'db_block_size'
and b.name = 'db_file_multiblock_read_count';
List objects in the SYSTEM tablespace that doesn't belong to SYS or SYSTEM
select * from sys.dba_segments
where owner not in ('PUBLIC', 'SYS', 'SYSTEM')
and tablespace_name = 'SYSTEM';
Reports information about your current database context
select 'User: '|| user || ' on database ' || global_name,
' (term='||USERENV('TERMINAL')||
', audsid='||USERENV('SESSIONID')||')' as MYCONTEXT
from global_name;
Reports free memory available in the SGA
select name,
sgasize/1024/1024 "Allocated (M)",
bytes/1024 "Free (K)",
round(bytes/sgasize*100, 2) "% Free"
from (select sum(bytes) sgasize from sys.v_$sgastat) s,
sys.v_$sgastat f
where f.name = 'free memory';
Measure the Buffer Cache Hit Ratio
Get initial Buffer Hit Ratio readingSELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets' AND phy.name = 'physical reads';
******************************
SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets' AND con.name = 'consistent gets'
AND phy.name = 'physical reads';
Database users with deadly roles assigned to them.
select grantee, granted_role, admin_option
from sys.dba_role_privs
where granted_role in ('DBA', 'AQ_ADMINISTRATOR_ROLE',
'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
'OEM_MONITOR')
and grantee not in ('SYS', 'SYSTEM', 'OUTLN',
'AQ_ADMINISTRATOR_ROLE',
'DBA', 'EXP_FULL_DATABASE',
'IMP_FULL_DATABASE',
'OEM_MONITOR',
'CTXSYS', 'DBSNMP', 'IFSSYS',
'IFSSYS$CM', 'MDSYS', 'ORDPLUGINS',
'ORDSYS',
'TIMESERIES_DBA');
Database users with deadly system privilages assigned to them.
select grantee, privilege, admin_option
from sys.dba_sys_privs
where (privilege like '% ANY %'
or privilege in ('BECOME USER', 'UNLIMITED TABLESPACE')
or admin_option = 'YES')
and grantee not in ('SYS', 'SYSTEM', 'OUTLN',
'AQ_ADMINISTRATOR_ROLE',
'DBA', 'EXP_FULL_DATABASE',
'IMP_FULL_DATABASE',
'OEM_MONITOR', 'CTXSYS',
'DBSNMP', 'IFSSYS',
'IFSSYS$CM', 'MDSYS',
'ORDPLUGINS', 'ORDSYS',
'TIMESERIES_DBA');
Allocated, Used & Free space within datafiles
SELECT SUBSTR (df.NAME, 1, 40) file_name,
df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;
Show Used/free space by tablespace name
SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) Free_space,
nvl(total_space-Free_space, 0) Used_space,
total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;
Total Tablespace size
SELECT space.tablespace_name, space.total_space, free.total_free,
ROUND(free.total_free/space.total_space*100) as pct_free,
ROUND((space.total_space-free.total_free),2) as total_used,
ROUND((space.total_space-free.total_free)/space.total_space*100) as pct_used,
free.max_free, next.max_next_extent
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 total_space
FROM dba_data_files
GROUP BY tablespace_name) space,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free, ROUND(MAX(bytes)/1024/1024,2) max_free
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent FROM dba_segments
GROUP BY tablespace_name) NEXT
WHERE space.tablespace_name = free.tablespace_name (+)
AND space.tablespace_name = next.tablespace_name (+)
AND (ROUND(free.total_free/space.total_space*100)<> free.max_free)
order by pct_used desc;
Ivestigating undo-tablespace in EBS R12
SELECT version FROM v$timezone_file;select count(status) from dba_undo_extents where status = 'ACTIVE';
select count(status) from dba_undo_extents where status = 'UNEXPIRED';
select (sum(bytes)/(1024*1024)) "SIZE in MB",tablespace_name from dba_free_space group by tablespace_name;
Last Analyzed
select max(last_analyzed) from dba_tables;
Listing Memory Used By All Sessions
select se.sid,n.name,
max(se.value) maxmem
from v$sesstat se,
v$statname n
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
group by n.name,se.sid
order by 3 ;
INDEX :-
select segment_name, owner, extents, max_extents
from dba_segments
where segment_type = 'INDEX' and
(extents +1) >= max_extents;
SESSION WAITS
SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM v$session_wait sw,
v$session s
WHERE s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC;
Find type of objects available in a tablespace
select ds.segment_name,do.object_type,do.status,ds.tablespace_name from dba_segments ds,dba_objects do where ds.tablespace_name='TOOLS';
Temp tablespace usage information
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
To count no. of segments in each t.s.
select tablespace_name,count(*) from dba_segments group by tablespace_name;
Database growth per month for last year
select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month');
To select the username and the process status
select a.requested_start_date,a.last_update_date,a.status_code,b.user_name
from fnd_concurrent_requests a,fnd_user b
where a.requested_by = b.user_id and a.request_id = 677224
select a.requested_start_date,a.last_update_date,a.status_code,b.user_name
,a.argument_text
from fnd_concurrent_requests a,fnd_user b
where a.requested_by = b.user_id and a.request_id = 677224
To select the username,process,status,Terminal name using SID
select a.status,p.spid, a.sid, a.serial#, a.username, a.terminal,a.osuser
,c.Consistent_Gets, c.Block_Gets, c.Physical_Reads,
(100*(c.Consistent_Gets+c.Block_Gets-c.Physical_Reads)/
(c.Consistent_Gets+c.Block_Gets)) HitRatio, c.Physical_Reads, b.sql_text
from v$session a, v$sqlarea b, V$SESS_IO c,v$process p
where a.sql_hash_value = b.hash_value
and a.SID = c.SID
and p.addr = a.paddr
and (c.Consistent_Gets+c.Block_Gets)>0
and a.Username is not null
Order By a.status asc, c.Consistent_Gets desc , c.Physical_Reads desc;
To see the currently updated archive log files
SQL>select name from v$archived_log where trunc(completion_time) >= trunc(sysdate)-5;
To find the BDUMP,UDUMP directory
select value from v$parameter where name = 'background_dump_dest';
select value from v$parameter where name = 'user_dump_dest';
select value from v$parameter
where name in ('background_dump_dest','user_dump_dest', 'log_archive_dest');
Prompt Showing what sql statement is doing
select a.sid, a.value session_cpu, c.physical_reads,
c.consistent_gets,d.event,d.seconds_in_wait
from v$sesstat a,v$statname b, v$sess_io c, v$session_wait d
where a.sid= 14
and b.name = 'CPU used by this session'
and a.statistic# = b.statistic#
and a.sid=c.sid
and a.sid=d.sid;
Check all active processes, the latest SQL, and the SQL hit ratio
select a.status, a.sid, a.serial#, a.username, a.terminal,
a.osuser, c.Consistent_Gets, c.Block_Gets, c.Physical_Reads,
(100*(c.Consistent_Gets+c.Block_Gets-c.Physical_Reads)/
(c.Consistent_Gets+c.Block_Gets)) HitRatio,
c.Physical_Reads, b.sql_text
from v$session a, v$sqlarea b, V$SESS_IO c
where a.sql_hash_value = b.hash_value
and a.SID = c.SID
and (c.Consistent_Gets+c.Block_Gets)>0
and a.Username is not null
and a.status = 'ACTIVE'
Order By a.status asc, c.Consistent_Gets desc , c.Physical_Reads desc;
Monitoring Oracle processes
select p.spid "Thread ID", b.name "Background Process", s.username "User Name",
s.osuser "OS User", s.status "STATUS", s.sid "Session ID",
s.serial# "Serial No.",
s.program "OS Program"
from v$process p, v$bgprocess b, v$session s
where s.paddr = p.adr and b.paddr(+) = p.addr
order by s.status,1;
Query to find the ATG rollup Patch level in your application (11i).
connect as apps:
SELECT (bug_number),
decode((bug_number),
'3438354','ATG_PF.H',
'4017300','ATG_PF.H RUP 1',
'4125550','ATG_PF.H RUP 2',
'4334965','ATG_PF.H RUP 3',
'4676589','ATG_PF.H RUP 4',
'5473858','ATG_PF.H RUP 5',
'5903765','ATG_PF.H RUP 6',
'6241631','ATG_PF.H RUP 7')
FROM ad_bugs
WHERE bug_number IN
('3384350',
'3438354',
'4017300',
'4125550',
'4334965',
'4676589',
'5473858',
'5903765',
'6241631')
order by bug_number desc;
To find RUP Level in R12 : - select bug_number, creation_date
from ad_bugs
where bug_number in ('6022657','6266113','6728000')
ORDER BY 2;
To Check Maintenance Family Pack Level select patch_level from fnd_product_installations
where application_id = 275;
Query to find the Apps Version : - select release_name from fnd_product_groups;
Which FND_USER is locking that table
The column named "module" will tell you the name of the Form Function
or the Concurrent Program Short name which has aquired a lock onto that table.
SELECT c.owner ,c.object_name,c.object_type, fu.user_name locking_fnd_user_name ,fl.start_time locking_fnd_user_login_time, vs.module,vs.machine ,vs.osuser ,vlocked.oracle_username,vs.sid,vp.pid ,vp.spid AS os_process,vs.serial# ,vs.status ,vs.saddr ,vs.audsid ,vs.processFROM fnd_logins fl ,fnd_user fu ,v$locked_object vlocked ,v$process vp ,v$session vs,dba_objects cWHERE vs.sid = vlocked.session_idAND vlocked.object_id = c.object_idAND vs.paddr = vp.addrAND vp.spid = fl.process_spid(+)AND vp.pid = fl.pid(+)AND fl.user_id = fu.user_id(+)AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'AND nvl(vs.status ,'XX') != 'KILLED';Security related database initialization parameters and password file users.
select name || '=' || value "PARAMTER"
from sys.v_$parameter
where name in ('remote_login_passwordfile', 'remote_os_authent',
'os_authent_prefix', 'dblink_encrypt_login',
'audit_trail', 'transaction_auditing');
List security related profile information
select profile, resource_name, limit
from dba_profiles
where resource_name like '%PASSWORD%'
or resource_name like '%LOGIN%';
To find space used by a database user
Query to find space used by a database user. Following query can be used to know the space used by the logged in user in MBs:SELECT sum(bytes)/1024/1024 user_size FROM user_segments;
Query to find the space occupied by all the users in a database. This requires access to dba_segments table:SELECT owner, sum(bytes)/1024/1024 total_size FROM dba_segments
GROUP BY owner ORDER BY total_size DESC;
Total space occupied by all users:SELECT sum(bytes)/1024/1024 total_size FROM dba_segments;
To find Database Size
The database mainly comprises of datafiles, temp files and redo log files.
The biggest portion of a database’s size comes from the datafiles.
To find out how many megabytes are allocated to all datafiles: SELECT sum(bytes)/1024/1024 data_size FROM dba_data_files;
To get the size of all TEMP files: SELECT nvl(sum(bytes),0)/1024/1024 temp_size FROM dba_temp_files;
To get the size of the on-line redo-logs: SELECT sum(bytes)/1024/1024 redo_size FROM sys.v_$log;
Finally, summing up the three above, total database size can be found:SELECT (dsize.data_size + tsize.temp_size + rsize.redo_size)/1024/1024 "total_size"
FROM (SELECT sum(bytes) data_size
FROM dba_data_files ) dsize,
(SELECT nvl(sum(bytes),0) temp_size
FROM dba_temp_files ) tsize,
(SELECT sum(bytes) redo_size
FROM sys.v_$log ) rsize;
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;
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;
Tablespaces that have less then 90% free space
select tbs.tablespace_name,
tot.bytes/(1024*1024) "Total Space in MB",
round(tot.bytes/(1024*1024)- sum(nvl(fre.bytes,0))/(1024*1024),2) "Used in MB",
round(sum(nvl(fre.bytes,0))/(1024*1024),2) "Free in MB",
round((1-sum(nvl(fre.bytes,0))/tot.bytes)*100,2) Pct,
decode(
greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),
90, '', '*'
) Pct_warn
from dba_free_space fre,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/(1024*1024), tot.bytes
order by 5 desc, 1 ;
Problem tablespaces SELECT space.tablespace_name, space.total_space, free.total_free,
ROUND(free.total_free/space.total_space*100) as pct_free,
ROUND((space.total_space-free.total_free),2) as total_used,
ROUND((space.total_space-free.total_free)/space.total_space*100) as pct_used,
free.max_free, next.max_next_extent
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 total_space
FROM dba_data_files
GROUP BY tablespace_name) space,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free, ROUND(MAX(bytes)/1024/1024,2) max_free
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent FROM dba_segments
GROUP BY tablespace_name) NEXT
WHERE space.tablespace_name = free.tablespace_name (+)
AND space.tablespace_name = next.tablespace_name (+)
AND (ROUND(free.total_free/space.total_space*100)<> free.max_free)
order by pct_used desc ;
Number of Disk Sorts vs Memory Sorts
select 'INIT.ORA sort_area_size: 'value
from v$parameter
where name like 'sort_area_size' ;
select a.name, value
from v$statname a, v$sysstat
where a.statistic# = v$sysstat.statistic#
and a.name in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)') ;
Jobs Currently running in the local database.
select
djr.sid sess,
djr.job jid,
dj.log_user subu,
dj.priv_user secd,
dj.what proc,
to_char(djr.last_date,'MM/DD') lsd,
substr(djr.last_sec,1,5) lst,
to_char(djr.this_date,'MM/DD') nrd,
substr(djr.this_sec,1,5) nrt,
djr.failures fail
from
sys.dba_jobs dj,
sys.dba_jobs_running djr
where
djr.job = dj.job;
Jobs that have been submitted to run in the local database job queue select
job jid,
log_user subu,
priv_user secd,
what proc,
to_char(last_date,'MM/DD') lsd,
substr(last_sec,1,5) lst,
to_char(next_date,'MM/DD') nrd,
substr(next_sec,1,5) nrt,
failures fail,
decode(broken,'Y','N','Y') ok
from
sys.dba_jobs ;
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';
=== Temp Usage ===
select sum(blocks)/1024*8 "Size in MB" FROM v$sort_usage;
SELECT ss.sid, sum(st.blocks)/1024*8
FROM v$sort_usage st, v$session ss
where ss.saddr=st.session_addr
group by ss.sid
having sum(st.blocks)/1024*8 > 100 order by 2;
SELECT /*+ RULE */ s.username, s.osuser, s.sid,
NVL(s.machine,'N/A'),
NVL(s.module,'N/A'), NVL(s.action,'N/A'),
NVL(s.program,'N/A'),
s.status ||' for '||LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0') ||':'||LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr',
u.tablespace, u.contents, u.extents,
round((u.blocks*8)/1024),s.sql_address, s.sql_hash_value
FROM v$session s, v$sort_usage u
WHERE s.saddr = u.session_addr
AND u.contents = 'TEMPORARY'
AND s.audsid != USERENV('sessionid')
AND (u.blocks*8)/1024 >= 1000
ORDER BY 1,2,3,4,5 Desc;
=== High Redo ===
SELECT s.inst_id,s.sid, s.serial#, s.username,
s.program, i.block_changes
FROM gv$session s, gv$sess_io i
WHERE s.sid = i.sid
AND i.block_changes > 10000000
ORDER BY 6 desc, 1, 2, 3, 4;
=== Rollback Used ===
SELECT rn.name, ROUND(rs.rssize/1024/1024),
s.username, s.osuser, s.sid, NVL(s.machine,'N/A'),
NVL(s.module,'N/A'), NVL(s.action,'N/A'),
NVL(s.program,'N/A'),
s.sql_address, s.sql_hash_value, p.spid,
s.status ||' for '||LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0') ||':'||LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr' ,
round(ceil((t.used_ublk*8)/1024),1)
FROM v$rollname rn, v$rollstat rs,
v$session s, v$transaction t, v$process p
WHERE rn.usn = rs.usn
AND round((t.used_ublk*8)/1024) >= 1000
AND rs.usn = t.xidusn
AND s.sid = p.pid (+)
AND s.taddr = t.addr
ORDER BY 2 desc, s.sid ,s.status;
=== Roll back segement Information ====
select tablespace_name, status segment_status,
count(extent_id) "Extent Count", sum(blocks) "Total Blocks",
sum(blocks)*8/(1024*1024) "Total Space in GB"
from dba_undo_extents
where tablespace_name like '%UNDO%'
group by tablespace_name, status;
=== Shared Pool Usage ===
SELECT INST_ID,ROUND(bytes/1024/1024, 2)||' MB'
FROM gv$sgastat
WHERE name='free memory' AND pool='shared pool';
=== Archive Generation for last 5 hours ===
SELECT TO_CHAR(first_time, 'DD-MM-YY') AS Day,
TO_CHAR(first_time, 'HH24') AS Hour,COUNT(*)
FROM v$log_history
WHERE TO_CHAR(first_time, 'DD-MM-YY') = TO_CHAR(sysdate, 'DD-MM-YY')
AND TO_CHAR(first_time, 'HH24') >= TO_CHAR(sysdate, 'HH24') - 5
GROUP BY TO_CHAR(first_time, 'DD-MM-YY'),
TO_CHAR(first_time, 'HH24') ORDER BY 2;
=== High Memory ===
select s.inst_id, s.sid, name,
round(value/1024/1024) Mem_in_mb, p.*
from gv$sesstat s, v$statname n, gv$session p
where n.statistic# = s.statistic#
and s.inst_id = p.inst_id
and s.sid = p.sid
and name like '%pga memory%'
and value > 1*1024*1024*512;
=== Performance ===
select sql_hash_value, count(*)
from v$session
where event like 'enq%'
group by sql_hash_value;
select sql_hash_value,username,osuser, count(*)
from v$session
where event like 'enq%'
and SQL_HASH_VALUE='&event'
group by sql_hash_value,username,osuser;
select sql_text
from v$sqlarea
where hash_value = '&hash_value';
select s1.sid,FETCHES,ROWS_PROCESSED from v$sql s,v$session s1 where s.HASH_VALUE=s1.SQL_HASH_VALUE and s1.sid=4885;select s.sid,s.serial#,s.status,s.username,s.module,s.osuser,x.event from v$session s,v$session_wait x where s.paddr in ( select addr from v$process where spid in (2340,23869,13827,18261,14880,2381))
and x.sid=s.sid;select s.sid,s.serial#,s.status,s.username,s.module,s.osuser,x.event from v$session s,v$session_wait x
where x.sid=s.sid and x.event like '&event_name';=== Other Script to update the daily report ===
select status,count(1) from gv$session group by status;
select count(1) from dba_tables where logging='NO';
select distinct status,count(1) from dba_indexes group by status;
select count(1) from dba_objects where status='INVALID';
Regards,
Sukhwinder Singh
If you are familiar with Oracle Apps 11i , you can easily understand picture on your right. This is file system for 11i with various TOP's , APPL_TOP, ORA_TOP, COMMON_TOP, DB_TOP and DATA_TOP. With Multiple Middle Tier configuration came shared APPL_TOP (only APPL_TOP used to be shared ) . After shared APPL_TOP came shared Application Tier (All three TOP's on Application Tier, APPL_TOP, ORA_TOP and COMMON_TOP were shared across servers). With this came challenge of configuration files which are specific to a server mainly iAS & 806 configuration file. If you have configured Shared Application Tier (sharing ORA_TOP as well) in 11i then this problem was overcome by creating conf directory in COMMON_TOP.
If you are familiar with Oracle Apps 11i , you can easily understand picture on your right. This is file system for 11i with various TOP's , APPL_TOP, ORA_TOP, COMMON_TOP, DB_TOP and DATA_TOP. With Multiple Middle Tier configuration came shared APPL_TOP (only APPL_TOP used to be shared ) . After shared APPL_TOP came shared Application Tier (All three TOP's on Application Tier, APPL_TOP, ORA_TOP and COMMON_TOP were shared across servers). With this came challenge of configuration files which are specific to a server mainly iAS & 806 configuration file. If you have configured Shared Application Tier (sharing ORA_TOP as well) in 11i then this problem was overcome by creating conf directory in COMMON_TOP.
