Create PaaS(Platform as a Service)

Using Enterprise Manager 13c Cloud control we can create PaaS Infrastructure Zone. It creates platforms onto which we can deploy our applications. It may be used for Databases, Middleware, SOA. A platform resource is typically comprised of a host, operating system, and application server, and may be virtualized. The platform may also include a database and even multiple hosts in a cluster.

Open Enterprise Manager Cloud control.

Click on Enterprise >> Cloud >> Cloud Home

On Cloud Home Page Click on Oracle Cloud and Click on Resource Providers.

Click on Create button on the Resource Providers page to Add Resource Providers.

Create Resource Provider window will be appeared. 

Select on PaaS Infrastructure Zone and Click on Create.

Type the Name for Target Name, Name and add description.

On members page click on Add to add hosts.

Search the member hosts that will be the part of this PaaS.

Select the member hosts that will be the part of this PaaS and Click on Select.

Click on Next once select of the member host is completed.

Select required host credentials. In my case I am creating new host credential. If already Named credential exists you can use it too.

Select required Placement Constraints and click on Next.

Provide the Characteristics. They are the values as the contact details, Cost Center, Department and others.

The Role page will be appeared. Click on Add to add the Roles that will have access on this PaaS Infrastructure Zone.

Search the role.

Select the Role, in this case ROLE_SSA has been selected.

Once Role is selected click on Next to continue.

Finally Click on Review to review the details. Click on Submit to Create PaaS.

Now PaaS Infrastructure Zone creation is on progress.

Read More

Create privileges and Roles

Once you login as user SYSMAN on the Oracle Enterprise Manager 13c Cloud Control. Click on Setup >> Security >> Roles

Below is the landing page Once you click on Roles. Enter Name of the Role and the Descriptions, Click on Next to continue.

Select additional Role that need to be granted for the newly created roles. Click on Next to continue.

We do not modify and of the target privileges so Click on Next to continue or Click on Review to complete the creation step and just to Review for task Completion.

Click on Finish to complete installation.

Once we click on Finish the Role creation completes. We can get the confirmation message as below.

Create Required SSA Users. 

Click on Setup >> Security >> Administrator.

Below is the landing page once you click on Administrator. Click on Create to create new user.

Type required username, password and Click on Next to continue.

Remove default groups, that is remove EM_USER and PUBLIC. Add Newly created Role ROLE_SSA. 

Once you add the Role. Click on Next to continue. 

Use default Target Privileges Click on Next to continue.

Leave default settings for Resource Privileges and Click on Next to continue.

Below is the Review page. Check all the details and Click on Finish to complete user creation.

Below is the confirmation page once you complete user creation.

Read More

Oracle Create REST enable web service with Oracle PL/SQL

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.

Read More