How to install and configure Oracle Apex 24.1 with ORDS 22, Tomcat 9 and Jasper Report 7 on Oracle Linux 8.10

#########################Install Oracle 

APEX 24.1################################

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

unzip apex_24.1_en.zip

cd APEX

sqlplus sys as sysdba

show pdbs

show con_name

#now change then session into orclpdb

#To open the specific pluggable database, run:

#If you want to open all PDBs at once, use:

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

ALTER SESSION SET CONTAINER = orclpdb;

ALTER PLUGGABLE DATABASE orclpdb OPEN;

ALTER PLUGGABLE DATABASE ALL OPEN;

# create a tablespace named apex when we want to put all our data/metadata

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

create tablespace apex datafile '/u02/oradata/ORCL/orclpdb/apex01.dbf' size 500m autoextend on next 100m;

# For starting installation follow the underneath step that is avaialbe in

  apex directory

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

@apexins.sql apex apex temp /i/

# Now we are going to set our admin, email & password and this username and password 

  must be need to access workspace as internal

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

@apxchpwd.sql

Password: Barishal2024#

# As we are going to use rest service that is why we configure rest public 

  user and listener

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

@apex_rest_config.sql

###################Our Oracle apex 24.1 installation is completed#############

###################Oracle Rest Data Service ORDS 22.1 

           Installation and configuration###################

# For installation of ORDS 22.1 or Upper, we must need java/jdk 11 or upper

# Check jdk version

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

java -version

---------------------------------Installation of jdk 11-----------------------------------

# At first download jdk-11.0.19_linux-x64_bin.tar.gz compressed archive file from oracle official website

# Move it /u0l directory and unzip

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

tar xzf jdk-11.0.12_linux-x64_bin.tar.gz

# Set environment Variable

# We can set it setEnv.sh file or .bash_profile

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

# vim /home/oracle/.bash_profile

export JAVA_HOME=/u01/jdk-11.0.12

export PATH=$JAVA_HOME/bin:$PATH

..bash_profile or  . /home/oracle/.bash_profile

# Now check again

java -version

#if it is ok then we will move to install ORDS 22.1

###################Installation and configuration of ORDS 24.1###################

# For starting Installation, at first we must setup some environment variables

# Just open the bash_profile for setting env variables

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

export ORDS_HOME=/home/oracle/ords

export ORDS_CONFIG=/home/oracle/ords/config

or

export ORDS_HOME=/u01/ords

export ORDS_CONFIG=$ORDS_HOME/config

. .bash_profile

#Create a directory named ords or your preferable name

#Move the ords-22.1.0.105.1723.zip file into ords directory and Unzip it

#[oracle@ol8db19capex u01]$ mv /u01/ords-22.1.0.105.1723.zip /u01/ords

[oracle@ol8db19capex ords]$ unzip ords-22.1.0.105.1723.zip

[oracle@ol8db19capex ords]$

[oracle@ol8db19capex ords]$ . /home/oracle/.bash_profile

[oracle@ol8db19capex ords]$

[oracle@ol8db19capex ords]$

[oracle@ol8db19capex ords]$ echo $ORDS_HOME

/u01/ords

[oracle@ol8db19capex ords]$

[oracle@ol8db19capex ords]$ echo $ORDS_CONFIG

/u01/ords/config

[oracle@ol8db19capex ords]$

#Create a directory into ords folder

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

[oracle@ol8db19capex ords]$ mkdir config

#Now just type then comand {}

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

[oracle@ol8db19capex ords]$ $ORDS_HOME/bin/ords --config ${ORDS_CONFIG} install

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

#THEN

#Choose option 2 for Create or update a database pool and install/upgrade ORDS in the database

#Press enter for leave it defaul

#Choose option 1 for Basic (host name, port, service name)

#Press enter for leave it defaul

#hostname/ip address

#port

#service name       ===== Enter the database service name [orcl]: orclpdb

#administrator username:sys

#password:

#and confirmation password

#Set default tablespace as we created when we install oracle apex 24.1 Our default tablespace is

#Default tablespace (SYSAUX): apex

#Temporary Space will be (TEMP): temp

#choose 1 one for enable all

#Press enter for leave it defaul

#Press 2 for configure with other server (like tomcat, weblogic) and we do not use standalone mode

#Press 2 for exiting as we want to use our tomcat server

#Heap Memory Settings for 8 GB RAM (25%-50%) Base on workload

#(25% to 50% of the available RAM for Java heap memory, depending on the workload)

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

