Friday, December 30, 2016

DIY - PeopleSoft on the Cloud - Part 3

In this third and final part, I will demonstrate how to access the demo instance (PUM image on OPC) via SQL Developer and App Designer from an on premise workstation.

Step 1: Create Security List for database access and add to the instance

Create Security IP List:

Oracle Compute Cloud Service console > Network tab > Security IP Lists > Create Security IP List

Enter the public ip address(es) of your on premise workstation(s). This will ensure that only a trusted list of on premise ip addresses can access the database (via SQL Developer/App Designer) of your demo instance (PUM image running on OPC).

Note: If you are not sure of the public ip address of your on premise workstation then simply visit https://whatismyipaddress.com/ using a browser on the workstation.


Create Security Application:

Oracle Compute Cloud Service console > Network tab > Security Applications > Create Security Application

Create a Security Application to open TCP port 1522 to allow database access. You might wonder how I figured the database for the demo instance is using port 1522? We can find it in the tnsnames.ora file located in /opt/oracle/psft/pt/tools_client/ which we can access via WinSCP (refer: Part 2).


Create Security List:

Oracle Compute Cloud Service console > Network tab > Security Lists > Create Security List


Create Security Rule:

Oracle Compute Cloud Service console > Network tab > Security Rules > Create Security Rule

Using the Security IP List, Security Application and Security List that we just created, create a Security Rule as follows:


Add Security List to Instance:

Oracle Compute Cloud Service console > Instances tab > View > Under "Security Lists" > Add to Security List

In my example, the security list that I added is called 'psftuser_sec_list_1522'.



Step 2: Installing Oracle and PeopleTools Clients (SQL Developer and App Designer)

Hypothetically, if we had installed the PUM image locally and if we did not already have the Oracle and PeopleTools clients installed on the local workstation, then we would have mapped the samba drive and installed the clients from there.

I was not able to figure out how to map the samba drive of the demo instance (PUM image) running on OPC to an on premise workstation. The alternative/workaround is to connect to the instance on OPC using WinSCP from the on premise workstation (as described in Part 2), download the relevant folders locally and install the necessary clients.

We can find the content of the samba drive under the following location using WinSCP:
 /opt/oracle/psft/pt/tools_client


Step 3: Updating the tnsnames.ora file in the on premise work station

Find the database name of the instance:

Login to the instance online and navigate to the following page:
Navigator > PeopleTools > Lifecycle Tools > Update Manager > Update Manager Dashboard > Update Manager Utilities > About PeopleSoft Image


Add database to the tnsnames.ora file on the on premise workstation:

For a typical Oracle client installation, the tnsnames.ora file can be found in the following directory:
C:\oracle\product\12.1.0\client_1\network\admin


TNS Details:

HOST: Enter the public ip address of your instance
PORT: 1522
SERVICE_NAME: Enter the database name of your instance which we determine in the preceding steps

<ENTER_DBNAME> =
   (DESCRIPTION =
       (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = <ENTER_PUBLIC_IP>)(PORT = 1522))
       )
       (CONNECT_DATA =
           (SERVER = DEDICATED)
           (SERVICE_NAME = <ENTER_DBNAME>)
       )
    )

For Example:


Step 4: Access OPC instance via SQL Developer from on premise workstation

Default Username/Password: SYSADM/SYSADM

Connection Properties:


Successful Connection:


Step 5: Access OPC instance via App Designer from an on premise workstation

Note: The default connect id and password is the same as people/peop1e

If you have not configured the default connect id/password then you can do so using Configuration Manager.

The PeopleTools client installation should be in your C drive.
For example: C:\PT8.55.11_Client_ORA\

We can find Configuration Manager (pscfg.exe) and App Designer (pside.exe) in the following location:
C:\PT8.55.11_Client_ORA\bin\client\winx86\

Connect via App Designer:

Use the database name of your OPC instance which we determined in the preceding steps.


Successful Connection:


7 comments:

  1. Hello Sasank -

    Great write-up, thank you.

    Regarding the mapping of the samba drive from the VM to your local computer for PeopleTools client installation, Oracle does have a tutorial on that topic. I tried it out and it worked well:

    http://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/compute-iaas/permitting_access_samba/permit_access_samba.html#overview

    ReplyDelete
    Replies
    1. Hi Brennan - Thanks for sharing that tutorial. I am sure others might find it very useful.

      I actually tried those steps previously but for some reason I am still not able to connect. I am pretty sure it is some setting on my on-premise workstation end that is preventing this connection.

      Did you have to open up any ports at the (on-premise) network level on your side?

      Delete
    2. I didn't make any changes to the ports or firewall on my local Windows computer. My Windows 10 computer is a VM running in VMWare Fusion on a MacBook Pro, and when I was running through this tutorial I was on my home network. However, when I tried this later while connected to my company VPN, it was not able to access the SAMBA share through the Windows computer, which is not terribly surprising as there is likely additional restrictions associated with the VPN. If you are trying this from your work PC on your work network, it is quite likely that the restriction is due to something with your company's network rather than an Oracle Cloud restriction.

      For the network setup within the Oracle Compute Cloud, I just created the security applications/lists/rules to allow traffic to ports 139 and 445 as indicated by the tutorial. Nothing special was done on the Oracle Cloud side.

      Of course, using WinSCP works just fine also.

      Delete
    3. Brennan - Thanks for the update!

      I tried using both my home and work computer. Had the same results though. I agree that it is definitely not an issue with the Oracle Compute Cloud setup.

      Based on my experience, I think my home network also has security by default and I probably need to make some changes to the network settings. I will update this post, if I figure it out.

      Thanks again!

      Delete
  2. Very useful blog. Thank you

    ReplyDelete
  3. Hello ,

    I need you help to connect to PUM image 38 instance via App Designer from my window workstation while im able to connect to PUM DB using sqlplus from same machine.
    im not able to connect application designer of new client version to PUM DB with PS user im getting below error.

    File: SQL Access ManagerSQL error. Stmt #: 2 Error Position: 0 Return: 12154 - ORA-12154: TNS:could not resolve the connect identifier specified

    can you help me how to fix this.

    ReplyDelete
    Replies
    1. Hi Deepa - First, are you able to connect via sqlplus from the same windows workstation? How are you connecting to sqlplus? Using putty?

      You local windows workstation tnsnames.ora file might need to be updated (if you have not done that already).

      Delete