.::: Script Backup Full & Incremental All Database Using Record to Table in PostgreSQL EDB :::.

correlation with https://teguhth.blogspot.com/2025/02/script-backup-full-all-database-using.html
correlation with https://teguhth.blogspot.com/2025/01/script-backup-full-all-database-using.html

1. create table 

-- Table: public.backuplogs

CREATE TABLE backuplogs (
    host VARCHAR(255) NOT NULL,
    date TIMESTAMP NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    backup VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
);

.::: Script to Restore Backup Full & all Transaction (Restore Incremental) in one Command for EnterpriseDB PostgreSQL :::.

 

correlation https://teguhth.blogspot.com/2024/02/script-to-restore-backup-full-all.html
correlation https://teguhth.blogspot.com/2025/09/script-daily-backup-full-incremental.html

1. check before restore
 
2. check directory backup & create dblist,txt
[postgres@teguhth dwh]$ pwd
/var/lib/pgsql/16/manual/dwh
[postgres@teguhth dwh]$
[postgres@teguhth dwh]$ cat dblist.txt
FULL_dwh_20250913_000001.sql.gz
INC_dwh_20250913_010002.sql.gz
INC_dwh_20250913_020002.sql.gz
INC_dwh_20250913_030002.sql.gz
INC_dwh_20250913_040002.sql.gz
INC_dwh_20250913_050002.sql.gz
INC_dwh_20250913_060002.sql.gz
INC_dwh_20250913_070001.sql.gz
INC_dwh_20250913_080002.sql.gz
INC_dwh_20250913_090002.sql.gz
INC_dwh_20250913_100001.sql.gz
INC_dwh_20250913_110002.sql.gz
INC_dwh_20250913_120002.sql.gz
INC_dwh_20250913_130001.sql.gz
INC_dwh_20250913_140002.sql.gz
INC_dwh_20250913_150002.sql.gz
INC_dwh_20250913_160001.sql.gz
INC_dwh_20250913_170002.sql.gz
INC_dwh_20250913_180002.sql.gz
INC_dwh_20250913_190001.sql.gz
INC_dwh_20250913_200002.sql.gz
INC_dwh_20250913_210002.sql.gz
INC_dwh_20250913_220002.sql.gz
INC_dwh_20250913_230001.sql.gz
[postgres@teguhth dwh]$
 

.::: Script Daily Backup FULL & Incremental PostgreSQL EDB Using pgdump & pgaudit :::.

 

correlation with https://teguhth.blogspot.com/2025/09/how-to-create-backup-incremental-in.html

1. Backup All FULL
 

 

.::: How to Create Backup Incremental in PostgreSQL EDB as Logical Backup (like SQL Server or MariaDB with mysqlbinlog) using pgaudit pgauditlogtofile :::.

 

A. Activate pgaudit 
correlation https://teguhth.blogspot.com/2024/02/enable-pgaudit-pgauditlogtofile-in.html

B. convert to table 

correlation https://teguhth.blogspot.com/2024/04/convert-pgaudit-pgauditlogtofile-log.html

C. Testing sample CRUD Activity (sample database 'dwh')

1. create table pembelian;

create table barang(
KODE_BARANG char(6) not null ,
NAMA_BARANG varchar(25),
SATUAN_BARANG varchar(20),
STOK_BARANG decimal(4),
primary key (KODE_BARANG));
  

.::: Install MariaDB GTID Mirroring Monitoring using php :::.

 

install php https://infotechys.com/install-php-8-3-on-rhel-9-centos-9/

1. create bash monitoring 

[root@teguhth03 web]# cat /data/web/server31.sh
#!/bin/bash

# Variabel koneksi
USER="admin"
PASS="xxxxx"
HOST="10.10.10.31"
PORT="3306"

# Test koneksi ke MySQL server
mysql --skip-ssl -u$USER -p$PASS -h$HOST -P$PORT -e "SELECT 1;" >/dev/null 2>&1

 if [ $? -ne 0 ]; then
    echo "{ \"connections\": \"\", \"role\": \"Down\", \"gtid\": \"\",\"hostname\": \"$hostnamex\", \"host\": \"$HOST\", \"port\": \"$PORT\",\"dbv\": \"$versionx\",\"osv\": \"$osx\" }"
    exit 0
fi
 

.::: Install Microsoft SQL Server 2022 in Centos 9 / Redhat 9 / Almalinux 9 :::.

 

1. disable selinux

