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, […]
Oracle 12
Oracle Database Version 12cR1 related posts
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 […]
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 […]
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 […]
Getting started with Enterprise Modules’ ora_profile to install Oracle 18c
A couple of moths ago, Enterprise Modules announced the easy to start way of making use their Oracle modules, ora_profile. Recently their modules have been updated to support the on-premise release of Oracle 18c (18.3). In this post I’ll explain how to get started using Vagrant, VirtualBox and a little […]
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 […]
ORDS: Installation and Configuration
In my job as system administrator/DBA/integrator I was challenged to implement smoketesting using REST calls. Implementing REST in combination with WebLogic is pretty easy. But then we wanted to extend smoketesting to the database. For example we wanted to know if the database version and patch level were at the […]
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 […]
Getting started with Oracle Database in a Docker container!
One of the benefits of using Docker is quick and easy provisioning. I wanted to find out first-hand if this could help me get an Oracle Enterprise Edition database quickly up and running for use in a development environment. Oracle provides Docker images for its Standard and Enterprise Edition database […]
R and the Oracle database: Using dplyr / dbplyr with ROracle on Windows 10
R uses data extensively. Data often resides in a database. In this blog I will describe installing and using dplyr, dbplyr and ROracle on Windows 10 to access data from an Oracle database and use it in R.
ODA X6-2M – How to create your own ACFS file system
In this post I will explain how to create your own ACFS file system (on the command line) that you can use to (temporarily) store data. So you have this brand new ODA X6-2M and need to create or migrate some databases to it. Thus you need space to store […]
Create a 12c physical standby database on ODA X5-2
ODA X5-2 simplifies and speeds up the creation of a 12c database quite considerably with oakcli. You can take advantage of this command by also using it in the creation of physical standby databases as I discovered when I had to setup Dataguard on as many as 5 production and […]
after 27+ years in Oracle land I am forced to patch sqlplus
When starting to use sqlplus 12.2 I noticed that my SQL prompt was not changing to what login.sql told it to be. This did not happen in sqlplus 12.1 or lower versions. Maybe this is a bug, maybe a new feature I thought. Behaviour in 12.2 of sqlplus indeed has […]
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 […]
Better track the Usage of Database Options and Management Packs, or it will cost you
So here it is Oracle announces a license audit, some urgency kicks in and this familiar but also really serious question comes down from management: “Are we using any unlicensed database features“. The seriousness is quite understandable, because if so, the company can look forward to some negotiations with Oracle […]
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 […]
Oracle SQL Pattern Recognition – introducing the Match Recognize operator
Oracle Database Release 12c (12.1) introduced a new operator that we can use in SQL queries. The MATCH_RECOGNIZE operator allows us to detect patterns in our relational data. Specifically: it allows us to identify records that mark the beginning of a set of records that together form a pattern. In […]
New PL/SQL pragma (12cR2) to deprecate program units – sign of a modern programming language
Bryn Llewellyn (Distinguished Product Manager, Database Division, Oracle) presented at OOW2016 on new features in PL/SQL in Oracle Database 12cR2. One of the features that stood out was a new pragma deprecate that can be added to program units such as functions and procedures inside packages. This pragma is used […]
Next Step in Row generation in Oracle Database 12c SQL using JSON_TABLE
One of those things SQL developers are frequently looking at is the generation of rows: having a query return records that do not really exist. For example to generate test data or to produce records for all days in a month. Tom Kyte usually selects from data dictionary views. Various […]
You are in trouble when more than 8 kernel options are enabled in oracle executable
A brief headsup for everyone responsible for maintaining kernel options in the oracle executable. The environment where I discovered this is a 3 node racattack cluster with Oracle GI & RDBMS 12.1.0.2.0. After enabling Unified auditing in my 12.1.0.2 database I noticed that all of a sudden database startup started […]
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, […]
Virtual columns
Maarten wrote a post on Virtual Columns in the oracle database. I read a blogpost on preventing people issuing SELECT * on a table. This was done in a different database, so I decided to try it out in my Oracle Database. First I create a simple table: and I […]
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 […]
Implementing ORAchk in Oracle Enterprise Manager 13c
Heard about it at Oracle OpenWorld 2015: A new feature in Oracle Enterprise Manager 13c is the possibility to incorporate ORAchk, the healthcheck tool for databases, RAC and Engineered Systems. This post is about downloading (and importing), deploying and provisioning the ORAchk-plugin. Spoiler-alert: there’s downtime involved of the OMS. There […]
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 […]
Time is of the essence – The Fourth Dimension in Oracle Database 12c (on Flashback and Temporal Database)
Time has always been an important dimension for data in any database with topics like when was data created, when are records valid, how did records evolve over time, can we compare with yesteryear or even travel through time and data. The Oracle Database 12c release added a number of […]
Use DB Vault to protect password strength policy
Suppose your organization wants to enforce a security policy on database password strength. The DBA’s have implemented a password strength verification function in PLSQL such as the oracle supplied ora12c_strong_verify_function in the DEFAULT profile of the database. There seems no way to get around it at first: Database account u4 […]
Keeping track of your licenses with OEM12C – reports
Wouldn’t be nice to get regularly informed how (in)compliant you are with Oracle licenses in an easy – centralized – way, and therefore not have to worry about visits of Oracle’s LMS – License Management Services? I think that would be nice for the most of us. Running LMS-scripts on […]
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: […]
When to use the Oracle Database In-Memory option?
The application and usage of the Oracle Database In-Memory has been described by Pom Bleeksma in this post. Oracle Database In-Memory can result in huge improvement in application query performance. This post will answer the question: “what would be an optimal situation for using the Oracle Database In-Memory feature?” The […]
Oracle InMemory compared to indexing
In August 2014 Oracle released its RDBM 12.1.0.2 with a potentially useful and exiting new option: Database InMemory. Upon reading about it it became clear to me that this is a powerful option, worth examining deeper. This blog will briefly describe what InMemory is and what it isn’t. The emphasis […]
Getting my hands on a Virtual Machine with Oracle Database 12.1.0.2 Enterprise Edition – 20-30 minutes from start to finish
NOTE: even though the steps described in this article all work – I am running into a problem with shutting down and starting up the VM again. So at the present I can have the VM created and the database installed – however, I can not successfully restart it. I […]
A short guide to networking in Virtual Box with Oracle Linux inside.
This post is intended to be a dummy guide, best practices, or whatever you call it… about setting up a network while working with virtual boxes on a desk- or laptop. I was setting up a virtual box environment with several Virtual Boxes on my laptop, and as I’m not […]