DRCP - Database Resident Connection Pooling

Giriş

Açıklaması şöyle

Postgres uses a process-per-connection model. Every single connection spawns its own OS backend process, each eating a few MB of memory. So when a crashing service kept reconnecting, we weren't just opening connections. We were forking processes that nobody cleaned up. (Death by a thousand forks, basically.)

The usual fix in the Postgres world is an external pooler like PgBouncer, which sits between your app and the database and multiplexes many clients onto a small set of reused connections.

But here's what I found interesting: Oracle solved this inside the database itself. DRCP (Database Resident Connection Pooling) keeps a shared pool of server processes, so idle connections release their process instead of hoarding it, letting the DB scale to tens of thousands of connections without drowning in memory.

JDBC Reactive

Giriş

Açıklaması şöyle

The JDBC Reactive Extensions are available in the Oracle JDBC Thin Driver 21c or later. The extensions expose standard Java Flow Subscriber and Publisher types and APIs that can interoperate with the R2DBC API and other Reactive Streams libraries.

Jar

Açıklaması şöyle

Interestingly, if you are using the Oracle JDBC Driver, you do not have to include another JAR as a dependency. The JDBC Reactive Extensions belong to the same dependency artefact (JAR file) as the Oracle JDBC Driver.

Tipler

Açıklaması şöyle. Yani JDBC Reactive ile diğer kütüphanelerin beklediği tipler uyuşmuyor

The JDBC Reactive Extensions APIs handle the low-level mechanics of the Flow API to the application using java.util.concurrent.Flow.Publisher and java.util.concurrent.Flow.Subscriber. Popular libraries such as Reactor, RxJava, and Akka-Streams interface with the org.reactivestreams.Publisher and org.reactivestreams.Subscriber types defined by the reactive-streams-jvm project as specified in the following link:

Açıklaması şöyle. Yani JDBC Reactive ile diğer kütüphanelerin beklediği tipler uyuşmuyor

You can convert between the Flow type and the org.reactivestreams type using the org.reactivestreams.FlowAdapters class 

...

Oracle recommends that you use the Reactive Streams libraries when interfacing with the Flow types exposed by the JDBC driver.

API

OraclePreparedStatement  metodları şöyle

Flow.Publisher<Boolean> executeAsyncOracle
Flow.Publisher<Long> executeUpdateAsyncOracle
Flow.Publisher<Long> executeBatchAsyncOracle
Flow.Publisher<OracleResultSet> executeQueryAsyncOracle

Açıklaması şöyle

JDBC Reactive Extensions have the following limitations:

- You must access the asynchronous methods through the java.sql.Wrapper.unwrap method, instead of accessing those through a typecast. This ensures correct behaviour of the asynchronous methods when you use them with proxy Oracle JDBC classes, for example, when you use asynchronous methods with connection pools.

- Reading large responses from the network may require blocking I/O bound operations. Blocking read operations can occur if the driver reads a response that is larger than the TCP Receive buffer size.

- Asynchronous SQL execution supports neither scrollable ResultSet types nor sensitive ResultSet types.

executeAsyncOracle metodu

Örnek - CREATE TABLE

Elimizde şöyle bir kod olsun

import java.sql.SQLException;
import java.util.concurrent.Flow;

import oracle.jdbc.OracleConnection;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.pool.OracleDataSource;

private OracleDataSource ods = null;
private OracleConnection conn = null;

try {
  OracleDataSource ods = new OracleDataSource();
  // jdbc:oracle:thin@[hostname]:[port]/[DB service/name]
  ods.setURL("jdbc:oracle:thin@[hostname]:[port]/[DB service/name");
  ods.setUser("[Username]");
  ods.setPassword("[Password]");

  conn = (OracleConnection) ods.getConnection();
  // Execute a SQL DDL statement to create a database table asynchronously
  createTable(conn);
} catch (SQLException e) {
 ...
}

Şöyle yaparız

