Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Announcing SLOB 2.5 for Download at Github.

This is just a quick blog post to announce that SLOB 2.5 is now available for downloading at: https://github.com/therealkevinc/SLOB_distribution/tree/SLOB_2.5.0.

There is an important bug fix in this release that corrects redo logging payload generation when testing with non-zero slob.conf->UPDATE_PCT.  I recommend downloading and using this release accordingly.  The bug is described in the release notes.

A special thanks to Maris Elsins for finding, blogging and reporting the bug.

If you adopt this release there is no need to reload SLOB (via setup.sh). Data loaded with SLOB 2.4 is compatible with SLOB 2.5. Simply deploy the tar archive and bring over your slob.conf and you’re ready to test with SLOB 2.5.

How to add a reserve node to an existing 2+0 #Exasol Cluster

After having installed a 2+0 Cluster in Hyper-V, now let’s expand that to a 2+1 Cluster.

Add the node as a VM first

Add another VM in Hyper-V with the same attributes as the existing two data nodes n11 and n12 and name it n13:

  • 2500 MB memory, not dynamically extending
  • Legacy network adapter, connected to edu-cluster-interconnect
  • Network adapter, connected to edu-public-network
  • Boot order with legacy network adapter first
  • Two hard disk of type VHDX with max. size 100 GB

Give it a static MAC for the Legacy Network Adapter 08:00:27:58:03:21 and a static MAC for normal Network Adapter 08:00:27:71:27:26 and make sure they do not conflict with other existing MAC addresses in your environment.

Copy an existing node in EXAoperation

Go to the nodes branch and click on the link under n11:

https://uhesse.files.wordpress.com/2019/09/addnode1.png?w=150&h=66 150w, https://uhesse.files.wordpress.com/2019/09/addnode1.png?w=300&h=133 300w, https://uhesse.files.wordpress.com/2019/09/addnode1.png?w=768&h=340 768w, https://uhesse.files.wordpress.com/2019/09/addnode1.png 930w" sizes="(max-width: 620px) 100vw, 620px" />

On the nodes detail page, click on Copy:

https://uhesse.files.wordpress.com/2019/09/addnode2.png?w=141&h=150 141w, https://uhesse.files.wordpress.com/2019/09/addnode2.png?w=282&h=300 282w, https://uhesse.files.wordpress.com/2019/09/addnode2.png 677w" sizes="(max-width: 620px) 100vw, 620px" />

Enter 13 as the node numbers and change the MAC addresses as listed above, then click on Copy Node:

https://uhesse.files.wordpress.com/2019/09/addnode3.png?w=150 150w, https://uhesse.files.wordpress.com/2019/09/addnode3.png?w=300 300w" sizes="(max-width: 387px) 100vw, 387px" />

Click on the n0013(13) link and check the disk configuration of the new node. It should look like this:

https://uhesse.files.wordpress.com/2019/09/addnode4.png?w=150&h=23 150w, https://uhesse.files.wordpress.com/2019/09/addnode4.png?w=300&h=46 300w, https://uhesse.files.wordpress.com/2019/09/addnode4.png?w=768&h=118 768w, https://uhesse.files.wordpress.com/2019/09/addnode4.png 967w" sizes="(max-width: 620px) 100vw, 620px" />

Don’t forget to set the install flag

On the nodes branch, tick the checkbox of node n13 and Execute the action Set install flag for it:

https://uhesse.files.wordpress.com/2019/09/addnode5.png?w=150&h=65 150w, https://uhesse.files.wordpress.com/2019/09/addnode5.png?w=300&h=131 300w, https://uhesse.files.wordpress.com/2019/09/addnode5.png?w=768&h=335 768w, https://uhesse.files.wordpress.com/2019/09/addnode5.png 907w" sizes="(max-width: 620px) 100vw, 620px" />

The state changes to Unknown To install. Now power on n13 in Hyper-V.

After a while the logservice should display the new node getting installed:

https://uhesse.files.wordpress.com/2019/09/addnode6.png?w=150&h=46 150w, https://uhesse.files.wordpress.com/2019/09/addnode6.png?w=300&h=92 300w, https://uhesse.files.wordpress.com/2019/09/addnode6.png?w=768&h=235 768w, https://uhesse.files.wordpress.com/2019/09/addnode6.png?w=1024&h=314 1024w, https://uhesse.files.wordpress.com/2019/09/addnode6.png 1135w" sizes="(max-width: 620px) 100vw, 620px" />

This can be time consuming (took me more than 30 Minutes on my notebook ) but in the end, it lists: Boot process finished after x seconds. When you refresh the nodes branch then, the state of the node n13 changes to Running To Install.

Now tick the checkbox of n13 again and Execute the action Set active flag:

https://uhesse.files.wordpress.com/2019/09/addnode7.png?w=150&h=82 150w, https://uhesse.files.wordpress.com/2019/09/addnode7.png?w=300&h=164 300w, https://uhesse.files.wordpress.com/2019/09/addnode7.png 715w" sizes="(max-width: 620px) 100vw, 620px" />

The state changes to Running Active for n13, same as for the other nodes. Notice that you can’t tell from this page if a node is an active node or a reserve node.

Add the disk capacity of the new node to the storage service

On the Storage branch, tick the checkbox for n13 and click on Add Unused Disks:

https://uhesse.files.wordpress.com/2019/09/addnode8.png?w=150&h=55 150w, https://uhesse.files.wordpress.com/2019/09/addnode8.png?w=300&h=109 300w, https://uhesse.files.wordpress.com/2019/09/addnode8.png?w=768&h=280 768w, https://uhesse.files.wordpress.com/2019/09/addnode8.png?w=1024&h=373 1024w, https://uhesse.files.wordpress.com/2019/09/addnode8.png 1035w" sizes="(max-width: 620px) 100vw, 620px" />

Add the new node as reserve node to the running database

On the database detail pages, click Edit and add n13 as a Reserve node, then click Apply:

https://uhesse.files.wordpress.com/2019/09/addnode9.png?w=73 73w, https://uhesse.files.wordpress.com/2019/09/addnode9.png?w=145 145w" sizes="(max-width: 503px) 100vw, 503px" />

The database should look like this now:

https://uhesse.files.wordpress.com/2019/09/addnode10.png?w=150&h=31 150w, https://uhesse.files.wordpress.com/2019/09/addnode10.png?w=300&h=62 300w, https://uhesse.files.wordpress.com/2019/09/addnode10.png?w=768&h=160 768w, https://uhesse.files.wordpress.com/2019/09/addnode10.png 898w" sizes="(max-width: 620px) 100vw, 620px" />

Notice that you didn’t need to shutdown the database to add a reserve node to it.

Now your cluster has been extended to a 2+1 Cluster. Next article will explain how you can enlarge the database and make this a 3+0 Cluster.

Stay tuned </p />
</p></div>

    	  	<div class=

The Oracle Cloud Free Tier

The New “Always Free Service”s announced at OOW19

Every software vendor has also some free offers, to attract users, demonstrate their product, and support advocacy. What is free at Oracle? Today, the target is about the products which help to attract developers. We have the Oracle XE database that can be installed everywhere for free, with some limits on the capacity, but mostly every features. There are the developer tools that ease the use of the database, like SQL Developer. But what about Cloud?

Cloud free trials and promotions

You may have tested the 30-days free trial, and find it not so easy as it is for only one month, with an e-mail address, phone number, and credit card information that you cannot reuse. The limit on credit is not a problem as they are burned slowlier than in paid subscription (good to test many features, not good to evaluate the real price). As an ACE Director, I have access to longer trials. Actually, those trials are just promotions: you subscribe like for a paid account but are not be charged.

view-source:https://myservices.us.oraclecloud.com/mycloud/signup?language=en&sourceType=:ow:o:p:feb:0916FreePageBannerButton&intcmp=:ow:o:p:feb:0916FreePageBannerButton

As far as I know, there are 5 types of promotions.

  • “Free Trial” : $300 / 30 days
  • “Developer” : $500 / 30 days
  • “Student” : $5000 / 365 days
  • “Startup” : $100000 / 365 days
  • “Educator” : $25000 / 365 days

Where did I get this information from? It is just a guess when looking at the trial sign-up form source code which contains a JavaScript “promoMap”

