The extended or streched Oracle (RAC) cluster – part 1

First of all, both terms ‘extended’ and ‘stretched’ are used on blogs, in documentation and logfiles. I will use ‘extended’ in this one. Also, voting files are files, not disks. Oracle will store the file(s) on one of the disks within the specified diskgroup, preferable in different failgroups. Also people talk about a ‘RAC’ cluster, but an Oracle cluster can exist without RAC. RAC is the database option to run the database in a cluster on Oracle clusterware.

Sorry, no images in this blog post… only text.

When is an Oracle cluster an extended/stretched cluster?

It’s not distance. Oracle does not know if it’s in the same or different racks or buildings, divided by roads or rivers…  There is no setting ‘extended=Y’ in Oracle that it knows about being extended.

It’s the way _you_ design the storage for Oracle clusterware and ASM.

My point of view, extended is; When there is mirroring of blocks on disks and these mirrored blocks are on disks in different storage locations. Continue reading

Enabling Oracle 12c direct NFS client on Windows

Yes, enabling the Oracle 12c direct NFS client on Windows and it is documented, but I didn’t get changed on Oracle Support (I have send an improvement request for Doc ID 1468114.1)…

One needs to run ‘%ORACLE_HOME%\bin\enable_dnfs.bat’ now, which does NOT copy the ‘oranfsodm12.dll’ over the ‘oraodm12.dll’, but it copies the ‘oranfsodm12.dll’ to the ‘%ORACLE_HOME%\rdbms\lib\odm\’ directory!

After the file has been copied and Oracle is restarted, the [Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 3.0] line in the alert.log is shown!

Enabling Direct NFS Client – documentation

Oracle Database Installation Guide for Microsoft Windows – Oracle Database Postinstallation Tasks – 7.5.1.5 Enabling Direct NFS Client: http://docs.oracle.com/database/121/NTDBI/postcfg.htm#CHDFGFDC

ps. on Windows the supported ‘nfs_version’ is still NFSv3 :(

Server error: ‘The backing device /dev/mapper/xyz is not allowed to contain partitions’ on OracleVM

Ok, I added a new disk (well old actually, but new for the machine) to my Oracle VM server. It discovered it (or do it manually) and I wanted to create a new repository on it. Unfortunately it came with the following error:

Server error: 'The backing device /dev/mapper/3500a075109146bee is not allowed to contain partitions'

Continue reading

Histogram endpoint date values to ‘human readable’ date format

Let’s get an endpoint_value for a hight-balanced histogram which represents a date:

select table_name, column_name, endpoint_number, endpoint_value, endpoint_actual_value
from dba_tab_histograms where table_name='BSLN_BASELINES' and column_name='LAST_COMPUTE_DATE'
order by endpoint_number;

Endpoint values for dates will be shown as followed: ‘2457594.23701157’.

The number in front of the dot is the day’s since ’01 JAN -4712′, this is retrievable with the Julian format element ‘J’:

select to_char(to_timestamp('2457594', 'J'), 'DD MON YYYY') endpoint_actual_value from dual; -- 24 JUL 2016
select to_char(to_timestamp('1', 'J'), 'DD MON SYYYY') endpoint_actual_value from dual; -- 01 JAN -4712
select to_char(to_timestamp('5373484', 'J'), 'DD MON YYYY') endpoint_actual_value from dual; -- 31 DEC 9999

The number after the dot is the faction of the day: .5 is noon, 0.25 is six ‘o clock in the morning:

select to_char(trunc(sysdate) + 0.25, 'HH24:MI:SS') endpoint_actual_value from dual; -- 06:00:00
select to_char(trunc(sysdate) + 0.23701157, 'HH24:MI:SS') endpoint_actual_value from dual; -- 05:41:18
select to_char(trunc(sysdate) + 0.87305213, 'HH24:MI:SS') endpoint_actual_value from dual; -- 20:57:12

Continue reading

ORA-28040: No matching authentication protocol – SQLNET.ALLOWED_LOGON_VERSION_SERVER

Ah, an old client is trying to connect to Oracle 12c (presumably)… ;)
Also “ORA-03134: Connections to this server version are no longer supported” can occur.

