Application Listener showing down while it is up from em12c console

Application Listener showing down while it is up from em 12c console

After discovering E-Business suite node (12.1.3) from em 12c console, Application listener appear down while it is up and running

to solve the issue perform the following action plan:
as E-Business suite Application user

chmod 775 $ORACLE_HOME/network/mesg
cd $ORACLE_HOME/network/mesg
chmod 666 *
chmod 775 $ORACLE_HOME/network
chmod 775 $ORACLE_HOME/network/admin

Note: this error occuer when the agent user is differ than the EBS user

Purging errored workflow items ib EBS R12

CREATE OR REPLACE procedure APPS.XX_PURGE_Error_WF_ITEMS (errbuf OUT NOCOPY VARCHAR2,retcode OUT NOCOPY NUMBER)
as
v_Commit NUMBER:=0;
v_commit1 NUMBER:=0;
CURSOR wf_error IS
SELECT item_key
FROM wf_items
WHERE item_type = 'WFERROR'
AND end_date is null;
Begin
/*Aborting errored workflows*/
FOR i IN wf_error LOOP
WF_ENGINE.abortProcess('WFERROR', i.item_key);
END LOOP;
    v_commit:=v_commit1+1;
    IF v_commit=5000 THEN
    v_commit1:=0;
    COMMIT;
    end if;
/*Purging errored work flow item types*/
FOR cur IN   (SELECT wi.item_key     --,WIS.BEGIN_DATE
                      FROM wf_items wi ,wf_item_activity_statuses  wis
                      WHERE wi.item_type LIKE '%WFERROR%'
                      AND wi.end_date IS NOT NULL
                      AND wis.item_key=wi.item_key
                      AND wis.item_type=wi.item_type)
  loop
    wf_purge.items ('WFERROR',cur.item_key,SYSDATE,FALSE,TRUE);
    v_commit1:=v_commit1+1;
    IF v_commit1=5000 THEN
    v_commit1:=0;
    COMMIT;
    END IF;
  END loop;
END;
/

Step by Step Clone RAC 10g Database to Non RAC Database in R12 Environments

1- make pre clone for database from $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/ perl adpreclone.pl dbTier
2- shutdown the source database and listener
3- copy the source files(ORACLE_HOME,Datafiles) to destination servers

export PATH=$PATH:{PATH TO ORACLE HOME}/bin
which unzip

export ORACLE_HOME={PATH TO ORACLE HOME}/
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk rac_off
make -f ins_rdbms.mk ioracle

4- execute post clone steps with the following options
cd $ORACLE_HOME/appsutil/clone/bin
perl adcfgclone.pl dbTier

Target System Hostname (virtual or normal) [devmdb1] :

Target Instance is RAC (y/n) [y] : n

Target System Database SID : PRD ( In this example i used the same name of the production Instance with a different port)

Target System Base Directory : /oracle/product/

Target System utl_file_dir Directory List : /usr/tmp

Number of DATA_TOP's on the Target System [2] : 1

Target System DATA_TOP Directory 1 : /oracle/PRD/datafile

Target System RDBMS ORACLE_HOME Directory [//oracle/product/db/tech_st/10.2.0] : /oracle/product/RAC10g

Do you want to preserve the Display [rmsmdb1:0.0] (y/n) ? : n

Target System Display [devmdb1:0.0] :

Do you want the the target system to have the same port values as the source system (y/n) [y] ? : y


the Post Clone will fail after creating the control file, and will not be able to open the database (Just mounted) execute the following:

5- a- shutdown the databse immediate;
   b- Modify initSID.ora with the following parameter: _no_recovery_through_resetlogs=TRUE
   c- startup mount;
   d- alter database open resetlogs
   e- execute any failed steps in the clone log file like adding temp files or renaming the global name of the database


6-reexecute pre clone on same machine.

 shutdown immediate;
 stop the listener

7-re-execute post clone process.