Where did I get to this sign-up page? I just clicked on “Start for free” in the new Oracle Cloud Free Tier that was just announced by Larry Elison at Oracle Open World 2019. And that’s the purpose of this blog post.

The 30-days “Free Trial” is the one available from the Oracle website (the annoying pop-up that you get even when reading Oracle blogs). The “Developer” one can be available for Hands-On Labs. The “Student” is for Oracle University customers, the “Educator” is for the Oracle University instructors. The “Student” is also the one we can get through the ACED program. The “Startup” one has higher limits (like 20 OCPU instead of 6 in the other promotions)

Oracle Cloud Free Tier

Here it is, an extension of the current free trial (300$ on mostly all services, up to 8 instances and 5TB, for 30 days) where, in addition to this free trial, some services are offered free for un unlimited time.

https://www.oracle.com/cloud/free/

Oracle Cloud Free Tier

It is an extension. You still need to create a trial account (with a new e-mail, phone number, credit card) but beyond the trial you will continue to have access to some free service, forever.

What is free?

The unlimited free tier lets you create at most 2 database services and 2 compute services.

2 database services

Those are the common autonomous services: ATP serverless (for OLTP) and ADW (for datawarehouse). They come with many tools for administration, development and reporting: APEX (low code rapid application development), SQL Developer (Web version), Zepplin notebooks (through Oracle Machine Learning),…

Each database service is limited to 1 OCPU and can go up to 20 GB.

2 compute services

Each VM is limited to 1/8th of OCPU and 1 GB RAM.

It includes the Block storage (100GB) for 2 volumes (to be associated to the 2 VMs), Object Storage (10GB), Archive storage (10GB), and one load balancer (10 Mbps).

How free and unlimited?

First, you must sign-up for the 30-days trial, where you have to provide credit card information.

But you will not be billed.

What you do with the 30-days trial can be upgraded later to a paid subscription. Or not, and you still keep the free tier.

You need to provide a unique e-mail, phone, and credit card. You need to access the free service at least every 3 months or it can be removed. Note that nothing prevents you to run production on it (except the limits of course).

More info on the “Always Free Cloud Services” in the Universal Credits document:

Here is what I get after the subscription:

Updatable Join Views

Here’s a quick “how to”.

If you want to update a column in table A with a column value from table B, then there’s a simple way to check if the required result can be achieved through an updatable join view.

Step 1: write a query that joins table A to table B and reports the rows in table A that you want to update, with the value from table B that should be used to update them, e.g.


select  a.rowid, a.col1, b.col2 
from    
        tableA a,
        tableB b
where
        a.status = 'Needs Update'
and     b.colX   = a.colX
and     b.colY   = a.colY
and     b.colZ   = a.colZ
/

Step 2: If there is a uniqueness constraint (or suitable index) on table B (the table from which you are copying a value) that enforces the restriction that there should be at most one row in B for any combination of the join columns (colX, colY, colZ) then you can take this query, and turn it into an inline-view in an update statement:


update (
        select a.rowid, a.col1, b.col2 
        from    
                tableA a,
                tableB b
        where
                a.status = 'Needs Update'
        and     b.colX   = a.colX
        and     b.colY   = a.colY
        and     b.colZ   = a.colZ
)  v
set     v.col1 = v.col2
/

If there is nothing enforcing the uniqueness of (colX, colY, colZ) this statement will result in Oracle raising error ORA-01779 “cannot modify a column which maps to a non key-preserved table”. This error will appear even if there are currently no actual duplicates in table B that could cause a problem.

Footnote

This example ignores the extra bit of mess that is needed to deal with the case where B rows are supposed to match A rows when the columns in the join predicates can be null; but that just means your original query will probably have to include some predicates like (b.colX = a.colX or (a.colX is null and b.colX is null)) or make use of the sys_op_map_nonnull() function.

 

Little sleeps

A peripheral question in a recent comment (made in response to me asking whether a loop had been written with a sleep time of 1/100th or 1/1000th of a second) asked “How do you sleep for 1/1000th of a second in pure PL/SQL?”

The answer starts with “How pure is pure ?” Here’s a “pure” PL/SQL solution that “cheats” by calling one of the routines in Oracle’s built-in Java library:


create or replace procedure milli_sleep( i_milliseconds in number) 
as 
        language java
        name 'java.lang.Thread.sleep(long)';
/


create or replace procedure nano_sleep( i_milliseconds in number, i_nanoseconds in number)
as
        language java
        name 'java.lang.Thread.sleep(long, int)';
/

prompt  Milli sleep
prompt  ===========
execute milli_sleep(18)

prompt  Nano sleep
prompt  ==========
execute  nano_sleep(0,999999)


The “nano-second” component of the nano_sleep() procedure is restricted to the ranage 0 – 999999. In both cases the “milli-second” component has to be positive.

Whether your machine is good at handling sleeps of less than 1/100th of a second is another question, of course.

Update – due to popular demand

If you want to find out what else is available in the database you can query view all_java_methods searching by partial name (which is very slow) for something you think might exist, for example:

SQL> select owner, name , method_name from all_java_methods where upper(method_name) like '%MILLI%'

OWNER           NAME                                     METHOD_NAME
--------------- ---------------------------------------- ----------------------------------------
SYS             java/util/concurrent/TimeUnit$4          toMillis
SYS             java/util/concurrent/TimeUnit$5          toMillis
SYS             java/util/concurrent/TimeUnit$6          toMillis
SYS             java/util/concurrent/TimeUnit$7          toMillis
SYS             java/util/concurrent/TimeUnit            toMillis
SYS             java/lang/System                         currentTimeMillis
SYS             javax/swing/ProgressMonitor              setMillisToDecideToPopup
SYS             javax/swing/ProgressMonitor              getMillisToDecideToPopup

