Environment
OS: CentOS 7.0
Oracle Database: 11.2.0.3.0
JDK Version: jdk1.8.0_121
Apex Version: 5.1
ORDS Version: 3.0.9.348.07.16
Download JDK8
http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html
Download apex software from URL below:
http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html
Download ORDS software from the URL below:
http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html
In my case I have downloaded all the source to home directory of oracle user. So change directory to ~oracle and list all the files. Extract java source to /opt directory and set JAVA_HOME environment to newly extracted directory.
cd ~oracle
ls
tar xzvf jdk-8u121-linux-x64.tar.gz -C /opt/
Configure environment variables for JAVA_HOME and make it executable
cat > /etc/profile.d/java.sh
export JAVA_HOME=/opt/jdk1.8.0_121
chmod 755 /etc/profile.d/java.sh
Verify environment variables
su - oracle
echo $JAVA_HOME
Installing APEX 5.1
cd ~
unzip apex_5.1_en.zip
Change directory to apex and connect Oracle database as sysdba
cd apex
sqlplus /nolog
conn / as sysdba
Create tablespace to hold apex information
Create tablespace tbs_apex
datafile '/u01/app/oracle/oradata/mentordb/tbs_apex01.dbf'
size 10m
autoextend on
next 10m
maxsize unlimited;
Execute apex installation script with the following syntax
Syntax: @apexins.sql apex_tablespace_name apex_datafile_name temporary_tablespace_name image_location
@apexins.sql TBS_APEX TBS_APEX TEMP /i/
Change admin password with script below. Note Username and password for future use.
@apxchpwd.sql
Execute the script below to create the APEX_LISTENER and APEX_REST_PUBLIC_USER Users
@apex_rest_config.sql
Extract ords archive source.
mkdir ords
unzip ords.3.0.9.348.07.16.zip -d ords/
Create configuration directory and modify the configuration file
mkdir -p /home/oracle/ords/conf
Change ords parameter configuration
vi /home/oracle/ords/params/ords_params.properties
Make sure following are the parameter values
db.hostname=mentor.localdomain
db.port=1521
db.servicename=mentordb
#db.sid=
db.username=APEX_PUBLIC_USER
migrate.apex.rest=false
rest.services.apex.add=true
rest.services.ords.add=true
schema.tablespace.default=TBS_APEX
schema.tablespace.temp=TEMP
standalone.http.port=8080
standalone.static.images=/home/oracle/apex/images
user.tablespace.default=TBS_APEX
user.tablespace.temp=TEMP
Set configuration directory with command below
cd /home/oracle/ords
$JAVA_HOME/bin/java -jar ords.war configdir /home/oracle/ords/conf
Make sure the following users exists and Account status is not locked.
ALTER USER APEX_LISTENER IDENTIFIED BY oracle_4U ACCOUNT UNLOCK;
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY oracle_4U ACCOUNT UNLOCK;
ALTER USER APEX_REST_PUBLIC_USER IDENTIFIED BY oracle_4U ACCOUNT UNLOCK;
ALTER USER APEX_INSTANCE_ADMIN_USER IDENTIFIED BY oracle_4U ACCOUNT UNLOCK;
ALTER USER ORDS_PUBLIC_USER IDENTIFIED BY oracle_4U ACCOUNT UNLOCK;
Configure ords with the command below
cd /home/oracle/ords
$JAVA_HOME/bin/java -jar ords.war
$JAVA_HOME/bin/java -jar ords.war
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Please login with SYSDBA privileges to verify Oracle REST Data Services schema.
Enter the username with SYSDBA privileges to verify the installation [SYS]:
Enter the database password for SYS:
Confirm password:
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 database password for APEX_PUBLIC_USER:
Confirm password:
Enter the database password for APEX_LISTENER:
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:
Confirm password:
Mar 24, 2017 10:56:45 AM
INFO: Updated configurations: defaults, apex, apex_pu, apex_al, apex_rt
Installing Oracle REST Data Services version 3.0.9.348.07.16
... Log file written to /home/oracle/ords/logs/ords_install_core_2017-03-24_105645_00281.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 /home/oracle/ords/logs/ords_install_datamodel_2017-03-24_105703_00209.log
Completed installation for Oracle REST Data Services version 3.0.9.348.07.16. Elapsed time: 00:00:19.249
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:1
Enter 1 if using HTTP or 2 if using HTTPS [1]:1
2017-03-24 10:57:16.295:INFO::main: Logging initialized @76835ms
Mar 24, 2017 10:57:16 AM
In above output all the bold black characters are the Input provided while prompting. There are lot of prompts but we use pressed enter if we are fit with the DEFAULT value mentioned between large brackets. Example in line Enter the username with SYSDBA privileges to verify the installation [SYS]: we are ok with the SYS username, no required to modify so just pressed enter to continue.
The prompt will stay busy. The program will start of foreground. If we need to start the ords in background
then execute following command.
$JAVA_HOME/bin/java -jar ords.war standalone &
Now we can access ords with the URL below:
http://192.168.56.251:8080/ords
You may login to apex with the following details
Workspace: INTERNAL
USERNAME: ADMIN
PASSWORD: <PASSWORD SET FOR ADMIN>
Create Basic RESTful Web Services Using PL/SQL
Login as dba user and create tablespace, uses and grant required privileges.
conn / as sysdba
create tablespace tbs_rest
datafile '/u01/app/oracle/oradata/mentordb/tbs_rest01.dbf'
size 10m
autoextend on
next 10m
maxsize unlimited;
Create user user_rest
identified by oracle_4U
default tablespace tbs_rest
quota unlimited on tbs_rest;
grant connect,resource
to user_rest;
create table user_rest.employees
as select * from hr.employees;
Enable ORDS for mentordb database.
cd /home/oracle/ords
$JAVA_HOME/bin/java -jar ords.war setup --database mentordb
cd /home/oracle/ords
$JAVA_HOME/bin/java -jar ords.war setup --database mentordb
Enter the name of the database server [mentor.localdomain]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:1
Enter the database service name [mentordb]:
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:
Passwords do not match, try again
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
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]:2
Mar 24, 2017 11:12:22 AM
INFO: Updated configurations: mentordb_pu
Mar 24, 2017 11:12:22 AM oracle.dbtools.rt.config.setup.SchemaSetup install
INFO: Oracle REST Data Services schema version 3.0.9.348.07.16 is installed.
In above output all the bold black characters are the Input provided while prompting. There are lot of prompts but we use pressed enter if we are fit with the DEFAULT value mentioned between large brackets. Example in line Enter the name of the database server [mentor.localdomain]: we are ok with the hostname, no required to modify so just pressed enter to continue.
Create a URL mapping to the new database connection.
$JAVA_HOME/bin/java -jar ords.war map-url --type base-path /mentordb mentordb
Connect as usr_rest and enable REST web services for the usr_rest schema.
conn user_rest/oracle_4U
BEGIN
ORDS.enable_schema(
p_enabled => TRUE,
p_schema => 'USER_REST',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'user_rest',
p_auto_rest_auth => FALSE
);
COMMIT;
END;
/
BEGIN
ORDS.define_service(
p_module_name => 'sel_employee',
p_base_path => 'sel_employee/',
p_pattern => 'employees/',
p_method => 'GET',
p_source_type => ORDS.source_type_query,
p_source => 'SELECT * FROM employees',
p_items_per_page => 0);
COMMIT;
END;
/
We can access ORDS url as below
http://192.168.56.251:8080/ords/mentordb/user_rest/sel_employee/employees/
Uninstallation guide
Kill ords process if running in standalone mode
ps ax | grep ords
kill PID
Generate uninstallation script
cd /home/oracle/ords
/opt/jdk1.8.0_121/bin/java -jar ords.war ords-scripts --scriptdir /tmp
Start uninstallation with the command below.
su - oracle
cd /tmp/scripts/uninstall/core/
sqlplus /nolog
conn / as sysdba
@ords_manual_uninstall /tmp/scripts/logs
ORDS uninstallation completed.
