.::: Create tablespace & add new tablespace in schema Oracle 19C :::.

 

Berikut adalah langkah-langkah untuk membuat tablespace bernama teguhthspace dan membuat tabel pada schema teguhth menggunakan tablespace tersebut:

1. Buat Tablespace teguhthspace

CREATE TABLESPACE teguhthspace
DATAFILE '/u01/tgh/teguhthspace_1.dbf'
SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

Penjelasan:

SIZE 100M: Ukuran awal file data adalah 100 MB.
AUTOEXTEND ON NEXT 10M: File akan otomatis bertambah 10 MB setiap kali diperlukan.
MAXSIZE UNLIMITED: Tidak ada batas ukuran maksimum untuk file data.
EXTENT MANAGEMENT LOCAL: Tablespace dikelola secara lokal, yang lebih efisien.
 

.::: Backup restore Export import expdb impdb from schema to another schema in oracle :::.

 

1. create another schema & check

CREATE USER aisyah_training IDENTIFIED BY hanin;
GRANT CONNECT, RESOURCE,oem_monitor TO aisyah_training;
ALTER USER aisyah_training QUOTA UNLIMITED ON USERS;

-- Membuat user aisyah_training dengan password oracle
CREATE USER aisyah_training IDENTIFIED BY hanin;

-- Memberikan hak akses dasar kepada user aisyah_training
GRANT CONNECT, RESOURCE,oem_monitor TO aisyah_training;

-- Memberikan hak akses untuk melakukan aisyah_trainingistrasi
GRANT CREATE SESSION TO aisyah_training;
GRANT UNLIMITED TABLESPACE TO aisyah_training;
GRANT DBA TO aisyah_training;

-- Memberikan hak akses untuk membaca data dictionary (opsional)
GRANT SELECT ANY DICTIONARY TO aisyah_training;

SELECT username, account_status, created,expiry_date, profile,last_login
FROM dba_users where inherited='NO'
 

.::: How to backup and restore table & database Oracle using import (impdb) & export (expdp) backup full, pertable in Oracle 19C, 21C :::.

 

A. Create DATA_PUMP_DIR or dba_directories

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/home/oracle/backup';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO aisyah;

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/home/oracle/backup';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO ADMIN;

CREATE OR REPLACE DIRECTORY BACKUP_DAILY AS '/u01/backup/daily';
GRANT READ, WRITE ON DIRECTORY BACKUP_DAILY TO aisyah;

SELECT directory_name, directory_path
FROM dba_directories
 

.::: Query to Check Schema, table Oracle Database include Last Login, Create Date & update User, object, table :::.

 

Untuk memeriksa schema yang ada di database Oracle, Anda dapat menggunakan beberapa perintah SQL. Berikut langkah-langkahnya:

1. Gunakan SQL*Plus atau Oracle SQL Developer
Login ke database menggunakan akun dengan akses DBA, seperti system atau user lain yang memiliki hak istimewa.

2. Lihat Daftar Semua Schema
Gunakan query ini untuk melihat semua schema (user) di database:

SELECT *
FROM dba_users
ORDER BY username;
 

.::: Sample Database ERP (Enterprise Resource Planning) and sample query using Join in MSSQL :::.

 

Berikut ini adalah contoh pembuatan database ERP yang lebih sederhana dengan fokus pada customers dan products, serta tabel tambahan orders untuk mengaitkan data pelanggan dengan produk yang mereka pesan. Setiap tabel akan diisi dengan minimal 10 baris data.

1. Create Database ERP
CREATE DATABASE TEGUHTH_ERPDB;
USE TEGUHTH_ERPDB;

2. Create Tabel customers
CREATE TABLE customers (
    customer_id INT IDENTITY(1,1) PRIMARY KEY,
    customer_name NVARCHAR(100) NOT NULL,
    email NVARCHAR(100) UNIQUE,
    phone NVARCHAR(15),
    address NVARCHAR(MAX),
    created_at DATETIME DEFAULT GETDATE()
);

.::: Sample Database ERP (Enterprise Resource Planning) and sample query using Join in Oracle Database :::.

Berikut ini adalah contoh pembuatan database ERP yang lebih sederhana dengan fokus pada customers dan products, serta tabel tambahan orders untuk mengaitkan data pelanggan dengan produk yang mereka pesan. Setiap tabel akan diisi dengan minimal 10 baris data.

1. Create Database ERP
CREATE DATABASE TEGUHTH_ERPDB;
USE TEGUHTH_ERPDB;

2. Create Tabel customers
CREATE TABLE customers (
    customer_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_name VARCHAR2(100) NOT NULL,
    email VARCHAR2(100) UNIQUE,
    phone VARCHAR2(15),
    address CLOB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

.::: Fine-Grained Audit (FGA) sample Oracle Audit & Compare with Standart Audit :::.

 

Fine-Grained Audit (FGA) in Oracle Database allows auditing of specific data access based on defined conditions. For example, it monitors activity only when sensitive data or specific rows/columns are accessed. This targeted auditing ensures better data security and minimizes unnecessary logs by focusing on high-risk scenarios.

correlation https://teguhth.blogspot.com/2024/11/enable-oracle-19c-unified-auditing-or.html
 

 

.::: Install XRDP (Remote Dekstop for Linux) :::.

 

1. Instal xRDP:

sudo dnf install epel-release -y
sudo dnf install xrdp -y

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

 

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
 
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
);
 

