DBA NAVIGATOR

Understanding Oracle Fast Recovery Area (FRA) 🚀 Oracle Fast Recovery Area (FRA) Explained Oracle's Fast Recovery Area (FRA) is a centralized storage location that holds all recovery-related files, providing an efficient and simplified way to manage backup and recovery operations. It is a crucial component in any robust Oracle database backup strategy. 🎯 Purpose of FRA Centralized Storage: Stores backups, archived logs, flashback logs, control files, and copies of datafiles. Space Management: Automatically manages space by deleting obsolete or redundant files based on retention policies. Simplified Recovery: Provides a single location for RMAN to locate backup and recovery files, making restores easier and faster. 🛠️ Setting Up FRA Two parameters must be configured to enable FRA: DB_RECOVERY_FILE_DEST: Specifies the directory location of the FRA. DB_RECOVERY_FILE_DEST_SIZE: Specifies the maximum size (in bytes) that...

🧱 Steps to Purge SQL Plan from the Shared Pool In some cases, after assigning an SQL profile for a particular SQL_ID, it may not be used as expected. This guide will help you ensure that the SQL profile is being used and, if necessary, purge the SQL plan from the shared pool to force the system to reoptimize and apply the assigned SQL profile. 🔧 Step-by-Step Instructions 1️⃣ Verify if the SQL Profile is Being Used Before purging any SQL plan, you should first check if the assigned SQL profile is being used. Run the following query to check: select distinct p.name sql_profile_name, s.sql_id from dba_sql_profiles p, DBA_HIST_SQLSTAT s where p.name = s.sql_profile and sql_id = '5dj81jnf8t03a'; -- Replace with your SQL_ID If the profile is being used, the output will display the associated SQL profile name. Alternatively, you can use the DBMS_XPLAN function to check the SQL profile: SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('...

🧱 How to Run SQL Tuning Advisor (STA) in Oracle The SQL Tuning Advisor (STA) is a powerful tool in Oracle that helps database administrators identify and resolve performance issues in SQL statements. This guide provides a step-by-step approach to using STA effectively. In this guide, we'll walk you through a clean and safe method to create a new tuning task, execute it, check its status, and view the recommendations. 🔧 Step-by-Step Instructions 1️⃣ Create the Tuning Task for the Given SQL_ID DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_id => 'abc123xyz7890', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 60, task_name => 'abc123xyz7890_tuning_task', description => 'Tuning task for statement abc123xyz7890'); ...

🧠 Understanding and Managing SQL Profiles in Oracle SQL Profiles in Oracle are powerful tuning tools generated by the SQL Tuning Advisor. They help the optimizer produce better execution plans by supplying additional statistics or corrected cardinality estimates — all without changing the original SQL text. Unlike SQL Plan Baselines or Hints, SQL Profiles do not lock down a specific plan. Instead, they allow the optimizer to adapt and make smarter decisions based on real-time data. These profiles are especially helpful when the optimizer misestimates row counts or selects suboptimal joins. 🔧 Step-by-Step: SQL Profile Management 1️⃣ Check If a SQL Profile Is Assigned to a SQL ID SELECT DISTINCT p.name AS sql_profile_name, s.sql_id FROM dba_sql_profiles p JOIN dba_hist_sqlstat s ON p.name = s.sql_profile WHERE s.sql_id = '&sqlid'; 💡 Tip: Replace &sqlid with the actual SQL ID to see if it's linked to any profile in AWR history. 2️⃣ List...

🧱 How to Add a Temporary Tablespace in Oracle Standalone Database Temporary tablespaces in Oracle are used for sorting operations, managing global temporary tables, and handling intermediate result sets. It's a good practice to add or manage these tablespaces especially in large databases or when the default temp tablespace gets full or fragmented. In this guide, we'll walk you through a clean and safe method to create a new temporary tablespace, make it default (if needed), and drop the old one (optional). 🔧 Step-by-Step Instructions 1️⃣ Connect to the Database as SYSDBA sqlplus / as sysdba -- or sqlplus sys@ORCL as sysdba 2️⃣ Identify Existing Temporary Tablespaces SELECT tablespace_name, status, contents FROM dba_tablespaces WHERE contents = 'TEMPORARY'; 3️⃣ Create a New Temporary Tablespace CREATE TEMPORARY TABLESPACE temp_new TEMPFILE '/u01/app/oracle/oradata/ORCL/temp_new01.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 5G EXTENT ...

🧼 Cleaning Up Inactive Patches in Oracle RAC In Oracle RAC environments, patches accumulate over time, many of which become inactive due to rollback, superseding, or failed attempts. These inactive patches can clutter the patch inventory, slow down operations, and introduce inconsistencies across nodes. This post explains how to clean them up safely using opatch util deleteinactivepatches , and why it's essential to run this under both Grid Infrastructure and Oracle RDBMS users . $ORACLE_HOME/OPatch/opatch util deleteinactivepatches -silent 🔍 What Does This Command Do? This command removes all inactive patches from the Oracle inventory. These include: Rolled-back patches Superseded interim or PSU patches Obsolete metadata left from earlier patch attempts It frees up space, reduces conflict risk, and improves future patching performance. The -silent flag allows it to run without user input. ⚙️ Importance in RAC Setup Oracle RAC requires consistent ...

🪄 Oracle GoldenGate: Overview, Architecture, and Silent Installation Oracle GoldenGate (OGG) is a real-time data replication and integration tool. It enables the replication of data across heterogeneous systems, ensuring high availability, disaster recovery, and data distribution for analytics or migration purposes. 📌 What is Oracle GoldenGate? Oracle GoldenGate captures transactional data changes from a source system and applies them to a target system in real-time. It supports unidirectional, bidirectional, and cascading replication setups. Real-time replication Heterogeneous platform support Low impact on source system 📤 Extract Process in GoldenGate Extract is a data capture process that reads changes from the source database. 🔹 Types of Extract Classic Extract: Reads redo/archived logs from Oracle database. Used in non-integrated mode. Integrated Extract: Uses Oracle Streams infrastructure for better performance and support of complex data types...