Linux YUM Best Kept Secret – whatprovides

Most of the time, when we are installing a software like Oracle or DB2 on a Linux system, there will be required RPMs that has not been installed yet. If the RPM required is unfamiliar, we will be forced to research and find out what is the RPM about or where to download it.

From Linux Man Pages (whatprovides argument):

Is used to find out which package provides some feature or file. Just use a specific name or a file-glob-syntax wildcards to list the packages available or installed that provide that feature or file.

Usage Example:

When I tried to install DB2, it complains the following error:

DBT3514W  The db2prereqcheck utility failed to find the following 32-bit library file: “libstdc++.so.6”.

So let’s try the argument out!

[root@centos68 server_t]# yum whatprovides libstdc++.so.6
libstdc++-4.4.7-17.el6.i686 : GNU Standard C++ Library
Repo : base
Matched from:
Other : libstdc++.so.6

[root@centos68 server_t]# yum install -y libstdc++-4.4.7-17.el6.i686

[root@centos68 server_t]# yum list libstdc++.i686 0:4.4.7-17.el6
Installed Packages
libstdc++.i686 4.4.7-17.el6 @base

Regards,
Wei Shan

Investigating Oracle Database Memory Pressure Behavior

Oracle database will grab all and release as the O/S sees memory pressure.

I came across the above statement recently during a discussion., which led to this blog post. I wasn’t very sure if it was true so I did not reply to it. I’m aware of the behavior when it face CPU pressure but not memory pressure. So, this has definitely piqued my curiosity to find out more.

---

In Oracle 11gR2 and later, we can choose to use have the following options:

• Automatic Memory Management (AMM)
  • We configure MEMORY_TARGET and MEMORY_MAX _TARGET and let Oracle database manage the SGA and PGA for us
• Automatic Shared Memory Management (ASMM)
  • We configure SGA_TARGET and SGA_MAX_SIZE and let Oracle manage SGA for us.
• Automatic PGA Memory (Usually used together with ASMM)
  • We configure PGA_AGGREGATE_TARGET and let Oracle manage PGA for us.

So, we can be sure that the parameters below affect Oracle database memory usage on a system. (Of course, we are not referring to manual memory tuning.)

1. MEMORY_MAX _TARGET
2. MEMORY_TARGET
3. SGA_TARGET
4. SGA_MAX_SIZE
5. PGA_AGGREGATE_TARGET

It is widely understood that the above parameters are merely a a target. It may exceed the optimum size due to other circumstances. For example, the number of users increases beyond limit or insane SQL queries that exceed PGA value. PGA size is the unknown factor here, since we do not know how many users or what kind of queries will be running in the database.

The question here is, when it uses more memory than the target value, does it start to swap or does it grab the free memory from the non-allocated memory from the O/S. I would think that it will use free memory then swap space since it will ask the memory from O/S until it has memory pressure which then, it will start to swap.

Let’s test it out!

Let’s login and check all the baseline numbers.

[oracle@primary ~]$ sqlplus / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter pga_aggregate_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 200M

#free -m

total used free shared buffers cached
Mem: 2002 721 1281 183 24 451
-/+ buffers/cache: 245 1757
Swap: 4031 0 4031


select a.name, to_char(b.value, '999,999,999') bytes,
to_char(round(b.value/1024/1024,1), '99,999.9' ) mbytes
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name like '%ga memory%';

NAME BYTES MBYTES
-------- ------------ ---------
session uga memory 2,515,088 2.4
session uga memory max 2,515,088 2.4
session pga memory 3,555,880 3.4
session pga memory max 22,889,000 21.8

The PGA_AGGREGATE_TARGET is configure to use 200MB. Currently it is only using 2.4MB. We have 4GB of swap space configured as well.

Let’s create a package and generate the workload

SQL> create or replace package demo_pkg
 as
 type array is table of char(2000) index by binary_integer;
 g_data array;
 end;
 /

