Tales From A Lazy Fat DBA

Fan of Oracle DB & Performance, PostgreSQL & Cassandra … \,,/

  • Likes

    • 249,795
  • Archives

  • Categories

  • Subscribe

  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Archive for the ‘Uncategorized’ Category

repmgr failover done, but where is my Standby ?

Posted by FatDBA on December 15, 2020

Hey Guys,

Last weekend I’ve got a call from one of my friend asking about a weird issue in his master-slave (primary-standby) setup where he’s using repmgr for automatic failover and switchover operations. He said after the failover (server crashed) with role reversal or promotion the old standby becomes new primary (expected, all good till here), but as soon as the old primary comes back online and rejoins the configuration, it started as a primary too. I mean you can imagine something like below, both of the two hosts becomes master and there is no sign of a new standby.


[postgresdb@fatdba ~]$ repmgr -f /etc/repmgr.conf cluster show

 ID | Name  | Role    | Status    | Upstream | Location | Connection string
----+-------+---------+-----------+----------+----------+-----------------------------------------
 1  | node1 | primary | * running |          | default  | host=db_node1 dbname=repmgr user=repmgr
 2  | node2 | primary |   running |          | default  | host=db_node2 dbname=repmgr user=repmgr
 

He checked repmgr logs on the standby node and everything looks okay, standby promotion was sucessfull. It seems something was stopping the repmgr to update the information and bring that crashed node (previous primary) back as a standby. So the final situation is there are two primary nodes at this point.

So, how to and where to start from ?
A quick solution to this problem is to create an UPDATE TRIGGER on one of the internal table EVENTS of repmgr database (it creates its own database with default name of repmgr). The logic of creating a trigger is to handle events “standby_promote”, I mean when it happens a script is triggered that will bring back the old primary as new standby.

Next we would be needing a script that will help to automate the standby promotion. For that purpose you can use this script (link) or you can write something similar. And now when we have to call a bash script or to write stored procedures in a shell, we need the ‘.plsh’ extension in the database. Let’s install one of the most popular extension to do that, petere/plsh (link)


[postgresdb@fatdba ~]$ cd /usr/share/postgresql/12/extension/
[postgresdb@fatdba ~]$ sudo git clone https://github.com/petere/plsh.git
[postgresdb@fatdba ~]$ cd plsh
[postgresdb@fatdba ~]$ sudo make PG_CONFIG=/usr/bin/pg_config
[postgresdb@fatdba ~]$ sudo apt-get install make
[postgresdb@fatdba ~]$ sudo apt-get install gcc
[postgresdb@fatdba ~]$ sudo apt-get install postgresql-server-dev-12
[postgresdb@fatdba ~]$ sudo make install PG_CONFIG=/usr/bin/pg_config
 

Now, next create the extension in the database.


[postgresdb@fatdba ~]$ psql -U repmgr
 

This one needs to be executed only n primary side.


repmgr=# create extension plsh;
 

Now, let’s create the function and a trigger to invoke the script failover_promote.sh


[postgresdb@fatdba ~]$ psql
postgres=# CREATE FUNCTION failover_promote() RETURNS trigger AS $$
#!/bin/sh
/bin/bash /tmp/psqlscripts/failover_promote.sh $1 $2
$$
LANGUAGE plsh;
 

And the UPDATE TRIGGER on EVENTS table to call function created above.


postgres=#  create trigger failover
after insert
on events
for each row
execute procedure failover_promote();
 

Yes, that’s it. All done!
Now you can mimic the failover scenario i.e by stopping the primary database and check repmgr logs on the standby server.
You will see the standby is promoted and becomes new primary and as soon it’s finished, it will start pinging the crashed node and the moment it is back it will be promoted as a new standby.

Hope It helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , | Leave a Comment »

DDL generation in PostgreSQL & a TRICK …

Posted by FatDBA on December 3, 2020

Hi Guys,

One of my friend recently asked if there is any way to get DDL of all objects that exists inside a schema in PostgreSQL without using any third party tools, something similar like dbms_metadata what we have in Oracle. Though it sounds trivial, but it’s not and this indeed an interesting question. I mean if you have to collect DDL for an Index, View, constraint, trigger or a function you have predefined/in-build PostgreSQL functions (pg_get_*def) and it will be easy to get the structure, but what if you want DDL of a Table, sequence or a type (There few more) ?

You might be thinking of doing concatenations or by doing some coding, and yes that can be done but that will be tricky and complicated. So there isn’t any direct way of generating definitions of said objects, but here comes the PG_DUMP utility for the rescue, you can take the dump of the entire schema and redirect its output in to a SQL file and you can get all your DDLs from the dump file.

Let’s do some hands on, I am on EDB PostgreSQL Version 10.12.20.


