DB Notes

Oracle RMAN → Azure Blob: Quick Setup Guide Oracle RMAN → Azure Blob Backup to Azure, the clean way Minimal steps, Oracle JDK only, and the recommended oracle.azure library alias. What you’ll need Azure Storage account: storageaccount , sharedkey , container Oracle Database 19c RU ≥ 19.28 (or 23ai RU ≥ 23.8 ) Use the Java in $ORACLE_HOME/jdk/bin/java Outbound access from DB host to Azure Blob Tip: Prefer a dedicated wallet directory (mode 700 ) that survives Oracle Home patching. At a glance ✅ Supported Works on on-prem or Azure VMs. Reference: Oracle Database Cloud Backup Module for Azure 1) Prepare the module # Unpack the Azure setup tool inside your Oracle Home mkdir -p $ORACLE_HOME /lib/azmodule cd $ORACLE_HOME /lib/azmodule unzip -q $ORACL...

Oracle Auto-Startup via Cron Use one of these two options to have both your primary and standby databases come up automatically on reboot. Paste the entire snippet into your Blogger HTML editor. Option 1: Listener + Data Guard Broker Only Prerequisites Broker Start Enabled In each database as SYSDBA: ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=SPFILE; Then restart so the Broker daemon comes up with the instance. Listener Configuration Make sure your listener.ora has the proper service entries for both primary and standby. Verify it can be started manually: lsnrctl status lsnrctl start Crontab Entry # As user oracle (`sudo crontab -u oracle -e`) @reboot /home/oracle/scripts/start_all.sh >> /home/oracle/scripts/start_all.log 2>&1 Startup Script /home/oracle/scripts/start_all.sh #!/bin/bash . /home/oracle/scripts/setEnv.sh # 1) Ensure listener is running lsnrctl start ...

PostgreSQL Maintenance Cheat Sheet PostgreSQL Maintenance Commands Cheat Sheet VACUUM Frees space from dead tuples and makes pages reusable without locking out writers. -- vacuum a single table VACUUM store.customers; -- vacuum all tables in the current database VACUUM; VACUUM FULL Fully rewrites a table to compact it and return space to the OS (locks the table). -- reclaim disk space on a large table VACUUM FULL store.orders; ANALYZE Collects statistics on column distributions for the planner to choose optimal plans. -- update stats for specific tables ANALYZE store.emp; ANALYZE store.dept; -- analyze entire database ANALYZE; REINDEX Rebuilds an index to remove fragmentation and bloat. -- rebuild a single index REINDEX INDEX store.ix_orderlines_orderid; -- rebuild all indexes on a table REINDEX TABLE store.orderlines; Best Practices Enable autovacuum to run VACUUM & ANALYZE autom...

PostgreSQL Restore Commands Reference PostgreSQL Restore Commands Reference This guide covers the three primary methods to restore PostgreSQL data: using psql for plain‐text dumps, pg_restore for archive-format dumps, and pg_dumpall for full-cluster plain‐text restores. Copy & paste these one-liner commands into your shell and adjust connection or file paths as needed. 1. Restore Plain‐Text Dump with psql Basic command: psql -U <user> -h <host> -p <port> -d <target_db> -f <dumpfile.sql> -d <target_db> : database to restore into. -f <dumpfile.sql> : path to the SQL dump. -1 (optional): wrap in a single transaction to abort on first error. 2. Restore Archive‐Format Dump with pg_restore Basic command: pg_restore -U <user> -h <host> -p <port> -d <target_db> <archive-file> -Fc / -Fd / -Ft : custom, directory...

PostgreSQL Backup Commands Reference 1. Schema-Only Dump (Store Schema) Command: pg_dump --schema-only --schema=store edbstore > store_schema.sql --schema-only : Dumps only the database structure (DDL). --schema=store : Limits the dump to the store schema. Redirects output to store_schema.sql . 2. Data-Only Dump (Triggers Disabled + INSERTs) Command: pg_dump --data-only --disable-triggers --inserts edbstore > edbstore_data.sql --data-only : Dumps only data (INSERT statements). --disable-triggers : Disables all triggers during data load. --inserts : Uses INSERT statements instead of COPY . Redirects output to edbstore_data.sql . 3. Table-Specific Full Dump (Customers Table) Command: pg_dump --table=edbuser.customers edbstore > edbstore_customers.sql --table=edbuser.customers : Dumps only the customers table (DDL + data)....

1. Create the Override Drop-In Open the drop-in editor for the service: sudo systemctl edit postgresql-16.service In the editor that appears, paste the following above the “### Lines below…” comment: [Service] Environment=PGDATA=/your/custom/pgdata/path ### Lines below this comment will be discarded Save and exit: • Nano: Ctrl+O → Enter , then Ctrl+X • Vim: :wq → Enter 2. Reload systemd and Restart PostgreSQL sudo systemctl daemon-reload sudo systemctl restart postgresql-16 sudo systemctl enable postgresql-16 3. Manual File Creation (Alternative) If the editor method fails, create the drop-in file directly: sudo mkdir -p /etc/systemd/system/postgresql-16.service.d sudo tee /etc/systemd/system/postgresql-16.service.d/override.conf <<EOF [Service] Environment=PGDATA=/your/custom/pgdata/path EOF sudo systemctl daemon-reload sudo systemctl restart postgresql-16 4. Verify Your New Data Directory Check service...

