ORA-06512: at "APPS.FND_CP_FNDSM"

 After clone Concurrent manager shut down after 3/4 hours.

Internal concurrent manager log found below error

logfile=/apps/UAT/inst/apps/UAT_ora/logs/appl/conc/log/UAT_101.mgr

Cause: insert_fcp failed due to ORA-01403: no data found

ORA-06512: at "APPS.FND_CP_FNDSM", line 149

ORA-06512: at line 1.


Solution :-

stop Concurrent Managers using adcmctl.sh

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

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

[applmgr@oracle scripts]$ sqlplus apps/*****

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

 CONCURRENT_QUEUE_NAME

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

FNDSM_ORACLE

 

SQL> set lines 200

SQL> column CONTROL_CODE format A15

 SQL> select CONCURRENT_QUEUE_NAME, CONTROL_CODE , TARGET_NODE, NODE_NAME   from FND_CONCURRENT_QUEUES where concurrent_queue_name like 'OAMGCS_%';

 

CONCURRENT_QUEUE_NAME          CONTROL_CODE    TARGET_NODE   NODE_NAME

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

OAMGCS_ORACLE                                                E                            NULL                   ORACLE

 

 The standard codes used by Oracle Applications are as followings:


'A', -> 'Activate concurrent manager'
'D', -> 'Deactivate concurrent manager'
'E', -> 'Deactivated'
'N', -> 'Target node/queue unavailable'
'R', -> 'Restart concurrent manager'
'T', -> 'Terminate requests and deactivate manager'
'U', -> 'Update concurrent manager env inf.'
'V', -> 'Verify concurrent managers status'
'X', -> 'Terminated'



STATUS_CODE Column:

A Waiting
B Resuming
C Normal
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting

PHASE_CODE column

C Completed
I Inactive
P Pending
R Running


SQL> update FND_CONCURRENT_QUEUES set control_code = null where concurrent_queue_name = 'OAMGCS_ORACLE';

 1 row updated.

 SQL> commit;

 Commit complete.

 SQL> update FND_CONCURRENT_QUEUES set TARGET_NODE='ORACLE' where CONCURRENT_QUEUE_NAME='OAMGCS_ORACLE';

 1 row updated.

 SQL> commit;

 Commit complete.

 SQL> set lines 200

SQL> column CONTROL_CODE format A15

 SQL> select CONCURRENT_QUEUE_NAME, CONTROL_CODE , TARGET_NODE, NODE_NAME   from FND_CONCURRENT_QUEUES where concurrent_queue_name like 'OAMGCS_%';

 CONCURRENT_QUEUE_NAME----CONTROL_CODE----TARGET_NODE----NODE_NAME

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

OAMGCS_ORACLE---null-- ORACLE-------- ORACLE

 

start Concurrent Managers using adcmctl.sh

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

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

SYSTEM Tablespace is Growing Abnormally in 12.2 Oracle database.

 My SYSTEM tablespace was growing rapidly. This was happening in my test environment. We observe that we were not doing much on this database.

Database version is 12cR2

Possibly there are few reasons for the same.

1)    Some has assigned default tablespace as SYSTEM
2)    Auditing is ON and consuming more space.
3)    SYS_LOB Objects uses lot of disk space
4)    Data dictionary objects uses lot of disk space

So now, how to find the root cause and what could be the solution?

TABLESPACE Status :- 




Let’s query to dba_segments table to find the root cause. I came across few scenarios in my different databases. Here I am discussing those scenarios, what could be the problem and solution of it.

Use below query to find out what are those segments which are consuming lot of space in SYSTEM tablespace.

Connect with sysdba.
[oracle@ora ~]$ sqlplus sys/sys@2023@PDB as sysdba

SQL :- select owner,segment_name,segment_type
 ,bytes/(1024*1024) size_mb
 from dba_segments
 where tablespace_name = 'SYSTEM'
 order by size_mb desc;






Here in above database my AUD$ table is growing rapidly and it is almost 30gb in size.

Problem
Auditing is enabled hence AUD$ table is growing very fast. Organization wants to do auditing and there is no option to disable it.

Solution
Few solutions which Oracle always recommended,
 ‘-  Move AUD$ to different tablespace
‘- Purge audit data
‘- Delete/truncate older data

Connect with sysdba.

[oracle@ora ~]$ sqlplus sys/sys@2023@PDB as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 13 06:52:45 2020

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

Connected to:

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

 

SQL> CREATE TABLE backup_AUD_13feb20 AS (SELECT * from AUD$);

 

Table created.

 

SQL> truncate table AUD$;

 

Table truncated.

 

SQL> commit;

 

Commit complete.


Recheck SYSTEM Tablespace status.









Maintenance Mode in Oracle EBS R12.1.3

 

How to Enable/DISABLE Maintenance Mode in Oracle EBS R12.1.3

Login with Application OS user.

Conn  with apps.

$ sqlplus apps/****

$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE 

$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE

sending the parameter 'ENABLE' or 'DISABLE'


How to verify Enable/DISABLE Maintenance Mode

 $ sqlplus apps/****

SQL> select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual; 

     FND_PROFILE.VALUE('APPS_MAINTENANCE_MODE')

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

     NORMAL

The above query will return MAINT (maintenance mode is ENABLE) 

The above query will return NORMAL (maintenance mode is DISABLE) 

How to Kill Zombie Processes on Linux

###How to Kill Zombie Processes on Linux

 Defunct processes may also be known as "zombie" processes. They do not use any system resources - CPU, memory etc. but may be seen as entries in their respective operating system process table. 

 A zombie is already dead, so you cannot kill it. To clean up a zombie, it must be waited on by its parent, so killing the parent should work to eliminate the zombie. (After the parent dies, the zombie will be inherited by pid 1, which will wait on it and clear its entry in the process table.) If your daemon is spawning children that become zombies, you have a bug. Your daemon should notice when its children die and wait on them to determine their exit status. 


 ##What Causes Zombie Processes on Linux?

 A poorly written parent process might not call the wait() function when the child process is created. This means nothing is watching for state changes in the child process, and the SIGCHLD signal will be ignored. Or, perhaps another application is affecting the execution of the parent process, either due to poor programming or malicious intent.


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

oracle 175489580 0 11:33 pts/0 00:00:00 grep --color=auto zombie

ORA-00020: No more process state objects available

 How to increase processes, sessions, and transactions parameters in Oracle 12R1.

Whenever DB processes are utilized 100% then it’s NOT allowed any new sessions/connections to DB’s instances and we get the below error:

ORA-00020: No more process state objects available

ORA-20 errors will not be written to the alert log for

If we are planning to increase “PROCESSES” parameter so, we  should also plan to increase the “SESSIONS” and “TRANSACTIONS” parameters. 

A basic formula for determining  these parameter values is as follows:

 

        processes=x

        sessions=x*1.1+5

        transactions=sessions*1.1


Cause: All process state objects are in use.

Action: Increase the value of the PROCESSES initialization parameter.

Before starting on the solution, we understand what is processes, sessions & transactions:

PROCESSES: It specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, 

job queue processes, and parallel execution processes.

The default values of the SESSIONS and TRANSACTIONS parameters are derived from the PROCESSES parameter. Therefore, if we change the value of PROCESSES, 

We should evaluate whether to adjust the values of those derived parameters.


SESSIONS: It specifies the maximum number of sessions that can be created in the system. Because every login requires a session, this parameter effectively determines the maximum number of concurrent users in the system. 

We should always set this parameter explicitly to a value equivalent to our estimate of the maximum number of concurrent users, plus the number of background processes, plus approximately 10% for recursive sessions. Oracle uses the default value of this parameter as its minimum. Values between 1 and the default do not trigger errors, but Oracle ignores them and uses the default instead.

TRANSACTIONS: It specifies the maximum number of concurrent transactions. Greater values increase the size of the SGA and can increase the number of rollback segments allocated. 

The default value is greater than SESSIONS (and, in turn, PROCESSES) to allow for recursive transactions.


Solution:-

[oradb@oracle ~]$ sqlplus / as sysdba

SQL> show parameter processes

NAME                                 TYPE        VALUE

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

aq_tm_processes                      integer     1

db_writer_processes                  integer     1

gcs_server_processes                 integer     0

global_txn_processes                 integer     1

job_queue_processes                  integer     2

log_archive_max_processes            integer     4

processes                            integer     400

SQL> show parameter sessions

NAME                                 TYPE        VALUE

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

java_max_sessionspace_size           integer     0

java_soft_sessionspace_limit         integer     0

license_max_sessions                 integer     0

license_sessions_warning             integer     0

sessions                             integer     800

shared_server_sessions               integer

SQL> show parameter transactions

NAME                                 TYPE        VALUE

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

transactions                         integer     880

transactions_per_rollback_segment    integer     5

SQL>


If we are planning to increase “PROCESSES” parameter so, we  should also plan to increase the “SESSIONS” and “TRANSACTIONS” parameters. A basic formula for determining  these parameter values is as follows:

          processes=x

        sessions=x*1.1+5

        transactions=sessions*1.1

Before altering parameters we need to take a backup of pfile.

SQL> create pfile from spfile;

SQL> alter system set processes=500 scope=spfile;

SQL> alter system set sessions=555 scope=spfile;

SQL> alter system set transactions=610 scope=spfile;

After resetting the parameters we need to bounce the database-

SQL>shutdown immediate;

SQL>startup;

After that will check the settings of parameters-

SQL> show parameter processes;

SQL> show parameter session;

SQL> show parameter transactions;



ORA-39087: directory name DPUMP_DIR is invalid

 This error may be returned if you are trying to export or import using oracle data pump, 

but invalid directory name has been provided during this process. 

Following is an example of this.

Error:

[oracle@hostname ~]$ expdp system/manager DIRECTORY=dpump_dir DUMPFILE=system.dmp logfile=system.log schemas=system

Export: Release 11.2.0.3.0 - Production on Wed Aug 2022 10:07:55 

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-39087: directory name DPUMP_DIR is invalid

Solution:

Check the directory is exists or not, If not exist then create the directory with proper path: 

If the directory exists then check grant on the directory, if the grant missing then provide grant 

SQL> CREATE OR REPLACE DIRECTORY DPUMP_DIR AS '/u01/dpump'

NOTE :

If SYS or SYSTEM user will be used for export/import, granting rights on above directory are not needed, 

otherwise grant rights to the user performing export/import

SQL> GRANT READ, WRITE ON DIRECTORY DPUMP_DIR TO SYSTEM;

Now try to take export again. It should work.

Concurrent Program Output Saving as FNDWRR.txt Using Edge Browser


SYMPTOMS

Using Edge browser when concurrent program output is saved the output file name defaulting to FNDWRR.txt.This is happening only for txt format output type files.

Expected Behaviour:

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

When concurrent program output is saved the file name should default with program name.

For ex: For Active User concurrent program the Output should be Active_Users.txt


STEPS

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

The issue can be reproduced at will with the following steps:

1.Using Edge Browser Connect to EBS application

2.Navigate to System administrator responsibility

3.Submit Active User concurrent Program

4.Click On view Output

5.Program output open in edge browser.

6.Save the output using option Ctrl+S

This issue is only for output format of type txt and only in Edge browser. Other browsers like the IE, Chrome the concurrent program outputs were getting saved with expected file naming convention.

CAUSE

Edge browser failed to convert the output file name when using existing viewer options setup for txt output type files.


SOLUTION

To implement the solution test the following setups::

1.Navigate to System Administrator responsibility

2.Install -- Viewer option

3.Add new entry

   File Format :: Text

   mime type :: application/txt

   Description :: Text File

   Select Allow Native encoding checkbox





4.Using System administrator responsibility Navigate to User -- Profile form

5.Update profile 'Viewer: Application for Text' value to Text File.





7.Logout and re-login to EBS application.

8.Retest the issue.


REFERENCES

Meta link id :- (Doc ID 2786457.1)

ora-39087 directory name data_pump_dir is invalid

Error :-

ora-39002 invalid operation

ora-39070 unable to open the log file

ora-39087 directory name DATAPUMP is invalid


Solution :-

create a new directory object (as a SYS user or as a user with CREATE ANY DIRECTORY privilege granted), grant READ and WRITE privileges on that DIRECTORY, and perform data pump export/import tasks.

Make sure that the /u01/EXPDP_IMPDP directory has 660 permission.

create or replace your directory and grant the following privileges' to user(system).


[oracle@prod u01]$ mkdir EXPDP_IMPDP

[oracle@prod u01]$ chmod 660 EXPDP_IMPDP


[oracle@QA u01]$ sqlplus sys/*****@PDB1 as sysdba


SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 7 11:30:58 2022

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

Last Successful login time: Sat Jun 25 2022 14:49:08 -04:00

Connected to:

Oracle Database 12c EE High Perf Release 12.2.0.1.0 - 64bit Production


SQL> create directory EXPDP_IMPDP as '/u01/EXPDP_IMPDP';

Directory created.


SQL> GRANT READ, WRITE ON DIRECTORY EXPDP_IMPDP TO system;

Grant succeeded.


SQL>

How to change Oracle Applications R12.1.3 Port

 How to change Oracle Applications R12.1.3 Port-8002 to 8050

Step:-1

Dowm Application Tier

[applprod@prd-erp01 ~]$ cd $ADMIN_SCRIPTS_HOME

[applprod@prd-erp01 scripts]$ ./adstpall.sh apps/apps

Step:-2

Open your  Context XML file for edit it. 

Go to Context XML path :- $INST_TOP/appl/admin/

Take backup of .XML file :- cp Oracle_prd-erp01.xml Oracle_prd-erp01.xml-bkp10MAY22

Edit Oracle_prd-erp01.xml file with below parameter port value replace from 8002 to 8050.

Change the value from 8002 to be 8050 on the following lines:

      <web_port oa_var="s_webport" oa_type="PORT" base="8000" step="1" range="-1" label="Web Listener Port">8050</web_port>

      <activewebport oa_var="s_active_webport" oa_type="DUP_PORT"  base="8000" step="1" range="-1" label="Active Web Port">8050</activewebport>

      <login_page oa_var="s_login_page">http://<host>.<domain>:8050/OA_HTML/AppsLogin</login_page>

      <chronosURL oa_var="s_chronosURL">http://<host>.<domain>:8050/oracle_smp_chronos/oracle_smp_chronos_sdk.gif</chronosURL> 

      <oa_var="s_endUserMonitoringURL">http://<host>.<domain>:8050/oracle_smp_chronos/oracle_smp_chronos_sdk.gif</EndUserMonitoringURL>

      <externURL oa_var="s_external_url">http://<host>.<domain>:8050</externURL>

      <httplistenparameter oa_var="s_http_listen_parameter">8050</httplistenparameter> 


 Save .XML file>>>>>>

Step:-3

Run Autoconfig on apps_tier for changes to take place.

[applprod@Oracle-prd-erp01 scripts]$ ./adautocfg.sh

Enter the APPS user password: *****

Step:-4

Start Application services.

[applprod@Oracle-prd-erp01 scripts]$ ./adstrtal.sh apps/apps

Step:-5

Verify new URL.

SQL> select HOME_URL from icx_parameters;

HOME_URL

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

http://Oracle-prd-erp01.oracle.net:8050/OA_HTML/AppsLogin




FDPSTP failed due to ORA-20100: Error: FND_FILE failure

I got recently a issue with Concurrent program, All program completed with below error.


**Starts**09-MAY-2022 21:07:39

ORACLE error 20100 in FDPSTP

Cause: FDPSTP failed due to ORA-20100: Error: FND_FILE failure. Unable to create file, o5819484.tmp in the directory, /usr/tmp.

You will find more information in the request log.

ORA-06512: at "APPS.FND_FILE", line 417

ORA-06


Solution :-

change Directory file permssion to 777. so that others can write a file into it.

# chmod 777 /usr/tmp


Retest issue.



ORA-30091: secondary translation table not loaded

 ORA-31693: Table data object "DEV1"."TABLE_CUST" failed to load/unload and is being skipped due to error:

ORA-30091: secondary translation table not loaded

Data Pump Import Reports ORA-31693,ORA-30091: Secondary Translation Table Not Loaded (Doc ID 2736570.1)


SYMPTOMS:

Performing a Data Pump Import the following errors occur:

ORA-31693: Table data object "USER"."TABLE" failed to load/unload and is being skipped due to error:

ORA-30091: secondary translation table not loaded

CAUSE:

The source database has a newer timezone version than the target database:


Target database timezone version:

SQL> select * from v$timezone_file;

FILENAME                VERSION     CON_ID

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

timezlrg_26.dat              26          0


Source database timezone version :-

SQL> select * from v$timezone_file;

FILENAME                VERSION     CON_ID

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

timezlrg_28.dat              28          0


SOLUTION :- 

Install the newer Timezone Version on the Target Database.

SQL> shut immediate

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size                  2926472 bytes

Variable Size             889194616 bytes

Database Buffers         1241513984 bytes

Redo Buffers               13848576 bytes

Database mounted.

Database opened.

SQL> EXEC DBMS_DST.BEGIN_UPGRADE (28);

PL/SQL procedure successfully completed.

SQL> shut immediate

SQL> startup

SQL> SELECT version FROM v$timezone_file;

   VERSION

----------

        28


Retest issue.




ORA-01172: recovery of thread 1 stuck at block 3024 of file 19


ERROR :

 SQL> startup

ORACLE instance started.

Total System Global Area 2.1475E+10 bytes

Fixed Size                 12170960 bytes

Variable Size            3019901232 bytes

Database Buffers         1.8388E+10 bytes

Redo Buffers               54935552 bytes

Database mounted.

ORA-01172: recovery of thread 1 stuck at block 3024 of file 19

ORA-01151: use media recovery to recover block, restore backup if needed

Cause: 

This error usually means that blocks of file 19 are corrupted, In this case we need to recover file 19.

Crash recovery or instance recovery could not apply a change to a block because it was not the next change. 
This can happen if the block was corrupted and then repaired during recovery.

SOLUTION :- 

We need to recover all of corrupted block by using data file recovery or Database recovery. 

In my case I have recover datafile 19.

SQL>  recover datafile 19;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select name,OPEN_MODE from v$database;

NAME      OPEN_MODE

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

TEST    READ WRITE


ORA-19563: header validation failed for file

Encountered while restoring the server on test database.

Restore log file :-

Finished restore at 03-MAR-22

released channel: c1

released channel: c2

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of switch command at 03/03/2022 22:58:55

ORA-19563:  header validation failed for file

RMAN>


CAUSE :-

Check alerts log in target and get the file number/name for which throwing this error .

SQL > SELECT FILE#, NAME FROM V$DATAFILE where  NAME like '%system%';

FILE#           NAME

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

 92            /u01/test/db/apps_st/data/system17.dbf

 139          /u01/test/db/apps_st/data/system17.dbf


Solution :-

Rename the file like (system17.dbf to system18.dbf)

SQL > SELECT FILE#, NAME FROM V$DATAFILE where  NAME like '%system%';

FILE#           NAME

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

 92            /u01/test/db/apps_st/data/system17.dbf

 139          /u01/test/db/apps_st/data/system18.dbf


NOW try to  restore again.

REP-1804: Unable to open printer definition file HPL.prt

REP-0069: Internal error

REP-57054: In-process job terminated:Terminated with error:

REP-1804: Unable to open printer definition file '/apps/apps_st/appl/CUST_TOP/12.0.0/reports/HPL.prt'.


Solution :- 

Verify HPL.prt file location and copy HPL.prt file to ('/apps/apps_st/appl/CUST_TOP/12.0.0/reports/HPL.prt') path.

[applmgr@oracle ]$ locate HPL.prt

/apps/apps_st/appl/fnd/12.0.0/reports/HPL.prt

[applmgr@oracle ]$ cp /apps/apps_st/appl/fnd/12.0.0/reports/HPL.prt  /apps/apps_st/appl/CUST_TOP/12.0.0/reports/


Now retest issue.


ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Used the system rollback segment for non...