HR_OPERATING_UNITS Returns No Data in SQL Developer — The Hidden NLS Language Issue in Oracle Apps

HR_OPERATING_UNITS Returns No Data in SQL Developer — The Hidden NLS Language Issue in Oracle Apps

Recently, while working with a custom user/schema in Oracle E-Business Suite (EBS), I hit an unexpected issue. I executed the following simple queries:

select count(*) from apps.hr_operating_units;

select count(*) from hr_operating_units;

To my surprise, both queries returned zero rows.

I knew very well that the HR Operating Units were already defined in the system, and other users could see the data. So why was my SQL Developer session not returning anything?

After a bit of investigation, I discovered the real culprit — NLS Language settings in SQL Developer.

🧩 Understanding the Problem

1. HR_OPERATING_UNITS is not a simple table

This object is a view owned by the APPS schema. It pulls data from multiple HR and FND tables:

HR_ALL_ORGANIZATION_UNITS

HR_OPERATING_UNITS

FND_LOOKUP_VALUES

and other HR-related lookup tables

These underlying tables often use lookup codes and meanings that are language dependent in EBS.

2. SQL Developer session language matters

When you connect using SQL Developer, it sets certain environment parameters automatically, including:

NLS_LANGUAGE

NLS_TERRITORY

NLS_DATE_FORMAT

If NLS_LANGUAGE does not match the base language installed in Oracle EBS (typically AMERICAN), then any HR or FND lookup join that depends on translated values may fail to match — which results in no rows returned.

This is exactly what happened with the HR_OPERATING_UNITS query.

The Fix: Set NLS_LANGUAGE to AMERICAN

Once I changed the NLS settings in SQL Developer, everything started working correctly.

Steps to Fix in SQL Developer

Go to

Tools → Settings → Database → NLS

Set the following:

NLS_LANGUAGE = AMERICAN

NLS_TERRITORY = AMERICA

Reconnect your database session.

Rerun the query:

SELECT COUNT(*) FROM apps.hr_operating_units;

Most Oracle EBS installations use AMERICAN as the base language.The HR and FND lookup tables store the translations for AMERICAN only. So when SQL Developer requested data using another language like:

ENGLISH

INDIA

FRENCH

GERMAN

or any other locale

The view internally filtered out rows because there were no lookup translations for that language.

Switching to AMERICAN ensured lookup matches were found.

This prevents issues with:

- Value sets not showing correct meanings

- HR views returning no rows

- Date conversion errors

- Responsibility-based security failing

- Incorrect FND lookup behavior

Thanks & Regards,

Chandan Tanwani

Fixing OCI Load Balancer Error: “Invalid SSL Configuration – Certificate Alias Required”

Fixing OCI Load Balancer Error: “Invalid SSL Configuration – Certificate Alias Required”

Recently, while creating a Load Balancer in OCI using the new UI, I ran into a strange SSL error that took me a good amount of time to troubleshoot. Since I didn’t find anything useful on Google or Oracle MOS, I am documenting my experience here so it can help others facing the same issue.

This article explains the issue, what causes it, and the exact workaround that finally fixed it.

💥 The Error Message

During Load Balancer creation, at the Configure Listener step, I selected:

• Load Balancer Managed Certificate
• Uploaded SSL Certificate
• Uploaded CA Certificate
• Uploaded Private Key

As soon as I clicked Submit, OCI returned this error:

1 problem occurred:

Load balancer

Invalid SSL configuration: [Constraint listener:listener_lb_2025-1202-1553 violation: Invalid listener ssl configuration.

* Constraint sslConfiguration violation: Certificate alias or certificateIds / trustedCertificateAuthorityIds required ]

This was confusing because all required certificate components were uploaded correctly.

🔍 Root Cause (New OCI UI Issue)

I spent a lot of time searching for the root cause:

No information on Google

No Oracle blogs

No Oracle MOS note

Nothing in developer forums

This pointed to one thing — a UI-level issue in the new OCI Console (2025 release).

Although the SSL certificate was uploaded, the new interface fails to map the certificate to the listener being created. OCI internally expects:

certificateAlias

OR

certificateIds

OR

trustedCertificateAuthorityIds

Because the new UI does not pass these values correctly, the listener creation fails.

🛠️ Working Solution (Workaround)

The fix is very simple:

➡️ Do NOT upload an SSL certificate during Load Balancer creation.

Instead:

Create the Load Balancer without SSL

After creation, manually upload your certificate

Once you follow this sequence, everything works perfectly.

📌 After Load Balancer Creation — Two Ways to Add Certificates

OCI gives you two options to add SSL certificates after LB creation:

1️⃣ Certificate Service Managed Certificates (Recommended)

Steps to Upload Certificate into OCI Certificate Service

• Click on the three horizontal lines (☰) in the top-left corner
• Navigate to Identity & Security
• Under Certificates, click Certificates
• Choose the correct compartment
• Click the Add Certificate button
• Upload your certificate details
• Save

Once uploaded:

• Go to your Load Balancer
• Open Certificate & Ciphers
• Under Certificate service managed certificates, click Assign Certificate
• Select the certificate you uploaded
• Update the listener

2️⃣ Load Balancer Managed Certificates (Manual Upload)

Use this if you want to upload certificates directly to the Load Balancer.

Steps:

Open your Load Balancer

