Installing Oracle APEX and Configuring Oracle REST Data Services on Window

Downloading and Installing Oracle Application Express on Windows

· Installing Application Express https://www.oracle.com/tools/downloads/apex-downloads.html

· Unzip downloaded zip file to C:\Oracle\apex_xx.x

· C:\apex_xx.x> sqlplus or sqlplus '/ as sysdba'

· Connect to container database

SQL> -- EZCONNECT (Direct)

SQL> CONN sys/password@//localhost:1521/XE as sysdba

SQL> -- tnsnames.ora

SQL> CONN sys/password@XE

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 XEPDB1 READ WRITE NO

· Create PDBs on 18c here the steps How to Creating a PDB, Oracle 12c on Windows

· Set session to the Pluggable database

SQL>ALTER SESSION SET CONTAINER = XEPDB2;

SQL>COLUMN CON_NAME FORMAT A20

SQL>COLUMN INSTANCE_NAME FORMAT A20

SQL>SELECT CON_NAME, INSTANCE_NAME, STATE FROM DBA_PDB_SAVED_STATES;

SQL>SELECT COMP_ID, VERSION, STATUS FROM CDB_REGISTRY WHERE COMP_ID='APEX' ORDER BY CON_ID;

SQL>SELECT R.COMP_NAME, R.VERSION, C.NAME, C.CON_ID FROM CDB_REGISTRY R, V$CONTAINERS C WHERE R.CON_ID=C.CON_ID AND R.COMP_ID='APEX' ORDER BY CON_ID;

· Create Table Space

CREATE TABLESPACE APEX

LOGGING

DATAFILE 'C:\ORACLE\APP\PRODUCT\18.0.0\ORADATA\XE\XEPDB2\APEX.DBF' SIZE 1024M

AUTOEXTEND ON

NEXT 64M MAXSIZE 5G

EXTENT MANAGEMENT LOCAL

· Login to Container Database and Alter MEMORY_TARGET

SQL> conn sys/naeem123@//localhost:1521/XE as sysdba

Connected.

SQL> ALTER SYSTEM SET MEMORY_TARGET='300M' SCOPE=spfile;

ALTER SYSTEM SET MEMORY_TARGET='300M' SCOPE=spfile;

· Install APEX to new PDB

SQL> CONN SYS/NAEEM123@//LOCALHOST:1521/XEPDB2 AS SYSDBA

--Full development environment

SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/

--Runtime environment

SQL> @apxrtins.sql SYSAUX SYSAUX TEMP /i/

· Change Apex Admin Password

Running the apxchpwd.sql script enables you to create or update your Instance Administrator account.

SQL> @apxchpwd.sql

· Restarting Processes

· Configuring the APEX_PUBLIC_USER Account

SQL> SELECT USERNAME,ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME LIKE 'APEX%;

SQL>ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK IDENTIFIED BY ADMIN_123;

SQL>ALTER USER APEX_INSTANCE_ADMIN_USER ACCOUNT UNLOCK IDENTIFIED BY ADMIN_123;

SQL>ALTER USER APEX_200200 ACCOUNT UNLOCK IDENTIFIED BY ADMIN_123;

· Create the APEX_LISTENER and APEX_REST_PUBLIC_USER users by running the "apex_rest_config.sql" script.

SQL> @apex_rest_config.sql -- All password --> admin_123

If you want to add these users silently, you can specify the passwords as parameters to the script.

SQL> @apex_rest_config.sql ApexPassword1 ApexPassword2

SQL> select username,account_status from dba_users where username like 'APEX%';

ALTER USER APEX_LISTENER ACCOUNT UNLOCK identified by admin_123;

ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK identified by admin_123;

ALTER USER APEX_REST_PUBLIC_USER ACCOUNT UNLOCK identified by admin_123;

ALTER USER APEX_INSTANCE_ADMIN_USER ACCOUNT UNLOCK identified by admin_123;

ALTER USER APEX_200200 ACCOUNT UNLOCK identified by admin_123;

SQL> select username,account_status from dba_users where username like 'ORDS%';

ALTER USER ORDS_METADATA ACCOUNT UNLOCK identified by admin_123;

ALTER USER ORDS_PUBLIC_USER ACCOUNT UNLOCK identified by admin_123;

ALTER USER ORDSYS ACCOUNT UNLOCK identified by admin_123;

· Granting connecting privileges

For Oracle Database version 12c or later run the below script: Enabling Network Services in Oracle Database 11g or Later , Granting connecting privileges to any host for the APEX_200200 database user.

BEGIN

DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (

HOST => '*',

ace =>

xs$ace_type (privilege_list => xs$name_list ('connect'),

principal_name => 'APEX_200200',

principal_type => xs_acl.ptype_db));

END;

COMMIT;

· Granting connecting privileges to access to local network resources.

BEGIN

DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (

HOST => 'localhost',

ace =>

xs$ace_type (privilege_list => xs$name_list ('connect'),

principal_name => 'APEX_200200',

principal_type => xs_acl.ptype_db));

END;

COMMIT;

· Granting Connect Privileges Prior to Oracle Database 12c

DECLARE

ACL_PATH VARCHAR2(4000);

BEGIN

-- Look for the ACL currently assigned to '*' and give APEX_190200, the "connect" privilege if APEX_190200 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;

· Disabling Oracle XML DB Protocol Server

If you were previously using the embedded PL/SQL gateway and now want to use Oracle REST Data Services, you need to disable the Oracle XML DB Protocol Server.

EXEC DBMS_XDB.sethttpport(0);

Downloading, Installing and Configuring Oracle REST Data Services

· Installing and Configuring Oracle REST Data Services

o Download jdk -- > https://www.oracle.com/java/technologies/javase-downloads.html

o Download Ords --> https://www.oracle.com/database/technologies/appdev/rest-data-services-v192-downloads.html

· Unzip ords.xxx.xx file and rename it to ords C:\Oracle\ords

· Configuring Oracle REST Data Services

· Copying the images directory from apex directory (C:\Oracle\apex_xx.x\apex\images) and past to ords folder (C:\Oracle\ords\images) .

· Validating And Install the Oracle REST Data Services Installation

C:\Oracle\ords>java -jar ords.war validate XE –- for CDB

C:\Oracle\ords>java -jar ords.war validate XEPDB2 –- for PDB

C:\Oracle\ords>java -jar ords.war XEPDB2

Enter the name of the database server [localhost]:

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:XEPDB2

Enter the database password for ORDS_PUBLIC_USER:

Confirm password:

Retrieving information.

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 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]:1

2021-01-22T08:23:57.288Z INFO reloaded pools: []

2021-01-22T08:23:57.289Z INFO Oracle REST Data Services schema version 20.4.1.r0131644 is installed.

Enter 1 if you wish to start in standalone mode or 2 to exit [1]:

Enter the APEX static resources location:C:\Oracle\apex_20.2\apex\images

Enter 1 if using HTTP or 2 if using HTTPS [1]:

2021-01-22T08:24:19.056Z INFO HTTP and HTTP/2 cleartext listening on host: localhost port: 8081

Open Apex in browser URL http://localhost:8081/ords

Reference

https://docs.oracle.com/en/database/oracle/application-express/20.2/htmig/installing-and-configuring-apex-and-ords.html#GUID-8EE981AD-B1F9-46C2-BB5A-C9FE60CCD4AA

https://oracle-base.com/articles/misc/oracle-application-express-apex-installation

NaeemZayid

Search This Blog