There’s a lot more than the few listed above – but I just wanted to pick up currentTimeMillis. If you spot something that looks interesting the easiest next step is probably to do a google search with (for example): Oracle java.lang.system currenttimemillis (alternatively you could just keep a permanent link to Oracle’s manual pages for the Java and serarch them. In my case this link was high on the list of google hits, giving me the following method description:


static long 	currentTimeMillis​() 	Returns the current time in milliseconds.

Conveniently this is easy to embed in pl/sql (be careful with case sensitivity):


create or replace function milli_time return number
as
        language java
        name 'java.lang.System.currentTimeMillis() return long';
/

execute dbms_output.put_line(milli_time)
execute dbms_lock.sleep(1)
execute dbms_output.put_line(milli_time)

----------------

SQL> @ java_procs

Function created.

1568719117725

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

1568719118734

PL/SQL procedure successfully completed.

You now have a PL/SQL function that will return the number of millisecond since 1st January 1970.

Nologging

Bobby Durrett recently published a note about estimating the volume of non-logged blocks written by an instance with the aim of getting some idea of the extra redo that would be generated if a database were switched to “force logging”.

Since my most recent blog notes have included various extracts and summaries from the symbolic dumps of redo logs it occurred to me that another strategy for generating the same information would be to dump the redo generated by Oracle when it wanted to log some information about non-logged blocks. This may sound like a contradiction, of course, but it’s the difference between data and meta-data: if Oracle wants to write data blocks to disc without logging their contents it needs to write a note into the redo log saying “there is no log of the contents of these blocks”.

In terms of redo op codes this is done through “layer 19”, the set of op codes relating to direct path loads, with op code 19.2 being the specific “invalidate range” one that we are (probably)interested in.

So here’s a little demo of extracting the information we need:

rem
rem     Script:         nologging_writes.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Sep 2019
rem
rem     Last tested 
rem             12.2.0.1
rem

column c_scn new_value m_scn_1
select to_char(current_scn,'999999999999999999999999') c_scn from v$database;

create table t1 nologging
as
select  * 
from    all_objects
where   rownum <= 10000
/

column c_scn new_value m_scn_2
select to_char(current_scn,'999999999999999999999999') c_scn from v$database;

alter session set tracefile_identifier = 'TABLE';
alter system dump redo scn min &m_scn_1 scn max &m_scn_2 layer 19;


create index t1_i1
on t1(object_name, owner, object_id)
pctfree 80
nologging
/

column c_scn new_value m_scn_3
select to_char(current_scn,'999999999999999999999999') c_scn from v$database;

alter session set tracefile_identifier = 'INDEX';
alter system dump redo scn min &m_scn_2 scn max &m_scn_3 layer 19;


insert /*+ append */ into t1
select * from t1
/

column c_scn new_value m_scn_4
select to_char(current_scn,'999999999999999999999999') c_scn from v$database;

alter session set tracefile_identifier = 'APPEND';
alter system dump redo scn min &m_scn_3 scn max &m_scn_4 layer 19;


I’ve executed a “create table nologging”, a “create index nologging”, then an “insert /*+ append */” into the nologging table. I’ve captured the current SCN before and after each call, added an individual identifier to the tracefile name for each call, then dumped the redo between each pair of SCNs, restricting the dump to layer 19. (I could have been more restrictive and said “layer 19 opcode 2”, but there is an opcode 19.4 which might also be relevant – though I don’t know when it might appear.)

Here’s the list of trace files I generated, plus a couple extra that appeared around the same time:

 ls -ltr *.trc | tail -6
-rw-r----- 1 oracle oinstall 361355 Sep 12 19:44 orcl12c_ora_23630.trc
-rw-r----- 1 oracle oinstall   5208 Sep 12 19:44 orcl12c_ora_23630_TABLE.trc
-rw-r----- 1 oracle oinstall  27434 Sep 12 19:44 orcl12c_ora_23630_INDEX.trc
-rw-r----- 1 oracle oinstall   2528 Sep 12 19:44 orcl12c_ora_23630_APPEND.trc
-rw-r----- 1 oracle oinstall 162633 Sep 12 19:45 orcl12c_mmon_3042.trc
-rw-r----- 1 oracle oinstall 162478 Sep 12 19:45 orcl12c_gen0_2989.trc


And having identified the trace files we can now extract the block invalidation records (I’ve inserted blank lines to separate the results from the three separate files):

grep OP orcl12c_ora_23630_*.trc

orcl12c_ora_23630_APPEND.trc:CHANGE #1 INVLD CON_ID:3 AFN:22 DBA:0x058001bd BLKS:0x0043 OBJ:125947 SCN:0x00000b860da6e1a5 SEQ:1 OP:19.2 ENC:0 FLG:0x0000
orcl12c_ora_23630_APPEND.trc:CHANGE #1 INVLD CON_ID:3 AFN:22 DBA:0x05800482 BLKS:0x006e OBJ:125947 SCN:0x00000b860da6e1a5 SEQ:1 OP:19.2 ENC:0 FLG:0x0000

orcl12c_ora_23630_INDEX.trc:CHANGE #1 INVLD CON_ID:3 AFN:22 DBA:0x058000c4 BLKS:0x0004 OBJ:125948 SCN:0x00000b860da6e162 SEQ:1 OP:19.2 ENC:0 FLG:0x0000
orcl12c_ora_23630_INDEX.trc:CHANGE #1 INVLD CON_ID:3 AFN:22 DBA:0x058000c8 BLKS:0x0004 OBJ:125948 SCN:0x00000b860da6e162 SEQ:1 OP:19.2 ENC:0 FLG:0x0000
...
...     70 lines deleted
...
orcl12c_ora_23630_INDEX.trc:CHANGE #1 INVLD CON_ID:3 AFN:22 DBA:0x05800424 BLKS:0x0004 OBJ:125948 SCN:0x00000b860da6e181 SEQ:1 OP:19.2 ENC:0 FLG:0x0000
orcl12c_ora_23630_INDEX.trc:CHANGE #1 INVLD CON_ID:3 AFN:22 DBA:0x05800428 BLKS:0x0004 OBJ:125948 SCN:0x00000b860da6e181 SEQ:1 OP:19.2 ENC:0 FLG:0x0000
orcl12c_ora_23630_INDEX.trc:CHANGE #1 MEDIA RECOVERY MARKER CON_ID:3 SCN:0x0000000000000000 SEQ:0 OP:18.3 ENC:0 FLG:0x0000

orcl12c_ora_23630_TABLE.trc:CHANGE #1 INVLD CON_ID:3 AFN:22 DBA:0x05800103 BLKS:0x000d OBJ:125947 SCN:0x00000b860da6e13e SEQ:1 OP:19.2 ENC:0 FLG:0x0000
orcl12c_ora_23630_TABLE.trc:CHANGE #1 INVLD CON_ID:3 AFN:22 DBA:0x05800111 BLKS:0x000f OBJ:125947 SCN:0x00000b860da6e140 SEQ:1 OP:19.2 ENC:0 FLG:0x0000
orcl12c_ora_23630_TABLE.trc:CHANGE #1 INVLD CON_ID:3 AFN:22 DBA:0x05800121 BLKS:0x0007 OBJ:125947 SCN:0x00000b860da6e141 SEQ:1 OP:19.2 ENC:0 FLG:0x0000
orcl12c_ora_23630_TABLE.trc:CHANGE #1 INVLD CON_ID:3 AFN:22 DBA:0x05800268 BLKS:0x0008 OBJ:125947 SCN:0x00000b860da6e142 SEQ:1 OP:19.2 ENC:0 FLG:0x0000
orcl12c_ora_23630_TABLE.trc:CHANGE #1 INVLD CON_ID:3 AFN:22 DBA:0x05800271 BLKS:0x000f OBJ:125947 SCN:0x00000b860da6e144 SEQ:1 OP:19.2 ENC:0 FLG:0x0000
orcl12c_ora_23630_TABLE.trc:CHANGE #1 INVLD CON_ID:3 AFN:22 DBA:0x05800081 BLKS:0x000f OBJ:125947 SCN:0x00000b860da6e146 SEQ:1 OP:19.2 ENC:0 FLG:0x0000
orcl12c_ora_23630_TABLE.trc:CHANGE #1 INVLD CON_ID:3 AFN:22 DBA:0x05800091 BLKS:0x000f OBJ:125947 SCN:0x00000b860da6e148 SEQ:1 OP:19.2 ENC:0 FLG:0x0000
orcl12c_ora_23630_TABLE.trc:CHANGE #1 INVLD CON_ID:3 AFN:22 DBA:0x058000a1 BLKS:0x000f OBJ:125947 SCN:0x00000b860da6e14a SEQ:1 OP:19.2 ENC:0 FLG:0x0000
orcl12c_ora_23630_TABLE.trc:CHANGE #1 INVLD CON_ID:3 AFN:22 DBA:0x058000b1 BLKS:0x000f OBJ:125947 SCN:0x00000b860da6e14c SEQ:1 OP:19.2 ENC:0 FLG:0x0000
orcl12c_ora_23630_TABLE.trc:CHANGE #1 INVLD CON_ID:3 AFN:22 DBA:0x05800182 BLKS:0x003b OBJ:125947 SCN:0x00000b860da6e14c SEQ:1 OP:19.2 ENC:0 FLG:0x0000

Each line records the number of blocks (BLKS:) allocated and, as you can see, the APPEND trace shows much larger allocations than the TABLE trace (except for the last one) because the tablespace is locally managed with system allocated extents, and the first few invalidation records for the table creation are in the initial 8 block (64KB) extents; by the time we get to the last few blocks of the initial table creation we’ve just allocated the first 128 block (1MB) extent, which is why the last invalidation record for the table can cover so many more blocks than than the first few.

It is interesting to note, though, that the invalidation record for the INDEX trace are all small, typically 4 blocks, sometimes 3, even when we’ve obviously got to a point where we’re allocating from extents of 128 blocks.

I believe that somewhere I have a note explaining that the invalidation records always identified batches of 5 blocks in older versions of Oracle – but that may simply have been a consequence of the way that freelist management used to work (allocating 5 blocks at a time from the segment to the master freelist).

Although we could simply list all the invalidation records and sum the block counts manually we could be a little smarter with our code, summing them with awk, for example.

grep -n "OP:19.2" orcl12c_ora_23630_TABLE.trc |
     sed 's/.*BLKS://' |
     sed 's/ .*$//'  |
     awk '{m = m + strtonum($0) ; printf("%8i %8i \n",strtonum($0),m)}'
 
      13       13 
      15       28 
       7       35 
       8       43 
      15       58 
      15       73 
      15       88 
      15      103 
      15      118 
      59      177 

It’s left as an exercise to the Unix enthusiast to work out how to take the base tracefile name extract all the sets of data, cater for the odd 18.3 records (whose presence I didn’t request), report any lines for 19.x rows other than 19.2 and sum BLKS separately by TABLE, INDEX, and APPEND.

Once you’ve summed the number of blocks across all the invalidation records (and assuming you’re using the standard 8KB block size) the increease in the volume of redo generated if you alter the database to force logging will be (8KB + a little bit) * number of blocks.  The “little bit” will be close to 44 bytes.

If you’ve set your database up to use multiple block sizes you’ll have to aggregate the invalidation recrords by the AFN (absolute file number) entry and check which files use which block size and multiply up accordingly. And if you’re using a pluggable database (as I was) inside a container database you might also want to filter the redo dump by CON_ID.

If you do set the database to force logging and repeat the search for layer 19 in the redo  you’ll find that each individual data block written using a direct path write generates its own redo record, which will have length “data block size + 44” bytes and hold a single change vector of type 19.1 (Direct Loader block redo entry).

Footnote

It’s worth mentioning, that the dump of redo will go back into the archived redo logs in order to cover the entire range requested by the SCN man/max valeus; so it would be perfectly feasible (though possibly a little time and I/O consuming) to run the report across a full 24 hour window.

Dead Connection Detection (DCD) and the Oracle database

Dead Connection Detection is a useful feature of the Oracle database: it allows for the cleanup of “dead” sessions so they don’t linger around consuming memory and other system resources. The idea is simple: if the database detects that a client process is no longer connected to its server process, it cleans up. This can happen in many ways, in most cases this kind of problem is triggered by an end user.

A dead connection shouldn’t be confused with idle connections: an idle connection still maintains the network link between client and server process, except that there is no activity. Idle connections aren’t maintained/controlled via DCD, there are other tools in the database handling such cases.

As a by product, DCD can also help with overly eager firewalls forcibly removing seemingly idle network connections. I found the following posts and the references therein very useful:

With Oracle 12c Oracle changed the way DCD works by no longer relying on its network layer but rather pushing the functionality into the TCP stack on platforms that support it. This change in behaviour is also explained in an Oracle white paper from 2014.

For the first part of this post I set sqlnet.expire_time to 1 as per a MOS note I found, your value is probably different. The parameter is documented in the Net*8 reference, please use it to work out what the best value is for you. As others have pointed out, this parameter has to go into the RDBMS home, more specifically $ORACLE_HOME/network/admin/sqlnet.ora. I am using Oracle 19.4 on Oracle Linux 7.7 for this blog post. I have seen the same behaviour in 12.1.0.2 as well in my lab.

Background

In addition to the proven methods of checking whether TCP_KEEPALIVE is enabled for a given session I wanted to show another one. Using the ss(8) utility it is possible to show socket options. I also tried lsof but on my system I couldn’t get it to print the options:

SQL> select spid from v$process where addr = (select paddr from v$session where username = 'MARTIN');

SPID
------------------------
13656

SQL> exit 

...

[root@server2 ~]# lsof -nP -p 13656 -T f
lsof: unsupported TCP/TPI info selection: f
lsof 4.87

Although the man-page for lsof reads:

       -T [t]   controls the reporting of some TCP/TPI information,  also  reported  by  net‐
                stat(1),  following  the network addresses.  In normal output the information
                appears in parentheses, each item except TCP or TPI state name identified  by
                a keyword, followed by `=', separated from others by a single space:

                     
                     QR=
                     QS=
                     SO=
                     SS=
                     TF=
                     WR=
                     WW=

                Not all values are reported for all UNIX dialects.  Items values (when avail‐
                able) are reported after the item name and '='.

                When the field output mode is in effect  (See  OUTPUT  FOR  OTHER  PROGRAMS.)
                each item appears as a field with a `T' leading character.

                -T with no following key characters disables TCP/TPI information reporting.

                -T with following characters selects the reporting of specific TCP/TPI infor‐
                mation:

                     f    selects reporting of socket options,
                          states and values, and TCP flags and
                          values.

So let’s try something else: ss(8) – another utility to investigate sockets

Revealing socket options

I have used ss(8) in the past when I didn’t have netstat available, which is more and more common now that netstat is deprecated and its designated successor is ss :)

