Saturday 11 November 2023

Debunking 10 Myths about Database Administrators! πŸ”₯

Database administrators (DBAs) play a critical and often underappreciated role in the world of information technology. 

We might say they are the "groovy guardians of data harmony" in the digital universe. Here are some whimsical thoughts and myths about DBAs 


Myth #1: DBAs just manage databases.

Reality: One common myth is that DBAs only manage databases. In reality, their role is much more diverse and involves tasks like performance tuning, capacity planning, data security, backup and recovery, and more. πŸ’ͺ


Myth #2: DBAs only need technical skills.

Reality: While technical expertise is essential for a DBA, they also need strong communication, problem-solving, and project management skills to work effectively with teams and stakeholders.


Myth #3: Automation will replace Oracle DBAs soon.

Reality: Automation tools can simplify certain tasks, but they can't entirely replace DBAs. DBAs play a crucial role in designing automation strategies and handling complex issues that require human intervention.πŸ€–πŸ’Ό


Myth #4: DBAs are not required in the cloud.

Reality: With the shift to cloud-based databases, some believe DBAs are no longer necessary. However, cloud database management still requires skilled professionals to configure, optimize, and secure the databases. ☁️πŸ’»


Myth #5: DBAs are only needed during implementation.

Reality: Some assume DBAs are only required during the initial setup of the database. In reality, their ongoing maintenance, performance monitoring, and troubleshooting remain essential throughout the database's lifecycle. πŸ”„πŸ”§


Myth #6: Only large enterprises need DBAs.

Reality: Small and medium-sized businesses may believe they don't require a dedicated DBA. However, any organization dealing with critical data can benefit from having a skilled DBA. πŸ’ΌπŸ’


Myth #7: DBAs are on-call 24/7.

Reality: While some organizations may require 24/7 support, many DBAs follow regular business hours and have backup procedures in place to handle emergencies during off-hours.πŸ•°️🚨


Myth #8: DBAs are replaceable with junior staff.

Reality: Hiring inexperienced staff to replace skilled DBAs can lead to costly mistakes and inadequate management of critical data. πŸš«πŸ’”


Myth #9: DBAs can recover data from any disaster instantly.

Reality: While DBAs implement robust backup and recovery solutions, restoring data after a disaster takes time, depending on the complexity and scope of the issue.πŸ•°️πŸ“‚


Myth #10: Becoming a DBA requires a computer science degree.

Reality: While a computer science degree can be beneficial, it is not the only path. Many successful Oracle DBAs come from diverse educational backgrounds and gain experience through certifications, on-the-job training, and self-learning. πŸ“šπŸŽ“πŸ‘¨‍πŸ’»


In conclusion, Oracle DBAs are essential professionals responsible for managing and maintaining critical databases, and understanding the realities of their roles can help organizations make better decisions regarding their database management needs.πŸš€πŸ“š


Saturday 4 November 2023

A Case Study: Oracle Database vs. PostgreSQL

 Considering the best database management system for your organization? Let's explore the key differences between Oracle Database and PostgreSQL, two powerful contenders in the database landscape.


  Oracle Database  

Developed by Oracle Corporation, Oracle Database is an enterprise-grade, high-performance relational database management system. It offers advanced security, high availability, and support for complex data types. However, licensing costs and hardware requirements can be substantial, making it more suitable for large enterprises.


  PostgreSQL  

PostgreSQL is an open-source, object-relational database system renowned for its reliability and extensibility. It provides a comprehensive set of features, including support for JSON, XML, and GIS data. Its cost-effectiveness makes it attractive for startups and small to medium-sized businesses.


  Performance and Scalability  

Oracle Database's performance is exceptional, especially for heavy workloads and complex queries. PostgreSQL offers strong performance but may require more fine-tuning to achieve peak efficiency. Its extensibility and ability to scale horizontally make it compelling for data-driven businesses.


  Data Security  

Oracle Database is renowned for robust security features, granular access controls, encryption, and auditing capabilities. PostgreSQL is secure but may require additional configurations to match Oracle's level. The active community frequently releases updates to enhance security.


  Ease of Use and Administration  

PostgreSQL has made strides in user-friendliness and administration. Its tools are intuitive, making it accessible to developers and administrators. Oracle Database, with its vast features, can be more complex to set up and manage, necessitating skilled database administrators.


 Licensing Costs and Total Cost of Ownership (TCO)  

PostgreSQL stands out with no upfront licensing costs, allowing businesses to allocate resources elsewhere. Oracle Database's initial investment may be higher, but advanced features could provide long-term benefits for enterprises.


  Use Cases  

Oracle Database fits mission-critical, data-intensive applications like ERP systems. PostgreSQL excels in web applications, content management systems, and startups seeking cost-effectiveness and scalability.


 Community and Support  

