Oracle Application Express安装手册

1. APEX安装步骤

1. 1. 软件版本

1. 1. 1. 安装相关软件版本信息说明

1. Oracle Application Express(All Languages) 20.1
2. Oracle REST Data Services 19.2
3. OS version CentOS release 6.5(Final)
4. Apache tomcat 8.5.55

1. 1. APEX安装

1. 1. 1. 系统备份

apex安装前做一个数据层的全备份，db software采用gtar备份，数据部分使用rman备份

1. 1. 1. 删除老的apex 3.2

11gr2自带3.2版本的apex，先将其删除

su - oradev

cd $ORACLE_HOME/apex

sqlplus '/as sysdba'

@?/apex/apxremov.sql

执行结果如下：

...Removing Application Express

old   1: alter session set current_schema = &APPUN

new   1: alter session set current_schema = APEX_030200

ERROR:

ORA-01435: user does not exist

提示显示user APEX_030200不存在，当前ebs系统未安装apex

1. 1. 1. 备份旧的apex目录

cd $ORACLE_HOME

mv apex apex.3.2

1. 1. 1. 创建apex使用的表空间

create tablespace apex_data datafile '/home/JVDEV/db/data/apex_data01.dbf' size 2000m autoextend on maxsize 8000M;

1. 1. 1. 执行安装

上传并解压apex 20.1（全语言版本）

cd /u01/apex

unzip -od $ORACLE_HOME  apex_20.1.zip

安装APEX，指定默认表空间和静态文件别名

cd $ORACLE_HOME/apex

sqlplus '/as sysdba'

@apexins.sql apex_data apex_data temp /i/

此脚本创建的用户：

APEX_200100  -此帐户拥有Application Express架构和元数据。

FLOWS_FILES -此帐户拥有Application Express上传的文件。

APEX_PUBLIC_USER -此最低特权帐户用于通过Oracle REST数据服务或Oracle HTTP Server和Oracle进行Application Express配置mod_plsql。

APEX_INSTANCE_ADMIN_USER -用于REST管理界面的最低特权帐户。

如果配置了RESTful Web服务，则将创建以下其他帐户：

APEX_REST_PUBLIC_USER -调用存储在Oracle Application Express中的RESTful服务定义时使用的帐户。

APEX_LISTENER -用于查询存储在Oracle Application Express中的RESTful服务定义的帐户。

脚本执行日志如下：

 

1. 1. 1. 设置admin及相关账户

创建并修改实例管理员账户(instance administrator account)

-- 创建APEX实例管理员（Instance Administration）及密码，这个密码必须包含特殊符号，否则设置不上。这个密码很重要，是管理APEX平台的账号密码，以后创建新的应用schema、解锁账号等都靠它，第一次登录APEX时也要用到。

sqlplus '/as sysdba'

@?/apex/apxchpwd.sql

SQL> @?/apex/apxchpwd.sql

================================================================================

This script can be used to change the password of an Application Express

instance administrator. If the user does not yet exist, a user record will be

created.

================================================================================

Enter the administrator's username [ADMIN]

User "ADMIN" does not yet exist and will be created.

Enter ADMIN's email [ADMIN]

Enter ADMIN's password []  -- 密码为 Aperoracle03#

Created instance administrator ADMIN.

-- 配置RESTful Services服务

SQL> @apex_rest_config.sql

Enter a password for the APEX_LISTENER user              [] 密码为 Aperoracle03#

Enter a password for the APEX_REST_PUBLIC_USER user              [] 密码为 Aperoracle03#

...set_appun.sql

...setting session environment

...create APEX_LISTENER and APEX_REST_PUBLIC_USER users

--解锁并修改 apex_public_user账户密码

alter user apex_public_user account unlock;

alter user apex_public_user identified by Aperoracle03#;

1. 1. 1. 安装apex简体中文

检查数据库字符集是否支持中文语言

select * from v$nls_parameters where parameter ='NLS_CHARACTERSET';

-- AL32UTF8

设置oradev用户字符集变量，字符集变量必须设置为 American_America.AL32UTF8

检查oradev用户变量

echo $NLS_LANG

American_America.AL32UTF8

无需设置

cd $ORACLE_HOME/apex/builder/zh-cn

sqlplus '/as sysdba'

ALTER SESSION SET CURRENT_SCHEMA = APEX_200100;

@load_zh-cn.sql

安装日志：

--application/pages/page_01320

--application/pages/page_03000

--application/pages/page_03010

--application/pages/page_03020

--application/pages/page_03030

