DBA Blogs

 I've just published a short video on the new JOIN TO ONE clause in SELECT statements in Oracle 26ai 26.2

This clause allows you to let the database automatically determine JOIN columns based on Primary Key and Foreign Key relationships configured in the database.  JOIN TO ONE defaults to doing a LEFT OUTER JOIN so I also demonstrate how to use it for INNER JOINs

 In today's video I have demonstrated how Power BI can use DirectQuery to query an Oracle database and refresh reports without actually storing the data in the Power BI file (as would be done if "Import" was used instead of DirectQuery).

I have used SQL Tracing in the Database Instance to identify the SQL statement that Power BI executes

For the first visual in Power BI which shows total salary by Department, the Power BI module and SQL statement are identified as :

MODULE NAME:(msmdsrv.exe) 
CLIENT DRIVER:(ODPM.NET : 23.6.0.0.0)

sqlid='c8a9qd2dzks8h'

SELECT * FROM (
SELECT
*
FROM 
(

SELECT
"t1"."DEPARTMENT_NAME" "c6", SUM ( "t4"."SALARY" )
 "a0"
FROM 
((
select "$Table"."EMPLOYEE_ID" as "EMPLOYEE_ID",
    "$Table"."FIRST_NAME" as "FIRST_NAME",
    "$Table"."LAST_NAME" as "LAST_NAME",
    "$Table"."EMAIL" as "EMAIL",
    "$Table"."PHONE_NUMBER" as "PHONE_NUMBER",
    "$Table"."HIRE_DATE" as "HIRE_DATE",
    "$Table"."JOB_ID" as "JOB_ID",
    "$Table"."SALARY" as "SALARY",
    "$Table"."COMMISSION_PCT" as "COMMISSION_PCT",
    "$Table"."MANAGER_ID" as "MANAGER_ID",
    "$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID"
from "HR"."EMPLOYEES" "$Table"
) "t4"

 LEFT OUTER JOIN 

(
select "$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID",
    "$Table"."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
    "$Table"."MANAGER_ID" as "MANAGER_ID",
    "$Table"."LOCATION_ID" as "LOCATION_ID"
from "HR"."DEPARTMENTS" "$Table"
) "t1" on 
(
"t4"."DEPARTMENT_ID" = "t1"."DEPARTMENT_ID"
)
)

GROUP BY "t1"."DEPARTMENT_NAME"
)
 "MainTable"
WHERE 
(

NOT(
(
"a0" IS NULL 
)
)

)

ORDER BY "a0"
DESC
,"c6"
ASC
 ) WHERE ROWNUM (lessthan) 1001

For the second visual in Power BI which shows count of employees in each Department, the Power BI module and SQL statement are identified as :

MODULE NAME:(msmdsrv.exe)
CLIENT DRIVER:(ODPM.NET : 23.6.0.0.0)

sqlid='dh7nbfqsy942q'

SELECT
"t1"."DEPARTMENT_NAME" "c6",
COUNT("t4"."EMPLOYEE_ID")
 "a0"
FROM 
((
select "$Table"."EMPLOYEE_ID" as "EMPLOYEE_ID",
    "$Table"."FIRST_NAME" as "FIRST_NAME",
    "$Table"."LAST_NAME" as "LAST_NAME",
    "$Table"."EMAIL" as "EMAIL",
    "$Table"."PHONE_NUMBER" as "PHONE_NUMBER",
    "$Table"."HIRE_DATE" as "HIRE_DATE",
    "$Table"."JOB_ID" as "JOB_ID",
    "$Table"."SALARY" as "SALARY",
    "$Table"."COMMISSION_PCT" as "COMMISSION_PCT",
    "$Table"."MANAGER_ID" as "MANAGER_ID",
    "$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID"
from "HR"."EMPLOYEES" "$Table"
) "t4"

 LEFT OUTER JOIN 

(
select "$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID",
    "$Table"."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
    "$Table"."MANAGER_ID" as "MANAGER_ID",
    "$Table"."LOCATION_ID" as "LOCATION_ID"
from "HR"."DEPARTMENTS" "$Table"
) "t1" on 
(
"t4"."DEPARTMENT_ID" = "t1"."DEPARTMENT_ID"
)
)

GROUP BY "t1"."DEPARTMENT_NAME" 

