Oracle APEX19安装部署

[[Oracle APEX]]

安装

整体架构

OsXM1-2024-08-16-15-02-53

前置条件

ssh免密,改端口,禁用root登录
防火墙配置
添加用户,赋权
Oracle 数据库本次是四个对应不同连接
1. Oracle 19c数据库 test162 (非 CDB/PDB模式)
2. Oracle 19c数据库 testdb (CDB/PDB模式 165)
3. Oracle 19c数据库 test163 (CDB/PDB模式)
4. Oracle 19c数据库 test164 (CDB/PDB模式)
Oracle 驱动环境Oracle Client 下载 - 官方安装参考
SQLcl (可选) 官方文档
jdk-16 Oracle JDK 下载
tomcat-9.0.45 Tomcat 9 下载
nginx-1.19.9 下载 wget http://nginx.org/download/nginx-1.19.10.tar.gz
ords 下载地址 - 官方文档
APEX 下载地址 - 官方文档
检查 Oracle字符集 NLS_CHARACTERSET AL32UTF8 后面会涉及到中文乱码

1

select * from nls_database_parameters where parameter like '%CHARACTERSET%';

设计多个数据库路径
1. test162 对应url http://xxxx.com/apex_test/test162/f?p=4000
2. testdb 对应url http://xxxx.com/apex_test/testdb/f?p=4000
3. test163 对应url http://xxxx.com/apex_test/test163/f?p=4000
4. test164 对应url http://xxxx.com/apex_test/test164/f?p=4000

检查环境依赖版本

Oracle DB要求

open_cursors=1000;
命名长度128 文档
支持varchar2 超过4000 文档
字符集UTF8
准备 user 90天过期的profile,对外提供的普通账号文档
准备 user 一年过期的profile,用于平台系统服务账号文档
指定 JOB_QUEUE_PROCESSES = cpu* 20 文档
主备库实例SID/Service Name 名字一致
例如:
主库10.xxxx.xxxx.xxxx:xxxx/apexdev
备库10.xxxx.xxxx.xxxx:xxxx/apexdev

https://docs.oracle.com/en/database/oracle/apex/23.2/htmig/apex-installation-requirements.html#
Oracle APEX requires the system global area (SGA) and program global area (PGA) to be at least 300 MB.
Databases typically use automatic memory management, where the memory can be controlled by the server parameter MEMORY_TARGET. If your database does not use automatic memory management, consult the Oracle Database Administrator’s Guide to find out how to configure manual memory parameters (for example, SGA_TARGET, PGA_AGGREGATE_TARGET, SHARED_POOL_SIZE) instead, for a similar result.

Tomcat(Java,tomcat, Oracle Client配置)安装

SSH 免密

1

[root@xxxxxxxxxx ~]# ssh-copy-id xxx@xx.xx.xx.xx

JAVA

安装
Oracle JDK 下载
 安装说明

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


[root@xxxxxxxxxx ~]# rpm -ivh jdk-16_linux-x64_bin.rpm
[root@xxxxxxxxxx ~]# find / -name jav*|grep jdk 
...
/usr/java/jdk-16/bin/java
...
<!--设置环境变量-->
[root@xxxxxxxxxx ~]#  vim  /etc/profile
...
export JAVA_HOME="/usr/java/jdk-16"
export JAVA_BIN=$JAVA_HOME/bin
export PATH=$JAVA_BIN:$JAVA_HOME:$PATH
<!--生效环境变量-->
[root@xxxxxxxxxx ~]#  source ~/.bash_profile 
<!--验证环境变量-->
[root@xxxxxxxxxx ~]#  java -version

升级

1
2
3


[root@xxxxxxxxxx ~]# java -version
[root@xxxxxxxxxx ~]# rpm -e java-16-openjdk
[root@xxxxxxxxxx ~]# rpm -ivh jdk-21_linux-x64_bin.rpm

Tomcat安装

安装 Tomcat 9 下载

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22


[root@xxxxxxxxxx ~]#  unzip  apache-tomcat-9.0.45.zip 
# 创建tomcat运行用户
useradd -d /opt/tomcat -s /bin/nologin tomcat
useradd -M -s /bin/nologin tomcat

mv ./apache-tomcat-9.0.45 /opt/tomcat

# 赋予可执行权限
chmod +x /opt/tomcat/bin/catalina.sh
chmod +x /opt/tomcat/bin/startup.sh
chmod +x /opt/tomcat/bin/shutdown.sh

# 更改文件权限
chown -R tomcat:tomcat /opt/tomcat

# 设置环境变量
vim  /etc/profile 
...
export CATALINA_HOME=/opt/tomcat
export CATALINA_BASE=/opt/tomcat
# 生效环境变量
source ~/.bash_profile

设置tomcat后台进程及自启参考文档vim /etc/systemd/system/tomcat.service TODO 参数可能要改

 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


[Unit]
Description=Apache Tomcat 9 Servlet Container
Wants=network.target
After=network.target

[Service]
User=tomcat
Group=tomcat
Type=forking
UMask=0007
RestartSec=10

Environment=CATALINA_PID=/opt/tomcat/tomcat.pid
Environment=CATALINA_HOME=/opt/tomcat
Environment=CATALINA_BASE=/opt/tomcat
Environment=JAVA_HOME=/usr/java/jdk-16
Environment='CATALINA_OPTS=-Xms512M -Xmx1G -Djava.net.preferIPv4Stack=true'
Environment='JAVA_OPTS=-Djava.awt.headless=true'

ExecStart=/opt/tomcat/bin/startup.sh
ExecStop=/opt/tomcat/bin/shutdown.sh
SuccessExitStatus=143
Restart=on-failure

[Install]
WantedBy=multi-user.target

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


[root@xxxxxxxxxx ~]#  systemctl start tomcat
# <!--自启-->
[root@xxxxxxxxxx ~]#  systemctl enable tomcat
# <!--注册自启-->
[root@xxxxxxxxxx ~]#  systemctl daemon-reload
# <!--验证监听-->
[root@xxxxxxxxxx ~]#  netstat -antup | grep 8080
# <!--验证防火墙-->
[root@xxxxxxxxxx ~]#  firewall-cmd --list-all
[root@xxxxxxxxxx ~]#  firewall-cmd --permanent --add-port=8080/tcp
[root@xxxxxxxxxx ~]#  firewall-cmd --reload

添加后端加密(Nginx → tomcat启用ssl证书 )
用户 → Nginx（反向代理） → Tomcat（启用 HTTPS + 客户端证书认证)

生成自签证书后续Nginx配置也要用到后续Nginx配置也要用到

1
2
3
4
5
6


# 生成20年的自签证书
[root@xxxxxxxxx ~]# keytool -genkeypair -alias tomcat -keyalg RSA -keystore /opt/tomcat.keystore -validity 7300 -storepass changeit
Generating 3,072 bit RSA key pair and self-signed certificate (SHA384withRSA) with a validity of 7,300 days 
    for: CN=pan, OU=Data Engineering, O=Company Ltd., L=shanghai, ST=shanghai, C=CN  
# 授权
[root@xxxxxxxxx ~]# chown tomcat:tomcat /opt/tomcat.keystore

配置自签证书 vim $CATALINA_HOME/conf/server.xml

 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


