Oracle Apps R12 - Concurrent Program terminated by signal 25

Concurrent Program terminated by signal 25

Issue
Concurrent program getting errored with emsg:was terminated by signal 25

Solution
Concurrent program accessing reports.log in $APPLCSF/$APPLLOG will get above error if reports.log file size is more than 2 GB.

cd $APPLCSF/$APPLLOG
mv reports.log reports.log_bkup
touch reports.log

This File Size limit of 2 GB applies to any log/out file generated by EBS process. Any log/out file limit of 2 GB has been reached then corresponding process/program will get error out. This applicable for DB/Apps Listener as well.

Error: Signal 25 Concurrent Program Completes with Error (Metalink Doc ID 842850.1)

Oracle Apps R12 - Concurrent manager is consuming high usage

Concurrent manager is consuming high usage

Query

SELECT R.Conc_Login_Id, R.Request_Id, R.Phase_Code, R.Status_Code, P.Application_ID, P.Concurrent_Program_ID, P.Concurrent_Program_Name, R.Enable_Trace, R.Restart, DECODE(R.Increment_Dates, 'Y', 'Y', 'N'), R.NLS_Compliant, R.OUTPUT_FILE_TYPE, E.Executable_Name, E.Execution_File_Name, A2.Basepath, DECODE(R.Stale, 'Y', 'C', P.Execution_Method_Code), P.Print_Flag, P.Execution_Options, DECODE(P.Srs_Flag, 'Y', 'Y', 'Q', 'Y', 'N'), P.Argument_Method_Code, R.Print_Style, R.Argument_Input_Method_Code, R.Queue_Method_Code, R.Responsibility_ID, R.Responsibility_Application_ID, R.Requested_By, R.Number_Of_Copies, R.Save_Output_Flag, R.Printer, R.Print_Group, R.Priority, U.User_Name, O.Oracle_Username, O.Encrypted_Oracle_Password, R.Cd_Id, A.Basepath, A.Application_Short_Name, TO_CHAR(R.Requested_Start_Date,'YYYY/MM/DD HH24:MI:SS'), R.Nls_Language, R.Nls_Territory, R.Nls_Numeric_Characters, DECODE(R.Parent_Request_ID, NULL, 0, R.Parent_Request_ID), R.Priority_Request_ID, R.Single_Thread_Flag, R.Has_Sub_Request, R.Is_Sub_Request, R.Req_Information, R.Description, R.Resubmit_Time, TO_CHAR(R.Resubmit_Interval), R.Resubmit_Interval_Type_Code, R.Resubmit_Interval_Unit_Code, TO_CHAR(R.Resubmit_End_Date,'YYYY/MM/DD HH24:MI:SS'), Decode(E.Execution_File_Name, NULL, 'N', Decode(E.Subroutine_Name, NULL, Decode(E.Execution_Method_Code, 'I', 'Y', 'J', 'Y', 'N'), 'Y')), R.Argument1, R.Argument2, R.Argument3, R.Argument4, R.Argument5, R.Argument6, R.Argument7, R.Argument8, R.Argument9, R.Argument10, R.Argument11, R.Argument12, R.Argument13, R.Argument14, R.Argument15, R.Argument16, R.Argument17, R.Argument18, R.Argument19, R.Argument20, R.Argument21, R.Argument22, R.Argument23, R.Argument24, R.Argument25, X.Argument26, X.Argument27, X.Argument28, X.Argument29, X.Argument30, X.Argument31, X.Argument32, X.Argument33, X.Argument34, X.Argument35, X.Argument36, X.Argument37, X.Argument38, X.Argument39, X.Argument40, X.Argument41, X.Argument42, X.Argument43, X.Argument44, X.Argument45, X.Argument46, X.Argument47, X.Argument48, X.Argument49, X.Argument50, X.Argument51, X.Argument52, X.Argument53, X.Argument54, X.Argument55, X.Argument56, X.Argument57, X.Argument58, X.Argument59, X.Argument60, X.Argument61, X.Argument62, X.Argument63, X.Argument64, X.Argument65, X.Argument66, X.Argument67, X.Argument68, X.Argument69, X.Argument70, X.Argument71, X.Argument72, X.Argument73, X.Argument74, X.Argument75, X.Argument76, X.Argument77, X.Argument78, X.Argument79, X.Argument80, X.Argument81, X.Argument82, X.Argument83, X.Argument84, X.Argument85, X.Argument86, X.Argument87, X.Argument88, X.Argument89, X.Argument90, X.Argument91, X.Argument92, X.Argument93, X.Argument94, X.Argument95, X.Argument96, X.Argument97, X.Argument98, X.Argument99, X.Argument100, R.number_of_arguments, C.CD_Name, NVL(R.Security_Group_ID, 0), NVL(R.org_id, 0)
FROM fnd_concurrent_requests R, fnd_concurrent_programs P, fnd_application A, fnd_user U, fnd_oracle_userid O, fnd_conflicts_domain C, fnd_concurrent_queues Q, fnd_application A2, fnd_executables E, fnd_conc_request_arguments X
WHERE R.Status_code = 'I' And ((R.OPS_INSTANCE is null) or (R.OPS_INSTANCE = -1) or (R.OPS_INSTANCE = decode(:dcp_on,1,FND_CONC_GLOBAL.OPS_INST_NUM,R.OPS_INSTANCE))) And R.Request_ID = X.Request_ID(+) And R.Program_Application_Id = P.Application_Id(+) And R.Concurrent_Program_Id = P.Concurrent_Program_Id(+) And R.Program_Application_Id = A.Application_Id(+) And P.Executable_Application_Id = E.Application_Id(+) And P.Executable_Id = E.Executable_Id(+) And P.Executable_Application_Id = A2.Application_Id(+) And R.Requested_By = U.User_Id(+) And R.Cd_Id = C.Cd_Id(+) And R.Oracle_Id = O.Oracle_Id(+) And Q.Application_Id = :q_applid And Q.Concurrent_Queue_Id = :queue_id And (P.Enabled_Flag is NULL OR P.Enabled_Flag = 'Y') And R.Hold_Flag = 'N' And R.Requested_Start_Date <= Sysdate And ( R.Enforce_Seriality_Flag = 'N' OR ( C.RunAlone_Flag = P.Run_Alone_Flag And (P.Run_Alone_Flag = 'N' OR Not Exists
(Select Null
From Fnd_Concurrent_Requests Sr
Where Sr.Status_Code In ('R', 'T') And Sr.Enforce_Seriality_Flag = 'Y' And Sr.CD_id = C.CD_Id)))) And Q.Running_Processes <= Q.Max_Processes And R.Rowid = :reqname And ((P.Execution_Method_Code != 'S' OR (R.PROGRAM_APPLICATION_ID,R.CONCURRENT_PROGRAM_ID) IN ((0,98),(0,100),(0,31721),(0,31722),(0,31757))) AND ((R.PROGRAM_APPLICATION_ID,R.CONCURRENT_PROGRAM_ID) NOT IN ((20006,121091),(20006,167185),(20007,127088)))) FOR UPDATE OF R.status_code NoWait

