Insights with MiniLM: Hands-On Text Embeddings for MLOps

From MOT Notes to Insights with MiniLM: A Practical Guide to Text Embeddings

 

Intro: I’m not a data scientist or statistician – I’m a DevOps engineer who got interested in ML through building CarHunch.
 
This post shares what I’ve learned about embeddings through that journey, hopefully presented in a way that other DevOps engineers and people interested in AI/ML can understand and experiment with.
 
The Jupyter notebook is a simplified version of techniques I use in CarHunch at a much larger scale, made quick and easy to run so you can see and the concepts in action.
 

 

Every year, millions of vehicles undergo MOT testing in the UK, generating a massive amount of free-text defect notes that could revolutionize how we understand vehicle maintenance patterns.

 

But there’s a catch – these notes are messy, inconsistent, and nearly impossible to analyze at scale using traditional methods.

 

Consider these real examples from MOT records:

 

“Nearside rear brake pipe corroded”
“Brake hose deteriorated”
“Brakes imbalanced across an axle”
“Headlamp aim too high”
“Exhaust leaking gases”

 

While these notes are invaluable for mechanics, they create a nightmare for data analysis. Every tester phrases things slightly differently, and traditional keyword searches miss the bigger picture. How do you find all brake-related issues when they’re described in dozens of different ways?

 

The answer lies in embeddings – a powerful technique that transforms unstructured text into structured, analyzable data.

 

Embeddings convert text into numeric vectors, placing similar meanings close together in high-dimensional space. With embeddings, “brake hose deteriorated” and “brake pipe corroded” become neighbors – even though the wording differs significantly. This opens up entirely new possibilities for analyzing text data at scale.

 

This post demonstrates a practical, hands-on approach using MiniLM to:

 

  • – Transform messy MOT defect notes into structured embeddings
  • – Cluster similar defects automatically using machine learning
  • – Run semantic search to find related issues by meaning, not just keywords
  • – Visualize the results to understand patterns in vehicle defects

 


Try the Interactive Demo

 

The demonstration is a Jupyter notebook that you can open directly in Google Colab – no setup required on your local machine.

 

Important Note about Google Colab: When you click the link below, you’ll be prompted to sign in to Google. This is completely normal and free – Google Colab requires a Google account to save your work and provide computational resources. Your data remains private, and you can always download your work or run it locally if you prefer.

 

Open the demo in Google Colab

 

Or if you’d rather you can view the repository and run it locally:

github.com/DonaldSimpson/mot_embeddings_demo

 


How It Works: From Text to Insights

 
The demo is comprised of three key steps, each building on the previous one:
 

Step 1: Text to Numbers

 

The MiniLM model (specifically “all-MiniLM-L6-v2”) converts each defect note into a 384-dimensional vector. Think of this as creating a unique “fingerprint” for each piece of text that captures its semantic meaning. Notes about similar issues will have similar fingerprints.

 

Step 2: Finding Patterns

 

K-means clustering automatically groups these fingerprints together. The algorithm discovers that “brake pipe corroded” and “brake hose deteriorated” belong in the same cluster, while “headlamp aim too high” forms its own group. You’ll see this visualized in a 2D scatter plot using PCA (Principal Component Analysis).
 

Step 3: Intelligent Search

 

Semantic search uses cosine similarity to find the most relevant notes for any query. When you search for “brake failure,” it doesn’t just look for those exact words – it finds notes that are semantically similar, even if they use completely different terminology.
 

The notebook demonstrates this with a carefully curated set of real MOT defect notes, including:

 

  • Brake-related issues (pipes, hoses, imbalance)
  • Lighting problems (headlamp aim, functionality)
  • Steering and suspension defects
  • Exhaust system issues
  • Tyre wear problems

 

Each example is designed to show how embeddings capture meaning beyond literal word matching.

 


Hands-On Experimentation: Make It Your Own

 

This isn’t just a static demonstration – it’s a tool for discovery. The notebook is designed for active exploration, and the best way to understand embeddings is to experiment with them yourself.

 

Here’s a roadmap for turning this demo into a more personal learning experience:

 

    1. Start with your own data  
      The most rewarding experiment is using your own MOT notes. Have you had an MOT recently? Try adding those defect notes to see how they cluster with the sample data. You might be surprised by the patterns that emerge.

      notes = [
          "Engine oil leak",
          "Headlight not working", 
          "Nearside front tyre bald",
          "Steering pulling to the left",
          "Brake discs worn and pitted",
          # Add your own notes here...
          "Your defect notes here",
          "More of your defect notes"
      ]

      Suggestion: Try adding notes from different vehicle types (cars, vans, motorcycles) to see if the clustering adapts to different contexts.

 

    1. Play with clustering granularity 
      This is where things can get really interesting. Change the number of clusters and watch how the groupings shift:

      # Try different values: 2, 3, 4, 5, 6...
      kmeans = KMeans(n_clusters=3, random_state=42)

      This uses scikit-learn’s KMeans implementation.

      Start with 3 clusters and gradually increase. You’ll see how the algorithm balances between creating too many small groups versus too few large ones. The visualization will show you exactly how your notes are being grouped – some results might surprise you!

    2.  

    3. Make your own queries 
      The semantic search feature is incredibly powerful. Try queries that test the model’s understanding:

      # Test the model's semantic understanding
      query = "tyre wear"           # Should find tyre-related issues
      query = "steering problem"    # Should find steering defects  
      query = "engine issue"        # Should find engine problems
      query = "safety concern"      # Should find safety-related defects

      Try abstract concepts like “safety concern” or “performance issue” to see how well the model understands context beyond literal word matching.

 

  1. Experiment with different models (for the curious) 
    If you want to see how different embedding models perform, try swapping out MiniLM:

    # Larger, potentially more accurate model 
    model = SentenceTransformer("multi-qa-mpnet-base-dot-v1")
     
    # Or try a model specifically trained for technical text
     
    model = SentenceTransformer("all-mpnet-base-v2")

    These are SentenceTransformer models from the Hugging Face model hub.

    Compare the results – do the clusters change? Are the search results more relevant? This is a great way to understand how model choice affects performance.

  2.  

  3. Scale up and discover patterns 
    Once you’re comfortable with the basics, try working with larger datasets. The DVLA MOT dataset contains millions of records, and you’ll start to see fascinating patterns emerge:

    • Which vehicle makes have the most brake-related failures?
    • Do certain types of defects cluster by geographic region?
    • How do defect patterns change over time?

    This is where embeddings really shine – they can reveal insights that would be impossible to find with traditional keyword searches.

 

Each of these modifications provides immediate feedback – you can see the results directly in the notebook, making it an ideal learning environment.


 

Real-World Applications: CarHunch

 