Thus, every refresh runs a number of queries -- some to synchronise the schema from Oracle to Power BI and others to refresh the numbers to present in the Visuals. This proves that the actual load of computing the GROUP BY and aggregations is in the *database instance* (because that is where the data actually resides) and not in the Power BI file (because no data is copied into the Power BI file)

A simple reminder about how JSON PL/SQL methods deal with JSON values, it easy to get confused when you mix up JSON objects and their serialized counterparts, especially if some if these parts are coming from JSON SQL functions and you need to combine them with other parts generated by PL/SQL.

The code below should clarify the difference between a "real" JSON value and its textual representation, especially when you are assembling a JSON object with values containing other JSON objects or their serialized representation.

When you PUT a serialized JSON string into a new JSON, the method escapes all the special characters that otherwise would break the syntax (lines 10-16).
In order to reconstruct a valid JSON string, something that you can PARSE as JSON, you need to retrieve the value with GET_STRING or GET_CLOB if it is large (lines 18-20).

If you need to include a serialized JSON object into a new JSON object thus avoiding the automatic escaping, then you need to convert the serialized JSON string into a proper JSON object and then PUT it inside the new object (lines 26-29). 

declare
    c  varchar2(255) := '{"key": 1, "value": "X"}';
    d  varchar2(255);
    j  json_object_t;
    j1 json_object_t;
    j2 json_object_t;
begin
    
    if c is json then
        dbms_output.put_line('c is a string containing a valid JSON');
        j := json_object_t.parse(c);

        j1 := new json_object_t;
        j1.put('document', c);
        dbms_output.put_line('c is now escaped and becomes a string literal value');
        dbms_output.put_line(j1.to_string());
        dbms_output.new_line;
        d := j1.get_string('document');
        dbms_output.put_line('c is converted back into the original JSON object string');
        dbms_output.put_line(d);


        dbms_output.new_line;
        dbms_output.new_line;

        j2 := new json_object_t;
        j2.put('document', j);
        dbms_output.put_line('c is still a json object value, now embedded in a new JSON object');
        dbms_output.put_line(j2.to_string());
    else
        dbms_output.put_line('NOT JSON');
    end if;
end;
/

Watch out for NULL values because GET_STRING and GET_CLOB show two different behaviors in older releases of Oracle 19c.

 

Hello All, I encounter an error trying to use a SQL MACRO (TABLE) in freesql.com, in both versions 26ai and 23ai. The function top_n compiles successfully, but trying to call it fails: <code>create or replace function top_n (p_table DBMS_TF.TABLE_T, p_rows NUMBER) return varchar2 SQL_Macro is l_sql varchar2(200) := 'select * from top_n.p_table fetch first top_n.p_rows rows only'; begin dbms_output.put_line('sql='||l_sql); return l_sql; end; / Function TOP_N compiled -- test in SQL select * from top_n (scott.dept, 3) / ORA-00942: table or view "TOP_N"."P_TABLE" does not exist</code> The same example works ok in 19c <code>SQL> select * from top_n (scott.dept, 3) DEPTNO DNAME LOC ------ ------------ ---------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO sql=select * from top_n.p_table fetch first top_n.p_rows rows only 3 rows selected. </code> It looks like this is a database bug, as I also encounter the same problem in a different 26ai environment. Thanks a lot in advance & Best Regards, Iudith Mentzel

