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 Developer Web is a common requirement. If you do not have APEX but want to set up SQL Developer Web, please see Note 2636617.1 - How to Configure / Run ORDS for SQL Developer Web in Standalone Mode: ORDS 19.4 +.

The document makes the following assumptions:
* ORDS is being configured and tested on the database server.
* ORDS schemas (ORDS_METADATA and ORDS_PUBLIC_USER) do not already exist on the database.
* The user being configured for SQL Developer Web (SDW) is DEMO.
* APEX has already been installed on the target database, and the location of the installation media is known.
If ORDS is installed on a different server / host machine the APEX static files from the installation media must be copied to the server.

At the end of this document, you should be able to bring up the SDW login page and log in as a database user.



1) Verify the version of java on the server is 1.8:

java -version

The installation should also work with JDK 11, 12, 13.

Java can be downloaded from:

https://www.oracle.com/downloads/#category-java

Once installed, add the java location to the OS PATH:

UNIX
export PATH=<JAVA_DIR>/bin:$PATH

Windows
set PATH=<JAVA_DIR>/bin:$PATH


2) Find the location of the ords.war on the server. If ORDS has not been installed, download ORDS 19.4 from the following location:
https://www.oracle.com/database/technologies/appdev/rest.html

2.1) Unzip the installation media, and make note of the location for the ords.war file.  In my case

T:\CIS\ords-19.4



Verify that the ords.war file is 19.4 or above and supports SQL Developer Web (SDW).
java -jar ords.war version
2.2) 

Also ensure that the .war file has not already been configured:

java -jar ords.war configdir

This should return:

<DATE>T<TIME> INFO The config.dir setting is not set

    if return with path then before start setup please change the path of ORDS installation  (Create conf folder in 
    source downloaded Path like T:\CIS\ords-19.4\Confg)

    C:\Program Files\Java\jdk1.8.0_241\bin>java -jar T:\CIS\ords-19.4\ords.war configdir T:\CIS\ords-19.4\Conf



3) Identify the version of APEX installed in the database:  
it should be 12c DB or Later 

select comp_name, version from dba_registry order by 1;


4). java -jar ords.war install advanced
or
java -jar ords.war   Will follow this normal one

NOTE: On windows machines, an out of memory error has been observed. In those situation the command should be:
java -Xmx1024m -Xms1024m -jar ords.war install advanced


Specify the database connection type to use.
Enter number for [1] Basic  [2] TNS  [3] Custom URL [1]: 1

Enter the name of the database server [localhost]:
Enter the database listen port [1521]:1522 (My Case)

Enter 1 to specify the database service name, or 2 to specify the database SID [1]:1
Enter the database service name:CISPDB

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 to login with administrator privileges to verify Oracle REST Data Services schema.


Enter the administrator username:SYS
Enter the database password for Sys as SYSDBA: 
Confirm password:

Connecting to database user: SYS url: jdbc:oracle:thin:@//localhost:1522/CISPDB

Retrieving information.

Enter the default tablespace for ORDS_METADATA [SYSAUX]:
Enter the temporary tablespace for ORDS_METADATA [TEMP]:
Enter the default tablespace for ORDS_PUBLIC_USER [USERS]:
Enter the temporary tablespace for ORDS_PUBLIC_USER [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]:
Enter the PL/SQL Gateway database user name [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]:

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 [2] REST Enabled SQL [3] None [1]:1

Installing Oracle REST Data Services version 19.4.6.r1421859
... Log file written to C:\Users\jignesh\ords_install_core_2020-07-06_035554_00285.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\jignesh\ords_install_datamodel_2020-07-06_035651_00649.log
... Log file written to C:\Users\jignesh\ords_install_apex_2020-07-06_035703_00490.log
Completed installation for Oracle REST Data Services version 19.4.6.r1421859. Elapsed time: 00:01:23.845


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

 4.1) Ones installation done then go to T:\CIS\ords-19.4\conf\ords folder and open the defualt.xml file

  check feature.sdw and restEnabledSql.activity both entry should be there 

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<comment>Saved on Mon Jul 06 03:55:53 EDT 2020</comment>
<entry key="db.connectionType">basic</entry>
<entry key="db.hostname">localhost</entry>
<entry key="db.port">1522</entry>
<entry key="db.servicename">CISPDB</entry>
<entry key="feature.sdw">true</entry>
<entry key="restEnabledSql.active">true</entry>
<entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>
<entry key="security.validationFunctionType">plsql</entry>
</properties>

check feature.sdw and restEnabledSql.activity if entry not there then follow below step 

$ java -jar ords.war set-property feature.sdw true
$ java -jar ords.war set-property restEnabledSql.active true


5) Enabling a Schema for SDW

This section describes the user set up for SQL Developer Web. For this example, the schema in use is DEMO. It is assumed that the user is able to login to the database and select, insert, update, delete against objects owned by the schema.

1. Enable the schema for REST access and define the schema alias to be used in the URL. This step can be performed through an SQL Client with access to the target database:
    SQLPlus, SQLcl, SQL Developer, TOAD, etc. If the schema is associated with an APEX workspace, the RESTful Services interface of APEX may also be used.

    For this example assumes SQLPlus is in use.

    Login to SQLPlus as the DEMO user.


2. Enable the schema for SDW, and define the schema alias to be used when referencing this schema from a browser. For simplicity, the schema and the schema alias are the same for this example.


BEGIN
ORDS_ADMIN.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => 'BLZLIVE',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'blzlive',
p_auto_rest_auth => FALSE
);
COMMIT;
END;
/

3. Go to Tomacat C:\Program Files\Apache Software Foundation\Tomcat 9.0\webapps 

copy the ords.war file which is lcoated in T:\CIS\ords-19.4\ords.war  and past in above tomcat path


4. Restart the Tomcat Server 


5. Verify that the schema is accessible through ORDS: 
https://localhost:8443/ords/blzlive/metadata-catalog/

For this example the URL will be:

https://localhost:8443/ords/blzlive/metadata-catalog/

This will return JSON data similar to:

{"items":[],"hasMore":false,"limit":25,"offset":0,"count":0,"links":[{"rel":"self","href":"http://localhost:8080/ords/demo/metadata-catalog/"},{"rel":"first","href":"http://localhost:8080/ords/demo/metadata-catalog/"}]}


6. Access SQL Developer. This can be done through one of the following URLs: 

https://localhost:8443/ords/sql-developer

OR
http://localhost:8080/ords/demo/_sdw/




6) Basic Setup to Enable ORDS Database API

To enable the ORDS database API, set the database.api.enabled property to true and then restart ORDS:

java -jar ords.war set-property database.api.enabled true

For example, to use PDBADMIN schema, in the SALESPDB database for ORDS Database API services, use the following commands in the database.

sqlplus system@SALESPDB
GRANT PDB_DBA TO PDBADMIN;
BEGIN
ORDS_ADMIN.ENABLE_SCHEMA(p_schema => 'PDBADMIN');
END;
/


The PDBADMIN user is now ready to use the ORDS database API services.

To list the tables in the database, send a GET request to https://<server>/ords/salespdb/pdbadmin/_/db-api/stable/database/objects/tables/


The same service can be accessed through command line utilities such as curl:

curl --user pdbadmin:password https://<server>/ords/salespdb/pdbadmin/_/db-api/stable/database/objects/tables/


Comments

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