As far as I know you can’t limit ss to show information just for a PID, I use grep to limit the output. The output is in fact very wide, which is why this might not look pretty on the blog depending on whether the renderer decides to wrap output or not.

[root@server2 ~]# ss -nop | egrep 'NetidState|13656'
NetidState Recv-Q Send-Q                                    Local Address:Port                                      Peer Address:Port                                                                                                           
tcp  ESTAB 0      0                               [::ffff:192.168.100.22]:1521                           [::ffff:192.168.100.21]:25780                           users:(("oracle_13656_si",pid=13656,fd=18)) timer:(keepalive,4.412ms,0)        
[root@server2 ~]# 

I used the following options:

  • -n for “Do not try to resolve service names”
  • -o for “Show timer information” and finally
  • -p to “Show process using socket”.

The main option here is -o. As per the man page:

       -o, --options
              Show timer information. For tcp protocol, the output format is:

              timer:(,,)

              
                     the name of the timer, there are five kind of timer names:

                     on: means one of these timers: tcp retrans timer, tcp early retrans timer and tail loss probe timer
                     keepalive: tcp keep alive timer
                     timewait: timewait stage timer
                     persist: zero window probe timer
                     unknown: none of the above timers

              
                     how long time the timer will expire

              
                     how many times the retran occurs

With a little shell loop I can show how that timer is decrementing:

[root@server2 ~]# for i in $(seq 1 5); do ss -nop | grep 13656; sleep 1 ; done
tcp  ESTAB 0      0                               [::ffff:192.168.100.22]:1521                           [::ffff:192.168.100.21]:25780                           users:(("oracle_13656_si",pid=13656,fd=18)) timer:(keepalive,20sec,0)          
tcp  ESTAB 0      0                               [::ffff:192.168.100.22]:1521                           [::ffff:192.168.100.21]:25780                           users:(("oracle_13656_si",pid=13656,fd=18)) timer:(keepalive,19sec,0)          
tcp  ESTAB 0      0                               [::ffff:192.168.100.22]:1521                           [::ffff:192.168.100.21]:25780                           users:(("oracle_13656_si",pid=13656,fd=18)) timer:(keepalive,18sec,0)          
tcp  ESTAB 0      0                               [::ffff:192.168.100.22]:1521                           [::ffff:192.168.100.21]:25780                           users:(("oracle_13656_si",pid=13656,fd=18)) timer:(keepalive,17sec,0)          
tcp  ESTAB 0      0                               [::ffff:192.168.100.22]:1521                           [::ffff:192.168.100.21]:25780                           users:(("oracle_13656_si",pid=13656,fd=18)) timer:(keepalive,16sec,0)          
[root@server2 ~]# 

Summary

Using the ss utility it is possible to check if a keepalive timer is implemented as a means to support DCD with 12.1 and later releases. Invoking ss(8) hasn’t caused any problems on my system, but as with every such tool you need to ensure it’s safe to use before attempting to look at an important system.

UKOUG 2019

At the UKOUG TechFest 2019 event in Brighton this year I’ll be chairing the CBO Panel session on Monday 2nd December at noon. The panellists will be Maria Colgan, Nigel Bayliss, Christian Antognini and Richard Foote.

It will be possible to propose questions on the day – written or spoken, but this if you have a question that you’d like to give the panellists a little warning about then you can:

Tweeting is particularly good (and I’ll copy any tweeted questions to this note) because 280 characters is long enough to be an interesting question but short enough for people to identify the key point.

 

Installing an #Exasol 2+0 Cluster on Hyper-V

After having installed Hyper-V, an ISO file with the Exasol software needs to be downloaded. The 2+0 cluster consisting of two data nodes and one license server needs a host machine with at least 8 GB memory and 60 GB free disk space. I do it on my Windows 10 notebook with 16 GB memory. This is for educational purposes of course and not suitable for production use.

2+0 means two active nodes and no reserve node. A reserve node can be added later, expanding the environment to a 2+1 cluster.