• Go to Certificate & Ciphers
• Scroll to Load balancer managed certificates
• Click Add Certificate
• Upload:

SSL Certificate

CA Certificate

Private Key

• Save

Update the Listener to use this certificate

This approach is straightforward and works perfectly with the new UI.

Thanks & Regards,

Chandan Tanwani

ORA-12545: Connect failed because target host or object does not exist

Fixing ORA-12545: When Your Oracle Binary Goes Missing

Recently, while connecting to one of my test database instances, I ran into a frustrating error:

ORA-12545: Connect failed because target host or object does not exist

At first glance, this looks like a typical network issue — host unreachable, wrong IP, DNS mismatch, etc. But in my case, the root cause was completely different.

---

Digging Into the Error

After searching across a few forums and documents, I landed on the Oracle Support Note KB167814 – Troubleshooting guide for ORA-12545 / TNS-12545.
This document outlines three common problem categories along with causes and solutions.

From those, one scenario matched my situation exactly.

---

The Actual Problem

Problem:
You are trying to connect locally on the server without using the listener (using BEQ protocol). Such connections can fail with ORA-12545.

Cause:
The Oracle binary is missing from the $ORACLE_HOME/bin directory.

---

Diagnosis

To verify, I ran a quick check:

$ cd $ORACLE_HOME/bin
$ ls -l oracle

The result confirmed the problem — the oracle binary was missing.

And without this binary, the database simply cannot function.

What You Lose Without the Oracle Binary

• You cannot start the database
• You cannot mount or open the DB
• You cannot connect as SYSDBA locally
• You cannot use sqlplus / as sysdba

Why?

Because the oracle binary is the actual database engine. Without it, the entire Oracle stack becomes unusable.

---

Resolution Options

At this point, I had three practical solutions:

1. Raise an SR with Oracle Support and involve the DB Install team

2. Reinstall the Oracle Home (only the software, not the database)

3. Copy the oracle binary from another server

I chose the 3rd option, but with extreme caution.

---

Copying the Oracle Binary: Not as Simple as It Sounds

The oracle executable is not a simple file that you can copy from any system.
It relies on:

✔ Exact OS version
✔ Exact Kernel version
✔ Exact glibc & system libraries
✔ Exact Oracle patch level (RU/RUR)
✔ Identical ORACLE_HOME structure
✔ Identical linking options used during installation

If any of these differ, the copied binary will fail — or worse, cause unpredictable issues.

Fortunately, in my case, the source server matched my environment exactly.
I copied the binary, set the correct permissions, and—good news—my database started successfully.

---

Conclusion

The ORA-12545 error is often mistaken as just a networking issue. But sometimes the cause lies deeper inside the Oracle Home itself.
If you're connecting using BEQ and getting ORA-12545, don't forget to check whether your oracle binary actually exists.

A missing binary is rare, but when it happens, knowing the right direction saves enormous troubleshooting time. 

Thanks & Regards,

Chandan Tanwani

How to Transfer Files Between an OCI VM (Compute Instance) and Object Storage (Part - 3) 

Check out Part -1 - Install OCI CLI, pre-requisite 

Check out Part -2 - Upload files to Object storage

In this article, let’s take the reverse approach:

downloading a file from an OCI Object Storage bucket into a VM that lives in a private subnet.

This is a common scenario — your production VMs are usually in private subnets with no public IP, and you still need to move files in and out efficiently.

Let me explain the simplest way to do it.

🔹 Key Challenge

A private-subnet VM cannot reach the internet directly.

But the good news,

👉 OCI CLI works perfectly inside private subnets via the Service Gateway, as long as it's properly configured.

If your subnet is connected to:

✔️ Service Gateway

AND

✔️ Object Storage Public Endpoint allowed

…you can directly download objects from Object Storage using CLI.

If your subnet isn't configured this way, you can still use the OCI Bastion Service to connect securely and run CLI commands.

Step 1: Confirm Network Access to Object Storage

From the private VM, run:

#> oci os ns get

If you get a valid namespace response, you're connected to Object Storage.

If it fails, ensure:

-> Subnet has Service Gateway attached

-> Route table has: 0.0.0.0/0 → Service Gateway

-> No overly restrictive security lists

Step 2: Download a File from Object Storage

Use the oci os object get command.

Basic Download Command

oci os object get \

  --bucket-name <bucket_name> \

  --name <object_name> \

  --file <output_file_name>

Example

Download db_backup.tar.gz from bucket my-backup-bucket:

oci os object get \

  --bucket-name my-backup-bucket \

  --name db_backup.tar.gz \

  --file db_backup.tar.gz

This saves the file in your current working directory.

Download Large Files

If you're dealing with multi-GB backups:

oci os object get \

  --bucket-name my-backup-bucket \

  --name largefile.gz \

  --file largefile.gz \

  --multipart-download-threshold 64 \

  --part-size 64

Step 3: Verify the File

After download:

ls -lh

and optionally check checksum:

md5sum db_backup.tar.gz

If SSH is disabled or restricted, Access Private VM via OCI Bastion

Click here to know more how to access Private VM via OCI Bastion service

Once you're inside the bastion session, the exact same download commands work.

🎉 That's It!

Thanks & Regards,

Chandan Tanwani

How to Transfer Files Between an OCI VM (Compute Instance) and Object Storage (Part - 2)