<?xml version="1.0" encoding="UTF-8"?>
<Server port="8045" shutdown="SHUTDOWN">
    <Listener className="org.apache.catalina.startup.VersionLoggerListener"/>
    <Listener className="org.apache.catalina.core.AprLifecycleListener" SSLEngine="on"/>
    <Listener className="org.apache.catalina.core.JreMemoryLeakPreventionListener"/>
    <Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener"/>
    <Listener className="org.apache.catalina.core.ThreadLocalLeakPreventionListener"/>
    <GlobalNamingResources>
        <Resource name="UserDatabase" auth="Container"
                type="org.apache.catalina.UserDatabase"
                description="User database that can be updated and saved"
                factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
                pathname="conf/tomcat-users.xml"/>
    </GlobalNamingResources>
    <Service name="Catalina">
        <!-- https -->
        <Connector port="8040"
                    protocol="org.apache.coyote.http11.Http11NioProtocol"
                    connectionTimeout="20000"   
                    maxThreads="150"
                    SSLEnabled="true" 
                    scheme="https"     
                    relaxedQueryChars="[]"        >                             
        <SSLHostConfig>                                                     
            <Certificate certificateKeystoreFile="/opt/tomcat.keystore"     
                        certificateKeystorePassword="changeit"  />           
        </SSLHostConfig>                                                    
        </Connector> 
        <!-- http -->
        <!-- <Connector port="8080" protocol="HTTP/1.1" connectionTimeout="20000" proxyPort="443" scheme="https"
                relaxedQueryChars="[]"
                redirectPort="8443"/> -->
        <Engine name="Catalina" defaultHost="localhost">
            <Realm className="org.apache.catalina.realm.LockOutRealm">
                <Realm className="org.apache.catalina.realm.UserDatabaseRealm"
                    resourceName="UserDatabase"/>
            </Realm>
            <Host name="localhost" appBase="webapps"
                unpackWARs="true" autoDeploy="true">
                <Valve className="org.apache.catalina.valves.AccessLogValve" directory="logs"
                    prefix="localhost_access_log" suffix=".txt"
                    pattern="%h %l %u %t &quot;%r&quot; %s %b"/>
                <Valve className="org.apache.catalina.valves.RemoteIpValve"
                    remoteIpHeader="X-Forwarded-For"
                    protocolHeader="X-Forwarded-Proto"
                    internalProxies="192\.168\.x\.x|127\.0\.0\.1|10\.x\.x\.x" />
            </Host>
        </Engine>
    </Service>
</Server>

优化

删除无用的信息

1
2


[root@xxxxxxxxxx ~]#  rm -rf /opt/tomcat/webapps/manager  /opt/tomcat/webapps/host-manager
[root@xxxxxxxxxx ~]#  rm -rf ../webapps/docs  ../webapps/examples

隐藏版本号借鉴文档

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


[root@xxxxxxxxxx ~]#  cd /opt/tomcat/lib/
[root@xxxxxxxxxx ~]#  unzip catalina.jar

[root@xxxxxxxxxx ~]#  vim  org/apache/catalina/util/ServerInfo.properties
... 
修改配置
server.info=Apache Tomcat/0.0.0
server.number=0.0.0.0
... 
[root@xxxxxxxxxx ~]#  jar uvf catalina.jar org/apache/catalina/util/ServerInfo.properties
<!--重启服务-->
[root@xxxxxxxxxx ~]#  systemctl restart tomcat

Server.xml 配置 vim /opt/tomcat/conf/server.xml

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


...
<Service name="Catalina">
<Connector port="8080" protocol="HTTP/1.1"
        connectionTimeout="20000"  
        scheme="https"
        redirectPort="8443" /> <!--奇奇怪怪的oauth2回调跳转 -->
...
<Host name="localhost"  appBase="webapps"
        unpackWARs="true" autoDeploy="true">
    <Valve className="org.apache.catalina.valves.AccessLogValve" directory="logs"
        prefix="localhost_access_log" suffix=".txt"
        pattern="%h %l %u %t &quot;%r&quot; %s %b" />

    <!-- Host内新增Nginx配置-->
    <Valve className="org.apache.catalina.valves.RemoteIpValve"
        remoteIpHeader="X-Forwarded-For"
        protocolHeader="X-Forwarded-Proto"
        internalProxies="127\.0\.0\.1|10\.x\.x\.x"/> <!--nginx服务器地址 -->
</Host>
...

通过jmx监控tomcat配置参考文档

新增 /opt/tomcat/bin/setenv.sh

1
2
3
4
5
6
7


#!/bin/sh 
export CATALINA_OPTS="$CATALINA_OPTS -Dcom.sun.management.jmxremote \
-Dcom.sun.management.jmxremote.port=12345 \
-Dcom.sun.management.jmxremote.rmi.port=12345 \
-Dcom.sun.management.jmxremote.ssl=false \
-Dcom.sun.management.jmxremote.authenticate=false \
-Djava.rmi.server.hostname=本机IP"

添加执行权限chmod +x /opt/tomcat/bin/setenv.sh
开放防火墙端口

Oracle Client

Oracle Client 下载
 官方安装参考
 Oracle Client Sqlplus 下载

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


[root@xxxxxxxxxx ~]#  mkdir  /opt/oracle_client 
[root@xxxxxxxxxx ~]#  mv /home/xxxx/instantclient-basic-linux.x64-21.1.0.0.0.zip  /opt/oracle_client
<!-- sqlplus-->
[root@xxxxxxxxxx ~]#  mv /home/xxxx/instantclient-sqlplus-linux.x64-21.1.0.0.0.zip /opt/oracle_client
[root@xxxxxxxxxx ~]#  unzip instantclient-basic-linux.x64-21.1.0.0.0.zip
[root@xxxxxxxxxx ~]#  unzip instantclient-sqlplus-linux.x64-21.1.0.0.0.zip 
[root@xxxxxxxxxx ~]#  yum install libaio

[root@xxxxxxxxxx ~]#  sh -c "echo /opt/oracle_client/instantclient_21_1 >/etc/ld.so.conf.d/oracle-instantclient.conf"

[root@xxxxxxxxxx ~]#  ldconfig

<!--设置环境变量-->
[root@xxxxxxxxxx ~]#  vim  /etc/profile
...
export PATH=$JAVA_BIN:$JAVA_HOME:$PATH:/opt/oracle_client/instantclient_21_1
    

Apex 安装

SQLcl(可选)

官方文档
 Oracle-base 文档
 SQLcl这个可爱的小工具，来了解一下呀~ 微信

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


[oracle@xxxxxxxxxx ~]#  unzip sqlcl-20.4.2.35.2359.zip
[oracle@xxxxxxxxxx ~]#  mv sqlcl /opt/sqlcl
<!--设置环境变量-->
[oracle@xxxxxxxxxx ~]#  vim  /etc/profile
...
export PATH=$JAVA_BIN:$JAVA_HOME:$PATH:/opt/oracle_client/instantclient_21_1:/opt/sqlcl/bin

<!--test-->
[oracle@xxxxxxxxxx ~]#  sql sys/我是密码@x.x.x.x:x/testpdb as SYSDBA
<!--或者-->
[oracle@xxxxxxxxxx ~]#  sql hr/我是密码@x.x.x.x:x/testpdb

Apex

官方下载
 官方文档

安装APEX

CDB/PDB模式下安装apex 到 PBD

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


<!--创建pdb-->
CREATE PLUGGABLE DATABASE testdb ADMIN USER admin IDENTIFIED BY "w}RZJVCrzdfLh3]z(2h9" FILE_NAME_CONVERT=('pdbseed','testdb');

<!--打开pdb-->
ALTER PLUGGABLE DATABASE testdb OPEN;

<!--删除CDB中的apex-->
@apxremov.sql

<!--激活pdb-->
ALTER SESSION SET CONTAINER = testdb;

<!--PDB安装apex-->
@apexins.sql SYSAUX SYSAUX TEMP /i/

<!--验证apex安装结果-->
begin
    sys.dbms_utility.compile_schema( 'APEX_190200', false );
    sys.dbms_utility.compile_schema( 'FLOWS_FILES', false );
end;

select app_name, app_version, app_status from dba_applications where app_name = 'APEX';

select app_name, app_statement, errornum, errormsg from dba_app_errors where app_name = 'APEX';

用户	环境	密码	备注
APEX_PUBLIC_USER	testdb(165)	S9dFCaUaCejbmLM52L	最低特权帐户用于通过ords和Oracle进行Application Express配置mod_plsql。

非PDB模式

1
2
3
4
5
6


<!-- -->
@apexins.sql SYSAUX SYSAUX TEMP /i/


