| The first 6 questions should be provided manually and rest of them could be gathered by script.
1. Hardware Information
* DB sever
- System Model :
- OS version :
- # CPUs :
- CPU speed :
- MEMORY :
- Disk I/O :
- Etc :
* CM (Concurrent Manager) sever
- System Model :
- OS version :
- # CPUs :
- CPU speed :
- MEMORY :
- Disk I/O :
- Etc :
* AP (Application) sever
- System Model :
- OS version :
- # CPUs :
- CPU speed :
- MEMORY :
- Disk I/O :
- Etc :
2. environment information (# tiers, network setup, RAC etc..)
3. # of total and avg. concurrent users during peak times.
4. DB size (including version)
5. modules being used.
6. # of employees
====
The following sql script will gather additional information
from the production database. After sql*plus login with apps user,
pl run system.sql and get the spool output file, system.txt
under the current directory.
--File name : system.sql
spool system.txt
set linesize 180
set pagesize 600
prompt Apps Version SELECT release_name from fnd_product_groups; prompt DB SERVER INFORMATION
col host_name format a20
COL VERSION FORMAT A15
COL STATUS FORMAT A10
COL THREAD# FORMAT 99
select
INST_ID,
INSTANCE_NUMBER ,
INSTANCE_NAME ,
HOST_NAME ,
VERSION ,
STATUS ,
PARALLEL ,
THREAD#
from gv$instance ;
PROMPT MAJOR TABLE LIST
select owner, table_name, num_rows, LAST_ANALYZED
From dba_tables
where table_name in (
'AP_INVOICES_ALL', 'AP_INVOICE_DISTRIBUTIONS_ALL', 'AR_PAYMENT_SCHEDULES_ALL',
'RA_CUSTOMER_TRX_ALL', 'RA_CUSTOMER_TRX_LINES_ALL' ,
'HZ_PARTIES', 'HZ_CUST_ACCOUNTS',
'AS_SALES_LEADS', 'AS_ACCESSES_ALL_ALL',
'BOM_STRUCTURES_B', 'BOM_COMPONENTS_B',
'CS_INCIDENTS_ALL_B',
'FA_ADJUSTMENTS', 'FA_DEPRN_DETAIL', 'FA_DEPRN_SUMMARY',
'FND_USERS',
'GL_JE_HEADERS', 'GL_JE_LINES',
'MRP_GROSS_REQUIREMENTS', 'MRP_RECOMMENDATIONS', 'MRP_FULL_PEGGING',
'MRP_BOM_COMPONENTS', 'MTL_MATERIAL_TRANSACTIONS',
'MTL_TRANSACTION_ACCOUNTS', 'MTL_SYSTEM_ITEMS_B',
'HR_ORGANIZATION_INFORMATION', 'HR_OPERATING_UNITS',
'MTL_PARAMETERS',
'OE_ORDER_HEADERS_ALL', 'OE_ORDER_LINES_ALL',
'PO_HEADERS_ALL', 'PO_LINES_ALL', 'PO_VENDORS',
'WF_ITEM_ACTIVITY_STATUSES', 'WF_ITEM_ATRIBUTE_VALUES',
'WF_NOTIFICATIONS', 'WF_NOTIFICATION_ATTRIBUTES' ,
'WSH_DELIVERY_DETAILS' , 'WSH_DELIVERY_ASSIGNMENTS',
'WSH_NEW_DELIVERIES', 'WSH_DELIVERY_LEGS',
'WSH_TRIP_STOPS', 'WSH_TRIPS' )
order by table_name ;
PROMPT number of daily concurrent requests.
SELECT trunc(REQUESTED_START_DATE), count(*)
FROM FND_CONCURRENT_REQUESTS
WHERE REQUESTED_START_DATE BETWEEN sysdate-30 AND sysdate
group by rollup(trunc(REQUESTED_START_DATE)) ;
PROMPT Applications versions and family pack versions.
COL APPLICATION_NAME FORMAT A60
COL SHT_NAME FORMAT A10
col PATCH_LEVEL FORMAT A20
SELECT A.APPLICATION_SHORT_NAME SHT_NAME,T.APPLICATION_NAME, I.STATUS,
NVL(I.PATCH_LEVEL, 'n/a') PATCH_LEVEL, I.DB_STATUS
FROM FND_PRODUCT_INSTALLATIONS I,
FND_APPLICATION A,
FND_APPLICATION_TL T
WHERE A.APPLICATION_ID = I.APPLICATION_ID
AND A.APPLICATION_ID = T.APPLICATION_ID
AND T.LANGUAGE = USERENV('LANG')
ORDER BY 1 ;
PROMPT Multi-org being used.
select MULTI_ORG_FLAG org, MULTI_LINGUAL_FLAG lingual, MULTI_CURRENCY_FLAG currency
from FND_PRODUCT_GROUPS ;
PROMPT DB size with Tablespace
set head on
set pagesize 30
select NVL(tablespace_name,'** Total **') "Tablespace Name",
sum("allocated") "Allocated(M)",
sum("used") "Used(M)",
sum("free") "Free(M)",
sum(df_cnt) "#(File)"
from
(
select a.tablespace_name, trunc(b.assigned/1048576) "allocated",
trunc((b.assigned-a.free)/1048576) "used",
trunc(a.free/1048576) "free",
df_cnt
from
(
select tablespace_name, sum(bytes) free
from dba_free_space
group by tablespace_name ) a,
(
select tablespace_name, sum(bytes) assigned, count(*) df_cnt
from dba_data_files
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name
UNION ALL
SELECT tablespace_name||'[TMP]', trunc(sum(bytes)/1048576), null, null, count(*) df_cnt
from dba_temp_files
group by tablespace_name
)
group by rollup(tablespace_name) ;
spool off
|
No comments:
Post a Comment