Connect to private instance in OCI using bastion service

By Gowthami | apps-dba.com | Oracle Cloud Infrastructure Series

Connecting to private OCI compute instances without a public IP is a common security requirement. OCI Bastion Service provides a fully managed, secure way to access private resources without requiring a jump server or VPN. This post walks through setting up and using OCI Bastion to connect to a private instance.

What You Will Learn: How to create an OCI Bastion, create a managed SSH session, and connect to a private compute instance using the OCI Bastion Service—no public IP or VPN required.

What Is OCI Bastion Service?

OCI Bastion is a managed service that provides restricted, time-limited SSH access to private resources in your VCN. Key features:

• No need for a public IP on the target instance
• Sessions are time-limited (max 3 hours by default)
• Access is logged and auditable via OCI Audit
• Supports SSH port forwarding for database connections

Prerequisites

• A private OCI compute instance (no public IP)
• OCI CLI installed and configured on your local machine
• An SSH key pair
• IAM policy allowing Bastion use

Step 1: Create the Bastion

1. Navigate to Identity & Security > Bastion in OCI Console
2. Click Create Bastion
3. Provide:
   • Name: e.g., demo-bastion
   • Target VCN and subnet (must be in same VCN as private instance)
   • CIDR allowlist: your local IP (e.g., 203.0.113.0/32)
4. Click Create Bastion and wait for Active state

Step 2: Create a Managed SSH Session

1. Select your Bastion and click Create Session
2. Session type: Managed SSH session
3. Enter:
   • Username: opc (Oracle Linux) or ubuntu
   • Compute instance: select your private instance
   • SSH public key: paste your public key content
   • Session TTL: up to 10800 seconds (3 hours)
4. Click Create Session

Step 3: Connect Using the SSH Command

Once the session is Active, OCI provides a ready-to-use SSH command. Copy it from the session details. It looks like:

ssh -i <private_key_file>   -o ProxyCommand='ssh -i <private_key_file> -W %h:%p -p 22 ocid1.bastionsession.oc1...@host.bastion.<region>.oci.oraclecloud.com'   -p 22 opc@<private_instance_ip>

Paste and run this command in your local terminal.

Step 4: Port Forwarding for Database Access

To connect to a private Oracle Database (port 1521) through Bastion:

1. Create a Port Forwarding Session instead of Managed SSH
2. Target: private instance IP, port 1521
3. Use the provided SSH command with local port forwarding:

ssh -i <private_key>   -N -L 1521:<DB_PRIVATE_IP>:1521   -p 22 ocid1.bastionsession...@host.bastion.<region>.oci.oraclecloud.com

Then connect SQL*Plus to localhost:1521 as if it were local.

IAM Policy Required

Allow group DBAs to manage bastion-family in compartment <compartment_name>
Allow group DBAs to manage bastion-session in compartment <compartment_name>

Master Oracle Exadata

This post is part of our Oracle Cloud Infrastructure series. Get our comprehensive Exadata guide with OCI deployment patterns, security best practices, and performance tuning techniques.

Get the Exadata PDF Guide

Modify AWR interval and retention periods in 12C

By Gowthami | apps-dba.com | Oracle Database Administration Series

AWR (Automatic Workload Repository) is Oracle's built-in performance data collection framework. By default, AWR snapshots are taken every 60 minutes and retained for 8 days. For detailed performance analysis or compliance requirements, DBAs often need to adjust these settings. This post covers how to modify AWR snapshot interval and retention in Oracle 12c.

What You Will Learn: How to view current AWR settings, modify the snapshot interval and retention period using DBMS_WORKLOAD_REPOSITORY, and verify the changes in Oracle Database 12c.

Default AWR Settings

Parameter	Default Value	Description
Snapshot Interval	60 minutes	How often AWR takes a snapshot
Retention Period	8 days (11520 minutes)	How long snapshots are kept
Top SQL	Top 30	Number of top SQL statements captured

Check Current AWR Settings

Query the DBA_HIST_WR_CONTROL view to see current configuration:

SQL> SELECT dbid,
       snap_interval,
       retention,
       topnsql
FROM dba_hist_wr_control;

DBID        SNAP_INTERVAL      RETENTION           TOPNSQL
----------- ---------------    ----------------    -------
1234567890  +00000 01:00:00.0  +00008 00:00:00.0   DEFAULT

Modify AWR Interval and Retention

Use the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure. All time values are in minutes.

Syntax:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention  => <retention_in_minutes>,
    interval   => <interval_in_minutes>,
    topnsql    => <number_of_top_sql>,
    dbid       => <dbid>
  );
END;
/

Example 1: Change Interval to 30 Minutes, Retain 14 Days

BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention => 20160,  -- 14 days (14 * 24 * 60)
    interval  => 30      -- 30 minutes
  );
