Oracle Labs by Yuri Khazin, Oracle DBA

May 16, 2016

Linux 6.7 telnet: connect to address x.x.x.x: No route to host

Filed under: Uncategorized — oraclelabs @ 15:39

You are trying to connect with telnet to a freshly unwrapped Red Hat Linux 6.7 (or a derivative, such as Oracle OEL). Why would you use such a commonly despised tool as telnet begs an explanation. Well telnet is still a useful diagnostics tool, sometimes you need to check if you can talk to a web server, to a mail server or, in our case, to Oracle listener on a particular port. And you may run into an interesting problem while trying (other than a fact you need to install telnet binaries as it is not usually included).

So you can ping the other host (which is a Linux 6.7), you can even ssh to it, but the telnet will give you “No route to host” message.

Since ping and ssh do work you rule out the gateway settings (/etc/sysconfig/network) on the client side, the routing table or client side firewall (well, it still could be just it). But a quick check on the target side will show that it’s indeed firewall issue. When you shut down the target’s firewall the telnet message will change to “Connection refused” that is, if you attepmpt to talk to a port where no process is listening. Telnet to a port 1521 returns Oracle listener response. Picture below illustrates just that.

image

So what to do now? We need to add a rule to the firewall on the target side for a particular protocol and port. For instance, port 1521 for Oracle’s listener. Here are the ‘out of the box’ rules:

image

Last rule in the INPUT chain is a reject rule. Adding more rules after it is pointless, the new rule has to be added before the ‘reject’.

iptables -I INPUT 5 -m state –state NEW -m tcp -p tcp –dport 1521 -j ACCEPT

This command will add a rule at a position 5 in the INPUT chain, pushing the last rule to position 6. That’s it. Save the iptables current configuration so it is preserved through reboot.

image

Advertisements

April 15, 2016

How to publish your document from Google Drive (Google Docs) to WordPress

Filed under: Uncategorized — oraclelabs @ 08:52

Want a short answer? – It’s complicated but possible.

As of this time (April 2016) I have tried many different ways to transfer some of my writings from Google Docs to wordpress.com based blog and can’t claim a success. Thing is, both Google and WordPress are constantly changing their API and security and what not, so various plug-ins and techniques just can’t keep up with these changes. Eventually, I came up with a lesser evil approach. That is – the use of a desktop program named  Open Live Writer, which is a successor to Microsoft Live Writer, but now as an open source project.

In short, you can copy your document from Google Docs and paste it into the Live Writer (I think the old Windows Live Write will do just as well, if you happened to have it). After copying into the Writer you keep the text and the pictures (great relief) but may loose table formatting. After posting to your blog you may need to download that post back to the Writer and fix table formatting. As my blog is heavy on in-text pictures this approach was a salvation for me and a great time saver, I can’t imagine being forced to manually upload and re-insert my screenshots into the blog post.There is a small complication with pictures as well, I found out that later. When you copy-paste your document, what gets copied is HTML. When it comes to pictures, you will get links to pictures that are still on Google Drive. If that is acceptable to you then no issue here. If you delete original document from Google and purge the trash bin the WordPress article will have lost the pictures as well. The workaround is to copy pictures one by one and paste them over again in the Writer. This way they become local pictures rather than links to remote files.

P.S. By the way, I tried BlogDesk program as well and it has lost the pictures in the transition, so – no help here. Same applies to Chrome’s plug-in “Google Docs to WordPress” – couldn’t make it work, besides, people say it needs to dial home server in order to connect to both Google and WordPress and that is a security concern.

P.S.2 If you host a wordpress on your server your story is different, there are many wordpress plug-ins that may help you, my case is wordpress.com based blogs.

April 14, 2016

RHEL 6.7, OEL 6.7 network adapters configuration and VM cloning


Cloning of a virtual machine in Oracle’s VB (Virtual Box) presents a particular pain. The exact clone, of course, is created with same MAC and IP addresses for all NICs present. Great for some purposes, not so great if you wanted a duplicate to repurpose. Then you would have reinitialized MACs during or after cloning and your trouble would start as all NICs in the clone won’t be functional upon power up. In older Linux distribution all you had to do is edit MAC addresses in the ifcfg-eth* files in /etc/sysconfig/network-scripts and that was it. Not so now.

