Oracle-APEX组件失效问题
1. 问题现象
在Oracle11.2.0.4版本中发现APEX组件失效
set lines 168 pages 99
col comp_name for a32
select comp_name, version, status, schema from dba_registry where status=‘INVALID‘;
COMP_NAME VERSION STATUS SCHEMA
-------------------------------- ------------------------------ ---------------------- ------------------------------
Oracle Application Express 3.2.1.00.12 INVALID APEX_030200
col object_name for a32
select owner, object_name, object_type from dba_objects where status = ‘INVALID‘;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------------------- -------------------
APEX_030200 WWV_FLOW_HELP PACKAGE BODY
2. 分析
重新编译失效包体
alter package APEX_XXXXXX.WWV_FLOW_HELP compile body;
show err;
col text for a64
select text from dba_errors where name = ‘WWV_FLOW_HELP‘ and owner =‘APEX_030200‘;
输出结果，发现表或视图或包不存在。如下：
SQL> show err
Errors for PACKAGE BODY APEX_030200.WWV_FLOW_HELP:
LINE/COL ERROR
-------- -----------------------------------------------------------------
188/20 PL/SQL: SQL Statement ignored
189/27 PL/SQL: ORA-00942: table or view does not exist
191/13 PL/SQL: Statement ignored
191/13 PLS-00201: identifier ‘CTX_DDL.DROP_PREFERENCE‘ must be declared
196/16 PL/SQL: SQL Statement ignored
197/23 PL/SQL: ORA-00942: table or view does not exist
199/9 PL/SQL: Statement ignored
199/9 PLS-00201: identifier ‘CTX_DDL.DROP_PREFERENCE‘ must be declared
261/5 PL/SQL: Statement ignored
261/5 PLS-00201: identifier ‘CTX_DDL.CREATE_PREFERENCE‘ must be
declared
262/5 PL/SQL: Statement ignored
262/5 PLS-00201: identifier ‘CTX_DDL.SET_ATTRIBUTE‘ must be declared
265/9 PL/SQL: Statement ignored
265/9 PLS-00201: identifier ‘CTX_DDL.SET_ATTRIBUTE‘ must be declared
280/9 PL/SQL: Statement ignored
280/9 PLS-00201: identifier ‘CTX_DDL.CREATE_PREFERENCE‘ must be
declared
292/13 PL/SQL: Statement ignored
292/13 PLS-00201: identifier ‘CTX_DOC.FILTER‘ must be declared
312/13 PL/SQL: Statement ignored
312/13 PLS-00201: identifier ‘CTX_DOC.FILTER‘ must be declared
SQL>
通过Oracle内部包都wrapped加密，不能直接查看包体代码，需要解码
set long 9999
select dbms_metadata.get_ddl(‘PACKAGE_BODY‘,‘WWV_FLOW_HELP‘,‘APEX_030200‘) from dual;
检查相关对象是否存在
set lines 168 pages 99
col object_name for a32
select owner,object_name, object_type, status from dba_objects where object_name in (‘CTX_DDL‘,‘CTX_DOC‘,‘CTX_USER_PREFERENCES‘);
no rows selected
从上面输出结果，发现对象都不存在。到其它数据库检查知道这3个对象都属于CTXSYS 模式
检查故障库用户及其所有组件信息
select username, account_status from dba_users where username=‘CTXSYS‘;
select comp_id, comp_name,VERSION,MODIFIED,PROCEDURE, status from dba_registry where schema=‘CTXSYS‘;
发现该故障库未安装相关对象组件。
总结
至此，问题原因已经浮出水面。由于CTXSYS模式未安装引起APEX组件缺失相关表、视图和包，导致APEX组件中一个包体失效。
3. 解决措施
前期准备
配置环境变量
Platform: Requires path set: ENV variable:
--------------------- --------------------- -------------------
Linux x86-64 YES LD_LIBRARY_PATH
Solaris SPARC64 YES LD_LIBRARY_PATH
IBM AIX YES LIBPATH
HP PA-RISC YES SHLIB_PATH
HP Itanium YES LD_LIBRARY_PATH
- If you have the C Shell (csh or tcsh), enter the following:
$ setenv LD_LIBRARY_PATH $ORACLE_HOME/ctx/lib:$LD_LIBRARY_PATH
- If you have the Bourne shell (sh), Bash shell (bash), or Korn shell (ksh), enter the following:
$ export LD_LIBRARY_PATH=$ORACLE_HOME/ctx/lib:$LD_LIBRARY_PATH
Run the following command to check if the LD_LIBRARY_PATH environmental variable is set correctly:
$ echo $LD_LIBRARY_PATH
CTXSYS用户所需权限
需要执行DBMS_LOB and DBMS_SCHEDULER的权限（默认授权给PUBLIC）
grant execute on DBMS_SCHEDULER to PUBLIC;
grant execute on DBMS_JOB to PUBLIC;
grant execute on UTL_FILE to PUBLIC;
grant execute on UTL_HTTP to PUBLIC;
-- 确认
select owner, object_name, object_type,status from dba_objects where owner=‘PUBLIC‘ and object_name in (‘DBMS_SCHEDULER‘, ‘DBMS_JOB‘, ‘UTL_FILE‘, ‘UTL_HTTP‘) ;
安装Oracle Text组件
创建CTXSYS用户和组件
sqlplus / as sysdba
spool text_install.txt
@?/ctx/admin/catctx.sql change_on_install SYSAUX TEMP NOLOCK
- change_on_install 是ctxsys 用户密码
- SYSAUX 是ctxsys默认表空间
设置默认语言
connect "CTXSYS"/"change_on_install"
@?/ctx/admin/defaults/dr0defin.sql "AMERICAN";
conn / as sysdba
alter user ctxsys account lock password expire;
spool off
结果检查
connect / as SYSDBA
set pages 1000
col object_name format a40
col object_type format a20
col comp_name format a30
column library_name format a8
column file_spec format a60 wrap
spool text_install_verification.log
-- check on setup
select comp_name, status, substr(version,1,10) as version from dba_registry where comp_id = ‘CONTEXT‘;
select * from ctxsys.ctx_version;
select substr(ctxsys.dri_version,1,10) VER_CODE from dual;
select count(*) from dba_objects where owner=‘CTXSYS‘;
-- Get a summary count
select object_type, count(*) from dba_objects where owner=‘CTXSYS‘ group by object_type;
-- Any invalid objects
select object_name, object_type, status from dba_objects where owner=‘CTXSYS‘ and status != ‘VALID‘ order by object_name;
spool off