Category: post

This will hold all blog related to Oracle Database

ORA-01017 invalid username password error showing Oracle authentication architecture, password hashing versions, and troubleshooting workflow for Oracle 19c/21c databases

ORA-01017: Invalid Username/Password – Complete Guide

Posted on by sanjeeva

Last Updated: December 2025 | Oracle 19c/21c | By Ahaaryo Oracle Version Scope: This guide covers Oracle 19c (fully supported) and Oracle 21c (Innovation Release, already desupported but widely deployed). Introduction “The password is definitely correct!” – Every DBA has said this at least once while troubleshooting ORA-01017 invalid username password. I remember a production Read more about ORA-01017: Invalid Username/Password – Complete Guide[…]

DBA_HIST_ACTIVE_SESS_HISTORY Oracle ASH analysis dashboard showing SQL performance and wait events

DBA_HIST_ACTIVE_SESS_HISTORY: Complete Guide to Oracle ASH Analysis

Posted on by sanjeeva

Introduction Have you ever been asked to explain why your Oracle database was slow last Tuesday at 2:30 PM? Or needed to identify which SQL statements were causing CPU spikes during last night’s batch run? This is where DBA_HIST_ACTIVE_SESS_HISTORY becomes your most powerful diagnostic tool. Recently, we faced a production issue where users reported intermittent Read more about DBA_HIST_ACTIVE_SESS_HISTORY: Complete Guide to Oracle ASH Analysis[…]

Complete Oracle listener configuration guide. Fix TNS-12541 errors with step-by-step listener.ora & tnsnames.ora setup for standalone databases.

How to Configure Oracle Listener for Standalone Database: Step-by-Step Guide

Posted on by sanjeeva

The Problem: TNS-12541 and Database Connection Failures You’ve just installed Oracle 19c on your server. The database is up and running perfectly—you can connect locally using SQL*Plus without any issues. But the moment you try connecting from a remote application or another server, you’re hit with the dreaded TNS-12541: TNS:no listener error. Sound familiar? This Read more about How to Configure Oracle Listener for Standalone Database: Step-by-Step Guide[…]

Oracle listener configuration architecture - database instance connected to multiple clients through TNS listener port 1521

How to Configure Oracle Listener for Standalone Database: Step-by-Step Guide

Posted on by sanjeeva

The Problem: TNS-12541 and Database Connection Failures You’ve just installed Oracle 19c on your server. The database is up and running perfectly—you can connect locally using SQL*Plus without any issues. But the moment you try connecting from a remote application or another server, you’re hit with the dreaded TNS-12541: TNS:no listener error. Sound familiar? This Read more about How to Configure Oracle Listener for Standalone Database: Step-by-Step Guide[…]

ORA-01555 snapshot too old error with UNDO tablespace configuration and retention fix for Oracle 19c

ORA-01555: Snapshot Too Old – Complete Fix Guide for Oracle 19c

Posted on by sanjeeva

The Problem: Long-Running Query Fails with ORA-01555 You’re running a critical report or data export, and after 30 minutes it fails with: When this happens: The query was working fine yesterday. The data hasn’t changed much. But today it fails with ORA-01555. This guide shows you exactly how to fix it permanently. What is ORA-01555 Read more about ORA-01555: Snapshot Too Old – Complete Fix Guide for Oracle 19c[…]

ORA-12154: TNS Could Not Resolve the Connect Identifier – Complete Fix Guide

Posted on by sanjeeva

The Problem: Cannot Connect to Oracle Database You’re trying to connect to your Oracle database and you get this error: When this happens: You check the database – it’s running. You check the listener – it’s up. But nothing connects. This is ORA-12154, and it means Oracle can’t find or read your TNS configuration. Let Read more about ORA-12154: TNS Could Not Resolve the Connect Identifier – Complete Fix Guide[…]

Migrating Oracle 19c Database from Local Storage to ASM: A Complete Step-by-Step Guide

Posted on by sanjeeva

Last Updated: December 2025 | Oracle 19c | ASM Grid Infrastructure Have you ever encountered a frustrating situation where your Oracle database is running on local storage, and you need to migrate everything to ASM (Automatic Storage Management) without significant downtime? Recently, we faced this exact scenario with a production Oracle 19c Container Database (CDB) Read more about Migrating Oracle 19c Database from Local Storage to ASM: A Complete Step-by-Step Guide[…]

ORA-00257: Archiver Error Connect Internal Only Until Freed – Complete Resolution Guide

Posted on by sanjeeva

Introduction Have you ever encountered a situation where your Oracle database suddenly stops accepting new connections, and users start complaining that the application is down? One of the most common culprits behind this scenario is the dreaded ORA-00257: archiver error. Recently, we faced this exact scenario with one of our production databases during a high-transaction Read more about ORA-00257: Archiver Error Connect Internal Only Until Freed – Complete Resolution Guide[…]

