Sujeet APPS DBA: R12/11i APPS Command

OS Level Command

[root@sonu ~]# cat /etc/redhat-release

Red Hat Enterprise Linux Server release 5.4 (Tikanga)

[root@sonu ~]# cat /proc/version

Linux version 2.6.18-164.el5 (mockbuild@x86-002.build.bos.redhat.com) (gcc version 4.1.2 20080704 (Red Hat 4.1.2-46)) #1 SMP Tue Aug 18 15:51:54 EDT 2009

[root@sonu ~]# getconf LONG_BIT

[root@sonu ~]# uname -a

Linux sonu.oracle.com 2.6.18-164.el5 #1 SMP Tue Aug 18 15:51:54 EDT 2009 i686 i686 i386 GNU/Linux

sysdba SQL command:-(11g database)

[oracle@r0469 ~]$ cd /oracle/VIS/db/tech_st/11.1.0/

[oracle@r0469 11.1.0]$ . VIS_r0469.env

[oracle@r0469 11.1.0]$ sqlplus

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Sep 19 11:35:19 2012

Enter user-name: sys as sysdba

Enter password: sys

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production

PL/SQL Release 11.1.0.7.0 - Production

CORE 11.1.0.7.0 Production

TNS for Linux: Version 11.1.0.7.0 - Production

NLSRTL Version 11.1.0.7.0 - Production

SQL> select

INST_ID,

INSTANCE_NUMBER ,

INSTANCE_NAME ,

HOST_NAME ,

VERSION ,

STATUS ,

PARALLEL ,

THREAD#

from gv$instance ;

INST_ID INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STATUS PAR THREAD#

1 1 VIS r0469.oracle.com 11.1.0.7.0 OPEN NO 1

SQL> select version from orasso.wwc_version$;

VERSION

--------------------------------------------------------------------------------

10.1.2.0.2

APPS User SQL COMMAND:-(R12.1.1)

[oracle@ebs602 scripts]$ sqlplus

SQL*Plus: Release 10.1.0.5.0 - Production on Sun Apr 19 11:27:04 2009

Enter user-name: apps

Enter password: apps

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select release_name from apps.fnd_product_groups;

RELEASE_NAME

--------------------------------------------------

12.1.1

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.

SQL> 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.

SQL> select MULTI_ORG_FLAG org, MULTI_LINGUAL_FLAG lingual, MULTI_CURRENCY_FLAG currency

from FND_PRODUCT_GROUPS ; 2

O L C

- - -

Y Y

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) ;

Switch SQL promt to command promt:-

SQL> !

[oracle@ebs602 scripts]$

SQL> exit

[oracle@ebs602 scripts]$

Startup Applications (R12.1.1)

1. Start Database first:-

[oracle@r0469 scripts]$ cd /oracle/VIS/db/tech_st/11.1.0/appsutil/scripts/VIS_r0469/

[oracle@r0469 VIS_r0469]$ ./addbctl.sh start

Connected to an idle instance.

ORACLE instance started.

Total System Global Area 1071333376 bytes

Fixed Size 1318172 bytes

Variable Size 427819748 bytes

Database Buffers 629145600 bytes

Redo Buffers 13049856 bytes

Database mounted.

Database opened.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

addbctl.sh: exiting with status 0

2. Start Listener:-

[oracle@r0469 scripts]$ cd /oracle/VIS/db/tech_st/11.1.0/appsutil/scripts/VIS_r0469/

[oracle@r0469 VIS_r0469]$ ./addlnctl.sh start VIS

Starting listener process VIS ...

addlnctl.sh: exiting with status 0

3. Start Applications:-

[oracle@r0469 ~]$ cd /oracle/VIS/apps/apps_st/appl/

[oracle@r0469 appl]$ . APPSVIS_r0469.env

[oracle@r0469 appl]$ cd $ADMIN_SCRIPTS_HOME

[oracle@r0469 scripts]$ ./adstrtal.sh apps/apps

All enabled services for this node are started.

