APEX页面无法返回问题

最近为某位客户迁移数据库,由于种种原因,应用也由我来负责完成。最终的结果竟然是在应用上花费的时间比迁移测试还要久。把应用配置中出现的问题记录如下:

客户数据库迁移完毕后APEX页面总是无法访问,在firefox浏览器中返回空白页面,在IE浏览器中返回错误(如访问繁忙、DNS配置错误等)。由于配置过程完全依据Oracle官方文档(Oracle Application Express Installation Guide R3.2),判断为网络配置出现问题。HTTP服务器有两种选择:embedded PL/SQL gateway or Oracle HTTP Server and mod_plsql,我们选择配置Oracle HTTP Server and mod_plsql。

当前问题http://hostname:8080/apexhttp://hostname:8080/apex/apex_admin 无法访问,返回空白页

查找错误根源

  1. 首先怀疑8080端口没有指定,查看:
    SQL> SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;
    
    GETHTTPPORT
    
    -----------
    
    8080

    显然端口配置没有问题

  2. 怀疑主机层面端口有问题,查看:
    [[email protected]]$ netstat -anp
     
    
    (Not all processes could be identified, non-owned process info
    
    will not be shown, you would have to be root to see it all.)
    
    Active Internet connections (servers and established)
    
    Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name
    
    tcp        0      0 127.0.0.1:2208              0.0.0.0:*                   LISTEN      -
    
    tcp        0      0 0.0.0.0:111                 0.0.0.0:*                   LISTEN      -
    
    tcp        0      0 0.0.0.0:945                 0.0.0.0:*                   LISTEN      -
    
    tcp        0      0 127.0.0.1:631               0.0.0.0:*                   LISTEN      -
    
    tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      -
    
    tcp        0      0 127.0.0.1:2207              0.0.0.0:*                   LISTEN      -
    
    tcp        0      0 127.0.0.1:41008             127.0.0.1:1521              ESTABLISHED 8303/ora_pmon_MARSH
    
    tcp        0      0 :::8080                     :::*                        LISTEN      7738/tnslsnr
     
    
    tcp        0      0 :::1521                     :::*                        LISTEN      7738/tnslsnr
    
    tcp        0      0 :::39764                    :::*                        LISTEN      8336/ora_d000_MARSH

    看不出有什么问题

  3. 在本地重建一个11gR2的库,用5分钟配置好APEX后访问成功

  4. 查看客户库与本地库监听情况

    客户库:

    [[email protected] ~]$ lsnrctl status
    
    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 28-APR-2011 20:38:48
    
    Copyright (c) 1991, 2010, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx)(PORT=1521)))
    
    STATUS of the LISTENER
    
    ------------------------
    
    Alias                     LISTENER
    
    Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
    
    Start Date                28-APR-2011 20:14:56
    
    Uptime                    0 days 0 hr. 23 min. 51 sec
    
    Trace Level               off
    
    Security                  ON: Local OS Authentication
    
    SNMP                      OFF
    
    Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
    
    Listener Log File         /u01/app/oracle/diag/tnslsnr/xxx/listener/alert/log.xml
    
    Listening Endpoints Summary...
    
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx)(PORT=1521)))
    
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
    
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx)(PORT=8080))(PROTOCOL_STACK=(PRESENTATION=HTTP)(SESSION=RAW)))
    
    Services Summary...
    
    Service "PLSExtProc" has 1 instance(s).
    
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    
    Service "XE5" has 1 instance(s).
    
    Instance "XE5", status READY, has 1 handler(s) for this service...
    
    The command completed successfully
    
    本地测试库:
    
    [[email protected] dbs]$ lsnrctl status
    
    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 29-APR-2011 00:35:03
    
    Copyright (c) 1991, 2010, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Enmopri)(PORT=1521)))
    
    STATUS of the LISTENER
    
    ------------------------
    
    Alias                     LISTENER
    
    Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
    
    Start Date                28-APR-2011 16:51:47
    
    Uptime                    0 days 7 hr. 43 min. 16 sec
    
    Trace Level               off
    
    Security                  ON: Local OS Authentication
    
    SNMP                      OFF
    
    Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
    
    Listener Log File         /u01/app/oracle/diag/tnslsnr/Enmopri/listener/alert/log.xml
    
    Listening Endpoints Summary...
    
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Enmopri)(PORT=1521)))
    
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
    
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Enmopri)(PORT=8080))(Presentation=HTTP)(Session=RAW))
    
    Services Summary...
    
    Service "MARSHALL" has 1 instance(s).
    
    Instance "MARSHALL", status READY, has 1 handler(s) for this service...
    
    Service "MARSHALLXDB" has 1 instance(s).
    
    Instance "MARSHALL", status READY, has 1 handler(s) for this service...
     
    
    The command completed successfully

    在本地库中,多一个XDB服务,我们知道在运行APEX时需要XML DB的支持,所以这点差别可能是导致问题出现的原因。

  5. 通过查看监听日志,寻找错误原因:

    使用tailf /u01/app/oracle/diag/tnslsnr/xxx/listener/alert/log.xml命令监视监听log,在浏览器输入apex地址,查看日志变化

    在客户机上:

    
    
    <msg time='2011-04-28T20:39:20.615+08:00' org_id='oracle' comp_id='tnslsnr'  type='UNKNOWN' level='16' host_id='xxx'  host_addr='127.0.0.1'>  <txt>28-APR-2011 20:39:20 * http * (ADDRESS=(PROTOCOL=tcp)(HOST=220.231.12.9)(PORT=63737)) * handoff * http * 12519  </txt>  </msg>  <msg time='2011-04-28T20:39:20.615+08:00' org_id='oracle' comp_id='tnslsnr'  type='UNKNOWN' level='16' host_id='xxx'  host_addr='127.0.0.1'>  <txt>TNS-12519: TNS:no appropriate service handler found  </txt>  </msg>

    本地测试库上:

    <msg time='2011-04-29T00:39:40.673+08:00' org_id='oracle' comp_id='tnslsnr'
    
    type='UNKNOWN' level='16' host_id='Enmopri'
    
    host_addr='127.0.0.1'>
    
    <txt>29-APR-2011 00:39:40 * http * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.160)(PORT=54149)) * handoff * http * 0
    
    </txt>
    
    </msg>
    
    <msg time='2011-04-29T00:40:08.940+08:00' org_id='oracle' comp_id='tnslsnr'
    
    type='UNKNOWN' level='16' host_id='Enmopri'
    
    host_addr='127.0.0.1'>
    
    <txt>29-APR-2011 00:40:08 * service_update * MARSHALL * 0
    
    </txt>
    
    </msg>

    除了上边加黑的部分外,我不觉得两个库上log有何太大区别。但是加黑部分似乎对于我们来说,意义不大。我们再去看看其他配置。

  6. 查看监听服务lsnrctl service

    客户库:

    [[email protected] ~]$ lsnrctl service
     
    
    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 28-APR-2011 21:34:36
    
    Copyright (c) 1991, 2010, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx)(PORT=1521)))
    
    Services Summary...
    
    Service "PLSExtProc" has 1 instance(s).
    
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    
    Handler(s):
    
    "DEDICATED" established:0 refused:0
    
    LOCAL SERVER
    
    Service "XE5" has 1 instance(s).
    
    Instance "XE5", status READY, has 1 handler(s) for this service...
    
    Handler(s):
    
    "DEDICATED" established:0 refused:0 state:ready
    
    LOCAL SERVER 


    本地测试库:

    [[email protected] dbs]$ lsnrctl service
    
    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 29-APR-2011 00:43:18
    
    Copyright (c) 1991, 2010, Oracle.  All rights reserved.
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Enmopri)(PORT=1521)))
    
    Services Summary...
    
    Service "MARSHALL" has 1 instance(s).
    
    Instance "MARSHALL", status READY, has 1 handler(s) for this service...
    
    Handler(s):
    
    "DEDICATED" established:124 refused:0 state:ready
    
    LOCAL SERVER
    
    Service "MARSHALLXDB" has 1 instance(s).
    
     Instance "MARSHALL", status READY, has 1 handler(s) for this service...
    
     Handler(s):
    
     "D000" established:50 refused:0 current:1 max:1022 state:ready
     
    
     DISPATCHER <machine: Enmopri, pid: 8336>
     
    
     (ADDRESS=(PROTOCOL=tcp)(HOST=Enmopri)(PORT=39764))
     
    
    The command completed successfully

    在本地库上,标红的部分引起了我的注意。之前由使用ps –ef查看是否有HTTP服务器进程在运行,一无所获,我们认为可能是 Oracle的某些后台进程或是之类在提供此服务。

    D000

    Dispatchers are optional background processes, present only when the shared server configuration is used.

    原来正是D000后台进程在负责这一部分的工作。

    [[email protected] dbs]$ ps -ef | grep d000
    
    oracle    8336     1 89 Apr28 ?        05:51:21 ora_d000_MARSHALL
    
    oracle   20694 19478  0 00:49 pts/3    00:00:00 grep d000