We’ll start adding two virtual switches to be able to connect the network adapter of the VMs to them later.

Create Hyper-V Switches for Private and Public Network of the cluster

Click the Virtual Switch Manager in Hyper-V Manager:

https://uhesse.files.wordpress.com/2019/09/hyper1.png?w=1240&h=604 1240w, https://uhesse.files.wordpress.com/2019/09/hyper1.png?w=150&h=73 150w, https://uhesse.files.wordpress.com/2019/09/hyper1.png?w=300&h=146 300w, https://uhesse.files.wordpress.com/2019/09/hyper1.png?w=768&h=374 768w, https://uhesse.files.wordpress.com/2019/09/hyper1.png?w=1024&h=498 1024w" sizes="(max-width: 620px) 100vw, 620px" />

 

Select Private and click Create Virtual Switch:

https://uhesse.files.wordpress.com/2019/09/hyper2.png?w=1240&h=476 1240w, https://uhesse.files.wordpress.com/2019/09/hyper2.png?w=150&h=58 150w, https://uhesse.files.wordpress.com/2019/09/hyper2.png?w=300&h=115 300w, https://uhesse.files.wordpress.com/2019/09/hyper2.png?w=768&h=295 768w, https://uhesse.files.wordpress.com/2019/09/hyper2.png?w=1024&h=393 1024w" sizes="(max-width: 620px) 100vw, 620px" />

Name the Private switch edu-cluster-interconnect:

https://uhesse.files.wordpress.com/2019/09/hyper3.png?w=1240&h=588 1240w, https://uhesse.files.wordpress.com/2019/09/hyper3.png?w=150&h=71 150w, https://uhesse.files.wordpress.com/2019/09/hyper3.png?w=300&h=142 300w, https://uhesse.files.wordpress.com/2019/09/hyper3.png?w=768&h=364 768w, https://uhesse.files.wordpress.com/2019/09/hyper3.png?w=1024&h=485 1024w" sizes="(max-width: 620px) 100vw, 620px" />

Create another Virtual Switch of type Internal and name it edu-public-network:

https://uhesse.files.wordpress.com/2019/09/hyper4.png?w=1238&h=588 1238w, https://uhesse.files.wordpress.com/2019/09/hyper4.png?w=150&h=71 150w, https://uhesse.files.wordpress.com/2019/09/hyper4.png?w=300&h=142 300w, https://uhesse.files.wordpress.com/2019/09/hyper4.png?w=768&h=365 768w, https://uhesse.files.wordpress.com/2019/09/hyper4.png?w=1024&h=486 1024w" sizes="(max-width: 620px) 100vw, 620px" />

Go to Network and Sharing Center and click on the edu-public-network there, then on Properties:

https://uhesse.files.wordpress.com/2019/09/hyper5.png?w=1240&h=832 1240w, https://uhesse.files.wordpress.com/2019/09/hyper5.png?w=150&h=101 150w, https://uhesse.files.wordpress.com/2019/09/hyper5.png?w=300&h=201 300w, https://uhesse.files.wordpress.com/2019/09/hyper5.png?w=768&h=516 768w, https://uhesse.files.wordpress.com/2019/09/hyper5.png?w=1024&h=688 1024w" sizes="(max-width: 620px) 100vw, 620px" />

Then select TCP/IPv4 and click again Properties:

https://uhesse.files.wordpress.com/2019/09/hyper6.png?w=150&h=150 150w, https://uhesse.files.wordpress.com/2019/09/hyper6.png?w=300&h=298 300w, https://uhesse.files.wordpress.com/2019/09/hyper6.png?w=768&h=762 768w, https://uhesse.files.wordpress.com/2019/09/hyper6.png 898w" sizes="(max-width: 620px) 100vw, 620px" />

Select Use the following IP address and type in 192.168.43.1 and the Subnet mask 255.255.255.0

https://uhesse.files.wordpress.com/2019/09/hyper7.png?w=131&h=150 131w, https://uhesse.files.wordpress.com/2019/09/hyper7.png?w=262&h=300 262w, https://uhesse.files.wordpress.com/2019/09/hyper7.png?w=768&h=878 768w, https://uhesse.files.wordpress.com/2019/09/hyper7.png 982w" sizes="(max-width: 620px) 100vw, 620px" />

The IP above can be different as long as it doesn’t conflict with other IPs used in your virtual environment. I find it convenient to let it end with the digit 1, though.

Create the License Server

In Hyper-V Manager, click New then Virtual Machine:

https://uhesse.files.wordpress.com/2019/09/hyper8.png?w=1974&h=436 1974w, https://uhesse.files.wordpress.com/2019/09/hyper8.png?w=150&h=33 150w, https://uhesse.files.wordpress.com/2019/09/hyper8.png?w=300&h=66 300w, https://uhesse.files.wordpress.com/2019/09/hyper8.png?w=768&h=169 768w, https://uhesse.files.wordpress.com/2019/09/hyper8.png?w=1024&h=226 1024w" sizes="(max-width: 987px) 100vw, 987px" />

Do not use Quick Create.

Give it the name ls and click Next:

https://uhesse.files.wordpress.com/2019/09/hyper9.png?w=1240&h=740 1240w, https://uhesse.files.wordpress.com/2019/09/hyper9.png?w=150&h=90 150w, https://uhesse.files.wordpress.com/2019/09/hyper9.png?w=300&h=179 300w, https://uhesse.files.wordpress.com/2019/09/hyper9.png?w=768&h=458 768w, https://uhesse.files.wordpress.com/2019/09/hyper9.png?w=1024&h=611 1024w" sizes="(max-width: 620px) 100vw, 620px" />

It must be Generation 1:

https://uhesse.files.wordpress.com/2019/09/hyper10.png?w=1580&h=436 1580w, https://uhesse.files.wordpress.com/2019/09/hyper10.png?w=150&h=41 150w, https://uhesse.files.wordpress.com/2019/09/hyper10.png?w=300&h=83 300w, https://uhesse.files.wordpress.com/2019/09/hyper10.png?w=768&h=212 768w, https://uhesse.files.wordpress.com/2019/09/hyper10.png?w=1024&h=283 1024w" sizes="(max-width: 790px) 100vw, 790px" />

Give it 1500 MB memory if your host has >= 16 GB memory, else give it 1100 MB. De-select Use Dynamic Memory:

https://uhesse.files.wordpress.com/2019/09/hyper11.png?w=1556&h=364 1556w, https://uhesse.files.wordpress.com/2019/09/hyper11.png?w=150&h=35 150w, https://uhesse.files.wordpress.com/2019/09/hyper11.png?w=300&h=70 300w, https://uhesse.files.wordpress.com/2019/09/hyper11.png?w=768&h=180 768w, https://uhesse.files.wordpress.com/2019/09/hyper11.png?w=1024&h=240 1024w" sizes="(max-width: 778px) 100vw, 778px" />

Do not yet connect it to a network:

https://uhesse.files.wordpress.com/2019/09/hyper12.png?w=1536&h=272 1536w, https://uhesse.files.wordpress.com/2019/09/hyper12.png?w=150&h=27 150w, https://uhesse.files.wordpress.com/2019/09/hyper12.png?w=300&h=53 300w, https://uhesse.files.wordpress.com/2019/09/hyper12.png?w=1024&h=181 1024w" sizes="(max-width: 768px) 100vw, 768px" />

Give it a disk of 100 GB maximum size:

https://uhesse.files.wordpress.com/2019/09/hyper13.png?w=1518&h=406 1518w, https://uhesse.files.wordpress.com/2019/09/hyper13.png?w=150&h=40 150w, https://uhesse.files.wordpress.com/2019/09/hyper13.png?w=300&h=80 300w, https://uhesse.files.wordpress.com/2019/09/hyper13.png?w=768&h=205 768w, https://uhesse.files.wordpress.com/2019/09/hyper13.png?w=1024&h=273 1024w" sizes="(max-width: 759px) 100vw, 759px" />

Select Install operating system later:

