ORACLE EBS 启用REST服务-1-环境安装篇_oracle apex reset

目前有一套R12的EBS的环境（12.1.3），要同外围系统做REST对接，目前系统是启用了Oracle E-Business Suite Integrated SOA Gateway (ISG) ，但是只支持SOAP风格的webservice，可以理解为基于XML形式来传送，如果要支持REST风格需要做一个较大的版本升级，这个系统是个核心系统，基于成本和稳定性考虑决定不做版本升级，启用ORDS组件来实现发布RESTFUL，ORDS + SQL DEVELOPER也能实现这个服务，但是用 SQL DEVELOPER 开发REST服务界面有点不友好，再加上新版的APEX封装了一些操作JSON的API，对开发者来说甚是方便，因此考虑用APEX来作为开发RESTFUL服务的前端界面，以下是实现过程，本文分两个章节，第一部分是环境搭建篇，第二部分是开发篇。此篇是第一部分。
参考文章：
https://blog.csdn.net/x6_9x/article/details/104873963
https://docs.oracle.com/en/database/oracle/application-express/20.2/index.html

整体架构如下

ORDS 实现原理

和EBS集成后的架构

应用层

数据库层：

环境准备

软件版本及环境如下

系统：Oracle Linux 6
数据库：11.2.0.3
Tomcat:9.0.45
JDk: ORACLE JDK 16
ORDS: ords-20.4.3.050.1904
AEPX :20.2
1
2
3
4
5
6

上面几个在apache官网和Oracle官网都可以方便下载到，不愿意下载的铁子，给我留言，私下传送。

安装

--创建安装表空间
sqlplus / as sysdba

--创建安装表空间
SQL> CREATE TABLESPACE apex_install DATAFILE '/data/ebs/uat/db/apps_st/data/apex_install.dbf' SIZE 1G AUTOEXTEND ON NEXT 1M  maxsize 4G;

Tablespace created.
--创建专用临时表空间
SQL> create temporary tablespace temp_apex tempfile '/data/ebs/uat/db/apps_st/data/temp_apex.dbf' size 1G reuse autoextend on next 1m maxsize 4G;

#执行安装命令
@apexins.sql apex_install apex_install temp_apex /i/

#
# Actions in Phase 3:
#
    ok 1 - BEGIN                                                        |   0.00
    ok 2 - Computing Pub Syn Dependents                                 |   0.00
    ok 3 - Upgrade Hot Metadata and Switch Schemas                      |   0.02
    ok 4 - Removing Jobs                                                |   0.00
    ok 5 - Creating Public Synonyms                                     |   0.03
    ok 6 - Granting Public Synonyms                                     |   0.05
    ok 7 - Granting to FLOWS_FILES                                      |   0.00
    ok 8 - Creating FLOWS_FILES grants and synonyms                     |   0.00
    ok 9 - Creating Jobs                                                |   0.00
    ok 10 - Creating Dev Jobs                                           |   0.00
    ok 11 - Installing FLOWS_FILES Objects                              |   0.00
    ok 12 - Installing APEX$SESSION Context                             |   0.00
    ok 13 - Recompiling APEX_200200                                     |   0.02
    ok 14 - Installing APEX REST Config                                 |   0.00
    ok 15 - Set Loaded/Upgraded in Registry                             |  11.12
    ok 16 - Removing Unused SYS Objects                                 |   0.00
    ok 17 - Validating Installation                                     |   0.08
ok 3 - 17 actions passed, 0 actions failed                              |  11.32

PL/SQL procedure successfully completed.

Thank you for installing Oracle Application Express 20.2.0.00.20

Oracle Application Express is installed in the APEX_200200 schema.

The structure of the link to the Application Express administration services is as follows:
http://host:port/ords/apex_admin

The structure of the link to the Application Express development interface is as follows:
http://host:port/ords

timing for: Phase 3 (Switch)
Elapsed: 00:11:18.36
timing for: Complete Installation
Elapsed: 00:20:42.60

PL/SQL procedure successfully completed.

1 row selected.
#经过了二十多分钟后终于创建完成,

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

unlock APEX_PUBLIC_USER

记住这个密码后面安装ORDS要用

SYS> alter user APEX_PUBLIC_USER identified by "xxxxxx" account unlock;

用户已更改。

SYS>
1
2
3
4
5
6

Create the APEX Instance Administration user and set the password

SQL> @apxchpwd.sql
...set_appun.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] ebs_admin@yangshixian.com
Enter ADMIN's password [] （此处要复杂密码1x2x34xxxxqwer!@#$xx）
Created instance administrator ADMIN.

1
2
3
4
5
6
7
8
9
10
11
12
13

配置RESTFUL 服务

SQL> @apex_rest_config.sql

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Enter a password for the APEX_LISTENER user              [] 
Enter a password for the APEX_REST_PUBLIC_USER user              [] 
...set_appun.sql
...setting session environment
--验证
SQL> conn APEX_LISTENER/XXXXXXX
Connected.
SQL> conn APEX_REST_PUBLIC_USER/XXXXXXXXXX
Connected.
SQL> exit

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

网络权限更改

sqlplus "/ as sysdba"

DECLARE
  ACL_PATH  VARCHAR2(4000);
BEGIN
  -- Look for the ACL currently assigned to '*' and give APEX_200200
  -- the "connect" privilege if APEX_200200 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_200200',
     'connect') IS NULL THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
     'APEX_200200', 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_200200', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

安装apex 汉化包

cd $ORACLE_HOME/apex/builder/zh-cn
sqlplus / as sysdba
alter session set current_schema=APEX_200200;
@load_zh-cn.sql
#汉化的时间比较长，可以让运行着，不用等跑完，然后去应用服务器配置下面的步骤就好了
1
2
3
4
5