<!--  设置 APEX_PUBLIC_USER -->
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY S9dFCaUaCejbmLM52L ACCOUNT UNLOCK;

用户	环境	密码	备注
APEX_PUBLIC_USER	test162	S9dFCaUaCejbmLM52L	最低特权帐户。

设置internal工作区admin用户,配置RESTful服务

PDB模式

1
2
3
4
5
6
7
8


<!-- PDB模式切换SESSION-->
ALTER SESSION SET CONTAINER = testdb;

<!--设置 INTERNAL 工作区的admin用户及密码-->
@apxchpwd.sql

<!--配置RESTful服务-->
@apex_rest_config.sql

非PDB模式

1
2
3
4
5


<!--设置 INTERNAL 工作区的admin用户及密码-->
@apxchpwd.sql

<!--配置RESTful服务-->
@apex_rest_config.sql

安装中文支持

PDB模式

1
2
3
4
5
6
7
8


<!-- PDB模式 切换库(PDB)--> 
ALTER SESSION SET CONTAINER = xxxxxxx;

############################  注意 是APEX_190200用户   ############################ 
<!-- 切换SCHEMA -->
ALTER SESSION SET CURRENT_SCHEMA = APEX_190200;
<!--安装中文支持-->
@builder/zh-cn/load_zh-cn.sql

非PDB模式

1
2
3
4


<!-- 切换SCHEMA -->
ALTER SESSION SET CURRENT_SCHEMA = APEX_190200;
<!--安装中文支持-->
@builder/zh-cn/load_zh-cn.sql

验证APEX的有效性

1
2


SELECT STATUS FROM DBA_REGISTRY WHERE COMP_ID = 'APEX';
SELECT * FROM apex_release;

安装补丁

unzip p30392181_1920_Generic.zip
cat 30392181/README.txt

按照说明执行

1
2
3
4
5
6
7
8
9


 /****************看文档************
 sqlplus "sys/ as sysdba" 
 <!--切SESSION -->
 <!--以下任选其一-->
 1. @catpatch.sql        -- 适用于Oracle数据库11.2及更早版本，适用于非CDB，以及APEX没有安装在根目录下的CDB
 2. @catpatch_con.sql    -- for CDB where Application Express is installed in the root
 3. @catpatch_appcon.sql -- for installations where Application Express is installed in an application container
 **************************************/
@catpatch.sql 

验证补丁

1
2


select APEX_INSTANCE_ADMIN.GET_PARAMETER( 'APEX_19_2_0_PATCH_30392181' ) from dual;
SELECT * FROM apex_release;

将补丁中的静态文件覆盖到原静态文件

1
2


[oracle@xxxxxxxx 37366599]$ zip -r apex242_images_p4.zip images
[oracle@xxxxxxxb 37366599]$ rsync -av ./images/ /usr/local/nginx/html/i242/

APEX https 配置

设定 Oracle ACL

 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


<!--切换至PBD, 非PBD省略-->
ALTER SESSION SET CONTAINER = testDB;
<!--HTTP ACL 配置-->
declare
l_acl_name  varchar2(30) := 'HttpsServiceACLConfig.xml';
l_principal varchar2(20) := 'APEX_190200';
begin
	begin
		dbms_network_acl_admin.drop_acl(acl => l_acl_name);
	exception
		when others then
			null; -- ACL does not exist yet
	end;
	dbms_network_acl_admin.create_acl(
			acl => l_acl_name
		, description => 'ding ding https'
		, principal => l_principal
		, is_grant => true
		, privilege => 'connect'
		, start_date => systimestamp
		, end_date => null
		);
	dbms_network_acl_admin.add_privilege(
			acl => l_acl_name
		, principal => l_principal
		, is_grant => true
		, privilege => 'resolve'
		, start_date => systimestamp
		, end_date => null
		);

	-- 添加钉钉
	dbms_network_acl_admin.assign_acl(
			acl => l_acl_name
		, host => '*.dingtalk.com'
		, lower_port => 443
		, upper_port => 443
		);
	-- 添加
	dbms_network_acl_admin.assign_acl(
			acl => l_acl_name
		, host => '*.oracle.com'
		, lower_port => 443
		, upper_port => 443
		);
	--  删除
	-- DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(host =>'*.oracle.com');
	commit;
end;

https 证书 wallet 配置参考文档

 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


[oracle@xxxxxxxxxx ~]#  cd $ORACLE_HOME
[oracle@xxxxxxxxxx ~]#  pwd
<!--创建wallet目录-->
[oracle@xxxxxxxxxx ~]#  mkdir wallets

<!--创建wallet-->
[oracle@xxxxxxxxxx ~]#  orapki wallet create -wallet /xx/xx/xx/xx/xxx/xxx/wallets/https_wallet  -pwd xxxxxxxx -auto_login 

<!--复制证书-->  #  可以参考 7. 服务器验证证书 第一个 ---BEGIN CERTIFICATE---END CERTIFICATE---

<!--授权-->
[oracle@xxxxxxxxxx ~]#  chown -R oracle:oinstall /u01/ssl

<!--添加证书-->
[oracle@xxxxxxxxxx ~]#  orapki wallet add -wallet /xx/xx/xx/xx/xxx/xxx/wallets/https_wallet -trusted_cert -cert "/u01/ssl/sts.cer" -pwd xxxxxxxx

<!--删除证书-->
[oracle@xxxxxxxxxx ~]#  orapki wallet remove -wallet /xx/xx/xx/xx/xxx/xxx/wallets/https_wallet  -dn "CN=DigiCert Global Root CA,OU=www.digicert.com,O=DigiCert Inc,C=US" -trusted_cert  -pwd xxxxxxxx

<!--查看wallet-->
[oracle@xxxxxxxxxx ~]#  orapki wallet display -wallet /xx/xx/xx/xx/xxx/xxx/wallets/https_wallet

Requested Certificates: 
User Certificates:
Trusted Certificates: 
Subject:        CN=GlobalSign Root CA,OU=Root CA,O=GlobalSign nv-sa,C=BE

<!--导出证书-->  
[oracle@xxxxxxxxxx ~]#  orapki wallet export -wallet ./https_wallet -dn  'CN=GlobalSign Root CA,OU=Root CA,O=GlobalSign nv-sa,C=BE'  -cert  ./2.txt 

<!--清空wallet-->
[oracle@xxxxxxxxxx ~]#  orapki wallet remove -wallet /xx/xx/xx/xx/xxx/xxx/wallets/https_wallet  -trusted_cert_all  -pwd xxxxx

将 wallet配置到 APEX的 INTERNAL 工作区

配置后稍稍等等(喝口水,上个卫生间)再验证

验证https/http 请求

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


<!-- 注意此处验证的 https url需已经添加对应的ACL和wallet,  -->
<!-- 注意此处验证的 http url需已经添加对应的ACL  -->
select 
    apex_web_service.make_rest_request(p_url=>'https://xxxxx.xxxx.com/adfs/oauth2/token',
    p_http_method => 'GET',
    p_wallet_path => 'file:/xx/xx/xx/xx/xxx/xxx/wallets/https_wallet') 
from dual;
select 
    apex_web_service.make_rest_request(p_url=>'http://127.0.0.1:9999/adfs/oauth2/token',
    p_http_method => 'GET') 
from dual;

https post请求 Demo

 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


declare
	l_clob1              clob;
	l_response_json_clob clob;
	L_1                  clob := '';
begin
	apex_json.initialize_clob_output;
	apex_json.open_object;
	apex_json.write('AppId', '');
	apex_json.write('AppSecret', 'B');
	apex_json.open_array('Email');
	for i in 1..2
		loop
			apex_json.open_object;
			apex_json.write('from', '@.com');
			apex_json.write('to', '@.com');
			apex_json.write('subject', '下');
			apex_json.write('body', L_1);
			apex_json.close_object;
		end loop;
	apex_json.close_array;
	apex_json.close_object;
	l_clob1 := apex_json.get_clob_output;
	apex_json.free_output;
	apex_web_service.g_request_headers(1).name := 'Content-Type';
	apex_web_service.g_request_headers(1).VALUE := 'application/json';
	l_response_json_clob := apex_web_service.make_rest_request(
			p_url => 'http://xxxx/xxxx/xxxx/SendEmail',
			p_http_method => 'POST',
			p_body => l_clob1);

	apex_debug.warn(l_response_json_clob);