Rescuing a Dropped PostgreSQL Database: Two pgBackRest Paths to Success That sinking feeling when a DROP DATABASE command slips through... we've all been there, or at least dreaded it. The good news is that with robust tools like PostgreSQL and pgBackRest, recovery is often very achievable. I recently put this to the test after "accidentally" dropping a database named NEWDB and wanted to share two distinct, successful methods I used to bring it back. The Setup: A PostgreSQL cluster with several databases, including one I created called NEWDB . Data was added to NEWDB . A full backup was taken using pgBackRest while NEWDB existed . Then, the fateful command: DROP DATABASE "NEWDB"; The mission: Restore NEWDB without impacting the other live databases. Method 1: The Classic & Controlled - Restore to Temporary, then pg_dump / pg_restore This is a well-trodden and highly reliable path for surgically extracting specific data f...

PostgreSQL 16 Data Dictionary & Catalog Cheat-Sheet PostgreSQL ships with a rich “data dictionary” — a collection of catalog tables , views and utility functions stored in the builtin pg_catalog schema. They let you inspect almost every aspect of a running cluster without touching the on-disk files. All catalog names in this guide are lower-case and live in pg_catalog unless noted otherwise. 1 · The pg_catalog schema Automatically created for every database Always implicitly included at the front of search_path Contains: system tables (e.g. pg_class ), builtin functions (e.g. pg_database_size() ), and handy views (e.g. pg_stat_activity ) 2 · High-Value Catalog Tables Table What you get pg_tables view All user tables visible in current database pg_indexes view Index list plus definition (handy for DDL generators) pg_constraints CHECK, PK, FK & UNIQUE definitions pg_trigger All triggers & their firin...

PostgreSQL 16 Server Configuration & Tuning Cheatsheet 1 · Parameter Fundamentals Case-insensitive names. Value types: boolean · integer · float · string · enum. Precedence: SET (in-session) → ALTER SYSTEM ( postgresql.auto.conf ) → postgresql.conf → internal defaults. 2 · Inspecting & Changing Parameters Scope Command Effect Session SET work_mem = '64MB'; Lasts until disconnect or RESET . Database ALTER DATABASE mydb SET work_mem = '128MB'; For every new session in mydb . Role ALTER ROLE analytics SET work_mem = '256MB'; Overrides DB setting for that user. Cluster-wide ALTER SYSTEM SET work_mem = '512MB'; Stored in postgresql.auto.conf . Reload SELECT pg_reload_conf(); No downtime for most GUCs. 3 · Core Categories & Quick Rules 3.1 Connection GUC Default Note listen_addresses * Bind address list. port 5432 Postgres TCP port. max_connections 100 Back-ends allowed. superuser_reserved_connecti...

Managing a PostgreSQL Cluster with pg_ctl & pg_controldata PostgreSQL Cluster Control Cheat-Sheet 1 · What Exactly Is a “Cluster”? In PostgreSQL terminology, a cluster is a self-contained instance: one data directory, one listening port, one set of background processes. Multiple databases live inside, but they all share the same WAL stream and config files ( postgresql.conf , pg_hba.conf , …). 2 · Starting a Cluster ( pg_ctl start ) # basic invocation (foreground wait) pg_ctl -D /pgdata -l /var/log/pg_start.log start # common flags -l logfile # redirect server stdout/stderr -w / -W # wait / don’t wait for startup confirmation -o "-c port=5434" # extra postmaster options (here: override port) systemd users: prefer systemctl start postgresql-16 . But pg_ctl is still essential for ad-hoc clusters, containers, or single-user mode troubleshooting. 3 · Stopping a Cluster ( pg_ctl stop ) ...

Resolving PostgreSQL 17 Dependency Error on Oracle Linux 8 Fixing perl(IPC::Run) Dependency Error When Installing PostgreSQL 17 on Oracle Linux 8 Background While preparing a fresh Oracle Linux 8 server for a PostgreSQL 17 deployment, an attempt to install all PostgreSQL packages in one go failed with an unsatisfied dependency error: Problem 1: cannot install the best candidate for the job - nothing provides perl(IPC::Run) needed by postgresql17-devel-17.4-1PGDG.rhel8.x86_64 ... Root Cause The perl(IPC::Run) module (along with several tool‑chain libraries) resides in the CodeReady Builder repository, which is disabled by default on Oracle Linux 8. Because postgresql17-devel and postgresql17-test depend on that module, dnf / yum cannot resolve the full dependency tree unless the repository is enabled. CodeReady Builder hosts developer‑oriented packages—compilers, debuggers, Perl/Python modules—that ma...