Oracle ASM 19c architecture diagram showing containerized databases using Oracle ASM for shared storage management with disk groups

Oracle ASM 19c Installation: Complete Guide to Oracle Restart Grid Infrastructure [2025]

Posted on by sanjeeva

Last Updated: December 10, 2025Tested On: Oracle Enterprise Linux 9.5, Oracle ASM 19c Patch 25 Oracle Automatic Storage Management (ASM) provides a robust storage management solution for Oracle databases. In this comprehensive guide, we’ll walk through ASM 19c Installation (patch set 25) in a standalone “Oracle Restart” grid infrastructure configuration on Oracle Enterprise Linux (OEL) Read more about Oracle ASM 19c Installation: Complete Guide to Oracle Restart Grid Infrastructure [2025][…]

Response file for Oracle ASM Standalone Oracle 19c

Posted on by sanjeeva

Introduction Response file for Oracle ASM Standalone is a critical thing to understand if you want to install this using CLI. This blog shows how to install Oracle 19c Grid Infrastructure in standalone (Oracle Restart) mode using a response file from CLI without any GUI. This is particularly helpful for DBAs working on headless servers Read more about Response file for Oracle ASM Standalone Oracle 19c[…]

How to Enable Archive Log Mode in Oracle 19c (Complete Guide)

Posted on by sanjeeva

Introduction Need to enable archive log mode in Oracle 19c but unsure of the exact steps? Converting from NOARCHIVELOG to ARCHIVELOG mode is essential for production databases, yet many DBAs hesitate due to concerns about downtime and configuration complexity. In this guide, I’ll walk through the complete process of enabling archive log mode in Oracle Read more about How to Enable Archive Log Mode in Oracle 19c (Complete Guide)[…]

Oracle Database Memory Monitoring Guide

Posted on by sanjeeva

Introduction Database memory management is crucial for maintaining optimal performance in Oracle databases. Inefficient memory usage can lead to slower query execution, increased I/O operations, and poor application response times. This comprehensive guide provides database administrators and developers with practical tools and techniques for monitoring and optimizing Oracle database memory components. Why Memory Monitoring Matters Read more about Oracle Database Memory Monitoring Guide[…]

How to Export NLS_DATE_FORMAT in Oracle: Data Pump Import Date Format Fix

Posted on by sanjeeva

The Problem: Data Pump Import Fails with Date Format Error You’re running a Data Pump import and it crashes with date format errors like ‘1998-01-01 00:00:00′. You try setting export NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’ in your shell, but the import still fails. Error you’re seeing: This happens because Data Pump doesn’t always respect environment-level NLS settings, especially Read more about How to Export NLS_DATE_FORMAT in Oracle: Data Pump Import Date Format Fix[…]

ASMSNMP User Setup: Complete Oracle ASM Monitoring Guide

Posted on by sanjeeva

Have you ever encountered a frustrating situation where your Oracle Enterprise Manager couldn’t monitor your ASM environment, leaving you blind to storage issues? Recently, we faced this exact scenario with a client’s production ASM cluster, and ASMSNMP user comes to rescue. The ASMSNMP user plays a crucial role in Oracle ASM (Automatic Storage Management), primarily Read more about ASMSNMP User Setup: Complete Oracle ASM Monitoring Guide[…]

Resolving Pluggable Database (PDB) Opening in RESTRICTED MODE (ORA-65177) in Oracle 19c

Posted on by sanjeeva

Introduction Encountering, ORA-65177 ,a Pluggable Database (PDB) that opens in RESTRICTED mode can cause operational challenges, especially when working in a multitenant environment. This issue is often linked to synchronization failures between the Container Database (CDB) and its associated PDBs. We will delve into the root cause of the problem and provide a step-by-step resolution. Read more about Resolving Pluggable Database (PDB) Opening in RESTRICTED MODE (ORA-65177) in Oracle 19c[…]

Optimizer Access Paths: index unique scan

Posted on by sanjeeva

In our previous blog, we have talked about the optimizer access path. In the last few blogs, we have explained different access paths. In this blog, we will see how the index unique scan works. Index unique scan is the optimizer access path where the database returns at most one row. It reads the index Read more about Optimizer Access Paths: index unique scan[…]

Optimizer Access Paths : Fast Full Scan

Posted on by sanjeeva

An index fast full scan reads the entire index as it is i.e. as it exists on the disk. This index read is unsorted. This access path usage multiblock IO to perform the index scan. Index fast full scan simply reads all of the leaf, branch and root block of index quickly using multiblock IO Read more about Optimizer Access Paths : Fast Full Scan[…]

Oracle Direct Path Read: Complete Performance Guide with Real Examples

Posted on by sanjeeva

Have you ever wondered why some of your Oracle queries bypass the buffer cache entirely and read data directly from disk? Recently, we encountered this exact scenario while investigating performance issues in a data warehouse environment, and I wanted to share our findings about Oracle’s direct path read mechanism. Oracle Direct Path Read is a Read more about Oracle Direct Path Read: Complete Performance Guide with Real Examples[…]

