.::: Script Shell to collect differential time using epoch & human time in Centos 8 :::.

 

A. Script dengan time

1. Pengurangan dalam format human time

[root@teguhth temp]# cat start_finish_time.sh
#!/bin/bash

# Menyimpan waktu mulai dalam format manusiawi
datex=$(date "+%Y-%m-%d %H:%M:%S.%N")
epoch_start=$(date +%s%N)
echo "trial time"

# Menyimpan waktu selesai dalam format manusiawi
datey=$(date "+%Y-%m-%d %H:%M:%S.%N")
epoch_end=$(date +%s%N)

.::: Script EXPDP Backup Daily Database Oracle 19C with Record to Table :::.

 

A. Create Backup with record table
1. Create DATA_PUMP_DIR backup directory

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

2. create schema (dbatools)

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

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

-- Memberikan hak akses untuk melakukan dbatoolsistrasi
GRANT CREATE SESSION TO dbatools;
GRANT UNLIMITED TABLESPACE TO dbatools;
GRANT DBA TO dbatools;
 

.::: Shell Script to create,insert data CRUD Oracle Database :::.

 

1. check version
sh check_version.sh
 
2. insert data
sh create_insert.sh

3. script check version

[oracle@teguhth blog]$ cat check_version.sh
#!/bin/bash

# Konfigurasi koneksi database
vlogin="aisyah/hanin@//10.10.10.19:1521/tgh"

# Menjalankan query SQL dan menyimpan output ke file
sqlplus -s "$vlogin" <<EOF > output_instance_info.txt
SET HEADING ON
SET FEEDBACK OFF
SET PAGESIZE 100
SET LINESIZE 200

.::: Convert Query select PostgreSQL EDB to Excel CSV :::.

 

1. query table pembelian

SELECT pg_read_file('/etc/hostname') as hostname,VERSION() AS versi;
select * from pembelian;
 

2. Ekspor Data ke File CSV
COPY pembelian TO '/convert/pembelianedb.csv' WITH CSV HEADER;
 

.::: Create table PostgreSQL EDB Support jsonb_build_object,jsonb_agg & Output Json :::.

 

A. Create table with ouput json

1. run query to ouput JSON_OBJECT

-- Query untuk tabel barang
SELECT
    jsonb_build_object(
        'KODE_BARANG', KODE_BARANG,
        'NAMA_BARANG', NAMA_BARANG,
        'SATUAN_BARANG', SATUAN_BARANG,
        'STOK_BARANG', STOK_BARANG
    ) AS barang_json
FROM barang;

.::: Create table MSSQL SQL Server Support JSON_QUERY,JSON PATH & Output Json :::.

 

 

A. Create table with ouput json

1. run query to ouput JSON_OBJECT
-- Barang
SELECT
    JSON_QUERY((
        SELECT
            KODE_BARANG AS 'KODE_BARANG',
            NAMA_BARANG AS 'NAMA_BARANG',
            SATUAN_BARANG AS 'SATUAN_BARANG',
            STOK_BARANG AS 'STOK_BARANG'
        FOR JSON PATH
    )) AS barang_jsonarr
FROM barang;
 

.::: Create table MariaDB MySQL Support JSON_OBJECT,JSON_ARRAYAGG & Output Json :::.

 

A. Create table with ouput json

1. run query to ouput JSON_OBJECT

SELECT
    JSON_OBJECT(
        'KODE_BARANG', KODE_BARANG,
        'NAMA_BARANG', NAMA_BARANG,
        'SATUAN_BARANG', SATUAN_BARANG,
        'STOK_BARANG', STOK_BARANG
    ) AS barang_json
FROM barang;

SELECT
    JSON_OBJECT(
        'KODE_CUSTOMER', KODE_CUSTOMER,
        'NAMA_CUSTOMER', NAMA_CUSTOMER,
        'ALAMAT_CUSTOMER', ALAMAT_CUSTOMER,
        'KOTA_CUSTOMER', KOTA_CUSTOMER,
        'TELEPON_CUSTOMER', TELEPON_CUSTOMER
    ) AS customer_json
FROM customer;