Flow.Publisher<Boolean> createTable(OracleConnection connection)throws SQLException {
  OraclePreparedStatement createTableStatement = (OraclePreparedStatement) 
  connection.prepareStatement("CREATE TABLE employee_names (id NUMBER PRIMARY KEY,"
      + "first_name VARCHAR(50), last_name VARCHAR2(50))");

  Flow.Publisher<Boolean> createTablePublisher = createTableStatement
    .unwrap(OraclePreparedStatement.class)
    .executeAsyncOracle();

  createTablePublisher.subscribe(
    // This subscriber will close the PreparedStatement
    new Flow.Subscriber<Boolean>() {
      @Override
      public void onSubscribe(Flow.Subscription subscription) {
	subscription.request(1L);
      }

      @Override
      public void onNext(Boolean item) {}

      @Override
      public void onError(Throwable throwable) {closeStatement();}

      @Override
      public void onComplete() {closeStatement();}

      void closeStatement() {
	try {
	  createTableStatement.close();
	} catch (SQLException closeException) {
	}
      }
  });
  return createTablePublisher;
}

executeUpdateAsyncOracle metodu

Örnek

Şöyle yaparız

class PipelineExample{
  void pipelineExample(OracleConnection connection) throws SQLException {
    PreparedStatement delete = connection.prepareStatement(
            "DELETE FROM example WHERE id = 0");
    PreparedStatement insert = connection.prepareStatement(
            "INSERT INTO example (id, value) VALUES (1, 'x')");
    PreparedStatement select = connection.prepareStatement(
            "SELECT id, value FROM example WHERE id = 2");

    Flow.Publisher<Long> deletePublisher =
            delete.unwrap(OraclePreparedStatement.class).executeUpdateAsyncOracle();
    Flow.Publisher<Long> insertPublisher =
            insert.unwrap(OraclePreparedStatement.class).executeUpdateAsyncOracle();
    Flow.Publisher<OracleResultSet> selectPublisher =
            select.unwrap(OraclePreparedStatement.class).executeQueryAsyncOracle();

    // Subscribe to publishers to get results back
    …
  }
}

Örnek

Şöyle yaparız. Burada org.reactivestreams.FlowAdapters.toPublisher() kullanılıyor

// Execute statements in a pipeline
Flow.Publisher<Long> deletePublisher =
  delete.unwrap(OraclePreparedStatement.class).executeUpdateAsyncOracle();
Flow.Publisher<Long> insertPublisher =
  insert.unwrap(OraclePreparedStatement.class).executeUpdateAsyncOracle();
Flow.Publisher<OracleResultSet> selectPublisher =
  select.unwrap(OraclePreparedStatement.class).executeQueryAsyncOracle();

// Consume statement results synchronously with Project Reactor
long deleteCount = Mono.from(toPublisher(deletePublisher)).block();
System.out.println(deleteCount + " rows deleted");

long insertCount = Mono.from(toPublisher(insertPublisher)).block();
System.out.println(insertCount + " rows inserted");

OracleResultSet resultSet = Mono.from(toPublisher(selectPublisher)).block();

DUAL Sistem Tablosu

Örnek

Şöyle yaparız

SELECT 'Hello World!' FROM dual;

Docker ve Oracle

Örnek - 23c

Şöyle yaparız

docker pull gvenzl/oracle-free

docker run -d \
  -p 1521:1521 \
  -e ORACLE_PASSWORD=<your password> \
  -v oracle-volume:/opt/oracle/oradata \
  gvenzl/oracle-free

XE ve Slim

Oracle Database Express Edition (Oracle Database XE) anlamına gelir. Oracle Application Express (APEX) araçlarını içermez

Örnek

Şöyle yaparız. Kullanıcı adı system şifre mypassword  olur

docker run -d -p 1521:1521 -e ORACLE_PASSWORD=mypassword gvenzl/oracle-xe:21-slim-faststart

TestContainers

Maven

Şu satırı dahil ederiz