end;

https证书的获取

服务器验证证书参考

 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


openssl s_client -connect http://www.dingtalk.com -tls1 -showcerts

CONNECTED(00000003) depth=2 C = BE, O = GlobalSign nv-sa, OU = Root CA, CN = GlobalSign Root CA verify return:1 depth=1 C = BE, O = GlobalSign nv-sa, CN = GlobalSign Organization Validation CA - SHA256 - G2 verify return:1 depth=0 C = CN, ST = ZheJiang, L = HangZhou, O = "Alibaba (China) Technology Co., Ltd.", CN = *.dingtalk.com verify return:1 --- Certificate chain 0 s\:/C=CN/ST=ZheJiang/L=HangZhou/O=Alibaba (China) Technology Co., Ltd./CN=*.dingtalk.com
i:/C=BE/O=GlobalSign nv-sa/CN=GlobalSign Organization Validation CA - SHA256 - G2
-----BEGIN CERTIFICATE-----
MIIGYDCCBUigAwIBAgIMCZwLPIt+KS8mrSqZMA0GCSqGSIb3DQEBCwUAMGYxCzAJ
......
Dn6XwwJTuLDHvdZhE54cswjBpc0OizT63hRg6X+C5syJB8CKLSJxJGmPThWuNbpI
MVUwRA==
-----END CERTIFICATE-----
1 s\:/C=BE/O=GlobalSign nv-sa/CN=GlobalSign Organization Validation CA - SHA256 - G2
i:/C=BE/O=GlobalSign nv-sa/OU=Root CA/CN=GlobalSign Root CA
-----BEGIN CERTIFICATE-----
MIIEaTCCA1GgAwIBAgILBAAAAAABRE7wQkcwDQYJKoZIhvcNAQELBQAwVzELMAkG
.....
SOlCdjSXVWkkDoPWoC209fN5ikkodBpBocLTJIg1MGCUF7ThBCIxPTsvFwayuJ2G
K1pp74P1S8SqtCr4fKGxhZSM9AyHDPSsQPhZSZg=
-----END CERTIFICATE-----
---
Server certificate
subject=/C=CN/ST=ZheJiang/L=HangZhou/O=Alibaba (China) Technology Co., Ltd./CN=\*.dingtalk.com
issuer=/C=BE/O=GlobalSign nv-sa/CN=GlobalSign Organization Validation CA - SHA256 - G2
---
No client certificate CA names sent
Server Temp Key: ECDH, P-256, 256 bits
---
SSL handshake has read 3453 bytes and written 315 bytes
---
New, TLSv1/SSLv3, Cipher is ECDHE-RSA-AES128-SHA
Server public key is 2048 bit
Secure Renegotiation IS supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
SSL-Session:
Protocol  : TLSv1
Cipher    : ECDHE-RSA-AES128-SHA
Session-ID: 795C7128D1F65CD328662802B1DCD0DC2D753A185568EE1A9BC9ED8172078942
Session-ID-ctx:
Master-Key: B5BE8CAF4C8CE29D0945324B39FEDC3E40B293DD7A5B8395248169B8AAC3587134E6D3B9D0ADFA75E3ADFB251233185D
Key-Arg   : None
Krb5 Principal: None
PSK identity: None
PSK identity hint: None
TLS session ticket lifetime hint: 600 (seconds)
TLS session ticket:
0000 - 86 a7 5b 81 b5 e0 db 84-29 fd 41 bd cb 2c 65 fc   ..\[.....).A..,e.

Start Time: 1585991577
Timeout   : 7200 (sec)
Verify return code: 0 (ok)

浏览器访问目标网站另存导出

注意选中最外层的CA证书即可

移除 Apex

```sql
DROP USER APEX_230200 CASCADE;

<!--删除Apex-->
@apxremov.sql
<!--执行后必须关闭SQL*Plus 重新打开-->
<!--关闭pdb-->
ALTER PLUGGABLE DATABASE testdb CLOSE;
<!--删除pdb-->
DROP PLUGGABLE DATABASE testdb INCLUDING DATAFILES;

```

Ords

ORDS 本身使用通用连接池（UCP）技术维护到数据库的连接池。池配置更改是通过 ORDS 连接池系统属性进行的，而不是通过 Tomcat 或 WebLogic 进行的服务器。

例如，如果数据库服务器有 2 个 CPU，每个 CPU 有 12 个核心，每个 CPU 有 2 个线程核心，则有 24 个核心可用，以及与数据库应介于 12 和 120 之间。线程数不考虑在内考虑到只有 CPU 内核才能执行指令。这个数字是对于连接到系统的所有应用程序和所有数据库，如果系统上有多个数据库。如果有两个应用程序服务器，则最大连接数（例如，在本例中为 120）应为在他们之间分配。如果系统上运行着两个数据库，则最大连接数，即 120 个连接需要划分他们。

注意
1. 如果是多个环境(数据库)需要验证下ords的版本 java -jar apex_test.war version
2. 需要知晓sys/system数据库账号密码
3. 验证下数据库是否已安装 ordsjava -jar apex_test.war validate
1 2

2024-11-12T06:31:46.514Z INFO Error cannot validate ORDS: ORDS is not installed

设置URL前缀

1
2


<!--xxxx.com/ords 将变成 xxxx.com/apex_test-->
cp ords.war apex_test.war

设置配置文件目录

1

[root@xxxxxxxxxx ords]#  java -jar apex_test.war configdir /opt/ords_8011_db1/config_apex_test

安装并添加数据库 test162 官方提示说明

 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


<!--配置ords连接数据库 test162-->
[root@xxxxx ords]# java -jar apex_test.war  setup --database test162
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]:10.x.x.x
Enter the database listen port [1521]:xxxx
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name:apex_testtest164
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:sys
Enter the database password for SYS AS SYSDBA:
Confirm password:
Connecting to database user: SYS AS SYSDBA url: jdbc:oracle:thin:@//10.x.x.x:xxxx/apex_testtest164

Retrieving information.
Enter the default tablespace for ORDS_METADATA [SYSAUX]:
Enter the temporary tablespace for ORDS_METADATA [TEMP]:
Enter the default tablespace for ORDS_PUBLIC_USER [SYSAUX]:
Enter the temporary tablespace for ORDS_PUBLIC_USER [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]:
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-15T07:36:18.280Z INFO        reloaded pools: [|test162||, |test162|al|, |test162|pu|, |test162|rt|, |test163||, |test163|al|, |test163|pu|, |test163|rt|, |test164||, |test164|al|, |test164|rt|, |test164|pu|]
Installing Oracle REST Data Services version 20.4.3.r0501904
... Log file written to /root/ords_install_core_2021-04-15_153618_00432.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 /root/ords_install_datamodel_2021-04-15_153629_00290.log
... Log file written to /root/ords_install_apex_2021-04-15_153630_00155.log
Completed installation for Oracle REST Data Services version 20.4.3.r0501904. Elapsed time: 00:00:12.659 

配置路径指向对应的数据库

如:xxx.com/apex_test/test162 将指向 test162这个数据库
1

java -jar apex_test.war map-url --type base-path /test162 test162
重复上面操作依次添加其他数据库

配置完成后, 将 ords 配置文件授权给tomcat.

1
2
3


chown -R tomcat:tomcat /opt/ords/ 

systemctl restart tomcat

调整Ords相关配置(实测影响挺大的.) 官方文档

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


ls /opt/ords/conf/ords/
conf/         credentials   defaults.xml  standalone/ 

