20 years… down the memory lane (and a complimentary news at the end!)

February 22nd, 2000.

I was 20 when I started working as Junior Database Administrator for the service provider SemaGroup, who was born as a branch of the dying Olivetti.

20 years ago, tomorrow! In 20 years, I went through quite a few professional changes. For the records, I would like to write down the main ones.

2000-2003: the years of Sema @ Infostrada

My main customer, for the few next years, was Infostrada, a Telco company who over the years has been merged with Wind Telecomunicazioni and finally acquired by Hutchinson. At that time, I was in a team responsible for the operations, monitoring and availability of all the DB systems of the customer (mostly Digital and HP-UX): Oracle 7.3, 8, 8i, 9i and a few Sybase ASE. We had to respond to all incidents 24/7, including Alert errors, tablespace metrics, etc. It was very demanding, it took me not that much to become confident with the main Oracle technologies. I had the chance to know many colleagues who helped me growing up both technically and personally.

2003-2010: many customers, big projects and new technologies

In 2003, my managers decided to move me back to the main Data center, where Sema Group was providing hosting and managed operations for several big Italian companies.

Different customers mean different technologies. I had to study Microsoft SQL Server (6.5, 7.0 and 2000 at that time), some DB2, and install the first production MySQL (4.0, although I was already playing with it since 3.23). I have also had to become very confident with middle-tier technologies: Oracle Application Server (9 and 10g), Tomcat, Jboss, Apache, but more than this, I have had full responsibility on big projects: Disaster Recovery implementations of big customer environments, data center moving, re-platform, and absolutely heterogeneous customer environments (Windows, Linux, AIX, HP, Compaq, Solaris… SAN, NAS, DAS, Fiber Channels, FCoE, Gigabit Ethernet… Networker, Tivoli, Data Protector… do a cartesian join of all of them and you get the idea.) Among the big customers, I have worked with Costa Crociere, Lavazza, Illy, Fiat, Banca d’Italia, Fila, AirLiquide, Credit Agricole, Carrefour, International Olympic Committee, Heineken, Enel, Banco Popolare S.C., Piaggio, Pagine Gialle, European Space Agency, several public administrations (Regions, Ministries, Cities…)… I am sure I miss many, as they were more than 100.

During these years, I had to deal with incredibly hard on-call rotations (several calls per night) and high customer expectations, who helped giving me confidence and “seniority”.

Over the years, SemaGroup has been acquired/merged a few times. It became Sema, SchlumbergerSema, Schlumberger, AtosOrigin, Engineering.IT, then Engineering.

2010-2012: the team lead experience @ Engineering

In 2010 I became team lead, at the beginning just of a team of 5, then gradually of the whole Engineering Managed Operations DBA team (23 including employees and external collaborators). Sadly, in Italy it is very uncommon to have a technical career: as soon as you are good enough, you have to switch to the management side (either completely or partially). Being team lead, I understood the difference between technical problems and people problems. With the increase of customers and projects, and with the many company acquisitions, we were understaffed, most of us underpaid: working days, nights and week-ends without the chance to recover properly. It took me just a few months to realize that that was the time to say goodbye to the IT market in Italy and seek a better opportunity, abroad.

2012-2018: Senior Consultant @ Trivadis

I still remember perfectly the first call I have got from Trivadis, the very next day after I applied for a position for Oracle Consultant. Four months later I moved to Switzerland: different country, different language and completely different working culture despite the very short distance from my home region (less than 200 KM!).

There I have had many important customers and cool projects: everything was quality-driven rather than quantity-driven. Being part of that company has been a pure joy for me: company culture, colleagues, side-activities, conferences… results!

I have learnt to be outgoing and communicative, and I have kept enriching my technical knowledge (mostly on Oracle and PostgreSQL).

2018-2020: The CERN experience

Once I have got the contract proposal from CERN, it was hard to say no. Awesome conditions (pension, holidays, healthcare, dress code, flexibility…) and a once-in-a-lifetime opportunity to join the geekiest place in the world.

Here I have been working exclusively with Oracle RAC on Linux. There are incredibly large databases (from a few TBs to 1 PB) and interesting technologies, but the vast majority of my time is dealing with legacy database schemas and methodologies. I am trying hard to improve things, but the inertia of such big projects does not allow me to innovate as fast as I would like. So what’s next?

2020- : Back to Trivadis

Last December Trivadis contacted me back and tried to convince me to go back… with success 🙂

Next April 1st I will start back in Trivadis as Principal Consultant. Initially, I will be working on a couple of critical projects: it will be very interesting! Other than that, I am excited to go back to the company (and colleagues) that I have loved so much!

To end this quite long post… I would like to paste here one of the quotes that well represents my feelings since I have moved in Switzerland…

“Why do you go away? So that you can come back. So that you can see the place you came from with new eyes and extra colors. And the people there see you differently too. Coming back to where you started is not the same as never leaving.”