In Linux 6 (Red Hat, Fedora, Centos) the hardware device control is primarily done by udev system and by NetworkManager service. This is an out of the box configuration. In the following exercise we will look at a case of a virtualized OEL 6.7 Server, which is a non-GUI Oracle’s package based on RHEL 6.7

Why Server edition? Because it is a headless, non-GUI distro, where all management needs to be done via command line and not through the nice desktop tools.

As stated above, in Linux 6 the udev system looks after changes in hardware and enumerates new NICs while preserving the old NIC records. Both parts are now present in this file: /etc/udev/rules.d/70-persistent-net.rules

Therefore, your /etc/sysconfig/network-scripts/ifcfg-eth* files are now not in sync with persistent rules. We will look at how they should be reconciled.

A few additional notes:

  • In my practice with OEL 6.7 the system-config-network-tui tool had no effect on configuration so I don’t recommend using it
  • Don’t just edit /etc/sysconfig/network-scripts/ifcfg-eth* files after cloning, read this article to the end to understand why you need to examine and change the udev part
  • OEL 6.7 Server (and probably Red Hat, Centos and Fedora distros) come with sshd server preconfigured with some annoying security features that make ssh login very slow. If you went through this article and still have issues logging into your machine you may need to read this guide about “Fixing SSH login long delay”

So, let’s start:

On original machine our network configuration is as follows:

eth0 is an adapter with static IP connected to host-only network

eth1 is an adapter with DHCP connected to NAT network

Both are running

Following are their respective config files

[root@oms1 network-scripts]# cat ifcfg-eth0
DEVICE=eth0BOOTPROTO=none

ONBOOT=yes

IPADDR=20.20.10.21

NETMASK=255.255.255.0

[root@oms1 network-scripts]# cat ifcfg-eth1
DEVICE=eth1HWADDR=08:00:27:F9:3C:34

TYPE=Ethernet

ONBOOT=yes

NM_CONTROLLED=yes

BOOTPROTO=dhcp

Notice, that although ifcfg-eth1 contains HWADDR keyword it is not required and can be removed when udev is allowed to manage the hardware.

Below is the contents of persistent rules (before cloning). Sometimes after cloning, the order of devices may be mixed up, so pay close attention to MAC addresses and then assign the desired names in proper order.

/etc/udev/rules.d/70-persistent-net.rules
# This file was automatically generated by the /lib/udev/write_net_rules# program, run by the persistent-net-generator.rules rules file.

#

# You can modify it, as long as you keep each rule on a single

# line, and change only the value of the NAME= key.

# PCI device 0x8086:0x100f (e1000)

SUBSYSTEM==”net”, ACTION==”add”, DRIVERS==”?*”, ATTR{address}==”08:00:27:7d:f0:88″, ATTR{type}==”1″, KERNEL==”eth*”, NAME=”eth0″

# PCI device 0x8086:0x100e (e1000)

SUBSYSTEM==”net”, ACTION==”add”, DRIVERS==”?*”, ATTR{address}==”08:00:27:f9:3c:34″, ATTR{type}==”1″, KERNEL==”eth*”, NAME=”eth1″

After cloning with MAC address re-initialized the machine comes up like this (new NICs detected but since there are no matching ifcfg-eth* files the NICs are inactive):

Old NICs are eth0 and eth1, the new ones are eth2 and eth3

Delete old NICs:

Rename the ordering of NICs:

Make sure ifcfg* files match (optionally change the static IP)

Make sure original machine is NOT online as the clone will try to come up with same static IP. Reboot the clone machine (necessary, because of change to persistent rules)

After reboot of the clone:

If you want to change the static IP you can do it in ifcfg-eth0 and then restart networks

service network restart

Acknowledgements and references:

December 30, 2014

Building a Virtual Oracle RAC – Project Introduction


This article was originally written in 2010 and then updated several times. In 2010 the Virtual Box belonged to Sun Microsystems, now it is a part of Oracle’s toolbox. The interface of Virtual Box has somewhat changed over the years, so my apologies to the reader for screen snapshots that don’t look exactly like today’s version. You can still download older version of the program so that chances are that you won’t encounter issues while trying out this guide.