Optimizer Access Paths : What is Index Full Scan

Posted on by sanjeeva

Index full scan is the optimizer access path where the database reads the index from start to end, one block at a time. It reads the index starting from the root block, navigating down using branch towards the left side, or right side if we are doing a descending full scan until it hits the Read more about Optimizer Access Paths : What is Index Full Scan[…]

Optimizer Access Paths: How Index FFS works with Distinct

Posted on by sanjeeva

Distinct is the keyword that oracle used to filter out unique values from a column. However, it is very interesting to see the behavior followed by the optimizer for its access path. In this “Optimizer Access Paths” blog, we will see how we can make optimizer to use an index with a distinct keyword. Let’s Read more about Optimizer Access Paths: How Index FFS works with Distinct[…]

Optimizer Access Paths: How oracle database works

Posted on by sanjeeva

In this blog, we will explore what is the access path in the Oracle database. However, before moving to the access path, let’s first understand what is a row source. It is a set of rows that are returned in a step by execution plan. It can be a table or a result of join operations Read more about Optimizer Access Paths: How oracle database works[…]

Cloud database war: Advantage shifting to Red?

Posted on by sanjeeva

With its inception in 2006, Amazon AWS has definitely gone a long way.  Engineers from Amazon worked really-really well which has not only completely changed the horizon of cloud but also emerges as one of the boon for any business to adopt.  Although, there are many other vendors available in the cloud market and they Read more about Cloud database war: Advantage shifting to Red?[…]

Data Pump: impdp

Posted on by sanjeeva

Problem Statement: Restore entire database using Data Pump. Restore table(s) Restore tablespace(s) Restore schema(s) Restore using Transportable tablespaces (TTS) Restore from multiple small sizes of dump files Restore in parallel mode Approach: There are single shot solution to all the above problem statement and it is IMPDP in Data Pump.  It is one of various Read more about Data Pump: impdp[…]

DBA_HIST_SEG_STAT Oracle database growth analysis dashboard showing segment statistics and capacity trends

DBA_HIST_SEG_STAT: SQL Queries to Track Oracle Growth

Posted on by sanjeeva

Introduction Have you ever needed to analyze Oracle database growth trends but found yourself limited by current snapshot data? In real production environments, tracking database growth trends is a critical aspect of DBA life because this allows businesses not only to foresee the real position in terms of existing hardware but also enables planning future Read more about DBA_HIST_SEG_STAT: SQL Queries to Track Oracle Growth[…]

Oracle Data Pump: expdp & impdp

Posted on by sanjeeva

Problem Statement: Backup entire database using Data Pump. Backup table(s) Backup tablespace(s) Backup schema(s) Backup using Transportable tablespaces (TTS) Generate multiple small sizes of dump files Backup in parallel mode Approach: There are single shot solution to all the above problem statement and it is Data Pump.  It is one of various backup tools provided Read more about Oracle Data Pump: expdp & impdp[…]

Data Pump: a tool to backup and restore database

Posted on by sanjeeva

Among various techniques of backing up your database Oracle provides data pump as one of tool which they are constantly improving and making this tool sharper release by release. Since its first launch with 10g version, it has improved a lot not only in terms of its new features but in terms of performance as Read more about Data Pump: a tool to backup and restore database[…]

How to re-organize your data files of a tablespace

Posted on by sanjeeva

Problem statement:  How to move data files from one location to another on same storage. How to move data files from one storage to another. How to rename data files to make data file name standardized Environment / Scenario: You have a database where you have to move your data files from old slower storage Read more about How to re-organize your data files of a tablespace[…]

How to copy Multi terabyte data to another Database Server

Posted on by sanjeeva

Problem statement:  How to migrate huge data from One DB to another DB. Multi-Terabyte data loaded on one database should be copied to another database. Environment: You have multi-terabyte Database Your database is growing on daily basis, based on data feeds. Number of Indexes on these tables are very high, and thus, size of indexes Read more about How to copy Multi terabyte data to another Database Server[…]

Move DB with Oracle 11g Binaries

Posted on by sanjeeva

Problem Statement: Move DB with Oracle Binaries on New Storage Create new DEV/UAT from Production. Scenarios: While creating new UAT or DEV from production and make this version of oracle to the same patch set level as of production, there are more than to move DB with Oracle Binaries. Examples: One way to move DB Read more about Move DB with Oracle 11g Binaries[…]

How to: Speed up Inserts in database

Posted on by sanjeeva

We have written blogs which helps DBA doing their day to day works.  This blog will explain how to Speed up Inserts while loading data from huge files.  Problem Statement: The problem statement this blog is going to answers with respect to Speed up Inserts are: How to speed up loading millions of rows from Read more about How to: Speed up Inserts in database[…]