adstrtal.sh: Exiting with status 0

adstrtal.sh: check the logfile /oracle/VIS/inst/apps/VIS_r0469/logs/appl/admin/log/adstrtal.log for more information ...

NOTE:- Open web page enter <IP Address:port pool>

URL :- 192.16.1.354:8000

open Application Login page...

Down Applications (R12.1.1)

1. Down Application Tier:-

[oracle@r0469 ~]$ cd /oracle/VIS/apps/apps_st/appl/

[oracle@r0469 appl]$ . APPSVIS_r0469.env

[oracle@r0469 appl]$ cd $ADMIN_SCRIPTS_HOME

[oracle@r0469 scripts]$ ./adstpall.sh apps/apps

All enabled services on this node are stopped.

adstpall.sh:Exiting with status 0

adstpall.sh: check the logfile /oracle/VIS/inst/apps/VIS_r0469/logs/appl/admin/log/adstpall.log for more information ...

2. Down Listener:-

[oracle@r0469 scripts]$ cd /oracle/VIS/db/tech_st/11.1.0/appsutil/scripts/VIS_r0469/

[oracle@r0469 VIS_r0469]$ ./addlnctl.sh stop VIS

You are running addlnctl.sh version 120.1.12010000.2

Logfile: /oracle/VIS/db/tech_st/11.1.0/appsutil/log/VIS_r0469/addlnctl.txt

Shutting down listener process VIS ...

addlnctl.sh: exiting with status 0

3. Down database Tier:-

[oracle@r0469 scripts]$ cd /oracle/VIS/db/tech_st/11.1.0/appsutil/scripts/VIS_r0469/

[oracle@r0469 VIS_r0469]$ ./addbctl.sh stop immediate

Connected.

Database closed.

Database dismounted.

ORACLE instance shut down.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

addbctl.sh: exiting with status 0

***********************************END*****************************

How to check Database & Application UP & Running:-

Check how many Database UP & Down:-

[oracle@r0469 ~]$ ps -ef | grep pmon

oracle 22862 1 0 12:19 ? 00:00:00 ora_pmon_VIS

oracle 24395 20422 0 12:27 pts/0 00:00:00 grep pmon

Hear only one database UP (VIS)

Check Listener UP & Down:-

[oracle@r0469 ~]$ ps -ef | grep tns

oracle 22952 1 0 12:21 ? 00:00:00 /oracle/VIS/db/tech_st/11.1.0/bin/tnslsnr VIS -inherit

oracle 23123 1 0 12:22 ? 00:00:00 /oracle/VIS/apps/tech_st/10.1.2/bin/tnslsnr APPS_VIS -inherit

oracle 24399 20422 0 12:27 pts/0 00:00:00 grep tns

Hear Listener Up & Running

Check Application UP & Down:-

[oracle@r0469 ~]$ ps -ef | grep FNDLIBR

oracle 23557 23524 0 12:23 pts/0 00:00:00 FNDLIBR

oracle 23851 23730 0 12:24 ? 00:00:00 FNDLIBR

oracle 23854 23730 0 12:24 ? 00:00:00 FNDLIBR

oracle 23855 23730 0 12:24 ? 00:00:00 FNDLIBR

oracle 23860 23730 0 12:24 ? 00:00:00 FNDLIBR

oracle 24404 20422 0 12:28 pts/0 00:00:00 grep FNDLIBR

[oracle@r0469 ~]$ ps -ef | grep FNDLIBR |wc -l

Hear Application Up & Running....

count concurrent_users in Oracle apps:-

SQL> select count(distinct d.user_name) from apps.fnd_logins a,

v$session b, v$process c, apps.fnd_user d

where b.paddr = c.addr

and a.pid=c.pid

and a.spid = b.process

and d.user_id = a.user_id

and (d.user_name = 'USER_NAME' OR 1=1);

COUNT(DISTINCTD.USER_NAME)

--------------------------

This will give the number of users on the system in the past 1 hour:-

SQL> select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1/24 and user_id != '-1';

