Host your own WordPress Website for Free on Oracle Cloud Infrastructure

The Oracle documentation can be accessed with the following link:

https://www.oracle.com/cloud/free-1/?source=v1-TECH-JumpNav-j2204-20241205

Create a free OCI account.

Need credit card details, but won’t be charged (unless chargeable features are used). Main challenge is to ensure that you do not use chargeable features.

Create a VM with following specifications:

Shape configuration

Shape:VM.Standard.A1.Flex

OCPU count:2

Network bandwidth (Gbps):2

Memory (GB):12

Local disk:Block storage only

Image details

Operating system:Oracle Linux

Version:8

Image:Oracle-Linux-8.9-aarch64-2024.05.29-0

Image depends on latest available. I used the above.

Switch to a Pay As You Go account. Pay only for services that exceed the monthly free amounts from Always Free Services.

Make sure only “always free” services are used.

(To be continued…)

Oracle – Using ASH to Monitor Temporary Tablespace Usage

Using ASH to Monitor Temporary Tablespace Usage

To use ASH (Active Session History) to monitor temporary tablespace usage use SQL similar to the query below. Detect the space used by queries.

SELECT ASH.inst_id,
  ASH.user_id,
  ASH.session_id sid,
  ASH.session_serial# serial#,
  ASH.sql_id,
  ASH.sql_exec_id,
  ASH.sql_opname,
  ASH.module,
  MIN(sample_time) sql_start_time,
  MAX(sample_time) sql_end_time,
  ((CAST(MAX(sample_time) AS DATE)) - (CAST(MIN(sample_time) AS DATE))) * (3600*24) etime_secs ,
  ((CAST(MAX(sample_time) AS DATE)) - (CAST(MIN(sample_time) AS DATE))) * (60*24) etime_mins ,
  MAX(temp_space_allocated)/(1024*1024) max_temp_mb
FROM gv$active_session_history ASH
WHERE ASH.session_type = 'FOREGROUND'
AND ASH.sql_id        IS NOT NULL
--AND sample_time BETWEEN to_timestamp('01-04-2019 10:00', 'DD-MM-YYYY HH24:MI') AND to_timestamp('13-04-2019 12:00', 'DD-MM-YYYY HH24:MI')
AND sample_time > systimestamp - 3
GROUP BY ASH.inst_id,
  ASH.user_id,
  ASH.session_id,
  ASH.session_serial#,
  ASH.sql_id,
  ASH.sql_opname,
  ASH.sql_exec_id,
  ASH.module
HAVING MAX(temp_space_allocated) > 10000
order by 9 desc

Oracle – RMAN commands

delete archivelog all backed up 1 times to sbt_tape;
delete archivelog all completed before 'sysdate -1';
delete noprompt archivelog all;
....until time "to_date('2018-03-13 14:00:07','YYYY-MM-DD HH24:MI:SS')";

Oracle – Multitenant – Create a sysdba Common User

Oracle – Multitenant – Create a sysdba Common User

😎 To create a sysdba user that can administrate all containers:

sys@ORCL> show con_id

CON_ID
------------------------------
1
sys@ORCL> show con_name

CON_NAME
------------------------------
CDB$ROOT
 
sys@ORCL> create user c##username identified by <password>;

User created.

sys@ORCL> grant create session to c##username;

Grant succeeded.

sys@ORCL> grant sysdba to c##username container = ALL;

Grant succeeded.

If it is necessary to change the common user prefix (or get rid of it!), the value of _common_user_prefix_common_user_prefix can be changed:

sys@ORCL> select rpad(ksppinm,40) ||' ---->  '||ksppstvl "param, value" from x$ksppi a,x$ksppsv b
  2  where a.indx=b.indx and substr(ksppinm,1,1) = '_' and ksppinm like '%common%';

param, value
_ksipc_common_sl                         ---->
_eliminate_common_subexpr                ---->  TRUE
_dump_common_subexpressions              ---->  FALSE
_common_data_view_enabled                ---->  TRUE
_common_user_prefix                      ---->  C##

Oracle VM – Collect diagnostic info

Collect diagnostic info for Oracle Virtual Machine Manager and Servers

If you are experiencing issues with OVM, and an SR is opened, Oracle support will request diagnostic information. The shell session below shows collection of this information for OVM 3.4.x

[root@ovm-manager support]# ./vmpinfo3.sh --help
Usage:
    vmpinfo3.sh --username=ADMIN_USER [listservers | servers=SERVERS]
    vmpinfo3.sh --wallet=WALLET [listservers | servers=SERVERS]

    Collect info from all owned servers:
        vmpinfo3.sh --username=admin
    List owned servers:
        vmpinfo3.sh --username=admin listservers
    Collect info from specific server(s):
        vmpinfo3.sh --username=admin servers=server3
        vmpinfo3.sh --username=admin servers=server1,server5,server9