Mastering psql — Command-Line Power Tips psql Cheat-Sheet & Power User Guide 1 · Startup Sequence Environment – PGHOST , PGPORT , PGUSER , PGDATABASE are honoured. User profile – reads $HOME/.psqlrc (skip with -X ). Single-shot execution -f FILE – run file then quit -c "COMMAND" – inline SQL / meta cmd then quit --help prints all options · --version shows build info. 2 · Line Editing & History Arrow-up / down cycles past commands (libreadline). Tab completion on Unix (SQL keywords, objects, filenames). History & Buffer Description \s Show command history (same as ~/.psql_history ) \s FILE Save history to FILE \e Edit current query buffer in $EDITOR , then execute \e FILE Open FILE in editor, then execute contents \w FILE Write current buffer to FILE (do not execute) 3 · Controlling Output Command Effect -o F...

Enterprise‑Grade PostgreSQL 16 Installation on RHEL PostgreSQL 16 Enterprise‑Grade Installation Guide RHEL 8/9 Edition 1 · Preparation Checklist 64‑bit RHEL 8/9 minimal install, fully patched. Dedicated LVM volume: /pgdata  → data files (XFS, noatime) /pgwal  → WAL (optional, SSD / NVMe for low latency) Outbound HTTPS for the official PostgreSQL YUM repository. Root (or sudo) access for initial setup; thereafter run the service as a locked postgres OS user. 2 · Create the  postgres  Service Account # groupadd --system postgres # useradd --system --gid postgres --home-dir /pgdata --shell /bin/bash postgres # passwd -l postgres # lock password (SSH key / sudo only) Why? Running the server under an unprivileged user isolates the cluster and its files from the rest of the OS—exactly as recommended in the EDB training. 3 · Kernel & System Tuning cat >/etc/sysctl.d/99-postgresql-tuning.conf ...

PostgreSQL Reference Guide PostgreSQL Reference Guide General Database Limits Maximum Database Size: Unlimited Maximum Table Size: 32 TB Maximum Row Size: 1.6 TB Maximum Field Size: 1 GB Maximum Rows per Table: Unlimited Maximum Columns per Table: 250–1600 (depends on column types) Maximum Indexes per Table: Unlimited Common Database Object Names Industry Term PostgreSQL Term Table or Index Relation Row Tuple Column Attribute Data Block Page (on disk) Page Buffer (in memory) Process & Memory Architecture 1. Top‑Level Process postmaster (also postgres ) is the parent daemon. It listens on 5432 (by default), accepts client connections, and forks a dedicated backend for each session. 2. Shared Memory Areas Component Purpose Shared Buffers Main buffer cache holding data pages read from disk. WAL Buffers Staging area for write‑ahead log ...

Oracle 19c Data Guard – Standby Creation, Switchover & Failover Oracle 19c Physical Standby Creation & Management (RMAN Duplicate + Data Guard Broker) This article walks through building a physical standby using RMAN  DUPLICATE FROM ACTIVE DATABASE , then managing switchover / failover with the Data Guard Broker. The commands were tested on Oracle Linux 8 with Oracle Database 19c . Assumptions Two hosts ( oracle1.localdomain & oracle2.localdomain ) with Oracle 19c installed. Primary instance runs on oracle1 ; standby will be created on oracle2 . Listener port 1521 is reachable in both directions (check firewalls). Primary DB name MYDBPROD ; standby unique name MYDBDG . 1 – Primary Server Preparation 1.1 Switch to ARCHIVELOG & Force Logging -- Check mode SELECT log_mode FROM v$database; -- If NOARCHIVELOG: SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCH...

Cloning MYDBPROD → MYDBTEST with RMAN DUPLICATE Sometimes we need a quick, up‑to‑date copy of production for troubleshooting or functional testing. The fastest route is RMAN duplicate from active database , which streams the data files over the network—no backup & restore cycle required. Environment snapshot oracle1 → production server (SID MYDBPROD ) oracle2 → test server (SID MYDBTEST ) Database names: MYDBP (prod) → MYDBT (test) OS: Oracle Linux 8 SELinux Permissive , firewalld disabled. Only the Oracle binaries are installed on the test host—no database. 1  Configure the listener on the test server LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle2.localdomain)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1) (SID_NAME = MYDBTEST) ) ) Start (or reload) it: lsnrctl start listener 2  Add tnsnames.ora en...

Oracle 19c – Offline Relocation of Datafiles & Redo Logs Consolidating Oracle 19c Datafiles & Redo Logs onto a Single Drive Contents 0 · Executive Summary 1 · Pre‑Start Requirements 2 · Inventory & Duplicate‑Name Audit 3 · Dry‑Run on a Test Tablespace 4 · Generate Rename Scripts 5 · Shutdown & Robocopy 6 · Update Controlfile Pointers 7 · Temp Tablespace Plan 8 · (Option) Moving Controlfiles 9 · Post‑Migration Checks 10 · Rollback Plan Appendix A · FAQ 0 · Executive Summary We will shut down the Oracle instance, move every datafile and redo‑log file from drives D: , E: and G: to the new drive H: using ROBOCOPY /MOV , then update the controlfile pointers and reopen the database. A quick test with a disposable tablespace proves the commands before the real move. 1 · Pre‑Start Requirements ✔️  New drive ready: H: must have enough free space for all datafiles + redo logs + ≈...