OAC - Using CLI to Automate Starting-Stopping of Instances

A couple of years ago I blogged on how to start and stop Oracle Analytics instances using the REST API. This enabled you to schedule scripts to manage the uptime of your instances so you can save those valuable oracle credits. This worked well for the old Autonomous instances but those scripts no longer work with the newer Gen2 OAC architecture. In this blog I’ll show how to use the Oracle CLI (Command Line Interface) to control your instances. 

The Oracle CLI is very simple to use, requires no coding skills and has the added bonus of being able to manage most of your Oracle Cloud Infrastructure tasks (e.g. managing DBaaS instances or virtual machines etc).

Pre-Requisites

You will require an IAM user account (non-IDCS) in your Oracle tenancy to run the CLI commands. You will need the OCID value for this user:

You also need the OCID value of your Oracle tenancy:

Installing the Oracle CLI on Windows

You can install on either Linux or Windows. I've chosen Windows as it's what most of our customers use. 

I followed the Oracle documentation on how to install CLI on Windows here: https://docs.oracle.com/en-us/iaas/Content/API/SDKDocs/cliinstall.htm 

Here is the process in a nutshell:

• Download a powershell script
• Run the powershell script. 
• This will download the Python and install it.
• It will also download the CLI and install it.

Initially, I installed Python manually but the auto-installer kept failing on the import ssl statement. I ended up deinstalling Python and getting the installation utility to install python for me.

This version of python is older (3.6) than the one I installed (failed with both 3.9 and 3.8).

Here are the commands you need to run:

Start Powershell as an administrator.

Set-ExecutionPolicy RemoteSigned 

(allow the script to run).

[Net.ServicePointManager]::SecurityProtocol = "tls12, tls11, tls" 

(enable tls otherwise it won’t be able to download the install files).

Invoke-WebRequest https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.ps1 -OutFile install.ps1

(download the script).

iex ((New-Object System.Net.WebClient).DownloadString('https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.ps1'))

(run the installer with prompts)

Here is the rest of  the input/output:

===> In what directory would you like to place the install? (leave blank to use 'C:\Users\Administrator\lib\oracle-cli')

: E:\oracle-cli

-- Creating directory 'E:\oracle-cli'.

-- We will install at 'E:\oracle-cli'.