On PCA the --wallet option retrieves the credential from the PCA wallet

Data collected is aggregated in your system temporary directory.
If there is not enough free space in this filesystem you can override
the location by setting $TMPDIR -- see mktemp(1).

# cd /u01/app/oracle/ovm-manager-3/ovm_tools/support/
[root@ovm-manager support]# ./vmpinfo3.sh --username=admin
Enter OVM Manager Password:

Starting data collection
Gathering files from all servers. This process may take some time.
Gathering OVM Model Dump files
Gathering sosreports from servers
The following server(s) will get info collected: [ovs1]
Gathering sosreport from ovs1
Data collection complete
Gathering OVM Manager Logs
Clean up metrics
Copying model files
Copying DB backup log files
Running lightweight sosreport
Archiving vmpinfo3-20181211-080334
=======================================================================================
 Please send /tmp/vmpinfo3-3.4.5.1919-20181211-080334.tar.gz to Oracle support
=======================================================================================

Oracle – Identify Locked Objects

Identify Locked Database Objects – Oracle

select b.owner, b.object_name, a.oracle_username, a.os_user_name  
from gv$locked_object a, all_objects b
where a.object_id = b.object_id


select * from gv$locked_object

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   gv$locked_object a ,
   gv$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id
and a.inst_id = b.inst_id

SELECT Decode(request, 0, 'Holder: ',
  'Waiter: ')
  ||vl.sid sess,
  status,id1,id2,lmode,request,vl.TYPE
FROM v$lock vl,v$session vs
WHERE ( id1, id2, vl.TYPE ) IN 
(SELECT id1, id2, TYPE FROM v$lock
  WHERE request > 0)
  AND vl.sid = vs.sid
ORDER BY id1, request

Oracle – 18c XE Flashback Pluggable Database Session

18c XE Flashback Pluggable Database Session

SQL> sho parameters reco

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
remote_recovery_file_dest            string
SQL> host df -h
Filesystem           Size  Used Avail Use% Mounted on
devtmpfs             4.8G     0  4.8G   0% /dev
tmpfs                4.8G     0  4.8G   0% /dev/shm
tmpfs                4.8G   26M  4.8G   1% /run
tmpfs                4.8G     0  4.8G   0% /sys/fs/cgroup
/dev/mapper/ol-root   45G   24G   21G  54% /
/dev/xvda1          1014M  399M  616M  40% /boot
tmpfs                974M   12K  974M   1% /run/user/42
tmpfs                974M     0  974M   0% /run/user/0
tmpfs                974M     0  974M   0% /run/user/54321

SQL> sho parameters pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /opt/oracle/product/18c/dbhome
                                                 XE/dbs/spfileXE.ora

SQL> alter system set db_recovery_file_dest_size=20G scope=BOTH;

System altered.

SQL> alter system set db_recovery_file_dest='/opt/oracle/oradata/XE/FRA' scope=BOTH;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1610612704 bytes
Fixed Size                  8896480 bytes
Variable Size             520093696 bytes
Database Buffers         1073741824 bytes
Redo Buffers                7880704 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.

SQL> set lines 170
SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                             0                         0               0          0
BACKUP PIECE                             0                         0               0          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                         1.95                         0               2          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.

SQL> SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
YES

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                             0                         0               0          0
BACKUP PIECE                             0                         0               0          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                         1.95                         0               2          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.

SQL> SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
YES

SQL> alter session set container=xepdb1;

Session altered.

SQL> create restore point PDB1_GRP1 guarantee flashback database;

Restore point created.

SQL> create table tw (id number);

Table created.

SQL> insert into tw values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> shutdown immediate
Pluggable Database closed.
SQL> flashback pluggable database xepdb1 to restore point PDB1_GRP1;

Flashback complete.

SQL> show con_name

CON_NAME
------------------------------
XEPDB1

SQL> startup nomount

-- we are in the xepdb1 container

SQL> select name from v$pdbs;

NAME
--------------------------------------------------------
XEPDB1


SQL> alter pluggable database open resetlogs;

Pluggable database altered.

SQL> select * from dual;

D
-
X

SQL> desc tw
ERROR:
ORA-04043: object tw does not exist



SQL> select name from v$pdbs;

NAME
----------------------------------------------------------------
XEPDB1


SQL> ALTER SESSION SET container = cdb$root;

Session altered.

SQL> select name from v$pdbs;

NAME
---------------------------------------------------------------
PDB$SEED
XEPDB1
PDB2

Oracle – RMAN backups

select object_name from dba_objects where object_name like '%RMAN%'

select * from gv$backup_set order by completion_time desc

select * from v$rman_backup_job_details order by start_time desc


select start_time, end_time, input_bytes,output_bytes, status, input_type,elapsed_seconds,
input_bytes_display from v$rman_backup_job_details order by start_time desc