Introduction: This learning experiment is about building a totally virtual Oracle RAC environment at home using only open source software available to wide public (With the exclusion of Windows, if you happen to use it. I wish there was an open source version of MS Windows, that would benefit MS as well. Works for Red Hat, right?).

My project is generally based on an excellent article by Jeffrey Hunter (and other authors) – Build Your Own Oracle RAC Cluster on Oracle Enterprise Linux and iSCSI

Jeffrey’s article gives instructions on how to build a physical setup. I do recommend reading this article or at least giving it some 10 minutes scan before you attempt to build a RAC. In that article Jeffrey estimates a cost to build a production RAC in physical environment between $20K and $30K. He shows that for the purposes of learning the technology a “mostly physical” RAC with some parts virtualized can be built for under $2700. I am going to take the virtualization to the extreme and build a RAC inside a single PC. Yes, I do not care now about performance, and yes, I am willing to buy some additional memory for the PC. You can not really expect to run two Linux nodes with databases and a storage (SAN) simulation on a PC with 1GB of memory. My estimated cost is, therefore, somewhere under $100, if all we need is a memory upgrade. Sounds good? It is still a lot of work and it will probably take you many hours to do it right. The benefit is a hands on knowledge, which is priceless.

Now to the choice of hardware and software.

  • PC or server with some 200GB space and 3.5GB memory (XP Professional can handle up to 4GB).
  • The host OS: Windows XP SP3 professional (although SP2 will probably do as well)
  • Has been tested on Windows 7 Home and shown no issues
  • Can work on Linux host machine (Ubuntu, Debian, openSUSE, Fedora etc)
  • Virtualization software: Virtual Box 3.1.2 (or higher). Tested and works with no issues on both Intel and AMD processors (may need to adjust some CPU parameters in configuration).
  • Database nodes: Oracle Enterprise Linux 5 update 3 as a guest OS, running Oracle Database 10.2.0.1
  • SAN/NAS emulation software: Openfiler 2.3

My reasons for choosing particular software:

Host OS does not really matter, virtualization software can run on many different platforms. If you desire so, you can run this whole project using Linux as a host. Virtual Box is selected because it is an open source software produced by Oracle.  Virtual Box at 3.1.2 was still owned by Sun Microsystems, who acquired it from Innotek and then maintained from 2008. Oracle maintains this product from 2010.

Why Virtual Box and not VMware? VMware is a popular product but I do not see a consistency in their support of free versions of the software. In my experiments with VMware I have encountered some obstacles while running Linux guests (particularly in networking and CPU clock synchronization). Virtual Box is an open source product and quite a mature one, well documented and supported. So far I was able to run OEL4 and OEL5 with no issues (even with no guest additions installed). I like the product’s built-in networking, it seems to be more flexible and stable. These are all my personal preferences, of course.

Why Oracle’s Linux? This particular flavor of Linux is a modified Red Hat Enterprise Linux 5. Oracle has made a few adjustments that allow Oracle 10g R2 database (32 bit) to install with no issues on it (usually, no additional RPM’s required). Since I am aiming to install Oracle in the guest(s) thus the choice of this Linux flavor. Besides, this Linux comes with OCFS2 and most of the ASMLib software packages installed. If you have a licensing question about Oracle Enterprise Linux (OEL) I can answer it. You only need a license if you want Oracle’s support. For learning purposes you do not need a license. If you will need to download some rpm libraries and updates you can get those for free, the YUM configuration can be pointed to a free repository (not discussed here).

Now the database itself. Which edition of 10.2.0.1 are we going to use? There is no clarity in that article on this matter, for now I assume the Standard Edition will do. There are some additional pieces of software from Oracle (or used by Oracle) to be installed, those are: OCFS2, ASMLib 2.0 and TAF.

Now that we know what we need to get started let’s split the work into more manageable mini projects, follow them just in this order, and hopefully all pieces will fit:

Update (2015). There seems to be a viable alternative to Openfiler from the FreeNAS. First off, it is Free. Second, it is open source (or, maybe that was first). Then, they give you full documentation. There was time when we were on our own with Openfiler, as they wanted money for the tech documentation.

July 18, 2014

How to check patchlevel of Oracle Database

Filed under: Oracle Database, Uncategorized — oraclelabs @ 11:18

One way would probably be going through Oracle Inventory, assuming that it is in sync with the actual software. Another way is to query the registry (the registry history).