===> In what directory would you like to place the 'oci.exe' executable? (leave blank to use 'C:\Users\Administrator\bin

'): E:\oracle-cli\bin

-- Creating directory 'E:\oracle-cli\bin'.

-- The executable will be in 'E:\oracle-cli\bin'.

===> In what directory would you like to place the OCI scripts? (leave blank to use 'C:\Users\Administrator\bin\oci-cli-

scripts'): E:\oracle-cli\scripts

-- Creating directory 'E:\oracle-cli\scripts'.

-- The scripts will be in 'E:\oracle-cli\scripts'.

===> Currently supported optional packages are: ['db (will install cx_Oracle)']

What optional CLI packages would you like to be installed (comma separated names; press enter if you don't need any opti

onal packages)?:

-- The optional packages installed will be ''.

-- Trying to use python3 venv.

-- Executing: ['C:\\Users\\Administrator\\Python\\python.exe', '-m', 'venv', 'E:\\oracle-cli']

-- Executing: ['E:\\oracle-cli\\Scripts\\python.exe', '-m', 'pip', 'install', '--upgrade', 'pip']

Collecting pip

  Downloading https://files.pythonhosted.org/packages/47/ca/f0d790b6e18b3a6f3bd5e80c2ee4edbb5807286c21cdd0862ca933f751dd

/pip-21.1.3-py3-none-any.whl (1.5MB)

There was an error at the end but it seemed to install successfully:

Test the oci.exe by running the oci –version command:

OCI CLI Setup

Run the oci setup config command to configure the OCI CLI. This will prompt you to input your User and Tenancy OCID values. It will also prompt whether you want to create the API RSA key pair which I will do in this example:

Here is the rest of the input/output:

Enter a user OCID:

Enter a user OCID: ocid1.user.oc1..xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Enter a tenancy OCID: ocid1.tenancy.oc1..xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Enter a region by index or name(e.g.

1: ap-chiyoda-1, 2: ap-chuncheon-1, 3: ap-hyderabad-1, 4: ap-melbourne-1, 5: ap-

mumbai-1,

6: ap-osaka-1, 7: ap-seoul-1, 8: ap-sydney-1, 9: ap-tokyo-1, 10: ca-montreal-1,

11: ca-toronto-1, 12: eu-amsterdam-1, 13: eu-frankfurt-1, 14: eu-zurich-1, 15: m

e-dubai-1,

16: me-jeddah-1, 17: sa-santiago-1, 18: sa-saopaulo-1, 19: sa-vinhedo-1, 20: uk-

cardiff-1,

21: uk-gov-cardiff-1, 22: uk-gov-london-1, 23: uk-london-1, 24: us-ashburn-1, 25

: us-gov-ashburn-1,

26: us-gov-chicago-1, 27: us-gov-phoenix-1, 28: us-langley-1, 29: us-luke-1, 30:

 us-phoenix-1,

31: us-sanjose-1): 23

Do you want to generate a new API Signing RSA key pair? (If you decline you will

 be asked to supply the path to an existing key.) [Y/n]: Y

Enter a directory for your keys to be created [C:\Users\Administrator\.oci]: E:\oracle-cli\keys

Enter a name for your key [oci_api_key]: oci_api_key

Public key written to: E:\oracle-cli\keys\oci_api_key_public.pem

Enter a passphrase for your private key (empty for no passphrase):

Private key written to: E:\oracle-cli\keys\oci_api_key.pem

Fingerprint: xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx

Config written to c:\oracle-cli\bin

    If you haven't already uploaded your API Signing public key through the

    console, follow the instructions on the page linked below in the section

    'How to upload the public key':

        https://docs.cloud.oracle.com/Content/API/Concepts/apisigningkey.htm#How

2

The keys were generated here:

Note that I got the config location wrong when I ran this first time (C:\oracle-cli\bin instead of E:). I'm not sure if the CLI doesn't let you use another folder than the default one but I had to create the config file in C:\Users\Administrator\.oci\config.

I just copied the contents of the file which got created by the config command earlier and created a new file called config in C:\Users\Administrator\.oci\config. I had to use the cmd mkdir command to create the folder because it starts with a full-stop.

Add the Keys to your IAM User Account

Next we need to associate the public key we created earlier with our IAM user account.

Open the oci_api_key_public.pem file and copy the entire contents (including the -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY-----).

Login to OCI and go to your IAM user account.

In the bottom left of the screen click on API Keys and then Add API Key:

Copy the contents of the public key and paste into the box:

You should then see the fingerprint and the contents which should match the config file you have on the oracle-CLI install:

Stopping and Starting OAC using the CLI Command Line

Finally we get to the nitty gritty!

Once you have configured the Oracle CLI and you have imported the keys to your IAM user account you should be able to use CLI to manage your Oracle Analytics instances (and other OCI services).

To stop OAC using CLI you run this command: 

oci analytics analytics-instance stop --analytics-instance-id <OAC instance ID>

To start OAC you run this command:

oci analytics analytics-instance start --analytics-instance-id <OAC instance ID>

You can see the commands are successful in the OAC activity log:

You can also use the GET command to get the status of the instruction:

oci analytics analytics-instance get --analytics-instance-id <OAC instance ID>

Once it is complete the lifecycle-state changes:

All that remains is to save your script as a batch file and schedule it using the standard Windows scheduler.

Other Oracle CLI Commands

There are other useful commands such as scale which allows you to scale your instance up or down.

You can find all CLI commands in the OCI CLI Command Reference guide here:

https://docs.oracle.com/en-us/iaas/tools/oci-cli/3.3.2/oci_cli_docs/cmdref/analytics.html

EPM Cloud - Import Hybrid Snapshot into Non-Hybrid Pod

If you’ve ever tried to import a EPM Planning hybrid-enabled application snapshot into a non-hybrid EPM pod you will have seen this error:

EPMLCM-14000: Error reported from Core Platform.

Unsupported migration of artifacts and snapshots for Hybrid PBCS application from (Service Type ENTERPRISE with Hybrid support) to (Service Type LEGACY without Hybrid support).

The import will fail completely and you won’t be able to pull in any artefacts.

Here is a simple hack which will enable you import the entire snapshot (excluding data of course).

Download the Snapshot

Download the Hybrid snapshot to your local machine and unzip the snapshot.

You’ll need to make changes to the 'Application Definition.xpad' and 'Application Settings.xml' files:

Edit the Application Definition.xpad File

File before changes:

File after changes:

Edit the Application Settings.xml File

File before changes:

File after changes:

Once you have made these changes zip up the folders of the snapshot. Zip up the files at this level:

You can now upload the modified snapshot and import the entire application (excluding data).

To load the data into your non-hybrid pod you will need to export the data to a text file on the Hybrid environment and then import into the non-hybrid pod.

EPM Cloud - Make the User Experience Simple & Intuitive with Direct Links

One great new feature which came out recently in the Oracle EPM Cloud is the ability to download direct links to any EPM Cloud navigation flow card or cluster. This includes links to both the default cards and custom cards and clusters which you have created. Task lists can now be super simple for the end user. Instead of a War and Peace list of instructions on how to navigate to User Preferences or how to open up Data Integration just give them a direct link!

How to access the EPM Cloud direct links?

Login to your EPM Cloud instance. In the top-right click on the down arrow next to your username and click on ‘Export URLs’:

This will download a .CSV file which is pipe delimited:

Adding the URL to a Task List

You can add any number of task list URL links to aid the end users. Some very useful ones could be User Preferences and Variables for a brand-new roll-out.

You can copy the URL and paste it into a URL type task list, here I’ll use the User Preferences card:

The task will look like this in their Task List:

Clicking on the task list will open up the cluster/card/tab in a new browser tab:

Here’s another example with Data Integration. Copy the Data Integration URL from the downloaded file:

Create your task list item:

Click on the task list and the Data Integration card opens up directly in a new browser tab. If you added instructions to the task list it also means the user can navigate back and forth between the tabs to read the instructions associated with the task.

Here is a link from the EPM Cloud youtube channel which demos how this functionality can also be used to streamline the user experience across the Oracle suite by embedding EPM content into Oracle ERP and Oracle NetSuite: https://www.youtube.com/watch?v=qJirlSZpTq4

In the next post we'll make things even slicker with a little touch of Groovy and the REST API.

EPM Integration Agent - MS SQL Server Windows Authentication

If you have tried to use Windows authentication in the database connection details for your Oracle EPM Integration Agent on-premises data source you may have come across these errors:

Error: com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user

So, how DO you use a Windows username and password with an EPM Integration Agent target?

mssql-jdbc_auth-9.2.0.x64.dll

You need to add the mssql-jdbc_auth-9.2.0.x64.dll to your JRE\bin and JRE\lib folder. This is the library used by the MS SQL jdbc driver for Windows authentication. 

You should be able to find the library in the .zip file you downloaded containing the MS SQL jdbc driver (sqljdbc_9.2.0.0_enu.zip\sqljdbc_9.2\enu\auth\x64).

Restart the agent and it should now be ready to use Windows authentication. But WAIT, there's one additional step...

integratedSecurity=true

You'll need to add an additional parameter to your jdbc connection string to let the driver know that you are using Windows authentication and not native MS SQL authentication:

jdbc:sqlserver://server:port;databaseName=dbname;integratedSecurity=true

Et voilà! The EPM Agent will now login to your MS SQL Server database using a Windows user account for authentication. 

Happy integrating :)