DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Thursday, April 25, 2013

ORA-24067: exceeded maximum number of subscribers for queue APPLSYS.FND_CP_GSM_OPP_AQ

ORA-06512: at "APPS.FND_CP_OPP_IPC", line 85

ISSUE:XML reports  complete in warning

This is a freshly cloned instance and all managers and OPP services
were up and running.
However users were reporting of XML reports completing in error.
 
A check of the OPP logs revealed the following error:

[OPPServiceThread0] java.sql.SQLException: ORA-24067: exceeded maximum number of subscribers for queue APPLSYS.FND_CP_GSM_OPP_AQ
ORA-06512: at "APPS.FND_CP_OPP_IPC", line 85
ORA-06512: at line 1

    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
    at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:191)
    at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:950)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1225)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3488)
    at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:3857)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
    at oracle.apps.fnd.cp.opp.OPPAQMonitor.initAQ(OPPAQMonitor.java:558)
    at oracle.apps.fnd.cp.opp.OPPAQMonitor.init(OPPAQMonitor.java:534)
    at oracle.apps.fnd.cp.opp.OPPAQMonitor.initialize(OPPAQMonitor.java:89)
    at oracle.apps.fnd.cp.opp.OPPServiceThread.init(OPPServiceThread.java:94)
    at oracle.apps.fnd.cp.gs
f.BaseServiceThread.run(BaseServiceThread.java:135)


SOLUTION:
SQL> select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

  COUNT(*)
----------
   1039973

Take a back up of the table :

SQL> create table applsys.FND_CP_GSM_OPP_AQTBL_bkp2 as select * from applsys.FND_CP_GSM_OPP_AQTBL;

Table created.

SQL> show user
USER is "APPS"
SQL> conn applsys/devapps
Connected.
SQL> show user
USER is "APPLSYS"


SQL> exec dbms_aqadm.purge_queue_table('FND_CP_GSM_OPP_AQTBL', null, null);


PL/SQL procedure successfully completed.

SQL> SQL>
SQL> select count(*) from applsys.FND_CP_GSM_OPP_AQTBL ;

  COUNT(*)
----------
         0

Connect as APPS

bash-3.00$ sqlplus apps/devapps

SQL*Plus: Release 10.1.0.5.0 - Production on Tue Sep 11 11:12:45 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL>@$FND_TOP/patch/115/sql/afopp002.sql
Enter value for 1: APPLSYS
Enter value for 2: devapps
Connected.

PL/SQL procedure successfully completed.

SQL> exec fnd_cp_opp_ipc.remove_all_subscribers();




ISSUE SOLVED !!

Wednesday, April 24, 2013

Find Total Version Count for Every one hour from AWR table

Script:

set serveroutput on
set lines 130
set verify off
accept v_begin_time prompt 'Enter BEGIN_TIME DD-MON-YY HH24:MI:SS->'
accept v_end_time prompt 'Enter END_TIME DD-MON-YY HH24:MI:SS->'
DECLARE
   lv_v_version_count   dba_hist_sqlstat.version_count%TYPE;
   lv_n_cnt         NUMBER                            := 0;

BEGIN
   FOR c1 IN
      (SELECT TO_CHAR (begin_interval_time, 'DD/MON/YY:HH24') sun,TO_CHAR (begin_interval_time + (1/24), 'DD/MON/YY:HH24')  sun1
         FROM dba_hist_snapshot
        WHERE begin_interval_time BETWEEN   TO_DATE ('&v_begin_time',
                                                     'DD-MON-YYYY HH24:MI'
                                                    )
                                          - 1 / 24
                                      AND TO_DATE ('&v_end_time',
                                                   'DD-MON-YYYY HH24:MI'
                                                  )order by 1)
   LOOP
      BEGIN
      --dbms_output.put_line (c1.sun);
         select sum(sum(version_count))
         into lv_v_version_count
         from dba_hist_sqlstat a, dba_hist_snapshot b, dba_hist_sqltext c
         where a.SNAP_ID = b.SNAP_ID
         and a.sql_id = c.sql_id
         and BEGIN_INTERVAL_TIME >= to_date(c1.sun, 'DD-MON-YY:HH24:MI')
         and BEGIN_INTERVAL_TIME <= to_date(c1.sun1, 'DD-MON-YY:HH24:MI')
         group by a.version_count;
         lv_n_cnt := lv_n_cnt + 1;
         DBMS_OUTPUT.put_line (c1.sun ||'    '||lv_v_version_count);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            DBMS_OUTPUT.put_line ('No Data Found');
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('When Others Error');
      END;
   END LOOP;
END;
/

Sample Output :

