or Publishing a cleanup script for Oracle 12c+ At the moment I’m just for fun collecting Linux scripts from my direct colleagues. And while I was busy to think up a wiki page for internal use, for a moment I felt like Uncle Scrooge, gathering riches from other minds. Which, […]
Databases
All that is not covered by the Oracle Database…
Preparation for migrating data to Oracle Virtual Private Database
preparation for migrating data to Oracle Virtual Private Database Introduction Recently I was part of a team involved in the preparation of migration data belonging to multiple business units into a single Oracle 19c database with Virtual Private Database (VPD). The VPD solution is used for the virtual separation […]
A Quick how-to RMAN backup to OCI Object Storage
This is a quick how-to article on setting up RMAN to backup to Object Storage on OCI. This is applicable when you build your on Oracle database on an OCI instance, or when you have a database on-premise you wish to backup to the (OCI) cloud.
Migrating databases using Dataguard, some pitfalls and observations
My customer needed to migrate to a new set of datacenters, and while other services were able to “lift and shift”, the Oracle RAC database servers had to be rebuilt in the new datacenter because the underlying platform wasn’t able to migrate the shared disks. Fortunately the environment was already […]
Migrating an old (10.2.0.4) database to Oracle Cloud with minimal downtime
Unlike most of our posts this post will contain almost no code or examples. But it hopefully will help somebody who ends up to be in the same situation we landed on: migrating data from very old versions to a new environment. Recently we were tasked with the migration of […]
Performance of relational database drivers. R2DBC vs JDBC
R2DBC provides non-blocking reactive APIs to relational database programmers in Java. It is an open specification, similar to JDBC. JDBC however uses a thread per connection while R2DBC can handle more connections using less threads (and thus potentially use less memory). This could also mean threads are available to do other […]
Calling an Oracle DB stored procedure from Spring Boot using Apache Camel
There are different ways to create data services. The choice for a specific technology to use, depends on several factors inside the organisation which wishes to realize these services. In this blog post I’ll provide a minimal example of how you can use Spring Boot with Apache Camel to call […]
What is Apache Drill and how to setup your Proof-of-Concept
Apache Drill is a schema-free SQL query engine. Drill supports a variety of NoSQL databases and file systems, including HBase, MongoDB, MapR-DB, HDFS, MapR-FS, Amazon S3, Azure Blob Storage, Google Cloud Storage, Swift, NAS and local files. A single query can join data from multiple datastores. For example, you can […]
InfluxDB V2.0 – Stack Implementation Proof of Concept
This blogpost will give you detailed instructions and information regarding the InfluxDB stack. Since February 2019 InfluxDB now consists not only the database itself, but also Telegraf, Chronograf and Kapacitor. Later you’ll read about what every part of the stack does. 18-2-2019: Living on the bloody edge, do not run […]
Enterprise Manager Convert to Cluster Database (RAC) fails without showing errors at Pre Configuration phase
Oracle Enterprise Manager Cloud Control 13c provides a wizard to convert a single-instance database to an Oracle RAC database. This requires the Oracle Database Lifecycle Management Pack for Enterprise Manager Cloud Control. When converting the database to a cluster database, after proving all the required details in the wizard, a […]
Enterprise Manager DataGuard Add Standby Database fails without showing errors at Destination Preparation phase
Oracle Enterprise Manager Cloud Control 13c provides the Add Standby Database wizard to create a broker configuration that includes a primary database and one or more standby database. This requires the Oracle Database Lifecycle Management Pack for Enterprise Manager Cloud Control. When adding a Standby Database, after proving all the […]
How a double entry for LAST_ARCHIVE_TIMESTAMP can stop your audit purge in 12c
In the last nine month we were busy migrating our 11g databases to 12c rel. 2. Due to time constrictions we decided early in the project to migrate 1:1. This meant that we did not make use of the CDB/PDB-feature. During the course of the upgrades my colleagues encountered a […]
InfluxDB for time series data
In this small blog post I will talk about when to use a time series database and why to use InfluxDB for this. But first of all I need to explain what time series data is. Time series data is data where the time aspect is the most important characteristic. […]
Querying connected data in Graph databases with Neo4j
TL;DR; · Graph databases are ideal for query use cases with data with complex relationships and layers of connections · Its query language is fast, efficient and allows for retrieval of information at deeper levels of abstraction in the data · Neo4j is currently the most popular Graph database, and […]
How to deploy InfluxDB in Azure using a VM service with dedicated storage
InfluxDB isn’t natively supported on Azure. This blog post will teach you how to deploy InfluxDB (or any other database) in a VM with a managed disk on the Azure platform. This will enable you to use this fast time-series database for your project. If the standard range of […]
Crash recovery of Oracle databases using Commvault
Some time ago I published an article about creating a RMAN backup using Commvault. See: https://technology.amis.nl/2018/03/01/creating-rman-backups-using-commvault/ . In this article I describe how to execute a crash recovery using Commvault. You need to do a crash recovery when you lost all your datafiles, redolog files, control files and spfile. For example […]
Upgrade of Oracle Restart/SIHA from 11.2 to 12.2 fails with CRS-2415
We are in the process of upgrading our Oracle Clusters and SIHA/Restart systems to Oracle 12.2.0.1 The upgrade of the Grid-Infra home on a Oracle SIHA/Restart system from 11.2.0.4 to 12.2.0.1 fails when running rootupgrade.sh with error message: CRS-2415: Resource ‘ora.asm’ cannot be registered because its owner ‘root’ is not […]
How to fix Dataguard FAL server issues
One of my clients had an issue with their Dataguard setup, after having to move tables and rebuild indexes the transport to their standby databases failed. The standby databases complained about not being able to fetch archivelogs from the primary database. In this short blog I will explain what happened […]
Upgrade OEM 12.1.0.5 OMS to OEM13.2
Despite the potential of Oracle Management Cloud, I’m a fan of Oracle Enterprise Manager, with its small imperfections. So when noticing a 12c Oracle Enterprise Manager on Linux, and targets on Windows, I took the challenge of modernizing the customer’s environment a bit. And the starting point , 12.1.0.5 for […]
Oracle Open World 2017; day 1 observations
Just a quick note about day 1 at Oracle Open World. This Sunday traditionally is filled with presentations of usergroups, customers and product management, and at the end of the day the welcome-keynote. In the presentations there’s not really exciting news, they will have to wait until the keynotes. But […]
ETL using Oracle DBMS_HS_PASSTHROUGH and SQL Server
While I prefer a “loosely coupled architecture” for replication between Oracle and SQL Server, sometimes a direct (database) link cannot be avoided. By using DBMS_HS_PASSTHROUGH for data extraction the 2 other ETL processes (transformation and load) can be configured and administered with more flexibility, providing an almost acceptable level of […]
Golden Gate 12c and DIY Sequence Replication with PL/SQL
Recently, while migrating AIX 11gR2 Databases to Oracle Linux 12cR1 on an ODA X5-2, our setup of Sequence Replication by Oracle Golden Gate appeared to be faulty. The target side sequences were not automatically incremented. The problem came to light during the migration of acceptance databases, and under some time […]
Dump Oracle data into a delimited ascii file with PL/SQL
This is how I dump data from an Oracle Database (tested on 8i,9i,10g,11g,12c) to a delimited ascii file: Next to the query and the generated filename the Dump_Delimited function takes another 6 parameters, each one with a default value. Check out the PL/SQL, and BTW… the basics for this code […]
DIY Parallelization with Oracle DBMS_DATAPUMP
Oracle dbms_datapump provides a parallel option for exports and imports, but some objects cannot be processed in this mode. In a migration project from AIX 11gR2 to ODA X5-2 ( OL 5.9 ) 12c that included an initial load for Golden Gate, I had to deal with one of those […]
How About Oracle Database 12c Threaded_Execution
THREADED_EXECUTION Threaded_Execution is an Oracle Database 12c feature aiming to reduce the number of Oracle processes on LINUX. After setting parameter THREADED_EXECUTION on TRUE and a database bounce, most of the background processes are threads within just 6 Oracle processes, where more than 60 processes existed before the bounce. And […]
Oracle 12c STIG Password Generator in PL/SQL
Creating or modifying an Oracle Database user password can be done by using any standard password generator you can find on the WEB. But I wanted a password to comply to “ora12c_strong_verify_function”, and this isn’t as easy to generate as you might expect. Though most generators provide options to include […]
Serial database backups using Oracle Enterprise Manager
For several years now, I’ve been a fan of backups controlled by Enterprise Manager (of Grid Control, or Cloud Control, call it what you want). It is relatively easy to configure backups for your databases and it is well known in our DBA world. Only one thing bothered me though, […]
One of the many nice new features in 12c database: code based access control
Topic of this blog is a nice new feature in 12c, not the plsql package I built that’s using it. So here’s the story.. For one of our customers we needed to have a simple schema comparison tool that would be able to check, as part of application deployment activity, […]
Node.js and Oracle NoSQL Database
Oracle NoSQL Database is an interesting option to consider when you want a schemaless, fast, scale-able database which can provide relaxed (eventual) consistency. Oracle provides a Node.js driver for this database. In this blog I’ll describe how to install Oracle NoSQL database and how to connect to it from a […]
Oracle Database Appliance X6-2S and X6-2M, filling the gap
Oracle introduced the ODA X6-2S and ODA X6-2M. And no, they are not the successor of the quite popular ODA X5-2, which we installed and configured quite a number of times lately. These 2 new machines are the in-between ODA’s, entry level machines for small business. Single node with flash […]
Oracle Database 11g: Virtual columns
Views in the Oracle database have several uses. You can use them to provide a view of data in different tables as a single object to query. You can use views to achieve a virtualization layer. Also views can be used to provide a user specific view of data. Implementing […]
Using guaranteed restore points to navigate through time
Some months ago I worked on a project where we thought it would be a good idea to use multiple guaranteed restore points to be able to navigate through time back and forth. Sadly we ran into some unexpected behaviour of the Fast Recovery Area. Prologue. This was basically a […]
Oracle licenses needed while monitoring your (non-)Oracle environment with OEM
With Oracle Enterprise Manager you are able to monitor an Oracle environment as well as a non-Oracle environment, like Microsoft’s SQL Server. You are also able to exchange notifications between Oracle Enterprise Manager and e.g. Microsoft’s SCOM, and there’s a choice how to monitor this heterogeneous environment. One of the […]
12c DBCA skips database-patching activities
Thanks to colleague Patrick Roozen I learned that there’s a tiny little flaw in Oracle databases 12c patch procedures when using DBUA (upgrade) and DBCA (create custom database) when SPU/PSU/BP are installed on top of the base release. This is described by Mike Dietrich in a recent blog. Oracle Support […]
A Short Note on Pesky CSSCAN Results Before a Characterset Change
A client of mine was busy correcting one of his development streets and therefore the NLS charactersets of about 10 11.2.0.4 EE databases had to be changed. Most of the databases were originally configured with WE8ISO8859P15, others with WE8ISO8859P1 and all of them had to get WE8MSWIN1252. Some of them […]
Security Features of Standard Edition (One) – Part 2
or Some Musings on the Security Implications of Oracle Database Initialization Parameters Still following the steps of a database installation, this article will muse about some Initialization Parameters with security relevance. In order to make a Standard Edition database as secure as possible we could start by looking what is […]
How-to bulk delete ( or archive ) as fast as possible, using minimal undo, redo and temp
Deleting some rows or tens of millions of rows from an Oracle database should be treated in a completely different fashion. Though the delete itself is technically the same, maintaining indexes and validating constraints may have such a time and resource consuming influence that a vast amount of undo and […]
How-to set the current database schema of an application using a global context
Question: We have customers who want to work with different database schemas and we want to determine dynamically – depending on a choice during or after login – which database schema the application in a given session should use. Is it possible to set this up in the database? Answer: […]
Security Features of Standard Edition (One) – Part 1
or A closer look on database hosts The last couple of years quite a few organizations had a difficult time and internal costs where a main focal point was to save money. License costs was one thing where many IT-managers put their question marks and so often the decision was […]
Cloud Control Agent 12c managing many objects
An Oracle Enterprise Management Agent 12c that has to manage hundreds of objects needs extra tweaking. If not, it will not start, or it will die soon, leaving you with an unmanaged infrastructure. Situation The customer runs over 200 databases on 1 host. Intel bases host with 384GB RAM and […]