#Set another environment variables for ORDS into .bash_profile

#for change heap memory, change it according to your RAM

#Server RAM 4GB

export JAVA_OPTS="-Dconfig.url=${ORDS_CONFIG} -Xms512M -Xmx1024M"

#OR

#Server RAM 8GB

export JAVA_OPTS="-Dconfig.url=${ORDS_CONFIG} -Xms2048M -Xmx4096M"

..bash profile

###################Installation and configuration of apache-tomcat-9.0.54###################

#For starting Installation, at first we must setup some environment variables

#Just open the .bash_profile for setting env variables

export CATALINA_HOME=/u01/tomcat

export CATALINA_BASE=/u01/tomcat

. .bash_profile

#Download apache-tomcat-9 from official website and unzip it

#Move the apache-tomcat-9.0.54.tar.gz file into tomcat directory and Unzip it

#Move to /u01 directory and then

#Create a directory

mkdir tomcat

[oracle@ol8db19capex u01]$ mv /u01/apache-tomcat-9.0.98.tar.gz /u01/tomcat/

[oracle@ol8db19capex u01]$ cd tomcat/

[oracle@ol8db19capex tomcat]$ ls

apache-tomcat-9.0.98.tar.gz

[oracle@ol8db19capex tomcat]$ tar xzf apache-tomcat-9.0.54.tar.gz

#Go to/u01/tomcat/bin and start tomcat

sh startup.sh 

or 

sh shutdown.sh

#Now let's check in the browser like

192.168.0.4:8080

#let's do some configuration

#Directory located to /u01/tomcat/conf and open

#tomcat-users.xml by a editor

vi tomcat-users.xml.

#Chage roles, user and password

<role rolename="manager-gui"/>

  <user username="admin" password="admin" roles="manager-gui"/>

#Open server.xml in the same directory

vi server.xml

#Change default port number

#in my case it is 8888

    <Connector port="8888" protocol="HTTP/1.1"

               connectionTimeout="20000"

               redirectPort="8443"

               maxParameterCount="1000"

               />

    <!-- A "Connector" using the shared thread pool-->

    <!--

    <Connector executor="tomcatThreadPool"

               port="8888" protocol="HTTP/1.1"

               connectionTimeout="20000"

               redirectPort="8443"

               maxParameterCount="1000"

               />

#lets some configure in 

#Go to /u01/tomcat/webapps/host-manager/META-INF

#just open it by editor

vi context.xml

#Allowing IP for accessing to admin in host-manager

  <Valve className="org.apache.catalina.valves.RemoteAddrValve"

         allow="127\.\d+\.\d+\.\d+|::1|0:0:0:0:0:0:0:1|192.168.0.4" />

#Go to /u01/tomcat/webapps/manager/META-INF

#just open it by editor

vi context.xml

#Alowing IP for accessing to admin in manager

  <Valve className="org.apache.catalina.valves.RemoteAddrValve"

         allow="127\.\d+\.\d+\.\d+|::1|0:0:0:0:0:0:0:1|192.168.0.4" />

#Now copy ords.war and apex images to /u01/tomcat/webapps/ directory and 

go to ords and apex one afer another directory and run the command 

cp ords.war /u01/tomcat/webapps/

or 

[oracle@ol8db19capex ~]$ cd /u01/tomcat/bin/

[oracle@ol8db19capex bin]$ cp $ORDS_HOME/ords.war $CATALINA_HOME/webapps/

#create a directory into the /u01/tomcat/webapps/ and named it as i

[oracle@ol8db19capex webapps]$ mkdir i