For CarHunch, I’ve been applying this same approach to millions of MOT records. Embeddings make it possible to:

 

  • Standardize messy defect notes into consistent categories
  • Compare your car’s defects with similar vehicles
  • Surface patterns across the UK fleet (e.g., which makes and models fail most often on brakes)

 

A Surprising ‘Discovery’: The Land Rover Defender Seatbelt issue

DEFENDER2.NET - View topic - Seatbelt catching on @#$!

Sometimes, the most interesting insights come from patterns you’d never expect to find. Take my own Land Rover (original) Defender 110 as an example. When I analyzed its MOT history alongside thousands of similar vehicles, I discovered something surprising:

 

seatbelt damage is the number 1 most common issue for Defenders – not the engine, suspension or rust problems you’d probably expect from a rugged old off-road vehicle!

 

This revelation only became apparent through the kind of clustering and semantic analysis we’re exploring in this notebook. Traditional keyword searches would have missed this pattern entirely, because MOT testers describe seatbelt issues in dozens of different ways:

 

“Seatbelt webbing frayed”
“Driver’s seatbelt damaged”
“Seatbelt retraction mechanism faulty”
“Belt webbing showing signs of wear”

 

But embeddings revealed the underlying pattern: all these different descriptions clustered together as the same fundamental issue.

 

Even more fascinating, the analysis showed this is a design quirk specific to Defenders; the front seatbelts naturally fall right in to the door jambs as there’s nowhere else for them to go (plus the tensioners are weak/slow), so when the doors are closed they get trapped, causing accelerated wear that doesn’t occur in most other vehicles.

 

The Bigger Picture: How This Could Transform Automotive Design

 

This Defender example hints at something much larger: embeddings could impact how car manufacturers identify design flaws and improve vehicle quality. Imagine if every manufacturer had access to this kind of analysis across their entire fleet:

  • Early Warning System: Spot recurring issues before they become widespread problems
  • Design Validation: Verify that design changes actually solve the problems they’re meant to address
  • Cost-Benefit Analysis: Quantify the real-world impact of design decisions on maintenance costs
  • Competitive Intelligence: Understand how your vehicles compare to competitors in terms of reliability

 

Traditional quality control relies on warranty claims and customer complaints – reactive data that comes too late. But MOT data is generated continuously, providing a real-time view of how vehicles perform in the wild. The challenge has always been extracting meaningful insights from the unstructured text that testers write.

 

This is exactly the kind of insight that would be impossible to discover without the semantic understanding that embeddings provide. You can explore this particular analysis yourself with CarHunch’s enhanced hunches feature, which uses the same techniques demonstrated in this notebook.

 

This example is just a small subset of what that larger platform does, showing how embeddings can transform unstructured text data into actionable insights that reveal patterns invisible to traditional analysis methods.

 


From Experimentation to Production

 

Once you’ve experimented with the notebook and understand how embeddings work, you might be wondering: “How do I turn this into a production system?” This is where the journey from data science experimentation to operational ML begins.

 

In my previous post, “MLOps for DevOps Engineers – MiniLM & MLflow demo”, I showed how to take these same embedding techniques and build them into a proper MLOps pipeline. That post covers:

 

  • Containerizing the embedding pipeline with Docker
  • Tracking experiments and model versions with MLflow
  • Automating the entire workflow with Makefiles
  • Building quality gates and reproducibility into the process

 

Think of it this way: this notebook is your playground for understanding embeddings, while the MLOps post shows you how to turn that playground into a production system. The same MiniLM model that powers this interactive demo is the foundation for the automated pipeline in the MLOps example.

 

For DevOps engineers, this represents a natural progression: start with hands-on experimentation to understand the concepts, then apply your existing automation and infrastructure skills to make it production-ready.

 


Key Takeaways

 

For DevOps and SRE engineers curious about machine learning, embeddings represent an excellent entry point:

 

  • No GPU required for basic experimentation
  • Easy to run locally or in cloud environments
  • Immediately useful for messy, real-world text data
  • Natural bridge to production MLOps workflows

 

Give the notebook a try, experiment with your own MOT notes, and discover what insights you can uncover. When you’re ready to take it further, the MLOps post will show you how to automate and scale these techniques.

 

Open the demo in Google Colab

 


 

Contains public sector information licensed under the Open Government Licence v3.0.

MLOps for DevOps Engineers – MiniLM & MLflow demo

MLOps for DevOps Engineers – MiniLM & MLflow pipeline demo

 

As a DevOps and SRE engineer, I’ve spent a lot of time building automated, reliable pipelines and cloud platforms. Over the last couple of years, I’ve been applying the same principles to machine learning (ML) and AI projects.

 

One of those projects is CarHunch, a vehicle insights platform I developed. CarHunch ingests and analyses MOT data at scale, using both traditional pipelines and applied AI. Building it taught me first-hand how DevOps practices map directly onto MLOps: versioning datasets and models, tracking experiments, and automating deployment workflows. It’a a new and exciting area but the core idea is very much the same, with some interesting new tools and concepts added.

 

To make those ideas more approachable for other DevOps engineers, I have put together a minimal, reproducible demo using MiniLM and MLflow.

 

You can find the full source code here:

github.com/DonaldSimpson/mlops_minilm_demo

 

The quick way: make run

The simplest way to try this demo is with the included Makefile; that way all you need is Docker installed

# clone the repo
git clone https://github.com/DonaldSimpson/mlops_minilm_demo.git

cd mlops_minilm_demo

# build and run everything (training + MLflow UI)
make run

 

That one ‘make run’ command will:

  • – Spin up a containerised environment
  • – Run the demo training script (using MiniLM embeddings + Logistic Regression)
  • – Start the MLflow tracking server and UI

 

Here’s a quick screngrab of it running in the console:

Once it’s up & running, open
http://localhost:5001
in your browser to explore logged experiments

 

What the demo shows

– MiniLM embeddings turn short MOT-style notes (e.g. “brakes worn”) into vectors

– A Logistic Regression classifier predicts pass/fail

– Parameters, metrics (accuracy), and the trained model are logged in MLflow

– You can inspect and compare runs in the MLflow UI – just like you’d review builds and artifacts in CI/CD

– Run detail; accuracy metrics and model artifact stored alongside parameters

 

Here are screenshots of the relevant areas from the MLFlow UI:











 

Why this matters for DevOps engineers

    • Familiar workflows: MLflow feels like Jenkins/GitHub Actions for models – every run is logged, reproducible, and auditable

 

    • Quality gates: just as builds pass/fail CI, models can be gated by accuracy thresholds before promotion

 

    • Reproducibility: datasets, parameters and artifacts are versioned and tied to each run

 

    • Scalability: the same demo pattern can scale to real workloads – this is a scaled down version of my local process

 

 

Other ways to run it

 

If you prefer, the repo includes alternatives:

 

    • Python venv: create a virtualenv, install requirements.txt, run train_light.py

 

    • Docker Compose: build and run services with docker-compose up --build

 

    • Make targets: make train_light (quick run) or make train (full run)

 