SQL> begin
 for i in 1 .. 2000000
 loop
 demo_pkg.g_data(i) := 'x';
 end loop;
 end;
 /
 ERROR at line 1:
 ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu sessi,pl/sql vc2)

The above SQL script definitely uses more than 2GB of memory for sorting, which lead to ORA-04030 error.

From another separate SSH session, let’s monitor the memory usage via vmstat

[root@primary ~]# vmstat 1 6000000
 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r b swpd free buff cache si so bi bo in cs us sy id wa st
 0 0 0 1311424 24952 462624 0 0 1611 58 303 486 6 10 83 1 0
 0 0 0 1311392 24952 462628 0 0 0 16 151 282 0 1 99 0 0
 0 0 0 1311384 24952 462628 0 0 0 0 118 228 0 0 100 0 0
 0 0 0 1311384 24952 462628 0 0 0 32 151 287 0 0 100 0 0
 0 0 0 1311384 24952 462628 0 0 0 0 112 220 0 0 100 0 0
 0 0 0 1311384 24952 462628 0 0 0 0 141 263 0 1 99 0 0
 0 0 0 1311384 24960 462628 0 0 0 52 135 267 0 0 99 1 0
 0 0 0 1311384 24960 462628 0 0 0 0 141 271 0 0 100 0 0
 0 0 0 1311384 24960 462628 0 0 0 0 109 215 0 0 100 0 0
 0 0 0 1311384 24960 462628 0 0 0 32 150 283 0 0 100 0 0
 0 0 0 1311384 24960 462628 0 0 0 0 123 232 0 0 100 0 0
 0 0 0 1311384 24968 462624 0 0 0 20 144 276 0 1 99 0 0
 0 0 0 1311392 24968 462628 0 0 0 32 121 250 0 0 100 0 0
 0 0 0 1311136 24968 462628 0 0 0 0 167 329 1 1 98 0 0
 0 0 0 1311136 24968 462628 0 0 0 0 109 211 0 0 100 0 0
 1 0 0 226260 24968 463068 0 0 280 32 998 310 22 67 10 0 0
 2 4 86096 13744 60 105252 0 93124 12636 93144 2101 1698 13 86 0 1 0
 3 2 314548 14296 60 82612 1596 226304 29708 226304 2234 1695 9 91 0 0 0
 3 4 552488 13732 60 83128 9568 244640 35048 244664 2549 2140 8 90 0 2 0
 2 0 806904 13888 60 82228 1920 267312 34484 267344 2438 1904 9 89 0 2 0

The highlighted values above are the period where the SQL query executed. Noticed that the free memory was at 1.3GB until it drop to 200MB before it actually start to swap.

Summary

From the above, I have highlighted the important points:

1. It is not possible to “overuse” SGA as it uses LRU algorithm to remove unnecessary objects under memory pressure.
2. Although we configure PGA to only use a maximum of 200MB, it is possible to exceed this value due to user queries or sessions.
3. Under memory pressure, the database will try to use available memory until it doesn’t have a choice, which it will choose to use swap space.
4. When space space run out, the database will hit ORA-04030 error.

Regards,
Wei Shan

References: Expert Oracle Database Architecture -Thomas Kyte

 

 

 

 

Story of recovering a PostgreSQL 9.2 database

Background Story

It was a 2 node PostgreSQL 9.2 streaming replication setup. The master node crashed without possibility of recovery. Thus, there was a need to restore the standby database to resume the production services. The standby database also reboot when master node crashed. The standby node was performing database backup via pg_start_backup when it rebooted.

I will simulate the recovery below.

---

postgresql.conf

wal_level = hot_standby
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/9.2/data/pg_archive/%f'

recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=10.1.0.3 port=5432 user=postgres application_name=testdb keepalives_idle=60 keepalives_interval=5 keepalives_count=5'
restore_command = 'cp /var/lib/pgsql/9.2/data/pg_archive/%f %p'
recovery_target_timeline = 'latest'

Start PostgreSQL backup on the database

postgres=# select pg_start_backup('test1');
 pg_start_backup