[enterprisedb@fatdba ~]$ psql
psql.bin (10.12.20)
Type "help" for help.

enterprisedb=# set search_path=migr;
SET
enterprisedb=# \dtsvi+
                                                               List of relations
 Schema |            Name             |   Type   |    Owner     |         Table          |    Size    |              Description
--------+-----------------------------+----------+--------------+------------------------+------------+----------------------------------------
 migr   | audits                      | table    | enterprisedb |                        | 0 bytes    |
 migr   | audits_audit_id_seq         | sequence | enterprisedb |                        | 8192 bytes |
 migr   | audits_pkey                 | index    | enterprisedb | audits                 | 8192 bytes |
 migr   | bar                         | table    | enterprisedb |                        | 8192 bytes |
 migr   | bar_pk                      | index    | enterprisedb | bar                    | 8192 bytes |
 migr   | bigtab                      | table    | enterprisedb |                        | 944 kB     |
 migr   | bigtab1                     | table    | enterprisedb |                        | 112 kB     |
 migr   | bigtab1_vw                  | view     | enterprisedb |                        | 0 bytes    |
 migr   | foo                         | table    | enterprisedb |                        | 8192 bytes |
 migr   | foo_bar                     | table    | enterprisedb |                        | 8192 bytes |
 migr   | foo_pk                      | index    | enterprisedb | foo                    | 8192 bytes |
 migr   | i_mlog$_bar                 | index    | enterprisedb | mlog$_bar              | 8192 bytes |
 migr   | i_mlog$_foo                 | index    | enterprisedb | mlog$_foo              | 8192 bytes |
 migr   | mlog$_bar                   | table    | enterprisedb |                        | 8192 bytes | snapshot log for master table MIGR.BAR
 migr   | mlog$_foo                   | table    | enterprisedb |                        | 8192 bytes | snapshot log for master table MIGR.FOO
 migr   | person_address_details      | table    | enterprisedb |                        | 40 kB      |
 migr   | person_address_details_pkey | index    | enterprisedb | person_address_details | 16 kB      |
 migr   | person_info                 | table    | enterprisedb |                        | 40 kB      |
 migr   | person_info_pkey            | index    | enterprisedb | person_info            | 16 kB      |
 migr   | trigger_test                | table    | enterprisedb |                        | 8192 bytes |
(20 rows)
 

This is how you can generate DDL’s for your views, Indexes and others.


enterprisedb=# select pg_get_viewdef('bigtab1_vw'::regclass, true);
           pg_get_viewdef
------------------------------------
  SELECT bigtab1.id,               +
     bigtab1.created_date,         +
     bigtab1.lookup_id,            +
     bigtab1.data                  +
    FROM bigtab1                   +
   WHERE bigtab1.id > 950::numeric;
(1 row)
                           
enterprisedb=#  select pg_get_indexdef('person_address_details_pkey'::regclass);
                                                pg_get_indexdef
----------------------------------------------------------------------------------------------------------------
 CREATE UNIQUE INDEX person_address_details_pkey ON migr.person_address_details USING btree (person_address_id)
(1 row)
 

Let’s take a pg_dump backup of this schema and will look out for table and other object definitions.


[enterprisedb@fatdba dumpddl]$ pg_dump --schema migr > migr_ddl.sql
[enterprisedb@fatdba dumpddl]$
[enterprisedb@fatdba dumpddl]$ ls -ltrh
total 728K
-rw-rw-r--. 1 enterprisedb enterprisedb 728K Nov 10 11:34 migr_ddl.sql
 