--application/pages/page_03040

--application/pages/page_03050

--application/pages/page_03060

--application/pages/page_04020

--application/pages/page_04030

--application/pages/page_04040

--application/pages/page_08000

--application/end_environment

...done

Adjust instance settings

PL/SQL procedure successfully completed.

1. 1. Tomcat安装
2. 创建tomcat安装用户

useradd -g dba -d /home/appapex -m appapex

修改密码为  appapex/Aperoracle03#

1. 创建tomcat安装目录

mkdir -p /u01/apex

上传文件至服务器

sftp apache-tomcat-7.0.85.zip to /u01/apex

解压

cd /u01/apex

unzip apache-tomcat-7.0.85.zip

mv apache-tomcat-7.0.85 tomcat

1. tomcat默认端口修改(根据需要更改)

vi /u01/apex/tomcat/conf/server.xml

8080->8090

8005不变

8009->8019

8443->8453

1. 启动tomcat

启动tomcat

cd /u01/apex/tomcat/bin

nohup ./startup.sh &

访问tomcat console

http://10.89.181.20:8080

能打开tomcat欢迎页面即可

1. 1. 安装ORDS

1. 1. 1. 集成Oracle REST Data Services

上传ords文件至服务器

mkdir -p /u01/apex/ords

解压

cd /u01/apex/ords

unzip ords-19.2.0.199.1647.zip

拷贝apex/images目录到ORDS文件夹

cp -R /home/JVDEV/db/11.2.0/apex/images/  /u01/apex/ords

注意：

如果之前使用的是 embedded PL/SQL gateway，而现在想改成Oracle REST Data Services，那么需要禁用Oracle XML DB Protocol Server

sqlplus '/as sysdba'

sql>exec dbms_xdb.sethttpport(0);

此处我们之前没有配置过embedded PL/SQL gateway，鉴于可能会对ebs系统的http访问造成影响，暂时先不修改

------

1. 1. 1. 启用apex的network services

默认的11.2.0.4是未开启network services，若授权失败可能会有以下3点影响：

1、影响apex邮件发送

2、apex的web services集成

3、pdf文件打印

启用授权

sqlplus '/as sysdba'

执行以下语句：

DECLARE

  ACL_PATH  VARCHAR2(4000);

BEGIN

  -- Look for the ACL currently assigned to '*' and give APEX_200100

  -- the "connect" privilege if APEX_200100 does not have the privilege yet.

 

  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS

   WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

 

  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_200100',

     'connect') IS NULL THEN

      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,

     'APEX_200100', TRUE, 'connect');

  END IF;

 

EXCEPTION

  -- When no ACL has been assigned to '*'.

  WHEN NO_DATA_FOUND THEN

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',

    'ACL that lets power users to connect to everywhere',

    'APEX_200100', TRUE, 'connect');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');

END;

/

COMMIT;

1. 1. 1. 执行安装ORDS

安装前数据库owpwd 建立用户密码文件

Alter user system identified by manager;

Alter user sys identified by manager;

orapwd file=orapwJVDEV password=manager entries=5

安装密码统一：Aperoracle03#

cd /u01/apex/ords

使用高级安装：

[appapex@oracle03 ords]$ java -jar ords.war install advanced

This Oracle REST Data Services instance has not yet been configured.

Please complete the following prompts

Enter the location to store configuration data: /u01/apex/ords/config

Enter the name of the database server [localhost]:oracle03.didichuxing.com

Enter the database listen port [1521]:1571

Enter 1 to specify the database service name, or 2 to specify the database SID [1]:1

Enter the database service name:JVDEV

Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:1

Enter the database password for ORDS_PUBLIC_USER:

Confirm password:

Requires to login with administrator privileges to verify Oracle REST Data Services schema.

Enter the administrator username:sys

Enter the database password for SYS AS SYSDBA:

Confirm password:

Retrieving information.

Enter the default tablespace for ORDS_METADATA [SYSAUX]:APEX_DATA

May 22, 2020 11:30:18 AM oracle.dbtools.rt.config.setup.CdbPdbSetup isValidTablespace

INFO: Error the temporary tablespace TEMP does not exist. An existing tablespace is required for ORDS installation.

Enter the temporary tablespace for ORDS_METADATA [TEMP]:TEMP

May 22, 2020 11:30:59 AM oracle.dbtools.rt.config.setup.CdbPdbSetup isValidTablespace

Enter the default tablespace for ORDS_PUBLIC_USER [SYSAUX]:APEX_DATA

