.::: Grouping Year, Month, Day, Time, Date for Archive, Cleansing in Oracle Database :::.

1. create table 

CREATE TABLE datefilter (
    datex DATE NOT NULL
);

CREATE TABLE datefilter (
    datex TIMESTAMP NOT NULL
);

2. create date sample 3 years

INSERT INTO datefilter (datex)
WITH cte AS (
    SELECT TRUNC(ADD_MONTHS(SYSDATE, 1 - LEVEL), 'MM') AS dt
    FROM dual
    CONNECT BY LEVEL <= 36
)
SELECT dt + INTERVAL '1' SECOND
FROM cte;

INSERT INTO datefilter (datex)
WITH cte AS (
    SELECT TRUNC(ADD_MONTHS(SYSDATE, 1 - LEVEL), 'MM') AS dt
    FROM dual
    CONNECT BY LEVEL <= 36
)
SELECT dt + INTERVAL '15' DAY + INTERVAL '1' SECOND
FROM cte;

.::: Grouping Year, Month, Day, Time, Date for Archive, Cleansing in PostgreSQL EDB :::.

 

1. create table 

CREATE TABLE datefilter (
    datex TIMESTAMP NOT NULL
);

2. create date sample 3 years

WITH RECURSIVE cte AS (
    -- Mulai dari bulan ini
    SELECT date_trunc('month', now())::date AS dt,
           1 AS lvl

    UNION ALL
    
    -- Mundur 36 bulan
    SELECT (dt - INTERVAL '1 month')::date,
           lvl + 1
    FROM cte
    WHERE lvl < 36
)
INSERT INTO datefilter (datex)
SELECT dt + INTERVAL '1 millisecond'             -- tanggal 1
FROM cte
UNION ALL
SELECT dt + INTERVAL '14 days' + INTERVAL '1 millisecond'   -- tanggal 15
FROM cte;

.::: Grouping Year, Month, Day, Time, Date for Archive, Cleansing in MarinaDB MariaDB :::.

 

1. create table 

CREATE TABLE datefilter (
    datex DATETIME NOT NULL
);

2. create date sample 3 years
INSERT INTO datefilter (datex)
WITH RECURSIVE cte AS (
    -- Mulai dari bulan ini (tanggal 1)
    SELECT
        DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') AS dt,
        1 AS lvl

    UNION ALL

    -- Mundur 36 bulan
    SELECT
        DATE_SUB(dt, INTERVAL 1 MONTH),
        lvl + 1
    FROM cte
    WHERE lvl < 36
)
SELECT dt + INTERVAL 1000 MICROSECOND         -- tanggal 1
FROM cte

UNION ALL

SELECT (dt + INTERVAL 14 DAY) + INTERVAL 1000 MICROSECOND   -- tanggal 15
FROM cte;
 

.::: Grouping Year, Month, Day, Time, Date for Archive, Cleansing in SQL Server MSSQL :::.

 

1. create table 

CREATE TABLE datefilter (
    datex DATETIME NOT NULL
);

2. create date sample 3 years

;WITH cte AS (
    -- Mulai dari bulan ini
    SELECT 
        CAST(DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AS DATETIME) AS dt,
        1 AS lvl
    UNION ALL
    -- Mundur 36 bulan (3 tahun)
    SELECT 
        DATEADD(MONTH, -1, dt),
        lvl + 1
    FROM cte
    WHERE lvl < 36
)
INSERT INTO datefilter (datex)
SELECT DATEADD(MILLISECOND, 1, dt)              -- Tanggal 1
FROM cte
UNION ALL
SELECT DATEADD(MILLISECOND, 1, DATEADD(DAY, 14, dt))   -- Tanggal 15
FROM cte
OPTION (MAXRECURSION 0);

.::: Script Monitoring GTID Mirroring using Shell Script Colour :::.

 

1. Monitoring GTID no colour
 

 

.::: Install MariaDB 10.6, Maxscale, Keepalived in Suselinux SLES 15 :::.

 

1. enable repo 

teguhth01suse:~ # cat /etc/zypp/repos.d/mariadb.repo
[mariadb]
enabled=1
autorefresh=1
baseurl=https://yum.mariadb.org/10.6/sles/15/x86_64
gpgcheck=1
teguhth01suse:~ #

2. Prepare packages
rpm -qa | grep -i libmaria

zypper remove libmariadb3-3.1.22-150600.16.3.x86_64

rpm -qa | grep -i libmaria

rpm --import https://downloads.mariadb.com/MariaDB/MariaDB-Server-GPG-KEY
rpm --import https://downloads.mariadb.com/MariaDB/RPM-GPG-KEY-MariaDB

wget https://ftp.lysator.liu.se/pub/opensuse/update/leap/15.6/sle/x86_64/socat-1.8.0.0-150600.20.6.1.x86_64.rpm
zypper install socat-1.8.0.0-150600.20.6.1.x86_64.rpm

wget https://ftp.lysator.liu.se/pub/opensuse/update/leap/15.6/sle/x86_64/libasan8-14.3.0+git11799-150000.1.11.1.x86_64.rpm
zypper install libasan8-14.3.0+git11799-150000.1.11.1.x86_64.rpm

