Sujeet APPS DBA: December 2021

System Hold, Fix Manager before resetting counters

We have seen this error couple of times in the Concurrent manager Administer screen in our R12.1.3 QA instances.

OPP Concurrent Manager status : System Hold, Fix Manager before resetting counters

Solution:

1. Shutdown the Apps Tier cleanly make sure there are no sessions:

[applmgr@oracle ~]$ cd $ADMIN_SCRIPTS_HOME

[applmgr@oracle scripts]$ ./adstpall.sh apps/*****

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

[applmgr@oracle ~]$ ps -ef | grep FNDSM

[applmgr@oracle ~]$ ps -ef | grep FNDCRM

[applmgr@oracle ~]$ ps -ef | grep FNDFS

[applmgr@oracle ~]$ ps -ef | grep applmgr

2. Stop the database.

[ora@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Dec 14 14:28:07 2021

Connected to an idle instance.

SQL> shut immediate

3. Start the database.

[ora@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Dec 14 14:28:07 2021

Connected to an idle instance.

SQL> startup

4. To the appsTier go to $FND_TOP/bin and run the following relinks.

[applmgr@oracle ~]$ cd $FND_TOP/bin

$ adrelink.sh force=y link_debug=y "fnd FNDLIBR"

$ adrelink.sh force=y link_debug=y "fnd FNDFS"

$ adrelink.sh force=y link_debug=y "fnd FNDCRM"

$ adrelink.sh force=y link_debug=y "fnd FNDSM"

5. Run the CMCLEAN.SQL script and commit..

SQL> @cmclean.sql

6. Execute the following SQL:

SQL> select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';

CONCURRENT_QUEUE_NAME

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

FNDSM_ORACLE

SQL>

7. Now start the Apps tier:

[applmgr@oracle ~]$ cd $ADMIN_SCRIPTS_HOME

[applmgr@oracle scripts]$ ./adstrtal.sh apps/*****

Check status of OPP Concurrent manager.

I hope issue got fix.

ORA-06512: at "APPS.FND_CP_GSM_IPC"

Unable to Start “Output Post Processor” Concurrent Manager

After starting concurrent manager I noticed "Output Post Processor" did not start. I tried to start "Output Post Processor" using "Administrator Concurrent Manager" screen but it failed with given error in manager log file

OPP Manager Log

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)

.0002730 secs]

[GC 3338K->1928K(4992K), 0.0002350 secs]

Solution:

Checked and found "Service Manager" was down.

Started "Service Manager" and then started "Output Post Processor".

ORA-04033: Insufficient memory to grow pool

I am trying to expand by shared_pool size and I get the below error.

ERROR:-

SQL> alter system set streams_pool_size =300M scope=both;

alter system set streams_pool_size =300M scope=both

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-04033: Insufficient memory to grow pool

Verify:-

you do not have enough RAM allocated to the SGA to allow you to create a 300M shared pool.

[root@ora ~]# free -g

total used free shared buff/cache available

Mem: 14 9 1 0 3 2

Swap: 15 2 13

[root@ora ~]#

SQL> show parameter sga_max_size;

NAME TYPE VALUE

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

sga_max_size big integer 1G

SQL>

Solution :-

Specify a smaller value to grow the pool.

SQL> alter system set streams_pool_size =200M scope=both;

System altered.

SQL> show parameter streams_pool_size;

NAME TYPE VALUE

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

streams_pool_size big integer 200M

SQL>

Queries to Monitor Expdp Jobs Status

1. How to check the progress of export or import Jobs

SQL > SELECT OWNER_NAME,JOB_NAME,OPERATION,JOB_MODE,STATE from DBA_DATAPUMP_JOBS;

2. Finding the Datapump Job Status from V$SESSION_LONGOPS

SQL > SELECT B.USERNAME, A.SID, B.OPNAME, B.TARGET,

ROUND(B.SOFAR*100/B.TOTALWORK,0) || '%' AS "%DONE", B.TIME_REMAINING,

TO_CHAR(B.START_TIME,'YYYY/MM/DD HH24:MI:SS') START_TIME

FROM V$SESSION_LONGOPS B, V$SESSION A

WHERE A.SID = B.SID

AND B.OPNAME LIKE '%EXPORT%'

ORDER BY 6;

3. To monitor executing jobs using dba_datapump_jobs view:

SQL > SELECT

owner_name,

job_name,

operation,

job_mode,

state

FROM

dba_datapump_jobs

where state='EXECUTING';

4. To get the detail information like SID, Serial#, and % of completion:

SQL > SELECT

OPNAME,

SID,

SERIAL#,

CONTEXT,

SOFAR,

TOTALWORK,

ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"

FROM

V$SESSION_LONGOPS

WHERE

OPNAME in

(

select

d.job_name

from

v$session s,

v$process p,

dba_datapump_sessions d

where

p.addr=s.paddr

and

s.saddr=d.saddr

)

AND

OPNAME NOT LIKE '%aggregate%'

AND

TOTALWORK != 0

AND

SOFAR <> TOTALWORK;

5. To check the waiting status and wait event of the job waiting for:

SQL > SELECT w.sid, w.event, w.seconds_in_wait

FROM V$SESSION s, DBA_DATAPUMP_SESSIONS d, V$SESSION_WAIT w

WHERE s.saddr = d.saddr AND s.sid = w.sid;

SQL > select username,opname,target_desc,sofar,totalwork,message from V$SESSION_LONGOPS;

7. The percentage of work done

SQL > SELECT b.username, a.sid, b.opname, b.target,

round(b.SOFAR*100/b.TOTALWORK,0) || '%' as "%DONE", b.TIME_REMAINING,

to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') start_time

FROM v$session_longops b, v$session a

WHERE a.sid = b.sid ORDER BY 6;

SQL> select COMMAND,STATE,WAIT_CLASS,EVENT,SECONDS_IN_WAIT from v$session where sid=57 and SERIAL#=40478;

To check the orphaned datapump jobs. For orphaned jobs the state will be NOT RUNNING.

SQL> SELECT owner_name, job_name, operation, job_mode,

state, attached_sessions FROM dba_datapump_jobs;

To check the alert log and query the DBA_RESUMABLE view.

SQL > select name, sql_text, error_msg from dba_resumable;

To kill the datapump jobs:

SQL > alter system kill session 'SID,SERIAL#' immediate;

Check running EXPDP status.

$ expdp system/******@CDB attach=SYS_EXPORT_SCHEMA_04

[oracle@ora]$ expdp system/******@CDB attach=SYS_EXPORT_SCHEMA_04

Export: Release 12.2.0.1.0 - Production on Wed Dec 1 14:11:21 2021

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Job: SYS_EXPORT_SCHEMA_04

Owner: SYSTEM

Operation: EXPORT

Creator Privs: TRUE

GUID: ABSCDNMVJBK

Start Time: Wednesday, 01 December, 2021 19:03:20

Mode: SCHEMA

Instance: CDB

Max Parallelism: 0

Timezone: +00:00

Timezone version: 28

Endianness: LITTLE

NLS character set: AL32UTF8

NLS NCHAR character set: AL16UTF16

EXPORT Job Parameters:

Parameter Name Parameter Value:

CLIENT_COMMAND system/********@CDB schemas=SONU directory=SONU dumpfile=SONU.dmp logfile=SONU.log reuse_dumpfiles=true

TRACE 0

State: DEFINING

Bytes Processed: 0

Job Error Count: 0

Job heartbeat: 1

Export> status

Job: SYS_EXPORT_SCHEMA_04

Operation: EXPORT

Mode: SCHEMA

State: DEFINING

Bytes Processed: 0

Job Error Count: 0

Job heartbeat: 1

Export>

================================================================

References:-

http://www.runningoracle.com/product_info.php?products_id=390

DataPump Export/Import Hangs With "DEFINING" Status When Using A Directory On NFS Filesystem (Doc ID 2262196.1)

Data Pump Hanging When Exporting To NFS Location (Doc ID 434508.1)

=====================================================

System Hold, Fix Manager before resetting counters

ORA-06512: at "APPS.FND_CP_GSM_IPC"

ORA-04033: Insufficient memory to grow pool

Queries to Monitor Expdp Jobs Status

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