APEX Installation and Deployement

-
Apex Installation & Deployement  (Oracle 18c , Apex 20.1 , ORDS 19.0 , Tomacat 9.0)

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

https://www.aflorestorres.com/2019/03/install-oracle-database-18c-express-and.html

https://matthiashoys.wordpress.com/2019/12/12/installation-of-apex-19-2-on-oracle-18c-xe-express-edition/

Web Logic Configuration 

http://info.aimstar.com/blog/deploying-ords-on-weblogic-for-apex

1)Download the APEX software.

https://www.oracle.com/tools/downloads/apex-downloads.html

2)Create a new tablespace to act as the default tablespace for APEX. (Optional)

-- For Oracle Managed Files (OMF).
CREATE TABLESPACE apex DATAFILE SIZE 100M AUTOEXTEND ON NEXT 1M;

-- For non-OMF.
CREATE TABLESPACE apex DATAFILE '/path/to/datafiles/apex01.dbf' SIZE 100M AUTOEXTEND ON NEXT 1M;

3)Installation

Change directory to the directory holding the unzipped APEX software.

Unzip downlaoded file and past in Oracle Home Directory like T:\app\oracle\apex

3.1) Go to Command Prompt and go to T:\App\Oracle\Apex > Sqlplus

4)Connect to SQL*Plus as the SYS user and run the "apexins.sql" script, specifying the relevant tablespace names and image URL.

SQL> CONN sys/Manager1@CISPDB AS SYSDBA 

or

SQL> conn / as sysdba

SQL> ALTER SESSION SET CONTAINER=CISPDB;


SQL> @apexins.sql APEX APEX TEMP /i/   (Perform this If you have followed Stpe 2)
or 
SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/

this will take 10 minit time 

Last Line Out put will be 

timing for: Complete Installation
Elapsed: 00:10:33.61

PL/SQL procedure successfully completed.

4.1) APEX should have created a number of database accounts during the installation. To verify that the accounts have been installed in the correct PDB, run the following command:

SQL> alter session set container=CISPDB;

SQL> select username from dba_users order by username asc;

USERNAME
--------------------------------------------------------------------------------
ANONYMOUS
APEX_200100
APEX_INSTANCE_ADMIN_USER
APEX_PUBLIC_USER


5) Once complete, change the admin password by running the "apxchpwd.sql" scripts as the SYS user.

SQL> CONN sys@pdb1 AS SYSDBA
SQL> @apxchpwd.SQL

==============
Enter the Administrator's username [ADMIN]      : Enter
User "Admin" doesnot yet exit will be created.  
Enter ADMIN's email [ADMIN] : Enter
Enter ADMIN Password [] : Way2cis@1234#
Created  Instance administration Administrator

Here we kept the default name of “ADMIN” for the APEX Instance Administration account. Remember the password or keep it somewhere safe, you will need it later to log in for the first time.

As next step, we need to unlock and change the password of the APEX_PUBLIC_USER database account. This is one of the crucial accounts that is used to connect the web listener to the database.


SQL> conn / as sysdba
Connected.
SQL> alter session set container=CISPDB;

Session altered.

SQL> alter user APEX_PUBLIC_USER account unlock;

User altered.

SQL> alter user APEX_PUBLIC_USER identified by Manager1;


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


SQL> CONN sys/Manager1@CISPDB AS SYSDBA

SQL> @apex_rest_config.sql

PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.

Enter a password for the APEX_LISTENER user [] : Manager1
Enter a password for the APEX_REST_PUBLIC_USER user [] : Manager1

...create APEX_LISTENER and APEX_REST_PUBLIC_USER users



You will see some output and hopefully no errors. Two new database accounts have now been created: APEX_LISTENER and APEX_REST_PUBLIC_USER. Also store the passwords for these accounts somewhere safe.


6.1) Let’s summarise what we have done so far:

install APEX 20.1 by executing the apexins.sql script in the pluggable database CISPDB

create the APEX Instance Administration user by executing the script apxchpwd.sql
unlock the account APEX_PUBLIC_USER and set the password
configure RESTful services by executing the script apex_rest_config.sql

7) We now need to select and install a Web Listener. The Web Listener functions as “communications broker” between   the database and the client’s web browser.


7.1) the embedded PL/SQL gateway that is included with the database :-The embedded PL/SQL gateway has its limits 

7.2) Oracle HTTP Server with mod_plsql :- Oracle HTTP Server is close to being deprecated, (Like WebLogic OHS Server)

7.3) Oracle REST Data Services (ORDS) :- Advisable 

7.1 ) Embedded PL/SQL Gateway (EPG) Configuration
SQL > CONN sys/Manager1@CISPDB AS SYSDBA

