Saturday, March 25, 2017

Create privileges and Roles

6:49 AM Posted by Dilli Raj Maharjan , No comments
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.


Friday, March 24, 2017

Oracle Create REST enable web service with Oracle PL/SQL

12:00 AM Posted by Dilli Raj Maharjan , No comments

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.

Wednesday, March 15, 2017

Install required OEM Plug-ins for Oracle Private Cloud

9:08 AM Posted by Dilli Raj Maharjan , No comments

Below are the list of the plugins required for Oracle Private Database Cloud setup.


  1. Oracle Cloud Framework
  2. Oracle Fusion Middleware
  3. Oracle Virtualization
  4. Oracle Cloud Application
  5. Oracle Storage Management Framework
  6. Oracle Consolidation Planning and Chargeback

I have read somewhere that all the plugins are installed in the same order as listed above. There are dependency issues so it is better to install one at a time.

Once you complete installation, login to Enterprise Manager Cloud Control 13c as SYSMAN.

Click on Setup > Extensibility > Plug-ins.



Below is the landing page for plug-ins setup.



Click on Deploy on > Management Servers.



Below is the landing page once you click Deploy on > Management Servers. Click on Add. 



Plugin selection page will be displayed.



Select the required Plug-in. In our case since Oracle Cloud Framework and Oracle Fusion Middleware come with default installation. We are moving a head with the third one Oracle Virtualization. Select Oracle Virtualization and Click on OK



Click on Next to continue.



Select the backup the repository option and select NC_DB_SYS and the Named credential. These Named Credential has been created earlier Click on Next to continue.



Click on Deploy to begin deployment.



Click on OK to confirm and begin deployment.



We can view the status of plug-in deployment from command line with the command below:
cd $OMS_HOME
cd bin
./emctl status oms -details



Once the deployment of the plugin get completed we need to deploy next plugin Oracle Cloud Application.
On Plug-ins page Select Oracle Cloud Application plugin and Click on Deploy on > Management Servers.



Click on Next to continue.



Once pre-requisite check completes,  Click on Next to continue.




Select the backup the repository option and select NC_DB_SYS and the Named credential. These Named Credential has been created earlier Click on Next to continue.




Click on Deploy to continue.



Click on OK to confirm and begin plug-in deployment.



Finally adding two more plugins. Add Oracle Storage Management Framework and Oracle Consolidation Planning Chargeback.



Click on Next Once Pre-requisite get completes.



Select the backed up option and Select proper Named Credential. Click on Next to continue.



Click on Deploy to begin deployment.



Click on OK to confirm the deployment.