In 10g and 11g Enterprise Edition, one could select which options to install or not to install during the installation process (excl. the 10g ‘custom database’ option, you would get partioning, OLAP and rat). In 12g, one is not able to choose during install anymore, you will get all the options and they must be removed afterwards. Remove / disable them after installing the database software (only), but before creating databases.
The best way to do this is using the ‘chopt’ tool, or when the option is not available, the Oracle Universal Installer must most likely be used. It’s available in Windows and Linux. When using Windows, one can also rename the .dll’s which ‘enable’ the options. It will NOT remove the objects from the database! Continue reading
SQL> alter database force logging;
ORA-12920: database is already in force logging mode
Error in Dutch:
ORA-12920: Database staat al
niet in de force logging-modus.
The word ‘niet’ should not be there!
I’m talking about the ‘third’ field in the database entries. Field number three. Which indicates there could be a fourth, fifth etc… right?
A customer where I came (who went from AIX to Linux), who had interpreted this comment and therefore expanded the oratab with an extra column, to datapump the database (y/n).
When I shut down the databases, there was some unexpected behaviour when I invoked dbshut… Strange, but the extra (last) field ‘for datapump’ was read, not the third!
I had a virtual machine in VirtualBox for an OracleVM Server to test an OracleVM setup, but it froze every 5 minutes and had to restart the OVS again and again.
I got this tip from Robert Pastijn, Oracle Netherlands; When you create a virtual machine in VirtualBox for OracleVM Server, do _not_ choose the ‘Intel PRO’ type adapter type, but the ‘PCnet-FAST‘ one!
SOLVED: this issue is solved in Linux 6.4 (kernel: 2.6.39-400.17.1.el6uek and 2.6.32-358.el6).
A single entry in /etc/fstab like [tmpfs /dev/shm tmpfs size=3g 0 0] now works as it should!
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, ‘mounting’ the /dev/shm twice (?):
tmpfs /dev/shm tmpfs size=3g 0 0
IMPORTANT NOTE: make sure the first field (fs_spec) ‘shmfs’ or ‘tmpfs’ gets the same name as the already existing ‘defaults’ name. So if you have a line [tmpfs /dev/shm tmpfs defaults 0 0], make sure the ‘overruled’ line also starts with ‘tmpfs’: [tmpfs /dev/shm tmpfs size=3g 0 0].
If not, a `mount -a` will un-mount (!!!) the ‘shmfs’ and remount ‘tmpfs’, this results in immediate clearing the ‘/dev/shm’ memory and all your SGA is instantly gone! Running this when databases are running, your databases with AMM will crash! This ‘issue’ is still there last time checked in Linux 7.3. In Oracle documentation about /dev/shm, the first field is ‘shmfs’ with can result in crashing databases when a `mount -a` done!
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]. Continue reading
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! Continue reading
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:
When enabling the GPG key, use:
(Oracle VM 3.x is based on OEL5) Continue reading
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’:
yum clean all
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 .
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…