users

----------

This will give the number of users on the system in the past 1 day:-

SQL> select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1 and user_id != '-1';

users

----------

This will show the activity in the last 15 minutes:-

SQL> select limit_time, limit_connects, to_char(last_connect, 'DD-MON-RR HH:MI:SS') "Last Connection time", user_id, disabled_flag from icx_sessions where last_connect > sysdate - 1/96;

LIMIT_TIME LIMIT_CONNECTS Last Connection time USER_ID D

---------- -------------- --------------------------- ---------- -

999 9000 19-SEP-12 02:25:37 1013630 N

999 9000 19-SEP-12 02:12:50 1013691 Y

999 9000 19-SEP-12 02:12:58 6 N

999 9000 19-SEP-12 02:25:23 1013555 N

Total size of Database:-

SQL> SELECT ROUND(SUM(bytes/1024/1024/1024)) FROM dba_data_files;

ROUND(SUM(BYTES/1024/1024/1024))

--------------------------------

184

Free size of database:-

SQL> SELECT /*+ FIRST_ROWS*/ROUND(SUM(bytes/1024/1024/1024)) FROM dba_free_space;

ROUND(SUM(BYTES/1024/1024/1024))

--------------------------------

All Invalid objects in database:-

SQL> SELECT COUNT(*) FROM all_objects WHERE Status = 'INVALID';

COUNT(*)

----------

641

Total Blocking Session:-

SQL> SELECT count(*) FROM v$lock WHERE BLOCK='1';

COUNT(*)

----------

Total Locked objects:-

SQL> SELECT COUNT(*) FROM v$locked_object;

COUNT(*)

----------

Total no of session :-

SQL> SELECT count(*) FROM v$session;

COUNT(*)

----------

105

Total no of concurrent Process:-

SQL> SELECT COUNT(*) FROM apps.fnd_concurrent_processes;

COUNT(*)

----------

672

Total no of concurrent request:-

SQL> SELECT COUNT(*) FROM apps.fnd_concurrent_requests;

COUNT(*)

----------

21681

Running Concurrent Requests and scheduled Concurrent Requests:-

SQL> SELECT phase_code,COUNT(*) FROM apps.fnd_concurrent_requests WHERE phase_code IN('P','R') GROUP BY phase_code ORDER BY 1 DESC;

P COUNT(*)

- ----------

P 4

Last day total no. of concurrent requests:-

SQL> SELECT sysdate -1 , count(*) FROM apps.fnd_concurrent_requests WHERE to_char(actual_completion_date,'YYYYMMDD') =(SELECT to_char(sysdate -1,'YYYYMMDD') FROM dual);

SYSDATE-1 COUNT(*)

------------------ ----------

18-SEP-12 218

Database Size:-

SQL> select tablespace_name,sum(bytes/1024/1024) from dba_data_files group by tablespace_name order by 1;

TABLESPACE_NAME SUM(BYTES/1024/1024)

------------------------------ --------------------

APPS_CALCLIP 4770

APPS_OMO 1150

APPS_TS_ARCHIVE 1101

APPS_TS_DISCO 856

APPS_TS_DISCO_OLAP 1350

APPS_TS_INTERFACE 2404

APPS_TS_MEDIA 5211.25

APPS_TS_NOLOGGING 575

APPS_TS_QUEUES 4299

APPS_TS_SEED 3803

APPS_TS_SUMMARY 22369

TABLESPACE_NAME SUM(BYTES/1024/1024)

------------------------------ --------------------

APPS_TS_TX_DATA 62692.25

APPS_TS_TX_IDX 32471

B2B_DT 68

B2B_IDX 16

B2B_LOB 12

B2B_RT 42

BAM 8

BIA_RTL 1000

CTXSYS 79

CUST 20

CWMLITE 23

TABLESPACE_NAME SUM(BYTES/1024/1024)

------------------------------ --------------------

DATA 7200

DCM 198

DEMANTRA 5400

DISCO_PTM5_CACHE 108