vim /opt/ords/conf/ords/defaults.xml
...
<entry key="debug.printDebugToScreen">false</entry>
<entry key="feature.sdw">true</entry>
<entry key="apex.jdbc.AbandonedConnectionTimeout">1900</entry>
<entry key="apex.jdbc.DriverType">thin</entry>
<entry key="apex.jdbc.InactivityTimeout">2000</entry>
<entry key="apex.jdbc.InitialLimit">16</entry>
<entry key="apex.jdbc.MaxConnectionReuseCount">50000</entry>

<!-- MaxConnectionReuseCount 它控制 ORDS 在终止数据库会话（并释放泄漏的资源）并用新的数据库会话替换它之前使用数据库会话的次数。此设置的默认值为 1000。如果您遇到数据库资源耗尽，您应该：确定泄漏资源的数据库代码，并修复数据库代码不泄漏。
如果这不可行，则减少jdbc.MaxConnectionReuseCountin 增量的值，直到找到数据库不再遇到资源耗尽的点。 -->
<entry key="jdbc.MaxLimit">300</entry>
<entry key="jdbc.MinLimit">130</entry>
<entry key="jdbc.cleanup.mode">recycle</entry>
<entry key="plsql.gateway.add">true</entry>
<entry key="restEnabledSql.active">true</entry>

<entry key="instance.api.enabled">true</entry>
...

本人的 Demo配置.

 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


<?xml version="1.0" encoding="UTF-8"?>                                  
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Fri Aug 05 15:29:09 CST 2022</comment>
<entry key="cache.caching">false</entry>
<entry key="cache.directory">/tmp/apex/cache</entry>
<entry key="cache.duration">days</entry>
<entry key="cache.expiration">7</entry>                                    
<entry key="cache.maxEntries">500</entry>
<entry key="cache.monitorInterval">60</entry>
<entry key="cache.procedureNameList"></entry>
<entry key="cache.type">lru</entry>
<entry key="db.connectionType">basic</entry>
<entry key="db.hostname">xxxxxxxxxx</entry>
<entry key="db.port">xxxxxxx</entry>
<entry key="db.sid">xxxxxx</entry>
<entry key="debug.debugger">false<entry>
<entry key="debug.printDebugToScreen">false</entry>
<entry key="error.keepErrorMessages">true</entry>
<entry key="error.maxEntries">50</entry>
<entry key="jdbc.DriverType">thin</entry>
<entry key="jdbc.InactivityTimeout">2000</entry>
<entry key="jdbc.InitialLimit">30</entry>
<entry key="jdbc.MaxConnectionReuseCount">50000</entry>
<entry key="jdbc.MaxLimit">300</entry>
<entry key="jdbc.MaxStatementsLimit">10</entry>
<entry key="jdbc.MinLimit">50</entry>
<entry key="log.logging">false</entry>
<entry key="log.maxEntries">50</entry>
<entry key="jdbc.statementTimeout">2000</entry>
<entry key="misc.compress"></entry>
<entry key="misc.defaultPage">apex</entry>
<entry key="security.disableDefaultExclusionList">false</entry>
<entry key="security.maxEntries">2000</entry>
<entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>
<entry key="security.validationFunctionType">plsql</entry>
</properties> 

检查数据库ORDS安装版本

1
2
3


BEGIN
DBMS_OUTPUT.PUT_LINE('ORDS version : ' || ORDS_ADMIN.INSTALLED_VERSION);
END;

Nginx

安装基础环境
1. 新增Nginx 用户useradd -s /bin/nologin -M nginx
2. GCC编辑器 yum install -y gcc
3. gcc-c++ 自定义Nginx Http模块yum install -y gcc-c++
4. PCRE 函数库支持正则表达式 yum install -y pcre pcre-devel
5. zlib HTTP包gzip压缩yum install -y zlib zlib-devel
6. OpenSSL 开发库Https支持 yum install -y openssl openssl-devel
7. 汇总yum install -y gcc gcc-c++ pcre pcre-devel zlib zlib-devel openssl openssl-devel
8. 或者干脆yum group install 'Development Tools'

升级OpenSSL(慎重,可选,要不就别升了)

借鉴下载 https://openssl-library.org/source/index.html

安装 perl yum install perl-core

编译openssl

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


tar -zxf openssl-1.1.1o.tar.gz 
cd openssl-1.1.1o
<!--编译三部曲-->
./config  shared --openssldir=/usr/local/openssl --prefix=/usr/local/openssl
make
make test
make install

mv /usr/bin/openssl  /usr/bin/openssl_bak
mv /usr/lib/openssl  /usr/lib/openssl_bak
ln -s /usr/local/openssl/bin/openssl /usr/bin/openssl
ln -s /usr/local/openssl/include/openssl /usr/include/openssl
echo "/usr/local/lib64/" >> /etc/ld.so.conf
echo "/usr/local/openssl/lib/" >> /etc/ld.so.conf
ldconfig
openssl version

安装Nginx

下载 wget http://nginx.org/download/nginx-1.19.10.tar.gz)
解压tar -zxvf nginx-1.19.10.tar.gz
1. 参数说明见官网文档
2. –with-debug
3. –with-http_ssl_module
4. –with-http_v2_module
5. –with-http_realip_module
6. –with-http_gzip_static_module
7. –with-http_stub_status_module
8. –with-http_sub_module
9. –with-http_dav_module
10. ngx_http_core_module

```bash
[root@xxxxxxxxxx BoxAdmin]# cd nginx-1.19.10
<!--编译三部曲-->
 [root@xxxxxxxxxx nginx-1.26.2]#  ./configure --with-debug --with-http_ssl_module  --with-http_v2_module --with-http_realip_module --with-http_gzip_static_module --with-http_stub_status_module --with-http_sub_module --with-http_dav_module --with-openssl=<openssl 源码的路径 见上升级openssl的源码路径>
                            
./configure: error: SSL modules require the OpenSSL library.
into the system, or build the OpenSSL library statically from the source
You can either do not enable the modules, or install the OpenSSL library  
with nginx by using --with-openssl=<path> option.   
<!--查看编译后的插件-->
[root@xxxxxxxxxx nginx-1.26.2]# cat auto/options | grep YES --color
[root@xxxxxxxxxx nginx-1.26.2]# make
```

旧版本升级

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


<!--1. 备份旧版本和配置文件(可选) -->
nginx-1.22.0]# mv /usr/local/nginx/sbin/nginx /usr/local/nginx/sbin/nginx_old
nginx-1.22.0]# cp /usr/local/nginx/conf/nginx.conf /usr/local/nginx/conf/nginx.conf.old

<!--2. 拷贝新版本 --># 2. 拷贝新版本 
nginx-1.22.0]# cp ./objs/nginx /usr/local/nginx/sbin/nginx
<!-- 3. 验证配置文件 --> # 3. 验证配置文件
nginx-1.22.0]# /usr/local/nginx/sbin/nginx -t
<!-- 4. 查看现在的PID --> # 4. 查看现在的PID
nginx-1.22.0]# cat /usr/local/nginx/logs/nginx.pid 
123817
<!-- 5. 通知上一步得到的PID  --> # 5. 通知上一步得到的PID 
nginx-1.22.0]# kill -s SIGUSR2 123817  <在运行的 Nginx master pid>(nginx会将/usr/local/nginx/logs/pid文件重命名)
<!-- 6. 验证查看  --> # 6. 验证查看
nginx-1.22.0]# ps aux |grep nginx  
<!-- 7. 退出旧版本  --> # 7. 退出旧版本
nginx-1.22.0]# kill -s SIGQUIT  123817 <旧版本Nginx master pid>
<!-- 8. 清理旧版本  --> # 8. 清理旧版本
nginx-1.22.0]# mv /usr/local/nginx/sbin/nginx_old /usr/local/nginx/sbin/nginx_old_19

第一次安装 make install

nginx配置

 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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96


user nginx;
worker_processes auto;
error_log logs/error.log error;
pid logs/nginx.pid;
events {
    worker_connections 5000;
}
http {
    server_tokens off;# 隐藏版本号
    charset UTF-8;
    include mime.types;
    default_type application/octet-stream;
    log_format main '$remote_addr - $remote_user [$time_local] "$request" '
    '$status $body_bytes_sent "$http_referer" '
    '"$http_user_agent" "$http_x_forwarded_for"';
    log_format timing '$remote_addr - $remote_user [$time_local] $request'
    'upstream_response_time $upstream_response_time'
    'msec $msec request_time $request_time';
    log_format up_head '$remote_addr - $remote_user [$time_local] $request'
    'upstream_http_content_type $upstream_http_content_type';
    access_log logs/access.log main;

    sendfile on;
    #tcp_nopush     on;
    client_max_body_size 64M;
    #keepalive_timeout  0;
    keepalive_timeout 65;  #默认值为 75 秒。这个参数用于指定每个 TCP 连接最多可以保持多长时间，如果将它设置为 0，则禁止了 keepalive 连接 
    proxy_read_timeout: 150; #控制Nginx从后端Tomcat服务器读取响应的时间。比如，proxy_read_timeout 150s; 意味着Nginx将等待最多150秒来接收完整的响应 ⁠⁣ ⁠⁣14 。
    send_timeout: 120;  #定义了Nginx向客户端发送响应的超时时间，合理值如 send_timeout 120s; 可以确保数据传输过程中的稳定性 

    gzip on;
    gzip_min_length 1k;
    gzip_buffers 4 16k;
    #gzip_http_version 1.0;
    gzip_comp_level 2;
    gzip_types application/javascript text/plain application/-javascript image/jpeg image/gif image/png application/css text/css application/xml text/javascript
    gzip_vary off;
    gzip_disable "MSIE [1-6]\.";

    gzip_static  on;
    gzip_proxied expired no-cache no-store private auth;

    upstream backend {
    ip_hash;  # 文档 http://nginx.org/en/docs/http/ngx_http_upstream_module.html#ip_hash
    server 10.x.x.x:8080;
    server 10.x.x.x:8080 down;
    server 10.x.x.x:8080;
    }
    server {
        listen 80;
    # Nginx 监控配置 需要 with-http_stub_status_module 模块 https://nginx.org/en/docs/http/ngx_http_stub_status_module.html
        location = /basic_status {
            stub_status on;  
        allow 127.0.0.1;
        allow 本机ip;
        deny all;
        }   
    #  兼容旧的连接
        location ^~ /ords/ {
            rewrite ^/ords(.*)$ /apex_test/test162$1    redirect;
        }

        location ^~ /apex_test/ {
            proxy_pass http://backend/apex_test/;
            include /usr/local/nginx/conf/options-proxy-ssl-nginx.conf; 
        }
        location /i/ {
            root html;
        }
        error_page 500 502 503 504 /50x.html;
        error_page 404 /404.html;
        location = /50x.html {
            root html;
        }
        }
    upstream tomcat_db2 {
        ip_hash;
        server 127.0.0.1:8040 ;# down;
    }     
    server {
        listen       443 ssl ;
        server_name  xxxxxx.xxxxxx.com;
        ssl_protocols       TLSv1.2 TLSv1.3;
        ssl_certificate /usr/local/nginx/certificate/crt_2025/xxxxx.Pem;
        ssl_certificate_key /usr/local/nginx/certificate/crt_2025/xxxxx.key;
        ssl_session_cache     shared:SSL:1m;
        ssl_prefer_server_ciphers on;  
        location  /ords/ {
                rewrite ^/ords(.*)$ /edex/dev$1    redirect;
                }    
        location  /edex/db2/ {
                proxy_pass https://tomcat_db2;
                include /usr/local/nginx/conf/options-proxy-ssl-nginx.conf;  
                }
    }
}

vim /usr/local/nginx/conf/options-proxy-ssl-nginx.conf

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


# Apex 相关
proxy_set_header Host $host;          
proxy_set_header Origin "";  
proxy_set_header X-Forwarded-Proto $scheme;   
proxy_set_header X-Real-IP $remote_addr;     
proxy_set_header X-Real-Port $remote_port;  
proxy_set_header X-Forwarded-Server $host;   
proxy_set_header X-Forwarded-Host $host;    
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;  
client_max_body_size 120m;     
# 启用 SSL 代理    
proxy_ssl_certificate     /usr/local/nginx/certificate/tomcat.crt;        # 可选：客户端证书（如果 Tomcat 需要双向认证）  
proxy_ssl_certificate_key /usr/local/nginx/certificate/tomcat.key;        # 可选：客户端私钥     
proxy_ssl_verify off;                                 # 如果使用自签名证书，关闭验证（生产建议开启并配置 trusted_ca）  
proxy_ssl_trusted_certificate /usr/local/nginx/certificate/tomcat_ca.crt;    # Tomcat 公钥证书（用于验证服务器身份）  
proxy_ssl_session_reuse on;   

生成20年的自签证书上面 tomcat处生成

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


[root@xxxxxxxxx ~]# keytool -genkeypair -alias tomcat -keyalg RSA -keystore /opt/tomcat.keystore -validity 7300 -storepass changeit
Generating 3,072 bit RSA key pair and self-signed certificate (SHA384withRSA) with a validity of 7,300 days 
    for: CN=pan, OU=Data Engineering, O=Company Ltd., L=shanghai, ST=shanghai, C=CN  

## 导出 CA证书
[root@xxxxxxxxx ~]# keytool -exportcert -alias tomcat -file tomcat_ca.crt  -keystore "/opt/tomcat.keystore"  -storepass changeit   

# 导出 .key 私钥文件
## 1.先导出为 PKCS12 格式
[root@xxxxxxxxx ~]# keytool -importkeystore -srckeystore "/opt/tomcat.keystore" -srcstoretype PKCS12 -srcalias tomcat -destkeystore "tomcat.p12" -deststoretype PKCS12 -srcstorepass changeit -deststorepass changeit



## 2.提取私钥（.key）
[root@xxxxxxxxx ~]# openssl pkcs12 -in tomcat.p12 -nocerts -nodes -out tomcat.key -passin pass:changeit -passout pass:changeit
## 3.提取证书（.crt）
[root@xxxxxxxxx ~]# openssl pkcs12 -in tomcat.p12 -nokeys -out tomcat.pem -passin pass:changeit
[root@xxxxxxxxx ~]# openssl x509 -in tomcat.pem -out tomcat.crt -outform PEM

验证后端证书是否走https tcpdump -i any -s 0 -w traffic.pcap host tomcat-ip and port 8040

systemd配置文件 vim /etc/systemd/system/nginx.service

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


[Unit]
Description=The NGINX HTTP and reverse proxy server
After=syslog.target network-online.target remote-fs.target nss-lookup.target
Wants=network-online.target

[Service]
Type=forking
PIDFile=/usr/local/nginx/logs/nginx.pid
ExecStartPre=/usr/local/nginx/sbin/nginx -t
ExecStart=/usr/local/nginx/sbin/nginx
ExecReload=/usr/local/nginx/sbin/nginx -s reload
ExecStop=/bin/kill -s QUIT $MAINPID
PrivateTmp=true

[Install]
WantedBy=multi-user.target

设置自启

1
2
3
4
5
6
7
8
9


systemctl start nginx
<!--自启-->
systemctl enable nginx
<!--注册自启-->
systemctl daemon-reload
systemctl disable nginx 

<!--加个软连接-->
ln -s /usr/local/nginx/sbin/nginx /usr/bin/nginx   

将Oracle APEX的静态文件复制到nginx的静态文件夹 cp -fr /opt/tomcat/webapps/i /usr/local/nginx/html

SSL配置

1
2
3
4
5
6


server {
  listen 443 ssl http2;
  ssl_certificate /etc/nginx/certificate/crt_2021/wcom.crt;
  ssl_certificate_key /etc/nginx/certificate/crt_2021/wcom.key;
  ssl_session_cache	shared:SSL:1m;
  ssl_prefer_server_ciphers on;

服务器优化 (坑待填)

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


vim /etc/rc.local  
....
net.ipv4.tcp_max_syn_backlog= 65536 # 坑待填
net.core.netdev_max_backlog=32768 # 坑待填
net.core.somaxconn=32768 # 坑待填

net.core.wmem_default=8388608 # 坑待填
net.core.rmem_default=8388608 # 坑待填
net.core.rmem_max=16777216 # 坑待填
net.core.wmem_max=16777216 # 坑待填

net.ipv4.tcp_timestamps=0 # 坑待填
net.ipv4.tcp_synack_retries=2 # 坑待填

net.ipv4.tcp_syn_retries=2 # 坑待填
net.ipv4.tcp_tw_recycle=1 # 坑待填
net.ipv4.tcp_tw_reuse=1 # 坑待填

net.ipv4.tcp_mem=94500000 915000000 927000000 # 坑待填
net.ipv4.tcp_max_orphans=3276800 # 坑待填
net.ipv4.ip_local_port_range=1024 65535 # 坑待填
....

06 ops]#  /sbin/sysctl -p

后期处理

日常更新Oracle Ords对应的服务账号密码

改配置文件

1
2
3
4
5


vim /opt/ords/conf/apex_test/url-mapping.xml # 删除 test162 URl路径配置
java -jar apex_test.war  setup --database test162 # 重新配置新密码
java -jar apex_test.war map-url --type base-path  /test162 test162   # 配置URL路径
chown -R  tomcat:tomcat /opt/ords   # 设置文件权限
cp apex_test.war /opt/tomcat/webapps/apex_test.war   # 初始化新配置连接池

Ords 22.1之现在的版本

加载环境配置 source ~/.bashrc 验证 ords –help 是否可用

依据ords 配置密码

global配置

ords --config /opt/ords_config/conf config set --global instance.api.enabled true

配置单独pool其他

1
2
3
4
5
6
7
8


<!-- 检查现有的Pool -->
ords --config /opt/ords_config/conf  config --db-pool db2  list  

<!-- 配置其他属性 --> 
ords --config /opt/ords_config/conf  config --db-pool db2  set  misc.pagination.maxRows  300 

<!-- 配置单独pool密码 -->
ords --config /opt/ords_config/conf  config --db-pool db2  secret  db.password     

常见问题

使用Oauth2 认证时出现奇奇怪怪的重定向

如:
`redirect_uri=https://xx.xx.com:80/ords `
[解决办法](https://www.oracle-and-apex.com/tomcat-with-ords-running-apex-behind-a-reverse-proxy/) 

tomcat server.xml 配置 添加`scheme="https"` 见下
```conf
    <Connector port="8080" protocol="HTTP/1.1"
           connectionTimeout="20000"
           proxyPort="443"
           scheme="https"
           redirectPort="8443" />
```

出现偶然特殊字符导致404

```log
06-Apr-2022 16:12:33.606 INFO [http-nio-8080-exec-17841] org.apache.coyote.http11.Http11Processor.service Error parsing HTTP request header
 Note: further occurrences of HTTP request parsing errors will be logged at DEBUG level.
	java.lang.IllegalArgumentException: Invalid character found in the request target. The valid characters are defined in RFC 7230 and RFC 3986
		at org.apache.coyote.http11.Http11InputBuffer.parseRequestLine(Http11InputBuffer.java:488)
		at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:260)
```

加上APEX 常使用的\[]

```conf
    <Connector port="8080" protocol="HTTP/1.1"
           connectionTimeout="20000"
           proxyPort="443"
           scheme="https"
           redirectPort="8443" relaxedQueryChars="[]"  />
```

正常后上传应用提示连接被重置日志500 (忘了具体场景了)

```log
/wwv_flow.accept 500 org.apache.catalina.connector.ClientAbortException: java.net.SocketException: Connection reset                     InternalServerException [statusCode=500, logLevel=SEVERE, reasons=[An unexpected error with the following message occurred:java.net.SocketException: Connection reset]]                                                                  
```
1.  查看表空间

2.  公司网络问题,切换成 https

Too many open files参考

1.  ulimit -a  看下系统配置
2.  在Tomcat的systemctl配置中添加 `LimitNOFILE=10000`[文档](https://www.freedesktop.org/software/systemd/man/systemd.exec.html#Process%20Properties)
3.  `systemctl daemon-reload `

tomcat systemd启动失败

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


[root\@l01apex_test01 tomcat]# journalctl -xe

\-- Unit tomcat.service has finished shutting down.\
Dec 25 17:09:30 l01apex_test01 systemd\[1]: Starting Apache Tomcat 9 Servlet Container...\
\-- Subject: Unit tomcat.service has begun start-up\
\-- Defined-By: systemd

## -- Support: <https://access.redhat.com/support>

\-- Unit tomcat.service has begun starting up.\
Dec 25 17:09:30 l01apex_test01 systemd\[1922931]: tomcat.service: Failed to execute command: Permission denied\
Dec 25 17:09:30 l01apex_test01 systemd\[1922931]: tomcat.service: Failed at step EXEC spawning /opt/tomcat/bin/startup.sh: Permission denied\
\-- Subject: Process /opt/tomcat/bin/startup.sh could not be executed\
\-- Defined-By: systemd

## -- Support: <https://access.redhat.com/support>

## -- The process /opt/tomcat/bin/startup.sh could not be executed and failed.

\-- The error number returned by this process is 13.\
Dec 25 17:09:30 l01apex_test01 systemd\[1]: tomcat.service: Control process exited, code=exited status=203\
Dec 25 17:09:30 l01apex_test01 systemd\[1]: tomcat.service: Failed with result 'exit-code'.\
Dec 25 17:09:30 l01apex_test01 systemd\[1]: Failed to start Apache Tomcat 9 Servlet Container.

原因: 借鉴文档

SELinux正在阻止从主目录启动应用程序，并显示如下消息/var/log/audit/audit.log cat /var/log/audit/audit.log |grep startup

重置SELinux属性 restorecon -rv /opt/tomcat [[../../Linux/SELinux]]

nginx访问本机的tomcat 提示无权限

nginx 日志

1
2
3


2024/07/29 14:10:16 [crit] 44832#44832: *34 connect() to 127.0.0.1:8080 failed (13: Permission denied) while connecting to upstream, client: xxxx.xxxx.xxxx.xxxx, server: xxxx-dev.xxxx.com, request: "GET /xxxx/xxxx/f?p=666 HTTP/1.1", upstream: "http://127.0.0.1:8080/xxxx/xxxx/f?p=666", host: "xxxx-dev.xxxx.com"                                              
2024/07/29 14:10:16 [warn] 44832#44832: *34 upstream server temporarily disabled while connecting to upstream, client: xxxx.xxxx.xxxx.xxxx, server: xxxx-dev.xxxx.com, request: "GET 
/apex_test/dev/f?p=666 HTTP/1.1", upstream: "http://127.0.0.1:8080/xxxx/xxxx/f?p=666", host: "xxxx-dev.xxxx.com"      

检查文件权限sudo -u nginx namei /usr/local/nginx/html/i24/apex_ui/img/favicons/favicon-16x16.png 同上还是 SELinux 导致的,有两种方案可解决见下 [[../../Linux/SELinux]] 方案1. 关闭 SELinux, 临时关闭 setenforce 0,永久关闭 具体操作请百度之

方案2. 不关闭SELinux 参考 https://serverfault.com/questions/634294/nodejs-nginx-error-13-permission-denied-while-connecting-to-upstream

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


<!--检查现有的端口 默认情况下，SELinux 仅允许 Web 服务器与一组有限的端口建立出站连接。-->
[root@xxxxxxx nginx]# semanage port --list |grep http_port_t  
http_port_t                    tcp      8012, 80, 81, 443, 488, 8008, 8009, 8443, 9000                                                                                                
pegasus_http_port_t            tcp      5988  

<!--添加一个端口-->
[root@xxxxxxx nginx]# semanage port --add --type http_port_t --proto tcp 8012    
<!--移除一个端口-->
[root@xxxxxxx opt]# semanage port -d -t http_port_t -p tcp 9000
<!--改下tomcat的端口绑定8012-->
[root@xxxxxxx nginx]# vim /opt/tomcat/conf/server.xml    
<!--再改下nginx的配置 tomcat 8012-->
[root@xxxxxxx nginx]# vim  /etc/nginx/conf.d/default_443.conf   
    

关于服务账号问题

账号	作用
APEX_PUBLIC_ROUTER	用于 ORDS REST 模块解析友好的 URL,所有引用 /r/ 路径的 GET 请求都使用 ORDS REST 模块。6.3.5 Configuring the APEX_PUBLIC_ROUTER Account
APEX_LISTENER	用于查询存储在 Oracle Application Express 中的 RESTful 服务定义的帐户。4.4.5 Configuring RESTful Services
APEX_REST_PUBLIC_USER	调用存储在 Oracle Application Express 中的 RESTful 服务定义时使用的帐户。4.4.5 Configuring RESTful Services
APEX_190200	此账户拥有 Application Express 的架构和元数据。4.4.1 Installing Application Express
FLOWS_FILES	此账户拥有 Application Express 上传的文件。4.4.1 Installing Application Express
APEX_PUBLIC_USER	此权限最小的账户用于与 Oracle REST 数据服务或 Oracle HTTP 服务器和 mod_plsql 的 Application Express 配置。4.4.1 Installing Application Express
APEX_INSTANCE_ADMIN_USER	一个权限最小的账户，用于 REST 管理界面。4.4.1 Installing Application Express
ORDS_PUBLIC_USER	用于 ords 进行初始数据库连接以处理请求的用户。根据请求，该用户将代理到相关的 ords 启用的架构或 PL/SQL 网关用户，以完成在数据库中请求的操作。1.6 About the Database Users Used by Oracle REST Data Services
ORDS_METADATA	ords 功能实现所使用的 PL/SQL 包的拥有者,是存储关于启用 ords 的架构的元数据的地方。它并不是由 ords 直接访问的；ords 应用程序从不直接与该架构建立连接。1.6 About the Database Users Used by Oracle REST Data Services

关于服务账号密码问题

见文档,4.4.4 About Password Expiration in Oracle Database 11g and Late官方建议设为无限制.
24.1文档
个人DB User profile

 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


CREATE PROFILE APEX_SERVICE LIMIT
    PASSWORD_LIFE_TIME UNLIMITED -- 密码有效期(天)，默认 180 天；超过时间密码可能会过期，取决于是否设置了 PASSWORD_GRACE_TIME ;
    PASSWORD_VERIFY_FUNCTION ORA12C_STIG_VERIFY_FUNCTION
    SESSIONS_PER_USER UNLIMITED;
;

CREATE PROFILE APEX_APPLICATION LIMIT
    PASSWORD_LIFE_TIME 365 -- 密码有效期(天)，默认 180 天；超过时间密码可能会过期，取决于是否设置了 PASSWORD_GRACE_TIME ;
    PASSWORD_GRACE_TIME 7 -- 密码到期之后可以继续使用的天数，在这段时间内如果登录系统，会提示密码在几天内过期，默认7天;
    PASSWORD_REUSE_TIME 5 -- 密码历史记录保留时间(天)，密码在多少天之后可以被重用，默认 UNLIMITED，同 PASSWORD_REUSE_MAX 搭配使用，参考后面 PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX ;
    PASSWORD_REUSE_MAX 10 -- 密码历史记录保留次数，密码在修改多少次之后可以被重用，默认 UNLIMITED，同 PASSWORD_REUSE_TIME 搭配使用，参考后面 PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX ;
    FAILED_LOGIN_ATTEMPTS 5 -- 最大连续登录失败次数，超过则锁定用户，默认 10 次 ;
    PASSWORD_LOCK_TIME 15 / (24 * 40) --  超过最大连续登录失败次数后用户锁定时间(天)，默认 1 天 ;
    PASSWORD_VERIFY_FUNCTION ORA12C_STIG_VERIFY_FUNCTION
    SESSIONS_PER_USER 15;
;

CREATE PROFILE APEX_PEOPLE LIMIT
    PASSWORD_LIFE_TIME 90 -- 密码有效期(天)，默认 180 天；超过时间密码可能会过期，取决于是否设置了 PASSWORD_GRACE_TIME ;
    PASSWORD_GRACE_TIME 7 -- 密码到期之后可以继续使用的天数，在这段时间内如果登录系统，会提示密码在几天内过期，默认7天;
    PASSWORD_REUSE_TIME 5 -- 密码历史记录保留时间(天)，密码在多少天之后可以被重用，默认 UNLIMITED，同 PASSWORD_REUSE_MAX 搭配使用，参考后面 PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX ;
    PASSWORD_REUSE_MAX 10 -- 密码历史记录保留次数，密码在修改多少次之后可以被重用，默认 UNLIMITED，同 PASSWORD_REUSE_TIME 搭配使用，参考后面 PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX ;
    FAILED_LOGIN_ATTEMPTS 6 -- 最大连续登录失败次数，超过则锁定用户，默认 10 次 ;
    PASSWORD_LOCK_TIME 15 / (24 * 40) --  超过最大连续登录失败次数后用户锁定时间(天)，默认 1 天 ;
    PASSWORD_VERIFY_FUNCTION ORA12C_STIG_VERIFY_FUNCTION
    SESSIONS_PER_USER 5;
;

alter user APEX_190200 PROFILE  APEX_SERVICE;
alter user APEX_230200 PROFILE  APEX_SERVICE;
alter user APEX_INSTANCE_ADMIN_USER PROFILE  APEX_SERVICE;
alter user APEX_LISTENER PROFILE  APEX_SERVICE;
alter user APEX_PUBLIC_USER PROFILE  APEX_SERVICE;
alter user APEX_REST_PUBLIC_USER PROFILE  APEX_SERVICE;
alter user ORDS_METADATA PROFILE  APEX_SERVICE;
alter user ORDS_PUBLIC_USER PROFILE  APEX_SERVICE;
alter user APEX_PUBLIC_ROUTER PROFILE  APEX_SERVICE;  

add 用户时提示

1

[sss_cache] [sysdb_domain_cache_connect] (0x0010): DB version too old [0.22], expected [0.24] for domain implicit_files!

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


1. 停止 SSSD 服务
首先，停止 SSSD 服务：

sudo systemctl stop sssd
2. 删除旧的数据库文件
接下来，删除 SSSD 的缓存数据库文件。请注意，这将删除所有缓存的凭据，因此在执行此操作之前确保你了解其影响。

sudo rm -rf /var/lib/sss/db/*
3. 启动 SSSD 服务
删除数据库文件后，重新启动 SSSD 服务：

sudo systemctl start sssd

平台 internal 中的作业说明

https://docs.oracle.com/en/database/oracle/apex/23.2/aeadm/dbms_scheduler-jobs.html#

安装

整体架构

前置条件

检查环境依赖版本

Oracle DB要求

Tomcat(Java,tomcat, Oracle Client配置)安装

SSH 免密

JAVA

Tomcat安装

Oracle Client

Apex 安装

SQLcl(可选)

Apex

安装APEX

安装中文支持

验证APEX的有效性

安装补丁

APEX https 配置

移除 Apex

Ords

Nginx

升级OpenSSL(慎重,可选,要不就别升了)

安装Nginx

后期处理

日常更新Oracle Ords对应的服务账号密码

常见问题

使用Oauth2 认证时出现奇奇怪怪的重定向

出现偶然特殊字符导致404

正常后上传应用 提示连接被重置 日志500 (忘了具体场景了)

Too many open files参考

tomcat systemd启动失败

nginx访问本机的tomcat 提示无权限

关于服务账号问题

关于服务账号密码问题

add 用户时提示

平台 internal 中的作业说明

正常后上传应用提示连接被重置日志500 (忘了具体场景了)