2. enable repo 

curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/$(rpm -E %{rhel})/mssql-server-2022.repo

curl https://packages.microsoft.com/config/rhel/8/prod.repo | sudo tee /etc/yum.repos.d/mssql-release.repo

curl https://packages.microsoft.com/config/rhel/9/prod.repo | sudo tee /etc/yum.repos.d/mssql-release.repo

[root@teguhth data]# curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/$(rpm -E %{rhel})/mssql-server-2022.repo
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   444  100   444    0     0    199      0  0:00:02  0:00:02 --:--:--   199
[root@teguhth data]#
[root@teguhth data]#
[root@teguhth data]# cat /etc/yum.repos.d/mssql-server.repo
[packages-microsoft-com-mssql-server-2022]
name=Microsoft SQL Server 2022
baseurl=https://packages.microsoft.com/rhel/9/mssql-server-2022/
enabled=1
gpgcheck=1
repo_gpgcheck=1
# If installing the bare repo file consider installing the gpg key once and then updating
# this to point to the on-disk location. For example:
# gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-Microsoft
gpgkey=https://packages.microsoft.com/keys/microsoft.asc
sslverify=1
[root@teguhth data]#

.::: Install MariaDB 10.6 in Ubuntu 22 :::.

  

1. Tambahkan GPG Key mariadb

sudo apt install -y curl software-properties-common gnupg
curl -fsSL https://mariadb.org/mariadb_release_signing_key.asc | sudo gpg --dearmor -o /usr/share/keyrings/mariadb-keyring.gpg

2. Tambahkan Repository mariadb 10.6

deb [arch=amd64,arm64 signed-by=/usr/share/keyrings/mariadb-keyring.gpg] http://mirror.mariadb.org/repo/10.6/ubuntu noble main
 

.::: Script Backup & Restore MariaDB using Confirm before Action Execute :::.

 

A. Action 

1. test restore not found
 

 

.::: Query Check day, hour & early time, check time, different time in MSSQL, MariaDB MySQL, PostgreSQL EDB, Oracle DB to Get the Start of the Current Hour and the Previous Hour :::.

  

A. MSSQL

1. one day before

select @@servername as ServerName, GETDATE() as Currentdate, CAST(CONVERT(date, GETDATE() - 1) AS datetime) as datebefore , CAST(CONVERT(date, GETDATE()) AS datetime) as datecurrent 

INSERT INTO target_table (col1, col2, col3, tanggal)
SELECT col1, col2, col3, tanggal
FROM source_table
WHERE tanggal >= CAST(CONVERT(date, GETDATE() - 1) AS datetime) 
  AND tanggal <  CAST(CONVERT(date, GETDATE()) AS datetime);

.::: Simulation Query Select from Java to MariaDB using mariadb-java-client-3.5.3.jar (MariaDB Driver) using MariaDB SSL Certificate :::.

 

correlation https://teguhth.blogspot.com/2025/06/simulation-query-select-from-java-to.html
https://teguhth.blogspot.com/2025/06/simulation-connection-java-to-mariadb.html
    
1. Konversi client-cert.pem dan client-key.pem ke .p12

 

openssl pkcs12 -export -in /home/user/certs/client-cert.pem -inkey /home/user/certs/client-key.pem -out /home/user/certs/client-cert.p12 -name client -CAfile /home/user/certs/ca-cert.pem -caname root -passout pass:root

.::: Create User MariaDB MySQL using SSL / Certificate :::.

 

Mengaktifkan TLS/SSL pada MariaDB 10.6 dan jika sudah nanti akan di test dengan user 'adminssl' password 'adminssl' 

Berikut adalah panduan mengaktifkan TLS/SSL di MariaDB 10.6 dan menguji koneksi dengan user adminssl:

1. Buat Direktori & Sertifikat SSL

mkdir -p /etc/mysql/ssl

cd /etc/mysql/ssl

2. Create Certificate 
 

.::: Script Daily Backup MongoDB using Date or Compress :::.

  

1. Script backup full 

#[root@teguhth mongod]# cat backup_mongodb_full_script.sh
##-bash-4.2$ cat backup_mongodb_full_script.sh
echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address
 

.::: Sample shell script integration MariaDB to Redis :::.

 

1. run script using mariadb
sh compare_maria_redis.sh mariadb
 

2. run script using redis
sh compare_maria_redis.sh redis
 

.::: Install MongoDB 8 in Centos 9 (mongodb-org-server, mongodb-mongosh) :::.

  