<dependency>
  <groupId>org.testcontainers</groupId>
  <artifactId>oracle-xe</artifactId>
  <version>${testcontainers.version}</version>
  <scope>test</scope>
</dependency>

Örnek

Şöyle yaparız

OracleContainer oracleContainer = new OracleContainer("gvenzl/oracle-xe:21-slim-faststart")
  .withDatabaseName("testDB")
  .withUsername("testUser")
  .withPassword("testPassword")

Transactional Event Queues

Oracle Advanced Queuing (or AQ) vs Transactional Event Queues

Açıklaması şöyle

Oracle Database has had queueing for many years (since Oracle 8). The older implementation was (and still is) called Oracle Advanced Queuing (or AQ). TxEventQ is a newer implementation that is optimized for much higher throughput and lower latency, and it provides transactional semantics (hence the name). There are clients in all common languages, and you can set up propagation between databases, even across long distances.

Kafka APIs for Transactional Event Queues

Oracle Database 23c Free ile geliyor. Açıklaması şöyle

It has three main parts:

1. The ability for Java applications written using the Kafka API for Java to use the Oracle Database as a Kafka broker,

2. The ability for the Oracle Database to provide Kafka REST APIs, and

3. Connectors that allow the propagation of events between Kafka and the Oracle Datbase, and vice versa.

Şeklen şöyle

Oracle Advanced Queuing

Giriş

Açıklaması şöyle.  Oracle Advanced Queuing yerine Transactional Event Queues kullanılabilir

Oracle Database has had queueing for many years (since Oracle 8). The older implementation was (and still is) called Oracle Advanced Queuing (or AQ).

Oracle Cloud Infrastructure - OCI

SDK

Oracle Cloud Infrastructure altyapıya erişim için aynı AWS gibi SDK sağlıyor

Maven

Şu satırı dahil ederiz

<dependency>
  <groupId>com.oracle.oci.sdk</groupId>
  <artifactId>oci-java-sdk-common</artifactId>
  <version>3.2.0</version>
</dependency>
<dependency>
  <groupId>com.oracle.oci.sdk</groupId>
  <artifactId>oci-java-sdk-common-httpclient-jersey</artifactId>
  <version>3.2.0</version>
</dependency>
<dependency>
  <groupId>com.oracle.oci.sdk</groupId>
  <artifactId>oci-java-sdk-identity</artifactId>
  <version>3.2.0</version>
</dependency>

Burada bir örnek var

OCI GoldenGate

Açıklaması şöyle. Change Data Capture (CDC) içindir.

OCI offers a fully managed NoSQL Cloud Database, a robust alternative to Amazon DynamoDB, and a comprehensive Change Data Capture tool known as OCI GoldenGate. 

Kafka Connect OracleCdcSourceConnector

Bir yazı burada

Oracle LogMiner

Giriş

Hem Kafka Connect hem de Debezium bu arayüzü kullanııyor

Kafka Connect 

Kafka Connect için açıklaması şöyle

This connector uses the Oracle LogMiner interface to query online and archived redo log files. That’s an Oracle specific way to stream both the DML and DDL directly from the redo of the database. This is similar to how the Debezium project reads the Postgres write-ahead log or the MySQL binlog.

Debezium 

Debezium için açıklaması şöyle

Debezium ingests change events using Oracle’s native LogMiner database package. Oracle LogMiner is part of the Oracle Database utilities and provides a well-defined, easy-to-use, and comprehensive interface for querying online and archived redo log files.

The first time the Debezium Oracle connector starts, it performs an initial consistent snapshot of the database to see its entire history. You can change this behavior by setting the snapshot.mode. After the connector completes its initial snapshot, the Debezium connector continues streaming from the position it reads from the current system change number (SCN) position in the redo log. The initial snapshot ensures that the connector has a complete and consistent data set.

The Debezium connector for Oracle Databases supports the following database versions:
Oracle Database 12.2 EE
Oracle Database 19.3 EE
Oracle Database 21.3 EE

The Oracle connector used with Oracle Real Application Clusters (RAC) is in Technology Preview (TP) for this release.