问题解决

不怕遇到问题,就怕找不到问题根源。只要找到问题根源,解决起来只是修改参数的问题。

  1. 查看数据库参数配置

    在本地测试库上:

    SQL> show parameter dispatchers
    
    NAME                                 TYPE        VALUE
     
    
    ------------------------------------ ----------- ------------------------------
     
    
    dispatchers                          string      (PROTOCOL=TCP) (SERVICE=MARSHA
     
    
     LLXDB)
     
    
    max_dispatchers                      integer
     
    
    在客户库上:
     
    
    SQL> show parameter dispatchers
     
    
    NAME                                 TYPE        VALUE
     
    
    ------------------------------------ ----------- ------------------------------
     
    
    dispatchers                          string
     
    
    max_dispatchers                      integer


  2. 可以选择修改参数文件pfile的方式解决,也可以修改spfile。我选择修改pfile

    将(PROTOCOL=TCP) (SERVICE=MARSHALLXDB)这一部分修改下,如(PROTOCOL=TCP) (SERVICE=<SID>XDB),添加到客户库中的参数文件中。由于不方便贴,我在这贴上本地测试库中的参数:

    *.dispatchers='(PROTOCOL=TCP) (SERVICE=MARSHALLXDB)’

    保存,重启数据库,启动监听,客户库的监听服务中多了一段:

    [[email protected] dbs]$ lsnrctl service
     
    
    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 28-APR-2011 22:10:55
     
    
    Copyright (c) 1991, 2010, Oracle.  All rights reserved.
     
    
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx)(PORT=1521)))
     
    
    Services Summary...
     
    
    Service "PLSExtProc" has 1 instance(s).
     
    
     Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
     
    
     Handler(s):
     
    
     "DEDICATED" established:0 refused:0
     
    
     LOCAL SERVER
     
    
    Service "XE5" has 1 instance(s).
     
    
     Instance "XE5", status READY, has 1 handler(s) for this service...
     
    
     Handler(s):
     
    
     "DEDICATED" established:0 refused:0 state:ready
     
    
     LOCAL SERVER
     
    
    Service "XE5XDB" has 1 instance(s).
     
    
     Instance "XE5", status READY, has 1 handler(s) for this service...
     
    
     Handler(s):
     
    
     "D000" established:0 refused:0 current:0 max:1022 state:ready
     
    
     DISPATCHER <machine: xxx, pid: 16256>
     
    
     (ADDRESS=(PROTOCOL=tcp)(HOST=xxx)(PORT=32426))
     
    
    The command completed successfully


    再访问http://hostname:8080/apex 及http://hostname:8080/apex/apex_admin,成功!

总结:这次迁移时间过长,多数时间花在APEX的配置上,不过倒是对APEX的配置有了较多经验。整理下发出来。其实解决问题从来不会太难(可能我遇到的都比较弱智),我认为最重要的是发现问题的根源,只要发现了问题,解决起来只是时间问题。这个案例中,印证了我一直的判断:网络问题,但是却没有查看监听服务,没有对监听日志进行监控,导致排错时间过长。

引用同事的一句话:我们的工作就是在看log,机器中的任何变化,从有相关的log记录,认真查看,一定会找到问题的根源。

在引用Eygle大师的一句话:根据Oracle文档一步一步去做,如果有问题,那肯定是自己的问题。

普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress