linux

Testing Database Connectivity

Posted on by

Working with databases and new application installations can be really fun.  Problem is, when there is a problem, everyone starts the blame game.  Nothing unusual about that, part of an administrators job is to troubleshoot and prove where the problem starts.  When dealing with external databases, there can be numerous problem, the firewall could be blocking, the local or remote port could be blocked on the system, or the database credentials could be incorrect.  Testing for the last helps troubleshoot all of these.  Ruling out the database connection helps focus the application administrator on the real problem!  Testing a remote oracle database is pretty simple if you have the oracle client configured with tnsnames, etc.  But if that isn’t necessary you may not have it configured.  When you don’t this is the easiest way to test the database connection via the command line:

sqlplus 'user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))'

Prior to this make sure your ORACLE_HOME environment variable is set correctly.  You may also need the LD_LIBRARY_PATH set to $ORACLE_HOME/lib.

UPDATE: 21may2015:

Now that you are in you may want to check a few things out.  To give you a quick reminder of the syntax here are a few to get the lay of the land:

To list all tables in a database (accessible to the current user):

SQL> select table_name from user_tables;

 

To list the contents of a specific table, type:

SQL> select * from name_of_table;

You can find more info about views all_tables, user_tables, and dba_tables in Oracle Documentation.

 

Pain often equals Progress

Posted on by

It has been one of those weeks.  Not fun, to many hours worked, personal events missed, you know the kind of week I am talking about.  If not…what do you do for a living?!

Despite all the pain and stress this week has resulted in Progress, an increased understanding of certain products and new ways to use old tools.  I won’t share the details of my story, just insert yours here, but I will share/document the lessons and commands I learned or rediscovered.  Here we go…

Starting a long running process from home last night around 9PM and forgetting to start screen…priceless!  At 5:30AM this morning the process was still chugging along, with from my calculations would be running for another 18+ hours.  Off to work with no way to grab the terminal (an ssh session), what to do?  Why use strace of course!  Here is how:

strace -pPROCESS_PID -s9999 -e write

ie: strace -p3918 -s9999 -e write

Now even if my ssh session dies at home, I can still see the process output and know when it finishes and if it had any problems.  Yes, I could have piped output to a file, you never forgot anything after working for 15+ hours?

Dealing with a system that had some package inconsistencies and a yum update that failed, followed by a package-utils –cleandupes that erased many complete packages, I thought about using the ‘yum history’ command to revert the system until I read this: “Use the history option for small update rollbacks.”  Here are some of the commands I used which due to the systems package inconsistencies did not perform as expected.

# yum check
# package-cleanup --cleandupes
# yum-complete-transaction
# yum check
# package-cleanup --problems
# rpm -Va --nofiles --nodigest
# yum distribution-synchronizatio

The rest is pretty standard stuff, at least not worth noting in this post.  The end result this week is a lot of lessons learned and a much deeper understanding for an application that I support on my server.  In all, ignoring the backlog, I’d say that is what progress looks like.

 

 

The Root of Missing Mail

Posted on by

Like all conscientious system administrator I like to keep tabs on my servers.  One way of doing this is checking root’s email daily.  This is a great idea if you have a few servers and never take vacation!  I manage close to 100 servers, so I need a more efficient way of “hearing” my servers when they complain to root about something.  Aside from monitoring solution (not covered here) the best way to do this is to redirect where email for the root user gets sent.

This seems pretty simple so I never thought of posting about this, until today.  Some facts , to forward mail for the root user leverage the /etc/aliases file.  Like always I added a line to /etc/aliases like this:

# vi /etc/aliases

     root:    myemailaddress@uconn.edu

Ideally you want to set the email address to a list serve so that your backup administrator receives these messages also, so you can take a vacation.

I made that change yesterday on a new server and didn’t give it a second thought.  Today no mail, and I know there was an error on the system?!

First thing I checked was if I could send mail from the server, I could have…or I just forgot because I am sleep deprived…  I was able to send mail from the command line to an email address but not to an alias.  OK, that is a big clue.

While I have never had to do this before, (perhaps I restarted all my other systems?), regardless to fix the problem I simply ran this command:

# newaliases

Bingo, mail started flowing!

If that doesn’t fix it for you, other things to check are:

– Include the following in your /etc/hosts.allow:

ALL: 127.0.0.1 : allow

 

Remember when you issued that command…?

Posted on by

Bash History: Display Date And Time For Each Command

When working in a clustered environment where sometimes documentation gets written past, it is often helpful to know when you issued certain commands. The bash history is great except it doesn’t include a date/time stamp by default. Here is how to add one:

To display the time and date of with previously executed commands in your history, you need to set the “HISTTIMEFORMAT” variable. The variable has to be set in the users profile file so to take effect on each session. You define the environment variable in your bash profile as follows:

$ echo 'export HISTTIMEFORMAT="%d/%m/%y %T "' >> ~/.bash_profile

Where,
%d – Day
%m – Month
%y – Year
%T – Time
To see history type

$ history

Sample outputs:

....
  932  10/12/13 10:48:16 lsof -i
  933  10/12/13 10:49:55 tcpdump -i eth0 src host 137.99.xx.xx
  934  10/12/13 10:50:53 tcpdump -i eth0 src host 137.99.xx.xx port 8080
  935  10/12/13 10:51:10 tcpdump -i eth0 src host 137.99.xx.xx 
  936  10/12/13 10:52:42 ss -ln
....

References:

For more info type the following commands:

man bash
help history
man 3 strftime

That is it…

Running the Citrix Reciever on Linux

Posted on by

Setting up a new linux workstation and after installing the Citrix Receiver and attempting to start a module (Outlook) I got the dread SSL error:

citrix-receiver-ssl-errorHaving run into this every 6 months or so I decided it was time to jot down the fix.  The problem is the install does not install the Citrix SSL certificate into the browsers trusted certificate cache. So here is the solution for Firefox…

To prevent the SSL error 61 when accessing remote sessions:

Make Firefox’s certificates accessible to Citrix,

