.::: Sample Shrink Database SQL Server MSSQL :::.

 

Correlation shrink log http://teguhth.blogspot.com/2023/05/how-to-shrink-log-database-sql-server.html

simulation shrink database

1. Create table & insert 5 million row for lab

CREATE TABLE TestTable (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(50),
    CreatedDate DATETIME
);

.::: Configuration RMAN (Recovery Manager), backup & Restore Database Oracle Using RMAN :::.

 

a. Enable Archive log

Tetap di dalam RMAN, atau login ulang jika diperlukan:

http://teguhth.blogspot.com/2024/12/backup-database-restore-database-oracle.html

sqlplus >

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
SELECT dbid,name,controlfile_type,open_mode,created,log_mode,database_role,archivelog_compression,platform_name FROM V$DATABASE

.::: Enable ARCHIVELOG Database Oracle & Test Backup Online & Offline using RMAN :::.

 

Pesan error RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode menunjukkan bahwa database Anda saat ini berada dalam mode NOARCHIVELOG, sehingga hanya mendukung backup offline (ketika database dalam kondisi MOUNT, tetapi tidak di OPEN). Untuk memperbaiki masalah ini, Anda memiliki dua opsi:

Opsi 1: Aktifkan ARCHIVELOG Mode
Mode ARCHIVELOG memungkinkan Anda melakukan backup database dalam keadaan online (terbuka). Berikut langkah-langkahnya:

a. Login ke RMAN

Tetap di dalam RMAN, atau login ulang jika diperlukan:

rman target /
 

.::: Backup Database & Restore database Oracle Using RMAN :::.

 

A. Backup using RMAN

1. Backup Database + plus archivelog

backup database plus archivelog;

RMAN> backup database plus archivelog;

Starting backup at 24-DEC-24
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=50 RECID=1 STAMP=1188492501
input archived log thread=1 sequence=51 RECID=2 STAMP=1188492563
input archived log thread=1 sequence=52 RECID=3 STAMP=1188492609
input archived log thread=1 sequence=53 RECID=4 STAMP=1188492871
input archived log thread=1 sequence=54 RECID=5 STAMP=1188492941
input archived log thread=1 sequence=55 RECID=6 STAMP=1188493009
input archived log thread=1 sequence=56 RECID=7 STAMP=1188493163
input archived log thread=1 sequence=57 RECID=8 STAMP=1188493230
input archived log thread=1 sequence=58 RECID=9 STAMP=1188551567
input archived log thread=1 sequence=59 RECID=10 STAMP=1188553084
input archived log thread=1 sequence=60 RECID=11 STAMP=1188553084
input archived log thread=1 sequence=61 RECID=12 STAMP=1188553322
input archived log thread=1 sequence=62 RECID=13 STAMP=1188553322
input archived log thread=1 sequence=63 RECID=14 STAMP=1188553452
input archived log thread=1 sequence=64 RECID=15 STAMP=1188553452
input archived log thread=1 sequence=65 RECID=16 STAMP=1188553775
input archived log thread=1 sequence=66 RECID=17 STAMP=1188553775
input archived log thread=1 sequence=67 RECID=18 STAMP=1188555260
channel ORA_DISK_1: starting piece 1 at 24-DEC-24
channel ORA_DISK_1: finished piece 1 at 24-DEC-24
piece handle=/home/oracle/backuprman/163dfqfs_1_1 tag=TAG20241224T101420 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24-DEC-24

.::: Drop Schema Database & Drop Tablespace Oracle Database :::.

 

Untuk menghapus schema Oracle dan tablespace terkait, Anda perlu mengikuti langkah-langkah berikut:

1. Drop schema & object
 
DROP USER teguhth CASCADE;

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

Penjelasan:
DROP USER: Menghapus user/schema.
CASCADE: Menghapus semua objek milik user tersebut, seperti tabel, indeks, dan views.

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