X windows for Oracle DBAs

By Brian Fitzgerald

Introduction

This is for DBAs on who want to use Oracle X-windows based tools such as OUI or DBCA. Techniques described here can lead to a smooth installation experience. Beyond just a cookbook, this blog article also demonstrates the principles behind X forwarding. Red Hat Linux on AWS EC2 is presented here as an example platform.

In hurry? Skip to tl;dr, at the end.

X-windows

The X windows system consists of a server, a display, and clients. The X server accepts requests for graphical output from the clients and renders those requests on the display.  In older usage, the server and the client processes usually ran on the same host, a desktop Unix system. In the present context, the X server runs on your Windows PC and displays to your screen. The X server usually listens on TCP port 6000.

In X-windows lingo, applications and utilities are called clients. In the present context, clients run on the Oracle host system, an instance of AWS EC2. Examples of clients include xterm and Oracle Universal Installer (OUI), which run on separate server hardware. Our objective, therefore, is to have  an Oracle installation tool, such as OUI, and connect back to the X server on your PC so you can view and control the graphical user interface (GUI). Paradoxically, the X server does not run on “server” hardware, but on your windows PC. This table should clear up any confusion.

Description X Server X Client
Hardware PC server
Operating system Windows Linux
Has display monitor yes no
X server port (default 6000) binds
listens
accepts connections
connects to
Software Xming xterm, dbca, etc.

The client application connects to the X server via an ssh tunnel.

AWS EC2

Red Hat Linux on Amazon EC2 is presented as an example platform. AWS EC2 instances are built from an available Amazon Machine Image (AMI). For an Oracle installation, be sure to choose an AMI with a supported processor type, such as Intel x86_64 (not ARM), and a supported operating system, such as Red Hat (not Ubuntu or Amazon Linux).

The final step in EC2 launch is to select an existing key pair or create and download a new key pair. A key pair file from AWS has extension .pem.

Linux packages

Linux package installs are required before you can run X clients. In addition, it is helpful to install basic troubleshooting tools.

After you launch a new EC2 instance, run yum update.

# yum -y update

Running yum update may get you a few new tools, such as lsipc and lsmem.

Depending on the AMI, you may find that basic Linux tools are missing. Installing those tools makes troubleshooting easier. For example:

# yum -y install nc wget lsof unzip zip ksh sysstat strace traceroute 
# yum -y install python3 ed bc

You want to set up a working X windows environment, but before you do, notice three things from the login shell:

[ec2-user@ip-172-31-89-75 ~]$ echo $DISPLAY

[ec2-user@ip-172-31-89-75 ~]$ netstat -ntl | grep :60..
  1. The DISPLAY environment variable is not set
  2. There is no listener in the 6000 range
  3. No file .Xauthority is present

Now install the X windows packages.

# yum -y install motif xorg-x11-utils xterm xauth

xterm here is for testing X windows. After installing xauth, login using a new PuTTY session. On the first login, the following output will appear:

/usr/bin/xauth: file /home/ec2-user/.Xauthority does not exist

Now recheck:

[ec2-user@ip-172-31-89-75 ~]$ echo $DISPLAY
localhost:10.0
[ec2-user@ip-172-31-89-75 ~]$ netstat -ntl | grep :60..
tcp        0      0 127.0.0.1:6010          0.0.0.0:*               LISTEN
tcp6       0      0 ::1:6010                :::*                    LISTEN
  1. The DISPLAY environment variable is set
  2. There is a new listener in the 6000 range.
  3. A new .Xauthority file exists

The “10” in DISPLAY refers to the offset from 6000. When you set your DISPLAY to localhost:10.0, all X clients will connect to port 6010 by default.

Notice which process is listening;

[root@ip-172-31-88-44 ~]# lsof -Pi :6010
COMMAND  PID     USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
sshd    1351 ec2-user    9u  IPv6  20649      0t0  TCP localhost:6010 (LISTEN)
sshd    1351 ec2-user   10u  IPv4  20650      0t0  TCP localhost:6010 (LISTEN)