DISCO_PTM5_META 3

DSGATEWAY_TAB 7

DW_AGGREGATE_IDX 100

DW_AGGREGATE_TBS 100

DW_BASE_IDX 100

DW_BASE_TBS 100

DW_DERIVED_IDX 100

TABLESPACE_NAME SUM(BYTES/1024/1024)

------------------------------ --------------------

DW_DERIVED_TBS 100

DW_DM_TBS 100

DW_LOOKUP_TBS 100

DW_MVLOG_TBS 100

DW_REFERENCE_IDX 100

DW_REFERENCE_TBS 133.9375

GEOR_TBS 2136

HTMLDB 287

IAS_META 221

MTR 4

OCATS 3

TABLESPACE_NAME SUM(BYTES/1024/1024)

------------------------------ --------------------

ODM_DATA 40

OLAP_BAAD 1500

OLTS_ATTRSTORE 15

OLTS_BATTRSTORE 15

OLTS_CT_STORE 8

OLTS_DEFAULT 3

OLTS_SVRMGSTORE 3

OPMOR 6

ORABPEL 12

OWAPUB 10

PERFSTAT_TS 500

TABLESPACE_NAME SUM(BYTES/1024/1024)

------------------------------ --------------------

RBS_MIG 43

RE 8

SYNCSERVER 54

SYSAUX 1327

SYSTEM 19297

TS_DP 50

TS_SALES_DATA 50

TS_SALES_DATA_ENGINE 50

TS_SALES_DATA_ENGINE_X 50

TS_SALES_DATA_X 50

TS_SIM 50

TABLESPACE_NAME SUM(BYTES/1024/1024)

------------------------------ --------------------

TS_SIM_X 50

UDDISYS_TS 21

UNDO_TBS 3686.40625

USERS 10

WCRSYS_TS 3

XDB 61

72 rows selected.

Database free Size:-

SQL> select tablespace_name,sum(bytes/1024/1024) from dba_free_space group by tablespace_name order by 1;

TABLESPACE_NAME SUM(BYTES/1024/1024)

------------------------------ --------------------

APPS_CALCLIP 1543.75

APPS_OMO 511.875

APPS_TS_ARCHIVE 197.375

APPS_TS_DISCO 86.125

APPS_TS_DISCO_OLAP 1333.875

APPS_TS_INTERFACE 220.125

APPS_TS_MEDIA 495.75

APPS_TS_NOLOGGING 285.25

APPS_TS_QUEUES 610

APPS_TS_SEED 420.75

APPS_TS_SUMMARY 2208.75

TABLESPACE_NAME SUM(BYTES/1024/1024)

------------------------------ --------------------

APPS_TS_TX_DATA 6836

APPS_TS_TX_IDX 3728.875

B2B_DT 7.125

B2B_IDX 1.6875

B2B_LOB 1.3125

B2B_RT 4

BAM 1.6875

BIA_RTL 999.9375

CTXSYS 29.5

CUST 19

CWMLITE 1.75

TABLESPACE_NAME SUM(BYTES/1024/1024)

------------------------------ --------------------

DATA 7199.6875

DCM 19.75

DEMANTRA 5399.75

DISCO_PTM5_CACHE 10.5

DISCO_PTM5_META 1.0625

DSGATEWAY_TAB 1.5

DW_AGGREGATE_IDX 99.9375

DW_AGGREGATE_TBS 99.9375

DW_BASE_IDX 99.9375

DW_BASE_TBS 99.9375

DW_DERIVED_IDX 99.9375

TABLESPACE_NAME SUM(BYTES/1024/1024)

------------------------------ --------------------

DW_DERIVED_TBS 99.9375

DW_DM_TBS 99.9375

DW_LOOKUP_TBS 99.9375

DW_MVLOG_TBS 99.9375

DW_REFERENCE_IDX 99.9375

DW_REFERENCE_TBS 133.875

GEOR_TBS 214.25

HTMLDB 28.625

IAS_META 21.875