Date        Version_Count
07/FEB/13:07    5342
07/FEB/13:08    6575
07/FEB/13:09    6675
07/FEB/13:10    9361
07/FEB/13:11    8042
07/FEB/13:12    8224

Hard_Parse Value for every one hour from AWR tables.

Script :
 
set serveroutput on
set lines 130
set verify off
accept v_begin_time prompt 'Enter BEGIN_TIME DD-MON-YY HH24:MI:SS->'
accept v_end_time prompt 'Enter END_TIME DD-MON-YY HH24:MI:SS->'
DECLARE
   lv_v_stat_name   dba_hist_sysstat.stat_name%TYPE;
   lv_v_per_sec     NUMBER;
   lv_n_cnt         NUMBER                            := 0;

BEGIN
   FOR c1 IN
      (SELECT TO_CHAR (begin_interval_time, 'DD/MON/YY:HH24') sun,TO_CHAR (begin_interval_time + (1/24), 'DD-MON-YY:HH24')  sun1
         FROM dba_hist_snapshot
        WHERE begin_interval_time BETWEEN   TO_DATE ('&v_begin_time',
                                                     'DD-MON-YYYY HH24:MI'
                                                    )
                                          - 1 / 24
                                      AND TO_DATE ('&v_end_time',
                                                   'DD-MON-YYYY HH24:MI'
                                                  )order by 1)
   LOOP
      BEGIN
      --dbms_output.put_line (c1.sun);
         SELECT distinct e.stat_name "Statistic Name",
                  ROUND
                     (  (e.VALUE - b.VALUE)
                      / (SELECT AVG
                                   (    EXTRACT
                                             (DAY FROM (  e1.end_interval_time
                                                        - b1.end_interval_time
                                                       )
                                             )
                                      * 24
                                      * 60
                                      * 60
                                    +   EXTRACT
                                            (HOUR FROM (  e1.end_interval_time
                                                        - b1.end_interval_time
                                                       )
                                            )
                                      * 60
                                      * 60
                                    +   EXTRACT
                                           (MINUTE FROM (  e1.end_interval_time
                                                         - b1.end_interval_time
                                                        )
                                           )
                                      * 60
                                    + EXTRACT
                                          (SECOND FROM (  e1.end_interval_time
                                                        - b1.end_interval_time
                                                       )
                                          )
                                   )
                           FROM dba_hist_snapshot b1, dba_hist_snapshot e1
                          WHERE b1.snap_id = b.snap_id
                            AND e1.snap_id = e.snap_id
                            AND b1.dbid = b.dbid
                            AND e1.dbid = e.dbid
                            AND b1.instance_number = b.instance_number
                            AND e1.instance_number = e.instance_number
                            AND b1.startup_time = e1.startup_time
                            AND b1.end_interval_time < e1.end_interval_time),
                      2
                     ) "Per Second"
             INTO lv_v_stat_name,
                  lv_v_per_sec
             FROM dba_hist_sysstat b, dba_hist_sysstat e
            WHERE b.snap_id  =
                     (SELECT max(snap_id)
                        FROM dba_hist_snapshot
                       WHERE begin_interval_time
                                BETWEEN   TO_DATE (c1.sun,
                                                   'DD-MON-YY HH24:MI'
                                                  )
                                        - 1 / 24
                                    AND TO_DATE (c1.sun,
                                                 'DD-MON-YY HH24:MI'))
              AND e.snap_id =
                     (SELECT max(snap_id)
                        FROM dba_hist_snapshot
                       WHERE begin_interval_time
                                BETWEEN   TO_DATE (c1.sun1,
                                                   'DD-MON-YY HH24:MI'
                                                  ) - 1/24
                                     AND TO_DATE (c1.sun1,
                                                 'DD-MON-YY HH24:MI'))
              AND b.stat_id = e.stat_id
              AND e.stat_name LIKE '%hard%'
              AND e.VALUE >= b.VALUE
              AND e.VALUE > 0
         ORDER BY 1 ASC;

         lv_n_cnt := lv_n_cnt + 1;
         DBMS_OUTPUT.put_line (c1.sun||'    '||lv_v_stat_name||'     '||lv_v_per_sec);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            DBMS_OUTPUT.put_line ('No Data Found');
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('When Others Error');
      END;
   END LOOP;
END;
/

====

Sample Output :

Date Stat_Name Value
18/APR/13:07 parse count (hard) 3.23
18/APR/13:08 parse count (hard) 3.95
18/APR/13:09 parse count (hard) 4.14
18/APR/13:10 parse count (hard) 4.26
18/APR/13:11 parse count (hard) 4.12

SSL Configuration on R12