https://uhesse.files.wordpress.com/2019/09/hyper14.png?w=1462&h=540 1462w, https://uhesse.files.wordpress.com/2019/09/hyper14.png?w=150&h=55 150w, https://uhesse.files.wordpress.com/2019/09/hyper14.png?w=300&h=111 300w, https://uhesse.files.wordpress.com/2019/09/hyper14.png?w=768&h=284 768w, https://uhesse.files.wordpress.com/2019/09/hyper14.png?w=1024&h=378 1024w" sizes="(max-width: 731px) 100vw, 731px" />

After creating ls, click on Settings:

https://uhesse.files.wordpress.com/2019/09/hyper15.png?w=1240&h=910 1240w, https://uhesse.files.wordpress.com/2019/09/hyper15.png?w=150&h=110 150w, https://uhesse.files.wordpress.com/2019/09/hyper15.png?w=300&h=220 300w, https://uhesse.files.wordpress.com/2019/09/hyper15.png?w=768&h=564 768w, https://uhesse.files.wordpress.com/2019/09/hyper15.png?w=1024&h=751 1024w" sizes="(max-width: 620px) 100vw, 620px" />

Add a Legacy Network Adapter:

https://uhesse.files.wordpress.com/2019/09/hyper16.png?w=1240&h=488 1240w, https://uhesse.files.wordpress.com/2019/09/hyper16.png?w=150&h=59 150w, https://uhesse.files.wordpress.com/2019/09/hyper16.png?w=300&h=118 300w, https://uhesse.files.wordpress.com/2019/09/hyper16.png?w=768&h=302 768w, https://uhesse.files.wordpress.com/2019/09/hyper16.png?w=1024&h=402 1024w" sizes="(max-width: 620px) 100vw, 620px" />

We need Legacy Adapters in the VMs because later, the data nodes need to boot from network and that requires Legacy Adapters to be used. Connect it to the Virtual Switch edu-cluster-interconnect and click Apply.

Connect a normal (not legacy) Network Adapter to the switch edu-public-network:

https://uhesse.files.wordpress.com/2019/09/hyper17.png?w=1240&h=1104 1240w, https://uhesse.files.wordpress.com/2019/09/hyper17.png?w=150&h=134 150w, https://uhesse.files.wordpress.com/2019/09/hyper17.png?w=300&h=267 300w, https://uhesse.files.wordpress.com/2019/09/hyper17.png?w=768&h=684 768w, https://uhesse.files.wordpress.com/2019/09/hyper17.png?w=1024&h=912 1024w" sizes="(max-width: 620px) 100vw, 620px" />

Confirm the boot order is CD first for ls:

https://uhesse.files.wordpress.com/2019/09/hyper18.png?w=1240&h=472 1240w, https://uhesse.files.wordpress.com/2019/09/hyper18.png?w=150&h=57 150w, https://uhesse.files.wordpress.com/2019/09/hyper18.png?w=300&h=114 300w, https://uhesse.files.wordpress.com/2019/09/hyper18.png?w=768&h=292 768w, https://uhesse.files.wordpress.com/2019/09/hyper18.png?w=1024&h=389 1024w" sizes="(max-width: 620px) 100vw, 620px" />

Now power on the virtual machine just created and configured. It should come up with this screen and you type in install

https://uhesse.files.wordpress.com/2019/09/hyper19-1.png?w=150&h=79 150w, https://uhesse.files.wordpress.com/2019/09/hyper19-1.png?w=300&h=158 300w, https://uhesse.files.wordpress.com/2019/09/hyper19-1.png 713w" sizes="(max-width: 620px) 100vw, 620px" />

There’s no need for device encryption on an educational system. Tab to OK and press return:

https://uhesse.files.wordpress.com/2019/09/hyper20-1.png?w=150&h=76 150w, https://uhesse.files.wordpress.com/2019/09/hyper20-1.png?w=300&h=152 300w, https://uhesse.files.wordpress.com/2019/09/hyper20-1.png 711w" sizes="(max-width: 620px) 100vw, 620px" />

Confirm the LS number as 10. Tab to OK and press return:

https://uhesse.files.wordpress.com/2019/09/hyper21.png?w=150&h=74 150w, https://uhesse.files.wordpress.com/2019/09/hyper21.png?w=300&h=149 300w, https://uhesse.files.wordpress.com/2019/09/hyper21.png 711w" sizes="(max-width: 620px) 100vw, 620px" />

Confirm to install EXAClusterOS on first boot. Tab to OK and press return:

https://uhesse.files.wordpress.com/2019/09/hyper22.png?w=150&h=74 150w, https://uhesse.files.wordpress.com/2019/09/hyper22.png?w=300&h=149 300w, https://uhesse.files.wordpress.com/2019/09/hyper22.png 715w" sizes="(max-width: 620px) 100vw, 620px" />

Enter exasol12 as the password for the maintenance user:

https://uhesse.files.wordpress.com/2019/09/hyper23.png?w=150&h=74 150w, https://uhesse.files.wordpress.com/2019/09/hyper23.png?w=300&h=149 300w, https://uhesse.files.wordpress.com/2019/09/hyper23.png 715w" sizes="(max-width: 620px) 100vw, 620px" />

Check with ipconfig (using the cmd shell) for the Ethernet adapter vEthernet (edu-public-network): Then replace the last digit 1 with 10 for the IP of the public interface. It should be 192.168.43.10

https://uhesse.files.wordpress.com/2019/09/hyper24.png?w=1240&h=282 1240w, https://uhesse.files.wordpress.com/2019/09/hyper24.png?w=150&h=34 150w, https://uhesse.files.wordpress.com/2019/09/hyper24.png?w=300&h=68 300w, https://uhesse.files.wordpress.com/2019/09/hyper24.png?w=768&h=175 768w, https://uhesse.files.wordpress.com/2019/09/hyper24.png?w=1024&h=233 1024w" sizes="(max-width: 620px) 100vw, 620px" />

https://uhesse.files.wordpress.com/2019/09/hyper25.png?w=150 150w, https://uhesse.files.wordpress.com/2019/09/hyper25.png?w=300 300w" sizes="(max-width: 485px) 100vw, 485px" />

Check the network configuration comparing it to the tab Networking in Hyper-V Manager

https://uhesse.files.wordpress.com/2019/09/hyper26.png?w=150 150w, https://uhesse.files.wordpress.com/2019/09/hyper26.png?w=300 300w" sizes="(max-width: 511px) 100vw, 511px" />

Private must match edu-cluster-interconect and Public must match edu-public-network

https://uhesse.files.wordpress.com/2019/09/hyper27.png?w=150&h=27 150w, https://uhesse.files.wordpress.com/2019/09/hyper27.png?w=300&h=54 300w, https://uhesse.files.wordpress.com/2019/09/hyper27.png 684w" sizes="(max-width: 620px) 100vw, 620px" />

Click on Configure in the Advanced Network configuration to change it accordingly otherwise:

https://uhesse.files.wordpress.com/2019/09/hyper28.png?w=150 150w, https://uhesse.files.wordpress.com/2019/09/hyper28.png?w=300 300w" sizes="(max-width: 533px) 100vw, 533px" />

Confirm the (corrected) network configuration. Tab to OK and press return:

https://uhesse.files.wordpress.com/2019/09/hyper29.png?w=150 150w, https://uhesse.files.wordpress.com/2019/09/hyper29.png?w=300 300w" sizes="(max-width: 476px) 100vw, 476px" />

This triggers the installation of various components on the License Server and takes some time, depending on the hardware resources of your machine. The critical resource is CPU, so try to avoid running anything else on your machine that consumes CPU during the install.

If the License Server reboots during that process and presents the choice to install, update or boot from local disk, type in local. You may then remove the virtual CD from the CD drive of the VM.

When prompted for login on the node n0010, you may login as the OS user maintenance with the password exasol12 :

https://uhesse.files.wordpress.com/2019/09/hyper30.png?w=150 150w, https://uhesse.files.wordpress.com/2019/09/hyper30.png?w=300 300w" sizes="(max-width: 498px) 100vw, 498px" />

Refresh until it shows EXAoperation status: Online

This may take several Minutes depending on your hardware.

Connect to EXAoperation

Now open a Chrome browser (preferred) with the https URL of your License Server’s IP:

https://192.168.43.10

https://uhesse.files.wordpress.com/2019/09/hyper31.png?w=150&h=103 150w, https://uhesse.files.wordpress.com/2019/09/hyper31.png?w=300&h=207 300w, https://uhesse.files.wordpress.com/2019/09/hyper31.png 630w" sizes="(max-width: 620px) 100vw, 620px" />