1. download

wget https://repo.mongodb.org/yum/redhat/9/mongodb-org/8.0/x86_64/RPMS/mongodb-org-server-8.0.10-1.el9.x86_64.rpm

2. create repository

[root@teguhth ~]# cat /etc/yum.repos.d/mongodb-org.repo
[mongodb-org-6.0]
name=MongoDB Repository
#baseurl=https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/6.0/x86_64/
baseurl=https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/9.0/x86_64/
gpgcheck=0
enabled=1
gpgkey=https://www.mongodb.org/static/pgp/server-6.0.asc
[root@teguhth ~]#

.::: Start the SQL Server Import and Export Wizard From MariaDB to MSSQL using Write a query to specify the data to transfer & compare with Open Query :::.

 

correlation with https://teguhth.blogspot.com/2025/06/start-sql-server-import-and-export.html

A. Prepare

1. MariaDB check sample query 

select * from products where product_name='laptop' or product_name='tablet';

2. create view mariadb 

create view view_produk select * from products where product_name='laptop' or product_name='tablet';

3. test if using open query

select * from openquery([MARIALOCAL],'select * from erpdb.view_produk;')
select * from openquery([MARIALOCAL],'select * from erpdb.products where product_name=''laptop'' or product_name=''tablet'';')
  

.::: Start the SQL Server Import and Export Wizard From MSSQL to MariaDB :::.

 

1. Install ODBC Driver MariaDB MySQL (32 bits) for this case install mysql-connector-odbc-8.0.42-win32

mysql-connector-odbc-8.0.42-win32
mariadb-connector-odbc-3.2.6-win32

2. Setup DSN MariaDB Using MySQL Driver 

# using mysql driver 32 >> pakai yang ini 
Dsn=mysql321;Driver={MySQL ODBC 8.0 Unicode Driver};server=10.10.10.7;uid=admin;pwd=admin;database=erpdb;port=3306

# using maridb driver32
Dsn=maria;Driver={MariaDB ODBC 3.2 Driver};server=10.10.10.7;uid=admin;pwd=admin;database=erpdb;port=3306
 

.::: Shell Script to compare table Database MariaDB MySQL :::.

 

  correlation with https://teguhth.blogspot.com/2025/03/query-to-check-table-column-compare_28.html

A. Compare Multiple Table 
1. check table server1
 

 

.::: Shell Script to compare value table a Database MariaDB MySQL :::.

  

correlation with https://teguhth.blogspot.com/2025/03/query-to-check-table-column-compare_28.html

1. check value table barang server1
 

 

.::: Trial Backup & Restore MariaDB MySQL with crontab -l / crontab -e & /etc/crontab :::.

 

A. Prepare Script backup & Restore

1. create backup script

[root@teguhth cron]# pwd
/data/cron
[root@teguhth cron]# cat backup_test.sh
echo "backup full mulai" > log_backup.txt
date >> log_backup.txt

mysqldump -uroot -pxxx -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --routines=true --triggers=true --no-data=false teguhth | gzip -c > teguhth_ori.sql.gz ;

echo "backup full selesai " >> log_backup.txt
date  >> log_backup.txt
[root@teguhth cron]#

.::: Simulation Query Select from Java to MariaDB using mariadb-java-client-3.5.3.jar (MariaDB Driver) :::.

  

https://teguhth.blogspot.com/2025/06/simulation-connection-java-to-mariadb.html

1. Script java 

[root@teguhth maria]# cat queryjoin.java
import java.sql.*;