Issue

All standard managers run this query with "library cache: mutex X" event

Solution

Test and apply patch 13629213 for 12.1 EBS Instances

While applying if you hit Relink of module "AFPASSWD" failed issue

Test and apply Patch 13855823 for 12.1 EBS Instances

Concurrent Managers are consuming high CPU and memory (Metalink Doc ID 1075684.1)

Oracle Database 12c - Administer PDB Datafile

How to administer PDB datafile

set container to corresponding pdb from root.

SQL> alter session set container=dx12p1;

Session altered.

SQL> alter database datafile '/oradisk/oradata/dx12c/dx12p1/user_01.dbf' autoextend off;

Database altered.

SQL> select file_name from cdb_data_files;

FILE_NAME
---------------------------------------------------------------------------------
/oradisk/oradata/dx12c/dx12p1/system_01.dbf
/oradisk/oradata/dx12c/dx12p1/sysaux_01.dbf
/oradisk/oradata/dx12c/dx12p1/user_02.dbf
/oradisk/oradata/dx12c/dx12p1/user_01.dbf

cdb_data_files will list all the datafiles of all pdbs if it is executing from cdb$root container.

SQL> alter session set container=cdb$root;

Session altered.

SQL> select con_id,file_name from cdb_data_files;

    CON_ID FILE_NAME
---------- --------------------------------------------------------------------------------
         4 /oradisk/oradata/dx12c/dx12p1/system_01.dbf
         4 /oradisk/oradata/dx12c/dx12p1/sysaux_01.dbf
         4 /oradisk/oradata/dx12c/dx12p1/user_01.dbf
         4 /oradisk/oradata/dx12c/dx12p1/user_02.dbf
         1 /oradisk/oradata/dx12c/o1_mf_system_92z0hgg0_.dbf
         1 /oradisk/oradata/dx12c/o1_mf_sysaux_92z0drb2_.dbf
         1 /oradisk/oradata/dx12c/o1_mf_undotbs1_92z0l5j5_.dbf
         1 /oradisk/oradata/dx12c/o1_mf_users_92z0l4c0_.dbf
         3 /oradisk/oradata/dx12c/dx12p/dx12p_o1_mf_system_92z0mhgh_.dbf
         3 /oradisk/oradata/dx12c/dx12p/dx12p_o1_mf_sysaux_92z0mhg3_.dbf
         3 /oradisk/oradata/dx12c/dx12p/dx12p_users02.dbf
         3 /oradisk/oradata/dx12c/dx12p/dx12p_users01.dbf
         2 /oradisk/oradata/dx12c/o1_mf_system_92z0mhgh_.dbf
         2 /oradisk/oradata/dx12c/o1_mf_sysaux_92z0mhg3_.dbf

14 rows selected.

SQL> alter session set container=dx12p;

Session altered.

SQL> select con_id,file_name from cdb_data_files;

    CON_ID FILE_NAME