Transferring a file from an OCI Compute Instance (VM) to Object Storage is one of the simplest and most reliable ways to store, archive, or share data.

Once your OCI CLI is installed and configured (as explained in Part - 1), you're ready for the next step.

Step 1: Verify You Have Access to the Bucket

Before uploading anything, check that:

- The bucket exists

- You have the right permissions (via IAM policy)

- Your CLI is configured for the correct region

- List the buckets in your compartment:

        oci os bucket list --compartment-id <compartment_ocid>

If the bucket you want appears, you're good to go.

Step 2: Upload a File

Use the oci os object put command to upload a file.

Basic Upload Command

oci os object put \

  --bucket-name <bucket_name> \

  --file <local_file_path>

Example

Suppose your file is /u01/backups/db_backup.tar.gz and your bucket is my-backup-bucket:

oci os object put \

  --bucket-name my-backup-bucket \

  --file /u01/backups/db_backup.tar.gz

What Happens Here?

-> OCI CLI uploads the file

-> The object appears inside the bucket with the same filename

-> If the object already exists, it will be overwritten unless you disable overwrite

If you want to specify a different object name:

Example:

oci os object put \

  --bucket-name my-backup-bucket \

  --file db_backup.tar.gz \

  --name MY_Backup_26Nov2025.tar.gz

Bulk Upload files using below command

Example:

oci os object bulk-upload \

  -bn my-backup-bucket \

  --src-dir /u01/RMANBackup26Nov2025 \

  --include "*.bkp" \

  --prefix rman/

Upload Large Files

For large files (GBs or TB-scale), use the --part-size option.

Example:

oci os object put \

  --bucket-name my-backup-bucket \

  --file db_backup.tar.gz \

  --part-size 64

Step 3: Verify Upload

List objects inside the bucket:

oci os object list --bucket-name my-backup-bucket

To confirm size or metadata:

oci os object head \

  --bucket-name my-backup-bucket \

  --name db_backup.tar.gz

🎉 That's It!

You’ve now successfully uploaded files from a VM to Object Storage using OCI CLI.

This is the method I use daily for backups and log transfers.

In the next article, we will go the opposite direction — downloading files from Object Storage to a private subnet VM, even when it has no public internet access.

Thanks & Regards,

Chandan Tanwani

How to Transfer Files Between an OCI VM (Compute Instance) and Object Storage (Part - 1)

I’m writing a small series of articles where I walk you through one of the most common tasks we perform on Oracle Cloud Infrastructure (OCI):

moving files between a VM and an Object Storage bucket.

Whether you want to upload backups from your VM or download files into a private subnet, the process becomes extremely simple once your OCI CLI is set up correctly.

In this article, I’ll cover three clear steps, just the way I explain it to anyone who asks me:

1) Install and configure OCI CLI - (This only)

2) Upload files from a Public-Subnet VM to Object Storage

3) Download files from Object Storage to a Private-Subnet VM

Let’s begin!

1) Installing and Configuring OCI CLI

The very first prerequisite is simple:

👉 Your VM must have OCI CLI installed.

Log in to your VM using the opc user or any other privileged user.

I personally logged in using the oracle user (which was created during VM setup), and ran the installation from there.

Step 1: Install the OCI CLI

Run the following command:

curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh | bash

This script handles everything—from downloading the CLI to setting up environment variables.

[oracle@vm-test-1 ~]$ curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh | bash

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current

                                 Dload  Upload   Total   Spent    Left  Speed

100 17155  100 17155    0     0   186k      0 --:--:-- --:--:-- --:--:--  188k

