DB Notes

DBA Toolkit: SQL Server Health Check Queries This guide provides a suite of T‑SQL queries that give you a quick snapshot of your SQL Server’s health. The toolkit covers: Database Health Status Top 5 Resource‑Intensive Queries (by CPU time, in seconds) Top 5 Largest Objects (Tables) by Size Backup Status – including full, differential (incremental), and transaction log backups Database Size Summary Memory (RAM) Usage 1. Database Health Status This query returns basic state and configuration info for each database: SELECT name, state_desc, -- e.g. ONLINE, OFFLINE, RECOVERING, SUSPECT, etc. recovery_model_desc, -- SIMPLE, FULL, or BULK_LOGGED user_access_desc, -- MULTI_USER, SINGLE_USER, RESTRICTED_USER is_read_only, is_auto_close_on, is_auto_shrink_on FROM sys.databases; 2. Top 5 Resource‑Intensive Querie...

Handling Expired Oracle Users When an Oracle database user account expires, it needs to be unlocked and assigned to a profile that ensures password expiration does not occur. Follow these steps to handle expired Oracle users. Step 1: Create a Profile with Unlimited Password Lifetime To prevent password expiration, create a profile with unlimited password lifetime: CREATE PROFILE APP_PROFILE LIMIT PASSWORD_LIFE_TIME UNLIMITED; Step 2: Assign the Profile to the User Assign the newly created profile to the affected user: ALTER USER APP_USER PROFILE APP_PROFILE; Step 3: Reset Password for Expired Users If a user is already expired, their password must be reset: ALTER USER APP_USER IDENTIFIED BY same_password; Step 4: Retrieve the Existing Password (If Necessary) ...

How to Move (Rename) an Oracle Datafile to a New Folder in Oracle 11g How to Move (Rename) an Oracle Datafile to a New Folder in Oracle 11g When you accidentally create a datafile in the wrong folder, you can move it to the intended location. Unlike a regular file move, this process in Oracle 11g involves both operating system commands and an update to Oracle’s control file. Follow these steps carefully. Important: Backup: Before you begin, ensure you have a current backup of your database or at least the affected tablespace. Privileges: You must have the appropriate administrative privileges to perform these operations. Environment: The steps differ slightly based on whether you’re dealing with user tablespaces or system-critical tablespaces. Step 1: Identify the Datafile Start by confirming the file name and its current location. Connect to SQL*Plus or your preferred Oracle clie...

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