Thursday, 18 March 2021

DATABASE - DROP OBJECTS SCRIPT

 BEGIN

for i in (select 'drop ' || object_type || ' SCHEMA_OWNER.' || object_name as stmt

from all_objects

where object_type in ('PACKAGE') and owner = 'SCHEMA_OWNER') loop

execute immediate i.stmt;

end loop;

end;

/



BEGIN

for i in (select 'drop ' || object_type || ' SCHEMA_OWNER.' || object_name || ' Cascade constraints purge' as stmt

from all_objects

where object_type in ('TABLE') and owner = 'SCHEMA_OWNER') loop

execute immediate i.stmt;

end loop;

END;

/




BEGIN

for i in (select 'drop ' || object_type || ' SCHEMA_OWNER.' || object_name || ' force' as stmt

from all_objects

where object_type in ('TYPE') and owner = 'SCHEMA_OWNER' ) loop

execute immediate i.stmt;

end loop;

end;

/


BEGIN

for i in (select 'drop ' || object_type || ' SCHEMA_OWNER.' || object_name  as stmt

from all_objects

where object_type in ('SEQUENCE') and owner = 'SCHEMA_OWNER' ) loop

execute immediate i.stmt;

end loop;

END;

/

Monday, 8 February 2021

ORACLE OIC - OCI Object Storage List and Download Files

We can use the REST adapter to invoke the OCI Object Storage API's to list the files under a given bucket and download the files. 

Following API will provide all the files with a specific prefix. 

/n/{tenancy_name}/b/{bucket_name}/o/

Operation = GET.

Add a request parameter . 

Name = prefix 

Type = string. 

Note:  In the request mapping provide the values for tenancy_name , bucket_name and prefix elements. 


Use the following sample JSON response message to receive and parse the response message. 

Sample Response Message : 

{

  "objects" : [ {

    "name" : "1MB.data"

  } ]

}




If there are files in object storage then response will return an array of file names. Use the loop activity to loop over each file and download the file from Object Storage. 

To download the file use the following rest URI and GET operation. 

/n/{tenancy_name}/b/{bucket_name}/o/{object_name}

While defining response type select Binary value this will auto select media type.  



Use the response and write a file using stage activity.  Use an opaque schema element to write a file. 

While mapping , user the reference element from OCI response and add reference encoding function and map it to write schema element. 



To Upload Files to OS

Context URL to Use : /n/telenet/b/{bucket_name}/o/{object_name}

Operation : PUT

Select Request Parameter and Request Payload option.

Select the request type as BINARY.

curl --verbose -X PUT https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/{tenancy}/b/{bucket_name}/o/{object_name} \

 -H "Authorization: [authorization-value]" \

 -H "Content-Type: application/octet-stream" \

 --data-binary "@file.ext" 



Saturday, 2 January 2021

OIC - New Features


Asserter to store the run results and can be used to replay the request. 

Oracle OIC agent framework uses OIC messaging channel , Oracle SaaS agent and one or more connectivity agents.

Agents polls for outbound invocations. It also support triggers configured with Agents. 

One Agent is enough to connect with all your on premise applications.  We dont need to have multiple agents for each connection. 

Agent group can contain multiple agents configured in different hosts in on premise server and they can act like HA server. 


ATP Connections : 

1. Login to OCI
2. Go to Autonomous Database
3. Select a  compartment
4. It will display the configured ATP.
5. Select the DB connection option on top
6. Download the Wallet 
7. Provide the Password while downloading the wallet. Zip file will get downloaded
8. Extract the zip file and you will find TNS file.  
9. TNS file will have an entry for each consumer type - HIGH , LOW , MEDIUM
10.  Get the required connection details from tns file
11. Select Authentication type as JDBC over SSL.
12. Updload the Wallet Zip file 
13. Provide Wallet password. 
14. Provide DB user name and password. 



Oracle Fusion - User Creation or Migration and Role Provisioning

Oracle Fusion Users can be originated in Fusion Application where in users are created in Fusion (Security Console) or can be provisioned from identity providers like IDCS to Fusion Application. 

In case users are originating at IDCS and then provisioned or synced with Fusion Application then it is required  to setup SSO application in IDCS and enable User Sync from IDCS to FA. 

It is required to configure Auto Role Provisioning to all the users and assign Employee role or Contingent worker role to User.  It will assign a default role to user when an User account is created in Fusion. 

In case users are not assigned with any role and by chance if you run a seeded job - "Send Pending LDAP Request"  or if it gets kicked off due to some other sync job like Role Assignment then it will remove the users which are not assigned with any roles.  

"Send Pending LDAP Request" job will perform following tasks. 

  • Create , Suspend and Reactivate User Accounts. 
  • User create will be triggered when Person record is created for a Worker. 
  • User will be suspended when roles are removed from a User. 
  • User will be Reactivated when a User will be rehired. 

Note: These jobs will triggered when automatic user creation and management is enabled in Fusion. 

It is not required to run this Job if the user is created manually or synced from IDCS.  It is required only if we need to create user automatically using Employee records. 

Enable Auto Creation and Management :  FA > Setup and Maintenance > Manager Enterprise HCM Information > Edit and Set > User Account Creation as - Both Person and Party User. 