The following query applies to 11g.

select
b.host_name,
b.instance_name,
to_char(a.action_time, ‘yyyy-mm-dd hh24:mi:ss’) action_time,
substr(a.id,1,10) id,
substr(a.action,1,15) action,
substr(a.version,1,8) version,
substr(a.BUNDLE_SERIES,1,6) bundle,
substr(a.comments,1,30) comments
from sys.registry$history a,
v$instance b
order by a.action_time desc;

If you are into running this query in sqlplus, you can use these column settings:

set lines 130 pages 60
col host_name for a15
col instance_name for a8
col action_time for a20
col id for a8
col action for a15
col version for a12
col bundle for a6
col comments for a30

June 6, 2013

On DATE and TIMESTAMP math

Filed under: date and timestamp, Oracle Database — oraclelabs @ 15:38

Ever faced with a need to specify a time range like this: “between first and last day of the previous month”?

So how about this one:

select
add_months(trunc(sysdate,’MM’), -1) “First Day of Prev Month”,
trunc(sysdate,’MM’) “Last Day of Prev Month”
from dual;

OK, this was easy. But what is they say “between first and last second of the last day of the previous month”
Well, we just subtract an interval of 1 second:

select
add_months(trunc(sysdate,’MM’), -1) “First Day of Prev Month”,
trunc(sysdate,’MM’) – interval ‘1’ second “Last Day of Prev Month”
from dual;

Good, they say. Now how about give us all this in timestamp, not a date type.

Precisely, give us the range “between first moment and the last microsecond of the last day in previous month”. That was a long thing to say.

So here it is, in dollars, cents and microseconds:

select
to_timestamp(add_months(trunc(sysdate,’MM’), -1)) “First moment”
to_timestamp(trunc(sysdate,’MM’)) – INTERVAL ‘0 00:00:00.00001’ DAY TO SECOND(6) “Last moment”
from dual;

Was it a microsecond or millisecond? I always get confused with kilos and pounds…

May 2, 2013

New in Oracle 11g. Rollback on exit in sqlplus

Filed under: Oracle Database — Tags: , , , — oraclelabs @ 10:25

For a very long time it’s been a standard feature of sqlplus to commit on exit. This feature could have been overwritten only by a “WHENEVER SQLERROR EXIT ROLLBACK”, but there was no control for rollbacks when we exit normally. Having autocommit disabled (I am grateful it is disabled by default) and now disabling commit on exit will be very useful in a situation when your sqlplus session is really interactive and you would not appreciate a commit if you exit accidentally.

Here comes in a handy new feature in 11g:

SET EXITCOMMIT OFF (Default is ON)

Refer to the documentation on this feature (there is also info on AUTOCOMMIT and how that affects EXIT behavior).

If you want to set this and other features in your sqlplus automatically, you can use what Oracle calls “Default Site Profile Script“:

$ORACLE_HOME/sqlplus/admin/glogin.sql

%ORACLE_HOME%\sqlplus\admin\glogin.sql

A user profile (login.sql) can also be used for this purpose, provided that it is located some place where SQLPATH variable points (this is also described in the doc links above).

 

For some reason, most of the Google search links on this subject led me to the “whenever” command, rather than to “set”.

March 13, 2013

Oracle Virtual Server (Oracle VM, OVM) inside Virtual Box


As if Oracle Virtual Box was not virtual enough, now you can put one dream into another dream, just like in my favorite movie Inception.

Get this, you can run Oracle Virtual Server (other names: Oracle VM, OVM) inside Virtual Box. Wrap you mind around that…

Now, for confused, Oracle VM Server is a product separate from Virtual Box and comes with it’s own history. As far as I know, Oracle VM Server is a Xen Linux based software that is not hosted, i.e. installs onto a bare metal hardware. Virtual Box, in turn, is hosted and can run within almost any known operating system. Virtual Box was not always Oracle’s product and not even Sun’s, it was originally developed by Innotek.

So… if you wanted to test drive a VM Server farm but don’t have some 4-5 physical servers lying around, you can still do so if you put it all in Virtual Box environment.