.::: Enable Change Data Capture (CDC) Oracle Database using TRIGGER :::.

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
);
 

.::: Enable Oracle 19C Unified Auditing or Oracle Audit like SQL Audit, MariaDB Audit, pgaudit & disable Permanent :::.

 

Unified Auditing in Oracle Database 19c is a security feature designed to streamline and enhance auditing capabilities by consolidating all types of audits (traditional and unified) into a centralized and efficient architecture. This approach simplifies audit management, storage, and analysis

A. Setup Oracle Audit

1. Check Unified Auditing
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

2. Activate Unified Auditing

SHUTDOWN IMMEDIATE;

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle

startup

or

/etc/ini.d/dboracle start
 

.::: Check user, priviledge & how to copy user to another server in Oracle Database :::.

 

1. Sample user aisyah

SELECT * FROM DBA_USERS WHERE USERNAME = 'AISYAH';
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'AISYAH';
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'AISYAH';
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'AISYAH';
SELECT * FROM DBA_TS_QUOTAS WHERE USERNAME = 'AISYAH';

 
 

.::: Allow user oracle access to another user /schema :::.

Agar user aisyah dapat mengakses semua objek dalam schema adminx dengan hak istimewa yang sama seperti yang dimiliki adminx, Anda perlu memberikan akses eksplisit untuk semua objek, atau Anda bisa memberikan peran penuh pada schema tersebut. Berikut langkah-langkahnya:

1. Berikan Hak Akses untuk Seluruh Objek Schema adminx
Gunakan perintah GRANT untuk memberikan akses ke semua objek milik adminx kepada aisyah.

SELECT 'GRANT ALL ON ADMIN.' || object_name || ' TO AISYAH' AS grant_command
FROM all_objects
WHERE owner = 'ADMIN' AND object_type IN ('TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION')
 

.::: Create user Oracle Database 19C & Priviledge admin & specific table :::

1. create user & priviledge admin

CREATE USER admin IDENTIFIED BY oracle;
GRANT CONNECT, RESOURCE,oem_monitor TO admin;
ALTER USER admin QUOTA UNLIMITED ON USERS;

-- Membuat user admin dengan password oracle
CREATE USER admin IDENTIFIED BY oracle;

-- Memberikan hak akses dasar kepada user admin
GRANT CONNECT, RESOURCE,oem_monitor TO admin;

-- Memberikan hak akses untuk melakukan administrasi
GRANT CREATE SESSION TO admin;
GRANT UNLIMITED TABLESPACE TO admin;
GRANT DBA TO admin;

-- Memberikan hak akses untuk membaca data dictionary (opsional)
GRANT SELECT ANY DICTIONARY TO admin;
 

.::: Drop table default MB_, MD_, MIGR_ in Oracle Database & cleansing RECYCLEBIN :::.

 

1. Check table
SELECT *
FROM user_tables
WHERE table_name LIKE 'MB_%' OR table_name LIKE 'MIGR_%' OR table_name LIKE 'MD_%';
 

2. create script drop table by filter MB_ &  MIGR_
SELECT 'DROP TABLE ' || table_name || ' CASCADE CONSTRAINTS;' AS drop_command
FROM user_tables
WHERE table_name LIKE 'MB_%' OR table_name LIKE 'MIGR_%';

.::: Simulation Deadlocks MariaDB MySQL :::

 

1. create table & insert
CREATE TABLE clients (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    balance DECIMAL(10, 2)
);

-- Memasukkan data awal ke tabel 'clients'
INSERT INTO clients (id, name, balance) VALUES
(1, 'John', 50),
(2, 'Alice', 150);

2. Memulai transaksi terminal 1
START TRANSACTION;

-- Melakukan update pada saldo (balance) untuk id = 1
UPDATE clients SET balance = balance + 50 WHERE id = 1;

.:: Extract backup mysqldump to convert only table specify in PostgreSQL EDB :::.

A. Extract backup from file backup

1. Backup full database
pg_dump teguhth  > teguhth_singledb.sql

2. extract table only specific table (example barang)
---yang benar
sed -n '/-- Name: barang; Type: TABLE; Schema: public; Owner: postgres/,/ALTER TABLE public.barang OWNER TO postgres;/p' teguhth_singledb.sql > barang_filtered.sql

sed -n '/-- Name: barang barang_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres/,/);/p' teguhth_singledb.sql >> barang_filtered.sql

sed -n '/-- Data for Name: barang; Type: TABLE DATA; Schema: public; Owner: postgres/,/\\\./p' teguhth_singledb.sql >> barang_filtered.sql

.:: Extract backup mysqldump to convert only table specify (Sample Pembelian) in MySQL MariaDB :::.

A. Extract backup from file backup

1. Backup full database

mysqldump -u root -p teguhth  > table_teguhth.sql ;

2. extract table only specific table (example barang)

sed -n -e '/DROP TABLE.*`barang`/,/UNLOCK TABLES/p' table_teguhth.sql > /data/pertable/barang_only.sql

.::: How to Sample Simulation Deadlock Oracle Database & Trouble shoot & check running query process :::.

Untuk mensimulasikan deadlock di tabel locking_test di Oracle, kita perlu menjalankan dua sesi (dua transaksi) secara bersamaan, di mana setiap sesi saling mengunci resources (baris) yang akan diakses oleh sesi lainnya.

Berikut adalah langkah-langkah simulasi deadlock di Oracle menggunakan tabel locking_test:

Langkah 1: Buat Tabel dan Data
Jalankan skrip ini terlebih dahulu untuk membuat tabel dan menambahkan data awal:


CREATE TABLE locking_test (
    id NUMBER PRIMARY KEY,
    data VARCHAR2(50)
);

INSERT INTO locking_test (id, data) VALUES (1, 'Initial Data');
INSERT INTO locking_test (id, data) VALUES (2, 'Second Data');
COMMIT;

.::: How to Sample Simulation Blocking Locking Oracle Database & Trouble shoot & check running query process :::.

 
1. create table sample


CREATE TABLE locking_test (
    id NUMBER PRIMARY KEY,
    data VARCHAR2(50)
);

INSERT INTO locking_test (id, data) VALUES (1, 'Initial Data');
commit;

2. open 2 or 3 terminal

Buka Dua Sesi di SQL*Plus atau Alat Lainnya
Anda perlu dua sesi terpisah (misalnya, di SQL*Plus atau alat database lain seperti SQL Developer) untuk mensimulasikan locking. Sesi 1 akan melakukan UPDATE tanpa melakukan COMMIT atau ROLLBACK, sehingga menahan lock di baris tersebut. Sesi 2 akan mencoba melakukan operasi yang sama dan akan mengalami blocking sampai lock dilepas.

.::: Install Oracle 12C in Centos 8 :::.

 1. Update Sistem <optional>

sudo dnf update -y

2. Install package

sudo dnf install -y binutils compat-libcap1 compat-libstdc++-33 gcc gcc-c++ glibc glibc-devel ksh libaio libaio-devel libX11 libXau libXi libXtst libXrender libXrender-devel libgcc libstdc++ libstdc++-devel libxcb make smartmontools sysstat

3. Create user & Group

sudo groupadd oinstall
sudo groupadd dba
sudo useradd -g oinstall -G dba oracle
sudo passwd oracle

.::: Sample Database ERP (Enterprise Resource Planning) and sample query using Join in PostgreSQL EDB :::.

Berikut ini adalah contoh pembuatan database ERP yang lebih sederhana dengan fokus pada customers dan products, serta tabel tambahan orders untuk mengaitkan data pelanggan dengan produk yang mereka pesan. Setiap tabel akan diisi dengan minimal 10 baris data.

A. Sample DB ERP

1. Create Database ERP
CREATE DATABASE TEGUHTH_ERPDB;
USE TEGUHTH_ERPDB;

2. Create Tabel customers
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(15),
    address TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

.::: Sample Database ERP (Enterprise Resource Planning) and sample query using Join in MariaDB MySQL :::.

Berikut ini adalah contoh pembuatan database ERP yang lebih sederhana dengan fokus pada customers dan products, serta tabel tambahan orders untuk mengaitkan data pelanggan dengan produk yang mereka pesan. Setiap tabel akan diisi dengan minimal 10 baris data.

1. Create Database ERP
CREATE DATABASE TEGUHTH_ERPDB;
USE TEGUHTH_ERPDB;

2. Create Tabel customers
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(15),
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

.::: Check Date when Microsoft SQL Server (MSSQL), MariaDB MySQL & PostgreSQL Installed & Upgraded :::.

1. MSSQL

 
SELECT @@servername as ServerName,SERVERPROPERTY('productversion') as ProductVersion
      ,SERVERPROPERTY ('productlevel') as ProductLevel
      ,SERVERPROPERTY ('edition') as Edition
      ,SERVERPROPERTY ('MachineName') as MachineName
      ,SERVERPROPERTY ('LicenseType') as LicenseType
      ,SERVERPROPERTY ('NumLicenses') as NumLicenses
      ,create_date as 'SQLInstallDate'
      ,(select modify_date from sys.server_principals where name like '%##MS_SQLAuthenticatorCertificate##%') as SQLUpgradeDate
FROM sys.server_principals
WHERE name='NT AUTHORITY\SYSTEM';
 

2. MariaDB MySQL

.::: Backup All User, Specify User , Permission, Roles, Server Roles in SQL Server :::.

 
Untuk melakukan backup user dan privilege di SQL Server, Anda bisa menggunakan perintah Transact-SQL (T-SQL) untuk mengekstrak informasi tentang pengguna (user) dan hak akses (privilege). Berikut langkah-langkah yang dapat dilakukan:

1. Backup User Login dan Mapping ke Database

Gunakan skrip di bawah ini untuk mencadangkan login pengguna dan mappingnya ke database.
Output dari skrip ini akan memberikan Anda perintah untuk membuat ulang login yang sudah ada. Perhatikan bahwa password tidak dapat diekstrak, jadi Anda perlu memasukkannya secara manual saat melakukan restore.

-- Backup logins
SELECT @@servername as ServerName,'CREATE LOGIN [' + l.name + '] '
    + CASE
        WHEN l.type_desc = 'SQL_LOGIN' THEN 'WITH PASSWORD = ''<password>'', '
        WHEN l.type_desc = 'WINDOWS_LOGIN' THEN 'FROM WINDOWS, '
        ELSE ''
      END
    + ' DEFAULT_DATABASE=[' + l.default_database_name + ']'
    + CASE
        WHEN l.is_disabled = 1 THEN ', DISABLE;'
        ELSE ';'
      END AS CreateLoginScript
FROM sys.server_principals l
WHERE l.type IN ('S', 'U');  -- SQL Logins (S) and Windows Logins (U)
 

.::: Enable collect MariaDB User login Connect & Disconnect include prviledge using MariaDB Audit :::.

1. enable MariaDB Audit

http://teguhth.blogspot.com/2022/08/enable-server-audit-in-mariadb-mysql.html

2. convert to table

http://teguhth.blogspot.com/2022/09/load-log-file-insert-into-table-in.html

3. check filter tbl_mariadb_audit to filter connect & disconnect

MariaDB [teguhthtools]> select * from tbl_mariadb_audit where type ='connect' or type ='disconnect';
+-------------------+---------+--------+-----------+------+---------------+------------+------+---------+--------+
| date              | host    | userdb | client    | id   | connection_id | type       | db   | sqltext | status |
+-------------------+---------+--------+-----------+------+---------------+------------+------+---------+--------+
| 20240822 07:52:09 | teguhth | admin  | teguhth   |    4 |             0 | CONNECT    |      |         | 0      |
| 20240822 07:52:32 | teguhth | admin  | teguhth   |    4 |             0 | DISCONNECT |      |         | 0      |
+-------------------+---------+--------+-----------+------+---------------+------------+------+---------+--------+
3 rows in set (0.001 sec)

MariaDB [teguhthtools]>

.::: Script Broadcast Query in EDB / PostgreSQL to Multple Database in Linux :::.

1. create database sample
create database db_klt_teguh;
create database db_klt_teguh_dmart;
create database db_klt_teguh_archive;

create database db_bgr_tri;
create database db_bgr_tri_dmart;
create database db_bgr_tri_archive;

create database db_lpg_raju;
create database db_lpg_raju_dmart;
create database db_lpg_raju_archive;

.::: Script Broadcast Query in MariaDB MySQL to Multple Database in Linux :::.

1. create database sample
create database db_klt_teguh;
create database db_klt_teguh_dmart;
create database db_klt_teguh_archive;

create database db_bgr_tri;
create database db_bgr_tri_dmart;
create database db_bgr_tri_archive;

create database db_lpg_raju;
create database db_lpg_raju_dmart;
create database db_lpg_raju_archive;

.::: How to enable the MySQL/MariaDB general query logs :::.

1. enable general log temporary

SET GLOBAL general_log_file='/var/lib/mysql/general.log';
SET GLOBAL log_output = 'FILE,TABLE';
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log = 1;
 

.::: Create User Access MariaDB MySQL using PAM example OS Linux Login :::.

 
1. install pam plugin

INSTALL PLUGIN pam SONAME 'auth_pam';

MariaDB [(none)]> INSTALL PLUGIN pam SONAME 'auth_pam';
ERROR 1968 (HY000): Plugin 'pam' already installed
MariaDB [(none)]>

2. check plugin pam

show plugins
SELECT plugin_name,plugin_version,plugin_status,plugin_type,plugin_library,plugin_author FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%pam%';

.::: Script Backup Full + Differential + Transaction Log in MariaDB MySQL & Scheduler just like SQL Server for SingleDB & MultipleDB include Restore in One File Script :::.

 
A. SingleDB

1. run script one time to full backup

sh /data/script/scriptbackup/backup_full_log_single_db.sh
 

2. run script two time to log backup

sh /data/script/scriptbackup/backup_full_log_single_db.sh

.::: How to configure Transparent Data Encryption (TDE), Monitoring & Remove TDE in SQL Server :::.

A. Create TDE

1. Create Master Key

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='TDEpassword!';
GO
 
 
2. Create Certificate protected by master key
CREATE CERTIFICATE TDE_Cert
WITH
SUBJECT='Database_Encryption';
GO

.::: Additional Environment PostgreSQL EDB example pg_ctl: command not found :::.

 1. Run pg_ctl

pg_ctl
[root@teguhth ~]# su - postgres
Last login: Sun May 12 08:18:13 WIB 2024 on pts/1
-bash-4.2$ pg_ctl -D /var/lib/pgsql/15/data status
-bash: pg_ctl: command not found
-bash-4.2$
-bash-4.2$ pg_ctl -D /var/lib/pgsql/15/data start
-bash: pg_ctl: command not found
-bash-4.2$
-bash-4.2$ pg_ctl -D /var/lib/pgsql/15/data stop
-bash: pg_ctl: command not found
-bash-4.2$
-bash-4.2$

.::: Enable userstat USER_STATISTICS, CLIENT_STATISTICS, INDEX_STATISTICS, and the TABLE_STATISTICS MariaDB MySQL :::.

A. Enable Statistic Plugin

1. Check configuration statistic
From query
show variables like '%userstat%';

From mariadb configuration
# cat /etc/my.cnf.d/server.cnf | grep userstat

 

2. Enable userstat configuration From Query ( for temporary configuration)

SET GLOBAL userstat=1;
show variables like 'userstat';

.::: Backup & Restore Database MongoDB Using Mongodump & mongorestore :::.

Step 1: Create Direct Backups Using Mongodump

mongodump
 
[root@teguhth ~]# mongodump
2024-04-15T14:59:40.783+0700    writing admin.system.users to dump/admin/system.users.bson
2024-04-15T14:59:40.789+0700    done dumping admin.system.users (3 documents)
2024-04-15T14:59:40.790+0700    writing admin.system.version to dump/admin/system.version.bson
2024-04-15T14:59:40.790+0700    done dumping admin.system.version (2 documents)
2024-04-15T14:59:40.792+0700    writing teguhth.pembelian to dump/teguhth/pembelian.bson
2024-04-15T14:59:40.793+0700    writing teguhth.pasok to dump/teguhth/pasok.bson
2024-04-15T14:59:40.794+0700    writing teguhth.suplier to dump/teguhth/suplier.bson
2024-04-15T14:59:40.795+0700    writing kampus.mahasiswi to dump/kampus/mahasiswi.bson
2024-04-15T14:59:40.797+0700    done dumping teguhth.pembelian (15 documents)
2024-04-15T14:59:40.798+0700    writing teguhth.barang to dump/teguhth/barang.bson
2024-04-15T14:59:40.799+0700    done dumping teguhth.pasok (12 documents)
2024-04-15T14:59:40.800+0700    writing teguhth.customer to dump/teguhth/customer.bson
2024-04-15T14:59:40.801+0700    done dumping kampus.mahasiswi (8 documents)
2024-04-15T14:59:40.801+0700    writing tka.barang to dump/tka/barang.bson
2024-04-15T14:59:40.802+0700    done dumping teguhth.barang (6 documents)
2024-04-15T14:59:40.803+0700    writing tka.Barang to dump/tka/Barang.bson
2024-04-15T14:59:40.803+0700    done dumping teguhth.customer (6 documents)
2024-04-15T14:59:40.806+0700    done dumping tka.barang (6 documents)
2024-04-15T14:59:40.807+0700    done dumping teguhth.suplier (6 documents)
2024-04-15T14:59:40.808+0700    done dumping tka.Barang (0 documents)
[root@teguhth ~]#

.::: How to enable CDC (Change Data Capture) in PostgreSQL, EDB :::.

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
);
 

.::: Convert pgaudit & pgauditlogtofile log insert into table in PostgreSQL EDB :::.

 continue from http://teguhth.blogspot.com/2024/02/enable-pgaudit-pgauditlogtofile-in.html

1. Create table pgaudit_log

CREATE TABLE pgaudit_log (
log_time text,
user_name text,
dbname text,
process_id text,
connection_from text,
session_id text,
session_line_num text,
command_tag text,
session_start_time text,
virtual_transaction_id text,
transaction_id text,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos text,
context text,
query text,
query_pos text,
location text,
application_name text,
backend_type text,
leader_pid text,
query_id text,
custom_1 text,
custom_2 text,
custom_3 text,
console text
);
 

.::: Check Status Backup & Restore Progress in % Percent MSSQL SQL Server :::.

A. Backup

1. Query 1

SELECT @@servername as ServerName, command, percent_complete,total_elapsed_time, estimated_completion_time, start_time
  FROM sys.dm_exec_requests
  WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

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

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
);

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

.::: Script check cpu usage & memory usage & Total Memory Server SQL Server include using sqlcmd to monitoring NMS :::.

 1. create store procedure cpu monitoring sql server for monitoring

CREATE PROCEDURE sp_total_usage_cpu_SQLProcessUtilization

AS
BEGIN
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

SELECT TOP(1)
               100 - SystemIdle - SQLProcessUtilization + SQLProcessUtilization AS [Total Usage CPU SQL Utilization]
  

.::: Script to check cpu & memory usage service in linux include custome service, mariadb, mysql, mongodb :::.

1. check custome cpu usage and memory usage

[root@teguhth special]# cat custom_service.sh

#!/bin/bash

echo "run custom_service.sh <name_service>"
PIDX=${1}
# Mendapatkan PID dari proses mariadbd
MARIADB_PID=$(pgrep $PIDX)

# Mendapatkan informasi tentang proses mariadbd dari top
TOP_INFO=$(top -n 1 -b -p "$MARIADB_PID" | tail -n +8 | head -n 1)
Mem_Total_mb=$(free -m | awk 'NR==2{printf "%.f", $2 }')
Mem_Total_gb=$(free -m | awk 'NR==2{printf "%.2f", $2/1024 }')

# Mengekstrak nilai CPU dan penggunaan memori dari output top
CPU_USAGE=$(echo "$TOP_INFO" | awk '{print $9}')
MEMORY_USAGE=$(echo "$TOP_INFO" | awk '{print $10}')
MEMORY_B=$(echo "$TOP_INFO" | awk '{print $6 }')

echo "CPU Usage(%) $PIDX   : ${CPU_USAGE}%"
echo "Memory Usage(%) $PIDX: ${MEMORY_USAGE}%"
echo "Memory Usage $PIDX   : ${MEMORY_B}"
echo "Memory Total Server   : $Mem_Total_mb MB or $Mem_Total_gb GB"

[root@teguhth special]#

.::: Study Kasus Praktis belajar Query create & insert table/Collection MongoDB (Data Warehouse) dengan Primary Key :::.

1. correlation with other post

http://teguhth.blogspot.com/2020/12/study-kasus-praktis-belajar-query.html

2. create collection with primarykey

use teguhth
show dbs

show collections
db.createCollection("barang")
db.createCollection("suplier")
db.createCollection("customer")
db.createCollection("pasok")
db.createCollection("pembelian")
show collections
 

.::: Create User MongoDB role admin and readonly and authenticationRestrictions :::.

