Sujeet APPS DBA: November 2021

How to Check the table Size in Oracle.

To check table name segment type and table size in MB

SQL> select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where segment_type='TABLE' and segment_name='sujeet'

SEGMENT_NAME SEGMENT_TYPE MB

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

sujeet TABLE 192

To check Table Owner,Table Name and Table Size.

You can check here one SYSTEM owner have multiple tables with different sizes and names.

SQL> select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SYSTEM' and segment_type='TABLE' group by owner,segment_name order by "SIZE in GB" desc;

OWNER SEGMENT_NAME SIZE in GB

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

SYSTEM SONU 1.50683594

SYSTEM Aasu .984375

SYSTEM BIHAR .921875

SQL > select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments

where owner='SCOT' and segment_type='TABLE' group by owner,segment_name order by "SIZE in GB" desc;

SQL > select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments

where owner='SCOT' and segment_type='LOBSEGMENT' group by owner,segment_name order by "SIZE in GB" desc;

ORA-01000: maximum open cursors exceeded

Error in application log file.

<PRE>SQL_PLSQL_ERROR (ERRNO=604) (REASON=java.sql.SQLException: ORA-00604: error occurred at recursive

SQL level 1 ORA-01000: maximum open cursors exceeded ORA-00604:

error occurred at recursive SQL level 1 ORA-01000: maximum open cursors exceeded ORA-01000: maximum open cursors

exceeded ORA-06512: at "APPS.FND_SESSION_UTILITIES", line 37 ORA-06512: at line 1

) (ROUTINE=decryptSessionCookie(String)) ICX_SESSION_FAILED </PRE>

Servlet error: An exception occurred. The current application deployment descriptors do not allow for

including it in this response. Please consult the application log for details.

Solution

To troubleshoot the open cursors issue:

Find out the session that is causing the error by using the following SQL statement:

SQL> SELECT max(a.value) as highest_open_cur, p.value as max_open_cur FROM v$sesstat a, v$statname b, v$parameter p WHERE a.statistic# = b.statistic# and b.name = 'opened cursors current' and p.name= 'open_cursors' group by p.value;

HIGHEST_OPEN_CUR---------------- MAX_OPEN_CUR

600 600

SQL> SELECT value FROM v$parameter WHERE name = 'open_cursors';

VALUE

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

600

Increase open_cursors value 600 to 1000.

$ sqlplus / as sysdba

SQL> alter system set open_cursors = 1000 scope=both;

System altered.

SQL> commit;

Commit complete.

SQL> SELECT value FROM v$parameter WHERE name = 'open_cursors';

VALUE

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

1000

SQL> create pfile from spfile;

File created.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup

ORACLE instance started.

Total System Global Area 7482626048 bytes

Fixed Size 2160752 bytes

Variable Size 1677723536 bytes

Database Buffers 5771362304 bytes

Redo Buffers 31379456 bytes

Database mounted.

Database opened.

SQL> SELECT value FROM v$parameter WHERE name = 'open_cursors';

VALUE

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

1000

Find which SQL is using more cursors

SQL> select a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current' and s.username is not null;

VALUE USERNAME SID SERIAL#

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

47 APPS 358 134

63 APPS 365 160

88 APPS 453 12

51 APPS 459 17

HIGHEST_OPEN_CUR----------------MAX_OPEN_CUR

102 1000

Alert and Listener log path in Oracle 12C database

Alert log text file path in 12C

1. Find diag location

SQL> SELECT name,VALUE FROM V$DIAG_INFO where name = 'Diag Alert';

NAME VALUE

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

Diag Alert /u01/app/oracle/diag/rdbms/host_SID/SID/alert

2. For text file move to trace folder instead of alert folder.

/u01/app/oracle/diag\rdbms\xe\xe\trace

SQL> Show parameter background_dump_dest

NAME TYPE VALUE

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

background_dump_dest string /u01/app/oracle\DBHOMEXE\RDBMS\TRACE

Listener log path

1. Find the diag parameter location

SQL> show parameter diag

NAME TYPE VALUE

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

diagnostic_dest string /u01/app/oracle

2. For text file:

$DIAG_LOCATION /u01/app/oracle\listener\trace\listener.log

3. For XML File:

$DIAG_LOCATION /u01/app/oracle\listener\alert\log.xml

You get the listener log location with lsnrctl status command

$LSNRCTL status

How to Enable Database Management for Oracle Cloud Databases

Please follow below link for Enable Database Management for Oracle Cloud Databases

https://database-heartbeat.com/2021/09/13/enable-db-mgmt/

https://docs.oracle.com/en-us/iaas/database-management/doc/permissions-required-database-management.html#DBMGM-

https://docs.oracle.com/en-us/iaas/database-management/doc/perform-database-management-prerequisite-tasks.html#GUID-BF854DE9-2EB9-4333-B3BF-C164F16A2A1B

https://docs.oracle.com/en-us/iaas/database-management/doc/perform-database-management-prerequisite-tasks.html#DBMGM-GUID-C0C28FF3-12E3-4D45-9ED5-B06B2EF52978

https://docs.oracle.com/en-us/iaas/Content/API/Concepts/cloudshellgettingstarted.htm

Output Post Processor Down : Actual 0 Target 3

“Output Post Processor” Concurrent Manager not able to start

OPP manager log file path :- /u01/apps/inst/apps/QA_QA01/logs/appl/conc/log

[qa1@QA01 log]$ ls -lrt |grep FNDOPP

Error in OPP log file.

[qa1@QA01 log]$ cat FNDOPP2127409.txt

Unable to initialize state monitor.

oracle.apps.fnd.cp.gsm.GenCartCommException: ORA-01422: exact fetch returns more than requested number of rows

ORA-06512: at "APPS.FND_CP_GSM_IPC", line 539

ORA-06512: at line 1

at oracle.apps.fnd.cp.gsm.GenCartComm.initService(GenCartComm.java:233)

at oracle.apps.fnd.cp.gsm.GenCartComm.<init>(GenCartComm.java:80)

at oracle.apps.fnd.cp.gsf.GSMStateMonitor.init(GSMStateMonitor.java:74)

at oracle.apps.fnd.cp.gsf.GSMStateMonitor.<init>(GSMStateMonitor.java:62)

at oracle.apps.fnd.cp.gsf.GSMServiceController.init(GSMServiceController.java:111)

at oracle.apps.fnd.cp.gsf.GSMServiceController.<init>(GSMServiceController.java:66)

at oracle.apps.fnd.cp.gsf.GSMServiceController.main(GSMServiceController.java:428)

.0002460 secs]

SOLUTION

1. Shutdown the internal manager by using adcmctl.sh stop apps/****
2. Make sure there is no FNDLIBR processe running:
$ ps -ef| grep FNDLIBR
3. If there is any FNDLIBR processe please kill it $ kill -9 pid
4. Run cmclean.sql script as document from Note 134007.1
5. Restart the internal manager by using adcmctl.sh start apps/*****

Retest issue. I hope issue has been resolved.

For performance issue please increase java heap size

Maximum Memory Usage Per Process:

The maximum amount of memory or maximum Java heap size a single OPP process can use is by default set to 512MB.

This value is seeded by the Loader Data File: $FND_TOP/patch/115/import/US/afoppsrv.ldt which specifies that the

DEVELOPER_PARAMETERS is “J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx512m”.

How to determine the current maximum Java heap size:

SELECT service_id, service_handle, developer_parameters

FROM fnd_cp_services

WHERE service_id = (SELECT manager_type

FROM fnd_concurrent_queues

WHERE concurrent_queue_name = ‘FNDCPOPP’);SERVICE_ID SERVICE_HANDLE DEVELOPER_PARAMETERS

———- ————– ——————————————————–

1091 FNDOPP J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx512m

Increase the maximum Java heap size for the OPP to 1024MB (1GB):

UPDATE fnd_cp_services

SET developer_parameters =

‘J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m’

WHERE service_id = (SELECT manager_type

FROM fnd_concurrent_queues

WHERE concurrent_queue_name = ‘FNDCPOPP’);

The OPP queue can be Recreated the using $FND_TOP/patch/115/sql/afopp002.sql file as ‘APPLSYS’ user.

OPP Running process status.

SQL> select sid,serial#,status,logon_time,module from v$session where module like'%OPP%';

SID SERIAL# STATUS LOGON_TIM MODULE
--- ------- ------- --------- ------
5769 31 ACTIVE 14-APR-15 FNDCPOPP

5773 10 ACTIVE 14-APR-15 FNDCPOPP

5780 4 ACTIVE 14-APR-15 FNDCPOPP

Check from front end.

Actual and Target processes should both be 3.