cp/u01/apex/images/* to /u01/tomcat/webapps/i/

or 

[oracle@ol8db19capex webapps]$ cp -r /u01/apex/images/* /u01/tomcat/webapps/i/

#After that just restart tomcat

sh shutdown.sh

sh startup.sh

#http://192.168.0.4:8888/ords

#if ords face ok then

Workspace: Internal

Username: Admin

Password: Admin

#or face error then solution to try 

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

#open the pluggable database

#check lisner or stop to start

#check lisner and tnsname.ora file base ip (when use ip ords configure)

#shutdown or start tomcat server 

#check oracle apex user locked to unlock

conn sys as sysdba

show pdbs

show con_name

ALTER SESSION SET CONTAINER = orclpdb;

ALTER PLUGGABLE DATABASE orclpdb OPEN;

ALTER PLUGGABLE DATABASE ALL OPEN;

lsnrctl status

lsnrctl start

lsnrctl stop

#/u01/app/oracle/product/19.0.0/dbhome_1/network/admin

listener.ora

tnsnames.ora

cd /u01/tomcat/bin or cd $CATALINA_HOME/bin

sh startup.sh

sh shutdown.sh

SELECT * FROM DBA_USERS WHERE USERNAME LIKE '%APEX%'

ALTER USER APEX_240100 IDENTIFIED BY Barishal2024# ACCOUNT UNLOCK;

CREATE USER MIS IDENTIFIED BY 123456 ACCOUNT UNLOCK

GRANT DBA TO MIS;

MIS

ADMIN

123456

#/home/oracle

vi tomcat_start.sh

cd $CATALINA_HOME/bin

./startup.sh

vi tomcat_stop.sh

cd $CATALINA_HOME/bin

./shutdown.sh

########Ords Installation and deployment are completed########

##########################Jasper (jri-2.7.0-jasper-6.16.0) installation and Configuration##########################

#For starting Installation, at first we must setup some environment variables

#Just open the .bash_profile for setting env variables

#Download link https://github.com/daust/JasperReportsIntegration/releases/tag/v2.7.0

[oracle@ol8db19capex ~]$ vim /home/oracle/.bash_profile

[oracle@ol8db19capex ~]$

[oracle@ol8db19capex ~]$ . /home/oracle/.bash_profile

export JAVA_OPTS="$JAVA_OPTS -Djava.awt.headless=true"

#check it

[oracle@ol8db19capex ~]$ echo $JAVA_OPTS

#Move then jri-3.0.0-jasper-7.0.1.zip to /u01/ and unzip it

#Create a directory maybe name jasper

[oracle@ol8db19capex u01]$ mkdir jasper

[oracle@ol8db19capex u01]$ mv jri-3.0.0-jasper-7.0.1.zip /u01/jasper/

unzip jri-3.0.0-jasper-7.0.1.zip

#Change jri.war file to jasper.war

#shutdown tomcat and copy jasper.war to move /u01/tomcat/webapps/

#Go to/u01/jasper/bin

./setConfigDir.sh /u01/tomcat/webapps/jasper.war /u01/jasper

#Go to/u01/jasper/conf/ directory and open application.properties 

and set datasource and user cadential

#====================================================================

[datasource:default]

type=jdbc

url=jdbc:oracle:thin:@127.0.0.1:1521:XE

username=my_oracle_user

password=my_oracle_user_pwd

[datasource:mis]

type=jdbc

url=jdbc:oracle:thin:@192.168.0.4:1521/orclpdb

username=mis

password=123456

# this parameter is limiting access to the integration for the 

# specified list of ip addresses, e.g.: 

# ipAddressesAllowed=127.0.0.1,10.10.10.10,192.168.178.31

# if the list is empty, ALL addresses are allowed

#====================================================================

#now encrypt the Password

#Go to/u01/jasper/bin

./encryptPasswords.sh /u01/jasper/conf/application.properties

#Restart the tomcat and test then jasper report

#How to call jasper report from oracle apex

#Set substitution string to shared components>application definition

Substitution String> REPORT_PATH

Substitution Value>

http://150.230.55.31:9876/jasper/report?

------------------------------Excel

File--------------------------------------------------

var report_server = "&REPORT_PATH.";

var rep_locale = "en_US";

var file_name = "Account Ledger Report.xlsx";

var rep_encode = "UTF-8";

var report_name = "ACCOUNT_LEDGER_EXCEL";

var report_format = "xlsx";

var data_source = "epos";

var ap_1 = $v("P48_BRANCH");

var rp_1 = "&P_BM_ID";

var ap_2 = $v("P48_START_DATE");

var rp_2 = "&P_START_DATE";

var ap_3 = $v ("P48_END_DATE");

var rp_3 3 = "&P_END_DATE";

var ap_4 = $v("P48_TXT_ACC");

var rp_4 = "&P_AD ID";

var param =

""+rp_1+"="+ap_1+""+rp_2+"="+ap_2+""+rp_3+"="+ap_3+ ""+rp_4+"="+ap_4+"";

var report =

""+report_server+"&_repName="+report_name

  +"&_repFormat="+report_format+"&_dataSource="+data_source+"&_repLocale="+rep_locale+"&_outFilename="

  +file_name+"&_repEncoding="+rep_encode+"";

var run_report = ""+report+""+param+"";

window.open(run_report);

------------------------------PDF

File------------------------------------------------------------

var report_server = "&REPORT_PATH.";

var rep_locale = "en_US";

var rep_encode = "UTF-8";

var report name "ACCOUNT LEDGER";

var report_format = "pdf";

var data source = "mis_db_schema"; 

var ap_1 = $v ("P48_BRANCH");

var rp_1 = "&P_BM_ID";

var ap_2 = $v("P48_START_DATE");

var rp_2 = "&P_START_DATE";

var ap 3 = $v ("P48_END_DATE");

var rp_3 = "&P_END_DATE";

var ap_4 = $v ("P48_TXT_ACC");

var rp_4 = "&P_AD_ID";

var param =

""+rp_1+"="+ap_1+""+rp_2+"="+ap_2+""+rp_3+"="+ap_3+ ""+rp_4+"="+ap_4+"";

var report =

""+report_server+"&_repName="+report_name

  +"&_repFormat="+report_format+"&_dataSource="+data_ source+"&_repLocale="+rep_locale+"&_repEncoding="+r_ep_encode+"";

var run_report = ""+report+""+param+"";

window.open(run_report);

#############################OK#############################

ORDS Install (ORDS 22.1)

 [oracle@ol8db19capex ords]$ $ORDS_HOME/bin/ords --config ${ORDS_CONFIG} install

2025-01-11T07:00:06.510Z INFO        Your configuration folder /u01/ords/config is located in ORDS product folder.  Oracle recommends to use a different configuration folder.

ORDS: Release 22.1 Production on Sat Jan 11 07:00:06 2025

Copyright (c) 2010, 2025, Oracle.

Configuration:

  /u01/ords/config/

The configuration folder /u01/ords/config does not contain any configuration files.

Oracle REST Data Services - Interactive Install

  Enter a number to select the type of installation

    [1] Install or upgrade ORDS in the database only

    [2] Create or update a database pool and install/upgrade ORDS in the database

    [3] Create or update a database pool only

  Choose [2]:

  Enter a number to select the database connection type to use

    [1] Basic (host name, port, service name)

    [2] TNS (TNS alias, TNS directory)

    [3] Custom database URL

  Choose [1]:

  Enter the database host name [localhost]: 192.168.0.4

  Enter the database listen port [1521]:

  Enter the database service name [orcl]: orclpdb

  Provide database user name with administrator privileges.

    Enter the administrator username: sys

  Enter the database password for SYS AS SYSDBA:

Connecting to database user: SYS AS SYSDBA url: jdbc:oracle:thin:@//192.168.0.4:1521/orclpdb

Retrieving information.

  Enter the default tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [SYSAUX]: apex

  Enter the temporary tablespace for ORDS_METADATA and ORDS_PUBLIC_USER [TEMP]: temp

  Enter a number to select additional feature(s) to enable:

    [1] Database Actions  (Enables all features)

    [2] REST Enabled SQL and Database API

    [3] REST Enabled SQL

    [4] Database API

    [5] None

  Choose [1]:

  Enter a number to configure and start ORDS in standalone mode

    [1] Configure and start ORDS in standalone mode

    [2] Skip

  Choose [1]: 2

Skip for Installation Tomcat

ORDS Uninstall (ORDS 24.1)

 [oracle@ol8db19capex ords]$

[oracle@ol8db19capex ords]$ $ORDS_HOME/bin/ords --config ${ORDS_CONFIG} uninstall

2025-01-11T06:58:39.658Z INFO        Your configuration folder /u01/ords/config is located in ORDS product folder.  Oracle recommends to use a different configuration folder.

ORDS: Release 22.1 Production on Sat Jan 11 06:58:39 2025

Copyright (c) 2010, 2025, Oracle.

Configuration:

  /u01/ords/config/

The configuration folder /u01/ords/config does not contain any configuration files.

Oracle REST Data Services - Interactive Uninstall

  Enter a number to select the database connection type to use

    [1] Basic (host name, port, service name)

    [2] TNS (TNS alias, TNS directory)

    [3] Custom database URL

1

  Enter the database host name [localhost]: 192.168.0.4

  Enter the database listen port [1521]:

  Enter the database service name [orcl]: orclpdb

  Provide database user name with administrator privileges.

    Enter the administrator username: sys

  Enter the database password for SYS AS SYSDBA:

Uninstall 

[oracle@ol8db19capex ords]$

Anydesk start when windows open/start

Run: Windows  + R

shell:startup

Paste