SELECT
    JSON_OBJECT(
            'KODE_SUPLIER', KODE_SUPLIER,
            'NAMA_SUPLIER', NAMA_SUPLIER,
            'ALAMAT_SUPLIER', ALAMAT_SUPLIER,
            'KOTA_SUPLIER', KOTA_SUPLIER,
            'TELEPON_SUPLIER', TELEPON_SUPLIER
        )
    ) AS suplier_json
FROM suplier;
 

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

 

correlation https://teguhth.blogspot.com/2024/02/script-backup-full-all-database-in.html

1. create database & table
create database dbatools;
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
);

2. run sh backup_full_daily_with_record.sh

.::: Script Backup Daily Multiple Database, Backup FULL, Backup LOG, Backup Incremental with Record Table backup logs in MariaDB MySQL MarinaDB :::.

 

correlation +update from https://teguhth.blogspot.com/2024/07/script-backup-full-differential.html

1. Create database & table for save backup log
create database dbatools;
CREATE TABLE backuplogs (
    host VARCHAR(255) NOT NULL,
    date DATETIME 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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

2. put script sh backup_full_daily_with_record.sh

.::: Convert Query select MariaDB MarinaDB MySQL to Excel CSV include using script scheduler :::.

A. Using cmd MariaDB

1. check query using normal query

select * from pembelian;

 

2. create folder

mkdir /convert
chmod 777 /convert
 

.::: Test Insert, select Data using Maxscale include maxscale router to slave/ master :::.

 

1. Create table;
CREATE TABLE `hostname_tbl` (
   `host` varchar(100) DEFAULT NULL,
   `created_date` datetime DEFAULT NULL,
   `dbinfo` varchar(8000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

2. check maxscale list servers

[root@ha01 ~]#  maxctrl list servers
┌─────────────────┬─────────────┬──────┬─────────────┬─────────────────┬─────────┬─────────────────────────────┐
│ Server          │ Address     │ Port │ Connections │ State           │ GTID    │ Monitor                     │
├─────────────────┼─────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────────────────┤
├ gtid01-v31      │ 10.10.10.31 │ 3306 │ 0           │ Master, Running │ 0-1-121 │ MariaDB-Monitor-gtid        │
├─────────────────┼─────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────────────────┤
│ gtid02-v32      │ 10.10.10.32 │ 3306 │ 0           │ Slave, Running  │ 0-1-121 │ MariaDB-Monitor-gtid        │
├─────────────────┼─────────────┼──────┼─────────────┼─────────────────┼─────────┼─────────────────────────────┤
│ gtid03-v33      │ 10.10.10.33 │ 3306 │ 0           │ Slave, Running  │ 0-1-121 │ MariaDB-Monitor-gtid        │
└─────────────────┴─────────────┴──────┴─────────────┴─────────────────┴─────────┴─────────────────────────────┘
[root@ha01 ~]#

.::: Query Convert select to insert table in SQL Server MSSQL, MariaDB MySQL, PostgreSQL EDB, Oracle :::.

 

A. MariaDB
    
1. Query basic

select * from pembelian;

2. query to convert select to insert
SELECT CONCAT(
    'INSERT INTO pembelian (KODE_PEMBELIAN, KODE_BARANG, KODE_CUSTOMER, TANGGAL_PEMBELIAN, JUMLAH_PEMBELIAN) VALUES (',
    QUOTE(KODE_PEMBELIAN), ', ',
    QUOTE(KODE_BARANG), ', ',
    QUOTE(KODE_CUSTOMER), ', ',
    QUOTE(TANGGAL_PEMBELIAN), ', ',
    JUMLAH_PEMBELIAN, ');'
) AS insert_query
FROM pembelian;

.::: Create Table & Insert data using suport Arab & Thailand Character in PostgreSQL EDB :::.

 

Berikut adalah contoh tabel PostgreSQL dan skrip untuk menyisipkan data yang mendukung tulisan Arab, Thailand, Rusia, dan Jepang.

PostgreSQL mendukung berbagai jenis karakter (multilingual) melalui penggunaan encoding UTF-8. Pastikan database Anda menggunakan encoding UTF-8.

Langkah 1: Buat Database dengan Encoding UTF-8
Pastikan database sudah menggunakan UTF-8. Anda dapat memeriksa atau membuatnya dengan:

CREATE DATABASE multilingual_db
WITH ENCODING 'UTF8'
LC_COLLATE='en_US.UTF-8'
LC_CTYPE='en_US.UTF-8'
TEMPLATE template0;