1. create user admin

use admin
db.createUser({ user: "admin",pwd: "admin",
roles: [{ role: "readAnyDatabase", db: "admin" },{ role: "readWriteAnyDatabase", db: "admin" }]});
show users;

.::: Create a dblink to a remote server MariaDD MySQL to PostgreSQL EDB and using metode SRCDEF :::.

1. install ODBC postgresql * configure

http://teguhth.blogspot.com/2024/01/install-postgresql-odbc-driver-on-linux.html

A. using dblink

1. create table dblink example table barang
CREATE TABLE table_edb_barang(
KODE_BARANG char(6) not null ,
NAMA_BARANG varchar(25),
SATUAN_BARANG varchar(20),
STOK_BARANG decimal(4),
primary key (KODE_BARANG)) ENGINE = CONNECT TABLE_TYPE=ODBC TABNAME='barang' CONNECTION='DSN=edbdb';

.::: Access MariaDB from PostgreSQL using dblink /FOREIGN DATA WRAPPER mysql_fdw :::.

1. install mysql_fdw
wget https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-7.9-x86_64/mysql_fdw_15-2.8.0-2.rhel7.x86_64.rpm
yum install mysql_fdw_15-2.8.0-2.rhel7.x86_64.rpm -y

2. create extension;

teguhth=# \c edbmaria;
You are now connected to database "edbmaria" as user "postgres".
edbmaria=#

edbmaria=# CREATE EXTENSION mysql_fdw;
CREATE EXTENSION
edbmaria=#
SELECT * FROM pg_extension;
 

.::: Enable pgaudit & pgauditlogtofile in PostgreSQL EDB ::.

1. Download pgaudit & pgauditlogtofile
https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8.2-x86_64/
yum install -y https://rpmfind.net/linux/centos-stream/9-stream/AppStream/x86_64/os/Packages/pgaudit-16.0-1.module_el9+807+b1de07f3.x86_64.rpm
yum install -y https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-8.2-x86_64/pgauditlogtofile_16-1.5.12-1PGDG.rhel8.x86_64.rpm

2. check rpm & extension

[root@teguhth ~]# rpm -qa | grep -i postgres
postgresql16-server-16.0-1PGDG.rhel8.x86_64
postgresql16-contrib-16.0-1PGDG.rhel8.x86_64
postgresql16-16.0-1PGDG.rhel8.x86_64
postgresql16-libs-16.0-1PGDG.rhel8.x86_64
[root@teguhth ~]#

.::: How to Use backup & Restore Database MySQL MariaDB using MariaDB Backup :::.

 
A. Backup with mariabackup
1. install mariabackup

yum install -y MariaDB-backup -y

2. check data before testing

select * from teguhth.barang;
select * from teguhth.pembelian;
 

.::: Create dblink using dblink_connect & dblink in PostgreSQL EDB :::.

1. check before setting
select pg_read_file('/etc/hostname') as hostname, version();

psql -h 10.10.10.8 -U admin -p 5432 -d teguhth -c "select pg_read_file('/etc/hostname') as hostname, version();";
psql -h 10.10.10.71 -U admin -p 5444 -d teguhth -c "select pg_read_file('/etc/hostname') as hostname, version();";
 

2. create create new database dblink_edb <optional only case>
teguhth=# create database dblink_edb;
CREATE DATABASE
teguhth=#
 

.::: Create External Table PostgreSQL, EDB using Foreign Data Wrapper (postgres_fdw) like DBLink :::.

1. create new externaldb for sample
create database externaldb;
\c externaldb;

teguhth=# create database externaldb;
CREATE DATABASE
teguhth=# \c externaldb;
You are now connected to database "externaldb" as user "postgres".
externaldb=#

.::: Script Backup Full All Database in PostgreSQL EDB :::.

corelation with http://teguhth.blogspot.com/2023/08/backup-and-restore-postgresql-edb.html

 

 1. check data from table
select * from teguhth.public.pembelian;
select * from edb.public.barang;
 

2. run backup
su - postgres
sh backup_edb_full_script.sh

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

1. check before restore
2. check directory backup & create dblist,txt
ls > dblist.txt

[root@teguhth teguhth]# ls > dblist.txt
[root@teguhth teguhth]# cat dblist.txt
dblist.txt
[FULL]_teguhth_20240217_155359.sql.gz
[LOG]_teguhth_20240217_155414.sql.gz
[LOG]_teguhth_20240217_185039.sql.gz
[LOG]_teguhth_20240217_185041.sql.gz
read.sh
test.sh

.::: Restore Transaction Log Backup in MySQL MariaDB :::.

 
corelation with http://teguhth.blogspot.com/2024/02/script-backup-full-differential.html
and http://teguhth.blogspot.com/2023/09/set-up-incremental-backup-restore-in.html

1. backup full & transaction log
[root@teguhth fix]# sh backup_full_script.sh
[root@teguhth fix]# sh backup_log_script.sh
 

.::: Script Backup Full + Differential + Transaction Log in MariaDB MySQL & Scheduler just like SQL Server :::.

 

1. create script backup full

[root@teguhth fix]# cat backup_full_script.sh

echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address