The sshd process is listening on port 6010. You are going to run X-client application on the Linux box. The client will connect to port 6010. The sshd process will forward the X-requests to your PC.

X server

There are several MS Windows-based X-servers available, Xming, for example. By default, the X server listens on port 6000. Confirmation:

C:\>netstat -na | findstr :6000 | findstr LISTEN
TCP 0.0.0.0:6000 0.0.0.0:0 LISTENING

xdpyinfo

Behind the scenes, OUI runs xdpyinfo. If xdpyinfo is missing, this message will appear:

ERROR: Unable to verify the graphical display setup. This application requires X display. Make sure that xdpyinfo exist under PATH variable.

To get xdpyinfo, install package xorg-x11-utils. An example of normal OUI output is:

Checking monitor: must be configured to display at least 256 colors.   Actual 16777216    Passed

X libraries

The manual may instruct you to install certain X windows runtime libraries, or OUI may so instruct you. The library list depends on the Oracle product and the version. Example:

yum -y install libX11 libXau libXi libXtst libXrender

If you have installed xterm, then you already have these dependent libraries.

mwm

mwm is the Motif Window Manager. It lets you move and resize windows. Without a window manager, you may encounter situations where you have a dialog box waiting for input but is inaccessible because it is behind another window. Here is the X root window after dragging three windows to non-overlapping locations.

mwm

The shell commands leading up to this were:

xterm & disown
mwm & disown
unzip -q ~/download/LINUX.X64_193000_db_home.zip
./runInstaller

Network

The X windows display technique described here assumes that you can connect via the secure shell (ssh) from your PC directly to the Oracle server host. The AWS EC2 instance has a Public IP address that is reachable from your PC.

The X windows protocol is a very “chatty” protocol, meaning that clients make a high number of small server requests. Chattiness makes every button press on the GUI respond very slowly. Having a fast network connection is essential. One might give some thought to enabling SSH compression; however, SSH compression does not mitigate the chattiness.

putty.compression

Enabling SSH compression will usually not result in a noticeable improvement in responsiveness.

Running an X windows client across sudo

In a prior section, xterm worked fine from the login shell (ec2-user). In practice, you will sudo to the oracle account to execute runInsaller or dbca. You may also sudo to the grid account to run gridSetup.sh. First, test xterm from the login account:

[ec2-user@ip-172-31-82-194 ~]$ echo $DISPLAY
localhost:11.0
[ec2-user@ip-172-31-82-194 ~]$ xterm
(it works)

One thing you will notice here in this case is that the offset in the DISPLAY variable is 11. The offset is not always 10, and depends on what port is available for X forwarding when you log in.

[ec2-user@ip-172-31-82-194 ~]$ netstat -ntl | grep :60..
tcp        0      0 127.0.0.1:6010          0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:6011          0.0.0.0:*               LISTEN
tcp6       0      0 ::1:6010                :::*                    LISTEN
tcp6       0      0 ::1:6011                :::*                    LISTEN

In this example, port 6010 was is in use when I logged in, so sshd started listening on port 6011 and set offset to 11.

If you try to run an X client across a sudo, you will get this error:

[ec2-user@ip-172-31-82-194 ~]$ sudo -i -u oracle
[oracle@ip-172-31-82-194 ~]$ xterm
xterm: Xt error: Can't open display:
xterm: DISPLAY is not set
[oracle@ip-172-31-82-194 ~]$ echo $DISPLAY

The DISPLAY environment setting did not carry over to the sudo shell. You can try setting DISPLAY, but you will get a different error:

[oracle@ip-172-31-82-194 ~]$ export DISPLAY=localhost:11.0
[oracle@ip-172-31-82-194 ~]$ xterm
PuTTY X11 proxy: wrong authorisation protocol attemptedError: Can't open display: localhost:11.0

To fix the authorization error, copy .Xauthority from the login account to the oracle account:

[ec2-user@ip-172-31-82-194 ~]$ sudo cp -up ~/.Xauthority ~oracle/.Xauthority
[ec2-user@ip-172-31-82-194 ~]$ sudo chown oracle:` id -gn oracle ` ~oracle/.Xauthority
[ec2-user@ip-172-31-82-194 ~]$ sudo -i -u oracle
[oracle@ip-172-31-82-194 ~]$ xterm

