DBA NAVIGATOR

  Oracle Database Manual Switchover: A Comprehensive Guide Database high availability is critical for enterprise applications. Oracle's Data Guard provides robust disaster recovery capabilities, including the ability to perform planned switchovers between primary and standby databases. This blog post walks through the process of manually switching over from a primary database (DC) to a standby database (DR) and then switching back. Environment Details Primary Database (DC): testpri Standby Database (DR): testsby Pre-Switchover Verification Before initiating a switchover, it's essential to verify the current database state and ensure log shipping synchronization. Step 1: Check Active Sessions First, check for active database sessions that might be affected during the switchover: There should not be not active session, if there are any check with application team and kill those sessions. set time on col machine for a30 select MACHINE, count(*), status from v$session gr...

GC 2-Way and 3-Way Buffer Cache in Oracle RAC Oracle RAC uses a shared cache architecture where each instance has its own buffer cache. To maintain consistency, Oracle uses the Global Cache Service (GCS) to coordinate access across nodes using mechanisms known as GC 2-Way and GC 3-Way buffer transfers. 🔁 What is Global Cache in RAC? Global Cache ensures block consistency across instances in a RAC cluster. Instead of fetching from disk, Oracle RAC uses Cache Fusion to transfer blocks over the interconnect between nodes, reducing I/O and improving performance. ⚡ GC 2-Way Buffer Transfer A GC 2-Way transfer happens when the block is shipped directly from the current holder to the requesting instance in just two messages. 🔁 Request: Instance A → Instance B 📦 Response: Instance B → Instance A This is fast and efficient because it avoids additional overhead. 📘 Example Instance A runs a DML and needs a block held by Instance B in exclusive mode. B sends it directly...

📈 Forecasting Archive Log Growth in Oracle Database In Oracle databases running in ARCHIVELOG mode , archived redo logs are crucial for recovery, replication (like Data Guard), and auditing. Understanding and forecasting their growth is essential to avoid storage issues, ensure high availability, and plan proactively for backup strategies or disaster recovery setups. 🔍 Why Forecast Archive Log Usage? Proper monitoring and forecasting of archive log generation helps in: 📦 Storage Planning: Predict disk space requirements and avoid unexpected full mount points. 📊 Performance Tuning: Ensure the archiving process doesn’t become a bottleneck. 🌐 Data Guard: Estimate redo shipping volume for standby databases. 🛡️ Backup Strategy: Align RMAN archive log backups with actual usage patterns. 📅 Daily Archive Log Generation Report The following SQL query gives a breakdown of archive log generation on a per-day basis over the last 30 days: SELECT TO_CHAR(FIRS...

🔐 Locking and Unlocking Schemas in Oracle Database In Oracle Database, schemas (users) can be locked or unlocked to manage access, enhance security, or perform administrative tasks. This feature allows DBAs to temporarily disable a user without dropping the schema or affecting stored data. 👤 Why Lock or Unlock Schemas? There are many use cases where locking or unlocking a schema becomes essential: 🔧 Maintenance: Prevent access during application or database maintenance windows. 🛡️ Security: Disable suspicious or inactive users temporarily. 🚫 Compliance: Block access for terminated employees or deprecated applications. 🔒 Locking a Schema Use the below SQL command to lock a user account. This prevents new connections using that user but doesn’t affect existing sessions unless explicitly terminated. ALTER USER <username> ACCOUNT LOCK; -- Example ALTER USER HR ACCOUNT LOCK; 🔓 Unlocking a Schema To allow a previously locked user to connect again, you...

📊 Monitoring Tablespace Usage in Oracle Database Tablespaces in Oracle are logical storage units that group related logical structures such as segments. They act as a layer between the database and the physical storage, allowing database administrators to manage space allocation, performance, and maintenance more efficiently. 📁 What are Tablespaces? Oracle Database uses tablespaces to logically organize data stored in physical datafiles. Each tablespace can contain one or more datafiles, and each datafile belongs to exactly one tablespace. Permanent Tablespaces: Store user and application data. Temporary Tablespaces: Used for sorting operations and temporary storage needs. Undo Tablespaces: Hold undo records for rolling back transactions and for read consistency. 📈 Checking Tablespace Utilization The following SQL query helps you monitor permanent tablespace usage, showing total, used, free space, and percentage utilized. COLUMN tablespace_name FORMAT A20 CO...