Oracle Goldengate

Kafka'dan Oracle NoSQL Veri Tabanına

Açıklaması şöyle

Oracle’s GoldenGate seamlessly orchestrates data replication from source to sink through remote capture processes, known as Extract and Replicat. The Extract process captures the data and records it to trail files. These trail files are then leveraged to create a Replicat process, effectively applying the records to the sink.

Şeklen şöyle

PostgreSQL Veri Tabanından Oracle Veri Tabanına

Şeklen şöyle Burada iki tane ürün var

1. Goldengate 21c OCI Marketplace for PostgreSQL

2. OCI Goldengate for Oracle

Oracle APEX

Giriş

Açıklaması şöyle

In a way, Oracle Forms can be considered the grand-grand-grandfather of Oracle APEX. The basic idea behind these technologies is the same — easy creation of data-driven web applications with a minimal amount of coding required. Oracle APEX takes this philosophy much further, however, and is a much more evolved and modern technology. Also, most (if not all) Forms modules have their APEX counterparts.

The history of Oracle APEX dates back to 1999 when it was known as Oracle HTML DB (there were also other names, such as Flows and Project Marvel). Over the years, many versions of this technology have been released, and a few groundbreaking features have been introduced — such as plug-ins, Interactive Grid, or REST and PWA support.

Just like Forms, Oracle APEX uses the PL/SQL programming language. However, in APEX, a simple data-driven application can be created entirely — or almost entirely — through the use of specialized Wizards that allow you to connect various ready-made elements and make them work together. You can set up basic business logic, create a layout and interface, and get things working without writing a single line of code. Of course, if you’re not afraid of the command line and have the necessary coding skills (or team up with someone skilled), you can still use them to deliver more complex and powerful solutions.

It’s also worth noting that APEX uses the same database that Forms does (Oracle DB), so it’s equally scalable, safe, and stable. You also retain access to important features, ML/AI modules, Spatial, and so on. The main difference is that, unlike Forms, APEX works in the cloud.

There are no plans to abandon the development of Oracle APEX. It’s quite the opposite: APEX is becoming popular in the world of low-code development and, thus, is actively developed and supported by Oracle – which is great news for your software. The current version of Oracle Application Express is 22.1, and it was released in May 2022.

Oracle Forms

Giriş

Açıklaması şöyle. Oracle Forms yerine APEX kullanılmalı

A Little Bit of History
Oracle Forms is a Rapid Application Development (RAD) tool that allows you to create data entry systems that use database objects (mostly information) from the Oracle Database. It uses the popular PL/SQL language. While originally Forms was a standalone product, currently, it’s a component of Oracle Fusion Middleware.

The first version of Oracle Forms was known by a different name — Interactive Application Facility (IAF) — and saw the light of day in 1981 (along with the second iteration of the Oracle Database). IAF was later renamed to FastForms and then SQL*Forms, which is where the current name comes from (it came along with version 4.0). The very first variant of this technology was only comprised of a compiler and a runtime interpreter, but it quickly grew and evolved.

The main reason Forms got popular is the relative simplicity and ease of use. Creating Oracle Forms applications is a very natural process — anyone with basic SQL and PL/SQL knowledge can handle it. The work usually comes down to modifying so-called triggers, which are event-handling functions used to generate specific effects (i.e., opening a form after a button is pressed, etc.). Because of this, many things can be done without or with minimal coding. You can also change the form layouts using object libraries.

The newest “big” version of Oracle Forms was delivered along with Fusion Middleware 12c and was released in October 2015. It’s commonly known as Oracle Forms 12c, but it’s worth pointing out that it has received several updates since its release — the current Forms version is 12.2.1.4.0 (dated September 2019).

In the Oracle Forms Statement of Direction from a year ago, the company openly states it has no plans for completely dropping support for Oracle Forms. However, the level of the provided support is another matter. Premier Support for Fusion Middleware 12c (and, thus, Oracle Forms 12c) will end in December 2023 (and Extended Support is next in line, ending in August 2025).