To automate the copying, I added this code to the login user .bash_profile:

if [ -z "$SUDO_USER" -a -f ~/.Xauthority ] ; then
    for u in oracle grid ; do
        if id $u > /dev/null 2>&1 ; then
            eval hd=~$u
            sudo cp -up ~/.Xauthority $hd/.Xauthority
            sudo chown $u:$(id -gn $u) $hd/.Xauthority
        fi
    done
fi

To automate setting DISPLAY, I added this code to the oracle .bash_profile:

if [ -z "$DISPLAY" ] ; then
    for port in {6010..6019} ; do
        if netstat -ntl | grep -q :$port ; then
            offset=$(( port - 6000 ))
            export DISPLAY=localhost:$offset.0
            break
        fi
    done
fi

To summarize, before sudo, copy .Xauthority from the login account to oracle. After sudo, set the DISPLAY environment variable. Automate those settings in .bash_profile.

puttygen

To connect PuTTY from Windows to AWS EC2 you need to generate a Putty Private Key (PPK) file. Start with the PEM (Privacy Enhanced Mail) file that you downloaded when you created your EC2 instance. Startup PuTTY Key Generator (puttygen). Load the PEM file. Click Save private key. Provide a name. Example:

us-east-1-key.pem
us-east-1-key.ppk

puttygen

X forwarding with PuTTY

To establish a session with PuTTY, navigate the PuTTY screens and make these settings.

Screen Item Example
Session Host Name (or IP address) 3.86.243.42
Connection->Data Auto-login username ec2-user
Connection->SSH->Auth Private key file for authorization C:\Users\Brian Fitzgerald\.aws\us-east-1-key.ppk
Connection->SSH->X11 Enable X11 forwarding checked
Connection->SSH->X11 X display location localhost:0.0

putty.host

You may save your settings for future reuse.

putty.user

ppk.png

The private key file is the ppk file that you converted from the pem file.

putty.x11

X display location refers to the X server on your PC, and is usually localhost:0.0.

X forwarding via bastion host

You can use ssh from the Windows command line to setup X forwarding via a tunnel at the bastion. Identify the following items:

item example
bastion key file us-east-1-key.pem
arbitrary Windows local port 3333
oracle box private IP address 172.31.82.194
ssh port 22
bastion host user brian
bastion public IP address 54.159.116.176

and use them as follows:

ssh -i keyfile -C -N -L localport:oraclebox:22 bastionuser@bastionhost

For example:

C:\>ssh -i us-east-1-key.pem -N -L 3333:172.31.82.194:22 brian@54.159.116.176
  • The bastion key file is the pem file that you downloaded when you created the EC2 instance
  • The local port is a free port on your PC that you will choose arbitrarily.
  • The oracle box is the private IP address or the oracle box that you want to ssh to.
  • Bastion user is your login on the bastion host, i.e. ec2-user.
  • bastionhost is the public IP address of your bastion host.

The requirements for the bastion host are different from the oracle box.

  • Establishing ssh trust from the bastion to the oracle box is not required.
  • The tunneling session is not responsible for X forwarding. Option -X is not required.
  • Disabling X11Forwarding at the bastion does not disable X forwarding as described here because the X11 forwarding happens at the Oracle host and in PuTTY.
  • The bastion login user does not have to be the same as the oracle box login user.
  • The bastion host does not have to be the same hardware or operating system architecture as the oracle box. Amazon Linux works.
  • In some bastion EC2 systems, no configuration is required. ssh tunneling works out of the box.
  • If the bastion /etc/ssh/sshd_config has “AllowTcpForwarding no”, then ssh tunneling is disabled.

You do not need to log on to the bastion with an interactive shell. After the tunneling session is established, you will not interact with it.

bastion netstat analysis

You can login to the bastion and observe the connections with netstat.

initial condition

Here is the initial condition showing only my shell session and the IPv4 and IPv6 sshd listeners. 172.31.38.240 is the bastion; 74.101.107.146 is my PC.