.::: Install MariaDB 10.6 in Debian 11 :::.

 

1. add update debian 

root@debian02:~# cat /etc/apt/sources.list
# deb cdrom:[Debian GNU/Linux 11.6.0 _Bullseye_ - Official amd64 DVD Binary-1 20221217-10:40]/ bullseye contrib main

#deb cdrom:[Debian GNU/Linux 11.6.0 _Bullseye_ - Official amd64 DVD Binary-1 20221217-10:40]/ bullseye contrib main

#deb http://security.debian.org/debian-security bullseye-security main contrib
#deb-src http://security.debian.org/debian-security bullseye-security main contrib

# bullseye-updates, to get updates before a point release is made;
# see https://www.debian.org/doc/manuals/debian-reference/ch02.en.html#_updates_and_backports
# A network mirror was not selected during install.  The following entries
# are provided as examples, but you should amend them as appropriate
# for your mirror of choice.
#
# deb http://deb.debian.org/debian/ bullseye-updates main contrib
# deb-src http://deb.debian.org/debian/ bullseye-updates main contrib

deb http://deb.debian.org/debian bullseye main contrib non-free
deb-src http://deb.debian.org/debian bullseye main contrib non-free

deb http://security.debian.org/debian-security bullseye-security main contrib non-free
deb-src http://security.debian.org/debian-security bullseye-security main contrib non-free

deb http://deb.debian.org/debian bullseye-updates main contrib non-free
deb-src http://deb.debian.org/debian bullseye-updates main contrib non-free
root@debian02:~#

.::: Script Drop All Database non-system in MariaDB MySQL in Centos 9 :::.

 

1. run script drop database non-system 

sh drop_database_nosystem_all.sh
 

2. run script drop database non-system with duration

sh drop_database_nosystem_all_duration.sh 

.::: Backup Database MariaDB with encripted -aes-256-cbc dan restore mariadb database :::.

1. backup enkripsi

mysqldump -uroot -pxxx teguhth | openssl enc -aes-256-cbc -md sha1 -salt -out teguhthenc.sql.enc
mysqldump -uroot -pxxx teguhth | openssl enc -pass pass:abcde -aes-256-cbc -md sha1 -salt -out teguhthenc.sql.enc
mysqldump -uroot -pxxx teguhth | openssl enc -pass pass:abcde -aes-256-cbc -md sha1 -salt -out teguhthenc.sql.enc | gzip -c  > teguhthenc.gz.sql.enc
 
 

2. convert to sql from enkripsi file to sql
openssl enc -d -aes-256-cbc -md sha1 -in teguhthenc.sql.enc -out teguhth.sql -pass pass:abcde
 

.::: Script restore database MariaDB using record to table include duration Restore :::.

 

A. simple record

1. create table restore_log

CREATE TABLE IF NOT EXISTS restore_log (
    db_name VARCHAR(100),
    restore_time DATETIME,
    backup_file VARCHAR(255),
    restored_by VARCHAR(100)
);

2. create script 

.::: Script Backup Full & Incremental All Databases Using Record to Table include start, finish & duration backup in PostgreSQL EDB :::.

  

https://teguhth.blogspot.com/2025/02/script-backup-full-all-database-using.html
https://teguhth.blogspot.com/2025/09/script-backup-full-incremental-all.html

1. create database & table

CREATE TABLE backuplogduration (
    host VARCHAR(255) NOT NULL,
    backuptime TIMESTAMP NOT NULL,
    start TIMESTAMP NOT NULL,
    finish TIMESTAMP NOT NULL,
    duration VARCHAR(255) NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
);

.::: Create CDB & Sample Database in Oracle 19C :::.

 

1. Login sebagai SYS atau SYSTEM

Masuk ke CDB lalu kasih privilege ke HRADMIN:

-- Masuk ke CDB sebagai SYS
sqlplus sys@//localhost:1521/TGHDB as sysdba

-- Pindah ke PDB TGHDBHR
ALTER SESSION SET CONTAINER=TGHDBHR;

-- Beri hak penuh

CREATE USER hradmin IDENTIFIED BY hrpass;
GRANT CONNECT, RESOURCE TO hradmin;
ALTER USER hradmin QUOTA UNLIMITED ON USERS;

GRANT CONNECT, RESOURCE TO hradmin;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO hradmin;

-- Kalau mau full DBA di PDB
GRANT DBA TO hradmin;

sqlplus hradmin/hrpass@//localhost:1521/TGHDBHR
 

.::: Create New Container or add Container CDB in Oracle 19C :::.

 

1. Cek dulu container yang ada

sqlplus sys/oracle as sysdba;

SHOW CON_NAME;
SHOW PDBS;
 

2. Buat PDB baru dari PDB$SEED (template bawaan)

Misalnya mau buat container baru bernama TGHDBHR:

CREATE PLUGGABLE DATABASE TGHDBHR
  ADMIN USER hradmin IDENTIFIED BY hrpass
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/TGH/pdbseed/',
                     '/u01/app/oracle/oradata/TGH/TGHDBHR/');