Oracle – Install Clusterware – Test udp multicast on Private Interconnect (and get maximum MTU)

Test Multicast on Private Interconnect (and get maximum MTU)

There are several ways to test multicast communication using udp. I was debugging an oracle Clusterware installation – the clusterware on the second node would not completely start (specifically ocssd and crsd would not start on node2).

All standard/published Oracle tests passed. See Oracle – Clusterware Installation and Configuration

Without going into too much detail, the Clusterware daemons would not start on the second node because the MTU on some of the switches in the network was not configured correctly.

The two java programs below can be used to test whether multicast is working on the network between two servers. More specifically, they can be used to determine the maximum MTU that can be used between the two servers, taking into consideration all network elements (NICs, switches, routers).

Switches/routers may be configured to support Jumbo frames. MTU 9000 (maybe set to 9150 on switch/routers)

The default MTU on switches may be 1400!

Normally, the NICs on the Linux server are set to 1500 MTU.

Private interconnect NICs/bond can be set to 9000 if all intermediate network elements support and are configured for Jumbo frames

MultiCastTestReceiveLoop.java

This program is used to receive udp datagrams on a socket (max. 9150 – covers Jumbo frames).

The communication follows a similar path as that used between Oracle Clusterware processes.

To compile: /jdk/bin/javac MultiCastTestReceiveLoop.java

To execute: /jdk/bin/java -classpath . MultiCastTestReceiveLoop 230.0.1.0 42424

import sun.net.*;
import java.net.*;

public class MultiCastTestReceiveLoop {
    public MultiCastTestReceiveLoop() {
        MultiCastTestReceiveLoop MultiCastTestReceiveLoop = new MultiCastTestReceiveLoop(42424,"230.0.1.0");
    }
    
    public MultiCastTestReceiveLoop(int port,String group) {

	try {
	while (true) { 
	
        // Create the socket and bind it to port 'port'.
        MulticastSocket s = new MulticastSocket(port);

        // join the multicast group
	s.setNetworkInterface(NetworkInterface.getByName("<interconnect interface>"));
        s.joinGroup(InetAddress.getByName(group));
 
        byte buf[] = new byte[9500];
        DatagramPacket pack = new DatagramPacket(buf, buf.length);


	s.receive(pack);
	System.out.println("Received data from: " + pack.getAddress().toString() + ":" + pack.getPort() + " with length: " + pack.getLength());
	System.out.write(pack.getData(),0,pack.getLength());
	System.out.println();

        s.leaveGroup(InetAddress.getByName(group));
        s.close();
	} // end while true

        }


        // Adding just a catch-all 
        catch (Exception e)
        {
           e.printStackTrace();
        }

    }

    public static void main(String[] args) {
        if (args.length == 2) { 
        MultiCastTestReceiveLoop MultiCastTestReceiveLoop = new MultiCastTestReceiveLoop(Integer.parseInt(args[1]),args[0]);
        }
        else {
            MultiCastTestReceiveLoop MultiCastTestReceiveLoop = new MultiCastTestReceiveLoop();
        }
    }
}

MultiCastTestSend.java

This program is used to send udp datagrams on a socket. The buffer is filled with the letter ‘a’ (length set with the nbytes argument on the command line

The communication follows a similar path as that used between Oracle Clusterware processes.

To compile: /jdk/bin/javac MultiCastTestSend.java

To execute: /jdk/bin/java -classpath . MultiCastTestSend 230.0.1.0 42424

import sun.net.*;
import java.net.*;

public class MultiCastTestSend {
    public MultiCastTestSend() {
        MultiCastTestSend multiCastTestSend = new MultiCastTestSend(42424,"230.0.1.0",10);
    }

    public MultiCastTestSend(int port,String group,int nbytes) {

        int ttl = 1;
        try {
        // Create the socket
        MulticastSocket s = new MulticastSocket();

	// set LAN interface to send on
	s.setNetworkInterface(NetworkInterface.getByName("<interconnect interface>"));

        // Fill the buffer with some data
        byte buf[] = new byte[nbytes];
        for (int i=0; i<buf.length; i++) buf[i] = (byte)i;
        // Create a DatagramPacket 
        DatagramPacket pack = new DatagramPacket(buf, buf.length,
                                                 InetAddress.getByName(group), port);

        s.setTimeToLive(ttl);
        s.send(pack);
        // And when we have finished sending data close the socket
        System.out.println("Sent bytes to " + pack.getAddress().toString());
        s.close();
          }
        catch (Exception e)
        {
        e.printStackTrace();
        }


    }

    public static void main(String[] args) {
        if (args.length == 3) { 
        MultiCastTestSend multiCastTestSend = new MultiCastTestSend(Integer.parseInt(args[1]),args[0],Integer.parseInt(args[2]));
        }
        else {
            MultiCastTestSend multiCastTestSend = new MultiCastTestSend();
        }
    }
}