-----------------
 0/40000B8
(1 row)

Abort PostgreSQL database and start it again. (Notice we are not able to login the psql)

-bash-4.1$ /usr/pgsql-9.2/bin/pg_ctl -D /var/lib/pgsql/9.2/data -m immediate stop
waiting for server to shut down... done
server stopped
-bash-4.1$ /usr/pgsql-9.2/bin/pg_ctl -D /var/lib/pgsql/9.2/data -l logfile start
server starting
-bash-4.1$ psql
psql: FATAL: the database system is starting up

Notice that the startup process is waiting for a WAL file.

-bash-4.1$ ps -aef | grep postgres
root 2405 2283 0 18:19 pts/1 00:00:00 su - postgres
postgres 2407 2405 0 18:19 pts/1 00:00:00 -bash
postgres 2921 1 0 19:21 pts/1 00:00:00 /usr/pgsql-9.2/bin/postgres -D /var/lib/pgsql/9.2/data
postgres 2923 2921 0 19:21 ? 00:00:00 postgres: logger process
postgres 2924 2921 0 19:21 ? 00:00:00 postgres: startup process waiting for 000000010000000000000004
postgres 2925 2921 0 19:21 ? 00:00:00 postgres: checkpointer process
postgres 2926 2921 0 19:21 ? 00:00:00 postgres: writer process

Error logs in /var/log/messages

FATAL: the database system is starting up
FATAL: the database system is starting up
FATAL: could not connect to the primary server: could not connect to server: Connection timed out
 Is the server running on host "10.1.0.3" and accepting
 TCP/IP connections on port 5432?

