DB Notes

Practical RMAN Restore Guide Practical RMAN Restore Guide This quick 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. 1. Copy RMAN Backups to the New Server mkdir -p /u01/backup/test_restore cp /oldserver/backups/*.bkp /u01/backup/test_restore Explanation: Make sure you copy all backup pieces (datafiles, control file, SPFILE, archivelogs) to a directory the new server can access. 2. Start with a Minimal PFILE in NOMOUNT sqlplus / as sysdba -- Create or use a small init file: STARTUP NOMOUNT PFILE='/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initMYDB.ora'; Explanation: You can’t restore the control file or SPFILE unless the instance is NOMOUNT. 3. Restore the Control File & Mount the Database rman target / RESTORE CONTROLFILE FROM '/u01/bac...

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