---------- --------------------------------------------------------------------------------
         3 /oradisk/oradata/dx12c/dx12p/dx12p_o1_mf_system_92z0mhgh_.dbf
         3 /oradisk/oradata/dx12c/dx12p/dx12p_o1_mf_sysaux_92z0mhg3_.dbf
         3 /oradisk/oradata/dx12c/dx12p/dx12p_users02.dbf
         3 /oradisk/oradata/dx12c/dx12p/dx12p_users01.dbf

Oracle Database 12c - Drop PDB

Drop pluggable database

Unplugged database can not be opened again. This database can be dropped using keep datafiles or including datafiles.

SQL> drop pluggable database dx12p1 keep datafiles;

Pluggable database dropped.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DX12P                          READ WRITE NO

SQL> drop pluggable database dx12p including datafiles;

Pluggable database dropped.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO

Oracle Database 12c - Unplug PDB

How to Unplug the pluggable database

PDB must be closed before unplug the corresponding PDB.

SQL> alter pluggable database dx12p1 unplug into '/oracle/app/oracle/product/12.1.0/my_pdb/DX12P1.xml';
alter pluggable database dx12p1 unplug into '/oracle/app/oracle/product/12.1.0/my_pdb/DX12P1.xml'
*
ERROR at line 1:
ORA-65025: Pluggable database DX12P1 is not closed on all instances.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 DX12P1                          READ WRITE NO

SQL> alter pluggable database dx12p1 close;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 DX12P1                         MOUNTED

SQL> alter pluggable database dx12p1 unplug into '/oracle/app/oracle/product/12.1.0/my_pdb/DX12P1.xml';

Pluggable database altered.

Unplugged database can not be opened again.

SQL> alter pluggable database dx12p1 open;
alter pluggable database dx12p1 open
*
ERROR at line 1:
ORA-65086: cannot open/close the pluggable database

Oracle Database 12c - convert PDB from read only to read write

How to convert pdb from read only to read write

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DX12P                          READ ONLY  NO

SQL> alter pluggable database dx12p open;
alter pluggable database dx12p open
*
ERROR at line 1:
ORA-65019: pluggable database DX12P already open


SQL> alter pluggable database dx12p close immediate;

Pluggable database altered.

SQL> alter pluggable database dx12p open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DX12P                          READ WRITE NO
         4 DX12P1                         READ WRITE NO

Oracle Database 12c - convert PDB from read write to read only

How to convert pdb from read write to read only

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DX12P                          READ WRITE NO

SQL> alter pluggable database dx12p close;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DX12P                          MOUNTED

SQL> alter pluggable database dx12p open read only;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DX12P                          READ ONLY  NO

Oracle Database 12c - Open PDB for read write

How to open pdb for read write purpose

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DX12P                          MOUNTED

SQL> alter pluggable database dx12p open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DX12P                          READ WRITE NO

Oracle Database 12c - Clone PDB

Clone PDB

Cloning of pluggable database from another pluggable database is very simple.

create pluggable database <target pdb> from <source pdb>;

examples:

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DX12P                          READ WRITE NO

SQL> create pluggable database dx12p1 from dx12p;
create pluggable database dx12p1 from dx12p
*
ERROR at line 1:
ORA-65081: database or pluggable database is not open in read only mode

Source database should be in read only mode.

SQL> alter pluggable database dx12p close immediate;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DX12P                          MOUNTED

SQL> alter pluggable database dx12p open read only;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DX12P                          READ ONLY  NO

SQL> create pluggable database dx12p1 from dx12p;

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DX12P                          READ ONLY  NO
         4 DX12P1                         MOUNTED

SQL> alter pluggable database dx12p1 open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DX12P                          READ ONLY  NO
         4 DX12P1                         READ WRITE NO

Oracle Database 12c - Create PDB

Create New Pluggable database

SQL> CREATE PLUGGABLE DATABASE dx12p ADMIN USER pdb_adm IDENTIFIED BY pdb_adm FILE_NAME_CONVERT=('/oradisk/oradata/dx12c/pdbseed/','/oradisk/oradata/dx12c/dx12p/');

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DX12P                          MOUNTED

Oracle Database 12c - CDB and PDB

12c New features

Multitenant Acrchitecture - single container database (CDB) to host multiple separate pluggable databases (PDB)

CDB - Container Database - You can create CDB through DBCA or manual command mode.

PDB - Pluggable Database - You can create PDB through DBCA or manual command mode.

SQL> show pdbs - lists all pdb's present in cdb

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DX12P                          READ WRITE NO

Oracle Database 12c introduces a new privilege called "set container".

You can access root or cdb or pdb through sqlplus connect command or alter session set container

conn system/****@db12c - connect to cdb database

conn system/****@db12p - connect to pdb database

conn system/****@"localhost:1521/db12p - connect to pdb database

SQL> alter session set container=cdb$root; - command to set root container

SQL> alter session set container=db12p; - command to set db12p container.

Oracle Apps Queries

Query to check nodes installed in your EBS Instance

select node_name from fnd_nodes