[enterprisedb@fatdba dumpddl]$ more migr_ddl.sql |grep -A 5 --color 'CREATE TABLE'
CREATE TABLE migr.audits (
    audit_id bigint NOT NULL,
    table_name character varying(255),
    transaction_name character varying(10),
    by_user character varying(30),
    transaction_date timestamp without time zone
--
CREATE TABLE migr.bar (
    foo numeric NOT NULL,
    bar numeric NOT NULL
);


--
CREATE TABLE migr.bigtab (
    id numeric(12,6),
    v1 character varying(10),
    padding character varying(50)
);

--
CREATE TABLE migr.bigtab1 (
    id numeric(10,0),
    created_date timestamp without time zone,
    lookup_id numeric(10,0),
    data character varying(50)
);
--
CREATE TABLE migr.foo (
    foo numeric NOT NULL
);


ALTER TABLE migr.foo OWNER TO enterprisedb;
--
CREATE TABLE migr.foo_bar (
    foo numeric,
    bar numeric,
    foo_rowid rowid,
    bar_rowid rowid
);
--
CREATE TABLE migr."mlog$_bar" (
    "m_row$$" character varying(255),
    "snaptime$$" timestamp without time zone,
    "dmltype$$" character varying(1),
    "old_new$$" character varying(1),
    "change_vector$$" bytea(255),
--
CREATE TABLE migr."mlog$_foo" (
    "m_row$$" character varying(255),
    "snaptime$$" timestamp without time zone,
    "dmltype$$" character varying(1),
    "old_new$$" character varying(1),
    "change_vector$$" bytea(255),
--
CREATE TABLE migr.person_address_details (
    person_address_id numeric(5,0) NOT NULL,
    person_id numeric(5,0),
    city character varying(15),
    state character varying(15),
    country character varying(20),
--
CREATE TABLE migr.person_info (
    person_id numeric(5,0) NOT NULL,
    first_name character varying(20),
    last_name character varying(20)
);

--
CREATE TABLE migr.trigger_test (
    id numeric NOT NULL,
    description character varying(50) NOT NULL
);

 

So, we finally got the DDL of tables and all objects inside the schema.

Hope It helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , | Leave a Comment »

Thanks Percona for this great product called PMM (Percona Monitoring & Management Server)

Posted by FatDBA on December 2, 2020

Hi Guys,

For one of our customer we recently moved from on-prem to AWS cloud and we moved our entire infrastructure along with our MySQL & PostgreSQL Clusters. The very next question that started bothering us is to look out for any solid, stable and reliable monitoring and performance management tool, and then we learned about Percona’s Monitoring & Management Server (PMM).

After trying the setup we are pretty satisfied with it’s performance as it fulfils all our expectations from any monitoring tool to monitor our applications and databases. We found the product best in terms of Cost (comes with an hourly price of only $ 0.10/hr), highly secure (with SSL encryption) and offers some really cool and smart features.
There are many features what it offers, but being a long time Performance Consultant (DBA) I personally found the ‘Query Analytics’ section more useful and impressive. It has got everything what you need to know for your real-time workload, it provides an in-depth analysis of your queries i.e. execution plan, latency information, query fingerprints etc. This can provide very useful at the time when you have any problem in hand and want to deep dive in and want to know what all is going on with your system at the statement level.

It has got great integration with Grafana platform too, great data visualizations in the form of dashboards etc.

I will recommend this tool for people who are looking for any good stable monitoring platform along with performance administration tool. Not only for PostgreSQL & MySQL but it’s available for other platforms too like Amazon RDS MySQL, Amazon Aurora MySQL, MongoDB, Percona XtraDB Cluster and ProxySQL.

It’s available on both AWS and Azure markets.

Link to AWS Marketplace : https://aws.amazon.com/marketplace/pp/B077J7FYGX?qid=1605533229523&sr=0-1&ref_=srh_res_product_title

Link to Azure Marketplace: https://azuremarketplace.microsoft.com/en-us/marketplace/apps/percona.pmm_2?tab=Overview

 

Hope It Helped!

Prashant Dixit

Posted in Uncategorized | Tagged: , , | Leave a Comment »

Could not send replication command “TIMELINE_HISTORY”: ERROR: could not open file pg_wal/00xxxx.history

Posted by FatDBA on October 20, 2020

Hi All,

Ever encountered a situation where the backup history (TIMELINE_HISTORY) file was deleted by mistake or maybe someone removed it purposely, it was quite old and you try to restore a new backup. I remember many issues related with replication, backup tools (BARMAN & BART) that you might face if that file is removed from PG_WAL directory. Would like to discuss a problem that we encountered while taking BART backup on EDB version 10.

These ‘timeline files’ are quite important, as using the timeline history files, the pg_basebackup can follow the latest timeline present in the primary, just as it can follow new timelines appearing in an archive WAL directory. So, in short, it shows which timeline it branched off from and when. These history files are necessary to allow the system to pick the right WAL segment files when recovering from an archive that contains multiple timelines. So, its important to have this file there in WAL directory.


[enterprisedb@fatdba ~]$ bart -c /usr/edb-bart-1.1/etc/bart.cfg BACKUP -s edbserver --backup-name MAINFULLBKP_10-13-20
INFO:  DebugTarget - getVar(checkDiskSpace.bytesAvailable)
INFO:  creating full backup using pg_basebackup for server 'edbserver'
INFO:  creating backup for server 'edbserver'
INFO:  backup identifier: '1602788909136'
ERROR: backup failed for server 'edbserver' 

pg_basebackup: could not send replication command "TIMELINE_HISTORY": ERROR:  could not open file "pg_wal/00000002.history": No such file or directory 

1633701/1633701 kB (100%), 2/2 tablespaces
pg_basebackup: child process exited with error 1
pg_basebackup: removing data directory "/edbbackup/edbserver/1602788909136"
 

The file is not there under said directory.


[enterprisedb@fatdba ~]$ cd /edb/as10/as10/data/pg_wal/
[enterprisedb@fatdba pg_wal]$ ls
0000000200000005000000EA  0000000200000005000000EB.00000060.backup  0000000200000005000000ED  archive_status
0000000200000005000000EB  0000000200000005000000EC                  0000000200000005000000EE
 

In case of file missing/moved, you can always create a brand new empty file and that will be used by the respective utility and will be populated with metadata soon after. So, in order to quickly restore this issue, let’s create one.

 [enterprisedb@fatdba pg_wal]$ touch 00000002.history
[enterprisedb@fatdba pg_wal]$
[enterprisedb@fatdba pg_wal]$ ls *hist*
00000002.history 

Let’s try to take the backup once again.


[enterprisedb@fatdba pg_wal]$ bart -c /usr/edb-bart-1.1/etc/bart.cfg BACKUP -s edbserver --backup-name MAINFULLBKP_10-13-20
INFO:  DebugTarget - getVar(checkDiskSpace.bytesAvailable)
INFO:  creating full backup using pg_basebackup for server 'edbserver'
INFO:  creating backup for server 'edbserver'
INFO:  backup identifier: '1602789425665'
INFO:  backup completed successfully
INFO:
BART VERSION: 2.5.5
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1602789425665
BACKUP NAME: MAINFULLBKP_10-13-20
BACKUP PARENT: none
BACKUP LOCATION: /edbbackup/edbserver/1602789425665
BACKUP SIZE: 1.57 GB
BACKUP FORMAT: tar
BACKUP TIMEZONE: Europe/Berlin
XLOG METHOD: stream
BACKUP CHECKSUM(s): 0
TABLESPACE(s): 1
 Oid     Name      Location
 42250   UNKNOWN   /edb/as10/as10/data_test/pg_tblspc

START WAL LOCATION: 0000000200000005000000ED
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2020-10-15 21:17:05 CEST
STOP TIME: 2020-10-15 21:17:38 CEST
TOTAL DURATION: 33 sec(s)


[enterprisedb@fatdba pg_wal]$  bart -c /usr/edb-bart-1.1/etc/bart.cfg SHOW-BACKUPS
 SERVER NAME   BACKUP ID       BACKUP NAME            BACKUP PARENT   BACKUP TIME                BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS

 edbserver     1602789425665   MAINFULLBKP_10-13-20   none            2020-10-15 21:17:38 CEST   1.57 GB       16.00 MB      1           active
 

And it worked.


Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , , | Leave a Comment »

pg_dump: aborting because of server version mismatch — pg_restore: [archiver] unsupported version (1.13) in file header

Posted by FatDBA on October 16, 2020

Hi Guys,

First of all, this isn’t a problem but something that you should always set in case if your have multiple PostgreSQL versions or flavors running on the same host, else you might encounter some really strange errors. Few of the examples – You have PostgreSQL Community and EDB running or you have two different installations (versions) on the same server.

This can cause some basic command to fail, like in below example the pg_dump utility throwed an error about “server version mismatch” and it was all because this a POC box where we have more than three different PostgreSQL installations (Community PostgreSQL and EDB) and all of different versions. Two of them are for the EDB with same user ‘enterprisedb’ and one for community version. So either you set your bash_profile smartly, else you try something what I will be discussing next.

Okay, so this was the exact error what I have got when I tried to call pg_dump.


-bash-4.1$ pg_dump -p 6001 -U postgres -t classes > /tmp/classestable_psql_commdb_dump.dmp
pg_dump: server version: 11.9; pg_dump version: 8.4.20
pg_dump: aborting because of server version mismatch
 

There could be multiple issues or errors that you might encounter, one more that could arise due to multiple installations on same host.
Below, pg_restore failed with ‘unsupported version’ error.


-bash-4.1$ pg_restore -d postgresqlnew -h 10.0.0.144 -U postgres /tmp/commpsql_fulldbbkp.dmp
pg_restore: [archiver] unsupported version (1.13) in file header
 

This seems strange initially because the version of the utlity and postgresql is exactly the same.


-bash-4.1$ pg_restore --version
pg_restore (PostgreSQL) 8.4.20
-bash-4.1$ psql --version
psql (PostgreSQL) 8.4.20
 

Okay, let’s find how many pg_dump utilities exists in this database and their location.


-bash-4.1$ find / -name pg_dump -type f 2>/dev/null
/opt/edb/as10_BACKP_10042020SATIND/bin/pg_dump
/edb/as10/as10/bin/pg_dump
/usr/bin/pg_dump
/usr/pgsql-11/bin/pg_dump
/usr/edb/as10/bin/pg_dump
/usr/edb/as11/bin/pg_dump
 

So, we have 3 different pg_dump utlities here, all from different locations, and I know which version I would like to call. So, I can create a symbolic link to get rid of this error or to avoid writing the full/absolute path.


-bash-4.1$ sudo ln -s /usr/pgsql-11/bin/pg_dump /usr/bin/pg_dump --force
[sudo] password for postgres:
 

Great, It’s done. You can do the same for pg_restore too. Now lets try to call the same command all over again, to take a backup of single table with name ‘classes’.


-bash-4.1$ pg_dump -p 6001 -U postgres -t classes > /tmp/classestable_psql_commdb_dump.dmp

-bash-4.1$ ls -ll /tmp/classes*
-rw-r--r--. 1 postgres postgres 915 Oct 15 11:41 /tmp/classestable_psql_commdb_dump.dmp
-bash-4.1$
 

And it worked as expected.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Leave a Comment »

EDB PostgreSQL BART Error: tablespace_path is not set

Posted by FatDBA on October 16, 2020

Today would like to discuss about the issue that we faced while doing a BART restore operation of one of the EDB 11 PostgreSQL instance. This was a new system under realization phase (before delivery to customer). So, during one of the test we saw the restore got failed with a message which says something about the value ‘tablespace_path’. I know I have a tablespace in this system, but I initially though that BART will take care of it by its own, but its was not the case.

Below was the error what I have encountered during the test.


[enterprisedb@fatdba archived_wals]$ bart -c /usr/edb-bart-1.1/etc/bart.cfg RESTORE -s edbserver -i 1602187005158 -p /edb/as10/as10/data/
INFO:  restoring backup '1602187005158' of server 'edbserver'
ERROR: "tablespace_path" is not set
[enterprisedb@fatdba archived_wals]$
 

Okay, let’s first check tablespace details, we use the metacommand of ‘db’ to get that info about tablespaces. Okay, so we have it’s location, size details.
Let’s go inside the said directoy and see what all is there.
Note: Last two are the default tablespaces so need to worry about them.


enterprisedb=# \db+
                                              List of tablespaces
    Name    |    Owner     |           Location           | Access privileges | Options |  Size   | Description
------------+--------------+------------------------------+-------------------+---------+---------+-------------
 newtblspc  | dixit        | /home/enterprisedb/newtblspc |                   |         | 52 kB   |
 pg_default | enterprisedb |                              |                   |         | 1362 MB |
 pg_global  | enterprisedb |                              |                   |         | 774 kB  |
(3 rows)

[enterprisedb@fatdba pg_tblspc]$ pwd
/edb/as10/as10/data_test/pg_tblspc
[enterprisedb@fatdba pg_tblspc]$ ls -ltrh
total 4.0K
lrwxrwxrwx. 1 enterprisedb enterprisedb   28 May  5 17:58 42250 -> /home/enterprisedb/newtblspc
drwx------. 3 enterprisedb enterprisedb 4.0K Oct  8 21:56 PG_10_201707211
 

Okay, so we have a soft-link created for the tablespace under PG_TBLSPC directory under DATA dir with OID 42250.
Now when we have all the information, time to add requisite parameter in bart.cfg file to consider tablespaces, just like below.
Format: OID_1=tablespace_path_1;OID_2=tablespace_path_2 …
example: tablespace_path = 42250=/edb/as10/as10/data_test/pg_tblspc
Note: tablespace_path parameter must exist or to be empty at the time you perform the BART RESTORE operation.

Now let’s modify our bart confguration file, will look something like below with the ‘tablespace_path’ option set.


[EDBSERVER]
host = 10.0.0.144
port = 5444
user = enterprisedb
backup_name = mktg_%year-%month-%dayT%hour:%minute
cluster_owner = enterprisedb
description = "EDB PROD server"
archive_command='scp %p enterprisedb@10.0.0.144:/edbbackup/edbserver/archived_wals/%f'
tablespace_path = 42250=/edb/as10/as10/data_test/pg_tblspc
allow_incremental_backups=enabled
 

All set for the restore now, let’s try that.


[enterprisedb@fatdba pg_tblspc]$ bart -c /usr/edb-bart-1.1/etc/bart.cfg RESTORE -s edbserver -i 1602187005158 -p /edb/as10/as10/data/
INFO:  restoring backup '1602187005158' of server 'edbserver'
WARNING: tablespace restore path is not empty (/edb/as10/as10/data_test/pg_tblspc), restoring anyway
INFO:  base backup restored
INFO:  writing recovery.conf file
INFO:  WAL file(s) will be streamed from the BART host
INFO:  archiving is disabled
INFO:  permissions set on $PGDATA
INFO:  restore completed successfully
[enterprisedb@fatdba pg_tblspc]$
[enterprisedb@fatdba pg_tblspc]$
 

This is fixed.

Hope That Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , | Leave a Comment »

Datastax Certified Cassandra Administrator, some tips & more

Posted by FatDBA on August 21, 2020

Hi Guys,

With a sharp rise in NoSQL databases, many of the organizations are making a transition from traditional databases to distributed and high performance databases like ‘Cassandra’. Cassandra has become Apache’s one of the most popular projects. Though there are multiple NoSQL databases available in the market but no one has the features like peer-to-peer architecture, HA and Fault tolerant, Column based, Highly perform-ant, Schema Less, tunable consistency, great analytical possibilities, easy to scale-up & scale-down, distributed and the list goes on and on and on.

Cassandra already proved it’s mettle and is magical for IoT, Sensor data, Event based, Time series data, voucher generation systems and with other data models. Datastax provides best in class database management software and wide-range services with 24×7 support to get more from your Cassandra. Alongside comes some really cool features and tools i.e. opscenter (GUI), Nodesync (for enti entropy repairs), great SOLR integration, dsetool (similar to nodetool with more capabilities), sstableloader, pre-flight check tool, yaml file compare tools, stress tools, extra commands i.e. dsefs and many more.

DataStax is a pioneer and they have their own Cassandra certification path/track to prove you have valid credentials to work with Cassandra database either as a developer or an administrator. Now question comes where to start ?? – In fact many of you have asked me about my latest credentials ‘Datastax Apache Cassandra 3.x Administrator Associate‘, I was getting questions like how to prepare, how to book the exam and many other related questions. So, this post will be all about covering topics like how to prepare and book exam along with few tips.

I would always prefer to go point wise to make things more ordered and easy to digest.

1. Create your account on Datastax Academy.
Link: https://auth.cloud.datastax.com/auth/realms/CloudUsers/login-actions/registration?client_id=absorb&tab_id=lv4-57nRbu4

2. Go to the option ‘Catalog’ to lookout for courses available.
You have to choose between the Administrator (3 course based curriculum) or Developer (3 Courses based curriculum) track. I have completed the ADMIN path and it has three courses DS101 (Introduction), DS201 (Foundations) and DS210 (Operations with Apache Cassandra). All of the courses are beautifully designed, contains large numbers of demos, presentations, guides, quiz and a pre-build Ubuntu VM where you can all exercises.

Though the presentations and program covers every topic and all major parameters and topics but still if you want to read in depth, they have their own document collection and can be accessed through their website https://docs.datastax.com/en/landing_page/doc/landing_page/current.html or from https://cassandra.apache.org/doc/latest/

Note: There are few other specialized courses available too within the catalog i.e. Kafka connectors, DSE Graph, DSE Analytics, DSE Search etc.

3. Other learning platforms
Github: https://github.com/datastax
Can be very useful specially if you are preparing for developer track.
Youtube: Full of some great presentations, videos and some precious workshops and demos.
https://www.youtube.com/user/DataStaxMedia
Twitter: For news (about webinars etc.), press releases and other exciting information.
https://twitter.com/DataStax (@DataStax)

4. All set!
Once you are done with your all three of your courses under ADMIN track, you are done and ready for the certification. Go to ‘Datastax Certification’ widget within catalog and book your exam by creating your profile on their certification website.
https://certification.mettl.com/datastax/applicant/signup

Currently they are giving one free exam vouchers and those will be issued at the end of the series for participants of the workshop.

5. Once registered you have to choose your exam type – Admin or Developer.
Both of the exams has 60 questions that you have to complete within 90 minutes, exam fees (right now) is $145
Note: It’s good that you check your system comparability before the exam, for more details follow their official guidelines.

So, don’t wait, go and enroll for the course and grab a chance for giving free certification and more importantly stand out from the crowd. These widely accepted and recognized credentials will help you in your continued professional development and is an ideal way to gain a greater understanding of your industry, and to enhance your knowledge and skills. It also offers excellent chances to network among Cassandra geeks.

Hope It Helps!
Prashant Dixit

Posted in Basics, Uncategorized | Tagged: | Leave a Comment »

root.sh failing while installing 12cR2 on RHEL7 “Failed to create keys in the OLR” – Did your hostname starts with a number ?

Posted by FatDBA on July 29, 2019

Hi Guys,

I know its been too long since i last posted and it all happened due to some site authentication issues and some personal priorities. Here I am back with new issues, all related with performance, administration, troubleshooting, optimization and other subjects.

This time would like to share one of the issue that i have faced while installing Oracle 12c Release 2 (Yes, I still do installations, sometimes 🙂 ) on a brand new RHEL7 box where everything was good till I ran root.sh which got failed due to a weird error which initially got no hint behind the problem.
Initially i though if this qualifies to be a post and deserves a place here but actually I have spend few days identifying the cause and hours that I have spend with support, so just want to save all that time for you all who might facing the same issue and looking something on Google 🙂

So lets get started!
This is what exactly I got when ran the root.sh script



[root@8811913-monkey-db1:/u011/app1/12.2.0.1/grid]# ./root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u011/app1/12.2.0.1/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u011/app1/12.2.0.1/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u011/app1/12.2.0.1/crsdata/8811913-monkey-db1/crsconfig/roothas_2019-02-18_00-59-22AM.log
Site name (8811913-monkey-db1) is invalid.clscfg -localadd -z  [-avlookup]
                 -p property1:value1,property2:value2...

  -avlookup       - Specify if the operation is during clusterware upgrade
  -z   - Specify the site GUID for this node
  -p propertylist - list of cluster properties and its value pairs

 Adds keys in OLR for the HASD.
WARNING: Using this tool may corrupt your cluster configuration. Do not
         use unless you positively know what you are doing.

 Failed to create keys in the OLR, rc = 100, Message:


2019/02/18 00:59:28 CLSRSC-188: Failed to create keys in Oracle Local Registry
Died at /u011/app1/12.2.0.1/grid/crs/install/oraolr.pm line 552.
The command '/u011/app1/12.2.0.1/grid/perl/bin/perl -I/u011/app1/12.2.0.1/grid/perl/lib -I/u011/app1/12.2.0.1/grid/crs/install /u011/app1/12.2.0.1/grid/crs/install/roothas.pl ' execution failed


The error simply said that the script failed to ‘create the keys in OLR’. These keys were for HASD that it was attempting to add. I verified all run time logs that got created the time but they too gave no idea about this problem. That is when I had to engage the Oracle customer support and came to know that this all happened due to a new BUG (BUG 26581118 – ALLOW HOSTNAME WITH NUMERIC VALUE) that comes in to picture when you have the hostname starts with a numeral or number and is an RHEL7 and is specific to Oracle 12c Release 2.

Oracle suggested a bug fix (Patch Number: 26751067) for this issue. This is a MERGE patch and fixes both Bug 25499276 & 26581118. One more thing, you have to apply this patch before the root.sh script.
So let me quickly show how to do that (removing all redundant and other sections).



[oracle@8811913-monkey-db1:/u011/app1/12.2.0.1/grid/OPatch]$ ./opatch napply -oh /u011/app1/12.2.0.1/grid -local 26751067/26751067/
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2019, Oracle Corporation.  All rights reserved.

...
......

Patch 26751067 successfully applied.
Log file location: /u011/app1/12.2.0.1/grid/cfgtoollogs/opatch/opatch2019-02-18_01-05-41AM_1.log

OPatch succeeded.
[oracle@8811913-monkey-db1:/u011/app1/12.2.0.1/grid/OPatch]$
[oracle@8811913-monkey-db1:/u011/app1/12.2.0.1/grid/OPatch]$


Ran the root.sh after patching and it went smooth.
BTW, in case you don’t want to do all this, simply change the hostname and put any alphabet in front of your hostname i.e. 8811913 –> A8811913 — That’s It!

Hope It Helps!

Thanks
Prashant Dixit

Posted in troubleshooting, Uncategorized | Tagged: | 1 Comment »

Oracle DB Security Assessment Tool (DBSAT)

Posted by FatDBA on March 2, 2018

Hi Everyone,

Would like to discuss about one of the request came from my earlier projects to identify sensitive data (Tables, objects etc.) within their databases so that external policies can be enforced later on, but the customer only permitted us to use any inbuilt or Oracle branded audit tool and not any third party security/compliance auditing tools.

And then we landed to use Oracle In-Built database security assessment tool name as DBSAT.
DBSAT has three components: Collector, Reporter, and Discoverer. Collector and Reporter work together to discover risk areas and produce reports on those risk areas and produces the final assessment report in HTML and CSV formats.
You can use DBSAT report findings to:

– Fix immediate short-term risks
– Implement a comprehensive security strategy
– Support your regulatory compliance program
– Promote security best practices

Lets see what it is and how to use it.

Step 1: Unzip the package.

[oracle@dixitlab software]$ unzip dbsat.zip
Archive: dbsat.zip
inflating: dbsat
inflating: dbsat.bat
inflating: sat_reporter.py
inflating: sat_analysis.py
inflating: sat_collector.sql
inflating: xlsxwriter/app.py
inflating: xlsxwriter/chart_area.py
inflating: xlsxwriter/chart_bar.py
inflating: xlsxwriter/chart_column.py
inflating: xlsxwriter/chart_doughnut.py
inflating: xlsxwriter/chart_line.py
inflating: xlsxwriter/chart_pie.py
inflating: xlsxwriter/chart.py
inflating: xlsxwriter/chart_radar.py
inflating: xlsxwriter/chart_scatter.py
inflating: xlsxwriter/chartsheet.py
inflating: xlsxwriter/chart_stock.py
inflating: xlsxwriter/comments.py
inflating: xlsxwriter/compat_collections.py
inflating: xlsxwriter/compatibility.py
inflating: xlsxwriter/contenttypes.py
inflating: xlsxwriter/core.py
inflating: xlsxwriter/custom.py
inflating: xlsxwriter/drawing.py
inflating: xlsxwriter/format.py
inflating: xlsxwriter/__init__.py
inflating: xlsxwriter/packager.py
inflating: xlsxwriter/relationships.py
inflating: xlsxwriter/shape.py
inflating: xlsxwriter/sharedstrings.py
inflating: xlsxwriter/styles.py
inflating: xlsxwriter/table.py
inflating: xlsxwriter/theme.py
inflating: xlsxwriter/utility.py
inflating: xlsxwriter/vml.py
inflating: xlsxwriter/workbook.py
inflating: xlsxwriter/worksheet.py
inflating: xlsxwriter/xmlwriter.py
inflating: xlsxwriter/LICENSE.txt
inflating: Discover/bin/discoverer.jar
inflating: Discover/lib/ojdbc6.jar
inflating: Discover/conf/sample_dbsat.config
inflating: Discover/conf/sensitive_en.ini

Step 2: Configure the ‘dbsat configuration’ file.
Next you have to configre the main config file (dbsat.config) available under Discover/conf directory.

[oracle@dixitlab conf]$ pwd
/home/oracle/software/Discover/conf

[oracle@dixitlab conf]$ ls -ltrh
total 20K
-rwxrwxrwx. 1 oracle oinstall 13K Jan 16 22:58 sensitive_en.ini
-rwxrwxrwx. 1 oracle oinstall 2.4K Mar 1 22:12 dbsat.config

Few of the important parameters are given below.
vi dbsat.config

DB_HOSTNAME = localhost
DB_PORT = 1539
DB_SERVICE_NAME =tunedb
SENSITIVE_PATTERN_FILES = sensitive_en.ini >>>>> This param users sensitive_en.ini file for the English language patterns, which contains 75 patterns
ex: CREDIT_CARD_NUMBER, CARD_SECURITY_PIN, MEDICAL_INFORMATION, SOCIAL_SECURITY_NUMBER etc.

 

Step 3: Run the discoverer against the database to collect the information.

[oracle@dixitlab software]$ $(dirname $(dirname $(readlink -f $(which javac))))    --- To check the JAVAHOME.
-bash: /usr/java/jdk1.8.0_131: is a directory
[oracle@dixitlab software]$ export JAVA_HOME=/usr/java/jdk1.8.0_131

[oracle@dixitlab conf]$ cd ../..
[oracle@dixitlab software]$ ./dbsat discover -c Discover/conf/sample_dbsat.config tunedb_data

Database Security Assessment Tool version 2.0.1 (December 2017)

This tool is intended to assist in you in securing your Oracle database
system. You are solely responsible for your system and the effect and
results of the execution of this tool (including, without limitation,
any damage or data loss). Further, the output generated by this tool may
include potentially sensitive system configuration data and information
that could be used by a skilled attacker to penetrate your system. You
are solely responsible for ensuring that the output of this tool,
including any generated reports, is handled in accordance with your
company's policies.

Enter username: system
Enter password:
Connection Successful- Retrying regarding "tunedb" as SID
DBSAT Discover ran successfully.
Calling /usr/bin/zip to encrypt the generated reports...

Enter password:
Verify password:
zip warning: tunedb_data_report.zip not found or empty
adding: tunedb_data_discover.html (deflated 88%)
adding: tunedb_data_discover.csv (deflated 84%)
Zip completed successfully.

We have the audit reports created under the tool directory.
Sample report attached with this report.

https://1drv.ms/f/s!Arob5fjpN041ga58isTgjF-wBPLI0A
tunedb_data – Oracle Database Security Risk Assessment

Hope It Helps
Prashant Dixit

Posted in Uncategorized | Tagged: , | Leave a Comment »

Active Data Guard (ADG) is included in Golden Gate License on EE edition.

Posted by FatDBA on August 22, 2016

The license for Oracle GoldenGate includes a full use license for Oracle Active Data Guard, and a full use license for XStream in the Oracle Database.

Active Data Guard is a superset of Data Guard capabilities included with Oracle Enterprise Edition and can be purchased as the Active Data Guard Option for Oracle Database Enterprise Edition. It is included with every Oracle GoldenGate license, offering customers the ability to acquire the complete set of advanced Oracle replication capabilities with a single purchase.

Posted in Uncategorized | 1 Comment »

 
%d bloggers like this: