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...

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

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...

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 expdp Export Job Script Oracle expdp Export Job Script This bash script runs an Oracle Data Pump export (expdp) job with anonymized parameters (except the expdp binary path). Simply modify the variables in the script as needed. #!/bin/bash # This script runs an Oracle Data Pump export (expdp) job. # Modify the variables below to change the export settings. # Define variables for the export job ORACLE_CONN="'/ as sysdba'" # Oracle connection string EXPDP_BIN="/u01/app/oracle/product/19/dbhome_1/bin/expdp" # Oracle expdp binary (unchanged) DIRECTORY="EXP_DIR" # Oracle directory object name DUMPFILE="DUMPFILE.dmp" # Dump file name LOGFILE="LOGFILE.log" # Log file name SCHEMAS="SCHEMA1,SCHEMA2" # Comma-separated list of schemas to export PARALLELITY="1" # Parallel export degree # Log the job details ec...

 Normally, you can't restore a single database from a cluster backup that are taken via pg_dumpall.In order to achieve that, you can follow the steps below: 1- Create a script that greps specific database portion of the dump: # ! / bin / bash [ $# - lt 2 ] && { echo "Usage: $0 <postgresql dump> <dbname>"; exit 1 ; } sed "/connect.*$2/,\$!d" $ 1 | sed "/PostgreSQL database dump complete/,\$d" 2- Extract the portion via example command below: sh onedbscript.sh full_dump.dmp MY_DATABASE > onlyonedatabase.dmp 3- You may restore and test if it worked if you want: psql -p <portnumber> -d MY_DATABASE < onlyonedatabase.dmp