WARNING: Some interactive prompts may not function correctly if this script is piped into bash (e.g. 'curl "https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh" | bash)'

WARNING: Script should either be downloaded and invoked directly, or be run with the following command: bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"

    ******************************************************************************

    You have started the OCI CLI Installer in interactive mode. If you do not wish

    to run this in interactive mode, please include the --accept-all-defaults option.

    If you have the script locally and would like to know more about

    input options for this script, then you can run:

    ./install.sh -h

    If you would like to know more about input options for this script, refer to:

    https://github.com/oracle/oci-cli/blob/master/scripts/install/README.rst

    ******************************************************************************

Downloading Oracle Cloud Infrastructure CLI install script from https://raw.githubusercontent.com/oracle/oci-cli/v3.68.1/scripts/install/install.py to /tmp/oci_cli_install_tmp_zdp8.

######################################################################## 100.0%

Running install script.

python3 /tmp/oci_cli_install_tmp_zdp8 

-- Verifying Python version.

-- Python version 3.6.8 okay.

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

-- Creating directory '/home/oracle/lib/oracle-cli'.

-- We will install at '/home/oracle/lib/oracle-cli'.

===> In what directory would you like to place the 'oci' executable? (leave blank to use '/home/oracle/bin'): 

-- Creating directory '/home/oracle/bin'.

-- The executable will be in '/home/oracle/bin'.

===> In what directory would you like to place the OCI scripts? (leave blank to use '/home/oracle/bin/oci-cli-scripts'): 

-- Creating directory '/home/oracle/bin/oci-cli-scripts'.

-- The scripts will be in '/home/oracle/bin/oci-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 optional packages)?: 

-- The optional packages installed will be ''.

-- Trying to use python3 venv.

-- Executing: ['/bin/python3', '-m', 'venv', '/home/oracle/lib/oracle-cli']

-- Executing: ['/home/oracle/lib/oracle-cli/bin/pip', 'install', '--upgrade', 'pip']

Collecting pip

  Downloading https://files.pythonhosted.org/packages/a4/6d/6463d49a933f547439d6b5b98b46af8742cc03ae83543e4d7688c2420f8b/pip-21.3.1-py3-none-any.whl (1.7MB)

    100% |████████████████████████████████| 1.7MB 1.1MB/s 

Installing collected packages: pip

  Found existing installation: pip 9.0.3

    Uninstalling pip-9.0.3:

      Successfully uninstalled pip-9.0.3

Successfully installed pip-21.3.1

You are using pip version 21.3.1, however version 25.3 is available.

You should consider upgrading via the 'pip install --upgrade pip' command.

-- Executing: ['/home/oracle/lib/oracle-cli/bin/pip', 'install', '--cache-dir', '/tmp/tmpr32ini5k', 'wheel', '--upgrade']

Collecting wheel

  Downloading wheel-0.37.1-py2.py3-none-any.whl (35 kB)

Installing collected packages: wheel

Successfully installed wheel-0.37.1

-- Executing: ['/home/oracle/lib/oracle-cli/bin/pip', 'install', '--cache-dir', '/tmp/tmpr32ini5k', 'oci_cli', '--upgrade']

Collecting oci_cli

  Downloading oci_cli-3.71.0-py3-none-any.whl (25.2 MB)

     |████████████████████████████████| 25.2 MB 40.6 MB/s            

Collecting six>=1.15.0

  Downloading six-1.17.0-py2.py3-none-any.whl (11 kB)

Collecting click==8.0.4

  Downloading click-8.0.4-py3-none-any.whl (97 kB)

     |████████████████████████████████| 97 kB 13.4 MB/s            

Collecting jmespath==0.10.0

  Downloading jmespath-0.10.0-py2.py3-none-any.whl (24 kB)

Collecting certifi>=2025.1.31

  Downloading certifi-2025.4.26-py3-none-any.whl (159 kB)

     |████████████████████████████████| 159 kB 97.5 MB/s            

Collecting pyOpenSSL<=25.1.0,>=17.5.0

  Downloading pyOpenSSL-23.2.0-py3-none-any.whl (59 kB)

     |████████████████████████████████| 59 kB 13.1 MB/s            

Collecting arrow>=1.0.0

  Downloading arrow-1.2.3-py3-none-any.whl (66 kB)

     |████████████████████████████████| 66 kB 10.9 MB/s            

Collecting pytz>=2016.10

  Downloading pytz-2025.2-py2.py3-none-any.whl (509 kB)

     |████████████████████████████████| 509 kB 87.1 MB/s            

Collecting terminaltables==3.1.10

  Downloading terminaltables-3.1.10-py2.py3-none-any.whl (15 kB)

Collecting oci==2.164.0

  Downloading oci-2.164.0-py3-none-any.whl (33.0 MB)

     |████████████████████████████████| 33.0 MB 86.1 MB/s            

Collecting prompt-toolkit==3.0.29

  Downloading prompt_toolkit-3.0.29-py3-none-any.whl (381 kB)

     |████████████████████████████████| 381 kB 36.3 MB/s            

Collecting cryptography<46.0.0,>=3.2.1

  Downloading cryptography-40.0.2-cp36-abi3-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (3.7 MB)

     |████████████████████████████████| 3.7 MB 83.2 MB/s            

Collecting PyYAML<=6.0.2,>=5.4

  Downloading PyYAML-6.0.1-cp36-cp36m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (677 kB)

     |████████████████████████████████| 677 kB 90.0 MB/s            

Collecting python-dateutil<3.0.0,>=2.5.3

  Downloading python_dateutil-2.9.0.post0-py2.py3-none-any.whl (229 kB)

     |████████████████████████████████| 229 kB 94.4 MB/s            

Collecting importlib-metadata

  Downloading importlib_metadata-4.8.3-py3-none-any.whl (17 kB)

Collecting circuitbreaker<2.0.0,>=1.3.1

  Downloading circuitbreaker-1.4.0.tar.gz (9.7 kB)

  Preparing metadata (setup.py) ... done

Collecting wcwidth

  Downloading wcwidth-0.2.14-py2.py3-none-any.whl (37 kB)

Collecting typing-extensions

  Downloading typing_extensions-4.1.1-py3-none-any.whl (26 kB)

Collecting cffi>=1.12

  Downloading cffi-1.15.1-cp36-cp36m-manylinux_2_5_x86_64.manylinux1_x86_64.whl (402 kB)

     |████████████████████████████████| 402 kB 81.5 MB/s            

Collecting pycparser

  Downloading pycparser-2.21-py2.py3-none-any.whl (118 kB)

     |████████████████████████████████| 118 kB 82.6 MB/s            

Collecting zipp>=0.5

  Downloading zipp-3.6.0-py3-none-any.whl (5.3 kB)

Building wheels for collected packages: circuitbreaker

  Building wheel for circuitbreaker (setup.py) ... done

  Created wheel for circuitbreaker: filename=circuitbreaker-1.4.0-py3-none-any.whl size=7506 sha256=7e604a258e20a46edfdfd999605faec6d0a4ed5652229a140b4bec6b07f3c2eb

  Stored in directory: /tmp/tmpr32ini5k/wheels/46/17/98/db2eb826e4a98da672cffe66ec16838182cde0cf19ad2c0c70

Successfully built circuitbreaker

Installing collected packages: pycparser, cffi, zipp, typing-extensions, six, cryptography, wcwidth, pytz, python-dateutil, pyOpenSSL, importlib-metadata, circuitbreaker, certifi, terminaltables, PyYAML, prompt-toolkit, oci, jmespath, click, arrow, oci-cli

Successfully installed PyYAML-6.0.1 arrow-1.2.3 certifi-2025.4.26 cffi-1.15.1 circuitbreaker-1.4.0 click-8.0.4 cryptography-40.0.2 importlib-metadata-4.8.3 jmespath-0.10.0 oci-2.164.0 oci-cli-3.71.0 prompt-toolkit-3.0.29 pyOpenSSL-23.2.0 pycparser-2.21 python-dateutil-2.9.0.post0 pytz-2025.2 six-1.17.0 terminaltables-3.1.10 typing-extensions-4.1.1 wcwidth-0.2.14 zipp-3.6.0

===> Modify profile to update your $PATH and enable shell/tab completion now? (Y/n): 

===> Enter a path to an rc file to update (file will be created if it does not exist) (leave blank to use '/home/oracle/.bashrc'): 

-- Backed up '/home/oracle/.bashrc' to '/home/oracle/.bashrc.backup'

-- Tab completion set up complete.

-- If tab completion is not activated, verify that '/home/oracle/.bashrc' is sourced by your shell.

-- ** Run `exec -l $SHELL` to restart your shell. **

-- Installation successful.

Announcement

============

1. Interactive mode now available in CLI

Have you tried the new interactive features in OCI CLI yet? You can get started by typing `oci -i`.

Learn more by watching our informative video on YouTube -> https://www.youtube.com/watch?v=lX29Xw1Te54&ab_channel=OracleLearning

Also see https://docs.oracle.com/iaas/Content/API/SDKDocs/cliusing_topic-Using_Interactive_Mode.htm

============

-- Run the CLI with /home/oracle/bin/oci --help

[oracle@vm-test-1 ~]$ 

[oracle@vm-test-1 ~]$ 

After installation, verify it:

oci --version

If you see the version printed, your CLI is installed successfully.

[oracle@vm-test-bl-ebs-1 ~]$ oci --version

/home/oracle/lib/oracle-cli/lib64/python3.6/site-packages/oci/_vendor/httpsig_cffi/sign.py:10: CryptographyDeprecationWarning: Python 3.6 is no longer supported by the Python core team. Therefore, support for it is deprecated in cryptography. The next release of cryptography will remove support for Python 3.6.

  from cryptography.hazmat.backends import default_backend  # noqa: F401

3.71.0

[oracle@vm-test-bl-ebs-1 ~]$ 

Step 2: Configure the OCI CLI

Next, we need to configure it with your OCI credentials.

Run: oci setup config

The CLI will ask for:

User OCID

Tenancy OCID

Region

Directory for storing API keys

Passphrase for the private key

[oracle@vm-test-1 ~]$ oci setup config

/home/oracle/lib/oracle-cli/lib64/python3.6/site-packages/oci/_vendor/httpsig_cffi/sign.py:10: CryptographyDeprecationWarning: Python 3.6 is no longer supported by the Python core team. Therefore, support for it is deprecated in cryptography. The next release of cryptography will remove support for Python 3.6.

  from cryptography.hazmat.backends import default_backend  # noqa: F401

    This command provides a walkthrough of creating a valid CLI config file.

    The following links explain where to find the information required by this

    script:

    User API Signing Key, OCID and Tenancy OCID:

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

    Region:

        https://docs.cloud.oracle.com/Content/General/Concepts/regions.htm

    General config documentation:

        https://docs.cloud.oracle.com/Content/API/Concepts/sdkconfig.htm

Enter a location for your config [/home/oracle/.oci/config]: 

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

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

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

1: af-johannesburg-1, 2: ap-batam-1, 3: ap-chennai-1, 4: ap-chiyoda-1, 5: ap-chuncheon-1,

6: ap-chuncheon-2, 7: ap-dcc-canberra-1, 8: ap-dcc-gazipur-1, 9: ap-delhi-1, 10: ap-hyderabad-1,

11: ap-ibaraki-1, 12: ap-melbourne-1, 13: ap-mumbai-1, 14: ap-osaka-1, 15: ap-seoul-1,

16: ap-seoul-2, 17: ap-singapore-1, 18: ap-singapore-2, 19: ap-suwon-1, 20: ap-sydney-1,

21: ap-tokyo-1, 22: ca-montreal-1, 23: ca-toronto-1, 24: eu-amsterdam-1, 25: eu-budapest-1,

26: eu-crissier-1, 27: eu-dcc-dublin-1, 28: eu-dcc-dublin-2, 29: eu-dcc-milan-1, 30: eu-dcc-milan-2,

31: eu-dcc-rating-1, 32: eu-dcc-rating-2, 33: eu-dcc-zurich-1, 34: eu-frankfurt-1, 35: eu-frankfurt-2,

36: eu-jovanovac-1, 37: eu-madrid-1, 38: eu-madrid-2, 39: eu-madrid-3, 40: eu-marseille-1,

41: eu-milan-1, 42: eu-paris-1, 43: eu-stockholm-1, 44: eu-zurich-1, 45: il-jerusalem-1,

46: me-abudhabi-1, 47: me-abudhabi-2, 48: me-abudhabi-3, 49: me-abudhabi-4, 50: me-alain-1,

51: me-dcc-doha-1, 52: me-dcc-muscat-1, 53: me-dubai-1, 54: me-ibri-1, 55: me-jeddah-1,

56: me-riyadh-1, 57: mx-monterrey-1, 58: mx-queretaro-1, 59: sa-bogota-1, 60: sa-santiago-1,

61: sa-saopaulo-1, 62: sa-valparaiso-1, 63: sa-vinhedo-1, 64: uk-cardiff-1, 65: uk-gov-cardiff-1,

66: uk-gov-london-1, 67: uk-london-1, 68: us-ashburn-1, 69: us-ashburn-2, 70: us-chicago-1,

71: us-columbus-1, 72: us-gov-ashburn-1, 73: us-gov-chicago-1, 74: us-gov-phoenix-1, 75: us-langley-1,

76: us-luke-1, 77: us-newark-1, 78: us-phoenix-1, 79: us-saltlake-2, 80: us-sanjose-1,

81: us-somerset-1, 82: us-thames-1): 68

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 [/home/oracle/.oci]: 

Enter a name for your key [oci_api_key]: 

Public key written to: /home/oracle/.oci/oci_api_key_public.pem

Enter a passphrase for your private key ("N/A" for no passphrase): 

Repeat for confirmation: 

Private key written to: /home/oracle/.oci/oci_api_key.pem

Fingerprint: aa:3a:ff:d9:c5:10:7a:50:4f:ef:c0:f3:f4:b2:d2:72

Config written to /home/oracle/.oci/config

    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#How2

[oracle@vm-test-1 ~]$ 

[oracle@vm-test-1 ~]$ 

Note:- In above code snippets I kept most of the things default. But you can change it according to your environment.

Once you enter these inputs, the CLI will generate the required keys and a config file.

Next, we need to configure it with your OCI credentials.

Step 3: Add the API Key in OCI Console

This part is important.

Now that your CLI generated a public key, you must register it in OCI:

Log in to OCI Console, Open User Settings

Go to Tokens and Keys,  Click on API Keys

Choose Add API Key

Upload the public key generated during the CLI setup

Once added, you will see a fingerprint — keep it noted; it confirms your key is active.

This completes the foundational setup.

Now your VM is ready to communicate with OCI Object Storage securely using the CLI.

In the next steps, I’ll show you how to upload from a public-subnet VM and download into a private-subnet VM using Bastion or direct CLI commands.

