Monthly Archives: January 2024

Oracle DB Link setup

I have tested DB Link functionality setup locally with 2 Oracle on 2 local VM Servers.

DB Link is a functionality that one Oracle server can access tables / resource on another server as a local tables, which is particular useful when you want to keep multiple updates in a transaction.

DB Link is for.
1. Move small amount of data
2. Enjoy transaction garantee (with commit / rollback)

DB Link is NOT for.
1. Migrate huge amount of data
2. Database synchronisation (Use Golden Gate instead)

I have setup the topology as followed, both Oracle 1 and Oracle 2 are PDB in my home lab.

Oracle 2 is connected to Oracle 1 via a DB Link.

User can connect to Oracle 2 and operate on Oracle 1 as “Jimmy” on Oracle 1, including the DB Objects owned by “Jimmy” or owned by another owner (“Jimmy1”) via user role.

Operation involving Oracle 1 DB objects on Oracle 2 are within transaction boundary. It has transaction garantee but slow down DB operations as a drawback.

Next, we will look at the setup of the Topology.

On Oracle 1.
1. Setup 2 normal DB users (Jimmy and Jimmy1), which has RESOURCE, CTXAPP, CONNECT privilege.

2. Create HELLO_WORLD table under Jimmy schema, Create ANOTHER_TABLE table under Jimmy1 schema

3. Create a new role TEST_ROLE by SYS, which has a CREATE ROLE privilege .

4. As Jimmy1, grant “grant select, insert, update, delete on ANOTHER_TABLE to TEST_ROLE”

5. Assign Jimmy (NOT Jimmy1) to TEST_ROLE role.

6. Login as Jimmy, verify it can access Jimmy1.ANOTHER_TABLE.

On Oracle 2.
1. Setup 1 DB User (Jimmy), grant RESOURCE, CTXAPP, CONNECT privilege. Also, grant “CREATE DATABASE LINK” privilege too.

2. Create the database link (oracle1) with the following command on the DB User, is IP Address of Oracle 1

CONNECT TO jimmy IDENTIFIED BY password_of_jimmy_on_oracle1

3. Verify Jimmy on Oracle 2 can access HELLO_WORLD table and ANOTHER_TABLE with the following commands


The primary challenge on setting up the database link is the authentication between Oracle 1 and Oracle 2. Basically Oracle 2 acts like a client of Oracle 1, however, the exact mechanism depends on the database setup. It can be as simple as username and password, it can be Kerberos with forwarding credential, it can also be Oracle Wallet on Autonomus Transaction Database.

Setting up Oracle XE 21c on Oracle Linux on Hyper-V

I have experimented the setup of Oracle XE 21c on Hyper-V for testing. It is interesting that Redhat has changed their licensing agreement which most RHEL variants (Oracle Linux, CentOS, Scientific Linux and etc) are not longer automatically binary compatible with RHEL. Most Variants version 8 is still binary compatible while version 9 is completely independent distribution.

I need the Oracle only for learning and testing, therefore, focusing on a well known working method.

1. Create a External Switch on Hyper-V, so that we can access the installed Oracle as a server on the same network

2. Create a VM, with minimal of 2 CPU Core, 8GB of Ram, disable Secure Boot and Use External Switch as the network switch

3. Download and Install Oracle Linux 8 on the VM, we must use Oracle Linux 8.x (9.x is not supported)
Download Link:

4. After Installation, I configure the following items.
a. Allow Port 1521 and 5500 access for Firewall config, 1521 is used by Oracle Listener, while 5500 is used by Enterprise Manager.
b. Configure the LAN eth0 to be enabled automatically.

5. Download the Oracle XE 21c RPM file, follow the instructions for installation.
Download Link:

6. Once installed, configure the password for SYS, SYSDBA and PDBADMIN by running “/etc/init.d/oracle-xe-21c configure”

7. The default installation of Oracle XE is a CDB and PDB installation. There are a few links we can use.
a. Enterprise Manager: https://ip-address:5500/em
b. CDB Service Name: XE
c. PDB Service Name: XEPDB1

8. We can connect to the DB with the following configuration