Ensuring Data Integrity with Oracle 21c Data Pump Checksum

 Today, we’re diving into a fantastic feature of Oracle 21c – the Data Pump checksum parameter. This tool helps ensure your Data Pump dump files are valid and intact. Let’s explore how to use it and why it’s a game-changer for data integrity.

A DBA's Guide to Managing large size Databases

The life of a database administrator – filled with challenges, triumphs, and the occasional headache-inducing task of managing large databases which are terabytes in size. I present to you a roadmap to happiness amidst the vast expanse of data. Here's how to navigate the path of large-scale database management with a smile on your face:

1. Storage Serenity

Embrace the power of high-performance storage solutions like solid-state drives (SSDs) and storage area networks (SANs) to tame the beast of large database workloads.

• Spread your data files across multiple physical disks or storage devices to harness the magic of parallel I/O operations and ward off pesky I/O bottlenecks.
• Keep a watchful eye on disk space usage and plan for ample storage capacity to accommodate the ever-growing data size. After all, a happy DBA is a well-prepared DBA!

2. Backup and Restore 

Craft a robust backup and restore strategy, complete with full backups, differentials, and transaction log backups.

• Embrace the magic of backup compression to shrink backup sizes and speed up the restoration process.
• Regularly put your restore process to the test, ensuring a happy ending even in the face of adversity.
• Analyze the backup failures via alerting and ensure repetitive alerts are addressed.

3. Partitioning Paradise

Partition your tables and indexes to break down the colossal entities into more manageable chunks.

• Enjoy the benefits of better data distribution, enhanced query performance, and simplified maintenance operations.
• Select a partitioning strategy tailored to your data's unique characteristics and query patterns – because one size does not fit all in the land of databases.

4. Maintenance Operations Wonderland

Tread carefully through the realm of maintenance tasks, scheduling and automating with precision.

• Consider performing these tasks during off-peak hours to keep production systems humming along happily.

5. Indexing Euphoria

Craft and maintain your indexes with care to support swift data retrieval and query performance.

• Explore the wonders of filtered indexes to hone in on the relevant data subset, shrinking index size and boosting query performance.
• Regularly tend to your index statistics to keep query optimization in top-notch condition.

6. Compression Comfort

Harness the power of data compression to shrink storage footprints and elevate I/O performance to new heights.

• Enable compression for your hefty tables and indexes, especially those facing read-intensive workloads.
• Strike the perfect balance between storage savings and CPU overhead to find your compression sweet spot.

7. Query Optimization Bliss

Devote time to fine-tuning your queries for optimal execution against those mighty tables.

• Embark on a quest through query plans, vanquishing performance bottlenecks with strategies like indexing, query rewriting, and partition elimination.
• Make merry with features like columnstore indexes and in-memory OLTP to supercharge your queries when the need arises.

8. Monitoring and Performance Tuning Utopia

Keep a vigilant eye on database performance using built-in monitoring tools or third-party solutions.

• Track key performance indicators like disk I/O, CPU utilization, and query execution times, making adjustments as needed.
• Optimize server and database configurations based on monitoring insights for a performance paradise.

9. Archiving Awesomeness

Embrace the art of data archiving and purging to maintain a trim and tidy database. Bid farewell to historical or infrequently accessed data with grace, reducing the load on your production database.

10. Scalability and High Availability Elysium

Explore high availability features like Always On Availability Groups or database mirroring to ensure data resilience.

Consider scalability options like partitioning and scaling (both horizontal and vertical) to accommodate future growth and workload spikes.

And there you have it, fellow DBAs – Go forth, armed with knowledge and a smile, and may your databases be ever optimized and your queries lightning-fast.

PostgreSQL VACUUM and MVCC

   PostgreSQL uses MVCC (multi-version concurrency control) to provide transactional semantics. In the simplest form, MVCC lets each transaction work on a different copy of a row in a table. What it means is whenever a row is updated, a new version of the row is created.

Tracing Oracle Data Guard

 Tracing can also be enabled in dataguard using the parameter "Log_Archive_Trace"

The values can be set using DG-Broker (if configured) or at SQL prompt as below.

Feedspot - Top database blog list

Dear All,

I am pleased to inform  that I received this recognition from Feedspot and I'm proud to be part of the awesome family- Thank you Anuj and Team for this recognition!

Consistency check before performing RMAN resetlogs.

The article covers consistency check before performing OPEN RESETLOGS after restoring a database using backup 

12c Wallet creation and tablespace encryption

The following article describes the steps to create the wallet and perform a tablepsace encryption.

Datapump Errors and Solutions

   

SCOPE

The article covers common & complex error occurs during the Datapump Export and Import operations

Oracle Instance Startup Failure | Instance terminated by GEN0

  

SCOPE

Recently a server running both Oracle databases with version 11.1.0.2  had to be forced powered cycled due to a NFS issue. Since it wasn’t possible to login to the O/S to shutdown the DBs an instance recovery was required on both the 11.2 databases.

Resmgr:Cpu Quantum | Oracle wait event

 

SCOPE

Application job are running slow and DBA finds out the wait event as "Resmgr:Cpu Quantum" for multiple sessions running in database.

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.