OS level Required rpm’s:


  1. xmlsec1-openssl-1.2.6-3
  2. openssl-0.9.7a-43.18.el4
  3. openssl-devel-0.9.7a-43.18.el4
  4. openssl-devel-0.9.7a-43.18.el4
  5. xmlsec1-openssl-1.2.6-3
  6. openssl096b-0.9.6b-22.46.el4_8.1
  7. openssl-perl-0.9.7a-43.18.el4
  8. openssl-0.9.7a-43.18.el4
  9. openssl096b-0.9.6b-22.46.el4_8.1


Generate Certificate:

















Move  these key to the  wallet directory
 

 


To Validate the Certificate login to “versign” site  and click onto test login and select server platform as Apache copy and paste the above key “ofc.maveric-systems.com.csr”  and click continue it will shows the valid certify or not. Note this steps to check the generate key is correct or not.



IMPORT  CERTIFICATION TEST

Import the Generated “ ofc.maveric-systems.com.csr”  key  to the client or server  in browser.

üClick and open the Firefox.
ü  Select Tools à optionsàAdvancedàEncryption
ü  Click on view certificatesà Authorities.
ü  Click on Import option and select the key form the location
ü  Select the key “ofc.maveric-systems.com.csr” key and click ok to import.


CREATING WALLET
Go to wallet directory and enter the “owm&” command .
It will open the Wallet Window.








Click  “NO” option because we already generated the authentication key.


Enter the Password





üClick on the Operations à Export User Certificate.
ü  Now the browser window will appear select the Certificate “ofc.maveric-systems.com.csr” and press ok button it will Export the key.
ü  In our scenario after Export we have to import the key for wallet directory because we manually generate the certificate it is placed in /root ie., root home directory.
ü  Click on the Operations à Import User Certificate.
ü  Now the browser window will appear select the Certificate “ofc.maveric-systems.com.csr” and press ok button it will Import the key.

ü  Now click on Wallet select save option to save the wallet.
ü  Then select the Auto login.



Now Press the Exit Button for “Exit” the  Wallet.

Import the CA Certificate you'll also need to add the contents of ofc.maveric-systems.com.crt file to b64InternetCertificate.txt file located in“$ORACLE_HOME/sysman/configsysman/config/b64InternetCertificate.txt".

We also provided an Intermediate Certificate (intermediate.crt) then we will also need to add that to the b64InternetCertificate.txt







Modify the OPMN wallet
The E-Business Suite Rapid Install process creates a default "demo" opmn wallet in the $INST_TOP/certs/opmn directory that can be used in test instances for basic SSL testing. Now that the Apache wallet has been created we will need to to use these same certificates for opmn.   Use the following steps to backup and copy the wallets.
ü  Navigate to the $INST_TOP/certs/opmn directory.
ü  Create a new directory named BAK
ü  Move the ewallet.p12 and cwallet.sso files to the BAK directory just created.
ü  Copy the ewallet.p12 and cwallet.sso files from the $INST_TOP/certs/Apache directory to the $INST_TOP/certs/opmn directory.

Update the JDK Cacerts File
Oracle Web Services requires the Certificate of the Certifying Authority server certificate (ofc.maveric-systems.com.crt from the previous step) to be present in the JDK cacerts file.  In addition, some features of XML Publisher and BI Publisher require the server certficate (ofc.maveric-systems.com.crt from previous step) to be present,  Follow these steps to be sure these requirements are met.
ü  Navigate to the $OA_JRE_TOP/lib/security directory
ü  Backup the existing cacerts file.
ü  Copy the ofc.maveric-systems.com.crt and Intermediate.crt files to this directory and issue the following command to insure thatcacerts has write permissions.
ü  $ chmodu+wofc.maveric-systems.com.crt.


Add your Apache intermediate.crt and ofc.maveric-systems.com.crt to cacerts by the following commands.
$ keytool -import -alias ApacheRootCA -file intermediate.crt -trustcacerts -v -keystorecacerts

$ keytool -import -alias ApacheServer -file ofc.maveric-systems.com.crt -trustcacerts -v -keystorecacerts
(It will ask the password, enter the password (Abc123.) this passwd will given while creating the certificate ).


CONTEXT_FILE
Then we have to do the following changes in the “CONTEXT_FILE”.





Run Autoconfig



CLEAR the Persistenance