Thanks & Regards,

Chandan Tanwani

My Session at Oracle AI World: KNEX - Autonomous Database Journey

My Session at Oracle AI World: KNEX - Autonomous Database Journey

From this year onwards, Oracle has rebranded its flagship annual conference — Oracle Cloud World — to Oracle AI World, reflecting the company’s strong focus on Artificial Intelligence and its deep integration across Oracle products and solutions.

I was glad and truly honored to be part of this prestigious global event — not just as an attendee, but as a speaker representing real-world expertise and customer success stories.

My session, titled “Oracle Autonomous Database Best Practices and Lessons Learned: Customer Panel,” drew a full house of participants eager to explore practical insights and proven approaches for maximizing the value of Oracle Autonomous Database. The discussion was vibrant, with attendees keen to understand how AI-driven automation and optimization are transforming data management and performance tuning.

It was an incredible experience to share the stage with industry experts, exchange ideas, and connect with professionals from around the world — all united by the vision of making data smarter with AI.

I had covered following key topics:

- Our development use cases

- Evaluation criteria for adopting Autonomous Database

- Building blocks of our architecture

- Key lessons learned and takeaways from our implementation journey

It was an honor to co-present alongside Holger Friedrich, Sreedhar Mohan, Amar Kumar, and Peter Merkert, who shared their valuable experiences with the audience.

A truly amazing experience at Oracle AI World — learning, connecting, and exchanging ideas with the global community.

Many thanks to Vivek Sharma and Reiner Zimmermann for the opportunity to share our story on such a prestigious global platform.

Special thanks to Basheer Khan and Hadi Syed Abdul for all their continuous support throughout this journey.

Thanks & Regards,

Chandan Tanwani

AIOUG OCYatra 2025: A Journey of Knowledge, Community, and Inspiration

AIOUG OCYatra 2025 was nothing short of phenomenal. As someone who’s been deeply involved with the Oracle community for years, I feel incredibly grateful to be part of this journey — traveling across six vibrant cities in India: Bengaluru, Hyderabad, Pune, Mumbai, Delhi, and Chennai.

What made it even more special was not just attending the sessions, but being a speaker, a contributor, a connector — and someone fully immersed in both the learning and execution side of things.

🚆 City-by-City: The Journey