These are useful if you want to dig a little deeper and see exactly what’s happening

 

Next steps

Once you’re comfortable with this small demo, natural extensions are:

 

    • – Swap in a real dataset (e.g. DVLA MOT data)

 

    • – Add data validation gates (e.g. Great Expectations)

 

    • – Introduce bias/fairness checks with tools like Fairlearn

 

    • – Run the pipeline in Kubernetes (KinD/Argo) for reproducibility

 

    • – Hook it into GitHub Actions for end-to-end CI/CD

 

 

Closing thoughts

DevOps and MLOps share the same DNA: versioning, automation, observability, reproducibility. This demo repo is a small but practical bridge between the two

 

Working on CarHunch gave me the chance to apply these ideas in a real platform. This demo distills those lessons into something any DevOps engineer can try locally.

 

Try it out at github.com/DonaldSimpson/mlops_minilm_demo and let me know how you get on

 

CarHunch – Vehicle Insights Platform

CarHunch Logo

Turning billions of MOT and accident records into real-time vehicle insights.

Visit the live project here:

www.carhunch.com


What CarHunch Does

  • Aggregates billions of MOT test results and STATS19 UK accident records.
  • Provides real-time analytics on vehicle makes, models, years, and conditions.
  • Compares a specific car against similar vehicles (make/model/year).
  • Highlights common MOT failures and safety risks for different vehicles.

How It Works

CarHunch is powered by a ClickHouse data warehouse for ultra-fast queries, with:

  • Python ETL pipelines for MOT and accident data ingestion.
  • Incremental updates from DVLA bulk & delta files.
  • Redis caching for instant lookups.
  • Machine learning (MiniLM embeddings + clustering) to spot defect patterns.
  • LLM integration (LLaMA) to generate natural-language insights.

Example Insights

“Your 2010 Ford Focus has a 28% higher MOT failure rate than average for similar cars, mainly due to suspension wear.”

“BMW 3 Series (2008–2012) commonly fail MOTs due to brake issues around 80,000 miles.”

“Motorcycles show a different pattern of MOT failures compared to cars, with lighting and tyre defects being most common.”

Technical Overview

CarHunch isn’t just about insights — it’s also a demonstration of building a modern, high-performance OLAP data platform from the ground up.

  • Database: ClickHouse OLAP warehouse for real-time analytics on billions of records.
  • ETL: Python pipelines ingesting DVLA MOT bulk/delta files and STATS19 accident datasets.
  • Data Modeling: Normalised vehicle/test/defect schema with indexing and partitioning for query performance.
  • APIs: REST endpoints (Flask/FastAPI) serving real-time queries to front-end applications.
  • Caching: Redis for ultra-fast repeated lookups.
  • Machine Learning: MiniLM embeddings + HDBSCAN clustering for identifying defect patterns and grouping similar vehicles.
  • LLM Integration: Local LLaMA models for natural-language explanations and summaries.
  • Deployment: Dockerised services on a Proxmox node, easily portable to cloud infrastructure.
  • Monitoring: Logging & system metrics (rsyslog, lm-sensors) for reliability and performance tracking.

Why CarHunch?

CarHunch shows how big data + AI can turn raw government datasets into meaningful insights that benefit both consumers and the automotive industry.

👉 Explore more at

CarHunch.com

CarHunch Screenshot

 
Get in touch
if you’d like to collaborate or learn more.

Monitoring Proxmox with Grafana and InfluxDB

I took these notes while setting up Grafana and InfluxDB on Proxmox.

I hit a few minor issues so thought I’d post it here as a mini “How To” or reference for others.

 

 

NOTE: If you are just looking for a simple and light-weight way to monitor Proxmox stats (including memory, CPU, disk for your LXCs and VMs), check out the brief section on “Pulse” at the end of this page!

 

 

This setup allows me to easily monitor my Proxmox host and the VMs and LXCs it runs via a nice Grafana dashboard, with the data/metrics stored in InfluxDB.

 

The main steps are:

 

1. Install Influx DB
2. Install Grafana
3. Configure Proxmox
4. Configure InfluxDB
5. Configure Grafana

Install InfluxDB

Proxmox makes this very quick and very easy, if you’re happy to trust the Community scripts available here:

https://community-scripts.github.io/ProxmoxVE/

which just means running this one-liner in the proxmox console:

 

bash -c "$(curl -fsSL https://raw.githubusercontent.com/community-scripts/ProxmoxVE/main/ct/influxdb.sh)"

 

this created an InfluxDB LXC in a couple of minutes.

 

For me, the IP and port were: http://192.168.0.24:8086

 

Install Grafana

This was much the same with a different script, and just meant running:

 

bash -c "$(curl -fsSL https://raw.githubusercontent.com/community-scripts/ProxmoxVE/main/ct/grafana.sh)"

 

then I also had a new Grafana instance here:

 

http://192.168.0.114:3000

 

Note that the default user:password for Grafana is admin:admin

 

Configure Proxmox

Next you need to set the Metrics Server used byProxmox, this will tell proxmox to send all metrics on itself and the VMs and LXCs it runs to InfluxDB.

This is set under “Datacenter” in the proxmox UI:

 

This looked straightforward too, but there were conflicting opinions on how to do it. I initially went with UDP which didn’t work for me; there was nowhere to set any authentication and I wasn’t allowing anonymous access to InfluxDB, so I switched to using HTTP which then allowed me to specify the (InfluxDB) credentials.

 

Configure InfluxDB

I created a “proxmox” organisation and a “proxmox” bucket in InfluxDB

 

I then created an API key/Token specifically for that proxmox bucket, which I used in the above pic.

 

To verify things were working between Proxmox and InfluxDB, I took a look in the data explorer:

 

 

You can see in that pic that InfluxDB has data on my VMs and LXCs, which it must have received from Proxmox, so I then knew my remaining issues were with the connection between InfluxDB <-> Grafana.

 

Configure Grafana

 

Initially I was getting “InfluxDB returned error: Unauthorized error reading influxDB” – hence the check above to confirm that Proxmox -> InfluxDB was working ok.

 

I couldn’t see anywhere in this version of Grafana to specify the Token for InfluxDB though – other screenshots on the ‘net had & used that option, but it wasn’t available for me 🙁

 

After some reading I learned you could set the Token by creating a new Custom HTTP Header called “Authorization” with the value “Token BXx…….7yBkw==” (that’s the word Token, a space, then the full Token you got from InfluxDB, all set as the Value for a new Custom HTTP Header called Authorization…)

 

This seemed surprisingly flaky to me, but it worked.

 

My (working) connection details look like this:

 

Prior to adding that HTTP Header, I was getting a successful connection but “0 measurements found”.

 

Next I added a new Proxmox dashboard to Grafana from here:
https://grafana.com/grafana/dashboards/10048-proxmox/

 

you don’t need to sign up there or anything else, just enter the ID: 10048 like in this pic and it’ll pull the Dashboard down:

 

Now I was finally able to see data being populated in Grafana from my Proxmox node & its VMs & LXCs:
Happy days.

 

The Pulse option

 

A possible alternative to the above Grafana and InfluxDB stack is to use “Pulse” – this was new to me and I have recently set it up too (you can never have enough monitoring!).

 

This is a very lightweight and more focused option that is really quick and easy to set up.

 

While the InfluxDB and Grafana approach can be extended to cover a vast range of monitoring and alerting for all sorts of things – I have set up and used it in several large companies I’ve worked for – if all you really want is Proxmox monitoring without those possibilities, this looks perfect.

 

 

with a simple install script for Proxmox:

 

bash -c "$(wget -qLO - https://github.com/community-scripts/ProxmoxVE/raw/main/ct/pulse.sh)"

 

 

Here’s my settings screen:

 

And here’s what it looks like on my Proxmox host:

 

Neat!

 

Kubernetes Operators for Monitoring with Prometheus and Grafana Dashboards

Introduction

This post takes a look at setting up monitoring and alerting in Kubernetes, using Helm and Kubernetes Operators to deploy and configure Prometheus and Grafana.

This platform is quickly and easily deployed to the cluster using a Helm Chart, which in turn uses a Kubernetes Operator, to setup all of the required resources in an existing Kubernetes Cluster.

I’m re-using the Minikube Kubernetes cluster with Helm that was built and described in previous posts here and here, but the same steps should work for any working Kubernetes & Helm setup.

An example Grafana Dashboard for Kubernetes monitoring is then imported and we take a quick look at monitoring of Cluster components with other dashboards

Kubernetes Operators & Helm combo

K8s Operators are described ‘in plain English’ here:
https://enterprisersproject.com/article/2019/2/kubernetes-operators-plain-english

and defined by CoreOS as “a method of packaging, deploying and managing a Kubernetes application

The Operator used in this post can be seen here:

https://github.com/coreos/prometheus-operator

and this is deployed to the Cluster using this Helm Chart:

https://github.com/helm/charts/tree/master/stable/prometheus-operator

It may sound like Helm and Operators do much the same thing, but they are different and complimentary

Helm and Operators are complementary technologies. Helm is geared towards performing day-1 operations of templatization and deployment of Kubernetes YAMLs — in this case Operator deployment. Operator is geared towards handling day-2 operations of managing application workloads on Kubernetes.

from https://medium.com/@cloudark/kubernetes-operators-and-helm-it-takes-two-to-tango-3ff6dcf65619

Let’s get (re)started

I’m reusing the Minikube cluster from previous posts, so start it back up with:

minikube start

which outputs the following in the console

🎉  minikube 1.10.1 is available! Download it: https://github.com/kubernetes/minikube/releases/tag/v1.10.1
💡  To disable this notice, run: ‘minikube config set WantUpdateNotification false’

🙄  minikube v1.9.2 on Darwin 10.13.6
✨  Using the virtualbox driver based on existing profile
👍  Starting control plane node m01 in cluster minikube
🔄  Restarting existing virtualbox VM for “minikube” …
🐳  Preparing Kubernetes v1.18.0 on Docker 19.03.8 …
🌟  Enabling addons: dashboard, default-storageclass, helm-tiller, metrics-server, storage-provisioner
🏄  Done! kubectl is now configured to use “minikube”

this all looks ok, and includes the minikube addons I’d selected previously.
Now a quick check to make sure my local helm repo is up to date:

helm repo update

I then used this command to find the latest version of the stable prometheus-operator via a helm search:
helm search stable/prometheus-operator --versions | head -2

there’s no doubt a neater/builtin way to find out the latest version, but this did the job – I’m going to install 8.13.8:

install the prometheus operator using Helm, in to a new dedicated “monitoring” namespace just takes this one command:
helm install stable/prometheus-operator --version=8.13.8 --name=monitoring --namespace=monitoring

Ooops

that should normally be it, but for me, this resulted in some issues along these lines:

Error: Get http://localhost:8080/version?timeout=32s: dial tcp 127.0.0.1:8080: connect: connection refused

– looks like Helm can’t communicate with Tiller any more; I confirmed this with a simple helm ls which also failed with the same message. This shouldn’t be a problem when v3 of Helm goes “tillerless”, but to fix this quickly I simply re-enabled Tiller in my cluster via Minikube Addons:


➞  minikube addons disable helm-tiller
➞  minikube addons enable helm-tiller

verified things worked again with helm ls, then the helm install... command worked and started to do its thing…

New Operator and Namespace

Keeping an eye on progress in my k8s dashboard, I can see the new “monitoring” namespace has been created, and the various Operator components are being downloaded, started up and configured:

you can also keep an eye on progress with:
watch -d kubectl get po --namespace=monitoring

this takes a while on my machine, but eventually completes with this console output:

NOTES:
The Prometheus Operator has been installed. Check its status by running:
  kubectl –namespace monitoring get pods -l “release=monitoring”

Visit https://github.com/coreos/prometheus-operator for instructions on how
to create & configure Alertmanager and Prometheus instances using the Operator.

kubectl get po --namespace=monitoring shows the pods now running in the cluster, and for this quick example the easiest way to get access to the new Grafana instance is to forward the pods port 3000 to localhost like this:

➞  kubectl --namespace monitoring port-forward monitoring-grafana-64d4f6fcf7-t5zkv 3000:3000

(check and adjust the above to use the full/correct name of your monitoring-grafana-* pod)

Connecting to Grafana

now I can hit http://localhost:3000 and have that connect to port 3000 in the Grafana pod:


from the documentation on the Helm Chart and Operator here:

https://github.com/helm/charts/tree/master/stable/prometheus-operator

the default user for this Grafana is “admin” and the password for that user is “prom-operator“, so log in with those credentials…

Grafana Dashboards for Kubernetes

We can now use the ready-made Grafana dashboards, or add/import ones from the extensive online collection, like this one here for example: https://grafana.com/grafana/dashboards/6417 – simply save the JSON file

then go to Grafana and import it with these settings:

and you should now have a dashboard showing some pretty helpful stats on your kubernetes cluster, it’s health and resource usage:

Finally a very quick look at some of the other inbuilt dashboards – you can use and adjust these to monitor all of the components that comprise your cluster and set up alerting when limits or triggers are reached:

All done & next steps

There’s a whole lot more that can be done here, and many other ways to get to this point, but I found this pretty quick and easy.

I’ve only been looking at monitoring of k8s resources here, but you can obviously set up grafana dashboards for many other things, like monitoring your deployed applications. Many applications (and charts and operators) come with prom endpoints built in, and can easily and automatically be added to your monitoring and alerting dashboards along with other datasources.