END;
/

Example 2: Disable AWR Snapshots

Setting interval to 0 disables automatic snapshot collection:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    interval => 0
  );
END;
/

Example 3: Re-enable with Custom Settings

BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention => 43200,  -- 30 days
    interval  => 60      -- 60 minutes (default)
  );
END;
/

Verify the Changes

SQL> SELECT snap_interval, retention
FROM dba_hist_wr_control;

SNAP_INTERVAL        RETENTION
-------------------  -------------------
+00000 00:30:00.0    +00014 00:00:00.0

AWR Retention Quick Reference

Retention Period	Minutes Value
7 days (default)	10080
14 days	20160
30 days	43200
60 days	86400
90 days	129600

Important Considerations

• Increasing retention increases SYSAUX tablespace usage—monitor space carefully
• Shorter intervals (e.g., 15 minutes) are useful during performance troubleshooting but increase storage usage
• AWR is a licensed feature—requires Oracle Diagnostics Pack
• In a CDB (12c+), AWR settings apply per PDB when PDB-level AWR is enabled

Master Oracle Exadata

This post is part of our Oracle Database Administration series. Get our comprehensive Exadata guide with AWR analysis techniques, performance tuning, and real-world case studies.

Get the Exadata PDF Guide

Create New OS User, Enable SSH and Generate Key Pair in OCI Compute Instance

=> Here I am creating a user called xxtomcat under new group xxtomcat

Note: Below commands to be run as root user. Login as opc and switch to root

[opc@oci-compute-host-001 ~]# sudo su - root

[root@oci-compute-host-001 ~]# groupadd xxtomcat

[root@oci-compute-host-001 ~]# useradd -g xxtomcat -d /home/xxtomcat xxtomcat

[root@oci-compute-host-001 ~]# id xxtomcat

uid=54324(xxtomcat) gid=54332(xxtomcat) groups=54332(xxtomcat)

[root@oci-compute-host-001 ~]#

=> Create .ssh directory under $HOME directory and give 0700 permissions. 

Note 1 : Below commands should be run as xxtomcat user

Note 2 : These permissions are required for Public and Private Key authentication to work.

[xxtomcat@oci-compute-host-001 ~]$ mkdir .ssh

[xxtomcat@oci-compute-host-001 ~]$ chmod 0700 .ssh

[xxtomcat@oci-compute-host-001 ~]$ cd .ssh

=> Create authorized_keys under .ssh directory and give 0600 permissions

Note 1 : Below commands should be run as xxtomcat user

Note 2 : These permissions are required for Public and Private Key authentication to work.

[xxtomcat@oci-compute-host-001 .ssh]$ touch authorized_keys

[xxtomcat@oci-compute-host-001 .ssh]$ chmod 0600 authorized_keys

[xxtomcat@oci-compute-host-001 .ssh]$ ls -ltr

total 4

-rw------- 1 xxtomcat xxtomcat 398 Apr 12 12:28 authorized_keys

[xxtomcat@oci-compute-host-001 .ssh]$

=> Disable Password Expiry for xxtomcat user

[root@oci-compute-host-001 ~]# chage -m 0 -M 99999 -I -1 -E -1 xxtomcat

[root@oci-compute-host-001 ~]# chage -l xxtomcat

Last password change                                    : Apr 28, 2022

Password expires                                        : never

Password inactive                                       : never

Account expires                                         : never

Minimum number of days between password change          : 0

Maximum number of days between password change          : 99999

Number of days of warning before password expires       : 7

[root@oci-compute-host-001 ~]#

=> Enable SSH for the user

Note: Below commands to be run as root user. Login as opc and switch to root

Add the entry AllowUsers xxtomcat at the end of /etc/ssh/sshd_config file

[root@oci-compute-host-001 ~]# grep -i AllowUsers /etc/ssh/sshd_config

AllowUsers xxtomcat

[root@oci-compute-host-001 ~]#

=> Restart sshd service

[root@oci-compute-host-001 ~]# /sbin/service sshd restart

=> To login the server from Putty directly as xxtomcat user, you have to generate Public and Private Key Pair using Putty Key Gen Tool

Make sure below 3 default values are selected in PuttyGen Tool

=> Click on Generate

Generate some randomness by hovering mouse on the blank area

 

=> Copy the public key content and paste it in the authorized_keys file in the server as xxtomcat user $HOME/.ssh/authorized_keys

Note: Do not click on save public key, as this will save in putty supported format

=> Click on Save private key to save the private key in Putty supported format .ppk

=> Login to the server through putty as xxtomcat user by providing private key file 

=> Enter the server IP address

=> Select the Private Key : SSH => Auth => Private Key

=> Login as xxtomcat user