SQL> @apex_epg_config.sql T:\App\Oracle      (Here this is path where apex folder is reside)

..Loading images Directory: t:\app\oracle/apex/images

timing for: Complete Installation
Elapsed: 00:10:33.61

7.1.1) Unlock the ANONYMOUS account.

SQL> Conn Sys/Manager1 as sysdba

SQL> show PDBS (here u will get PDB$SEED and CISPDB Both)

SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;


7.1.2) Check the port setting for XML DB Protocol Server.

SQL > CONN sys/Manager1@CISPDB AS SYSDBA

SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;

If is 0 we need to change with 8080, or another.

EXEC DBMS_XDB.SETHTTPPORT(8088);

Check the port setting for XML DB Protocol Server.

Finally we can enter to Oracle Apex 20.1

http://localhost:8088/apex
http://localhost:8088/apex/apex_admin





7.2) Oracle HTTP Server (OHS) Configuration (Web Logic Server )

If you want to use Oracle HTTP Server (OHS) to front APEX, you can follow the instructions here.

Change the password and unlock the APEX_PUBLIC_USER account. This will be used for any Database Access Descriptors (DADs).


SQL> ALTER USER APEX_PUBLIC_USER IDENTIFIED BY myPassword ACCOUNT UNLOCK;

If you don't want the password to expire you will need to create a new profile with password expiration disabled and assign it to the user.

7.2.1) Create a DAD in the OHS:


Log into EM (http://server:port/em)
From the tree select "Farm_DomainName > Web Tier > ohs1"
On the resulting page select "Oracle HTTP Server > Administration > Advanced Configuration"
From the dropdown list slect "dads.conf" and click the "Go" button.
Edit the "dads.conf" file, adding an entry like that shown below, then click the "Apply" button.

<Location /apex>
   Order deny,allow
   PlsqlDocumentPath docs
   AllowOverride None
   PlsqlDocumentProcedure         wwv_flow_file_mgr.process_download
   PlsqlDatabaseConnectString     pdb1 TNSFormat
   PlsqlNLSLanguage               AMERICAN_AMERICA.AL32UTF8
   PlsqlAuthenticationMode        Basic
   SetHandler                     pls_handler
   PlsqlDocumentTablename         wwv_flow_file_objects$
   PlsqlDatabaseUsername          APEX_PUBLIC_USER
   PlsqlDefaultPage               apex
   PlsqlDatabasePassword          myPassword
   PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize
   PlsqlPathAlias                 r
   PlsqlPathAliasProcedure        wwv_flow.resolve_friendly_url
   Allow from all
</Location>


Select the "Oracle HTTP Server > Control > Restart" option.
Click the resulting "Restart" button.
Once restarted, click the "Close" button.


Alternatively, edit the "dads.conf" file directly. For the OHS that comes with Forms and Reports Services, this is located here "$FR_INST/config/OHS/ohs1/mod_plsql/dads.conf". Once amended, remember to restart the HTTP server.


7.2.2) $ $FR_INST/bin/opmnctl restartproc process-type=OHS

7.2.3) Copy the APEX images to your Oracle HTTP Server.

$ cp -R /tmp/apex/images $FR_INST/config/OHS/ohs1/htdocs/apex_images

    7.2.4) Make them available from the "/i/" alias by adding the following alias to the virtual host defined in the "ssl.conf" or "httpd.conf" file in the "$FR_INST/config/OHS/ohs1" directory.

Alias /i/ "/u01/app/oracle/middleware/FR_inst/config/OHS/ohs1/htdocs/apex_images/"

APEX should now be available from a URL like "http://machine:port/apex".


7.3) Oracle REST Data Services (ORDS) Configuration

Oracle REST Data Services (ORDS) : Installation on Tomcat

After Installation of Tomacat

https://blog.yannickjaquier.com/oracle/oracle-rest-data-services-ords.html
https://oracle-base.com/articles/misc/articles-misc#ords
https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-custom-authentication-schemes

1) Download the following software. Always use the latest version available.
Oracle REST Data Services (ORDS)
2) Unzip the ORDS distribution.

2.1) Past ORDS Folder in C:\app\admin\ciserp\ords_installation

2.2) Configuation Folder for ORDS  C:\app\admin\ciserp

2.3) Edit ords_installation/params/ords_params.properties

db.hostname=localhost
db.password=Manager1
db.port=1522
db.servicename=CISPDB
db.username=APEX_PUBLIC_USER
migrate.apex.rest=false
plsql.gateway.add=true
rest.services.apex.add=true
rest.services.ords.add=true
restEnabledSql.active=true
schema.tablespace.default=SYSAUX
schema.tablespace.temp=TEMP
standalone.http.port=8080
standalone.mode=false
user.public.password=Manager1
user.tablespace.default=USERS
user.tablespace.temp=TEMP
# Enable REST Enabled SQL.
restEnabledSql.active=true
# Enable SQL Developer Web. Available from 19.4 onward. Requires REST Enabled SQL.
feature.sdw=true
   2.4) Use the "ords.war" file to specify the configuration directory using the following command. The file name "ords.war" will result in a URL containing "/ords/". If you want a different URL, rename the WAR file accordingly. In this article I will use the original name.


   2.5) 

   C:\Program Files\Java\jdk1.8.0_241\bin>java -jar C:\app\admin\ciserp\ords_installation\ords.war configdir C:\app\admin\ciserp

   May 27, 2020 5:33:28 PM
   INFO: Set config.dir to C:\app\admin\ciserp\ords\confg in: C:\app\admin\ciserp\ords_installation\ords.war

   Note:- After above step, system will gnerate the ORDS\conf folder in C:\app\admin\ciserp
   with default below xml file C:\app\admin\ciserp\ords\conf

    apex , apex_al,apex_pu,apex_rt

   --> Give access of ords\conf folder to everyone (Go to Folder Security Setting and assign to every one access of this to folder)  

   2.6)

   C:\Program Files\Java\jdk1.8.0_241\bin>java -jar C:\app\admin\ciserp\ords_installation\ords.war

    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 en
ter 1 [1]:1
May 27, 2020 5:36:14 PM
INFO: reloaded pools: []
Installing Oracle REST Data Services version 19.2.0.r1991647
... Log file written to C:\Users\Administrator\ords_install_core_2020-05-27_1736
15_00042.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 C:\Users\Administrator\ords_install_datamodel_2020-05-27
_173634_00816.log
... Log file written to C:\Users\Administrator\ords_install_apex_2020-05-27_1736
36_00800.log
Completed installation for Oracle REST Data Services version 19.2.0.r1991647. El
apsed time: 00:00:23.992

2.7) If with above ords not calling from tomcat then 

Before Unlock All user

SELECT username, account_status, created, lock_date, expiry_date FROM dba_users WHERE account_status != 'OPEN';
*= all apex and ORds user

ALTER USER * IDENTIFIED BY Manager1 ACCOUNT UNLOCK;
ALTER USER APEX_190200 IDENTIFIED BY Manager1 ACCOUNT UNLOCK;
ALTER USER APEX_200100 IDENTIFIED BY Manager1 ACCOUNT UNLOCK;


2.7.1) Re-Install the ORDS 

C:\Program Files\Java\jdk1.8.0_241\bin>java -jar C:\app\admin\ciserp\ords_installation\ords.war setup

2.7.2) Restart the Tomact Server form service.msc


2.8) Validate ORDS

C:\Program Files\Java\jdk1.8.0_241\bin>java -jar C:\app\admin\ciserp\ords_installation\ords.war validate

Enter the name of the database server [localhost]:
Enter the database listen port [1522]:
Enter the database service name [CISPDB]:
Requires to login with administrator privileges to verify Oracle REST Data Servi
ces schema.

Enter the administrator username:sys as sysdba
Enter the database password for sys as sysdba:
Confirm password:

Retrieving information.

Oracle REST Data Services will be validated.
Validating Oracle REST Data Services schema version 19.2.0.r1991647
... Log file written to C:\Users\Administrator\ords_validate_core_2020-05-27_175
149_00921.log
Completed validating Oracle REST Data Services version 19.2.0.r1991647.  Elapsed
time: 00:00:05.343

 
2.9) ORA-20000: APEX_INSTANCE_ADMIN_USER Cannot Be Rest Enabled from Rest Administration Interface

if you are getting above error while enabled the Rest --Login with admin --> Manage Instance->REST ADMINISTRATION Interface --> Perform Eanbled Button 

alter session set "_ORACLE_SCRIPT"=true;

drop user APEX_INSTANCE_ADMIN_USER;

alter session set "_ORACLE_SCRIPT"=false;

--
-- recreate APEX_INSTANCE_ADMIN_USER as a non Oracle Maintained user
--

create user apex_instance_admin_user no authentication;

grant create session to APEX_INSTANCE_ADMIN_USER;


2.10) Manual ORDS Uninstall

C:\Program Files\Java\jdk1.8.0_241\bin>java -jar C:\app\admin\ciserp\ords_installation\ords.war uninstall

7.3.1) Tomcat Deployment

Copy the APEX images to the Tomcat "webapps" directory.