Cheers,

Don

Kubernetes – with Minikube and Helm – part 2

This is the second half of the Kubernetes with Minikube and Helm presentation, the first half explains all of the steps we went through to get to this point, and is available here:

In this section we cover the following:

  • Helm and Tiller – what they are, when & why you’d maybe use them
  • Helm and Tiller – prep, install and Helm Charts
  • Deploying Jenkins via Helm Charts
  • and WordPress w/MariaDB too
  • Wrap up

The below are mostly my technical notes from this session, with some added blurb/explanation.

Helm and Tiller – what they are, when & why you’d maybe use them

From the Helm site:

“Helm helps you manage Kubernetes applications — Helm Charts help you define, install, and upgrade even the most complex Kubernetes application. Charts are easy to create, version, share, and publish — so start using Helm and stop the copy-and-paste.”

https://helm.sh/

Helm is basically a package manager for Kubernetes applications. You can choose from a large list of Stable (or not so!) ready made packages and use the Helm Charts to quickly and easily deploy them to your own Kubernetes Cluster.

This makes light work of some very complex deployment tasks, and it’s also possible to extend these ready-made charts to suit your needs, and to write your own Charts from scratch, or pass your own values to override default ones, or… many other interesting options!

For this session we are looking at installing Helm, reviewing some example Helm Charts and deploying a few “vanilla” ones to the cluster we created in the first half of the session. We also touch upon the life-cycle of Helm Charts – it’s similar to dockers – and point out some of the ways this could be extended and customised to suit your needs – more on this at a later date hopefully.

Helm and Tiller – prep, install and Helm Charts

First, installing Helm – it’s as easy as this, run on your laptop/host that’s running the Minikube k8s we setup earlier:

Get & chmod the get_helm script, then run it:

curl https://raw.githubusercontent.com/kubernetes/helm/master/scripts/get > get_helm.sh

chmod 700 get_helm.sh

./get_helm.sh

Tiller is the client part of Helm and is deployed inside your k8s cluster. It’s set to be removed with the release of Helm 3, but the basic functionality wont really change. More details here https://helm.sh/blog/helm-3-preview-pt1/

Next we do the Tiller prep & install – add RBAC for tiller, deploy via helm and take a look at the running pods:

kubectl create serviceaccount -n kube-system tiller

kubectl create clusterrolebinding tiller-cluster-rule --clusterrole=cluster-admin --serviceaccount=kube-system:tiller

helm init --service-account tiller

kubectl --namespace kube-system get pods

Helm Charts – look at the list of available stable Charts, then deploy a couple. The github repo is here

https://github.com/helm/charts

Update the local helm repo info:

helm repo update

then, for example, install Redis from its Helm Chart to the k8s cluster as easily as this:

helm install stable/redis

or helm install stable/mysql and check the console output that explains how to access the newly deployed app.

keep an eye on the pods to see what’s going on: watch kubectl get pods -o wide

Deploying Jenkins via Helm Charts

helm ls

helm delete <things you don't want any more to free up resources>

helm install --set serviceType=NodePort --name jenki stable/jenkins

again, watch kubectl get pods -o wide

now get the URL for the Jenkins service from Minikube:

minikube service --url=true jenki-jenkins

Hit that URL in your browser, and grab the password in UI from Pods > Jenki and log in to Jenkins with the user “admin”:

That’s a Jenkins instance deployed via Helm and Tiller and a Helm Chart to our Kubernetes Cluster running inside Minikube via a VirtualBox VM… all done in a few minutes. And it’s all customisable, repeatable, highly scaleable and awesome.

and WordPress w/MariaDB too

This was the “bonus demo” if my laptop wasn’t on fire – and thanks to some rapid cleaning up it managed fine – showing how quickly we could deploy a functional WordPress with MariaDB backend to our k8s cluster using the Helm Chart.

To prepare for this I did a helm ls to see all the things I had running. then helm delete --purge jenki, gave it a while to recover then had to do

kubectl delete pods <jenkinpod>

before starting the WordPress Chart deployment with

helm install --set serviceType=NodePort --name wp-k8s stable/wordpress

watch kubectl get pods -o wide for a while – note the chart is configured with the mariadb pod as a pre requisite of the wordpress instance:

Once it’s started we requested the service URL from Minikube again, making ingress nice and easy:

minikube service --url=true wp-k8s-wordpress

Hit that in the browser, using https and accepting the cert warning…

then logged in as `user` and qureied for the password in the k8s secret…

echo Password: $(kubectl get secret wp-k8s-wordpress -o
jsonpath="{.data.wordpress-password}" | base64 --decode)

and logged in to WordPress:

Wrap up

That’s it – we covered a lot in this session, and plan to use this as a platform to explore Helm in more detail later, writing our own Helm Charts and providing our own customisations to them.

minikube delete; rm -rf ~/.minikube

Cleans up everything we’d done:

Leaving just the local tools to remove if you want to – see the first half for a reminder.

Cheers,

Don

Update: this follow-on post runs through setting up Jenkins with Helm then creating Jenkins Pipelines that dynamically provision dockerised Jenkins Agents:

Kubernetes – with Minikube and Helm – part 1

Intro:

This is the first of two posts on Kubernetes and Helm Charts, focusing on setting up a local development environment for Kubernetes using Minikube, then exploring Helm for package management and quickly and easily deploying several applications to the cluster – NGINX, Jenkins, WordPress with a MariaDB backend, MySQL and Redis.

The content is taken from the practical/demo session I wrote and published in Github here:

https://github.com/AutomatedIT/presentations/blob/master/minikube_demo.md

for this Meetup session we ran in Edinburgh in June 2019:

“Kubernetes – getting started with Minikube, Helm and Tiller” https://www.meetup.com/Automated-IT-Solutions/events/261623765/

<ramble>

One of the key objectives and challenges here was getting a useful local Kubernetes environment up and running as quickly and easily as possible for as wide an audience as we could- there’s so much to the Kubernetes ecosystem that it’s very easy to get side-tracked, and we could have (happily) spent a long time discussing the myriad of alternative possible solutions.

We plan to go “deeper” on all of this in future sessions and have an in-depth Helm session in the works, but for this session we were focused on creating a practical starting point.

</ramble>

Don

What is covered here:

  • Minikube – what it is (& isn’t) & why you’d use it (or not)
  • Kubernetes and Minikube components and concepts
  • setup for Mac and Linux
  • creating a first Kubernetes cluster in Minikube
  • minikube addons – what they are and how they can help you
  • minikube docker env – using DOCKER_HOST with minikube VM
  • Kubernetes dashboard with Heapster and Metrics Server – made easy by Minikube
  • kubectl – some examples and alternatives
  • example app – “hello (Kubernetes) world” minikube style with NGINX, scaling your world

and the second post covers:

  • Helm and Tiller – what they are, when & why you’d maybe use them
  • Helm and Tiller – prep, install and Helm Charts
  • Deploying Jenkins via Helm Charts
  • and WordPress w/MariaDB too
  • wrap up

Minikube – what it is (& isn’t) & why you’d use it (or not)


What it is, why you’d use it etc.

Local development of k8s – runs a single node Kubernetes cluster in a Virtual Machine on your laptop/PC.

All about making things easy for local development, it is not a production solution, or even close to it.

There are many other ways to run k8s, they all have their pros and cons and use cases. The slides from the Meetup covered this in more detail and include links for further info – they are available here:

Kubernetes and Minikube components and concepts

The (above) slides also cover this section:
Kubernetes components and concepts
what it solves
how Minikube works


Setup for Mac and Linux

There are three things you need to set up for this, they are:
VirtualBox: https://www.virtualbox.org/wiki/Downloads
Minikube: https://kubernetes.io/docs/tasks/tools/install-minikube/
kubectl: https://kubernetes.io/docs/tasks/tools/install-kubectl/

Using Ubuntu for example:

curl -Lo minikube https://storage.googleapis.com/minikube/releases/v1.1.0/minikube-linux-amd64 && chmod +x minikube && sudo cp minikube /usr/local/bin/ && rm minikube

curl -LO https://storage.googleapis.com/kubernetes-release/release/v1.14.0/bin/linux/amd64/kubectl