I am running Oracle Database 19c (RU 19.22) in a 4-node RAC extended cluster. During a recent incident, we experienced ORA-32701 ?Possible hangs detected? with Hang IDs (915, 916, 917). The Hang Manager detected a global hang situation where instance 1 was identified as the final blocker (CKPT process / DBRM session), and Oracle eventually evicted the instance due to a ?LOCAL, HIGH confidence hang?. We observed that _hang_resolution_scope is set to INSTANCE in our environment, along with _hang_detection_enabled = TRUE and _hang_resolution_policy = HIGH. My questions are: What is the recommended best practice for _hang_resolution_scope in Oracle 19c RAC 4-node clusters (especially extended RAC)? Should it remain PROCESS in production environments? Under what conditions does Oracle recommend switching to INSTANCE scope, and what are the risks (e.g. cascading instance evictions)? In global hang scenarios (ORA-32701), how does Hang Manager decide between process termination vs instance eviction in modern 19c releases? Are there known best practices to avoid false positives or unnecessary instance evictions in large RAC clusters under heavy batch workloads (PL/SQL, DBMS_SCHEDULER, materialized view refresh)? Any guidance or references to official Oracle best practices would be appreciated. more info for incident trace file for instance 1 *** 2026-06-01T11:36:12.118125+02:00 HM: Hang Statistics - only statistics with non-zero values are listed current number of local active sessions 78 current number of local hung sessions 38 instance health (in terms of hung sessions) 51.29% number of cluster-wide active sessions 224 number of cluster-wide hung sessions 104 cluster health (in terms of hung sessions) 53.58% -------------------------------------- 2297510041,1,0,2297510041,65128162,"06-01-2026 11:36:43.948768000",2502,36203,2,0,"",0,0,0,"",0,0,0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,33792,3,0,0,30070,0,4294967291,0,1,4294967295,0,0,0,0,0,,0,0,165959219,"oracle@ffcrac41.francefrais.local (CKPT)","","","","ffcrac41",0,"" -------------------------------------- log file for instace 3 DIA0 Critical Database Process As Root: Hang ID 915 blocks 7 sessions Final blocker is session ID 2502 serial# 36203 OSPID 32174 on Instance 1 If resolvable, instance eviction will be attempted by Hang Manager

Hi, this is a general question. I tried to explain a very long a 80Kb of aggregation and pivot query and I get an error "ORA-04031: unable to allocate 40 bytes of shared memory". Particularly when I try to add a parallel hint. Errors in file /tools/list/oracle/product/diag/rdbms/bnkppd/bnkppd/trace/bnkppd_ora_5178102.trc (incident=4708985): ORA-04031: unable to allocate 80 bytes of shared memory ("shared pool","explain plan set statement_i...","qmxqalgDiagDrv","qmxqalgDiag_newElem:qmxqalgDiagStackElem") The bad thing is that the full database hangs when this happened (on a test database). I think that I read somewhere that we can alter the size of the shared pool to accept bigger queries. Can you tell me which parameter I could change for this (even an hidden parameter is OK). Thank you.

Does TLS 1.3 supported in the current OEM 24ai version