cp: cannot stat `/var/lib/pgsql/9.2/data/pg_archive/000000010000000000000004': No such file or directory
cp: cannot stat `/var/lib/pgsql/9.2/data/pg_archive/000000010000000000000004': No such file or directory
cp: cannot stat `/var/lib/pgsql/9.2/data/pg_archive/00000002.history': No such file or directory
LOG: received smart shutdown request
FATAL: terminating walreceiver process due to administrator command
LOG: shutting down
LOG: database system is shut down
LOG: database system was shut down in recovery at 2016-08-03 19:17:49 SGT
cp: cannot stat `/var/lib/pgsql/9.2/data/pg_archive/00000002.history': No such file or directory
LOG: entering standby mode
LOG: restored log file "000000010000000000000004" from archive
LOG: redo starts at 0/4000020
LOG: record with zero length at 0/40000B8
LOG: record with zero length at 0/40000B8
cp: cannot stat `/var/lib/pgsql/9.2/data/pg_archive/00000002.history': No such file or directory
FATAL: the database system is starting up

The WAL file that it was waiting for existed in pg_xlog but not in pg_archive.

-bash-4.1$ ls /var/lib/pgsql/9.2/data/pg_archive
000000010000000000000002 000000010000000000000003

-bash-4.1$ ls /var/lib/pgsql/9.2/data/pg_xlog
000000010000000000000002 000000010000000000000003 000000010000000000000004

Solution: Remove recovery.conf and restart PostgreSQL. Noticed the we didn’t have to use pg_stop_backup to remove the backup label.

-bash-4.1$ mv recovery.conf recovery.done
-bash-4.1$ /usr/pgsql-9.2/bin/pg_ctl -D /var/lib/pgsql/9.2/data -l logfile start
server starting
-bash-4.1$ psql
psql (9.2.17)
Type "help" for help.

postgres=# select pg_stop_backup();
ERROR: a backup is not in progress

Verification (Formatted for readability)

-bash-4.1$ /usr/pgsql-9.2/bin/pg_controldata
Database cluster state: in production
pg_control last modified: Wed 03 Aug 2016 07:22:27 PM SGT
Latest checkpoint location: 0/40000B8
Prior checkpoint location: 0/4000058
Latest checkpoint's REDO location: 0/40000B8
Minimum recovery ending location: 0/0
Backup start location: 0/4000020
Backup end location: 0/0
End-of-backup record required: no
Current wal_level setting: hot_standby

Summary

There is a few learning points from the story.

• When a database crashed while it is running pg_start_backup() mode, after it reboots, it is not required to run pg_stop_backup().
• The reason why it was waiting for the WAL file that was in pg_xlog is due to recovery.conf. The restore command in recovery.conf was pointing to pg_archive to restore the WAL file.
• It was a crash recovery, we didn’t have to use recovery.conf to restore the database. recovery.conf is only used when we are restoring the database from backup

Regards,
Wei Shan

Solaris Sun Cluster – Changing Fault Monitor Password (fmuser) for MySQL

There was a requirement to change the fmuser password after the Sun Cluster for MySQL was implemented. The customer wanted to change the default password to something else. The fmuser is the default user for the Sun Cluster Fault Monitor to interact with the MySQL database.

Step 1: Check the cluster status.

# /usr/cluster/bin/clresource status
=== Cluster Resources ===

Resource Name Node Name State Status Message
------------- --------- ----- --------------
mysql-rg       node1           Online Online 
               node2           Offline Offline

mysql-lh-res node1 Offline Offline - LogicalHostname offline.
             node2 Online Online - LogicalHostname online.

mysql-stor-res node1 Offline Offline
               node2 Online Online

Step 2: Stop fault monitoring on mysql-rg resource (Note: The resource, not the resource group!)

# /usr/cluster/bin/clresource unmonitor mysql-rg
# /usr/cluster/bin/clresource status

=== Cluster Resources ===

Resource Name Node Name State Status Message
------------- --------- ----- --------------
mysql-rg node1 Online_not_monitored Online_not_monitored
 node2 Offline Offline

mysql-lh-res node1 Online Online - LogicalHostname online.
 node2 Offline Offline

mysql-stor-res node1 Online Online
 node2 Offline Offline

Step 3: Change the fmuser password in the MySQL database

/appl/bin/mysql -uroot -ppassword -S '/tmp/vnode.sock'

mysql> set password for 'fmuser'@'node1' = PASSWORD('newpassword');
Query OK, 0 rows affected (0.00 sec)

mysql> set password for 'fmuser'@'node2' = PASSWORD('newpassword');
Query OK, 0 rows affected (0.00 sec)

Step 4: Disable the mysql-rg resource and change the properties of it. We will need to change 4 properties of it. (Validate, Stop, Probe and Start)

# /usr/cluster/bin/clresource disable mysql-rg

./scrgadm -c -j mysql-rg -x Validate_command="/opt/SUNWscmys/bin/control_mysql -R mysql-rg -G mysql-rg -B /appl -D /data -U mysql -H vnode -F fmuser%newpassword -L /logs -C validate"
./scrgadm -c -j mysql-rg -x Stop_command="/opt/SUNWscmys/bin/stop_mysql -R mysql-rg -G mysql-rg -B /appl -D /data -U mysql -H vnode -F fmuser%newpassword -L /logs -C"
./scrgadm -c -j mysql-rg -x Probe_command="/opt/SUNWscmys/bin/probe_mysql -R mysql-rg -G mysql-rg -B /appl -D /data -U mysql -H vnode -F fmuser%newpassword -L /logs -C"
./scrgadm -c -j mysql-rg -x Start_command="/opt/SUNWscmys/bin/start_mysql -R mysql-rg -G mysql-rg -B /appl -D /data -U mysql -H vnode -F fmuser%newpassword -L /logs -C"

Step 5: Enable the mysql-rg resource and start back fault monitoring on the mysql resource.

#/usr/cluster/bin/clresource enable mysql-rg
#/usr/cluster/bin/clresource monitor mysql-rg

Please note that the above steps will result downtime. That’s all!

Regards,
Wei Shan

How to generate authentication file for PgBouncer

PgBouncer is a lightweight connection pooler that prevents the MAX_CONNECTION reached error in PostgreSQL. Basically, it does a very good job for what it says. It maintains a pool of connections for your applications. This helps in reducing the overhead of setting up and tearing down connections for every client incoming request. [link]

By default, it uses a plain text password file for authenticating users to the PostgreSQL database. This will obviously raise a red flag during security audit! Let’s see what we can do to prevent that red flag🙂

First, we have to install pg_bouncer and psycopg2.

yum install pgbouncer.x86_64 python-psycopg2.x86_64

Now that we got PgBouncer installed. We have to do configuration.

# cd /etc
# chown -R postgres. pgbouncer/
# su - postgres
$ cd /etc/pgbouncer/
$ ls
mkauth.py mkauth.pyc mkauth.pyo pgbouncer.ini

Now, let’s generate that password file🙂

$ ./mkauth.py "/etc/pgbouncer/password.txt" "dbname='postgres' user='postgres' host='localhost'"
$ ls
mkauth.py mkauth.pyc mkauth.pyo password.txt pgbouncer.ini

Let’s see if we can view the password!

$ cat password.txt
"postgres" "" ""
"repmgr" "md58ea99ab1ec3bd8d8a6162df6c8e1ddcd" ""
"testuser" "md5d70a0452418aeb8fb4030eae69ca2856" ""

It uses the pg_shadow table and generate the password file. Obviously, it is only salted and double hashed with MD5. It is not as secure as SHA-256, but still a lot more secure that reading the password in plain text isn’t it?

Regards,
Wei Shan

Redis 3.2 + Sentinel with Protected Mode

I had a little trouble while testing Redis 3.2 with Sentinel earlier today.

Below is a sample configuration from sentinel.conf.sample

#port 26379
#sentinel announce-ip <ip>
#sentinel announce-port <port>
#dir /tmp
#sentinel monitor mymaster 127.0.0.1 6379 2
#sentinel auth-pass mymaster MySUPER--secret-0123passw0rd
#sentinel down-after-milliseconds mymaster 30000
#sentinel parallel-syncs mymaster 1
#sentinel failover-timeout mymaster 180000
#sentinel notification-script mymaster /var/redis/notify.sh
#sentinel client-reconfig-script mymaster /var/redis/reconfig.sh

Below is the actual configuration.

port 5001
sentinel monitor mymaster 192.168.56.192 6379 2
sentinel down-after-milliseconds mymaster 1000
sentinel failover-timeout mymaster 10000

It kept on showing that the sentinel on my slave is down:

+sdown sentinel 3d41cb53869c640d95a9a0bcf7e3d4000ee1e272 192.168.56.193 5003 @ mymaster 192.168.56.192 6379

When I try to telnet in, I noticed the following error:

root@redis1:~ # telnet 192.168.56.193 5003
Trying 192.168.56.193...
Connected to 192.168.56.193.
Escape character is '^]'.

-DENIED Redis is running in protected mode because protected mode is enabled, no bind address was specified, no authentication password is requested to clients. 
In this mode connections are only accepted from the loopback interface. If you want to connect from external computers to Redis you may adopt one of the following solutions: 
1) Just disable protected mode sending the command 'CONFIG SET protected-mode no' from the loopback interface by connecting to Redis from the same host the server is running, however MAKE SURE Redis is not publicly accessible from internet if you do so. 
Use CONFIG REWRITE to make this change permanent. 
2) Alternatively you can just disable the protected mode by editing the Redis configuration file, and setting the protected mode option to 'no', and then restarting the server. 
3) If you started the server manually just for testing, restart it with the '--protected-mode no' option. 
4) Setup a bind address or an authentication password. NOTE: You only need to do one of the above things in order for the server to start accepting connections from the outside.
Connection closed by foreign host.

In Redis 3.2, it adds a “protected-mode” parameter to prevent stupid DBA from exposing Redis by not binding Redis instance to a specific interface. It was documented in redis.conf.sample file. However, this was not documented for sentinel.conf at all. Or at least, I couldn’t find it anywhere.

After disabling protected-mode in the sentinel.conf, it works fine🙂

Regards,
Wei Shan

 

MongoDB 3.2 – Authentication failed while connecting to a mongod/mongo instance

I needed to connect to a mongod process that started with –auth parameter, AKA Client Access Control.

root@testbm:~/data# mongo localhost:27024 admin -u user -p password
MongoDB shell version: 3.2.6
connecting to: localhost:27024/test
2016-06-02T15:07:55.521+0100 E QUERY [thread1] Error: Authentication failed. :
DB.prototype._authOrThrow@src/mongo/shell/db.js:1441:20
@(auth):6:1
@(auth):1:2

exception: login failed

Okay…Let’s try using db.auth method to authenticate!

root@testbm:~/data# mongo localhost:27024
MongoDB shell version: 3.2.6
connecting to: localhost:27024/test
> use admin
switched to db admin
> db.auth('user','password');
1
configsvr> exit

That’s weird, what is going on? A search in the MongoDB 3.2 manual showed me the right path….

root@testbm:~/data# mongo localhost:27024 -u 'user' -p 'password' --authenticationDatabase admin
MongoDB shell version: 3.2.6
connecting to: localhost:27024/test
Server has startup warnings:
2016-06-02T14:03:16.390+0100 I CONTROL [initandlisten] ** WARNING: You are running this process as the root user, which is not recommended.
2016-06-02T14:03:16.390+0100 I CONTROL [initandlisten]
2016-06-02T14:03:16.390+0100 I CONTROL [initandlisten]
2016-06-02T14:03:16.390+0100 I CONTROL [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/enabled is 'always'.
2016-06-02T14:03:16.390+0100 I CONTROL [initandlisten] ** We suggest setting it to 'never'
2016-06-02T14:03:16.390+0100 I CONTROL [initandlisten]
2016-06-02T14:03:16.390+0100 I CONTROL [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
2016-06-02T14:03:16.391+0100 I CONTROL [initandlisten] ** We suggest setting it to 'never'
2016-06-02T14:03:16.391+0100 I CONTROL [initandlisten]

Apparently, when we have CAC enabled, we need to pass 2 additional parameters:

1. authenticationDatabase
2. The authentication database, which is admin

 

Reference:

• https://docs.mongodb.com/manual/core/security-users/

 

Regards,
Wei Shan

MongoDB 101 Tips – How to view current configuration on a running system

Imagine you are a DBA managing a MongoDB environment. And you want to view the current configuration of a running system. Equivalent to the following;

• PostgreSQL => show all;
• Oracle => show parameters;

In MongoDB, it is not as straightforward. You have to know what you want to look for exactly. The entire database configuration is not in a single place.

• To get the configuration file that the current MongoDB instance is using, use “db.serverCmdLineOpts();”
• To get the current server parameters, use “db.runCommand( { getParameter : ‘*’ } )” against the admin database in any replica set
• To get the sharding configuration, run “sh.status()” against a mongos server.
• To get about the replica sets information, use “rs.status()” against the primary replica set

However, it is not possible to have a similar “show all” command in MongoDB to list what is the exact configurations that the MongoDB instance is running at that point of time. Thus, it would be ideal when you start mongod process with –config parameter rather than passing it as an argument for operation reasons.

Lastly, it is almost necessary to restart the mongod process after any configuration changes. The only run-time configuration that you can change is below, which is pretty darn short.

 -failIndexKeyTooLong
 -notablescan
 -logComponentVerbosity
 -logLevel
 -logUserIds
 -quiet
 -traceExceptions
 -replApplyBatchSize
 -replIndexPrefetch
 -auditAuthorizationSuccess
 -clusterAuthMode
 -scramIterationCount
 -sslMode
 -userCacheInvalidationIntervalSecs
 -journalCommitInterval
 -syncdelay
 -wiredTigerEngineRuntimeConfigSetting
 -disableJavaScriptJIT

Reference:

• https://docs.mongodb.com/manual/
• https://docs.mongodb.com/manual/reference/command/setParameter/

 

Regards,
Wei Shan