infotgl=$(date)
dbversion=$(mysql -uroot -proot -sNe "select @@version as VersionDB");
echo ".::: Backup Full All database using custom in '$Server $dbversion' with $ip_address :::."
echo ""
#!/bin/bash
#
#clear;
year=`date +%Y`; month=`date +%m`; day=`date +%d`; hour=`date +%H`; min=`date +%M`; sec=`date +%S`; dayname=`date +%a`;underscore='_';
datex=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S" ");
#echo "Backup begin $infotgl"
 backuppath="/backup";
#mkdir -p $backuppath/$year-$month-$day;
#path="$backuppath/$year-$month-$day";
# Find the latest mysql-bin log file
LATEST_BINLOG=$(ls -1t /var/lib/mysql/mysql-bin.* | grep -v 'mysql-bin.index' | head -n 1)
 

.::: Create user limited to access only spesific table or specific view, store procedure,function in MSSQL SQL Server :::.

 
1. create user & password
USE [master]
GO
CREATE LOGIN [onlyone] WITH PASSWORD=N'admin', DEFAULT_DATABASE=[teguhth], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
use [master];
GO
USE [teguhth]
GO
CREATE USER [onlyone] FOR LOGIN [onlyone]
GO
use [teguhth];
GO
USE [teguhth]
GO
CREATE USER [onlyone] FOR LOGIN [onlyone]
GO
 

.::: Create table DbLink MariaDB MySQL using ODBC/DSN using metode SRCDEF base on query :::.

1. Install Connect Engine
yum install MariaDB-connect-engine -y

2. Check ODBC INI

[root@teguhth ~]# cat /etc/odbc.ini

[edbdb]
Description         = PostgreSQL connection to SallyDB
Driver              = PostgreSQL
Database            = teguhth
Servername          = 10.10.10.8
UserName            = admin
Password            = admin
Port                = 5432
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
#ConnSettings        =
 

.::: Install MariaDB ODBC Driver on Linux & test DSN or odbcins.ini and odbc.ini :::.

 
1. Install mariadb connector odbc
[root@teguhth ~]# yum install -y https://dlm.mariadb.com/3680404/Connectors/odbc/connector-odbc-3.1.20/mariadb-connector-odbc-3.1.20-centos74-amd64.rpm

2. check rpm odbc

[root@teguhth ~]# rpm -qa | grep -i odbc
msodbcsql18-18.3.2.1-1.x86_64
mariadb-connector-odbc-3.1.20-1.x86_64
postgresql-odbc-09.03.0100-2.el7.x86_64
unixODBC-devel-2.3.11-1.rh.x86_64
unixODBC-2.3.11-1.rh.x86_64
[root@teguhth ~]#

.::: Create SQL Audit in SQL Server MSSQL for example Schema Change & Permission Change :::.

 A. Schema Audit
1. Schema change Audit script

USE [master]
GO

/****** Object:  Audit [Schema_Change_Sample]    Script Date: 8/3/2022 6:08:55 PM ******/
CREATE SERVER AUDIT [Schema_Change_Sample]
TO FILE
(    FILEPATH = N'C:\audit\' ---- Change Audit file path
    ,MAXSIZE = 20 MB
    ,MAX_ROLLOVER_FILES = 5
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = 'e5abe6c5-f451-41f3-936c-b1da1c0786ce'
)
ALTER SERVER AUDIT [Schema_Change_Sample] WITH (STATE = ON)
GO

.::: Create user limited to access only spesific table or specific view, store procedure,function in MariaDB MySQL :::.

corelation http://teguhth.blogspot.com/2022/08/create-user-admin-readonly-database.html

A. create user only akses 1 table
1. create user only akses 1 table
CREATE USER 'onlyone'@'%' IDENTIFIED BY 'onlyone';
GRANT SELECT, INSERT, UPDATE, DELETE ON teguhth.pembelian TO 'onlyone'@'%';
ALTER USER 'onlyone'@'%' IDENTIFIED BY 'onlyone';

CREATE USER 'onlyone'@'localhost' IDENTIFIED BY 'onlyone';
GRANT SELECT, INSERT, UPDATE, DELETE ON teguhth.pembelian TO 'onlyone'@'localhost';
ALTER USER 'onlyone'@'localhost' IDENTIFIED BY 'onlyone';

FLUSH PRIVILEGES;
 

.::: Install PostgreSQL ODBC Driver on Linux & test DSN or odbcins.ini and odbc.ini Access PostgreSQL using ODBC :::.

1. install odbc postgres
yum install unixODBC unixODBC-devel postgresql-odbc -y

[root@teguhth data]# rpm -qa | grep -i odbc
msodbcsql18-18.3.2.1-1.x86_64
postgresql-odbc-09.03.0100-2.el7.x86_64
unixODBC-devel-2.3.11-1.rh.x86_64
unixODBC-2.3.11-1.rh.x86_64
[root@teguhth data]#
 

-bash-4.2$ cat postgresql.conf | grep -i listen_a
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses ='*'                                   # comma-separated list of addresses;
-bash-4.2$
-bash-4.2$ cat pg_hba.conf | grep -i admin
host   all   admin 10.0.0.0/8 md5
-bash-4.2$

.::: Change the file created date & Modified file in Linux :::.

 1. check size & time file