`chmod +x ./kubectl

`sudo mv ./kubectl /usr/local/bin/kubectl`

Cleanup/prep – if required, remove any previous cluster & settings

`minikube delete; rm -rf ~/.minikube`

Creating a first Kubernetes cluster in Minikube

Here we create a first Kubernetes cluster with Minikube, then take a look around in & outside of the VM.

With the above initial setup done, it’s as simple as running this in a shell:

minikube start

Note you could optionally give this Cluster a name, if you are likely to have more than one for different branches of development for example. This is also where you could specify the VM provider if you want to use something other than VirtualBox – there are more details here:

https://kubernetes.io/docs/setup/learning-environment/minikube/#starting-a-cluster

This should produce output like the following, and it may well take a few minutes as the VM is downloaded and started, then a stack of Docker images are started up inside that….

At this point you should be able to see the minikube VM running in the VirtualBox GUI:

Now it’s running, we can connect from our local shell directly to the one inside the running VM by simply issuing:

minikube ssh

This will put you inside the VM where the Kubernetes Cluster is being run, and we can see and interact with the running components, for example:

docker images

should show all of the downloaded images:

and you could do this to see the running containers:

docker ps

Quitting out of the VM puts us back on the local host, where we can use kubectl to query the status of the Minikube cluster – the initial setup has told kubectl about the Minikube-managed Kubernetes Cluster, meaning there’s no other setup required here:

kubectl cluster-info

kubectl get nodes

kubectl describe nodes

minikube addons – what they are and how they can help you

Show some of the ways minkube makes things easier for local dev

First, take a moment to look around these two local folders:

ls -al ~/.minikube; ls -al ~/.kube

These are where Minikube keeps its settings and the VM Image, and where kubectl settings are persisted – and updated by Minikube.

With Minikube you’ve often got the option to either use kubectl directly, or to use some Minikube built-in features to make your life easier.

Addons are one of these features, allowing you to very easily add – or remove – functionality from the cluster like this:

minikube addons list

minikube addons enable heapster

minikube addons enable metrics-server

With those three lines we’ve taken a look at the available addons and their current status, and selected to enable both heapster and the metrics server. This was done to give us cpu and mem stats in the Kubernetes Dashboard, which we will set up in a moment. The output should look something like this:

minikube config view

shows the current state of the config – i.e. what changes have been made, so we can keep a track of them easily.

kubectl --namespace kube-system get pods

now we can enable the dashboard:

minikube addons enable dashboard

and check again to see the current state

minikube addons list

we’ll connect to the Dashboard and take a look around in a moment, but first…

minikube docker env – using the DOCKER_HOST in you minikube VM – how & why


Minikube docker-env – setup local docker client to use minikube docker host

We’re going to look at connecting our local docker client to the docker host inside the Minikube VM. This is made easy by:

minikube docker-env

if you run that command on its own it wiull show you what settings it will export and you can set them by doing:

eval ${minikube docker-env}

From then on, in that shell, your local docker commands will use the docker host inside Minikube.

This is very useful for debugging and local development – when you change and deploy anything to your Kubernetes Cluster, you can easily tail the logs or check for errors or issues. You can also do all of this via the dashboard or kubectl too if you prefer, but it’s another handy and powerful feature from Minikube.

The following image shows the result of running this command:

eval $(minikube docker-env) && docker ps | grep -i metrics

so we can now use our local docker client to run docker commands like…

docker ps

docker ps | grep -i metrics

docker logs -f <some container id>

etc.

Kubernetes dashboard with Heapster and Metrics Server – made easy by Minikube

Minikube k8s dashboard – here we will start up the k8s dashboard and take look around.

We’ve delayed starting the dashboard up until after we enabled the metrics-server & heapster components we deployed earlier. By doing it in this order, the dashboard will automatically detect and use these components, giving us cpu & mem stats and a nicer looking dash, with no additional config required.

Starting the dashboard simply involved running

minikube dashboard

and waiting for a minute…

That should fire up your browser automatically, then you can take a look around at things like Default namespace > Nodes

and in the namespace kube-system > Deployments

and kube-system > Pods

You can see the logs and statuses of everything running in your k8s cluster – from the core components we covered at the start, to the dashboard, metrics and heapster we enabled recently, and the application we’re going to deploy and scale up soon.

kubectl – some examples and alternatives

# kubectl command line – look at kubectl and keep an eye on things
kubectl get deployment -n kube-system

kubectl get pods -o wide -n kube-system

kubectl get services

kubectl

example app – “hello (Kubernetes) world” minikube style with NGINX, scaling your world

Now we’ll deploy the most basic application we can – a “Hello World” style NGINX docker image.

It’s as simple as this, where nginx is the name of the docker image you want to deploy, hello-nginx is the label you want to give it, and port 80 is where you want it to listen:

kubectl run hello-nginx --image=nginx --port=80

that shouldn’t take long, and you can watch the progress like this:

kubectl get pods -o wide

We can then expose the deployment using NodePort:

kubectl expose deployment hello-nginx --type=NodePort

then we can ask Minikube to provide the URL for Ingress:

minikube service --url=true hello-nginx

and hitting that URL in your browser should show the obvious:

“Welcome to nginx!

If you see this page, the nginx web server is successfully installed and working. Further configuration is required.”

you can keep an eye on the Service with

kubectl get svc

while we scale to x3 replicas:

kubectl scale --replicas=3 deployment/hello-nginx

and take a look at what happens with

kubectl get deployment

kubectl get pods -o wide

or check in the Dashboard to see something like this:

and monitor what’s going on in our “hello world” NGINX app with kubectl then scale it down to 0 or 1 or whatever you like…

kubectl get deployment

kubectl get pods -o wide

kubectl scale --replicas=0 deployment/hello-nginx

Next post – Helm & Tiller onwards…

Extracting data from Jenkins

In Part I,  Information Radiators, I covered what they are, what the main benefits are, and the approach I usually use to set them up. This post goes in to more technical detail on how I extract this data from Jenkins.

My usual setup/architecture for Jenkins Information Radiators goes something along these lines:

  • TV screens running Mozilla Firefox or Google Chrome in Kiosk Mode, and Tab Mix Plus set up to rotate tabs (if required)
  • JSP Pages served via Tomcat on Linux server (which also runs the data extracting script described below)
  • MySQL database on Linux server – contains tables with data pulled from Jenkins and other sources, and the config data too (which URL’s to monitor)

And you’ll need some Jenkins instances/jobs to monitor too, obviously 🙂

The Jenkins XML API is very useful for automating tasks like this – if you simply append “/api/xml” to a
Jenkins job URL, it will serve up an XML version – note there is also a JSON API and a CLI and plenty of other options, but I’m using what suits me.

The Jenkins XML API

For example, if you go to one of your Jenkins jobs and add /api/xml like this:

“http://yourjenkinsserver:8080/job/yourjobname/api/xml

you should get back some XML, possibly roughly like this example:

<?xml version="1.0"?>
<freeStyleBuild>
 <action>
 <parameter>
 <name>LOWER_ENV</name>
 <value>dev</value>
 </parameter>
 </action>
 <action>
 <cause>
 <shortDescription>Started by timer</shortDescription>
 </cause>
 </action>
 <building>false</building>
 <duration>61886</duration>
 <fullDisplayName>MyJob #580</fullDisplayName>
 <id>2014-04-01_10-01-50</id>
 <keepLog>false</keepLog>
 <number>580</number>
 <result>SUCCESS</result>
 <timestamp>1396342910088</timestamp>
 <url>http://jenkinsserver:8080/view/MyView/job/MyJob/580/</url>
 <builtOn/>
 <changeSet/>
</freeStyleBuild>

That XML contains loads of very useful information inside handy XML tag descriptions – you just need a way to get at that data and then you can present it as you like…

XPAth queries and the Jenkins XML API

so to automate that, I used to extend that approach a to query Jenkins via the XML API using XPAth queries to bring back just the data I actually wanted, quite like querying a database.

For example, wget’ing this URL would return just the current value of the <building> tag in the above XML:

http://yourjenkinsserver:8080/job/yourjobname/api/xml?xpath=//building/text()

e.g. “true” or “false” – this was very useful and easy to do, but the functionality was removed/disabled in recent versions of Jenkins for security reasons, meaning that my processes that used it needed rewritten 🙁

Extracting the data – Plan B…

So, here’s the new solution I went for – the real scripts/methods do some error handling and cleaning up etc but I’m just highlighting the main functions and the high level logic behind each of them here;

get_url’s method:

query a table in MySQL that contains a list of the job names and URL’s to monitor
for each $JOB_NAME found, it calls the get_file method, passing that the URL as a parameter.

get_file method:

this takes a URL param, and uses curl to fetch and save the XML data from that URL to a temporary file (“xmlfile”):

curl -sL "$1" | xmllint --format - > xmlfile

Note I’m using “xmllint –format” there to nicely format the XML data, which makes processing it later much easier.

get_data method:

this first calls “get_if_building” (see below) to see if the job is currently running or not, then it does:

TRUE_VAR="true"
 if [[ "$IS_BUILDING" == "$TRUE_VAR" ]]; then
 RESULT_TEXT="building..."
 else
 RESULT_TEXT=`grep "result>" xmlfile | awk -F\> '{print $2}' | awk -F\< '{print $1}'`
 fi

get_if_building method:

this simply checks and sets the IS_BUILDING var like so:

IS_BUILDING=`grep building xmlfile | awk -F\> '{print $2}' | awk -F\< '{print $1}'`

Putting it all together

My script then updates the MySQL database with the results from each check: success/failure, date, build number, user, change details etc

I then have JSP pages that read data from that table, and translate things like true/false in to HTML that sets the background colours (Red, Amber, Green), and shows the appropriate blocks and details per job.

If you have a few browsers/TV’s or Monitors showing these strategically placed around the office, developers get rapid feedback on the result of their code changes which speeds up development, increases quality and reduces development time and costs – and they can be fun to watch and set up too 🙂

Cheers,

Don

Loading CSV data in to MySQL – random quotations app Part 1

Time for a new PHP and MySQL app – “Who said that?” – a search tool for famous quotations.

I created these two web applications a while back:

UK post code search
crossword solver

and then wrote this page:
Some PHP examples
detailing roughly how they were put together, but this time I wanted to create a searchable database of famous quotations, and focus on the MySQL side of things a bit more too (so that next time I will have a note of how I did it!).

I found a very nice CSV data file on http://thewebminer.com/download for free – I don’t really do Facebook much and don’t have a Twitter account so I thought/hoped they’d settle for a blog post in exchange…

After installing and setting up MySQL, connect to your database…

mysql –user=myuser –password=myusualpassword dev

— or connect without specifying a database/schema and do “show databases;”

mysql> show tables;
 +---------------+
 | Tables_in_dev |
 +---------------+
 | areacodes |
 | dictionary |
 +---------------+
 2 rows in set (0.02 sec)

For this little app, I want to create a new table with fields for each row in the CSV file
plus I’d like an auto_increment field to make fetching random numbers easier

CREATE TABLE quotes (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 quote varchar(800),
 author varchar(100),
 genre varchar(100)
 );
mysql> show tables;
 +---------------+
 | Tables_in_dev |
 +---------------+
 | areacodes |
 | dictionary |
 | quotes |
 +---------------+
 3 rows in set (0.00 sec)
mysql> describe quotes;
 +--------+--------------+------+-----+---------+----------------+
 | Field | Type | Null | Key | Default | Extra |
 +--------+--------------+------+-----+---------+----------------+
 | id | int(11) | NO | PRI | NULL | auto_increment |
 | quote | varchar(800) | YES | | NULL | |
 | author | varchar(100) | YES | | NULL | |
 | genre | varchar(100) | YES | | NULL | |
 +--------+--------------+------+-----+---------+----------------+
 4 rows in set (0.06 sec)

ok, the table looks good, so I can load the CSV data file – note that I’ve got the “quotes35000.csv” file I downloaded from
http://thewebminer.com/download sitting in the current directory:

mysql> load data local infile 'quotes35000.csv' into table quotes
 -> fields terminated by ';'
 -> lines terminated by 'n'
 -> (quote, author, genre);
 Query OK, 35002 rows affected (1.54 sec)
 Records: 35002 Deleted: 0 Skipped: 0 Warnings: 0

that looks like it went well (“35002 rows affected”), time to check it:

mysql> select count(*) from quotes;
 +----------+
 | count(*) |
 +----------+
 | 35002 |
 +----------+
 1 row in set (0.04 sec)
mysql> select * from quotes where author like '%Einstein' and genre like 'attitude%';
 +------+-----------------------------------------------------+-----------------+-----------+
 | id | quote | author | genre |
 +------+-----------------------------------------------------+-----------------+-----------+
 |4647 | Weakness of attitude becomes weakness of character. | Albert Einstein | attitude
 +------+-----------------------------------------------------+-----------------+-----------+
 1 row in set (0.02 sec)

All looking good, the row count and the returned query match what I’d expect having looked at the contents of the CSV file.

I also want to do a “random quote of the day thing”, so looked in to ways to do this in MySQL – my initial thought was to use something basic like “ORDER BY RAND() LIMIT 0,1;” to bring back one random row, but I guessed there may be better ways.

Google led me to this site which has some good examples and some performance/comparison details too:
http://akinas.com/pages/en/blog/mysql_random_row/

so I tried this…

mysql> SELECT * FROM quotes WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM quotes ) ORDER BY id LIMIT 1;
 +-----+--------------------------------------------------------------------------+-----------------+-------+
 | id | quote | author | genre |
 +-----+--------------------------------------------------------------------------+-----------------+-------+
 |84 | Men are like wine - some turn to vinegar, but the best improve with age. | Pope John XXIII | age
 +-----+--------------------------------------------------------------------------+-----------------+-------+
 1 row in set (1.54 sec)

then this…

mysql> SELECT * FROM quotes ORDER BY RAND() LIMIT 0,1;
 +-------+-------------------------------------------------------------------------------------+----------------+-------+
 | id | quote | author | genre |
 +-------+-------------------------------------------------------------------------------------+----------------+-------+
 |29470 | The good die young, because they see it's no use living if you have got to be good. | John Barrymore | good
 +-------+-------------------------------------------------------------------------------------+----------------+-------+
 1 row in set (0.73 sec)

and found to my surprise that in this case, looking at the timings, the simple approach looks to be faster – probably because of the relatively small table and its simple structure?

Anyway, that’s the database side of things sorted, the next part is to put together some PHP code to allow searching for quotes based on author, partial quote or genre, and to write a simple “random quote” generator kind of thing.

Cheers,

Don

Oracle admin tasks

Oracle admin tasks – here are some basic queries and script examples I have gathered and adapted from various sources – the Internet, colleagues etc.

GitHub repo: https://github.com/DonaldSimpson/oracle_scripts

My main interest in this is in doing both day to day maintenance tasks to support environments, and in scripting monitoring and preventative Jenkins jobs that report on various aspects of Oracle Database servers – these automated database monitors have proved very worthwhile, and often identify upcoming issues before they cause problems (e.g. expiring users, table spaces filling up, disabled constraints and triggers, etc etc).

 

Find and kill sessions:

Connect:
sqlplus / as sysdba

Set the line size so things look better:
set linesize 999

Then run a query to show active users:
SELECT s.osuser, s.status, s.process, s.machine, s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE s.type != ‘BACKGROUND’;

If you want to kill one, use the SID and SERIAL from the above:

ALTER SYSTEM KILL SESSION ‘{SID},{SERIAL}’;

———————–

Tablespaces; finding, resizing and autoextending:

sqlplus / as sysdba
set linesize 999

List Oracle Database tablespace files:

SELECT FILE_NAME as FNAME, TABLESPACE_NAME as TSPACE,BYTES, AUTOEXTENSIBLE as AUTOEX, MAXBYTES as MAXB,INCREMENT_BY as INC FROM DBA_DATA_FILES;

From the above, get the file name for the Table Space that needs altered, and do something like this:

ALTER DATABASE DATAFILE ‘{/path to above TS file, eg /ora/path/undotbs_0001.dbf}’ AUTOEXTEND ON NEXT 64m MAXSIZE 2G;

———————–

Start and Stop things:

Listeners…

lsnrctl start listener_Name
lsnrctl stop listener_Name

databases…
sqlplus / as sysdba
startup
shutdown immediate

———————–

Find invalid objects:

Optionally filtered by owner(s) and without synonyms…

select owner || ‘.’ || object_name || ‘[‘ || object_type || ‘]’
from dba_objects
where status = ‘INVALID’
and object_type != ‘SYNONYM’
and owner in (‘SYSTEM’,’SYS’,’TOOLS’,’DEVUSER’);

———————–

Check Constraints and Triggers:

SELECT * FROM all_constraints WHERE status <> ‘ENABLED’;

or filter by users:

SELECT * FROM all_constraints WHERE owner = ‘ARBOR’ and status <> ‘ENABLED’;

Triggers are similar:

select * from all_triggers where status <> ‘ENABLED’;

———————–

Check for locked/locking users:

those already locked:

select * from dba_users where username in (‘SYSTEM’,’SYS’,’TOOLS’,’DEVUSER’) and lock_date is not null;

or those about to be locked (I add this to my Jenkins Database monitoring jobs so you get some warning…):

select * from dba_users where expiry_date < (trunc(SYSDATE) +7) and lock_date = null;

———————–

Check the Oracle Wallet:

Check to see if encrytion is present:

select * from dba_encrypted_columns;

if that brings something back, then you can check the state of the Oracle Wallet:

SELECT status from v$encryption_wallet where status not like ‘OPEN’;

———————–

Running SQL scripts from Shell scripts:

This can be done in various ways, but I tend to either use this approach to simply run a file and exit:

echo “About to run ${SCRIPT_NAME} on ${SERVER}…”
echo exit | sqlplus ${DB_USER}/${DB_PASSWORD}@${SERVER} @/path/to/sql/scripts/${SCRIPT_NAME}.sql
echo “Script ${SCRIPT_NAME} complete.” # now check the return code etc…

or sometimes a HEREDOC is more suitable, something like this example for checking database links work:

echo “Checking ${DBLINK} link for user ${DB_USER}…”
DBLINK_CHECK=$(sqlplus -s -l ${DB_USER}/${DB_PASS}@${ADM_DBASE}<<EOF
set echo off heading off feedback off
SELECT ‘Link works’ from dual@${DBLINK};
exit;
EOF)
if [ $? -ne 0 ]
then
echo “ERROR: Checking link ${DBLINK} as ${DB_USER} FAILED”
fi

———————–

If you find any of these useful or would like to suggest additions or changes please let me know.

Cheers,

Don