May 22, 2020 11:34:39 AM oracle.dbtools.rt.config.setup.CdbPdbSetup isValidTablespace

Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]:TEMP

Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.

If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:

Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:

Enter the database password for APEX_PUBLIC_USER:

Confirm password:

Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:1

Enter the database password for APEX_LISTENER:

Confirm password:

Enter the database password for APEX_REST_PUBLIC_USER:

Confirm password:

May 22, 2020 11:38:19 AM 

INFO: reloaded pools: []

Installing Oracle REST Data Services version 19.2.0.r1991647

... Log file written to /home/appapex/ords_install_core_2020-05-22_113819_00774.log

... Verified database prerequisites

... Created Oracle REST Data Services proxy user

... Created Oracle REST Data Services schema

... Granted privileges to Oracle REST Data Services

... Created Oracle REST Data Services database objects

... Log file written to /home/appapex/ords_install_datamodel_2020-05-22_113831_00043.log

... Log file written to /home/appapex/ords_install_apex_2020-05-22_113832_00293.log

Completed installation for Oracle REST Data Services version 19.2.0.r1991647. Elapsed time: 00:00:13.827

Enter 1 if you wish to start in standalone mode or 2 to exit [1]:2 （不启用standclone模式）

1. 1. 1. 校验ORDS

执行以下

java -jar ords.war validate

[appapex@erp02 ords]$ java -jar ords.war validate

Enter the name of the database server [erp02.didichuxing.com]:

Enter the database listen port [1601]:

Enter the database service name [DEV02]:

Please login with SYSDBA privileges to verify Oracle REST Data Services schema.

Enter the username with SYSDBA privileges to verify the installation [SYS]:          

Enter the database password for SYS:

Confirm password:

Oracle REST Data Services will be validated.

Validating Oracle REST Data Services schema version 3.0.12.263.15.32

... Log file written to /u01/apex/ords/logs/ords_validate_core_2018-03-16_155354_00126.log

Completed validating Oracle REST Data Services version 3.0.12.263.15.32.  Elapsed time: 00:00:03.17

1. 1. 1. 调整数据库job_queue_processes参数

JOB_QUEUE_PROCESSES determine the maximum number of concurrently running jobs. In

Oracle Application Express transactional support and SQL scripts require jobs. If

JOB_QUEUE_PROCESSES is not enabled and working properly, you cannot successfully

execute a script.

当前设置为2，我们调整为20

alter system set job_queue_processes=20;

当前使用为pfile，修改数据库参数文件 $ORACLE_HOME/initDEV02.ora，待下次重启后生效

1. 1. 1. 将ORDS部署至Tomcat

mkdir -p /u01/apex/tomcat/webapps/i