• Bengaluru – The kickoff city! The energy was electrifying. Tech enthusiasts and developers poured in, setting the tone for what would become an inspiring series of events.
• Hyderabad – A city buzzing with Oracle talent. Loved the questions, the hallway conversations, and catching up with community members.
• Pune – My home turf. Speaking here felt like a homecoming. Great turnout, engaging crowd, and vibrant networking.
• Mumbai – Business meets technology! The vibe was intense, and the discussions on cloud and Gen AI were top-notch.
• Delhi – A very enthusiastic crowd. Speaking here was a delight. The level of curiosity and interaction was truly rewarding.
• Chennai – The final city. A warm close to an exhilarating journey. Chennai gave a fitting wrap-up with amazing hospitality and insightful dialogue.

🎤 Speaker Moments: Sharing What I Love

I had the honor of speaking in three cities — Bengaluru, Pune, and Delhi, delivering my session on:
"Setup to Scaling: Why Developers Love Autonomous Database"

In this talk, I explored how Oracle Autonomous Database simplifies life for developers — from setup to seamless scaling — while enabling performance, security, and ease of development. I showcased live demos and real-world developer use cases that generated great interest and sparked some wonderful post-session discussions.

🎯 Behind the Scenes: Managing OCYatra Events

Apart from being a speaker, I also played an active role in coordinating and managing OCYatra events across all six cities. From venue logistics to speaker coordination, from audience engagement to brand promotion — it was a fulfilling, hands-on experience.

Managing events at this scale taught me a lot about collaboration, precision, and community building. Each city had its own rhythm and flavor, but the spirit of learning remained constant.

🤝 People, Community, and Conversations

One of the biggest takeaways from OCYatra 2025 was the people — old friends, new faces, curious learners, and inspiring thought leaders. It was refreshing to meet so many brilliant minds and exchange ideas on everything from Oracle Cloud, AI, APEX, Fusion Applications to performance tuning.

It was also a great opportunity to represent KNEX Technology, interact with current and future customers, and share how KNEX is enabling enterprises to unlock the power of Oracle solutions.

💡 My Final Thoughts

OCYatra 2025 was more than just a tech event. It was a celebration of community, innovation, and learning. I return from this journey inspired, humbled, and excited for what’s ahead.

Big thanks to the AIOUG team, all the speakers, volunteers, sponsors, and every single attendee who made this possible.

See you at the next stop in the journey! 🚀

Thanks & Regards,

Chandan Tanwani

🚀 From Oracle ACE Associate to Oracle ACE Pro – A Milestone in My Oracle Journey

I am thrilled to share that I have been recognized as an Oracle ACE Pro, advancing from my previous designation as an Oracle ACE Associate. This is not just a title change—it’s a recognition of continued passion, contribution, and commitment to the Oracle community.

🌟 What is the Oracle ACE Program?

The Oracle ACE Program honors individuals for their significant contributions to the Oracle ecosystem. Oracle ACEs are known for sharing deep technical knowledge, mentoring others, and building strong community engagement through blogs, sessions, forums, and real-world projects.

The ACE Pro level is awarded to seasoned professionals who have a consistent history of thought leadership, community contribution, and technical excellence.

🛤️ My Journey from ACE Associate to ACE Pro

My Oracle journey has been filled with curiosity, challenges, and constant learning. As an Oracle ACE Associate, I actively engaged in:

• Speaking at leading Oracle Conferences like AIOUG & INOAUG, 
• Writing technical blogs, articles, and whitepapers
• Supporting the Oracle community via forums, meetups, and workshops
• Mentoring young professionals and guiding database/cloud enthusiasts

I consistently explored newer Oracle technologies—especially Oracle Database 23ai, Autonomous Database, and OCI—while delivering practical insights through sessions, demos, and real-world implementations.

This promotion to ACE Pro is a recognition of that ongoing journey.

🙏 Gratitude

• I am truly grateful to Basheer Khan, Hadi Syed Abdul, and Gustavo Gonzalez for their unwavering support and encouragement throughout this journey. 💐
• A heartfelt thanks to Sai Penumuru for his guidance and continuous support in this journey. 💐 
• The Oracle ACE Program team for recognizing and supporting community contributors
• The Oracle user community—your questions, feedback, and engagement keep the fire of learning alive

Thanks & Regards,

Chandan Tanwani

Integrate Oracle APEX with OCI Identity and Access Management

Configure APEX with OCI Identity and Access Management. Here are simple steps.

First I will create a demo application i.e. Sales App in APEX and then will configure the IAM to integrated with APEX.

I am dividing this in two parts.

Part 1: Create application in APEX

Part 2: Integrate Oracle APEX with OCI IAM.

Part 1: Create application in APEX

1) Login to your APEX Application

2) Click on App Builder -> Click on create button.

3) Select option "Create App From a File".

4) Click on "Copy and Paste" tab. Then click on drop down button and select "Sales" application

5) Provide appropriate table name. I have given "SALESTB" in my example. And click on "Load Data" button below the screen.

6) It will load data. Once data loading completed click on "Create Application" button given below the screen.

7) Provide name of you application i.e. "SalesApp". Select Apperance style.

8) Select all the features of the application and click "Create Application" button.

Application creation will be done in 1-2 miniutes.

Once it is completed, you will see the screen as below.

Part 2: Integrate Oracle APEX with OCI IAM.

Once you install/create application in APEX. Now it's time to configure OCI IAM.

1) Navigate to "Identity & Security" -> Under Identity click on "Domains"

2) Here you will see the default domain created for you while you had provisioned the OCI Tenancy.

Click on "Default" domain.