rm -fr $INST_TOP/ora/10.1.3/j2ee/oacore/persistence/*
rm -fr $INST_TOP/ora/10.1.3/j2ee/oafm/persistence/*
rm -fr $INST_TOP/ora/10.1.3/j2ee/forms/persistence/*

Run the following commands on the middle (web) tier Oracle Config home:
ü  $ cd $ORA_CONFIG_HOME/10.1.3/Apache/Apache/bin
ü  $ ls -la    
ü  $ chown root .apachectl
ü  $ chmod 6750 .apachectl

Before Starting the Instance we have to do the following changes.
$ cd $ORA_CONFIG_HOME/10.1.3/Apache/Apache/bin
$ ls -la    
$ chowndoyenapps .apachectl
$ chmod 6750 .apachectl
Start Services
$ sh adstrtal.sh

Now Login to Apps























INSTALLING ORACLE APEX 3.1 ON ORACLE 10g




                         



  1. Download the Apex 3.1 and move to /home/oracle dir
  2. Now unzip the  file .




login to database and execute the following commands.

$sqlplus / as sysdba

sql> show parameter shared_pool_size

If the Shared Pool is Not up to 100M set it.

STEP-3
sql> ALTER SYSTEM SET SHARED_POOL_SIZE='100M' SCOPE=both;

STEP-4
sql> SELECT VALUE FROM v$parameter WHERE NAME = 'job_queue_processes';

STEP-5
If the Job_Queue_Processes is not up 20 then do the Following:

STEP-6
sql> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20;

STEP-7
sql> SELECT COMP_NAME,STATUS FROM DBA_REGISTRY WHERE SCHEMA='XDB';

STEP-8
sql> CREATE TABLESPACE apex DATAFILE '/u01/app/oracle/product/10.2.0/oradata/apex/APEX01.DBF ' SIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K SEGMENT SPACE MANAGEMENT auto;





cd $ORACLE_HOME/rdbms/admin

Sqlplus "/as sysdba"

SQL> spool text_install.txt
SQL> @catactx.sql CTXSYS SYSAUX TEMP NOLOCK

connect ctxsys
Enter password: password

SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/drdefus.sql






SQL> SELECT COMP_NAME,STATUS FROM DBA_REGISTRY ;







Then Go to Apex home Check the system tablespace is more than 1000M if Not then follow this.


SQL>alter database datafile '/u02/app/oracle/oradata/apex/system01.dbf' resize 2000M;
Database altered.





  1. Then Go to the Apex Folder.(cd /home/oracle/apex/)
  2. login to sqlplus / as sysdba and execute the following command.
  3. SQL>@apexins APEX APEX TEMP /i/

Now the Apex installation was complete. Then follow the steps

Login as sqlplus "/as sysdba"

SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;

User altered.

STEP
SQL> ALTER USER APEX_PUBLIC_USER IDENTIFIED BY oracle;

User altered.


Integrating Oracle Apex in Web Server

Set the Env for Companion.

 $ vim ias.env 


ORACLE_HOME=/u01/app/oracle/product/10.2.0/ohs; export ORACLE_HOME
PATH=$ORACLE_HOME/perl/bin:$ORACLE_HOME/Apache/modplsql/conf:$PATH; export PATH
PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1; export PERL5LIB


save this file and exit and follw the bellow steps.


$ . ias.env
$ cd /home/oracle/apex
$ cp -R images $ORACLE_HOME/Apache/Apache
$ cd $ORACLE_HOME/Apache/modplsql/conf




Now Edit the Dads.conf file by add and save the fillowing lines into dads.conf

$ vim dads.conf  

Alias /i/ "/u02/app/oracle/product/10.2.0/ias/Apache/Apache/images/"

AddType text/xml xbl
AddType text/x-component htc

<Location /pls/apex>
  SetHandler pls_handler
  Order deny,allow
  Allow from all
  AllowOverride                 None
  PlsqlDatabaseUsername         APEX_PUBLIC_USER
  PlsqlDatabasePassword         oracle
  PlsqlDatabaseConnectString    192.168.1.2:1521:apex
  PlsqlAuthenticationMode       Basic
  PlsqlDefaultPage              apex
  PlsqlDocumentTablename        wwv_flow_file_objects$
  PlsqlDocumentPath             docs
  PlsqlDocumentProcedure        wwv_flow_file_mgr.process_download
  PlsqlNLSLanguage              AMERICAN_AMERICA.AL32UTF8
</Location>



Enter the following command

$ cd $ORACLE_HOME/Apache/modplsql/conf

$ perl dadTool.pl -o

$ $ORACLE_HOME/opmn/bin/opmnctl restartproc ias-component=HTTP_Server



 $ cd /home/oracle/apex
 Login into sqlplus / as sysdba and execute the following cmd.

SQL> @apxchpwd.sql

Enter a value below for the password for the Application Express ADMIN user.
Enter a password for the ADMIN user: Password

After  Enter the Password Login Into Apex .