https://tedstruik-oracle.nl/ords/f?p=25384:1133::::::
Thursday, September 2, 2021
Thursday, July 22, 2021
Oracle Apex Image Prefix.
CD APEX21.1
CD APEX
CD UTILITIES
----------- F:\apex_21.1\apex\utilities :
SQLPLUS /NOLOG
CONN SYS/SYS@ORCLPDB2 AS SYSDBA
@reset_image_prefix.sql
Enter the Application Express image prefix [/i/] /images211/
...Changing Application Express image prefix
NEW_IMAGE_P
/images211/
Wednesday, June 2, 2021
The connection pool named: |apex|rt| is not correctly configured, due to the following error(s): ORA-28000: The account is locked.
The connection pool named: |apex|rt| is not correctly configured, due to the following error(s): ORA-28000: The account is locked.
Check if there is any Locked User :
CONN SYS/SYS@ORCLPDB AS SYSDBA
select username, account_status, profile from dba_users
where username in ('APEX_PUBLIC_USER','APEX_LISTENER','APEX_REST_PUBLIC_USER');
Run the following :
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY Apex123 ACCOUNT UNLOCK;
ALTER USER APEX_LISTENER IDENTIFIED BY Apex123 ACCOUNT UNLOCK;
ALTER USER APEX_REST_PUBLIC_USER identified BY Apex123 ACCOUNT UNLOCK;
ALTER USER APEX_200100 IDENTIFIED BY Apex123 ACCOUNT UNLOCK;
ALTER USER APEX_INSTANCE_ADMIN_USER IDENTIFIED BY Apex123 ACCOUNT UNLOCK;
Windows :
Go to : D:\ords\conf\ords\conf
Give Password at each xml file like : !Apex123 [Must add an ! exclamatory sign. before password. ]
Files are :
apex.xml
apex_al.xml
apex_pu.xml
apex_rt.xml
apex.xml :
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Wed Jun 02 22:29:52 BDT 2021</comment>
<entry key="db.password">!Apex123</entry>
<entry key="db.username">APEX_PUBLIC_USER</entry>
</properties>
apex_al.xml :
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Wed Jun 02 23:12:48 BDT 2021</comment>
<entry key="db.password">!Apex123</entry>
<entry key="db.username">APEX_LISTENER</entry>
</properties>
apex_pu.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Wed Jun 02 23:12:48 BDT 2021</comment>
<entry key="db.password">!Apex123</entry>
<entry key="db.username">ORDS_PUBLIC_USER</entry>
</properties>
apex_rt.xml :
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Wed Jun 02 23:12:48 BDT 2021</comment>
<entry key="db.password">!Apex123</entry>
<entry key="db.username">APEX_REST_PUBLIC_USER</entry>
</properties>
Now Uninstall ORDS with the following command :
D:
CD ORDS
"C:\Program Files\Java\jdk-15.0.1\bin\java" -jar ords.war uninstall
Now Install ORDS with following command :
"C:\Program Files\Java\jdk-15.0.1\bin\java" -jar ords.war install advanced
copy ords.war from
D:\ords
and paste to
C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps
Now Restart Services :
Now Restart Tomcat.
Now run Apex with ORDS :
localhost:8080/ords
or
127.0.0.1:8080/ords
Sunday, May 30, 2021
How to Integrate Oracle 10g Reports with Oracle Apex
Script 1:
var r_server = "http://192.168.100.100/reports/rwservlet?server=rep_appsrv1_asbiforms10&destype=cache&desformat";
var r_branch_code = $v("P100_BRANCH");
var r_emp_id = $v("P100_emp_NO");
var r_link = ""+r_server+"=pdf&userid=HR/HR@HR&REPORT=D:/A_REPORT/FUND_REPAYMENT_LETTER.rdf&P_BRANCH_CODE="+r_branch_code+"&P_REPAYMENT_NO="+r_emp_id+"";
window.open(r_link);
Script 2 :
var serIp = $v("P14_SERIP");
var catId = $v("P14_ITEM_CAT");
var scatId = $v("P14_ITEM_SUB_CAT");
var coaId = $v("P14_COA_TYPE");
var reportId = '&CATID='+catId+'&SCATID='+scatId+'&COAID='+coaId;
if ($v("14_RG")==1)
window.open(serIp+'COMPANY_INFO.rdf&SOMID=1');
else if ($v("14_RG")==2)
window.open(serIp+'DEPT_INFO.rdf');
else if ($v("14_RG")==3)
window.open(serIp+'DESIG_INFO.rdf');
else if ($v("14_RG")==4)
window.open(serIp+'LINE_INFO.rdf');
else if ($v("14_RG")==5)
window.open(serIp+'BLOCK_INFO.rdf');
else if ($v("14_RG")==6)
window.open(serIp+'BUYER_INFO.rdf');
else if ($v("14_RG")==7)
window.open(serIp+'SUPPLIER_INFO.rdf');
else if ($v("14_RG")==8)
window.open(serIp+'ITEM_INFO.rdf'+reportId);
else if ($v("14_RG")==9)
window.open(serIp+'CHART_OF_ACCOUNTS.rdf');
DECLARE
A VARCHAR2(100);
B VARCHAR2(100);
C VARCHAR2(200);
D VARCHAR2(200);
E NUMBER;
BEGIN
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') INTO A FROM DUAL;
SELECT SUBSTR(A,0,3) INTO B FROM DUAL;
SELECT LENGTH(A) INTO E FROM DUAL;
SELECT LIC_LOCAL_IP,LIC_REAL_IP INTO C,D FROM ISP_RUNAUTO;
IF E>6 THEN
IF B=192 THEN RETURN C; ELSE RETURN D; END IF;
ELSE
RETURN C;
END IF;
END;
Friday, April 30, 2021
Oracle Apex Software Development Companies
Oracle Apex Software Development Companies (1st page on google.)
https://adjacentsolutions.com/
http://ogesinfotech.com/index.php
https://www.insum.ca/
https://pretius.com/main/
https://www.octalsoft.com/
https://www.foex.at
https://diceus.com
-----------------------------------------------------------------------------------------------------------------------
https://abacasys.com/oracle-apex-software-development-company-india/
https://magia-solutions.com/oracle-solutions/oracle-application-express/
https://spiresoftware.ca/oracle-apex-services/
https://www.insum.ca/apex-community/about-oracle-apex/
https://pretius.com/technologies/oracle-apex/
https://www.apexrnd.be/index.html
https://www.absolute-tech.com/products/oracle-apex-services/
https://www.miroconsulting.com/blog/licensing-oracle-apex/
https://opportunities.ledfaststart.com/global/en/job/P-100250/Oracle-APEX-Developer
https://lsgsolutions.com/services/oracle-apex/
Monday, April 19, 2021
Oracle Apex Date Picker to Text Field Dynamic Action PLsql
DECLARE
V VARCHAR2(10):='';
vAttdt date;
BEGIN
vAttdt := :p44_attdt;
:P44_ATTDT2:=TO_CHAR(VaTTDT,'RRRRMMDD');
END;
Jasper Soft Character to Date conversion in Expression Editor
click [..] to go to Expression Editor.
new SimpleDateFormat("yyyyMMdd").parse($P{P_ATTDT})
$P{P_ATTDT} ------------ > This is the Parameter or field.
Note : Month should be in MM format not in mm Format.
e.g. new SimpleDateFormat("yyyyMMdd").parse($P{P_ATTDT})
enjoy !!!
Friday, April 9, 2021
How to enable/disable ssh login through putty
Some time you may want to disable login through putty by the port 22 SSH. You have to reboot the pc after disable sshd. Then add the firewall ports. Start listener. Restart tomcat and start Apache.
Step 1 : Enable or disable sshd in vultr.com console after login
---- systemctl enable sshd
systemctl disable sshd
Step 2 : Restart the server.
init 6
Step 3 : Add firewall rules:
firewall-cmd --zone=public --add-port=80/tcp
firewall-cmd --zone=public --add-port=443/tcp
firewall-cmd --zone=public --add-port=1510/tcp
firewall-cmd --zone=public --add-port=1539/tcp
Step 3: Start listener :
. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
lsnrctl start
Step 4 : Restart tomcat :
cd /usr/local/tomcat/bin
./shutdown.sh
./startup.sh
Step 5 : Start Apache :
sudo systemctl start httpd
Tuesday, April 6, 2021
How to integrate Jasper Report in Linux
cd /opt/oracle/apex
copy jri-2.7.0-jasper-6.16.0.zip to /opt/oracle/apex ---- By SSH Secure file transfer
Download : https://github.com/daust/JasperReportsIntegration/releases
unzip jri-2.7.0-jasper-6.16.0.zip
mv jri-2.7.0-jasper-6.16.0 jri
cd /opt/oracle/apex/jri
mkdir conf
mkdir logs
mkdir reports
cd /opt/oracle/apex/jri/bin
chmod +x setConfigDir.sh
cd /opt/oracle/apex
mkdir rep
cd /opt/oracle/apex/jri/bin
chmod +x setConfigDir.sh
chmod +x /opt/oracle/apex/jri
./setConfigDir.sh /opt/oracle/apex/jri/webapp/jri.war /opt/oracle/apex/jri
cd /opt/oracle/apex/jri/conf
192.168.1.1
vi application.properties
---- Change IP Address, Port, DB, Username and password.
cp /opt/oracle/apex/jri/webapp/jri.war /usr/local/tomcat/webapps/
cd /usr/local/tomcat/bin
./shutdown.sh
./startup.sh
Copy Reports to : (.jasper)
/opt/oracle/apex/jri/reports
Javascript :
var report_server = "http://localhost:8080/jri/report?";
var report_name = "dept";
var report_format = "pdf";
var data_source = "default";
var run_report=""+report_server+"&_repName="+report_name+"&_repFormat="+report_format+"&_dataSource="+data_source+"";
window.open(run_report);
Javascript with parameter :
var report_server = "http://localhost:8080/jri/report?";
var report_name = "dept_param";
var report_format = "pdf";
var data_source = "default";
var ap_1 = $v("P3_DEPT");
var rp_1 = "&P_DEPT";
var param = ""+rp_1+"="+ap_1+"";
var report=""+report_server+"&_repName="+report_name+"&_repFormat="+report_format+"&_dataSource="+data_source+"";
var run_report = ""+report+""+param+"";
window.open(run_report);
Saturday, April 3, 2021
How to Install SSL Certbot of LetsEncrypt
How to Install SSL Certbot of LetsEncrypt :
vi /usr/local/tomcat/conf/server.xml
-- Change tomcat port to : 1610
cd /usr/local/tomcat/bin
./shutdown.sh
./startup.sh
firewall-cmd --zone=public --add-port=1610/tcp
firewall-cmd --zone=public --add-port=443/tcp
sudo systemctl start httpd
cd /etc/httpd/conf.d
vi 10-apex.conf
<VirtualHost *:80>
ServerName example.com
ServerAlias www.example.com
Redirect Permanent / https://example.com
ProxyRequests On
<Proxy *>
Order deny,allow
Allow from all
</Proxy>
<Location />
ProxyPreserveHost on
RequestHeader unset Origin
ProxyPass http://192.168.1.1:1610/
ProxyPassReverse http://192.168.1.1:1610/
</Location>
RewriteEngine on
RewriteCond %{SERVER_NAME} =example.com [OR]
RewriteCond %{SERVER_NAME} =www.example.com [OR]
RewriteRule ^ https://%{SERVER_NAME}%{REQUEST_URI} [END,NE,R=permanent]
</VirtualHost>
sudo yum install epel-release
sudo yum install certbot python2-certbot-apache mod_ssl
sudo certbot --apache -d example.com
Check the Status :
https://www.ssllabs.com/ssltest/analyze.html?d=example.com
--- Add Auto Renewal at crontab :
sudo crontab -e
-- Add this line at crontab
0 0,12 * * * python -c 'import random; import time; time.sleep(random.random() * 3600)' && certbot renew
sudo certbot renew --dry-run
vi /etc/hosts
192.168.0.1 example.com
192.168.0.1 www.example.comThursday, January 21, 2021
How to install Oracle Database 19c In Centos 7 Linux
Install Oracle Database 19c :
-----------------------------
pSSWRDa
---------------- /opt/oracle/product/19c/dbhome_1
Log in as root.
curl -o oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
yum -y localinstall oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
cd /root
# yum -y localinstall oracle-database-ee-19c.rpm
Change the port here:
vi /etc/sysconfig/oracledb_ORCLCDB-19c.conf
To configure a sample Oracle Database instance, run the following service configuration script:
mkdir -p /opt/oracle/oradata
vi /etc/hosts
157.230.243.130 srvr01
--- add ip address with hostname
/etc/init.d/oracledb_ORCLCDB-19c configure
This script creates a container database (ORCLCDB) with one pluggable database (ORCLPDB1)
and configures the listener at the default port (1521).
Review the status information that is displayed on your screen.
echo '# setting oracle database environment variables and aliases' >> /etc/profile.d/oraenv.sh
echo 'ORACLE_SID=ORCLCDB' >> /etc/profile.d/oraenv.sh
echo 'ORAENV_ASK=NO' >> /etc/profile.d/oraenv.sh
echo 'ORACLE_HOME=/opt/oracle/product/19c/dbhome_1' >> /etc/profile.d/oraenv.sh
echo '. /usr/local/bin/oraenv -s' >> /etc/profile.d/oraenv.sh
echo 'alias sqlplus="rlwrap sqlplus"' >> /etc/profile.d/oraenv.sh
echo 'alias rman="rlwrap rman"' >> /etc/profile.d/oraenv.sh
. /etc/profile.d/oraenv.sh
/opt/oracle/product/19c/dbhome_1
or vi /etc/profile.d/oraenv.sh
chmod 6751 $ORACLE_HOME/bin/*
systemctl enable oracledb_ORCLCDB-19c
# chown -R oracle:oinstall /opt
# chmod -R 775 /opt
chown -R oracle:dba /opt
chmod -R 777 /opt
su oracle
sqlplus / as sysdba
create pfile from spfile;
delete this parameter from pfile:
*.memory_target=1024m
create spfile from pfile;
startup force pfile='/opt/oracle/product/19c/dbhome_1/dbs/initORCL.ora';
COLUMN name FORMAT a20;
SELECT name,con_id FROM v$pdbs;
SELECT name as “Service Name” FROM v$active_services WHERE con_id=3;
SQLPLUS / as SYSDBA
SHOW con_name
ALTER SESSION SET CONTAINER=orclpdb1;
Note that since 12c Oracle Database has multitenant architecture, which means there could be several pluggable databases and
one multitenant container database. By default, the XEPDB1 pluggable database is created during the installation of XE.
To make it easier to connect to the pluggable database, I recommend editing of tnsnames.ora file and add there a new connection
descriptor that we are going to use:
vi /opt/oracle/product/18c/dbhomeXE/network/admin/sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION=8
vi /opt/oracle/product/18c/dbhomeXE/network/admin/tnsnames.ora
Add this record there below the standard XE record:
PDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 157.230.243.130)(PORT = 1539))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XEPDB1)
)
)
And save the changes.
alter session set "_ORACLE_SCRIPT"=true;
copy tnsnames to your windows oracle /network/admin folder.
Check connection from anywhere. e.g. Your Laptop or PC.
CONNECT WITHOUT GIVING ANY DATABASE NAME:
=========================================
sqlplus /nolog
conn SYS/CBQQZ123 AS SYSDBA
SHUTDOWN IMMEDIATE
create pfile from spfile;
STARTUP
alter session set "_ORACLE_SCRIPT"=true;
How to Install Oracle 20.1 in Centos 7 and Oracle Database 11g Xe
Firewall Configuration :
firewall-cmd --zone=public --add-port=80/tcp
firewall-cmd --zone=public --add-port=8080/tcp
firewall-cmd --zone=public --add-port=1539/tcp
. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
sqlplus /nolog
TOMCAT :
https://www.tecmint.com/install-apache-tomcat-in-centos/
useradd nt
passwd nt
password : pSSWRD
yum install java-1.8.0-openjdk-devel #install JDK 8
java -version
cd /usr/local
yum install wget
wget https://archive.apache.org/dist/tomcat/tomcat-9/v9.0.39/bin/apache-tomcat-9.0.39.tar.gz
tar -xvf apache-tomcat-9.0.39.tar.gz
mv apache-tomcat-9.0.39 tomcat
echo "export CATALINA_HOME="/usr/local/tomcat"" >> ~/.bashrc
source ~/.bashrc
Now we all set to start the tomcat web server using the scripts provided by the tomcat package.
cd /usr/local/tomcat/bin
./startup.sh
sudo firewall-cmd --zone=public --add-service=http
http://192.168.1.100:8080
OR
http://example.com:8080
vi /usr/local/tomcat/conf/tomcat-users.xml
cd /usr/local/tomcat/bin
./shutdown.sh
./startup.sh
If you want to run Tomcat on different port say 80 port. You will have to edit the ‘server.xml‘ file in ‘/usr/local/tomcat/conf/‘. Before changing, port, make sure to stop the Tomcat server using.
# /usr/local/tomcat/bin/shutdown.sh
Now open the server.xml file using the Vi editor.
# vi /usr/local/tomcat/conf/server.xml
Now search for “Connector port” and change its value from 8080 to 1510 or any other port you want as it follows.
Restart Tomcat9 :
cd /usr/local/tomcat/bin
./shutdown.sh
./startup.sh
Secure Tomcat :
https://geekflare.com/apache-tomcat-hardening-and-security-guide/
https://geekflare.com/apache-tomcat-hardening-and-security-guide/
https://gridscale.io/en/community/tutorials/securing-apache-tomcat-with-ssl/
https://www.mulesoft.com/tcat/tomcat-security
https://www.upguard.com/blog/15-ways-to-secure-apache-tomcat-8
Let’s hide the product and version details from the Server header.
vi /usr/local/tomcat/conf/server.xml
Go to $tomcat/conf folder
Modify server.xml by using vi
Add following to Connector port
Server =” “
<Connector port="8080" protocol="HTTP/1.1"
connectionTimeout="20000"
Server =" "
redirectPort="8443" />
vi /etc/httpd/conf/httpd.conf
Server Time Zone :
sudo timedatectl set-timezone Asia/Dhaka
Swap Space :
swapon -s
free -m
df -h
sudo fallocate -l 2G /swapfile
ls -lh /swapfile
sudo mkswap /swapfile
sudo dd if=/dev/zero of=/swapfile count=2048 bs=1MiB
sudo chmod 600 /swapfile
sudo mkswap /swapfile
sudo swapon /swapfile
vi /etc/fstab
/swapfile swap swap sw 0 0
run this command:
sudo sysctl vm.swappiness=10
vi /etc/sysctl.conf
Add this line :
vm.swappiness = 10
vm.vfs_cache_pressure = 50
To verify swap's size
swapon --summary
free -h
Upgrade the system :
yum upgrade -y
yum install java-1.8.0-openjdk.x86_64 java-1.8.0-openjdk-devel.x86_64 mc net-tools.x86_64 htop iotop iftop unzip wget epel-release -y
yum install rlwrap -y
Minimal CentOS installation:
There's an utility called chrony for this purpose in the minimal CentOS installation:
systemctl start chronyd
systemctl enable chronyd
SElinux disable :
vi /etc/sysconfig/selinux
And change the value SELINUX=enforcing to SELINUX=disabled, then save the config file. After doing this, execute this to disable selinux in the runtime:
setenforce 0
Install Oracle DB :
cd /root
yum install oracle-rdbms-server-11gR2-preinstall-1.0-3.el7.x86_64.rpm --skip-broken
# curl -o oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
# yum -y localinstall oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
uninstall oracle database xe 11g :
---- yum remove oracle-database-xe-18c
---install oracle database xe 18c :
cd Disk1/
yum localinstall oracle-xe-11.2.0-1.0.x86_64.rpm
Now, when the packages are installed and the user is set up, you need to run the initial database configuration script:
Listener :
Configurations :
/etc/init.d/oracle-xe configure
---------------- https://davidghedini.com/pg/entry/install_oracle_11g_xe_on_centos/
. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
sqlplus /nolog
CONN SYS/SYS@XE AS SYSDBA
--To allow remote access to Oracle 11g XE GUI (as well as Application Express GUI) issue the following from SQL*Plus
EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
-- ADD PORT 8080
firewall-cmd --zone=public --add-port=8080/tcp
You should now add an additional member for each group under /u01/app/oracle/oradata/XE
SQL ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/XE/log1b.LOG' TO GROUP 1;
SQL ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/XE/log2b.LOG' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/XE/log1b.LOG' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/XE/log2b.LOG' TO GROUP 2;
alter system set sessions=250 scope=spfile;
shutdown immediate
startup
alter system set processes=200 scope=spfile;
startup
---------------- https://davidghedini.com/pg/entry/install_oracle_11g_xe_on_centos/
Note that since 12c Oracle Database has multitenant architecture, which means there could be several pluggable databases and one multitenant container database. By default, the XEPDB1 pluggable database is created during the installation of XE.
To make it easier to connect to the pluggable database, I recommend editing of tnsnames.ora file and add there a new connection descriptor that we are going to use:
---vi /u01/app/oracle/product/11.2.0/xe/network/admin/sqlnet.ora
--- SQLNET.ALLOWED_LOGON_VERSION=8
vi /u01/app/oracle/product/11.2.0/xe/network/admin/tnsnames.ora
Add this record there below the standard XE record:
srvr2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1539))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
--- give access to firewall of port 1539
firewall-cmd --zone=public --add-port=1539/tcp
copy tnsnames to your windows oracle /network/admin folder.
Check connection from anywhere. e.g. Your Laptop or PC.
CONNECT WITHOUT GIVING ANY DATABASE NAME:
sqlplus /nolog
conn SYS/PSSWRD123 AS SYSDBA
SHUTDOWN IMMEDIATE
create pfile from spfile;
STARTUP
Install Apex 20.1 :
>> So let's get it started. Change your directory back to /root, unzip the APEX archive and make the user oracle the owner of the directory.
Considering we are installing the 18.2 version of APEX, it would look like this:
cd /root
cd /opt
mkdir oracle
cd /opt/oracle
--rm -rf oracle
mkdir -p /opt/oracle/apex
cd /root
unzip apex_20.1.zip -d /opt/oracle
chown -R oracle:oinstall /opt/oracle/apex
>> Note that we will be installing the full development environment of APEX.
This could be converted to a runtime only environment if needed. Refer to the official documentation for this. The full development environment should be used only when the Application Builder is needed. On a production environment it is strongly recommended to choose the runtime only setup.
>> From the APEX new home directory connect to our pluggable database as sysdba and run the installation scripts
(we will be using SYSAUX schema for APEX metadata):
cd /opt/oracle/apex
sqlplus /nolog
conn sys/SYS@XE AS SYSDBA
--- @apxremov.sql
-- connect to the database
sqlplus /nolog
conn sys/SYS@XE AS SYSDBA
CREATE TABLESPACE APEX DATAFILE '/u01/app/oracle/oradata/apex01.dbf' SIZE 5000M AUTOEXTEND ON NEXT 1M;
CREATE TABLESPACE apexindex DATAFILE '/u01/app/oracle/oradata/apexindex01.dbf' SIZE 5000M AUTOEXTEND ON NEXT 1M;
---CREATE TABLESPACE bhorbazar DATAFILE '/opt/oracle/oradata/XE/bhorbazar01.dbf' SIZE 5000M AUTOEXTEND ON NEXT 1M;
ALTER USER ANONYMOUS ACCOUNT UNLOCK;
--- ALTER SESSION SET CONTAINER=XE;
-- run the script to install a full development environment:
SQL>
sqlplus /nolog
conn sys/SYS@XE AS SYSDBA
@apexins.sql APEX APEX TEMP /i/
----@apxldimg.sql E:\apex_20.1
----@apxldimg.sql /opt/oracle
---- create an instance administrator user and set their password
conn sys/SYS@XE AS SYSDBA
@apxchpwd.sql
@apex_rest_config.sql
@apex_epg_config.sql /opt/oracle
SELECT DBMS_XDB.gethttpport FROM DUAL;
exec dbms_xdb.sethttpport(8080);
--- exec dbms_xdb.setftpport(0);
>> unlock and set up APEX public user, this is needed for ORDS to be able to connect to APEX engine.
alter user apex_public_user account unlock;
alter user apex_public_user identified by PSSWRD123;
conn sys/SYS@XE AS SYSDBA
alter USER ANONYMOUS ACCOUNT UNLOCK;
alter user ANONYMOUS identified by PSSWRD123;
alter USER XDB ACCOUNT UNLOCK;
alter user XDB identified by PSSWRD123;
--- Account Status must be Open :
select account_status from dba_users where username='ANONYMOUS';
select account_status from dba_users where username='XDB';
conn sys/SYS@XE AS SYSDBA
-- now disconnect from the database
192.168.1.100:8080/apex
192.168.1.100:80/apex
Installation of ORDS :
cd /root
cd /opt/oracle
rm -rf ords
mkdir -p /opt/oracle/ords
cd /root
yum install unzip
unzip ords-19.2.0.199.1647.zip -d /opt/oracle/ords
cd /opt/oracle/ords
mkdir config
mv ords.war apps.war
How to UnInstall ORDS :
----- systemctl stop tomcat
----- systemctl stop httpd
----- java -jar apps.war uninstall
java -jar apps.war install advanced
------- Configuration folder path :
/opt/oracle/ords/config
When prompted for ORDS configuration directory (the first question), enter config. Then provide the connection info to your pluggable database (
******* specify XEPDB1 for the service name
Enter the name of the database server [localhost]:example
Enter the database listen port [1521]:1539
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:1
Enter the database service name:ORCLPDB1
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:1
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Requires SYS AS SYSDBA to verify Oracle REST Data Services schema.
Enter the database password for SYS AS SYSDBA:
Confirm password:
Retrieving information.
Enter the default tablespace for ORDS_METADATA [SYSAUX]:SYSAUX
Enter the temporary tablespace for ORDS_METADATA [TEMP]:TEMP
Enter the default tablespace for ORDS_PUBLIC_USER [USERS]:USERS
Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]: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]: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]:1
Enter the database password for APEX_LISTENER:
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:
Confirm password:
Oct 30, 2019 1:05:38 PM
INFO: reloaded pools: []
Installing Oracle REST Data Services version 18.3.0.r2701456
... Log file written to /root/ords_install_core_2019-10-30_130538_00357.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Created Oracle REST Data Services proxy user
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to /root/ords_install_datamodel_2019-10-30_130554_00366.log
... Log file written to /root/ords_install_apex_2019-10-30_130556_00005.log
Completed installation for Oracle REST Data Services version 18.3.0.r2701456. Elapsed time: 00:00:19.713
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:2
After the configuration is completed,
****** the values are saved in
vi /opt/oracle/ords/config/ords/defaults.xml
file and may be modified there.
You can find more information about possible ORDS configuration options in the official documentation. The tomcat user (created as part of Tomcat install) must have read-write access to the ORDS configuration folder:
chown -R nt:nt /opt/oracle/ords/config
Copy the ords.war into the Tomcat webapps directory for this (and we will restart the Tomcat service later):
------------------cp -a /opt/oracle/ords/ords.war /usr/share/tomcat/webapps/apps.war
---- cp -a /opt/oracle/ords/apps.war /usr/share/tomcat/webapps/
cp -a /opt/oracle/ords/apps.war /usr/local/tomcat/webapps/
Then we need to copy APEX static files (images, stylesheets, JS files and so on) to the web server directory:
cd /usr/local/tomcat/webapps
mkdir i
cp -a /opt/oracle/apex/images/. /usr/local/tomcat/webapps/i/
cd /usr/local/tomcat/conf
vi server.xml
--- Now search for :
<Host name="localhost" appBase="webapps"
unpackWARs="true" autoDeploy="true">
Add this line here :
<Context docBase="/opt/oracle/apex/images/" path="/i/" />
--------------------------------------------------------------------------------------------------------
<Connector port="1510" protocol="HTTP/1.1"
connectionTimeout="20000"
redirectPort="8443" />
<!-- A "Connector" using the shared thread pool-->
<!--
<Connector executor="tomcatThreadPool"
port="1510" protocol="HTTP/1.1"
connectionTimeout="20000"
redirectPort="8443" />
--------------------------------------------------------------------------------------------------------
cd /usr/local/tomcat/bin
./shutdown.sh
./startup.sh
sudo systemctl restart tomcat
Done! We succeeded in installing of ORDS and deploying it to Tomcat by now. Only one step is left.
ALTER USER APEX_LISTENER IDENTIFIED BY PSSWRD123 ACCOUNT UNLOCK;
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY PSSWRD123 ACCOUNT UNLOCK;
ALTER USER APEX_REST_PUBLIC_USER IDENTIFIED BY PSSWRD123 ACCOUNT UNLOCK;
HTTPD :
sudo yum install httpd
cd /etc/httpd/conf.d/
touch 10-apex.conf
vi /etc/httpd/conf.d/10-apex.conf
<VirtualHost *:80>
ServerName example.com
ServerAlias www.example.com
Redirect Permanent / https://example.com
ProxyRequests On
<Proxy *>
Order deny,allow
Allow from all
</Proxy>
<Location />
ProxyPreserveHost on
RequestHeader unset Origin
ProxyPass http://192.168.1.100:1510/
ProxyPassReverse http://192.168.1.100:1510/
</Location>
RewriteEngine on
RewriteCond %{SERVER_NAME} =example.com [OR]
RewriteCond %{SERVER_NAME} =www.example.com [OR]
RewriteRule ^ https://%{SERVER_NAME}%{REQUEST_URI} [END,NE,R=permanent]</VirtualHost>
systemctl enable httpd
systemctl restart httpd
<VirtualHost *:80>
ServerName bhorbazar.com
ServerAlias www.bhorbazar.com
ProxyRequests On
<Proxy *>
Order deny,allow
Allow from all
</Proxy>
<Location />
ProxyPreserveHost on
RequestHeader unset Origin
ProxyPass http://192.168.1.100:1510/
ProxyPassReverse http://192.168.1.100:1510/
</Location>
RewriteEngine on
RewriteCond %{SERVER_NAME} =example.com [OR]
RewriteCond %{SERVER_NAME} =www.example.com [OR]
RewriteRule ^ https://%{SERVER_NAME}%{REQUEST_URI} [END,NE,R=permanent]</VirtualHost>
Tomcat Redirect :
cd /usr/local/tomcat/webapps/ROOT
cp index.jsp original-index.jsp
vi index.jsp
<% response.sendRedirect(""); %>
mkdir public
mkdir admin
cd public
vi index.jsp
<% response.sendRedirect(""); %>
cd ..
cd admin
vi index.jsp
<% response.sendRedirect(""); %>
SSL in httpd:
sudo yum install epel-release
sudo yum install certbot python2-certbot-apache mod_ssl
sudo certbot --apache -d example.com
sudo certbot --apache -d example.com -d www.example.com
sudo certbot --apache
sudo certbot renew --dry-run
sudo crontab -e
ssl tomcat :
--- https://medium.com/@mashrur123/a-step-by-step-guide-to-securing-a-tomcat-server-with-letsencrypt-ssl-certificate-65cd26290b70
------------------------
-------- Prerequisites :
------------------------
>>> Before starting work on this task, I assume you already have:
01 - Running Centos system with sudo privileges shell access.
02 - A domain name registered and pointed to your server’s public IP address.
03 - For this tutorial, we use bhorbazar.com and www.bhorbazar.com, which is pointed to our server.
04 - Recent version of JAVA installed.
05 - Recent version of tomcat server installed in your .
06 - Have port 80 and 8443 open in your firewall.
07 - Have Openssl installed.
sudo yum install epel-release
sudo yum install certbot
---- If you have an active firewall, e.g firewalld, open https port on the firewall.
---- # firewall-cmd --add-service https --permanent
---- # firewall-cmd --reload
---- If you are not using letsencrypt then you can skip this step.
sudo certbot certonly --standalone -d www.example.com
If everything goes fine. A new ssl will be issued at below location. Navigate to below directory and view files.
cd /etc/letsencrypt/live/example.com
ls -l
Files List: -- >
cert.pem
chain.pem
fullchain.pem
privkey.pem
>>> Convert keypair + certificate to Java Keystore :
At first create a PKCS12 that contains both your full chain and the private key. You need to have openssl installed for that.
openssl pkcs12 -export -out /tmp/bhorbazar.com_fullchain_and_key.p12 \
-in /etc/letsencrypt/live/www.bhorbazar.com/fullchain.pem \
-inkey /etc/letsencrypt/live/www.bhorbazar.com/privkey.pem \
-name tomcat
Then convert that PKCS12 to a JKS, using java's keytool
keytool -importkeystore \
-deststorepass pSSWRD -destkeypass pSSWRD -destkeystore /tmp/www.bhorbazar.com.jks \
-srckeystore /tmp/bhorbazar.com_fullchain_and_key.p12 -srcstoretype PKCS12 -srcstorepass pSSWRD \
-alias tomcat
Replace pSSWRD with your password. : pSSWRD
----- Configure Tomcat with the Java Keystore :
Now go to your tomcat application and open your server.xml file
# vi /etc/tomcat/conf/server.xml
Ensure the following section is commented out
<!---
<Connector port="8080" protocol="HTTP/1.1"
connectionTimeout="20000"
redirectPort="8443" />
-->
Configure connector to use a shared thread pool
<Connector executor="tomcatThreadPool"
port="8080" protocol="HTTP/1.1"
connectionTimeout="20000"
redirectPort="8443" />
Next is to define SSL HTTP/1.1 Connector on port 8443
<Connector port="8443" protocol="org.apache.coyote.http11.Http11Protocol"
maxThreads="150" SSLEnabled="true" scheme="https" secure="true"
keystoreFile="/tmp/example.com.jks"
keystorePass="pSSWRD"
clientAuth="false" sslProtocol="TLS" />
With above configuration, http to https redirect will be done automatically for the application.
Now just Stop and Start Apache Tomcat and you are done.
cd /usr/local/tomcat/bin
./shutdown.sh
./startup.sh
How to enable iptables :
https://upcloud.com/community/tutorials/configure-iptables-centos/
drop the lines with REJECT commands.
DDos Attack :
netstat -ntu | grep ESTAB | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -nr
You can also bock an IP address on the server using iptables by entering the following commands:
https://www.hostingswift.com/how-to-block-or-unblock-an-ip-address-on-a-linux-server
iptables -A INPUT -s 88.99.252.172 -j DROP
iptables -A INPUT -s 67.207.67.2 -j DROP
systemctl restart httpd
HOW TO UNBLOCK AN IP ADDRESS ON A LINUX SERVER :
Use the following syntax (the -d options deletes the rule from table):
# iptables -D INPUT -s 209.175.453.23 -j DROP
# service iptables save
vi /etc/sysconfig/iptables
SSL in Database (Wallet):
https://apex.oracle.com/pls/apex/germancommunities/apexcommunity/tipp/6121/index-en.html