sudo ln -s /usr/share/ca-certificates/mozilla/* /opt/Citrix/ICAClient/keystore/cacerts

That’s it, you should be good to go!

Flush This!

Posted on by

I came across this today and learned something new so thought I would share it here.

After killing 2 processes that had hung I noticed the following in the ps output:

root       373     2  0 Jun11 ?        00:00:00 [kdmflush]
root       375     2  0 Jun11 ?        00:00:00 [kdmflush]
root       863     2  0 Jun11 ?        00:00:00 [kdmflush]
root       867     2  0 Jun11 ?        00:00:00 [kdmflush]
root      1132     2  0 Jun11 ?        00:01:03 [flush-253:0]
root      1133     2  0 Jun11 ?        00:00:43 [flush-253:2]

Now kdmflush I am use to seeing, but flush-253: was something I had never noticed so I decided to dig.  I started with man flush but that seemed to lead no where since I am not running sendmail or any mail server.  I turned to google (not to proud to admit it) and searched “linux process flush”.  Turns out ‘flush-# is kernel garbage collection that flushes unused memory allocations to disk so the RAM can be reused.  So ‘flush’ is trying to write out dirty pages from virtual memory, most likely associated with the processes I just killed.

I discovered these commands that shed more light on what is actually happening:

grep 253 /proc/self/mountinfo 
20 1 253:0 / / rw,relatime - ext4 /dev/mapper/vg_kfs10-lv_root rw,seclabel,barrier=1,data=ordered
25 20 253:3 / /home rw,relatime - ext4 /dev/mapper/vg_kfs10-lv_home rw,seclabel,barrier=1,data=ordered
26 20 253:2 / /var rw,relatime - ext4 /dev/mapper/vg_kfs10-LogVol03 rw,seclabel,barrier=1,data=ordered

Remember my listings were for flush-253:0 and flush-253:2 so I now know what partitions are being worked with.  Another interesting command to use is the following, which shows the activity of writing out dirty pages:

watch grep -A 1 dirty /proc/vmstat
nr_dirty 2
nr_writeback 0

If these numbers are significantly higher you might be having a bigger problem on your system.  Though from what I have read this sometimes indicates sync’ing.  If this becomes a problem on your server you can set system parameters in /etc/sysctl.conf to head this off by adding the following lines:

vm.dirty_background_ratio = 50
vm.dirty_ratio = 80

Then (as root) execute:

# sysctl -p

The “vm.dirty_background_ratio” tells at what ratio should the linux kernel start the background task of writing out dirty pages. The above increases this setting from the default 10% to 50%.  The “vm.dirty_ratio” tells at what ratio all IO writes become synchronous, meaning that we cannot do IO calls without waiting for the underlying device to complete them (which is something you never want to happen).

I did not add these to the sysctl.conf file but thought it worth documenting.

After Clone network configuration

Posted on by

The best part about Virtual Environments is the ability to clone new hosts from old ones.  Most of our infrastructure resides in vmware, so when we clone a system it retains the old Nic settings.  This is how you get the network interface working after cloning the system:

vi /etc/udev/rules.d/70-persistent-net-rules
       remove eth0
       rename eth1 to eth0
       save

vi /etc/sysconfig/networking/devices/ifcfg-eth0
       change the MAC address, IP, netmask
       make sure ONBOOT=yes
       If UUID is present, delete it.

vi /etc/sysconfig/networking/profiles/default/hosts
       change the host file definitions

vi /etc/sysconfig/network
       change the HOSTNAME

That is it for configuring the system.  Sometimes you will need to run the following commands (I run them as part of the process since they do no harm).

modprobe -r vmxnet3
modprobe vmxnet3

That’s it.  I usually reboot the system to clean up anything cached, however you should be able to ifdown/ifup eth0.

Don’t forget to clean up your other configuration files that will have the old system information.  Backup definitions, monitoring, etc.

 

Crontab Sudo Shenanigans

Posted on by

OK, here is a situation I haven’t seen in a while and it tripped me.  There I admitted it!

We have an application that requires a restart of Apache everyday (that is a different discussion).  Regardless I gave them sudo access so they could script the job to run with their process.  Obviously I thought nothing more of it, problem solved, more pressing things to do.  It worked like a charm until they put their script into cron.  They received the error:

sudo: sorry, you must have a tty to run sudo

I didn’t want to throw the baby out with the bathwater and enable tty for all of cron-dom, and I like command-line solutions over config files (less to maintain/remember).  So I tried this variation:

su --session-command="/usr/bin/sudo /sbin/service httpd restart" user_name

Slick huh?  Well of course it didn’t work because sudo is in control, pesky security controls keep me on the straight and narrow. This led me to one option, enable tty for the user (not everyone).  The solution for that is:

Defaults    requiretty
Defaults:%group_name !requiretty
Defaults:user_name !requiretty

In case that isn’t clear enough.  The first line requires TTY for all users and groups not expressly excluded from that requirement.  The second line exempts the group from the requirement and the the third line specifically exempts the user from the requirement.  The inclusion of the User_name and Group_name is redundant however this saves me revisiting the configuration file if we expand the group.

This ends the brain dump…

My MySQL Cheat Sheet

Posted on by

I know, man.  No, I mean I know I could use ‘man pages’!  Or I could just ‘google it’ but then it isn’t mine.  Since I do not have time for a complete brain-dump this MySql “cheat sheet” will grow over time.  Feel free to add your favorite MySql commands in the comments, if their really useful I’ll add them to the list!

If you don’t know what MySql is…look it up!  And, who are you?!  Seriously…

Create a DB & Assign to a User:

Create a New DB, Create a User and Grant them permissions to the New DB.

mysql> create database someDB_name;
Query OK, 1 row affected (0.13 sec)

mysql> create user 'someUser_name'@'localhost' IDENTIFIED BY 'some_password';
Query OK, 0 rows affected (0.13 sec)

mysql> GRANT ALL PRIVILEGES ON someDB_name.* to someUser_name@localhost;
Query OK, 0 rows affected (0.05 sec)

The above should be pretty self explanatory but for thoroughness sake…  The first line creates an empty database.  At that point only the root or admin user can use this database.  The Second command, creates a user account and assigns it a password.  This user account has NO privileges at this point.  The Third line is the most important.  When you grant permissions you can grant global permissions *.* meaning you can access ALL databases (not a good idea).  OR you can set Database permissions like I did above; database_name.*.  That .* after the database name means you have full privileges to that database only.  OR you can refine the permissions even further and grant permissions to a specific table in the database: database_name.some_table. Hope that clarifies things.  To state it in a more succinct way use this framework:

 GRANT [type of permission] ON [database_name].[table_name] TO ‘[username]’@'localhost’;

Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.

FLUSH PRIVILEGES;

Your changes will now be in effect.  I always like to test the account out before giving the account to the user.  To test out your new user, log out and log back in as the user:

mysql> quit 
mysql -u [username]-p

Revoke User Access or Delete a whole DB:

If you need to revoke a permission, the structure is almost identical to granting it:

 REVOKE [type of permission] ON [database name].[table name] TO ‘[username]’@‘localhost’;

You delete databases with DROP, you can also use DROP to delete a user altogether:

 DROP USER ‘demo’@‘localhost’;

 Recover Access when you have forgotten the root password:

Not that, that ever happens…

mysqld_safe --skip-grant-tables
mysql --user=root mysql

    update user set Password=PASSWORD('new-password') where user='root';
    flush privileges;
    exit;

That’s it for now.  More to follow…

Putting ‘lsof’ to use

Posted on by

lsof is a powerful tool that has proven very userful over the years in troubleshooting and forensic investigations.  Here are some useful lsof command examples:

In this example we are looking at all the files a given process has open (pid=1655 here this is the zabbix agent)

lsof -p 1767

Note you can clean up the output with something like the ‘cut’ or ‘awk’ command to focus in on the columns you are most interested in.  The output from the above command looks like this:

COMMAND    PID   USER   FD   TYPE DEVICE SIZE/OFF    NODE NAME
zabbix_ag 1767 zabbix  cwd    DIR  253,0     4096       2 /
zabbix_ag 1767 zabbix  rtd    DIR  253,0     4096       2 /
zabbix_ag 1767 zabbix  txt    REG  253,0   209432 1315973 /usr/sbin/zabbix_agentd
zabbix_ag 1767 zabbix  mem    REG  253,0   156872  917626 /lib64/ld-2.12.so
zabbix_ag 1767 zabbix  mem    REG  253,0  1922152  917633 /lib64/libc-2.12.so
zabbix_ag 1767 zabbix  mem    REG  253,0   145720  917661 /lib64/libpthread-2.12.so
zabbix_ag 1767 zabbix  mem    REG  253,0    22536  917663 /lib64/libdl-2.12.so
zabbix_ag 1767 zabbix  mem    REG  253,0    91096  917658 /lib64/libz.so.1.2.3
zabbix_ag 1767 zabbix  mem    REG  253,0   598680  917655 /lib64/libm-2.12.so
zabbix_ag 1767 zabbix  mem    REG  253,0   113952  917683 /lib64/libresolv-2.12.so
zabbix_ag 1767 zabbix  mem    REG  253,0    43392  917665 /lib64/libcrypt-2.12.so
zabbix_ag 1767 zabbix  mem    REG  253,0   386040  917664 /lib64/libfreebl3.so
zabbix_ag 1767 zabbix  mem    REG  253,0   224328 1317809 /usr/lib64/libssl3.so
zabbix_ag 1767 zabbix  mem    REG  253,0  1286744 1317807 /usr/lib64/libnss3.so
zabbix_ag 1767 zabbix  mem    REG  253,0    21256  917689 /lib64/libplc4.so
zabbix_ag 1767 zabbix  mem    REG  253,0   243096  917688 /lib64/libnspr4.so
zabbix_ag 1767 zabbix  mem    REG  253,0   177952 1317480 /usr/lib64/libnssutil3.so
zabbix_ag 1767 zabbix  mem    REG  253,0    17096  917690 /lib64/libplds4.so
zabbix_ag 1767 zabbix  mem    REG  253,0   108728 1312777 /usr/lib64/libsasl2.so.2.0.23
zabbix_ag 1767 zabbix  mem    REG  253,0   183896 1317813 /usr/lib64/libsmime3.so
zabbix_ag 1767 zabbix  mem    REG  253,0    63304  917530 /lib64/liblber-2.4.so.2.5.6
zabbix_ag 1767 zabbix  mem    REG  253,0   317168  917569 /lib64/libldap-2.4.so.2.5.6
zabbix_ag 1767 zabbix  DEL    REG    0,4                0 /SYSV6c0004c9
zabbix_ag 1767 zabbix  mem    REG  253,0    65928  917605 /lib64/libnss_files-2.12.so
zabbix_ag 1767 zabbix    0r   CHR    1,3      0t0    3662 /dev/null
zabbix_ag 1767 zabbix    1w   REG  253,2      386     120 /var/log/zabbix/zabbix_agentd.log
zabbix_ag 1767 zabbix    2w   REG  253,2      386     120 /var/log/zabbix/zabbix_agentd.log
zabbix_ag 1767 zabbix    3wW  REG  253,2        4  389438 /var/run/zabbix/zabbix_agentd.pid
zabbix_ag 1767 zabbix    4u  IPv4  13481      0t0     TCP *:zabbix-agent (LISTEN)
zabbix_ag 1767 zabbix    5u  IPv6  13482      0t0     TCP *:zabbix-agent (LISTEN)

In the above: the FD column represents the File Descriptor and Mode (Read/Write).  The 4th line from the bottom has an FD value of (2w) meaning it is open for writing, makes sense since it is a log.

The -Z option for ‘lsof’ specifies how SELinux security contexts are to be handled.  This option is only available of Linux systems that have an SELinux enabled kernel.

# lsof -Z -p 1767
COMMAND    PID SECURITY-CONTEXT                USER   FD   TYPE DEVICE SIZE/OFF    NODE NAME
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  cwd    DIR  253,0     4096       2 /
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  rtd    DIR  253,0     4096       2 /
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  txt    REG  253,0   209432 1315973 /usr/sbin/zabbix_agentd
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   156872  917626 /lib64/ld-2.12.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0  1922152  917633 /lib64/libc-2.12.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   145720  917661 /lib64/libpthread-2.12.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0    22536  917663 /lib64/libdl-2.12.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0    91096  917658 /lib64/libz.so.1.2.3
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   598680  917655 /lib64/libm-2.12.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   113952  917683 /lib64/libresolv-2.12.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0    43392  917665 /lib64/libcrypt-2.12.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   386040  917664 /lib64/libfreebl3.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   224328 1317809 /usr/lib64/libssl3.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0  1286744 1317807 /usr/lib64/libnss3.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0    21256  917689 /lib64/libplc4.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   243096  917688 /lib64/libnspr4.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   177952 1317480 /usr/lib64/libnssutil3.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0    17096  917690 /lib64/libplds4.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   108728 1312777 /usr/lib64/libsasl2.so.2.0.23
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   183896 1317813 /usr/lib64/libsmime3.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0    63304  917530 /lib64/liblber-2.4.so.2.5.6
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0   317168  917569 /lib64/libldap-2.4.so.2.5.6
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  DEL    REG    0,4                0 /SYSV6c0004c9
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix  mem    REG  253,0    65928  917605 /lib64/libnss_files-2.12.so
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix    0r   CHR    1,3      0t0    3662 /dev/null
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix    1w   REG  253,2      386     120 /var/log/zabbix/zabbix_agentd.log
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix    2w   REG  253,2      386     120 /var/log/zabbix/zabbix_agentd.log
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix    3wW  REG  253,2        4  389438 /var/run/zabbix/zabbix_agentd.pid
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix    4u  IPv4  13481      0t0     TCP *:zabbix-agent (LISTEN)
zabbix_ag 1767 system_u:system_r:initrc_t:s0 zabbix    5u  IPv6  13482      0t0     TCP *:zabbix-agent (LISTEN)

I’ll add more when I have time, comment if you want to see something specific.