MTR 2.3125

OCATS 1.25

TABLESPACE_NAME SUM(BYTES/1024/1024)

------------------------------ --------------------

ODM_DATA 9.5625

OLAP_BAAD 1499.8125

OLTS_ATTRSTORE 14.6875

OLTS_BATTRSTORE 14.6875

OLTS_CT_STORE .8125

OLTS_DEFAULT 1.4375

OLTS_SVRMGSTORE .8125

OPMOR 1.375

ORABPEL .5

OWAPUB 9.9375

PERFSTAT_TS 347.25

TABLESPACE_NAME SUM(BYTES/1024/1024)

------------------------------ --------------------

RBS_MIG 3.9375

RE 1.4375

SYNCSERVER 5.2265625

SYSAUX 80.25

SYSTEM 1862.41406

TS_DP 49.9375

TS_SALES_DATA 49.9375

TS_SALES_DATA_ENGINE 49.9375

TS_SALES_DATA_ENGINE_X 49.9375

TS_SALES_DATA_X 49.9375

TS_SIM 49.9375

TABLESPACE_NAME SUM(BYTES/1024/1024)

------------------------------ --------------------

TS_SIM_X 49.9375

UDDISYS_TS 1.875

UNDO_TBS 1176.25

USERS 1.9375

WCRSYS_TS 1.3125

XDB 6.1875

72 rows selected.

O/S level observation

1.Average Cpu Utilization (Percentage):-

[oracle@r0469 ~]$ top

top - 14:54:01 up 19 days, 2:40, 2 users, load average: 0.13, 0.09, 0.09

Tasks: 277 total, 1 running, 275 sleeping, 1 stopped, 0 zombie

Cpu(s): 9.6%us, 1.5%sy, 0.0%ni, 88.2%id, 0.5%wa, 0.0%hi, 0.2%si, 0.0%st

Mem: 4081424k total, 3907568k used, 173856k free, 39372k buffers

Swap: 8193140k total, 33848k used, 8159292k free, 2351040k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

24483 oracle 15 0 111m 66m 16m S 15.3 1.7 2:03.81 frmweb

23176 oracle 16 0 45524 7720 4920 S 3.3 0.2 0:30.07 httpd

24487 oracle 15 0 1194m 488m 476m S 1.7 12.3 1:18.22 oracle

26519 oracle 15 0 2360 1164 800 R 0.7 0.0 0:00.27 top

23850 oracle 17 0 1159m 90m 86m S 0.3 2.3 0:02.34 oracle

24080 oracle 17 0 1158m 54m 51m S 0.3 1.4 0:01.02 oracle

2. No of apps form process on os level:-

[oracle@r0469 ~]$ ps -ef | grep f60webmx

oracle 26535 26486 0 14:54 pts/0 00:00:00 grep f60webmx

[oracle@r0469 ~]$ ps -ef | grep frmweb

oracle 24483 23292 1 12:30 ? 00:02:05 /oracle/VIS/apps/tech_st/10.1.2/bin/frmweb server webfile=HTTP-0,0,1,default,172.16.3.4

oracle 26371 23292 0 14:43 ? 00:00:04 /oracle/VIS/apps/tech_st/10.1.2/bin/frmweb server webfile=HTTP-0,0,1,default,172.16.3.19

oracle 26537 26486 0 14:54 pts/0 00:00:00 grep frmweb

3. Size of Archive-logs (in GB):-

du -sh /apps/archive

4. Alert log Checking:-

vi alert log file and check last one hour error.

5. Total size and free size on apps mount point (All Servers):-

[oracle@r0469 ~]$ df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda5 434G 358G 54G 87% /

/dev/sda2 9.5G 151M 8.9G 2% /tmp

/dev/sda1 965M 23M 893M 3% /boot

tmpfs 2.0G 3.0M 2.0G 1% /dev/shm

Sujeet APPS DBA

R12/11i APPS Command

No comments:

ORA-00059: Maximum Number Of DB_FILES Exceeded in 19C database