Oracle Corporation offers extensive customer support, while PostgreSQL relies on its active community and commercial vendors. Many find the PostgreSQL community-driven support sufficient and effective.


  Conclusion  

The choice between Oracle Database and PostgreSQL depends on your organization's specific needs and preferences. Oracle offers unmatched performance and security but at a premium cost. PostgreSQL provides a scalable, cost-effective open-source alternative without compromising reliability. Evaluate your requirements, budget, and strategize for lasting business impact.

Saturday 7 October 2023

Facts: Native vs pglogical Logical Replication in PostgreSQL

Here are some of the facts about Native vs pglogical Logical Replication in PostgreSQL 

1. For Physical Replication (streaming WAL replication) pg_replication_slots.slot_type is 'physical' . For Logical Replication pg_replication_slots.slot_type is 'logical', but the pg_replication_slots.plugin will have different values based on the type of logical replication used.

2. pg_replication_slots.plugin is 'pgoutput' for the Native (Pub/Sub model available from v10+) Logical replication. It is 'pglogical_output' when using the pglogical extension.

3. Database Migration Services for various CSP uses Logical replication for migration.

4. Some other CDC Tools come with different logical replication plugins.

5. We cannot perform DMLs on PostgreSQL Physical standbys/read replicas which are created using physical streaming replication.

We will get the below error-"cannot execute INSERT in a read-only transaction"

6. We can perform DMLs on Logical standbys.

7. Logical standbys can be a source for Database Migration Services provided by AWS/GCP. 

8. The physical standby/read replicas created in AWS/GCP/Azure cannot act as a source for DMS Jobs.

9. pg_is_in_recovery() will return 'f' for Logical replica/standby instances and pg_is_wal_replay_paused() will return an error.

10. pg_is_in_recovery() will return 't' for Physical standby instances and pg_is_wal_replay_paused() will return 'f' if streaming replication is going on without any errors. Also pg_stat_wal_receiver will return data.

11. Pglogical replicates sequences but Native Replication does not.

Pglogical.replication_set_add_sequence and pglogical.replication_set_add_all_sequences can be used to add sequence/s and synchronize sequence data.

12. Pglogical allows Selective replication of table rows at either publisher or subscriber side (row_filter) , whereas Native replication supports row filter at publisher side from Postgres version 15 onwards.

13. Pglogical allows Selective replication of table columns at publisher side

14.Pglogical allows data gather/merge from multiple upstream servers and use of pglogical.conflict_resolution to resolve conflicts when a subscriber node is subscribed to multiple providers and/or local writes are allowed in the subscriber node.

15. Pglogical does not allow to replicate UPDATEs and DELETEs for tables that do not have Primary Keys, only INSERTs are replicated. Native logical replication allows tables without primary key to be replicated which have a REPLICA IDENTITY FULL set.

16. Pglogical copies the schema(using synchronize_structure of pglogical.create_subscription) at the subscriber node but in Native replication database schema and DDL commands are not replicated automatically.

17. Pglogical gives the option to re-syncronize a single table which has fallen behind using pglogical.alter_subscription_resynchronize_table but in Native replication the entire subscription needs to be refreshed.


Friday 29 September 2023

Agile is NOT FAST….

Agile is a widely adopted and highly effective approach to software development and project management in the IT environment. It has gained popularity for several reasons, and its principles and practices have proven to be beneficial for many organizations. 


Agile may not deliver the expected speed if several factors are hindering its effectiveness including: 


1. Dysfunctional teams

2. Lack of structure

3. Unclear rules of engagement

4. High turnover in teams

5. Ongoing disagreements over the definition of agile

6. Absence of a clear system to track and monitor work

7. Inefficient communication channels

8. Frequent team restructuring

9. Inflexible governance

10. Misalignment of people with their roles

11. Poorly laid foundations


Talking about agile as a fast approach becomes unjustified when grappling with these issues and then blaming its ineffectiveness.


To truly harness speed, it's essential to ensure that everyone is well-informed about our destination, the planned route to get there, the means of achieving it, and how relevant stakeholders are involved. We should also be prepared to handle obstacles and adapt to changes along the way. Above all, flexibility in decision-making is crucial.


Agile can be fast, when done right! 

Keep exploring :)

Monday 4 September 2023

Trace performance issues in PostgreSQL using the pg_stat_statements

 Trace performance issues in PostgreSQL using the pg_stat_statements



πŸ‘‰ The pg_stat_statements view is a PostgreSQL extension that provides information about the execution statistics of all SQL statements that have been executed by the PostgreSQL server.


πŸ‘‰ You can use the pg_stat_statements view to trace performance issues by identifying the queries that are taking a long time to execute and then analyzing the execution plan for those queries.

Here are some of the different ways to use the pg_stat_statements view to trace performance issues:

