Flashback Package

There are times when the developers or you are working on a certain package and it gets overwritten, gets deleted or something worse happens.
This causes a problem of data loss as well as stress. But what if there is a way to get back the package that you were trying t modify?

What is flashback?

Oracle Flashback Technology provides a way to revert database entries to a previous state without performing a traditional recovery from backup.

According to Oracle, Flashback Technology has the following capabilities:

Member Wait Events in Wait Classes

Today I will be sharing the member wait events present in wait event classes. By looking into the list one can conclude to which parent class the occurring events belong to and narrow down the investigation.
Oracle Wait Events are conditions where a session is waiting for something to happen.  A wait event can be caused by many things, from slow read/write speeds on the disk, to locking situations caused by the architecture, to various kinds of Oracle contentions.  Waits are either system-level or session-level.  A session-level wait event is an event that affects a single user activity within the database.  System-level wait events affect the entire database system.

Changes in Oracle 19c standard edition

Oracle has made some huge changes to the standard edition of 19c version.
The changes made are big and we can believe that Oracle is trying to push standard edition users towards enterprise edition.
Let’s take a look at some of the changes in the standard edition 19c onward:

Active connection script

Today I will be sharing a script for checking active connections in the database.
You can implement this script and couple it with different purposes like, shutting down the database or triggering rman or datapump.

So here in this script, I’m calling the SQL script from the bash prompt.
You can change the script as per your usage.

Generate AWR Snapshot Manually

Automatic Workload Repository (AWR) is a collection of database statistics owned by the SYS user. By default snapshot are generated once every 60min .

But In case we wish to generate awr snapshot manually, then we can run the below script.  This is usually useful, when we need to generate an awr report for a non-standard window with smaller interval.

Lets say, we want to generate a report for next 15 minutes. (12:10 – 12:25) . So we will generate a snapshot at 12:10 and another at 12:25 .

SRVCTL (Server Control utility) commands in Oracle

srvctl commands – 12c

• To manage Oracle ASM on Oracle Database 12c installations, use SRVCTL in the Oracle Grid Infrastructure home for a cluster (Grid home). If you have Oracle RAC or Oracle Database installed, then you cannot use SRVCTL in the database home to manage Oracle ASM.
• You cannot use this command when OCR is stored in Oracle ASM because it will not stop Oracle ASM. To stop Oracle ASM you must shut down Oracle Clusterware.

Script for killing sessions in Oracle

Hi friends, I am sharing the script generating kill statements based on various parameters.
It is crucial for DBAs to have such scripts handy in crunch situations.

Note – You can save the queries in <file_name>.sql and then call them in your command promptt.

Plan Hash Value change script

Hi all,
This script will help you track the plan hash value change of the sqlid.
It comes handy when you are trying to troubleshoot unusual slowness reported by client or user.

Event and Session script

Hi friends, being a DBA is a challenging job when at times you are facing multiple issues.
It becomes difficult to keep track of everything, hence the use of scripts to automate trivial things is very essential.
Here I will be sharing some of my scripts for trivial events which may prove useful for many.

In this blog I am sharing the script for checking the session details given the sid and instance number of the session and for checking which event is going on in the database.

Data-pump V/S Export-Import

Let us go through some of the differences between Data-pump which is successor of the original Export-Import.
This blog will help you understand the key differences and features of both the utilities.