public class queryjoin {
    public static void main(String[] args) {
        String url = "jdbc:mariadb://10.10.10.90:3306/teguhth"; // Ganti IP jika perlu
        String user = "admin";
        String pass = "admin";

        try {
            Connection conn = DriverManager.getConnection(url, user, pass);

            // Jalankan query
//            String query = "select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;";

//String query = "SELECT b.NAMA_BARANG, s.NAMA_SUPLIER, p.TANGGAL_PASOK, p.JUMLAH_PASOK, (SELECT SLEEP(20)) as DELAY FROM barang b, suplier s, pasok p WHERE b.KODE_BARANG = p.KODE_BARANG AND s.KODE_SUPLIER = p.KODE_SUPLIER;";

//String query ="select * from pembelian;";
 

.::: Simulation connection java to MariaDB for Success & Fail using mysql-connector-j-8.0.33.jar (MySQL Driver) (com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure. The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.) :::..

 

1. Download (Platform independent) MySQL Driver

https://repo1.maven.org/maven2/com/mysql/mysql-connector-j/8.0.33/mysql-connector-j-8.0.33.jar

java -version
dnf install java-17-openjdk-devel -y    # CentOS 9

2. check login mariadb

[root@teguhth ~]# mysql -h 10.10.10.90 -uadmin -p teguhth
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 11.4.4-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

.::: Simulation connection java to MariaDB for Success & Fail using mariadb-java-client-3.5.3.jar (MariaDB Driver) (java.sql.SQLNonTransientConnectionException: Socket fail to connect to 10.10.10.9. No route to host) :::.

 

1. Download (Platform independent)

https://mariadb.com/downloads/connectors/connectors-data-access/java8-connector
 

java -version
dnf install java-17-openjdk-devel -y    # CentOS 9

2. check login mariadb

[root@teguhth ~]# mysql -h 10.10.10.90 -uadmin -p teguhth
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 11.4.4-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

.::: Move TempDB to other folder (move SQL Server MDF and LDF Files location):::.

 

 
correlation https://teguhth.blogspot.com/2023/06/move-sql-server-mdf-and-ldf-files.html

1. check 

sp_helpdb tempdb

USE tempdb;
GO
SELECT 
    name AS [Logical Name],
    physical_name AS [File Path],
    type_desc AS [File Type],
    size * 8 / 1024 AS [Size (MB)],
    max_size,
    growth,
    is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');

SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb');

.::: Quary query to simulation High CPU & High Memory Usage In EDB PostgreSQL :::.

 

 
A. Simulation High CPU

1. Contoh query untuk Simulasi CPU Tinggi
 
Untuk mensimulasikan CPU utilization tinggi di PostgreSQL menggunakan query, kamu bisa menjalankan perintah yang bersifat berat secara berulang, misalnya:

PERINGATAN!
Skrip di bawah akan membuat CPU usage PostgreSQL tinggi, gunakan di lingkungan development/test, bukan production.


CREATE OR REPLACE PROCEDURE cpu_hog()
LANGUAGE plpgsql
AS $$
DECLARE
    start_time timestamp := clock_timestamp();
    elapsed interval;
    i integer := 0;
    j double precision := 0;
BEGIN
    LOOP
        j := sqrt(i * random());
        i := i + 1;

        elapsed := clock_timestamp() - start_time;
        EXIT WHEN elapsed >= interval '20 seconds';
    END LOOP;
END;
$$;
 

.::: Shell Script Kasir cashier with & Without MariaDB :::.

 

A. Simple Cashier without Database

1. Struktur File listbarang.txt

2. Script: kasir.sh
 
[root@teguhth program]# cat kasir.sh

#!/bin/bash

file_barang="listbarang.txt"

# Fungsi untuk menampilkan daftar barang
tampilkan_barang() {
    echo -e "\nDaftar Barang:"
    echo "---------------------------"
    while IFS="|" read -r id nama harga
    do
        echo "$id. $nama - Rp$harga"
    done < "$file_barang"
    echo "---------------------------"
}

.::: Quary to simulation High CPU & High Memory Usage In MySQL MariaDB :::.

 

A. Simulation High CPU

1. Contoh untuk Simulasi CPU Tinggi
 
Untuk mensimulasikan CPU utilization tinggi di SQL Server menggunakan T-SQL, kamu bisa menjalankan perintah yang bersifat berat secara berulang, misalnya:

PERINGATAN!
Skrip di bawah akan membuat CPU usage SQL Server tinggi, gunakan di lingkungan development/test, bukan production.

USE teguhth;
DELIMITER $$

DROP PROCEDURE IF EXISTS CPU_HOG$$

CREATE PROCEDURE CPU_HOG()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE j DOUBLE DEFAULT 0;

    WHILE i < 10000000 DO
        SET j = SQRT(i * RAND());
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

.::: Quary T-SQL to simulation High CPU & High Memory Usage In SQL Server :::.

 

A. Simulation High CPU

1. Contoh T-SQL untuk Simulasi CPU Tinggi
 
Untuk mensimulasikan CPU utilization tinggi di SQL Server menggunakan T-SQL, kamu bisa menjalankan perintah yang bersifat berat secara berulang, misalnya:

PERINGATAN!
Skrip di bawah akan membuat CPU usage SQL Server tinggi, gunakan di lingkungan development/test, bukan production.

USE tempdb;
GO

IF OBJECT_ID('dbo.CPU_HOG') IS NOT NULL
    DROP PROCEDURE dbo.CPU_HOG;
GO

CREATE PROCEDURE dbo.CPU_HOG
AS
BEGIN
    DECLARE @i INT = 0;
    DECLARE @j FLOAT = 0;

    WHILE (@i < 10000000)
    BEGIN
        SET @j = SQRT(@i * RAND()); -- operasi matematika berat
        SET @i = @i + 1;
    END
END;
GO
 

.::: Insert data table to another table with different Database in Oracle Database :::.

 

A. Without Date

1. Check

select * from pembelian;
 

2. table after create
CREATE TABLE pembelian_clone (
    KODE_PEMBELIAN     CHAR(10) NOT NULL,
    KODE_BARANG        CHAR(6) NOT NULL,
    KODE_CUSTOMER      CHAR(6) NOT NULL,
    TANGGAL_PEMBELIAN  DATE DEFAULT NULL,
    JUMLAH_PEMBELIAN   DECIMAL(4,0) DEFAULT NULL
);
 

.::: Insert data table to another table with different Database in PostgreSQL EDB :::.

 

A. Without Date

1. Check

select * from pembelian;
 

2. table after create
CREATE TABLE pembelian_clone (
    KODE_PEMBELIAN     CHAR(10) NOT NULL,
    KODE_BARANG        CHAR(6) NOT NULL,
    KODE_CUSTOMER      CHAR(6) NOT NULL,
    TANGGAL_PEMBELIAN  DATE DEFAULT NULL,
    JUMLAH_PEMBELIAN   DECIMAL(4,0) DEFAULT NULL
);
 

.::: Insert data table to another table with different Database in MariaDB :::.

 

A. Without Date

1. Check

select * from teguhth.pembelian;
 
2. table after create
CREATE TABLE pembelian_clone (
    KODE_PEMBELIAN CHAR(10) NOT NULL,
    KODE_BARANG CHAR(6) NOT NULL,
    KODE_CUSTOMER CHAR(6) NOT NULL,
    TANGGAL_PEMBELIAN DATE DEFAULT NULL,
    JUMLAH_PEMBELIAN DECIMAL(4,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 

.::: Setting up and using the mutt email client in Linux for alert NMS or Other :::.

 

Berikut contoh penggunaan mutt untuk mengirim email dari command line di CentOS 9:

A. Setup

1. update repository

sudo dnf update -y

2. setup mutt

sudo dnf install mutt -y

3. check

mutt -v
 

.::: how to use DBMS_CRYPTO.ENCRYPT dan DBMS_CRYPTO.DECRYPT in Oracle as example table pembelian :::.

 

Oke! Saya akan kasih kamu contoh penggunaan DBMS_CRYPTO.ENCRYPT dan DBMS_CRYPTO.DECRYPT pada tabel pembelian. Di sini, kita akan menyimpan kolom credit_card_number dalam bentuk terenkripsi, dan mendekripsinya saat dibaca.

Studi Kasus: Tabel pembelian_card

1. grant access

GRANT EXECUTE ON DBMS_CRYPTO TO admin;
commit;

2. Buat tabel:

CREATE TABLE pembelian_card (
    id               NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nama_pembeli     VARCHAR2(100),
    credit_card_enc  RAW(2000) -- data terenkripsi
);

.::: Backup restore Export import expdp impdp from schema to another schema in oracle using crontab :::.

 

correlation https://teguhth.blogspot.com/2024/12/backup-restore-export-import-expdb.html


1. backup manual

[oracle@teguhth ~]$ expdp aisyah/hanin@localhost:1521/tgh DIRECTORY=DATA_PUMP_DIR DUMPFILE=aisyah_schema.dmp LOGFILE=aisyah_schema.log SCHEMAS=aisyah

Export: Release 19.0.0.0.0 - Production on Tue Apr 22 14:10:38 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "AISYAH"."SYS_EXPORT_SCHEMA_01":  aisyah/********@localhost:1521/tgh DIRECTORY=DATA_PUMP_DIR DUMPFILE=aisyah_schema.dmp LOGFILE=aisyah_schema.log SCHEMAS=aisyah
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "AISYAH"."BARANG"                           6.984 KB       6 rows
. . exported "AISYAH"."CUSTOMER"                         7.554 KB       6 rows
. . exported "AISYAH"."EMPLOYEES"                        6.359 KB       2 rows
. . exported "AISYAH"."EMPLOYEES_CHANGE_LOG"             8.312 KB       5 rows
. . exported "AISYAH"."PASOK"                            7.695 KB      12 rows
. . exported "AISYAH"."PEMBELIAN"                        7.835 KB      15 rows
. . exported "AISYAH"."SUPLIER"                          7.578 KB       6 rows
Master table "AISYAH"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for AISYAH.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/backup/aisyah_schema.dmp
Job "AISYAH"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Apr 22 14:11:39 2025 elapsed 0 00:00:58

[oracle@teguhth ~]$
 

.::: Enable Change Data Capture (CDC) Oracle Database using TRIGGER with session_user & client_ip :::.

 

Berikut adalah langkah-langkah untuk mengaktifkan Change Data Capture (CDC) di Oracle menggunakan Trigger, termasuk contoh tabel, isi tabel, dan implementasi trigger untuk melacak perubahan data:

1. Membuat Tabel Sumber
Tabel ini adalah tabel utama tempat data disimpan, dan perubahan pada tabel ini akan dilacak.

CREATE TABLE EMPLOYEES (
    EMP_ID NUMBER PRIMARY KEY,
    EMP_NAME VARCHAR2(50),
    DEPARTMENT_ID NUMBER,
    SALARY NUMBER
);
 

.::: How to enable CDC (Change Data Capture) in SQL Server Using Trigger with client_ip :::.

 

Berikut adalah implementasi Change Data Capture (CDC) menggunakan trigger di SQL Server, termasuk contoh tabel, data awal, serta operasi INSERT, UPDATE, dan DELETE.

correlation  with http://teguhth.blogspot.com/2022/11/how-to-enable-cdc-change-data-capture.html
https://teguhth.blogspot.com/2024/12/how-to-enable-cdc-change-data-capture.html
 
1. Membuat Tabel Utama
Misalkan tabel utama bernama Products:

CREATE TABLE Products (
    ProductID INT IDENTITY PRIMARY KEY,
    ProductName NVARCHAR(100),
    Price DECIMAL(10, 2),
    Stock INT
);

.::: Query Check user session, current, Client IP in MariaDB MySQL, PostgreSQL EDB, MSSQL SQL Server, Oracle :::.

 

1. MariaDB MySQL

select USER();
 

2. PostgreSQL

SELECT session_user, CURRENT_USER || '@' || host(inet_client_addr()) AS user_with_ip;  
SELECT CURRENT_USER || '@' || host(inet_client_addr()) AS user_with_ip;
SELECT COALESCE(host(inet_client_addr()), 'localhost') AS ip_only;
SELECT session_user,CURRENT_USER || '@' || inet_client_addr() AS user_with_ip;

.::: How to enable CDC (Change Data Capture) in PostgreSQL, EDB with user,ip client :::.

 

correlation https://teguhth.blogspot.com/2024/04/how-to-enable-cdc-change-data-capture.html

1. Enable WAL (Write-Ahead Logging): PostgreSQL uses Write-Ahead Logging for durability. Ensure that your PostgreSQL server is configured to use WAL.

2. Create Audit Table: Create an audit table where you will log the changes.

CREATE TABLE audit_table (
    id SERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    action_type TEXT NOT NULL,
    old_data JSONB,
    new_data JSONB,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    performed_by VARCHAR(100),
    ip_client TEXT NOT NULL
);

.::: How to enable CDC (Change Data Capture) in MariaDB, MySQL, MarinaDB in Table with user() information :::.

 

correlation https://teguhth.blogspot.com/2024/03/how-to-enable-cdc-change-data-capture.html


Enable Binary Logging
1. check table for sample

SELECT *,@@hostname,@@version FROM teguhth.pembelian
 
2. Create table for cdc

# create table

CREATE TABLE audit_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(100),
    action_type ENUM('INSERT', 'UPDATE', 'DELETE'),
    old_data JSON,
    new_data JSON,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    performed_by VARCHAR(100)
);

ALTER TABLE audit_table
ADD COLUMN performed_by VARCHAR(100);

SELECT *,@@hostname,@@version FROM audit_table;

.::: Insert data table to another table with different Database in SQL Server :::.

 

  A. Without Date

1. Check

select * from teguhth..pembelian
 

2. create table & insert firstime

-- pertama kali insert table
select * into dwh..pembelian_clone from teguhth..pembelian