[brian@ip-172-31-38-240 ~]$ netstat -nat | grep :22
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 48 172.31.38.240:22 74.101.107.146:57042 ESTABLISHED
tcp6 0 0 :::22 :::* LISTEN

after tunneling is setup

Here is netstat after the tunneling session is established.

[brian@ip-172-31-38-240 ~]$ netstat -nat | grep :22
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 172.31.38.240:22 74.101.107.146:57297 ESTABLISHED
tcp 0 48 172.31.38.240:22 74.101.107.146:57042 ESTABLISHED
tcp6 0 0 :::22 :::* LISTEN

On Windows, netstat looks like this

C:\>netstat -na | findstr :3333
TCP 127.0.0.1:3333 0.0.0.0:0 LISTENING
TCP [::1]:3333 [::]:0 LISTENING

C:\>netstat -na | findstr :22
TCP 192.168.1.7:57297 54.159.116.176:22 ESTABLISHED

The tunnel appears as a port 3333 listener and a connection to the bastion.

after a login session is established

Finally, here is netstat after establishing a connection to the oracle box, at 172.31.82.194.

[brian@ip-172-31-38-240 ~]$ netstat -nat | grep :22
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 172.31.38.240:22 74.101.107.146:57297 ESTABLISHED
tcp 0 48 172.31.38.240:22 74.101.107.146:57042 ESTABLISHED
tcp 0 0 172.31.38.240:39522 172.31.82.194:22 ESTABLISHED
tcp6 0 0 :::22 :::* LISTEN

On the oracle box, netstat shows the connection from the bastion and the sshd listeners.

[ec2-user@ip-172-31-82-194 ~]$ netstat -nat | grep :22
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 172.31.82.194:22 172.31.38.240:39522 ESTABLISHED
tcp6 0 0 :::22 :::* LISTEN

On Windows, netstat shows the connection from PuTTY to the tunnel.

C:\>netstat -na | findstr :3333
TCP 127.0.0.1:3333 0.0.0.0:0 LISTENING
TCP [::1]:3333 [::]:0 LISTENING
TCP [::1]:3333 [::1]:61678 ESTABLISHED
TCP [::1]:61678 [::1]:3333 ESTABLISHED

The connection on the local host appears as two output lines, one for the socket endpoint from PuTTY and the other for the socket endpoint at the tunnel.

Client startup step by step

If you follow these steps one at a time and resolve issues as you go, it is less likely that you will encounter difficulty when you try to run an Oracle tool

  • Log in.
  • Check the DISPLAY environment variable.
  • Test xterm.
  • Test xdpyinfo.
xdpyinfo > /dev/null
  • copy .Xauthority to the oracle account.
  • sudo to oracle.
  • Set the DISPLAY environment variable.
  • Test xterm again.
  • startup mwm.
  • finally, start your Oracle tool. Ex:
./runInstaller

Summary

It is extremely helpful to configure a proper Linux and X windows environment before attempting to run an Oracle tool such as OUI. Commands nc, lsof, strace, traceroute, lsipc, and lsmem were used while writing this blog article. From xorg-x11-utils, xlsclients and xev were used. From motif, mwm was used. xauth is required for X windows. An oracle installation may require unzip, ksh, sysstat, or xdpyinfo. Not all needed utilities installed by default. You must install them yourself.

If you Google “x windows forwarding not working” you will find cases where one person’s advice works for one person and not another. Trying random tips and tricks without understanding can lead to your own solutions that don’t work all the time, and enabling unnecessary options. To improve understanding, I have added diagnostic output from netstat and lsof.

Be sure to understand that the X server runs on your PC and listens on port 6000 by default. On the oracle box, sshd listens on port 6000 + an offset, often 10. Setting DISPLAY=localhost:10.0 will lead X clients to connect to the sshd listening on port 6010 on the local host. sshd will forward the X traffic back to the X server on your PC.

It is well to break up the setup and testing into multiple, simple steps, rather than attempting to startup runInstaller in one go.

tl;dr

  • Install needed packages
  • Copy .Xauthority to the oracle account
  • Set the DISPLAY environment variable

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s