workflow rebuild queue Oracle EBS R12.2

How To Rebuild Mailers Queue When it is Inconsistent or Corrupted? (Doc ID 736898.1)



1. Stop application services and Workflow Agent Listeners and Mailers.

2. Find the table space where indexes on corrid for queue tables are created:

sqlplus apps/<apps_pwd>

select distinct tablespace_name
from dba_indexes,dba_queues
where index_name='WF_NOTIFICATION_OUT_N1'
and table_name=queue_table
and name like 'WF%';

3. Rebuild mailer's queue:

sqlplus apps/apps123 @$FND_TOP/patch/115/sql/wfntfqup.sql APPS apps123 APPLSYS

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Oct 14 16:43:06 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options

**** wfaqback.sql completed,  executing SQLs from wfntfqup
declare
*
ERROR at line 1:
ORA-20000: Oracle Error = -4020 - ORA-04020: deadlock detected while trying to
lock object APPLSYS.AQ$_WF_NOTIFICATION_OUT_F
ORA-06512: at line 318


Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options



Error - ORA-20000: Oracle Error = -4020 - ORA-04020: deadlock detected while trying to lock object APPLSYS.AQ$_WF_NOTIFICATION_OUT_F ORA-06512: at line 318

Soultion- ORA-04020: deadlock detected while trying to lock object APPLSYS.AQ$_WF_CONTROL_F (Doc ID 2496222.1)


[applmgr@pansoft sql]$ sqlplus applsys/apps123

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Oct 14 16:45:43 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options

SQL> SHOW parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      PRODERP
SQL> DROP VIEW applsys.AQ$WF_CONTROL;

View dropped.

SQL> DROP VIEW applsys.AQ$_WF_CONTROL_F;

View dropped.

SQL> @$FND_TOP/patch/115/sql/wfctqrec.sql APPLSYS apps123
Connected.

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options



SQL*Plus: Release 10.1.0.5.0 - Production on Mon Oct 14 16:48:35 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      PRODERP




[applmgr@pansoft sql]$ sqlplus apps/apps123 @$FND_TOP/patch/115/sql/wfntfqup.sql APPS apps123 APPLSYS

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Oct 14 17:49:04 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options

**** wfaqback.sql completed,  executing SQLs from wfntfqup
declare
*
ERROR at line 1:
ORA-20000: Oracle Error = -2289 - ORA-02289: sequence does not exist
ORA-06512: at line 318


Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options

Error- ORA-20000: Oracle Error = -2289 - ORA-02289: sequence does not exist
Solution- Restart database


[applmgr@pansoft sql]$ sqlplus apps/apps123 @$FND_TOP/patch/115/sql/wfntfqup.sql APPS apps123 APPLSYS

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Oct 14 17:56:03 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options

**** wfaqback.sql completed,  executing SQLs from wfntfqup
declare
*
ERROR at line 1:
ORA-20000: Oracle Error = -24010 - ORA-24010: QUEUE APPLSYS.WF_NOTIFICATION_OUT
does not exist
ORA-01403: no data found
ORA-01403: no data found
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_AQ_INV", line 1248
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_AQ_INV", line 1248
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_AQ_INV", line 1248
ORA-06512: at line 1
ORA-06512: at line 318


Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options



[applmgr@pansoft sql]$ sqlplus applsys/apps123

SQL> DROP VIEW applsys.AQ$WF_CONTROL;

View dropped.

SQL> DROP VIEW applsys.AQ$_WF_CONTROL_F;

View dropped.

SQL> @$FND_TOP/patch/115/sql/wfctqrec.sql APPLSYS apps123
Connected.

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options


[applmgr@pansoft sql]$ sqlplus applsys/apps123

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Oct 14 18:04:40 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options

SQL> @$FND_TOP/patch/115/sql/afwfqgnt.sql APPS apps123 APPLSYS apps123
Connected.
declare
*
ERROR at line 1:
ORA-24010: QUEUE APPLSYS.WF_NOTIFICATION_OUT does not exist
ORA-06512: at line 50


Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options

[applmgr@pansoft sql]$ sqlplus applsys/apps123

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Oct 14 18:30:29 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options

SQL> exec dbms_aqadm.stop_queue(queue_name => 'APPLSYS.WF_ERROR');

PL/SQL procedure successfully completed.

SQL> exec dbms_aqadm.drop_queue(queue_name => 'APPLSYS.WF_ERROR');

PL/SQL procedure successfully completed


SQL> exec dbms_aqadm.drop_queue_table( queue_table=> 'APPLSYS.WF_ERROR', force=>true);
BEGIN dbms_aqadm.drop_queue_table( queue_table=> 'APPLSYS.WF_ERROR', force=>true); END;

*
ERROR at line 1:
ORA-04020: deadlock detected while trying to lock object APPLSYS.AQ$_WF_ERROR_F
ORA-06512: at "SYS.DBMS_AQADM", line 388
ORA-06512: at line 1


SQL> DROP VIEW applsys.AQ$_WF_CONTROL_F;

View dropped.

SQL> exec dbms_aqadm.drop_queue_table( queue_table=> 'APPLSYS.WF_ERROR', force=>true);
BEGIN dbms_aqadm.drop_queue_table( queue_table=> 'APPLSYS.WF_ERROR', force=>true); END;

*
ERROR at line 1:
ORA-04020: deadlock detected while trying to lock object APPLSYS.AQ$_WF_ERROR_F
ORA-06512: at "SYS.DBMS_AQADM", line 388
ORA-06512: at line 1


SQL> drop view APPLSYS.AQ$_WF_ERROR_F;

View dropped.

SQL> exec dbms_aqadm.drop_queue_table( queue_table=> 'APPLSYS.WF_ERROR', force=>true);

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options
[applmgr@pansoft sql]$ ll $FND_TOP/patch/115/sql/wfevquc2.sql


applmgr@pansoft sql]$ sqlplus apps/apps123 @$FND_TOP/patch/115/sql/wfevquc2.sql APPLSYS apps123 WF_DEFERRED APPS6400

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Oct 14 18:38:35 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options


PL/SQL procedure successfully completed.

Connected.

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options
[applmgr@pansoft sql]$ sqlplus apps/apps123 @$FND_TOP/patch/115/sql/wfbesqsubc.sql APPLSYS apps123

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Oct 14 18:39:00 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options

Connected.

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

declare
*
ERROR at line 1:
ORA-24010: QUEUE APPLSYS.WF_ERROR does not exist
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 7046
ORA-06512: at "SYS.DBMS_AQADM", line 549
ORA-06512: at line 7


Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options


[applmgr@pansoft sql]$ sqlplus apps/apps123 @$FND_TOP/patch/115/sql/wfmqsubc.sql APPLSYS apps123

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Oct 14 18:41:25 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options


Commit complete.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options
[applmgr@pansoft sql]$


[applmgr@pansoft sql]$ sqlplus APPS/apps123 @afwfqgnt.sql APPS apps123 APPLSYS apps123

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Oct 14 18:43:34 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options

Connected.
declare
*
ERROR at line 1:
ORA-24010: QUEUE APPLSYS.WF_ERROR does not exist
ORA-06512: at line 50


Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options



[applmgr@pansoft sql]$ sqlplus apps/apps123 @$FND_TOP/patch/115/sql/wfevquc2.sql applsys apps123 WF_ERROR APPS 5 0

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Oct 14 19:12:15 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options


PL/SQL procedure successfully completed.

Connected.

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options



OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options
[applmgr@pansoft sql]$ sqlplus apps/apps123 @$FND_TOP/patch/115/sql/wfbesqsubc.sql APPLSYS apps123

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Oct 14 19:12:46 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options

Connected.

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options


[applmgr@pansoft sql]$ sqlplus APPS/apps123 @afwfqgnt.sql APPS apps123 APPLSYS apps123

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Oct 14 19:12:56 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options

Connected.
declare
*
ERROR at line 1:
ORA-24010: QUEUE APPLSYS.WF_NOTIFICATION_OUT does not exist
ORA-06512: at line 50


Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options


[applmgr@pansoft sql]$ sqlplus apps/apps123 @$FND_TOP/patch/115/sql/wfevquc2.sql applsys apps123 WF_OUT APPS 5 36

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Oct 14 19:14:05 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options


PL/SQL procedure successfully completed.

Connected.

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options


[applmgr@pansoft sql]$ sqlplus applsys/apps123

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Oct 14 19:18:41 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options

SQL> exec dbms_aqadm.drop_queue_table( queue_table=> 'APPLSYS.WF_NOTIFICATION_OUT',force=>true);
BEGIN dbms_aqadm.drop_queue_table( queue_table=> 'APPLSYS.WF_NOTIFICATION_OUT',force=>true); END;

*
ERROR at line 1:
ORA-04020: deadlock detected while trying to lock object
APPLSYS.AQ$_WF_NOTIFICATION_OUT_F
ORA-06512: at "SYS.DBMS_AQADM", line 388
ORA-06512: at line 1


SQL> drop view APPLSYS.AQ$_WF_NOTIFICATION_OUT_F
  2  ;

View dropped.

SQL> exec dbms_aqadm.drop_queue_table( queue_table=> 'APPLSYS.WF_NOTIFICATION_OUT',force=>true);

PL/SQL procedure successfully completed.

SQL> begin
 dbms_aqadm.create_queue_table
 (
 queue_table => 'WF_NOTIFICATION_OUT',
 queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE',
 sort_list => 'PRIORITY,ENQ_TIME',
 multiple_consumers => TRUE,
 comment => 'Workflow JMS Topic',
  2    3    4    5    6    7    8    9   compatible => '8.1'
 );
 exception
 when others then
 raise_application_error(-20000, 'Oracle Error Mkr2= '
 ||to_char(sqlcode)||' - '||sqlerrm);
 end;
 /
 10   11   12   13   14   15   16
