DB Notes

Practical RMAN Restore Guide Practical RMAN Restore Guide This guide shows how to restore and relocate an Oracle database via RMAN. We’ll assume you want all datafiles in a new folder ( /newpath instead of /oldpath ), and possibly handle redo logs in a new location as well. Steps include restoring SPFILE, cataloging backups, restoring archived logs, and point-in-time recovery. 1. Copy RMAN Backups to the New Server mkdir -p /u01/backup/test_restore cp /oldserver/backups/*.bkp /u01/backup/test_restore Copy all backup pieces (datafiles, control file, SPFILE, archivelogs) to a directory the new server can access. 2. Create Required Oracle Directories mkdir -p /u01/app/oracle/admin/MYDB/adump mkdir -p /u01/app/oracle/oradata/MYDB mkdir -p /u01/app/oracle/fast_recovery_area/MYDB mkdir -p /newpath/onlinelog Ensure directories exist for diagnostic files ( adump ), datafiles, FRA, and redo logs before proceeding. 3. Startu...

Running SQLCMD in PowerShell via SQL Server Agent Learn how to execute a SQL query via SQL Server Agent using PowerShell and sqlcmd . This method ensures UTF-8 compatibility and proper handling of non-English characters. 1️⃣ Save Your SQL Query in a File First, create a SQL file with the query you want to execute. Save it as C:\Scripts\ExportQuery.sql . SET NOCOUNT ON; SELECT TOP 100 * FROM [YourDatabase].[dbo].[YourTable]; 2️⃣ Create a PowerShell Script Now, create a PowerShell script to execute the query and export the results as a CSV file. sqlcmd -S YourServer -E -i "C:\Scripts\ExportQuery.sql" -s "," -W -h -1 | Out-File "C:\Exports\ExportedData.csv" -Encoding UTF8 3️⃣ Configure SQL Server Agent Follow these steps to create a SQL Server Agent job: Open SQL Server Management Studio (SSMS). ...

PostgreSQL pg_cron: Scheduling Materialized View Refresh Across Databases PostgreSQL pg_cron: Scheduling Materialized View Refresh Across Databases In this tutorial, I’ll walk you through the process of scheduling a Materialized View refresh using the pg_cron extension in PostgreSQL. Specifically, we’ll cover how to execute a scheduled SQL command in a different database using cron.schedule_in_database . Step 1: Enable the pg_cron Extension in Azure Portal On Azure PostgreSQL Flexible Server, the pg_cron extension is managed directly through the Azure portal. Follow these steps: Go to the Azure portal and navigate to your PostgreSQL Flexible Server instance. Under the Server Parameters section, locate the azure.extensions parameter. Add PG_CRON to the list of allowed extensions (as shown in the screenshot). Once enabled, the extension is automatically installed in the default `postgres` database . If you need to schedule jobs in o...