クローンしたPDBに含まれるAPEXをアップグレードします。
APEXのアップグレードは、マニュアルの以下の手順に沿って実施します。
B APEXのアップグレード中の稼働時間の最大化
@apexins SYSAUX SYSAUX TEMP /i/23.1.0/
@load_trans JAPANESE
これはAPEXのインストールとまったく同じ作業です。手順として紹介する意味があまりないため、３分割のスクリプトの実行と環境を切り替える前に日本語リソースをロードする手順を実施します。
curl -OL https://download.oracle.com/otn_software/apex/apex-latest.zip
[oracle@apex-test ~]$ curl -OL https://download.oracle.com/otn_software/apex/apex-latest.zip
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 244M 100 244M 0 0 60.7M 0 0:00:04 0:00:04 --:--:-- 60.7M
[oracle@apex-test ~]$
以前のバージョンのAPEXのメディアを移動します。
[oracle@apex-test ~]$ mv apex apex211
[oracle@apex-test ~]$
[oracle@apex-test ~]$ unzip apex-latest.zip
Archive: apex-latest.zip
inflating: META-INF/MANIFEST.MF
inflating: META-INF/ORACLE_C.SF
inflating: META-INF/ORACLE_C.RSA
creating: apex/
inflating: apex/apxappcon.sql
inflating: apex/coreins4.sql
inflating: apex/apxremov1.sql
inflating: apex/dbcsconf.sql
inflating: apex/apexins_nocdb.sql
inflating: apex/apexins_cdb.sql
[中略]
inflating: apex/core/wwv_flow_crypto.plb
inflating: apex/core/wwv_flow_yaml.plb
inflating: apex/coreins.sql
inflating: apex/devins.sql
inflating: apex/apxdevrm.sql
inflating: apex/apxpatch_nocdb.sql
inflating: apex/apex_rest_config_nocdb.sql
inflating: apex/apxremov_cdb.sql
inflating: apex/coreins3.sql
[oracle@apex-test ~]$
cp -r -p apex/images i/23.1.0
[oracle@apex-test ~]$ cp -r -p apex/images i/23.1.0
[oracle@apex-test ~]$
クローンしたPDBにユーザーSYSで接続します。
export NLS_LANG=American_America.AL32UTF8
sqlplus sys/<SYSのパスワード>@localhost/freepdb2 as sysdba
[oracle@apex-test ~]$ cd apex
[oracle@apex-test apex]$ export NLS_LANG=American_America.AL32UTF8
[oracle@apex-test apex]$ sqlplus sys/<SYSのパスワード>@localhost/freepdb2 as sysdba
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue May 30 14:18:56 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL>
apexins1.sqlを実行します。apexins1.sqlの実行中は開発ツール、ユーザーのアプリケーションともに継続して利用できます。
SQL> @apexins1 SYSAUX SYSAUX TEMP /i/23.1.0/
PL/SQL procedure successfully completed.
Session altered.
FOO3
------------------------------
install2023-05-30_14-20-57.log
. ORACLE
.
. Oracle APEX Installation.
..........................................
.
...set_appun.sql
... Checking prerequisites (MANUAL)
.
[中略]
Phase 3 (Switch)
...null1.sql
timing for: Phase 3 (Switch)
Elapsed: 0.00
timing for: Complete Installation
Elapsed: 2.47
SQL>
SQL> @apexins2 SYSAUX SYSAUX TEMP /i/23.1.0/
FOO3
------------------------------
install2023-05-30_14-24-25.log
. ORACLE
.
. Oracle APEX Installation.
..........................................
.
...set_appun.sql
... Checking prerequisites (MANUAL)
.
.
[中略]
Phase 3 (Switch)
...null1.sql
timing for: Phase 3 (Switch)
Elapsed: 0.00
timing for: Complete Installation
Elapsed: 3.37
SQL>
あらかじめ、日本語リソースをロードしておきます。load_trans.sqlを使う方法は、sys.dbms_registry.schema('APEX')の結果を見てcurrent_schemaを決定するため、APEXのアップグレードが完了してからでないと使えません。なので、以前の手法を使って日本語リソースをロードします。
一旦SQLPlusを終了し、日本語リソースが含まれるディレクトリへ移動します。
cd builder/ja
export NLS_LANG=American_America.AL32UTF8
sqlplus sys/<SYSのパスワード>@localhost/freepdb2 as sysdba
[oracle@apex-test apex]$ cd builder/ja
[oracle@apex-test ja]$ export NLS_LANG=American_America.AL32UTF8
[oracle@apex-test ja]$ sqlplus sys/<SYSのパスワード>@localhost/freepdb2 as sysdba
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue May 30 14:32:35 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL>
alter session set current_schema=APEX_230100;
@load_ja
SQL> alter session set current_schema=APEX_230100;
Session altered.
SQL> @load_ja
. ORACLE
.
. Application Express Hosted Development Service Installation.
..............................................................
declare
*
ERROR at line 1:
ORA-01741: illegal zero-length identifier
ORA-06512: at line 6
--application/set_environment
APPLICATION 4420 - Oracle APEX Builder, Wizard Messages and Native Plug-Ins
--application/delete_application
--application/create_application
--application/user_interfaces
[中略]
--application/pages/page_00204
--application/pages/page_00205
--application/pages/page_00206
--application/end_environment
...done
Adjust instance settings
PL/SQL procedure successfully completed.
SQL> exit
alter session set "_ORACLE_SCRIPT"=true;
@apexins3 SYSAUX SYSAUX TEMP /i/23.1.0/
[oracle@apex-test ~]$ cd $HOME/apex
[oracle@apex-test apex]$ sqlplus sys/<SYSのパスワード>@localhost/freepdb2 as sysdba
SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Wed May 31 09:52:51 2023
Version 23.2.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> alter session set "_ORACLE_SCRIPT"=true;
Session altered.
SQL> @apexins3 SYSAUX SYSAUX TEMP /i/23.1.0/
PL/SQL procedure successfully completed.
Session altered.
FOO3
------------------------------
install2023-05-31_09-53-09.log
. ORACLE
.
. Oracle APEX Installation.
..........................................
.
...set_appun.sql
... Checking prerequisites (MANUAL)
[中略]
Thank you for installing Oracle APEX 23.1.0
Oracle APEX is installed in the APEX_230100 schema.
The structure of the link to the Oracle APEX administration services is as follows:
http://host:port/ords/apex_admin
The structure of the link to the Oracle APEX development interface is as follows:
http://host:port/ords
timing for: Phase 3 (Switch)
Elapsed: 0.42
timing for: Complete Installation
Elapsed: 0.42
SYS> exit
Disconnected from Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
[oracle@apex-test apex]$
6.12 Performing Post Installation Tasks for Upgrade Installations
不要になったスキーマを確認します。APEXのアップグレードを行ったPDBにSYSで接続し、以下のSELECT文を実行します。
SELECT username
FROM dba_users
WHERE ( username LIKE 'FLOWS\_______' ESCAPE '\'
OR username LIKE 'APEX\_______' ESCAPE '\' )
AND username NOT IN ( SELECT schema
FROM dba_registry
WHERE comp_id = 'APEX' );SQL> SELECT username
FROM dba_users
WHERE ( username LIKE 'FLOWS\_______' ESCAPE '\'
OR username LIKE 'APEX\_______' ESCAPE '\' )
AND username NOT IN ( SELECT schema
FROM dba_registry
WHERE comp_id = 'APEX' ); 2 3 4 5 6 7
USERNAME
--------------------------------------------------------------------------------
APEX_210100
SQL>
リストされたスキーマを削除します。今回の例ではAPEX_210100です。
col owner format a22
col object_type format a22
col object_name format a30
select owner, object_type, object_name from dba_objects where status = 'INVALID';
SQL> set pages 1000 lines 180 trims on trimo on
SQL> col owner format a22
SQL> col object_type format a22
SQL> col object_name format a30
SQL> select owner, object_type, object_name from dba_objects where status = 'INVALID';
OWNER OBJECT_TYPE OBJECT_NAME
---------------------- ---------------------- ------------------------------
APEX_210100 PACKAGE BODY WWV_FLOW_AUTHENTICATION_NATIVE
APEX_210100 PACKAGE BODY WWV_FLOW_CUSTOM_AUTH_STD
APEX_210100 PACKAGE BODY WWV_FLOW_AUTHENTICATION_DEV
SQL>
drop user apex_210100 cascade;
drop package sys.wwv_dbms_sql_apex_210100;
SQL> alter session set "_ORACLE_SCRIPT"=true;
Session altered.
SQL> drop user apex_210100 cascade;
User dropped.
SQL> drop package sys.wwv_dbms_sql_apex_210100;
Package dropped.
SQL>
SQL> select owner, object_type, object_name from dba_objects where status = 'INVALID';
no rows selected
SQL>
インバリッド・オブジェクトが増えていなければ、特に問題なく以前のスキーマの削除ができたと考えられます。
set serveroutput on
exec sys.validate_apex;
SQL> select status from dba_registry where comp_id = 'APEX';
STATUS
-----------
VALID
SQL>
SQL> set serveroutput on
SQL> exec sys.validate_apex;
...(13:17:23) Starting validate_apex for APEX_230100
...(13:17:24) Checking missing sys privileges
...(13:17:24) Re-generating APEX_230100.wwv_flow_db_version
... wwv_flow_db_version is up to date
...(13:17:24) Checking invalid public synonyms
...(13:17:24) Key object existence check
...(13:17:24) Setting DBMS Registry for APEX to valid
...(13:17:24) Exiting validate_apex
PL/SQL procedure successfully completed.
SQL>
APEXのアップグレードに関する作業は以上で完了です。
Adding APEX 23.1 to your Database Free VM
今回の例に合わせてみます。クローンとして作成したFREEPDB2にSYSで接続し、表領域APEX231を作成します。
datafile '/opt/oracle/oradata/FREE/FREEPDB2/apex231.dbf'
size 100m autoextend on next 100m;
SQL> create tablespace apex231
2 datafile '/opt/oracle/oradata/FREE/FREEPDB2/apex231.dbf'
3 size 100m autoextend on next 100m;
Tablespace created.
SQL> @apexins APEX231 APEX231 TEMP /i/23.1.0/
SYS> select bytes, user_bytes, blocks, user_blocks from dba_data_files where tablespace_name = 'APEX231';
BYTES USER_BYTES BLOCKS USER_BLOCKS
---------- ---------- ---------- -----------
314572800 313524224 38400 38272
1 row selected.
SYS>
SQL> drop tablespace apex231 including contents and datafiles;
Tablespace dropped.
SQL>
Oracle Database 23c Freeのように、使用可能なディスク容量に厳しい制限がある場合に有効な手順といえます。
APEXインストール・プロセスの自動化
alter session set "_ORACLE_SCRIPT"=true;
drop user apex_listener cascade;
drop user apex_rest_public_user;
@/opt/oracle/product/23c/dbhomeFree/rdbms/admin/utlrp
select owner, object_type, object_name from dba_objects where status = 'INVALID';
SQL> select username from dba_users where username like 'APEX%';
USERNAME
--------------------------------------------------------------------------------
APEX_LISTENER
APEX_PUBLIC_USER
APEX_REST_PUBLIC_USER
APEX_230100
SQL> alter session set "_ORACLE_SCRIPT"=true;
Session altered.
SQL> drop user apex_listener cascade;
User dropped.
SQL> drop user apex_rest_public_user ;
User dropped.
SQL> @/opt/oracle/product/23c/dbhomeFree/rdbms/admin/utlrp
Session altered.
PL/SQL procedure successfully completed.
[中略]
SQL> select owner, object_type, object_name from dba_objects where status = 'INVALID';
no rows selected
SQL>