πŸ”― Identify the slow queries. You can use the total_time column to identify the queries that are taking a long time to execute. The queries with the highest total_time values are the ones that are most likely to be causing performance problems.

SELECT * FROM pg_stat_statements
ORDER BY total_time DESC

πŸ”― Identify the queries that are frequently executed. You can use the calls column to identify the queries that are frequently executed. The queries with the highest calls values are the ones that are most likely to be causing performance problems, even if they do not take a long time to execute individually.

SELECT * FROM pg_stat_statements
ORDER BY calls DESC;

πŸ”― Identify the queries that are using a lot of resources. You can use the rows and memory columns to identify the queries that are using a lot of resources. The queries with the highest rows values are the ones that are processing the most data. The queries with the highest memory values are the ones that are using the most memory.

SELECT * FROM pg_stat_statements
ORDER BY rows DESC;

πŸ”― Identify the queries that are changing their query plan frequently. You can use the plan_changes column to identify the queries that are changing their query plan frequently. The queries with the highest plan_changes values are the ones that are the most difficult for PostgreSQL to optimize.

SELECT * FROM pg_stat_statements
ORDER BY plan_changes DESC;

Once you have identified the queries that are causing performance problems, you can use the information in the pg_stat_statements view to analyze the execution plan for those queries. This can help you to understand why the queries are slow and how you can improve their performance.

Sunday 6 March 2022

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. The existing readers continue to see the old version of the row whereas new transactions see the new version. This is rather a very simplified presentation of MVCC. In the real world, there can be many versions of the row and a transaction can see one of those versions. The visibility depends on the transaction start time and the transaction isolation level.


The great advantage of MVCC is that readers don't wait for the writers to finish their work. Similarly, writers don't wait for the readers because they work on independent versions of the row. The downside is though that the table may get bloated with the multiple versions of a row. Sooner or later all but one version become invisible to all current or new transactions. The invisible versions are said to be DEAD rows. A maintenance activity called as VACUUM is required to recover these dead rows. The VACUUM scans the entire table, collect the DEAD rows and removes the corresponding index entries. This requires multiple passes over the table. As you can imagine, this can conflict with the normal database operations and can considerably slow down the operations, especially for heavily updated, large tables.


One may argue why we just don't remove the DEAD rows as and when we find them during normal database operations. The problem with that is we also need to cleanup the corresponding index entries. Otherwise the index entries may point nowhere, thus creating dangling references or even worse may point to unrelated rows (if the DEAD row is replaced by some other row), thus giving wrong results.

Finally, it's not possible to reach to the index entries from the heap tuples, making it extremely difficult to remove the dead rows easily.


PostgreSQL has the facility of AutoVacuum which is like a daemon service which periodically checks the tables and automatically vacuums them based on certain configuration parameters. This greatly simplifies the maintenance task.


Friday 4 February 2022

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.



     // Tracing primary site 



DGMGRL> edit database 'primary_db_name' set property LogArchiveTrace='1';



    // Tracing standby site



DGMGRL> edit database 'standby_db_name' set property LogArchiveTrace='1';




Also this parameter can be set by issuing a SQL statement :

SQL> ALTER SYSTEM SET LOG_ARCHIVE_TRACE=1;



dgmgrl  -debug / shutdown command  will shutdown instance, generate trace file and then exit. Tracefiles will be generated in the specific dump location.


Values for LOG_ARCHIVE_TRACE are described in table bellow:


LevelMeaning
0Disables archived redo log tracing (default setting)
1Tracks archiving of log files
2Tracks archive status by archive log file destination
4Tracks archive operational phase
8Tracks archive log destination activity
16Tracks detailed archive log destination activity
32Tracks archive log destination parameter modifications
64Tracks ARCn process state activity
128Tracks FAL server process activity
256Tracks RFS Logical Client
512Tracks LGWR redo shipping network activity
1024Tracks RFS physical client
2048Tracks RFS/ARCn ping heartbeat
4096Tracks real-time apply activity
8192Tracks Redo Apply activity (media recovery or physical standby)

Saturday 15 January 2022

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!


This has been chosen based on the significant contribution and activity in the Oracle and PostgreSQL Database technical community.


Thank you Anuj and Feedspot team for choosing me to present.


Here are the links for Top Database Blogs in Feedspot.


Explore more Blogs and Podcasts in various categories at Feedspot here : 

https://blog.feedspot.com/blogs_directory/?_src=menu








Saturday 8 January 2022

Consistency check before performing RMAN resetlogs.


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

Saturday 18 December 2021

12c Wallet creation and tablespace encryption

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

Saturday 4 December 2021

Datapump Errors and Solutions

   

SCOPE

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

Friday 19 November 2021

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.

Saturday 6 November 2021

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.

Sunday 17 October 2021

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.

Saturday 2 October 2021

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.