Oracle provides VirtualBox Templates for Oracle VM Manager and Oracle VM Server. From these templates you can create actual guests in Vritual Box. (Once again, this is not an installation media for physical servers but VM templates. If you are after that sort of media, you need to go to Oracle Software Delivery Cloud – Oracle Linux and Oracle VM).

To run this virtual farm you will need a lot of RAM on a single machine or, even better, run this setup on two machines. One PC can run the VM Manager and another will run one or two VM Servers. So much fun with the networking!

Follow the instructions on the download page, they are quite thorough. The only trouble I have had with version OVM 3.1.1  template (build 365) was that VM Manager comes with Oracle Express Edition 11g as an internal repository and it won’t start after guest starts for the first time. Turns out, there is a MEMORY_TARGET parameter in spfileXE.ora that is set to about 1GB and there was not enough free memory. Once this parameter was removed the database started and all works like a charm after guest reboot. I run this guest with 1.6GB RAM and the VM Servers needs 4GB at least (starts with 3GB but not sure if it works well). Honestly, I have not tried to create a guest inside VM Server yet, but I suspect I may need much more RAM for that (notice to self, update this post when I try).

One more thing. VM Manager is a browser based control interface for OVM, you can run it within your guest machine but it is better to do so from your PC. In that case you will need to check that firewall is turned off in the VM Manager guest (service iptables stop, then set it in chkconfig so it will stay stopped after reboot). Check firewall on your PC as well. Direct your browser to the guest IP address and appropriate port. If you somehow happened to install OVM 3.2.1, notice that http is now replaced by https and default port is not 7001 but 7002.

Good luck on your test drive.

OEL 6.2 on Virtual Box 4.2.4

Filed under: Oracle VirtualBox, Virtualization, Windows 7 — Tags: , , — oraclelabs @ 10:28

I was installing “Oracle Linux Release 6 Update 2 Media Pack v1 for x86_64 (64 bit)” on VBox 4.2.4 the other day. It was on Windows 7 Home Premium SP1, the RAM given to the guest was set to 2GB (quite generous). The installation of “basic” server went well and by the end of it the guest wanted to reboot. This is when the dreaded message “Virtualbox Manager has stopped working” appeared. A research on forums was educational, but useless, as a wide variety of causes may be in play. Tried this and that, upgraded first to VBox 4.2.6, then to 4.2.8 with no luck. Then, remembering that this Linux version used to work well on older VBox I have downgraded to 4.1.22 and recreated host only networks (those were deleted during the reinstall). Guess what? All works fine now. Maybe in later versions of 4.2.x this problem will be resolved.

May 2, 2011

My favorite Firefox add-ons

Filed under: Browsers — oraclelabs @ 13:24

Let me share some helpful information with you. Something collected and tested over a few years…

Any Key – type in any language   Yes, it is a phonetic transliteration

Transliterator – Similar to Any Key (may work in versions where Any Key does not)

WOT – Web Of Trust – Alerts you about dangerous links

WOT is an absolute MUST for anyone browsing the net. There is an uncountable number of evil sites on the net. Just skip “c” in “oracle” and watch what happens. I promise, you will be totally shocked when you see what kind of sites Google search brings you even in a sponsored section and how much malware you can get from an “innocent” web page.

“No color”. Remove colors and background images from the page in one click.

This one is experimental but I love it. It has one drawback – its effect is global, i.e. affects all tabs at once. Firefox and Opera have an option not to load images but this extension does it better.

Adblock – life saver !!! – Removes almost all advertisement

I am using Adblock for a long time and forgetting what a messy place the internet is. Only when I am forced to use IE it reminds me how lucky we are with Firefox.

Download Statusbar A very convenient thing for occasional downloads (replaces Firefox built-in, which is quite sloppy).

DownThemAll is all you can desire from a download manager  This one is for massive queued downloads.

Flashblock. Never be annoyed by a Flash animation again! Another great thing, saves time and nerves.

QuickProxy switch turns on/off your internet proxy configuration. May come handy if you work from home using VPN.

NoSquint   NoSquint allows you to adjust the text-only and full-page (both text and images) zoom levels as well as color settings both globally (for all sites) and per site.

And finally:

FEBE (Firefox Environment Backup Extension) lets you backup and restore all your installed plug-ins. Yes, even in a fresh  Firefox installation you can restore all you plug ins in a few seconds.

Older Posts »

Blog at WordPress.com.