ASM Diskspace utilization categorized by Database/File names

SCOPE

Below script helps to identify the space utilization of Databases including the files such as Datafiles, Redologfiles, Archivelog files on ASM diskgroup categorized by Database names and their files.

ORA-12519: TNS:no appropriate service handler found (followed by ORA-00020)

SCOPE

Application complains about the error ORA-12519: TNS:no appropriate service handler found while connection to the database.

Object related scripts for Oracle

 

Open Resetlog Failure : ORA-00392: log 1 of thread 1 is being cleared, operation not allowed

SCOPE

Restoring the database using RMAN backup.

After completion of cloning to the staging environment, OPEN RESETLOGS fails with error as below.

ORA-00392: log 1 of thread 1 is being cleared, operation not allowed

ORA-20001: Job failed: ORA-01630: max # extents (4096) reached in temp segment in tablespace USERS

SCOPE

Application jobs fails with messages as below.

ORA-20001: Job failed

ORA-01630: max # extents (4096) reached in temp segment in tablespace USERS

ORA-00600: internal error code, arguments: [qcisSetPlsqlCtx:tzi init]

 Below document explains the issue faced after the upgrade, troubleshooting  and need for timezone files.

Oracle or PostgreSQL

 

At the point when you take a gander at PostgreSQL versus Prophet data set administration frameworks, the primary distinction between these two information bases is that PostgreSQL is an open-source data set, while Oracle is a shut data set framework.

Shutdown is taking forever, can only do a SHUTDOWN ABORT

We recently performed the cloning of the staging Database from a production database, but unfortunately we faced an issue.

Once the restore is complete, we were not able to stop the database to rename it and perform the post checks. 

Identifying PID/SPID for killed session in 11g

Many times when we kill a session with alter system kill session command  ( or Toad )

PostgreSQL SSL Setup on Linux platform

PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security.

Installation and setting up Postgres 13 on RHEL

Setting up the PostgreSQL RDBMS in Red-Hat Linux is pretty easy.  Following article describes the installation and initial setup for a PostgreSQL database.

Official documentation : https://www.postgresql.org/download/linux/redhat/

Uncommitted transactions in Oracle

 Lets assume that we have an update, insert or delete statement that running but has not been committed yet. So question is how to show uncommitted transactions ?

Read the sqlnet trace files

While troubleshooting sqlnet  issue a week back and we used very same article  to read trace files. It was very useful.

Open Oracle Database with Delay Option

Found an interesting event, that helps to open the database with delay option. The event db_open_begin delay (opening database with delay)  is undocumented event ( read unsupported )

PostgreSQL Basic & Required Parameter Setting

    PostgreSQL works even if you start using it with the default settings without changing the parameter settings, but there may be various problems later, especially the default of performance-related parameters.

Is it possible to view database performance information without DBA privilege in Oracle 11g?

 

The DBA can grant you SELECT privileges to the V$ performance views. It's good for instance tuning, but not as effective as SQL Trace or ADVISOR privilege for query tuning.

How to kill own Oracle SQL sessions without DBA privileges?

 

To successfully run an ALTER SYSTEM command, you don't need to be the DBA, but you do need the ALTER SYSTEM privilege to be granted to you (or to the "user" owning the application through which you connect to the database - which may be different from "you" as the "user" ).

Postgres Frequently used commands - CHEAT SHEET

 \db- List all tablespaces

\dn- List all schemas

Postgresql Database Starts with 100% CPU processes ?

If you have a problem with PostgreSQL, it starts some processes that consume lots of resources. You kill those processes but they will restart after a few minutes.

Later you check the /var/log but there is nothing about this. What does that mean?😟

Oracle : Move indexes to another tablespace

 

Postgres High Availability Terms

 What is High Availability?

It is the amount of time that a service is available and is usually defined by the business.

Oracle Session Kill Script - Session Management

 

Possible ways to recover space from deleted rows with insufficient disk space remaining for full vacuum

 A recent scenario,

We had a single table with about 50M records that use around 80gb of space. Application recently deleted older records which was no longer needed reducing the Number of records to 30M.

What to do if listener log has grown too big in size

 

RMAN Backup Validate

 Sometimes RMAN backup files might get corrupted. Sometimes disk block corruption might happen. So it is better we do validate the database backups more frequently in order to make sure it is recoverable.