Category: post

This will hold all blog related to Oracle Database

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[…]

Oracle Data Pump Import Date Format Issues: Solving RMAN Way

Posted on by sanjeeva

Introduction Data Pump Import Date Format Issues can bring database migrations to a halt, particularly when dealing with timestamp formats in table creation SQLs like ‘1998-01-01 00:00:00’. Usually this got resolved using setting up NLS_DATE_FORMAT, however, this traditional NLS_DATE_FORMAT solutions might fail, this guide presents a practical RMAN (Recovery Manager) approach to resolve these Data Read more about Oracle Data Pump Import Date Format Issues: Solving RMAN Way[…]

Creating and Managing the ASMSNMP User in Oracle ASM

Posted on by sanjeeva

Introduction The ASMSNMP user plays a crucial role in Oracle ASM (Automatic Storage Management), primarily enabling monitoring through Oracle Enterprise Manager (OEM) and we learn Creating ASMSNMP User in Oracle ASM. In this guide, we’ll cover: This step-by-step guide ensures your ASM environment is properly monitored while following Oracle best practices. Understanding ASMSNMP in Oracle Read more about Creating and Managing the ASMSNMP User in Oracle ASM[…]

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[…]

Optimizer Access Paths : Direct Path Read

Posted on by sanjeeva

In usual business, Oracle reads data from data files into the buffer cache of SGA. A Read request is served from there to the client. However, A direct path read is where the data is read directly from the data files into the PGA rather than into the buffer cache in the SGA. Direct Path Read more about Optimizer Access Paths : Direct Path Read[…]

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[…]

Trend of data growth in Oracle Database

Posted on by sanjeeva

In real production world “Prediction” of data growth is an important aspects of DBA life because this will allow business not only to foresee the real position in terms of existing hardware but also enable to plan the future expenses which should be spent on hardware(s) & storage. To generate data growth report, Oracle provides Read more about Trend of data growth in Oracle Database[…]

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[…]