Oracle 48

A fresh view on Oracle

Bug: /def/shm does not use /etc/fstab attributes after reboot

There is a bug in Red Hat Linux 6 and Oracle Enterprise Linux 6 (UEK and RHEL-kernel) and probably all other Red Hat 6 related Linux Distro’s.

When you need more memory for SGA/PGA when using MEMORY_MAX_TARGET, you need to resize /dev/shm. By default this is 50% of total memory and Oracle tells you to add the following to /etc/fstab:

shmfs /dev/shm tmpfs size=3g 0 0

Which works…

[Read the rest of this entry...]

oracle.cluster.verification.VerificationException: [hostname]: [hostname]

A couple of days ago I did a new install of the Oracle Database Software (on a Virtual Machine) and it kept failing. I got some oracle.cluster.verification.VerificationException error, but I could not get more information from the logs. Usually it’s a bit more detailed after the colon, but this time it just said: [hostname]: [hostname].

[Read the rest of this entry...]

Partition pruning fails when using functions: use virtual column partitioning

Using partitions may decrease search time when searching through a lot of data from minutes to seconds, from hours to minutes. Storing information partitioned per month/year and retrieving it knowing which month/year you want from millions of records is more efficient than an index on all those months and years. Unfortunately changing to partitions afterwards almost always needs application changes, (I actually never tried an sql_profile…).

When looking for a solution using partitioning, I came across another problem. They were using a function in the one and only predicate in the query and the fact is that when a database uses partitioning for storing data, using a function on the partitioned column will the optimizer searching all data, not the partition. Understandable, because with the function you are searching for derived values, not the actual values in the partition. Oracle must search all the partitions and can not ‘prune’ the partitions. Determining and using a partition is called Partition Pruning.

The solution to be used here is called Virtual Column Partitioning and as a coincidence and nice extra they didn’t even had to change the application!

[Read the rest of this entry...]

Updating Oracle VM Servers with public YUM

You can update your Oracle VM Servers (3.x) with the Oracle Public Yum Server. In the Oracle VM Manager console go to the ‘Tools and Resources’ tab and click ‘Server Update Management (YUM)’.

For the ‘YUM Base URL:’, use:
http://public-yum.oracle.com/repo/OracleVM/OVM3/latest/x86_64/

When enabling the GPG key, use:
http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
(Oracle VM 3.x is based on OEL5)

[Read the rest of this entry...]

Oracle Linux 6, oracle-validated is missing? no renamed!

In Oracle Linux 6, it looks like the ‘oracle-validated’ package is missing, but Oracle actually renamed it to ‘oracle-rdbms-server-11gR2-preinstall.x86_64‘.

To quickly use the public yum repository of Oracle and install ‘oracle-rdbms-server-11gR2-preinstall.x86_64′:

cd /etc/yum.repos.d
wget http://public-yum.oracle.com/public-yum-ol6.repo
#vi /etc/yum.repos.d/public-yum-ol6.repo
yum clean all
yum repolist
yum install oracle-rdbms-server-11gR2-preinstall.x86_64

You will get the ‘latest’ build enabled.
If you don’t want this, vi /etc/yum.repos.d/public-yum-ol6.repo .

Happy installing!

Port 1521, should be 2483 or 2484 officialy…

Port 1521 is used for ages as the default port for the Oracle listener, but while I was looking at some default port numbers on Wikipedia, I saw port 1521 is not the official listener port. 1521 is the nCube License Manager official port!

Port 2483 and 2484 are the official Oracle ports according to IANA!

ttc                2483        tcp    Oracle TTC
ttc                2483        udp    Oracle TTC
ttc-ssl            2484        tcp    Oracle TTC SSL
ttc-ssl            2484        udp    Oracle TTC SSL

According to Metalink’s document “Listening Port numbers [ID 99721.1]“, in Future releases the 1521 port may change to 2483 TCP and 2484 TCP with SSL, but this is document is written in 2008…

Edit: The metalink note has been removed…

Happy listening!

Why I dislike non-English for OS and DB language

Deleting expired objects with RMAN, resulting in an invalid response while answering ‘correctly':

Wilt u de bovenstaande objecten verwijderen (voer JA of NEE in)? JA
"JA" is een ongeldige respons - voer opnieuw in.

Wilt u de bovenstaande objecten verwijderen (voer JA of NEE in)? j
"j" is een ongeldige respons - voer opnieuw in.

Wilt u de bovenstaande objecten verwijderen (voer JA of NEE in)? y
Het gearchiveerde log is verwijderd.

Happy answering…

Adding VirtualBox Simple External authentication

If you don’t want to use Null, Guest or External Host authentication when using VirtualBox Remote Display, one can add an extra external option called VBoxAuthSimple as explained here: http://www.virtualbox.org/manual/ch07.html#vbox-auth . You will use a separate password for connecting remotely to the VirtualBox’ Guest.  This username/password will be stored in the machine’s XML settings file.

(trouble with mstsc.exe?)

[Read the rest of this entry...]

VirtualBox VRDP authentication fails with mstsc.exe

I had this problem, when trying to connect with the External Authentication Method, using the Remote Display server in VirtualBox (4.1.8 and 4.2.0). When I want to connect with Microsoft’s Remote Desktop (mstsc.exe), it failed every time with “Your Remote Desktop session has ended. Your network administrator might have ended the connection (…)”.

[Read the rest of this entry...]

Oracle SQL Developer and a SQL Server Instance

Sometimes… as Oracle DBA you need to connect to SQL Server (with Oracle SQL Developer and jTDS plug-in installed).

When using ‘SQL Server Management Studio’, the ‘Server name’ is in the format:

SERVERNAME\INSTANCE.

This won’t work in SQL Developer:

Status : Failure -Unknown server host name 'SERVERNAME\INSTANCE'.

This can be solved using the ‘hostname’ in the next format in SQL Developer:

SERVERNAME;instance=INSTANCE

For me it worked :)