cp -R /u01/apex/ords/images/* /u01/apex/tomcat/webapps/i/.

cp  /u01/apex/ords/ords.war /u01/apex/tomcat/webapps/

启动tomcat

su - appapex

cd /u01/apex/tomcat/bin

./startup.sh

1. 1. 1. 测试访问

http://192.168.12.8:8080/ords/apex_admin

http://192.168.12.8080/ords

admin/Aperoracle03#

1. 1. APEX和EBS集成注意事项

1. Oracle不建议apex直接使用APPS来进行相关的开发工作。使用apex在ebs中进行开发，请单独定义一个XX_APEX的workspace和schema来进行开发。

Oracle E-Business Suite uses a schema called APPS, which has full access to the complete Oracle E-Business Suite data model.  As stated earlier, associating an Oracle APEX workspace directly with the APPS schema is not supported.  Instead, create at least one separate schema in the Oracle E-Business Suite database for developing your Oracle APEX applications. In this document a custom schema XX_APEX is created. As per Oracle E-Business Suite development standards, any “custom object created in the database should be prefixed with XX, YY, or ZZ. XX is the most popular prefix used, and is the prefix used in this paper, hence, the custom schema is called XX_APEX.

Defining the Oracle APEX Workspace and Schema Based on the recommended database architecture, you should create a workspace called XX_APEX and a custom schema called XX_APEX on the database server where Oracle E-Business Suite is installed. If you already have a custom schema defined you can also associate that schema with an Oracle APEX workspace. However, if you plan to develop Application Express on a different server then skip this section and follow the steps outlined in Configuring Oracle APEX in a Different Database.

1. 若使用的EBS版本是12.2以上，需要将该schema启用EBR（Edition-Based Redefinition），以适用于12.2的Online Patching

The above steps will create a database schema, XX_APEX, with the correct permissions for building local database objects. However, if you are running Oracle E-Business Suite Release 12.2 or above, then you must update this schema to enable Edition-Based Redefinition (EBR). This is required as the APPS schema is enabled in order to achieve E-Business Suite Online Patching, and you will receive Oracle database errors if you do not also enable your custom schema.  Enable Edition-Based Redefinition by performing the following steps: 1. Connect to your Oracle E-Business Suite Database server

2. Start SQL*Plus and connect as SYS specifying the SYSDBA role

3. Alter the APEX schema:

alter user XX_APEX enable editions;

{This functionality is incorporated into the apex_ebs_setup.sql script referenced later in this paper}

1. 若使用的EBS版本是12.2以上，需要将该schema在EBS R12.2 中注册

If you are running Oracle E-Business Suite Release 12.2 or above then you will also need to register this schema within E-Business Suite. Information on how to register the schema is available in the Oracle E-Business R12.2 Suite Maintenance Guide, Chapter 6 “Basic DBA Tasks”, section “Registering an ORACLE Schema”. The documentation references MOS Knowledge Base - Creating a Custom Application in Oracle EBusiness Suite Release 12.2 (Doc ID 1577707.1)

1. apex原则上访问ebs数据，需要通过视图来访问

Oracle APEX provides numerous wizards to rapidly build application components on existing Oracle tables or views.  However, a prerequisite of many wizards within Oracle APEX is that the underlying tables or views contain primary key constraints.  Given Oracle E-Business Suite tables and views do not typically include primary key constraints, it is advisable to define additional database views that incorporate primary key constraints for the Oracle Applications objects you wish to access.  For security purposes, it is recommended that Oracle E-Business Suite data is accessed only through these views.  The views can incorporate security, joins, etc., and prevent Oracle APEX applications from making any unauthorized changes to the underlying data. 

1. 禁止直接通过apex更新ebs的数据表。除非官网文档直接说明允许这样操作

Updating Oracle E-Business Suite Tables

Although it may be tempting to insert/update/delete records on Oracle E-Business Suite tables directly, this is unsupported and must be avoided.  Please keep in mind that direct updates to Oracle E-Business Suite tables is not supported unless explicitly documented.  Direct updates will bypass validation, security and business logic which could lead to data corruption or unexpected system behavior.  Oracle recommends that all Oracle EBusiness Suite updates are made through calls to public APIs.  The Oracle E-Business Suite Integration Repository provides a searchable list of public APIs for the system. 

1. 1. APEX和EBS应用集成设置

待补充

1. 1. 后续操作

1. 1. 1. 创建apex_ebs_extension工作区和用户

 

 

 

 

1. 1. 1. 启用apex_ebs_extension用户的EBR

su – oratest

sqlplus '/as sysdba'

alter user dd_apex enable editions;

在EBS 中注册，创建的XX_APEX 用户。见EBS 数据库注册文档

Creating a Custom Application in Oracle EBusiness Suite Release 12.2 (Doc ID 1577707.1)

1. 1. 1. 创建相关views

示例：

conn apps/<apps_pwd>

CREATE OR REPLACE VIEW xx_apex_ebs_user

( user_id

, user_name

, start_date

, end_date

, description

, email_address

, user_guid

, person_party_id

, CONSTRAINT APEX_EBS_USER_pk

PRIMARY KEY (user_id)

RELY DISABLE NOVALIDATE

)

AS

SELECT user_id

, user_name

, start_date

, end_date

, description

, email_address

, user_guid /* Used for Single-Sign On */

, person_party_id /* FK to party information */

FROM fnd_user;

GRANT SELECT ON xx_apex_ebs_user TO XX_APEX;

GRANT SELECT on fnd_responsibility_vl TO XX_APEX;

conn xx_apex /<pwd>

/*  should executed by apex_ebs_extension user

CREATE OR REPLACE VIEW xx_apex_ebs_user AS

SELECT * from apps.xx_apex_ebs_user;*/

1. 1. 1. 安装Sample Code

sqlplus '/as sysdba'

@apex_ebs_setup.sql   --包、api sample

@apex_ebs_application_rev3.sql –the sample application perform

Sqlplus apps/apps

@apex_ebs_env.sql   --设置环境变量

在XX_APEX 下创建的表，需要同步到APPS 下

4. Connect to your Oracle E-Business Suite Database server 5. Start SQL*Plus and connect as APPS 6. Execute the following command:

exec AD_ZD_TABLE.UPGRADE('XX_APEX','XX_CUST_TABLE');