User Name is admin, Password is admin

Check Network Configuration

The network configuration should look like this:

https://uhesse.files.wordpress.com/2019/09/hyper32.png?w=150&h=143 150w, https://uhesse.files.wordpress.com/2019/09/hyper32.png?w=300&h=286 300w, https://uhesse.files.wordpress.com/2019/09/hyper32.png 756w" sizes="(max-width: 620px) 100vw, 620px" />

Click on Edit and modify it to the above settings otherwise. You may change the Cluster Name to something else. Notice that the value None is not allowed for NTP Servers and you have to remove it. Then click Apply.

Add Monitoring Service

To be able to follow the node’s installation, it’s useful to add a monitoring service now. Click on Services at the left and then on the Add button:

https://uhesse.files.wordpress.com/2019/09/monitoringservice.png?w=150 150w, https://uhesse.files.wordpress.com/2019/09/monitoringservice.png?w=300 300w" sizes="(max-width: 560px) 100vw, 560px" />

Select all cluster services with the Log Priority Information to get the most verbose output, then click Add.

Add first data node

In Hyper-V Manager, create another VM with the name n11:

https://uhesse.files.wordpress.com/2019/09/hyper33.png?w=150&h=76 150w, https://uhesse.files.wordpress.com/2019/09/hyper33.png?w=300&h=151 300w, https://uhesse.files.wordpress.com/2019/09/hyper33.png 707w" sizes="(max-width: 620px) 100vw, 620px" />

Specify Generation 1 on next page.

Give it 2500 MB memory if your host has >=16 GB  and 2200 MB otherwise and de-select dynamic memory:

https://uhesse.files.wordpress.com/2019/09/hyper34.png?w=150&h=57 150w, https://uhesse.files.wordpress.com/2019/09/hyper34.png?w=300&h=114 300w, https://uhesse.files.wordpress.com/2019/09/hyper34.png 697w" sizes="(max-width: 620px) 100vw, 620px" />

Give it a 100 GB max. sized disk and name it n11sda:

https://uhesse.files.wordpress.com/2019/09/hyper35.png?w=150&h=53 150w, https://uhesse.files.wordpress.com/2019/09/hyper35.png?w=300&h=107 300w, https://uhesse.files.wordpress.com/2019/09/hyper35.png 682w" sizes="(max-width: 620px) 100vw, 620px" />

Select install OS later and finish the creation dialog. Then click on settings. Add a legacy network adapter to n11 and connect it to the switch edu-cluster-interconnect:

https://uhesse.files.wordpress.com/2019/09/hyper36.png?w=150&h=58 150w, https://uhesse.files.wordpress.com/2019/09/hyper36.png?w=300&h=117 300w, https://uhesse.files.wordpress.com/2019/09/hyper36.png 718w" sizes="(max-width: 620px) 100vw, 620px" />

Connect another normal network adapter to the switch edu-public-network.

Configure the boot order for n11 with legacy network adapter first:

https://uhesse.files.wordpress.com/2019/09/hyper37.png?w=150&h=56 150w, https://uhesse.files.wordpress.com/2019/09/hyper37.png?w=300&h=112 300w, https://uhesse.files.wordpress.com/2019/09/hyper37.png 697w" sizes="(max-width: 620px) 100vw, 620px" />

Add a second hard drive to n11:

https://uhesse.files.wordpress.com/2019/09/hyper38.png?w=150&h=67 150w, https://uhesse.files.wordpress.com/2019/09/hyper38.png?w=300&h=134 300w, https://uhesse.files.wordpress.com/2019/09/hyper38.png 701w" sizes="(max-width: 620px) 100vw, 620px" />

Select New Virtual Harddisk of type VHDX, dynamically expanding up to 100 GB max. and name it n11sdb:

https://uhesse.files.wordpress.com/2019/09/hyper39.png?w=150 150w, https://uhesse.files.wordpress.com/2019/09/hyper39.png?w=300 300w" sizes="(max-width: 481px) 100vw, 481px" />

We need to know the MAC addresses of the data nodes upfront to enter them in EXAoperation, so the data nodes can connect to the license server to get booted over network. The MAC addresses below can be different as long as you know they do not conflict with other machines in your virtual environment,

Select Advanced Features of the legacy network adapter and configure it with a static MAC address of 08:00:27:A9:D1:22

https://uhesse.files.wordpress.com/2019/09/hyper40.png?w=150&h=92 150w, https://uhesse.files.wordpress.com/2019/09/hyper40.png?w=300&h=184 300w, https://uhesse.files.wordpress.com/2019/09/hyper40.png 651w" sizes="(max-width: 620px) 100vw, 620px" />

Select Advanced Features of the normal network adapter and configure a static MAC address of  08:00:27:92:10:6B

https://uhesse.files.wordpress.com/2019/09/hyper41.png?w=150&h=108 150w, https://uhesse.files.wordpress.com/2019/09/hyper41.png?w=300&h=217 300w, https://uhesse.files.wordpress.com/2019/09/hyper41.png 663w" sizes="(max-width: 620px) 100vw, 620px" />

Do not yet power on the new VM. Instead, click on Add in the EXAoperation Nodes screen:

https://uhesse.files.wordpress.com/2019/09/hyper42.png?w=150&h=89 150w, https://uhesse.files.wordpress.com/2019/09/hyper42.png?w=300&h=177 300w" sizes="(max-width: 620px) 100vw, 620px" />

Configure the added node:

https://uhesse.files.wordpress.com/2019/09/hyper43.png?w=91 91w, https://uhesse.files.wordpress.com/2019/09/hyper43.png?w=182 182w" sizes="(max-width: 497px) 100vw, 497px" />

Click on Add, then click on the new node to configure it further:

https://uhesse.files.wordpress.com/2019/09/hyper44.png?w=150&h=70 150w, https://uhesse.files.wordpress.com/2019/09/hyper44.png?w=300&h=141 300w, https://uhesse.files.wordpress.com/2019/09/hyper44.png 726w" sizes="(max-width: 620px) 100vw, 620px" />

Scroll down on the node’s detail page and click on Disks:

https://uhesse.files.wordpress.com/2019/09/hyper45.png?w=150 150w, https://uhesse.files.wordpress.com/2019/09/hyper45.png?w=300 300w" sizes="(max-width: 535px) 100vw, 535px" />

Click on Edit on the EXACluster Disk Information for node n0011 screen:

https://uhesse.files.wordpress.com/2019/09/hyper46.png?w=150&h=33 150w, https://uhesse.files.wordpress.com/2019/09/hyper46.png?w=300&h=66 300w" sizes="(max-width: 620px) 100vw, 620px" />

Add the device /dev/sda with the other values like on the picture:

https://uhesse.files.wordpress.com/2019/09/hyper47.png?w=150&h=112 150w, https://uhesse.files.wordpress.com/2019/09/hyper47.png?w=300&h=224 300w" sizes="(max-width: 620px) 100vw, 620px" />

Do this accordingly for the other three storage partitions. Make sure to reduce the size to 30 GB for the Data partition and assign /dev/sdb for the Storage partition. It should look like this in the end:

https://uhesse.files.wordpress.com/2019/09/hyper48.png?w=150&h=32 150w, https://uhesse.files.wordpress.com/2019/09/hyper48.png?w=300&h=63 300w" sizes="(max-width: 620px) 100vw, 620px" />

Now power on the n11 VM. It should come up booting from network and start installing. Check that the install starts normally without errors:

https://uhesse.files.wordpress.com/2019/09/hyper49.png?w=150&h=69 150w, https://uhesse.files.wordpress.com/2019/09/hyper49.png?w=300&h=139 300w, https://uhesse.files.wordpress.com/2019/09/hyper49.png?w=768&h=355 768w, https://uhesse.files.wordpress.com/2019/09/hyper49.png 1000w" sizes="(max-width: 620px) 100vw, 620px" />

If you see errors in the log at this point, turn off the VM and check the configuration as above described, especially the disk layout. After having corrected it, power the VM on again. Make sure the state of the node shows as Unknown to install (Not Unknown Active) here. Execute the action Set install flag for the node to change it to the state Unknown to install otherwise.

