Infrastructure at your Service

Tag

Oracle 12c Archives - Blog dbi services

Christian Weinfurtner

Upgrade to Oracle 19c – performance issue

By | Database Administration & Monitoring, Database management, Oracle | No Comments

In this blog I want to introduce you to a workaround for a performance issue which randomly appeared during the upgrades of several Oracle 12c databases to 19c I performed for a financial services provider. During the upgrades we ran into a severe performance issue after the upgrades of more than 40 databases had worked just fine. While most of them finished in less than one hour, we run into one which would have taken…

Read More
Mouhamadou Diaw

Duplicate Database on ODA X4

By | Database Administration & Monitoring, Database management, Operation systems, Oracle | No Comments

The ODA X4 is still in use for some customers. The last time I was asked to validate the backups . Let me explain the context. Actually the backups are done via RMAN on local in a NFS share. After these backups are backep up by Netback on tape. The goal was just to validate that the backups done by Netback can be used to restore if needed. So the backup teams restored the backups…

Read More
Franck Pachot

Oracle non-linguistic varchar2 columns to order by without sorting

By | Cloud, Oracle | No Comments

By Franck Pachot . Sorting data is an expensive operation and many queries declare an ORDER BY. To avoid the sort operation you can build an index as it maintains a sorted structure. This helps with Top-N queries as you don’t have to read all rows but only those from a range of index entries. However, indexes are sorted by binary values. For NUMBER or DATE datatypes, the internal storage ensures that the order is…

Read More
Franck Pachot

No{Join,GroupBy}SQL – Analytic Views for BI

By | Cloud, Oracle | No Comments

By Franck Pachot . Advocates of NoSQL can query their structures without having to read a data model first. And without writing long table join clauses. They store and query a hierarchical structure without the need to follow relationships, and without the need to join tables on a foreign key name, in order to get a caption or description from a lookup table. The structure, like an XML or JSON document, provides metadata to understand…

Read More
Franck Pachot

Oracle 12c – global partial index

By | Cloud, Database Administration & Monitoring, Oracle | No Comments

By Franck Pachot . We have an incredible number of possibilities with Oracle. Yes, an index can be global (indexing many partitions without having to be partitioned itself on the same key) and partial (skipping some of the table partitions where we don’t need indexing). In the previous post of this series of small examples on recent features I partitioned a table, with covid-19 cases per day and per country, partitioned on range of date…

Read More
Franck Pachot

Oracle 12c – reorg and split table with clustering

By | Cloud, Database Administration & Monitoring, Oracle | 2 Comments

By Franck Pachot . In this series of small examples on recent features, I have imported in a previous post, the statistics of covid-19 per day and per countries. This is typical of data that comes as a time-series ordered by date, because this is how it is generated day after day, but where you probably want to query from another dimension, like per countries. If you want to ingest data faster, you keep it…

Read More
Franck Pachot

Oracle 12c – peak detection with MATCH_RECOGNIZE

By | Cloud, Database Administration & Monitoring, Oracle | 2 Comments

By Franck Pachot . This post is part of a series of small examples of recent features. I’m running this in the Oracle 20c preview in the Oracle Cloud. I’ll show a very basic example of “Row Pattern Recognition” (the MATCH_RECOGNIZE clause in a SELECT which is documented as “row pattern matching in native SQL” feature by Oracle”). You may be afraid of those names. Of course, because SQL is a declarative language there is…

Read More
Franck Pachot

Oracle 12c – pre-built join index

By | Cloud, Database Administration & Monitoring, Oracle | No Comments

By Franck Pachot . This post is part of a series of small examples of recent features. I’m running this in the Oracle 20c preview in the Oracle Cloud. I have created a few tables in the previous post with a mini-snowflake scheme: a fact table CASES with the covid-19 cases per country and day. And a dimension hierarchy for the country with COUNTRIES and CONTINENTS tables. This title may look strange for people used…

Read More
Marc Wagner

Migrating Oracle database from windows to ODA

By | Database Administration & Monitoring, Oracle | 2 Comments

Nowadays I have been working on an interesting customer project where I had to migrate windows oracle standard databases to ODA. The ODAs are X7-2M Models, running version 18.5. This version is coming with Red Hat Enterprise Linx 6.10 (Santiago). Both windows databases and target ODA databases are running PSU 11.2.0.4.190115. But this would definitively also be working for oracle 12c and oracle 18c databases. The databases are licensed with Standard Edition, so migrating through…

Read More
Oracle Team

When you change the UNDO_RETENTION parameter, the LOB segment’s retention value is not modified

By | Database Administration & Monitoring, Database management, Development & Performance, Hardware & Storage, Oracle | No Comments

Below, I will try to explain, a particular case for the general error : ORA-01555 snapshot too old error.. Normally, when we have this error, we are trying to adapt the retention parameters or to tune our queries. SQL> show parameter undo; NAME TYPE VALUE ———————————— ———– —————————— temp_undo_enabled boolean FALSE undo_management string AUTO undo_retention integer 3600 –extended from 900, undo_tablespace string UNDOTBS1 But, are some scenarios where the above rule is not working . We…

Read More