DBMS_SCHEDULER has several procedures that operate in bulk on various scheduler objects, such as DBMS_SCHEDULER.STOP_JOB, DBMS_SCHEDULER.DISABLE, etc, and offer a `commit_semantics` parameters. When set to 'ABSORB_ERRORS', it will not rollback when it encounters an error with some of the objects, but will instead report them to `SCHEDULER_BATCH_ERRORS'. May I please request a full example of how to process these errors? I am unable to find one online. A few preliminary questions: Does `SCHEDULER_BATCH_ERRORS` show only the errors from the last call within your specific session? This table doesn't have any kind of session identifiers or call identifiers, so I would assume this is true; however it doesn't explicitly say that in the documentation. If this is not true, how do we disambiguate? The documentation for `ORA-27362` say that the errors can be found in `SCHEDULER_JOB_ERRORS`. As far as I can tell this is not a real view. I assume this is a documentation bug and that it should be `SCHEDULER_BATCH_ERRORS`. This is my current approach for handling bulk errors in DBMS_SCHEDULER. In this case I am using STOP_JOB, and for handling bulk errors I will simply recall STOP_JOB with force => TRUE for those jobs that failed to stop normally. <code> DECLARE l_job_name_csv VARCHAR2(256) := '...'; BEGIN DBMS_SCHEDULER.STOP_JOB( job_name => l_job_name_csv, force => FALSE, commit_semantics => 'ABSORB_ERRORS' ); EXCEPTION WHEN OTHERS THEN IF SQLCODE != -27362 THEN RAISE; END IF; SELECT LISTAGG( scheduler_batch_errors.object_name, ',' ) WITHIN GROUP ( ORDER BY scheduler_batch_errors.object_name ) INTO l_job_name_csv FROM scheduler_batch_errors ; DBMS_SCHEDULER.STOP_JOB( job_name => l_job_name_csv, force => TRUE, commit_semantics => 'ABSORB_ERRORS' ); END; / </code> Is something like the above the correct approach? Thank you.

i am facing issue in fra as space is getting filled and not deleting flashback log filling the mount point on the server causing alert in my envoirnment

Hello, My question basically is this, Is there any scenario where it makes sense to define constraints in the application rather than at the database layer? So I work as a dba and sometimes it feels like a back and forth with the devs in my team. I try to always enforce the use of unique keys, foreign keys(including using same names for columns that mean the same thing) and check constraints as well. More than once I have got a request like this (things are anonymised for the purpose of this question and this is not a generic data model) : Create a table "testtab_3" with columns: testtab_id NUMBER(3) (primary key) object_type VARCHAR2(16) object_value NUMBER(3) now they plan to use object_type and object_value to implement foreign keys inside the application where object_value could be a value that relates back to the primary key of another table in the application say "testtab_1" or "testtab_2", and object_type will tell them what table to point back to. I have gotten requests like this before and pushed back; suggesting two separate columns leaving one null if that value is not required for the row to be able to enforce the integrity inside the database. In this scenario the developer explicitly said "I know you like forcing constraints on us but can you just allow me to do it like this in my application" I try explaining that it's not that "I like" the constraints but that they're important for data integrity. My question, as stated earlier, does this make sense? should constraints be defined in the application layer? is there a scenario where that is better than doing it in the database? am I just making life unnecessarily harder for my teammates ? Thanks for your time and appreciate your response.

Hello Tom, Can you please help me on explaining the difference and what the best approach is. I have a table used for outgoing messages to KAFKA. On the table I have an unique ID filled by a sequence. The KAFKA consumer reads the latest rows, which can be 0 to f/e 50 rows. Like <code>SELECT message FROM <TABLE> WHERE id > :P_ID ORDER BY ID ASC</code> So we need an INDEX on the table. Should I create the INDEX like <i>(ID DESC)</i> <i>(ID ASC)</i> or just <i>(ID)</i> She SQL to create the index could look like: <code>CREATE UNIQUE INDEX <NAME> ON <TABLE> (ID DESC);</code> or <code>CREATE UNIQUE INDEX <NAME> ON <TABLE> (ID);</code> I did not added the Primary Key constraint, while f/e the sequence already adds a number like: <code>CREATE TABLE <TABLE> ( ID NUMBER(10) GENERATED ALWAYS AS IDENTITY ( START WITH 1 MAXVALUE 9999999999 MINVALUE 1 CYCLE NOCACHE ORDER NOKEEP NOSCALE) NOT NULL, MESSAGE CLOB )</code> Thanks Wouter

I have a table. <code>CREATE TABLE QQ_SOME_TABLE ( ID NUMBER(9), GROUP_ID NUMBER(9), FROM_DATE DATE, TO_DATE DATE, VALUE1 VARCHAR2(4000 BYTE), VALUE2 VARCHAR2(4000 BYTE) ); CREATE UNIQUE INDEX QQ_SOME_TABLE_PK ON QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE); ALTER TABLE QQ_SOME_TABLE ADD ( CONSTRAINT QQ_SOME_TABLE_PK PRIMARY KEY (ID, GROUP_ID, FROM_DATE) USING INDEX QQ_SOME_TABLE_PK ENABLE VALIDATE); Insert into QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE, TO_DATE, VALUE1, VALUE2) Values (1, 56, TO_DATE('26/10/2025', 'DD/MM/YYYY'), TO_DATE('31/10/2025', 'DD/MM/YYYY'), NULL, '52'); Insert into QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE, TO_DATE, VALUE1, VALUE2) Values (1, 56, TO_DATE('01/11/2025', 'DD/MM/YYYY'), TO_DATE('30/11/2025', 'DD/MM/YYYY'), NULL, '52'); Insert into QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE, TO_DATE, VALUE1, VALUE2) Values (1, 56, TO_DATE('01/12/2025', 'DD/MM/YYYY'), TO_DATE('31/01/2026', 'DD/MM/YYYY'), NULL, '52'); Insert into QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE, TO_DATE, VALUE1, VALUE2) Values (1, 56, TO_DATE('01/02/2026', 'DD/MM/YYYY'), TO_DATE('01/02/2026', 'DD/MM/YYYY'), '1', '52'); Insert into QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE, TO_DATE, VALUE1, VALUE2) Values (1, 56, TO_DATE('25/02/2026', 'DD/MM/YYYY'), TO_DATE('28/02/2026', 'DD/MM/YYYY'), NULL, '51'); Insert into QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE, TO_DATE, VALUE1, VALUE2) Values (1, 56, TO_DATE('01/03/2026', 'DD/MM/YYYY'), TO_DATE('31/03/2026', 'DD/MM/YYYY'), NULL, '51'); Insert into QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE, TO_DATE, VALUE1, VALUE2) Values (1, 56, TO_DATE('01/04/2026', 'DD/MM/YYYY'), TO_DATE('31/12/4712', 'DD/MM/YYYY'), NULL, '51'); Insert into QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE, TO_DATE, VALUE1, VALUE2) Values (1, 56, TO_DATE('02/02/2026', 'DD/MM/YYYY'), TO_DATE('10/02/2026', 'DD/MM/YYYY'), '1', '52'); Insert into QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE, TO_DATE, VALUE1, VALUE2) Values (1, 56, TO_DATE('11/02/2026', 'DD/MM/YYYY'), TO_DATE('14/02/2026', 'DD/MM/YYYY'), '1', '52'); Insert into QQ_SOME_TABLE (ID, GROUP_ID, FROM_DATE, TO_DATE, VALUE1, VALUE2) Values (1, 56, TO_DATE('15/02/2026', 'DD/MM/YYYY'), TO_DATE('24/02/2026', 'DD/MM/YYYY'), NULL, '52'); COMMIT; select * from qq_some_table order by id, group_id, from_date;</code> There is no gaps in dates periods from_date and to_date for the same id and group_id. I need select that by the same id and group_id finds data diapazon when value1 and value2 are not changed and keeps the minimum of from_date and maximum of to_date. <code>create table qq_result_table as select * from qq_some_table where 1 =2;</code> Here is a result that I need to get. <code> insert into qq_result_table (id, group_i...

Hi! I have a simple subquery that returns the last record by id from table. I want to use this subquery for different queries. What is the difference between using CREATE VIEW AS subquery and CREATE FUNCTION function_name RETURN VARCHAR2 SQL_MACRO with subquery without any parameters? <code>CREATE VIEW v_last_rec AS SELECT id, MAX(col1) KEEP (DENSE_RANK LAST ORDER BY date_column) AS col1, MAX(col2) KEEP (DENSE_RANK LAST ORDER BY date_column) AS col2 FROM table GROUP BY id;</code> <code>CREATE OR REPLACE FUNCTION get_last_recs RETURN VARCHAR2 SQL_MACRO IS BEGIN RETURN q'[ SELECT id, MAX(col1) KEEP (DENSE_RANK LAST ORDER BY date_column) AS col1, MAX(col2) KEEP (DENSE_RANK LAST ORDER BY date_column) AS col2 FROM table GROUP BY id ]'; END; </code> What is the best option for the optimizer for big tables?

Tom, We have a requirement create a report for eg to determine the number of individual performance reports deleted each month for each user. Let us say there is a table report_card which has an individuals performance details for each year. Some inserts, updates and deletes happen on this table. Every month I need to find the number of performance reports deleted for each user. For example let us say the individual performance information is stored in a table like <code> CREATE TABLE test.REPORT_CARD ( USERID VARCHAR2(30 BYTE) CONSTRAINT report_card_pk PRIMARY KEY, EVALUATION_START_DATE DATE constraint report_card_start_dt_nn NOT NULL, EVALUATION_END_DATE DATE constraint report_card_end_dt_nn NOT NULL, PASS_FAIL_IND CHAR(1) ); alter table test.REPORT_CARD add constraint report_card_pk PRIMARY KEY (USERID,EVALUATION_START_DATE,EVALUATION_END_DATE); insert into test.report_card(USERID , EVALUATION_START_DATE , EVALUATION_END_DATE , PASS_FAIL_IND ) values('SMITH',TO_DATE('01/01/2007','MM/DD/YYYY'),TO_DATE('01/01/2008','MM/DD/YYYY'),'S') insert into test.report_card(USERID , EVALUATION_START_DATE , EVALUATION_END_DATE , PASS_FAIL_IND ) values('DOE',TO_DATE('01/01/2007','MM/DD/YYYY'),TO_DATE('01/01/2008','MM/DD/YYYY'),'S') insert into test.report_card(USERID , EVALUATION_START_DATE , EVALUATION_END_DATE , PASS_FAIL_IND ) values('MARLEY',TO_DATE('01/01/2007','MM/DD/YYYY'),TO_DATE('01/01/2008','MM/DD/YYYY'),'U') insert into test.report_card(USERID , EVALUATION_START_DATE , EVALUATION_END_DATE , PASS_FAIL_IND ) values('SMITH',TO_DATE('01/02/2008','MM/DD/YYYY'),TO_DATE('12/31/2008','MM/DD/YYYY'),'S') insert into test.report_card(USERID , EVALUATION_START_DATE , EVALUATION_END_DATE , PASS_FAIL_IND ) values('DOE',TO_DATE('01/02/2008','MM/DD/YYYY'),TO_DATE('12/31/2008','MM/DD/YYYY'),'S') insert into test.report_card(USERID , EVALUATION_START_DATE , EVALUATION_END_DATE , PASS_FAIL_IND ) values('MARLEY',TO_DATE('01/02/2008','MM/DD/YYYY'),TO_DATE('12/31/2008','MM/DD/YYYY'),'S') insert into test.report_card(USERID , EVALUATION_START_DATE , EVALUATION_END_DATE , PASS_FAIL_IND ) values('SMITH',TO_DATE('01/01/2009','MM/DD/YYYY'),TO_DATE('12/31/2009','MM/DD/YYYY'),'S') insert into test.report_card(USERID , EVALUATION_START_DATE , EVALUATION_END_DATE , PASS_FAIL_IND ) values('DOE',TO_DATE('01/01/2009','MM/DD/YYYY'),TO_DATE('12/31/2009','MM/DD/YYYY'),'S') insert into test.report_card(USERID , EVALUATION_START_DATE , EVALUATION_END_DATE , PASS_FAIL_IND ) values('MARLEY',TO_DATE('01/01/2009','MM/DD/YYYY'),TO_DATE('12/31/2009','MM/DD/YYYY'),'S') DELETE FROM test.REPORT_CARD WHERE TRUNC(EVALUATION_END_DATE) = TR...

Coordinated vs parallel replicat for Oracle GoldenGate initial loads: why coordinated wins and scales a single table.

The post Coordinated Replicats: The Best Way to Do Initial Load appeared first on DBASolved.

OCI GoldenGate drops the dirprm and dirmac directories, but INCLUDE files and macros still work. Here is how to create and reference them in the cloud console.

The post Using INCLUDE Files and Macros in OCI GoldenGate appeared first on DBASolved.

Understanding how Linux handles network packets at the kernel level can feel overwhelming — until you see how the pieces fit together. Netfilter provides the foundation, while nftables gives us a modern, flexible way to define firewall rules, NAT, and packet mangling.

Whether you’re debugging connectivity issues, writing security tools, or optimizing performance, knowing these internals helps you work more effectively with Linux networking.

Netfilter: The Kernel’s Packet Processing Framework

Netfilter is the Linux kernel’s packet filtering and mangling infrastructure. It defines well-known hook points where packets can be inspected and modified as they flow through the system:

• PREROUTING — Right after a packet arrives, before routing decisions
• INPUT — Packets destined for the local system
• FORWARD — Packets being routed through the host
• OUTPUT — Locally generated packets
• POSTROUTING — After routing, before leaving the host

Chains attached to these hooks let you enforce security policies, perform NAT, or influence routing.

nftables: The Modern Replacement for iptables

nftables brings a cleaner, more consistent syntax and better performance compared to the older iptables framework. It organizes configuration into tables, chains, rules, sets, and expressions.

Core nftables Building Blocks Tables

Containers that group related chains, sets, and rules. Common families include ip (IPv4), ip6 (IPv6), and inet (both).

nft add table ip myfirewall

Chains

Sequences of rules. Base chains attach directly to Netfilter hooks and define behavior (filter, nat, route).

nft add chain ip myfirewall input { type filter hook input priority 0 \; }

Rules

Define matching conditions and actions (accept, drop, jump, etc.).

nft add rule ip myfirewall input tcp dport 22 accept

Sets

Efficient collections for matching (IP addresses, ports, etc.).

nft add set ip myfirewall trusted_ips { type ipv4_addr \; }
nft add element ip myfirewall trusted_ips { 192.168.1.10, 10.0.0.5 }

Practical Example: Simple Firewall

Here’s how to create a basic firewall that allows SSH from trusted IPs and drops everything else:

nft add table ip myfirewall
nft add chain ip myfirewall input { type filter hook input priority 0 \; }
nft add set ip myfirewall trusted_ips { type ipv4_addr \; }
nft add element ip myfirewall trusted_ips { 192.168.1.1, 192.168.1.2 }
nft add rule ip myfirewall input ip saddr @trusted_ips accept
nft add rule ip myfirewall input drop

Behind the Scenes: User Space to Kernel

Tools like nft use libmnl and libnftnl to communicate with the kernel via Netlink. This allows atomic batch operations — multiple changes applied together or not at all — ensuring consistent firewall state.

Best Practices for Production

• Use named sets for frequently updated lists (trusted IPs, blocked addresses)
• Keep base chains simple and explicit with a final drop rule
• Leverage priorities to control execution order
• Batch operations when making multiple changes
• Monitor and log dropped packets for visibility

Conclusion

Netfilter and nftables form a powerful, unified framework for packet processing in Linux. Understanding how tables, chains, rules, and sets work together helps you build more effective firewalls, troubleshoot network issues faster, and appreciate the elegance of the modern Linux networking stack.

Whether you’re securing servers, implementing complex NAT rules, or exploring kernel internals, nftables gives you the tools to control traffic with precision and clarity.

Most Oracle DBAs and developers copy-paste the same “recommended” TNS connection string for RAC and Data Guard without fully understanding how each parameter affects real-world behavior. That changes today.

This guide breaks down the critical parameters in a typical HA connect descriptor, shows measurable timing impacts, and gives clear guidance on when to tune what — so your applications stay resilient during switchovers, failovers, and maintenance.

The Standard HA Connect String

Here’s the common pattern you’ll see in MAA documentation:

(DESCRIPTION =
  (CONNECT_TIMEOUT=90)(RETRY_COUNT=100)(RETRY_DELAY=3)
  (TRANSPORT_CONNECT_TIMEOUT=1000ms)
  (ADDRESS_LIST = (LOAD_BALANCE=on) (ADDRESS = ...))
  (ADDRESS_LIST = (LOAD_BALANCE=on) (ADDRESS = ...))
  (CONNECT_DATA = (SERVICE_NAME = my_service))
)

Let’s explore what each setting actually does and how changing it impacts connection behavior.

1. FAILOVER = ON (Default)

Controls whether the client tries alternate addresses when one fails. Keep this ON unless you have a very specific reason to disable it. Turning it OFF makes connections order-dependent and can prevent reaching an available site during role transitions.

2. LOAD_BALANCE = ON

Randomizes the starting address in an ADDRESS_LIST. This prevents one SCAN IP from being hammered and helps spread load. Strongly recommended when you have multiple addresses, especially during partial outages or maintenance.

3. RETRY_COUNT & RETRY_DELAY

RETRY_COUNT defines how many additional rounds the client makes through the address list. RETRY_DELAY adds a pause between rounds so the service has time to become available after a switchover or failover.

Tip: Use RETRY_DELAY=3 (seconds) as a good starting point. Tight loops (RETRY_DELAY=0) create unnecessary load and should be avoided in production.

4. TRANSPORT_CONNECT_TIMEOUT

This is crucial when an IP or port is unreachable. It caps how long the client waits for a TCP connect before moving to the next address. Set it low enough to fail fast during outages, but high enough to handle normal network jitter (1000ms is a common balanced value).

5. CONNECT_TIMEOUT

Limits the total time for a single connection attempt, including server process creation. Set this higher than TRANSPORT_CONNECT_TIMEOUT (commonly 60–90 seconds) to allow normal connects under load while protecting against hanging attempts.

Practical Recommendations

• For frequent role changes (Fast-Start Failover): Use LOAD_BALANCE=on and moderate RETRY_COUNT
• For stable primary with rare switchovers: Prefer LOAD_BALANCE=off with clear site ordering
• Always set TRANSPORT_CONNECT_TIMEOUT explicitly — don’t rely on defaults
• Align your application connection pool timeouts with the worst-case client wait time
• Use the latest Oracle client (26ai recommended) for millisecond precision support

Final Advice

Don’t treat the HA connection string as magic copy-paste code. Understand what each parameter controls and tune it to your environment’s failover patterns and network characteristics. Small changes here can dramatically improve application resilience during planned maintenance and unplanned outages.

Test your connection strings under simulated failure scenarios (service down, network blocked) and measure real connect times. The better you understand your client behavior, the more predictable and reliable your high-availability applications will be.

Mission-critical applications demand minimal downtime and lightning-fast recovery. With Oracle AI Database 26ai, Oracle has dramatically improved Data Guard and Active Data Guard role transitions, making high-availability architectures faster and more reliable than ever.

These enhancements push Oracle Maximum Availability Architecture (MAA) Platinum tier capabilities across Oracle’s multicloud ecosystem, giving organizations consistent, enterprise-grade resilience no matter where they run.

Game-Changing Performance Improvements

Oracle’s testing shows impressive gains:

• Up to **5x faster failovers** — often completing in under 30 seconds
• Up to **3.4x faster switchovers**
• Consistent results across both small and large workloads
• No changes required to your applications

These optimizations span database recovery, checkpoint processing, service management, and multitenant operations — delivering real reductions in Recovery Time Objectives (RTO).

MAA Platinum Tier Now Available Across Multicloud

Organizations using Oracle Database@Azure, Oracle Database@AWS, or Oracle Database@Google Cloud can now standardize on the same Platinum MAA architecture:

• Local HA with RTO under 10 seconds
• Regional DR with RTO under 30 seconds
• Zero or near-zero Recovery Point Objective (RPO)

This consistency lets teams apply the same proven best practices, operational procedures, and resiliency strategies across all their multicloud deployments.

Why This Matters for Your Business

• Less Downtime — Faster planned maintenance and unplanned recovery
• Better User Experience — Minimal disruption during role transitions
• Simplified Operations — Standardize high-availability practices across environments
• Future-Proof Architecture — Built for the most demanding mission-critical workloads

Who Should Upgrade?

Existing Exadata and Exadata Database Service customers running Active Data Guard will see immediate benefits from moving to Oracle AI Database 26ai. The performance gains make Platinum MAA tier objectives much more achievable without major architectural overhauls.

Next Steps

If you’re running mission-critical databases, now is the perfect time to evaluate Oracle AI Database 26ai. The combination of Exadata performance, Active Data Guard, and these accelerated role transitions creates one of the strongest availability platforms available today.

Explore the updated MAA reference architectures and multicloud certification matrix to see how you can strengthen your high-availability strategy across Oracle Cloud and major hyperscalers.

One of the nicest things about OKafka is how familiar it feels if you’ve used Kafka before. You configure connections with Properties objects, just like kafka-clients. The big difference? You’re talking directly to Oracle Database Transactional Event Queues instead of a separate broker.

Here’s how to set up authentication cleanly for development and production.

Two Main Authentication Paths 1. PLAINTEXT (Great for Local Dev)

Simple username/password setup using an ojdbc.properties file:

Properties props = new Properties();
props.put("security.protocol", "PLAINTEXT");
props.put("bootstrap.servers", "your-host:port");
props.put("oracle.service.name", "your_service_name");
props.put("oracle.net.tns_admin", "/path/to/config/dir");

Your ojdbc.properties file should contain:

user=testuser
password=YourStrongPassword123

2. SSL / mTLS (Production Ready)

Use Oracle Wallet for secure connections. Point to your wallet directory and specify the TNS alias:

props.put("security.protocol", "SSL");
props.put("oracle.net.tns_admin", "/path/to/wallet");
props.put("tns.alias", "your_tns_alias");

Full Working Example

Here’s a complete snippet to create an AdminClient and make a topic:

try (Admin admin = AdminClient.create(props)) {
    NewTopic topic = new NewTopic("MY_EVENTS", 5, (short) 0);
    admin.createTopics(Collections.singletonList(topic)).all().get();
    System.out.println("Topic created successfully");
}

Pro Tips for Smooth Sailing

• Always use uppercase topic names with OKafka
• Store wallet files securely and never commit them to version control
• Test with Oracle Database Free + Testcontainers for quick iterations
• Move to mTLS early in your development cycle

Final Thoughts

OKafka makes connecting to Oracle feel just like connecting to any other Kafka cluster — but you get all the power, security, and transactional guarantees of the database built right in.

No extra infrastructure. No separate cluster to manage. Just reliable event streaming where your data already lives.