[root@teguhth data]# ls -lh
total 932K
drwxr-xr-x 3 root root   19 Jan 17 18:01 backup
-rw-r--r-- 1 root root 924K Jun 23  2023 psqlodbc-15.00.0000.tar.gz
-rw-r--r-- 1 root root 7.9K Jan 16 14:59 teguhth_original.sql
[root@teguhth data]#
[root@teguhth data]#
[root@teguhth data]# cp psqlodbc-15.00.0000.tar.gz psqlodbc-15.00.0000_test.tar.gz
[root@teguhth data]# ls -lh
total 1.9M
drwxr-xr-x 3 root root   19 Jan 17 18:01 backup
-rw-r--r-- 1 root root 924K Jun 23  2023 psqlodbc-15.00.0000.tar.gz
-rw-r--r-- 1 root root 924K Jan 29 10:15 psqlodbc-15.00.0000_test.tar.gz
-rw-r--r-- 1 root root 7.9K Jan 16 14:59 teguhth_original.sql
[root@teguhth data]#
[root@teguhth data]#
 

.::: Shell Script to Get CPU Memory Usage (%), Swap (%) & Local Check, all_check GTID Mirroring,IP & Hostname,uptime MariaDB in Centos 9 Redhat 9 Rocky Linux 9 :::.

 1. local check

##[root@teguhth data]# cat local_check.sh
#  cat check_cpu_mem_usage.sh
#!/bin/bash
date
CPU=$(top -bn1 | grep load | awk '{printf "%.2f%%\t\t\n", $(NF-2)}')

Mem_Used_mb=$(free -m | awk 'NR==2{printf "%.f", $3 }')
Mem_Total_mb=$(free -m | awk 'NR==2{printf "%.f", $2 }')

Mem_Used_kb=$(free -m | awk 'NR==2{printf "%.f", $3*1024 }')
Mem_Total_kb=$(free -m | awk 'NR==2{printf "%.f", $2*1024 }')

Mem_Used_gb=$(free -m | awk 'NR==2{printf "%.f", $3/1024 }')
Mem_Total_gb=$(free -m | awk 'NR==2{printf "%.f", $2/1024 }')

.::: Create a dblink to a remote server MariaDB MySQL MarinaDB in Linux to MSSQL SQL Server 2022 in Windows Server 2022 using ha_connect,unixODBC,odbcinst.ini :::.

A. Install & Configuration DBLink
1. install haconnect in Linux Redhat

before 

http://teguhth.blogspot.com/2024/01/install-configure-odbcinstini-odbc-for.html
yum install MariaDB-connect-engine -y

[root@teguhth ~]# yum install MariaDB-connect-engine -y
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.cepatcloud.id
 * epel: mirror2.totbb.net
 * extras: mirror.cepatcloud.id
 * updates: mirror.cepatcloud.id
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-connect-engine.x86_64 0:10.11.6-1.el7.centos will be installed
--> Finished Dependency Resolution
 

.::: Install & Configure odbcinst.ini ODBC for MSSQL SQL Server, Oracle, MySQL MariaDB, PostgreSQL in Linux Centos 7, 8, 9 :::.

 A. ODBC Linux for MSSQL
1. prefer link
https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver16&tabs=redhat18-install%2Calpine17-install%2Cdebian8-install%2Credhat7-13-install%2Crhel7-offline

#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version

.::: Create a link or dblink to a remote server MariaDD MySQL to MSSQL SQL Server using ha_connect :::.

 A. Configure dblink MariaDB
1. install plugin ha_connect
INSTALL SONAME 'ha_connect';

2. install driver Driver=SQL Server Native Client 11.0 or mssqlodbc

SQL Server Native Client 11.0
https://www.microsoft.com/en-us/download/details.aspx?id=50402

or
mssqlodbc
https://www.microsoft.com/en-us/download/details.aspx?id=36434

.::: Create sample multiple dblink MariaDB MySQL to run multiple query Inner Join, Normal Join :::.

1. correlation dblink

https://teguhth.blogspot.com/2024/01/create-link-or-dblink-to-remote-server.html

2. check normal query from remoteserver

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;

select @@hostname,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;
 

.::: Create a link or dblink to a remote server MariaDD MySQL using federated ha_federatedx.so plugin :::.

A. Sample other database to get database (source)
create database teguhth_maria_dwh;
use teguhth_maria_dwh;
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));

.::: Script to check File backup Database MariaDB MySQL , SQL Server MSSQL success or not in NAS or FS :::.

 1. sample for linux backup mariadb & windows sql server

db linux

db_mariadb_teguh;
db_mariadb_teguh_dmart;

db windows

db_mssql_teguh;
db_mssql_teguh_dmart;
 

.::: Create Quary Daily Backup All Database MariaDB MySQL & retention and Crontab :::.

1. Create script all backup using custom

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

infotgl=$(date)

echo ".::: Backup all database using custom in $Server with $ip_address :::."
echo ""
#!/bin/bash
#
#clear;
year=`date +%Y`; month=`date +%m`; day=`date +%d`; hour=`date +%H`; min=`date +%M`; sec=`date +%S`; dayname=`date +%a`;
echo "Backup begin $infotgl"
 

.::: How to migrate a SQL Server database to a lower version or Downgrade MSSQL 2022 to 2019 or lower ::.

As of my last knowledge update in January 2022, SQL Server 2022 had not been released. However, if you're using a newer version of SQL Server and want to downgrade to SQL Server 2019,
it's important to note that downgrading a SQL Server database is not a straightforward process. Microsoft does not support direct downgrades, and you may need to go through a series of steps to achieve this.
Here are the general steps you might follow:

A. check version & sample query before downgrade (SQL 2022)