What’s more, Oracle Forms isn’t a future-proof technology. It won’t provide you with functionalities and features typical for current, cutting-edge web apps. It’s also hard to find people who can work with it effectively since young developers concentrate on other solutions for obvious reasons.

All of the above means that if you want your Oracle Forms-based software to stay stable and useful, you should start thinking about moving to another, more up-to-date technology now.

Debezium Connector

Giriş

Eğer Oracle, Debezium için hazır değilse ayarları yapmak için bir yazı burada.

Oracle LogMiner Paketi

Oracle LogMiner Paketi yazısına bakabilirsiniz

Örnek

Şöyle yaparız

curl -i -X POST -H “Accept:application/json” 
 -H “Content-Type:application/json” http://ip_kafka_connect:8083/connectors/ 
 -d ‘
{
 “name”:”debezium-ora-001",
 “config”: {
   “connector.class”:”io.debezium.connector.oracle.OracleConnector”,
   “db_type”:”oracle”,
   “tasks.max”:”1",
   “database.server.name”:”hostname_of_database",
   “database.tablename.case.insensitive”:”true”,
   “database.oracle.version”:”12+”,
   “database.hostname”:”10.23.131.132",
   “database.port”:”1522",
   “database.user”:”c##xstrm”,
   “database.password”:”xs”,
   “database.dbname”:”dbz”,
   “database.pdb.name”:”dbz1",
   “database.out.server.name”:”dbzxout”,
   “database.history.kafka.bootstrap.servers”:”10.23.131.131:9092",
   “database.history.kafka.topic”:”debezium.oracle”,
   “database.history.skip.unparseable.ddl”:”true”,
   “include.schema.changes”:”true”,
   “table.whitelist”:”DEBEZIUM.CUSTOMERS”,
   “errors.log.enable”:”true”
   }
 }’

exp komutu

Örnek

Şöyle yaparız

exp adv/adv@advfrw file=<dump-file-name>.dmp tables=ATGSR_%

CREATE DATABASE LINK

Örnek

Şöyle yaparız

CREATE PUBLIC DATABASE LINK CUST2DB.PRIMARY.COM
    CONNECT TO mydatabase IDENTIFIED BY "mypassword"
    USING '(DESCRIPTION=
                (ADDRESS=(PROTOCOL=TCP)(HOST=10.44.16.53)(PORT=1535))
                (CONNECT_DATA=(SERVICE_NAME=CUST2DB))
            )';

drop database link CUST2DB.PRIMARY.COM;

SHUTDOWN

Örnek

Şöyle yaparız

SHUTDOWN IMMEDIATE

expdp komutu

Giriş

expdp komutu "export  data pump" anlamına geliyor. Oracle Database 10g Release 1 (10.1)'den sonrası için kullanılır. Veri tabanının dump'ını alır. Daha eski bir veri tabanı varsa exp komutu kullanılır 

Örnek

Şöyle yaparız

C:\> set ORACLE_SID=ADV

C:\> expdp SYSTEM DUMPFILE=myexp.dmp FULL=y LOG=myexp.log
Password: password

DBA_REGISTRY Sistem Tablosu - Oracle'daki Yüklü Bileşenleri Gösterir

Örnek

Şöyle yaparız

SELECT comp_name, version, status FROM dba_registry;

COMP_NAME                          VERSION    STATUS
---------------------------------- ---------- ------
Oracle Workspace Manager           11.2.0.4.0 VALID
Oracle Database Catalog Views      11.2.0.4.0 VALID
Oracle Database Packages and Types 11.2.0.4.0 VALID
JServer JAVA Virtual Machine       11.2.0.4.0 VALID

tnsping komutu

Örnek

Şöyle yaparız

$ tnsping CUST2DB

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 30-MAR-2022 03:25:26

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
/export/home/oracle/products/12.2.0.1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = corelan-3-scanvip)
(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CUST2DB)))
OK (0 msec)