This actually implies that the authentication protocols use between client and server do not match. In Oracle 12c, the authentication protocol uses SHA-2 encryption algorithm by default, where older clients use SHA-1. So when an older client is used with defaults, the server will not accept the connection.

The best option would be upgrading the client, but when older dll’s are used (like ojdbc14.jar and you can not change this), this is a problem. The database instance can be told to accept older clients and use the older SHA-1 encryption algorithm.

SQLNET.ALLOWED_LOGON_VERSION_SERVER

Continue reading

Oracle Grid 12c and Linux 7: PRVE-0421 : No entry exists in /etc/fstab for mounting /dev/shm

When you run Oracle Grid 12c ‘cluvfy stage -pre crsinst’ on Linux 7 (Oracle Enterprise Linux 7.2 in my case) you will get the following error:

Starting check for /dev/shm mounted as temporary file system ...

ERROR:

PRVE-0421 : No entry exists in /etc/fstab for mounting /dev/shm
PRVE-0421 : No entry exists in /etc/fstab for mounting /dev/shm
PRVE-0421 : No entry exists in /etc/fstab for mounting /dev/shm
PRVE-0421 : No entry exists in /etc/fstab for mounting /dev/shm

Check for /dev/shm mounted as temporary file system failed

When you do a ‘df -h’ you will see that ‘/dev/shm’ actually is mounted:

Filesystem Size Used Avail Use% Mounted on
devtmpfs 24G 0 24G 0% /dev
tmpfs    24G 0 24G 0% /dev/shm

This is due to a bug (Doc ID 2065603.1) which comes from the case that the cluvfy check does not check the actual mount of the file system, but looks for the persistence line of this mount in ‘/etc/fstab’ like it did in Linux 6. In Linux 7 you get this tmpfs mount by default.

Continue reading

Add a larger disk and remove smaller disk from LVM, online!

Goal: To add new larger disk to LVM, move data and remove old smaller one

When using Oracle VM in my work, a disk can be enlarged in Oracle VM manager for a virtual machine. But because of an internal loop-mount on the XEN hypervisor level, the disk-file which is presented to the virtual machine as a disk, is not able to ‘pass on’ it’s resize information to the OS. This requires an unmount and mount of the disk-file on hypervisor level, or in simple words: a reboot of the virtual machine… yikes! Continue reading

Measuring MREADTIM system statistic, alternative way

[Why this post: GATHER_SYSTEM_STATS does not gather MREADTIM information from Direct Path Reads]

Oracle can be tuned in a lot of parts and places. One of these is when Oracle is going to choose between reading an index or doing a full table scan.

In this blog I’m not going into depth about all this, but one of the ‘parameters’ here is setting the MREADTIM system statistic to a ‘real life’ value. This value will tell Oracle how fast reading multiple blocks from disk is with all the overhead in between. How many multiple blocks is, is defined by the multi block read count (MBRC) setting. Together with SREADTIM, IOSEEKTIM and MBRC this will have influence in the execution path Oracle will choose.

Continue reading

When using SCAN, is LOAD_BALANCE still necessary?

Short answer, for balancing to the SCAN listeners from a single client, yes it does (a little).

When you look at a connection string with only a single SCAN ‘host’ there, it seems logical that the LOAD_BALANCE option is unnecessary, but  the Oracle client will replace (expand) this with an ADDRESS_LIST, containing the IP addresses it gets from the DNS server. It seems this order can not be trusted to be random. The DNS client can cache this until the TTL expires and/or the DNS server might give them in the order configured and not do this is a round-robin fashion (Round-robin DNS). Nothing will guarantee it will be returned randomly. It might look random when you do a nslookup of the SCAN address, but tracing the Oracle client it shows not to be.

“There is no standard procedure for deciding which address will be used by the requesting application, a few resolvers attempt to re-order the list to give priority to numerically “closer” networks. Some desktop clients do try alternate addresses after a connection timeout of 30–45 seconds.”

Furthermore (in 11.2), the LOAD_BALANCE option is only on by default in the DESCRIPTION_LIST, not the ADDRESS_LIST: Local Naming Parameters (tnsnames.ora).

Continue reading