6 Replies Latest reply: Nov 25, 2012 11:27 PM by 976059 RSS

    Apex on 11g - tried quite a bit but cannot get it to work

    976059
      All,

      I need to get Apex running on an existing installation and I'm used the following 2 links as my primary source of information.

      http://cglendenningoracle.blogspot.com/2009/08/how-to-install-oracle-application.html
      http://www.oracle-base.com/articles/10g/dbms_epg_10gR2.php

      Here is a detailed log of all the information.


      SQL> show user
      USER is "SYS"
      SQL> select current_timestamp from dual;

      CURRENT_TIMESTAMP
      ---------------------------------------------------------------------------
      25-NOV-12 09.07.14.056301 AM -04:00

      SQL> select * from all_registry_banners;

      BANNER
      --------------------------------------------------------------------------------
      Oracle Database Catalog Views Release 11.1.0.6.0 - Production
      Oracle Database Packages and Types Release 11.1.0.6.0 - Production
      Oracle Workspace Manager 11.1.0.6.0 - Production
      JServer JAVA Virtual Machine Release 11.1.0.6.0 - Production
      Oracle XDK Release 11.1.0.6.0 - Production
      Oracle Database Java Packages Release 11.1.0.6.0 - Production
      Oracle Expression Filter Release 11.1.0.6.0 - Production
      Oracle Text Release 11.1.0.6.0 - Production
      Oracle XML Database Version 11.1.0.6.0 - Production
      Oracle Rules Manager Release 11.1.0.6.0 - Production
      Oracle Multimedia Release 11.1.0.6.0 - Production
      OLAP Analytic Workspace Release 11.1.0.6.0 - Production
      Oracle OLAP API Release 11.1.0.6.0 - Production
      OLAP Catalog Release 11.1.0.6.0 - Production
      Spatial Release 11.1.0.6.0 - Production
      Oracle Ultra Search Release 11.1.0.6.0 - Production
      Oracle Enterprise Manager Release 11.1.0.6.0 - Production
      Oracle Application Express Release 3.0.1.00.08 - Production <<<<<<<<=============
      OWB Release 11.1.0.6.0 - Production

      19 rows selected.



      SQL> select username, account_status from dba_users where username = 'ANONYMOUS';

      USERNAME ACCOUNT_STATUS
      ------------------------------ --------------------------------
      ANONYMOUS OPEN

      SQL> select username, account_status from dba_users where username = 'APEX_PUBLIC_USER';

      USERNAME ACCOUNT_STATUS
      ------------------------------ --------------------------------
      APEX_PUBLIC_USER OPEN




      SQL> set serveroutput on
      SQL> DECLARE
      2 l_paths DBMS_EPG.varchar2_table;
      3 BEGIN
      4 DBMS_EPG.get_all_dad_mappings (
      5 dad_name => 'APEX',
      6 paths => l_paths);
      7
      8 DBMS_OUTPUT.put_line('Mappings');
      9 DBMS_OUTPUT.put_line('========');
      10 FOR i IN 1 .. l_paths.count LOOP
      11 DBMS_OUTPUT.put_line(l_paths(i));
      12 END LOOP;
      13 END;
      14 /
      Mappings
      ========
      /apex/*




      SQL> SET SERVEROUTPUT ON SIZE UNLIMITED
      DECLARE
      SQL> l_attr_names DBMS_EPG.varchar2_table;
      2 3 l_attr_values DBMS_EPG.varchar2_table;
      BEGIN
      4 5 DBMS_OUTPUT.put_line('Attributes');
      DBMS_OUTPUT.put_line('==========');
      6 7
      DBMS_EPG.get_all_dad_attributes (
      dad_name => 'APEX',
      8 9 10 attr_names => l_attr_names,
      11 attr_values => l_attr_values);
      12
      13 FOR i IN 1 .. l_attr_names.count LOOP
      14 DBMS_OUTPUT.put_line(l_attr_names(i) || '=' || l_attr_values(i));
      15 END LOOP;
      16 END;
      17 /
      Attributes
      ==========
      database-username=ANONYMOUS
      default-page=apex
      document-table-name=wwv_flow_file_objects$
      document-path=docs
      document-procedure=wwv_flow_file_mgr.process_download
      nls-language=american_america.al32utf8
      request-validation-function=wwv_flow_epg_include_modules.authorize

      PL/SQL procedure successfully completed.



      SQL> select dbms_xdb.gethttpport() from dual;

      DBMS_XDB.GETHTTPPORT()
      ----------------------
      7777



      SQL> select * from dba_epg_dad_authorization;

      DAD_NAME
      ----------------------------------------------------------------
      USERNAME
      ------------------------------
      APEX
      ANONYMOUS

      -- I also ran the procedure to enable anonymous access that is listed in one of the sites I mentioned. That didn't help either.


      SQL> !lsnrctl status

      LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 25-NOV-2012 08:15:52

      Copyright (c) 1991, 2007, Oracle. All rights reserved.

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.49)(PORT=1521)))
      STATUS of the LISTENER
      ------------------------
      Alias LISTENER
      Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
      Trace Level off
      Security ON: Local OS Authentication
      SNMP OFF
      Listener Parameter File /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
      Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
      Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.49)(PORT=1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.49)(PORT=7777))(Presentation=HTTP)(Session=RAW))
      Services Summary...
      Service "DB11G01" has 1 instance(s).
      Instance "DB11G01", status UNKNOWN, has 1 handler(s) for this service...
      The command completed successfully

      Tried with browser/curl - not much luck

      SQL> !curl http://192.168.1.49:7777/apex/apex_admin
      curl: (52) Empty reply from server

      I cannot use 8080 as it is being used for some other purpose.


      Any help/pointers is highly appreciated. Please let me know if there is any additional information I can provide from troubleshooting.
        • 1. Re: Apex on 11g - tried quite a bit but cannot get it to work
          Mehabub Sheikh
          Hi,

          By default on 11g the network access is disabled.
          You have to enable that.
          Please follow the instruction if you are using 4.1 on page 3-23 of Installation Guide (Enable Network Services in Oracle Database 11g).

          Then try.
          Thanks,
          Mehabub

          Edited by: Mehabub Sheikh on Nov 25, 2012 8:57 AM
          • 2. Re: Apex on 11g - tried quite a bit but cannot get it to work
            976059
            Hello Mehabub,

            Thanks for looking into it. Couple of points below I'd like to make.

            A)
            I'm not using 4.1. If you see my post the version is default that originally came when 11g r1 was release. [Oracle Application Express Release 3.0.1.00.08].

            However, I looked at the instructions and something looked odd to me. I see the following users (which I think are relevant) from dba_users table and I don't see any users like APEX_03000.

            ANONYMOUS
            APEX_PUBLIC_USER
            FLOWS_030000
            XDB
            FLOWS_FILES

            Should there be such a user?

            B) I was doing some additional research and my suspicion is that it has something to do with listener/dispatcher values. Does the following look right?

            SQL> show parameter dispatcher

            NAME TYPE VALUE
            ------------------------------------ ----------- ------------------------------
            dispatchers string (PROTOCOL=TCP) (SERVICE=DB11G01XDB)
            max_dispatchers integer

            Here is my listener.ora

            [oracle@localhost admin]$ cat listener.ora
            # listener.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
            # Generated by Oracle configuration tools.

            LISTENER =
            (DESCRIPTION_LIST =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.49)(PORT = 1521))
            )

            (DESCRIPTION=
            (ADDRESS=(PROTOCOL = TCP)(HOST=192.168.1.49)(PORT=7777))(Presentation=HTTP)(Session=RAW)
            )

            )

            SID_LIST_LISTENER=
            (SID_LIST =
            (SID_DESC =
            (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
            (SID_NAME = DB11G01)
            )
            )

            ---

            Listener log file entry

            <msg time='2012-11-25T13:46:51.052-05:00' org_id='oracle' comp_id='tnslsnr'
            type='UNKNOWN' level='16' host_id='localhost.localdomain.home'
            host_addr='192.168.1.5'>
            <txt>TNS-12523: TNS:listener could not find instance appropriate for the client connection
            </txt>
            </msg>

            Thanks in advance for any additional pointers.
            • 3. Re: Apex on 11g - tried quite a bit but cannot get it to work
              AndyPol
              APEX v 3 is in FLOWS_030000 schema.

              If you can't connect to apex home try:

              To set HTTP Access to allow remote connects (as SYS):

              SQL> exec dbms_xdb.setListenerLocalAccess (l_access => FALSE);

              Or if you on the server use localhost

              Regards
              • 4. Re: Apex on 11g - tried quite a bit but cannot get it to work
                976059
                Thanks for the suggestion. That didn't seem help either.


                [oracle@localhost ~]$ sqlplus /nolog

                SQL*Plus: Release 11.1.0.6.0 - Production on Sun Nov 25 17:58:10 2012

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

                SQL> connect / as sysdba
                Connected.
                SQL> exec dbms_xdb.setListenerLocalAccess (l_access => FALSE);

                PL/SQL procedure successfully completed.

                SQL>

                Browser Output (Chrome):
                Error 324 (net::ERR_EMPTY_RESPONSE): The server closed the connection without sending any data.

                SQL> !tail -10 /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
                host_addr='192.168.1.5'>
                <txt>25-NOV-2012 18:02:05 * http * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.6)(PORT=49239)) * handoff * http * 12523
                </txt>
                </msg>
                <msg time='2012-11-25T18:02:05.657-05:00' org_id='oracle' comp_id='tnslsnr'
                type='UNKNOWN' level='16' host_id='localhost.localdomain.home'
                host_addr='192.168.1.5'>
                <txt>TNS-12523: TNS:listener could not find instance appropriate for the client connection
                </txt>
                </msg>


                One thing that still bothers me is that I don't see a handler for DB11G01XDB in my "lsnrctl status" output.

                Listening Endpoints Summary...
                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.49)(PORT=1521)))
                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.49)(PORT=8080))(Presentation=HTTP)(Session=RAW))
                Services Summary...
                Service "DB11G01" has 1 instance(s).
                Instance "DB11G01", status UNKNOWN, has 1 handler(s) for this service...
                The command completed successfully


                This coupled with the error message seems suggests that somehow when the request goes to server, it doesn't know what to do with it.

                How do I make my listener get a handler for xdb?
                • 5. Re: Apex on 11g - tried quite a bit but cannot get it to work
                  976059
                  By the way, you may notice that port number has changed since my original post. This is because I shutdown what ever was running on 8080 so that I can use it for apex.

                  I went though the usual exercise of setting the port, restarting db/listener etc.

                  SQL> select dbms_xdb.gethttpport() from dual;

                  DBMS_XDB.GETHTTPPORT()
                  ----------------------
                  8080


                  Thanks.
                  • 6. Re: Apex on 11g - tried quite a bit but cannot get it to work
                    976059
                    I'm still stuck, but here is some additional info if that is of any use for figuring out the problem...

                    SQL> @?/rdbms/admin/epgstat.sql
                    --------------------------------------
                    | XDB protocol ports: |
                    | XDB is listening for the protocol |
                    | when the protocol port is non-zero. |
                    --------------------------------------

                    HTTP Port FTP Port
                    --------- --------
                    8080 0

                    1 row selected.

                    ---------------------------
                    | DAD virtual-path mappings |
                    ---------------------------

                    Virtual Path DAD Name
                    -------------------------------- --------------------------------
                    /apex/* APEX

                    1 row selected.

                    ----------------
                    | DAD attributes |
                    ----------------

                    DAD Name DAD Param DAD Value
                    ------------ ------------------------ ----------------------------------------
                    APEX database-username ANONYMOUS
                    default-page apex
                    document-table-name wwv_flow_file_objects$
                    request-validation-funct wwv_flow_epg_include_modules.authorize
                    ion

                    document-procedure wwv_flow_file_mgr.process_download
                    nls-language american_america.al32utf8
                    document-path docs

                    7 rows selected.

                    ---------------------------------------------------
                    | DAD authorization: |
                    | To use static authentication of a user in a DAD, |
                    | the DAD must be authorized for the user. |
                    ---------------------------------------------------

                    DAD Name User Name
                    -------------------------------- --------------------------------
                    APEX ANONYMOUS

                    1 row selected.

                    ----------------------------
                    | DAD authentication schemes |
                    ----------------------------

                    DAD Name User Name Auth Scheme
                    -------------------- -------------------------------- ------------------
                    APEX ANONYMOUS Anonymous

                    1 row selected.

                    --------------------------------------------------------
                    | ANONYMOUS user status: |
                    | To use static or anonymous authentication in any DAD, |
                    | the ANONYMOUS account must be unlocked. |
                    --------------------------------------------------------

                    Database User Status
                    --------------- --------------------
                    ANONYMOUS OPEN

                    1 row selected.

                    -------------------------------------------------------------------
                    | ANONYMOUS access to XDB repository: |
                    | To allow public access to XDB repository without authentication, |
                    | ANONYMOUS access to the repository must be allowed. |
                    -------------------------------------------------------------------

                    Allow repository anonymous access?
                    ----------------------------------
                    true

                    1 row selected.

                    Edited by: user1220721 on Nov 25, 2012 9:26 PM