PL/SQL procedure successfully completed.

SQL>
SQL> begin
 dbms_aqadm.create_queue
 (
 queue_name => 'WF_NOTIFICATION_OUT',
 queue_table => 'WF_NOTIFICATION_OUT',
 max_retries => 5,
 retry_delay => 3600,
 retention_time => 86400,
 comment => 'Workflow JMS Topics'
 );
 exception
 when others then
 raise_application_error(-20000, 'Oracle Error Mkr4= '
  2    3    4    5    6    7    8    9   10   11   12   13   14   ||to_char(sqlcode)||' - '||sqlerrm);
 end;
 / 15   16

PL/SQL procedure successfully completed.

SQL> begin
 dbms_aqadm.start_queue(queue_name => 'WF_NOTIFICATION_OUT');
 exception
 when others then
 raise_application_error(-20000, 'Oracle Error Mkr5= '
 ||to_char(sqlcode)||' - '||sqlerrm);
 end;
 /
  2    3    4    5    6    7    8
PL/SQL procedure successfully completed.

SQL> begin
 dbms_aqadm.start_queue(queue_name => 'AQ$_WF_NOTIFICATION_OUT_E',
 enqueue => FALSE);

 exception
 when others then
 raise_application_error(-20000, 'Oracle Error Mkr9= '
 ||to_char(sqlcode)||' - '||sqlerrm);
 end;
 /  2    3    4    5    6    7    8    9   10

PL/SQL procedure successfully completed.

SQL> declare
 lagent sys.aq$_agent;
 subscriber_exist exception;
 pragma EXCEPTION_INIT(subscriber_exist, -24034);
 begin
 lagent := sys.aq$_agent('WF_NOTIFICATION_OUT',null,0);
  2    3    4    5    6    7   dbms_aqadm.add_subscriber(queue_name =>'APPLSYS.WF_NOTIFICATION_OUT',
 subscriber=>lagent, rule=>'1=1');
 exception
 when subscriber_exist then
 dbms_aqadm.alter_subscriber(queue_name =>'APPLSYS.WF_NOTIFICATION_OUT',
 subscriber=>lagent,
 rule=>'1=1');
 null; -- ignore if we already added this subscriber.
 end;
  8    9   10   11   12   13   14   15   16   /

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options
[applmgr@pansoft sql]$ sqlplus APPS/apps123 @afwfqgnt.sql APPS apps123 APPLSYS apps123

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Oct 14 19:23:53 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options

Connected.

PL/SQL procedure successfully completed.

Connected.

PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options


[applmgr@pansoft sql]$ vi $FND_TOP/patch/115/sql/wfntfqup.sql
[applmgr@pansoft sql]$ vi $FND_TOP/patch/115/sql/wfntfqup.sql

-- backup Oracle Alert messages in wf_notification_out

#@$FND_TOP/sql/wfaqback.sql WF_NOTIFICATION_OUT/APPS:ALR%

[applmgr@pansoft sql]$ sqlplus apps/apps123 @$FND_TOP/patch/115/sql/wfntfqup.sql APPS apps123 APPLSYS

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Oct 14 19:39:08 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options


PL/SQL procedure successfully completed.

Elapsed: 00:00:00.49

Commit complete.

Elapsed: 00:00:00.00
**** TEMPORARY TABLES / AQs created ****

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.28
**** Messages backed up ****

Commit complete.

Elapsed: 00:00:00.00
**** wfaqback.sql completed,  executing SQLs from wfntfqup

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.53
***  invoking wfaqrenq.sql ******
*** Re-enqueing messages

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.47

Commit complete.

Elapsed: 00:00:00.00
***** Objects related to wf_queue_temp_evt_table and wf_queue_temp_jms_table are dropped *****
***** Re-enqueue OF Alerts completed *****
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options
[applmgr@pansoft sql]$ sqlplus applsys/apps123

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Oct 14 19:41:01 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options

SQL> select name from v$database;

NAME
---------
PRODERP

SQL> CREATE INDEX WF_NOTIFICATION_OUT_N1
ON WF_NOTIFICATION_OUT(CORRID)
STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
TABLESPACE &tbs;   2    3    4
Enter value for tbs: APPS_TS_QUEUES
old   4: TABLESPACE &tbs
new   4: TABLESPACE APPS_TS_QUEUES

Index created.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testing options
[applmgr@pansoft sql]$ cd $ADMIN_SCRIPTS_HOME/
[applmgr@pansoft scripts]$ pwd
/u01/oracle/proderp/fs2/inst/apps/PRODERP_pansoft/admin/scripts
[applmgr@pansoft scripts]$ ./adstrtal.sh

You are running adstrtal.sh version 120.24.12020000.11


Enter the APPS username: apps

Enter the APPS password:
[applmgr@pansoft scripts]$
[applmgr@pansoft scripts]$ ./adstrtal.sh

You are running adstrtal.sh version 120.24.12020000.11


Enter the APPS username: apps

Enter the APPS password:
Enter the WebLogic Server password: