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

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

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

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

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

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

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)

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