创建数据库用户ords_admin

这个用户是用来安装ORDS用的，之前ORDS的版本是用sys用户安装的，当然可以不做这一步，直接用sys来安装，生产环境还是建议做这个，权限最小化原则。

create user ords_admin
  identified by xxxxxxxxx
  default tablespace APEX_INSTALL
  temporary tablespace TEMP_APEX;

1
2
3
4
5

给ORDS管理用户授权

需要将这个脚本上传到数据库服务器上，这个脚本在ords的installer目录下

--到数据库服务器，用sysdba登录
SQL> @ords_installer_privileges.sql
Enter value for 1: ords_admin
1
2
3

应用侧-配置ORDS+TOMCAL

创建操作系统专用用户及目录

root用户操作：

useradd -g dba tomcat
passwd tomcat
mkdir -p /data/ords
mkdir -p /data/tomcat
mkdir -p /data/jdk16
chown tomcat:dba /data/ords
chown tomcat:dba /data/tomcat
chown tomcat:dba /data/jdk16

1
2
3
4
5
6
7
8
9

配置tomcat用户的jdk - 后续为tomcat用户操作：

#将ords-20.4.3.050.1904.zip 上传到 /data/ords 解压
#将apache-tomcat-9.0.45.tar.gz、apex_20.2.zip 上传到/data/tomcat 各自解压
#将jdk-16.0.1_linux-x64_bin.tar.gz上传到/data/jdk16解压

配置jdk

vi $HOME/.bashrc

export JAVA_HOME=/data/jdk16/jdk-16.0.1
export CLASSPATH=$JAVA_HOME/lib/
export PATH=$JAVA_HOME/bin:$PATH

#重载环境变量
. ~/.bash_profile 
#确认结果
[tomcat@ebstest ~]$ which java
/data/jdk16/jdk-16.0.1/bin/java
[tomcat@ebstest ~]$ java -version
java version "16.0.1" 2021-04-20
Java(TM) SE Runtime Environment (build 16.0.1+9-24)
Java HotSpot(TM) 64-Bit Server VM (build 16.0.1+9-24, mixed mode, sharing)
[tomcat@ebstest ~]$ 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

配置ords

--如果已经安装过，则卸载
#java -jar ords.war uninstall

[tomcat@ebstest ords]$ cd /data/ords
[tomcat@ebstest 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: config
Specify the database connection type to use.
Enter number for [1] Basic  [2] TNS  [3] Custom URL [1]:
Enter the name of the database server [localhost]:yangshixian-ebs-test-db
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name:UAT
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [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:ords_admin
Enter the database password for ords_admin:
Confirm password:
Connecting to database user: ords_admin url: jdbc:oracle:thin:@//yangshixian-ebs-test-db:1521/UAT

Retrieving information.
Enter the default tablespace for ORDS_METADATA [SYSAUX]:APEX_INSTALL
Enter the temporary tablespace for ORDS_METADATA [TEMP1]:TEMP_APEX
Enter the default tablespace for ORDS_PUBLIC_USER [SYSAUX]:APEX_INSTALL
Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP1]:TEMP_APEX
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]: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]:
Enter the database password for APEX_LISTENER:
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:
Confirm password:
Enter a number to select a feature to enable:
   [1] SQL Developer Web  (Enables all features)
   [2] REST Enabled SQL
   [3] Database API
   [4] REST Enabled SQL and Database API
   [5] None
Choose [1]:
2021-04-22T05:43:10.479Z INFO        reloaded pools: []
Installing Oracle REST Data Services version 20.4.3.r0501904
... Log file written to /home/tomcat/ords_install_core_2021-04-22_134310_00605.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/tomcat/ords_install_datamodel_2021-04-22_134331_00645.log
... Log file written to /home/tomcat/ords_install_apex_2021-04-22_134332_00859.log
Completed installation for Oracle REST Data Services version 20.4.3.r0501904. Elapsed time: 00:00:23.882 

Enter 1 if you wish to start in standalone mode or 2 to exit [1]:2
[tomcat@ebstest ords]$  cp ords.war ../tomcat/apache-tomcat-9.0.45/webapps/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

配置tomcat

将apex下面的images目录拷贝过去：

/data/tomcat/apex
cp -r images/ ../apache-tomcat-9.0.45/webapps/i
1
2

启动Tomcat

切换到bin目录下

cd /data/tomcat/apache-tomcat-9.0.45/bin
[tomcat@ebstest bin]$ pwd
/data/tomcat/apache-tomcat-9.0.45/bin
[tomcat@ebstest bin]$ ./startup.sh 
Using CATALINA_BASE:   /data/tomcat/apache-tomcat-9.0.45
Using CATALINA_HOME:   /data/tomcat/apache-tomcat-9.0.45
Using CATALINA_TMPDIR: /data/tomcat/apache-tomcat-9.0.45/temp
Using JRE_HOME:        /data/jdk16/jdk-16.0.1
Using CLASSPATH:       /data/tomcat/apache-tomcat-9.0.45/bin/bootstrap.jar:/data/tomcat/apache-tomcat-9.0.45/bin/tomcat-juli.jar
Using CATALINA_OPTS:   
Tomcat started.
[tomcat@ebstest bin]$ 
#关闭脚本为 shutdown.sh
1
2
3
4
5
6
7
8
9
10
11
12
13

前端配置

访问URL

http://ebssit.prefix.yangshixian.com:8080/ords

目前EBS数据库版本为11.2.0.3 ，执行汉化脚本后，不显示语言切换按钮，需要手工加入参数&p_lang=zh-cn，如http://ebssit.prefix.yangshixian.com:8080/ords/f?p=4550:1:8662650661477:::::&p_lang=zh-cn