– Terry Pratchett

Understand your Database through graphs

During the last months I have had to deal with highly consolidated databases, where there was no basic rule to achieve something maintainable. Over many years (some schemas are 30 years old) the number of schemas and their dependencies became a problem up to a point where basic operations like patching or upgrading were very complex to achieve.

In order to try to split these big databases and move towards Oracle Multitenant, I have created some graphs that helped me and my colleagues to understand the connections between schemas (grants) and databases (db links).

 

How  have I created the graphs?

I used Gephi , an open source software to generate graphs. Gephi is very powerful, I feel I have used just 1% of its capabilities.

How to create a graph, depends mostly on what you want to achieve and which data you have.

First, some basic terminology: Nodes are the “dots” in the graph, Edges are the lines connecting the dots. Both nodes and edges can have properties (e.g. edges have weight), but you might not need any.

Basic nodes and edges without properties

If you need just to show the dependencies between nodes, a basic edge list with source->target will be enough.

For example, you can have a edge list like this one: gephi_1_edges.csv

Open Gephi, go to New ProjectFile -> Import Spreadsheet, select the file. If you already have a workspace and you want to add the edges to the same workspace, select Append to existing workspace.

This will lead to something very basic:

In the Data Laboratory tab, you might want to copy the value of the ID column to the label column, so that they match:

Now you must care about two things:

First, re-arranging the nodes. With few nodes this is often not required, but when there are many, the default visualization is not satisfying. In the tab Overview , pane Layout there are a few algorithms you can choose. For big graphs I prefer Force Atlas. There are a few parameters to tune, especially  the attraction/repulsion strengths and the gravity. Speed is also crucial if you have many nodes. For this small example I put Repulsion Strength to 2000, Attraction Strength to 1. Clicking on Run starts the algorithm to rearrange the nodes, which with few edges will almost instantaneous (don’t forget to stop it afterwards).

Here is what I get:

Now that the nodes are in place, in the preview pane I can adjust the settings, like showing labels and changing colors. Also, in the Appearance pane I can change the scheme to have for example colors based on ranking.

In this example, I choose to color based on ranking (nodes with more edges are darker).

I also set the Preset Default Straight, Show labels (with smaller size) , proportional size.

Adding nodes properties

Importing edges from CSV gives only a dumb list of edges, without any information about nodes properties. Having properties set might be important in order to change how the graph is displayed.

By importing a node list containing the properties of each node , I can add important information. In this example file, I have columns Id, Label and Sex, that I will use to color the nodes differently: gephi_1_nodes.csv

In the appearance node, I have just selected to partition by sex with a meaningful color.

Using real metadata to understand schemas or dependencies…

I will take, as an example, the dependencies in a database between objects of type VIEW, MATERIALIZED VIEW and TABLE. The database has quite a usage of materialized views and understanding the relation is not always easy.

This is the query that interests me:

So I need the nodes, for that I need a UNION to get nodes from both sides of the dependency. The best tool to achieve this is SqlCl as it has the native CSV output format:

The edge list:

Using the very same procedure as above, it is easy to generate the graph.

I am interested in understanding what is TABLE, what is VIEW and what MATERIALIZED VIEW, so I partition the color by type. I also set the edge color to source so the edge will have the same color of the source node type.

I am also interested in highlighting which tables have more incoming dependencies, so I rank the node size by In-Degree.

 

In the graph:

  • All the red dots are MVIEWS
  • All the blue dots are VIEWS
  • All the black dots are TABLES
  • All the red lines are dependencies between a MVIEW and a  (TABLE|VIEW).
  • All the blue lines are dependencies between a VIEW and a  (TABLE|MVIEW).
  • The bigger the dots, the more incoming dependencies.

With the same approach I can get packages, grants, roles, db_links, client-server dependencies, etc. to better understand the infrastructure.

I hope you like it!

ORA-02002 and ORA-00942 while upgrading OWM to 19c

This is a quick post about a problem that we have had while upgrading a DB to 19c.

At 91% of the upgrade, the OWM (Workspace Manager) upgrade was failing with this error error:

Indeed, executing the statement was leading consistently to this problem:

and we have had this result:

So, resuming the autoupgrade job was not a solution.

The view definition is:

but the package wmsys.ltUtil is wrapped, so no chance to understand what was happening.

As a quick fix, we have recompiled the binaries with mixed auditing:

and put the audit_trail=DB in the upgrade pfile (was NONE in this specific case).

After that, restarted the DB in upgrade mode using the same pfile.

After that, the view was giving no errors anymore and we resumed the autoupgrade job.

This is an old troubleshooting method that I call “Database Administration by guess”: I am not sure about the real cause, but the workaround just worked fine for us.

It would be interesting to know if anyone of you have had the same problem, and what were the auditing parameters in your case…

Ludovico