While the install of n11 is ongoing, add the second data node as a Hyper-V VM just like the n11 VM before. Give it the name n12 with all the same properties as n11, except these MAC addresses:

Legacy network adapter (Private Network) 08:00:27:AA:61:8D

Normal network adapter (Public Network)  08:00:27:A3:AB:46

After having created n12 in Hyper-V, go to EXAoperation and click on the n11 node:

https://uhesse.files.wordpress.com/2019/09/hyper50.png?w=150&h=70 150w, https://uhesse.files.wordpress.com/2019/09/hyper50.png?w=300&h=141 300w, https://uhesse.files.wordpress.com/2019/09/hyper50.png 726w" sizes="(max-width: 620px) 100vw, 620px" />

Scroll down on the node’s detail page and click on Copy Node.

Change the node numbers to 12 and the MAC addresses as above listed:

https://uhesse.files.wordpress.com/2019/09/hyper51.png?w=150 150w, https://uhesse.files.wordpress.com/2019/09/hyper51.png?w=300 300w" sizes="(max-width: 393px) 100vw, 393px" />

Click on Copy Node again to finish the creation of n12.

Click on the Nodes screen:

https://uhesse.files.wordpress.com/2019/09/hyper52.png?w=150&h=53 150w, https://uhesse.files.wordpress.com/2019/09/hyper52.png?w=300&h=106 300w, https://uhesse.files.wordpress.com/2019/09/hyper52.png?w=768&h=271 768w, https://uhesse.files.wordpress.com/2019/09/hyper52.png 918w" sizes="(max-width: 620px) 100vw, 620px" />

The install of n11 is still ongoing and n12 is now ready to start in Hyper-V. Power it on.

It should come up and install like n11. The install ends with the nodes prompting for login and a message like Boot process finished after xxx seconds in the logservice.

The install consumes much CPU resources. Try to avoid running anything else on the machine while it is ongoing. It may take quite a long while to complete, depending on your local machine power.

It looks like this in the end:

https://uhesse.files.wordpress.com/2019/09/hyper53.png?w=150&h=44 150w, https://uhesse.files.wordpress.com/2019/09/hyper53.png?w=300&h=88 300w, https://uhesse.files.wordpress.com/2019/09/hyper53.png?w=768&h=225 768w, https://uhesse.files.wordpress.com/2019/09/hyper53.png 901w" sizes="(max-width: 620px) 100vw, 620px" />

Now select the two nodes and execute the action Set active flag for them:

https://uhesse.files.wordpress.com/2019/09/hyper54.png?w=150&h=68 150w, https://uhesse.files.wordpress.com/2019/09/hyper54.png?w=300&h=135 300w, https://uhesse.files.wordpress.com/2019/09/hyper54.png 715w" sizes="(max-width: 620px) 100vw, 620px" />

Their state should now change to Running Active.

On the EXAStorage screen, click on Startup Storage Service:

https://uhesse.files.wordpress.com/2019/09/hyper55.png?w=150&h=52 150w, https://uhesse.files.wordpress.com/2019/09/hyper55.png?w=300&h=105 300w, https://uhesse.files.wordpress.com/2019/09/hyper55.png 682w" sizes="(max-width: 620px) 100vw, 620px" />

Now select the two data nodes and click on Add Unused Disks:

https://uhesse.files.wordpress.com/2019/09/hyper56.png?w=150 150w, https://uhesse.files.wordpress.com/2019/09/hyper56.png?w=300 300w" sizes="(max-width: 509px) 100vw, 509px" />

Add a data volume

On the EXAoperation Storage screen, click on Add Volume:

https://uhesse.files.wordpress.com/2019/09/hyper57.png?w=150&h=39 150w, https://uhesse.files.wordpress.com/2019/09/hyper57.png?w=300&h=78 300w, https://uhesse.files.wordpress.com/2019/09/hyper57.png?w=768&h=201 768w, https://uhesse.files.wordpress.com/2019/09/hyper57.png 815w" sizes="(max-width: 620px) 100vw, 620px" />

Specify a Redundancy of 2, add user admin to Allowed Users, Volume Size should be 8 GB, add n11 and n12 to the Nodes List, Number of Master Nodes is 2 and Disk is d03_storage:

https://uhesse.files.wordpress.com/2019/09/hyper58.png?w=107&h=150 107w, https://uhesse.files.wordpress.com/2019/09/hyper58.png?w=214&h=300 214w" sizes="(max-width: 620px) 100vw, 620px" />

The new volume should then look like this:

https://uhesse.files.wordpress.com/2019/09/hyper59.png?w=150&h=31 150w, https://uhesse.files.wordpress.com/2019/09/hyper59.png?w=300&h=63 300w, https://uhesse.files.wordpress.com/2019/09/hyper59.png 740w" sizes="(max-width: 620px) 100vw, 620px" />

Create a database

On the EXASolution screen, click on Add:

https://uhesse.files.wordpress.com/2019/09/hyper60.png?w=150&h=91 150w, https://uhesse.files.wordpress.com/2019/09/hyper60.png?w=300&h=183 300w" sizes="(max-width: 620px) 100vw, 620px" />

On the Add Database screen, specify 2 Active Nodes, assign the two data nodes and click Add

https://uhesse.files.wordpress.com/2019/09/hyper61.png?w=120&h=150 120w, https://uhesse.files.wordpress.com/2019/09/hyper61.png?w=240&h=300 240w, https://uhesse.files.wordpress.com/2019/09/hyper61.png 649w" sizes="(max-width: 620px) 100vw, 620px" />

The new database shows a Status of Not created. Click on the database link:

https://uhesse.files.wordpress.com/2019/09/hyper62.png?w=150&h=34 150w, https://uhesse.files.wordpress.com/2019/09/hyper62.png?w=300&h=68 300w, https://uhesse.files.wordpress.com/2019/09/hyper62.png?w=768&h=175 768w, https://uhesse.files.wordpress.com/2019/09/hyper62.png 804w" sizes="(max-width: 620px) 100vw, 620px" />

Select the Action Create and click Submit:

https://uhesse.files.wordpress.com/2019/09/hyper63.png?w=150&h=31 150w, https://uhesse.files.wordpress.com/2019/09/hyper63.png?w=300&h=61 300w, https://uhesse.files.wordpress.com/2019/09/hyper63.png?w=768&h=156 768w, https://uhesse.files.wordpress.com/2019/09/hyper63.png 849w" sizes="(max-width: 620px) 100vw, 620px" />

The Action should change to Startup, n11 and n12 appear as Selected. Click Submit again:

https://uhesse.files.wordpress.com/2019/09/hyper64.png?w=150&h=30 150w, https://uhesse.files.wordpress.com/2019/09/hyper64.png?w=300&h=59 300w, https://uhesse.files.wordpress.com/2019/09/hyper64.png?w=768&h=152 768w, https://uhesse.files.wordpress.com/2019/09/hyper64.png 894w" sizes="(max-width: 620px) 100vw, 620px" />

The State changes to Starting for n11 and n12. Click on EXASolution on the Services menu on the left:

The new database should now show as Online.

https://uhesse.files.wordpress.com/2019/09/hyper65.png?w=150&h=33 150w, https://uhesse.files.wordpress.com/2019/09/hyper65.png?w=300&h=65 300w, https://uhesse.files.wordpress.com/2019/09/hyper65.png?w=768&h=167 768w, https://uhesse.files.wordpress.com/2019/09/hyper65.png 844w" sizes="(max-width: 620px) 100vw, 620px" />

Congratulations for going the whole way, it was a bit long at times, wasn’t it? But now you have your own Exasol cluster sandbox on Hyper-V – have fun with it!

Going forward, I find it convenient to create Hyper-V checkpoints with the database and storage service shutdown and the nodes having the cluster services shutdown as well. That way I make sure nothing can change on the disks of the data nodes when I take the checkpoint. I can then just turn off the VMs and apply these checkpoints afterwards. Then starting the VMs saves me the time they would need to go through the normal boot process otherwise. My next article will explain how to add a reserve node to this cluster, bringing it from 2+0 to 2+1. Hope you find this mini-series useful </p />
</p></div>

    	  	<div class=