Infrastructure at your Service
By Franck Pachot . Delphix is a tool for easy cloning of databases. The idea is that all is automated: the user can create a clone, rewind or refresh it with one click. However, I was suprised that the following common scenario is not managed by the Delphix engine: You clone from production, say Oracle 12c You upgrade the clone, say Oracle 19c You test there You refresh the clone from production, obviously being back…
By Franck Pachot . Oracle Error: ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01152: file … was not restored from a sufficiently old backup ORA-01110: data file … This error is one of the most misunderstood recovery error. It can happen in different case but I have here a simple example to reproduce it, with some comments and queries to see the state. I run an example to avoid long theory…
By Franck Pachot . With Oracle there are many ways to set a state in the session: context variables, set with DBMS_SESSION.SET_CONTEXT and retrieved with SYS_CONTEXT, or package global variables, or global or private temporary tables with ON COMMIT PRESERVE ROWS,… How would you do it in PostgreSQL? I’m taking an example from a Yugabyte slack question, as Yugabyte SQL layer is the PostgreSQL one. CREATE TABLE employees ( employee_no integer PRIMARY KEY, name text,…
By Franck Pachot . In a previous post I tried to build a glossary about Amazon DynamoDB terms that look like relational database terms, but with a different technical meaning. Here is more about it. If you work with AWS Databases and frequently switch between DynamoDB and Aurora, or other RDS databases, you may be confused by the same terms used for different meanings. An index is a redundant structure that is maintained by the…
By Franck Pachot . This is a little demo, easy to copy-paste, if you want to play with Kubernetes on your laptop. And, not a simple “Hello World” but a real database running here, able to scale up and down with full availability. Install Virtualbox I use Oracle VirtuaBox because I’m a big fan of Oracle products, especially when they are good and free. However, you can use Hyper-V (then just skip this paragraph and…
By Franck Pachot . This post is something I discovered by chance when writing about tagging SQL statement with recognizable comments. We know that Oracle is introducing more and more artificial intelligence and machine learning in the database engine, but here is the first time I see something where random or hash values seems to bring some meaning. There are two common ways to run a query and find it in V$SQL: add some tag…
By Franck Pachot . Should CPU-intensive logic be done in the DB or in application server? Here was a answer found in Reddit: Comment from discussion xpiv’s comment from discussion "Should CPU-intensive logic be done in the DB or in application server?". Reducing the CPU usage on the database server is a major cost saver with commercial databases, like Oracle Enterprise Edition where you pay license per core. But even in Open Source, on-premises or…
By Franck Pachot . I spent 30 minutes to try to understand this error with a query that I’ve run in many environments for years. So if you google it you may arrive here and the solution may be checking whether you have NLS_COMP=LINGUISTIC SQL> alter session set nls_comp=linguistic; Session altered. SQL> alter session set nls_language=french; Session modifiee. SQL> select * from ( 2 select 42 snap_id,’TM’ type,’DB time’ name,0 microseconds from dual 3 )…
By Franck Pachot . In the NoSQL times, it was common to hear thinks like “SQL is bad”, “joins are bad”, “foreign keys are bad”. Just because people didn’t know how to use them, or they were running on a database system with a poor implementation of it. MySQL was very popular because easy to install, but lacking on many optimization features that you find in other open source or commercial databases. Sometimes, I even…
By Franck Pachot . I never remember that in order to use instance caging you need to set a Resource Manager Plan but don’t need to set CPU_COUNT explicitly (was it the case in previous versions?). Here is how to test it quickly in a lab. SQL> startup force ORACLE instance started. SQL> show spparameter resource_manager_plan SID NAME TYPE VALUE ——– —————————– ———– —————————- * resource_manager_plan string SQL> show spparameter cpu_count SID NAME TYPE VALUE…