$ mkdir $CATALINA_HOME/webapps/i/
$ cp -R /tmp/apex/images/* $CATALINA_HOME/webapps/i/

Copy the "ords.war" file to the Tomcat "webapps" directory.

$ cd /u01/ords
$ cp ords.war $CATALINA_HOME/webapps/


ORDS should now be accessible using the following type of URL.

http://<server-name>:<port>/ords/


restart Tomcat Server

$ $CATALINA_HOME/bin/startup.sh
$ $CATALINA_HOME/bin/shutdown.sh


7.3.2) How tomcat deployee the Apex application ...?

--> At time of configure the ORDS , initally we are assignment the configuration directory in above case we have given C:\app\admin\ciserp\ords\conf 

-> Tomcat will take this conf file to start the ORDS services. 

--> Hence Make sure ords\conf  path should have read /write access for every one 

7.3.3) If services not start then go to Log file of Tomcat 


C:\Program Files\Apache Software Foundation\Tomcat 9.0\logs

catalina<date>.log 



Comments

  1. Raj Tilak13 August 2020 at 10:36

    Dear Jignesh,
    A famous quote says..
    "Gaining Knowledge, is the First Step to Wisdom.
    Sharing it, is the first step to Huminity"

    Thanks sharing your time and knowledge...Had memorable and good experience.

    Regards
    Raj Tilak

    ReplyDelete

Post a Comment

Popular posts from this blog

Telegram Integration with Oracle

-
 How to Integrate Telegram with Oracle Apex 1) create Telegram Account in your Mobile like whatsapp 2) Ones you register in telegram apps serach for BotFather 3) To Create New Bot u need to enter below command  /newbot  Alright, a new bot. How are we going to call it? Please choose a name for your bot. Macsbot Good. Now let's choose a username for your bot. It must end in `bot`. Like this, for example: TetrisBot or tetris_bot. MacsOmanBot Done! Congratulations on your new bot. You will find it at t.me/MacsOmanBot. You can now add a description, about section and profile picture for your bot, see /help for a list of commands. By the way, when you've finished creating your cool bot, ping our Bot Support if you want a better username for it. Just make sure the bot is fully operational before you do this. Use this token to access the HTTP API:<Here you will prompt with your api> 4) /mybots (this command will return the list of bots which you have created) ...
Read more

The username or password for the connection pool named |apex||, are invalid, expired, or the account is locked

-
  1. Execute the following command from SQL Plus as the SYS user:  Conn sys/Pass@xe as sysdba  (connect as container if 12c then  ALTER SESSION SET container = CDB$ROOT;) Alter user ords_public_user identified by xxxx; ALTER USER ORDS_PUBLIC_USER ACCOUNT UNLOCK; Connect as PDB Conn sys/Pass@xepdb1 as sysdba (if 12c  ALTER SESSION SET container = PDB1;) Alter user APEX_PUBLIC_USER identified by xxxx; Alter user APEX_REST_PUBLIC_USER identified by xxxx; Alter user APEX_LISTENER identified by xxxx; 2. Unlock the users: ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK; ALTER USER APEX_REST_PUBLIC_USER ACCOUNT UNLOCK; ALTER USER APEX_LISTENER ACCOUNT UNLOCK; ALTER USER ORDS_PUBLIC_USER ACCOUNT UNLOCK; 3. Now create a profile for the APEX related users: Create profile apex_public_profile limit password_life_time unlimited; 4. Assign the profile to the APEX users: alter user apex_public_user profile apex_public_profile; alter user apex_rest_public_user profile apex_public_pr...
Read more

Enabled SQL Developer Web (SDW) & ORDS Database API ORDS 19.4

-
SQL Developer Web (SDW)  ORDS 19.2 to 19.4 Migration /  Basic Setup to Enable ORDS Database API Bsic Information  Installing Oracle REST Data Services https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.4/aelig/installing-REST-data-services.html#GUID-673D35DD-9811-42F8-A500-4F494F79C470 https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.4/aelig/installing-REST-data-services.html#GUID-2C42D982-BF08-48E3-A4C6-3FE1A78287E3 Using Oracle® SQL Developer Web https://docs.oracle.com/en/database/oracle/sql-developer-web/19.2.1/sdweb/about-sdw.html#GUID-AF7601F9-7713-4ECC-8EC9-FB0296002C69 How to Configure / Run ORDS for SQL Developer Web and APEX in Standalone Mode: ORDS 19.4 + (Doc ID 2628004.1) GOAL This document is an overview on how to configure and run ORDS standalone for SQL Developer Web using the command line interface. It also includes the steps related to setting up APEX, since the ability to run APEX as well as SQL Develo...
Read more