Automatic Role Provisioning >  FA > Setup and Maintenance > Manager Enterprise HCM Information > Edit and Set > User Account Role Provisioning as - Both Person and Party User. 


Provision Roles to  Users Automatically. 
  • Login into FA
  • Setup and Maintenance
  • Tasks
  • Search > Manage Role Provisioning Rules / Manager Role Mapping
  • + Create
  • Give a Mapping Name - Employee Prov
  • Update Following Fields.  This will assign the roles to Active Employee
    • System Person Type =  Employee
    • HR Assignment Status = Active.
  • Under Associated role
    • + Add Row
    • Role Name >  Search for a Role - Employee Role
    • Add
    • Select Auto Provision Option. 
    • Save and Close. 
  • Follow Above Steps for Contingent Worker.
    • Add Mapping Name - Contingent Work
      • System Person Type = Contingent Worker
      • HR Assignment Status = Active
    • Add Role 
    • Select Contingent Worker Role
    • Select Auto Provision Option. 
    • Save and Close. 

When you schedule "Send Pending LDAP Request" it will perform Auto Role Provisioning. 


Sunday, 20 December 2020

ORACLE OIC - OAuth - Client with Multiple Audience from Different Resources

As we migrate from on premise to cloud ,  one of the critical requirement from customer to use user propagation capability to propagate the user credentials to ERP or Other target applications. 

This will allow the business users to track the changes and validate the records against a user making calls or updates to cloud. 

Its expected that OIC will propagate the user credentials to SaaS or other down stream application .  But this capability is not present in OIC. 

As a work around , you can configure an OIC service to receive the Authentication details in custom header and propagate to target. 

Client or users are expected to send two different authentication tokens or a single token with multiple scope or audiences.  That is the same token will be authorized in IDCS based on the audience value in the token. 

Following diagram will illustrate some of the security requirements. 






We can have an individual IDCS for each application or we can use same IDCS for multiple application.  

Users and Roles will be synced between IDCS and Cloud applications.  A sync job will be configured in IDCS to sync the users and roles. 

Above diagram illustrate the security setup and clients accessing each application using a specific token. 

Following are the some of the requirements.
  1. Client1 is configured to invoke OIC using OIC token.
  2. Client2 is configured to invoke OIC and SaaS using Single token
  3. Client2 can invoke SaaS directly using SaaS token.
  4. Client2 can invoke OIC is using OIC token. 
  5. Client3 is configured to invoke HCM using HCM token. 


  

Wednesday, 2 December 2020

Convert PEM file to PPK file

We cant use PEM file to access servers using PUTTY.  You need to convert the PEM file to PPK and then you can use the PPK file to connect to unix machine using PUTTY. 

Use the following steps to convert the PEM files to PPK file.


  • Download the PEM file 
  • Open the PuttyGen app
  • Select Type of key to generate option at the bottom as RSA.
  • Select Load
  • Select the downloaded PEM file. 
  • Putty gen will generate both PPK and Public Key. 
  • Download the ppk using Save private key option.
  • Copy the public key content from the top window and save it as public key. 
  • Use Putty and add ppk file under Auth option. 
  • Connect to Server. 

-- Happy Learning



Git Basic Commands

  • Create a remote repository using https://github.com or Oracle DCS. 
  • Generate SSH Key. 
    • Go to git bash cmd. 
    • Type   ssh-keygen  and Enter
    • Accept default values. Note down the folder. 
    • Enter Passphrase
    • Go to the folder and list the files.
    •   You will see two files - id_rsa (Private Key) id_rsa.pub (Public Key)
  • To avoid entering ssh password in case if you have given one while creating ssh key. 
    •   Run  Following Command :    eval $(ssh-agent)
    •   Then Run Following Command :  ssh-add ~private_key_path/.ssh/id_rsa
  • Add the public key in the remote server either using console or command. 
  • Get the SSH link from the Git console. 
  • Create a local folder
    • mkdir ICSLocalRepo
    • Go to local folder -  cd ICSLocalRepo
  • Clone remote repository using following commands. This will clone master branch by default. dot at the end will checkout files to current dir. 
    • git clone ssh/http_URL .
    •  Following command will create a new folder and checkout the branch.
      • git clone ssh/http_URL new_dir_name 
  • Clone a specific branch. Example - develop branch. 
    • git clone -b develop ssh/http_URL  . or new_dir_name
  • List  all remote branches. 
    • git branch -a
  • To check the remote repository details.
    •   git remote -v
  • Check the clone status using status command. 
    • git status
  • Command to add files to local repository and push it to stage
    • git add --all 
    • git add .
  • Commit command. 
    • git comit -m "Commit comments"
  • Push the changes to remote repository.
    • git push origin develop
  • Pull the latest files from remote repository. 
    • git pull --all
  • Access a specific branch 
    • git checkout develop
  • Create a new branch from a remote branch. 
    • git checkout -b release-<release-name> origin/develop
  • Push new branch to origin. 
    • git push origin release-<release-name>
  • Merge changes to a branch. This will merge the changes from new_release_branch to develop. 
    • git checkout develop 
    • git merge new_release_branch

To fix the large file name issue, execute the following GIT command first and then perform the clone.

 git config --global core.longpaths true


Oracle Fusion - Order Management Cross Reference

Summary:  This guide outlines the steps to manage cross-reference data for Sales Order FBDI upload for the source systems,  including export...