3) Click on "Integrated Applications" and then -> "Add Application" button.

4) Select "Confidential Application" and click "Launch workflow" button. 

5) Provide appropriate application name. Here in my example it is "ApexSalesApp". 

Do not modify any other values and click "Next"

6) Skip the server configuration. For Client configuration click on "Configure this application as a client now" radio button.

Select the "Authorization code" check box and click "Next".

7) Provide Redirect URL and Post-logout redirect URL.

    Here is the syntax of the URLs.

    Redirect URL: https://<myadb>.eu-frankfurt-1.oraclecloudapps.com/ords/apex_authentication.callback

Post-logout redirect URL (optional): https://<myadb>.adb.eu-frankfurt-1.oraclecloudapps.com/ords/home

    In my case Redirect URL is

    https://geb397a43cf343c-cbtapexprod.adb.ap-mumbai-        1.oraclecloudapps.com/ords/apex_authentication.callback

    Post-logout redirect URL is https://geb397a43cf343c-cbtapexprod.adb.ap-mumbai-    1.oraclecloudapps.com/ords/r/cbtapex/salesapp/home

8) We will not configure web tier policy so select "Skip and do later".

    Click on "Finish" button below the screen.

9) After finishing this, you will see the "ApexSalesApp" detail page as below. Application is in INACTIVE state.

10) Click on "Edit application" button.

    Below the edit page, under "Authentication and authorization" click check box of "Enforce Grants as     Authorization" and then Save Changes button.

11) Now click on the "Activate" button 

    Click on Activate application button on confirmation screen

    In no time the application will be activated.

12) Please do note down the Client ID and Secrete code somewhere in your notepad or copy directly from this screen. This will be used to configure the credentials in APEX.

13) Now, come back to the APEX application i.e. "SalesApp", Navigate to "Shared Components"

14) Right hand side below, you will see the "Credential" link, click on that link.

15) Click on "Create" button

16) Now Provide below infomation,

    Name - OCI IAM Sales App Credentials

    Static ID - OCI_IAM_Sales_App_Credentials

    Authentication Type - Basic Authentication

    Client ID or Username- This is the same we collected in previous step of OCI IAM of Client ID.

    Client Secret or Password - This is the same we collected in previous step of OCI IAM of Client Secret.

    Verify Client Secret or Password - This is the same we collected in previous step of OCI IAM of     Client Secret.

    Click Create button to create the credentials.

    You can see the credentials are created.

17) Now go back to the OCI IAM - > ApexSalesApp details page.

      Click on the Users -> Add Users -> select the available Users from the list to whom you want to               grant access to the APEX application. Clicl Assign button.

    In my example I have given access to one user only.

18) Now, Go back to your APEX application -> Shared Components -> Under Security click on "Authentication Schemes"

19) Click on "Create" button to create new schemes.

20) Keep the default option (Based on a pre-configured scheme for the gallery) and click Next

21) Provide below parameter as shown in screenshot.

    Name - OCI IAM SalesApp Auth Scheme

    Scheme Type - Social Sign-In

    Credential Store - OCI IAM Sales App Credentials  -> This we created previously in create cedential     steps. Choose it from the drop down menu.

    Authentication Provider - OpenID Connect Provider

    Discovery URL - https://idcs-a947aa0b126a47fd84b34cc647be6e03.identity.oraclecloud.com:443/.well-known/openid-configuration/

    Scope - profile,email,groups

    Username - #sub# 

    Additional User Attributes - groups

    Click on "Create Authentication Scheme" button.

    Here discovery URL is nothing but your domain URL just append /.well-known/openid-configuration/     at the end of your domain URL.

    Where do you find the domain URL? 

    Go to the Domain - Overview section and you will find the domain URL as shown in below             screenshot.

 

22) Now "OCI IAM SalesApp Auth Scheme" is created. Click on the "OCI IAM SalesApp Auth Scheme" link to edit it.

    Click on "Post-Logout URL" and provide the Logout URL 

    The Post-Logout URL is in the form             https://<host_name>/ords/r/<alias_schema_name>/<app_name>/home and it is the IDCS (now IAM)         URL redirect after logging out (it is optional, however recommended).

    In my case https://geb397a43cf343c-cbtapexprod.adb.ap-mumbai-            1.oraclecloudapps.com/ords/r/cbtapex/salesapp/home is my logout URL which redirect to home page of     my application.

    Click on "Apply Changes" button.

23) Now Click again on the "OCI IAM SalesApp Auth Scheme" link to edit it.

      Make this Scheme as current scheme by clicking "Make Current Scheme" button.

    Click OK.

    Now you can see, "OCI IAM SalesApp Auth Scheme" is the current scheme for this application.

24) Now, go back to the Shared Component -> Under Security -> Click on "Security Attributes"

25) Provide input as follow,

    Under Authentication, 

        Authentication Scheme - OCI IAM SalesApp Auth Scheme

    Under Authorization

        Authorization Scheme - No application authorization required -

        Source for Role or Group Schemes - Custom Code

    Click "Apply Changes" button

    Now Final steps to Run the application and Test if it is working or not.

    Click Run Application button.

You will redirect to OCI Cloud Account form: insert your username and password (in my case is Oracle SSO):

    

    The Integrated Application access page shows up: on this page click Allow

    And you are Logged-In...

Thanks & Regards,

Chandan Tanwani