Posts

RMAN Duplicate To Another Server With Different SID

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...
Post a Comment
Read more

Moving Oracle Datafiles and Log Files in Windows Server

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 + ≈...
Post a Comment
Read more

Script to export

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...
Post a Comment
Read more

SQL Server Basic Healthcheck Queries

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...
Post a Comment
Read more

Unlock Oracle Users and Make Their Password Unexpirable

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) ...
Post a Comment
Read more

How To Move Datafiles In Oragle 11gR2

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...
Post a Comment
Read more

Practical RMAN Restore Scenario For Linux on Different Server

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...
Post a Comment
Read more