This blog coincides with the start of the Long Bank Holiday Weekend.
With us all being on lockdown, this means that we need to find a new way to spend it.
Whatever it is you may be doing, #staysafestayhome
Here’s some reading material, videos and presentations for you to plough through while you’re at home.
Blogs of the week
1. While Upgrading From GI 12C to 19C +ASM1(19C) Does Not See + ASM2(12C)
Rene Antunez writes, “A few weeks ago I was working on a GI upgrade from 12.2 to 19.6, but after running the upgrade in node 1 +asm1(19c) does not see +asm2(12c). Below you will see what I did and how it was fixed.”
Read the full blog for his screenshots and fix instructions.
2. Active Data Guard – limitations on ROWTYPE
Connor McDonald says, “I had an AskTOM question come in with an issue trying to PL/SQL on an Active Data Guard (ADG) database (which of course is running in read-only mode). The PL/SQL block seems innocuous; it does not DML and yet refuses to run…”
3. Design Tips for Query Rewrite
Dani Schnider begins by saying, “I try to reduce the concerns about complexity with some general design tips for Query Rewrite. Materialized Views are very useful to increase the performance of frequently executed queries. In many data warehouses and other database applications, Materialized Views are accessed directly using SQL. However, using Query Rewrite is much more elegant and flexible: The query optimizer decides automatically whether a suitable Materialized View can be used to speed up the query.”
He goes through:
- Enable Query Rewrite
- Always Use Oracle Join Syntax in Materialised Views
- Use Constraints on Base Tables
- Additional Tops for Data Warehouses
- Refresh Materialised Views in a Suitable Way
- Reduce the Number of Materialised Views
- Verify Capabilities with DBMS_MVIEW Procedures
- Read the Oracle Documentation
4. PostgreSQL Denial Of Service Attack
This blog begins with: “In this blog post I am going to simulate a potential Denial of Service attack by hammering the postgresql database with temporary table that will exhaust the PostgreSQL PGDATA directory.”
Martin Giffy D’Souza lets us know:
“Aside from the obvious, it’s been disappointing for a lot of people in our community seeing all the cancelled conferences. Conferences are a great time to learn and meet new people. I was recently talking about this to Adrian Png and Trent Schafer and we decided to create a one-day mini conference called APEX@Home. Since our initial discussion others have gotten involved (Joel Kallman, Shakeeb Rahman, Peter Raganitsch, and Niels de Bruijn) and it’s blown up into a full one day 24-hour conference – for FREE!”
This will take place on April 16th.
Subscribe using this link here.
6. Add Certificates to Wallets Automatically with DevOps
Sean writes, “Configuring a database to connect to SSL/TLS secured sites is one of those things I do often enough to know there’s a procedure but not frequently enough that I breeze through it painlessly. I usually find my way back to Ruben de Vries’ excellent post on the topic. The openssl commands to pull certificates directly onto a host saves the tedium of opening a browser, downloading and transferring files to a database server. His explanation of the process and components is informative.”
7. ORA-01665: control file is not a standby control file
Satishbabu Gunukula shares the following:
“Users may see this error when trying to mount standby database after restore or during the switch over
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE MOUNT STANDBY DATABASE
*
ERROR at line 1:
ORA-01665: control file is not a standby control file
SQL> SELECT database_role FROM v$database;
DATABASE_ROLE”
He links to the following presentation:
This blog says, “Recently, I attended Oracle Groovy partner training with a room full of my interRel friends. Although I’m not the intended audience for this type of training (my nerdy coding days are long gone), I found it to be super useful.
I’ve sat in multiple, past presentations by Oracle where they’ve formally stated “Groovy is the future” and then gave some high level use cases. Those use cases made sense, but did not resonate with me until I took this training. This blog post was written to share some of that partner training wisdom to help explain why Groovy is the future.”
9. Oracle 19c Automatic Indexing: Index Created But Not Actually Used (Because Your Young)
Richard Foote says, “The following is an interesting example of how Oracle Automatic Indexing is currently implemented that can result in an Automatic Index being created but ultimately ignored by the CBO. To illustrate, we begin by creating a simple little table that has two columns of particular interest, CODE2 which has 100 distinct values and CODE3 which has only 10 distinct values. The data of both columns is very poorly clustered with data for both columns sprinkled throughout the table.”
10. Optimizing SQL with LIKE expression – 3
Quanwen Zhao says, “So far I’ve written two number of blog notes about how to optimize sql with LIKE expression (here and here). A couple of days ago I built another funny case including two different columns in the WHERE clause of SQL statement, the former is a like expression and the latter is a constant expression. Hence this post will focus on talking about how to optimize this kind of SQL query.”
This week on Twitter
RittmanMead posted Rittman Mead in conversation: Oracle Analytics Server
https://www.youtube.com/watch?v=6aeRKecddlk&feature=youtu.be&utm_content=buffercfc80&utm_medium=social&utm_source=rittmanmead-twitter&utm_campaign=buffer
Shayne Libby shared Oracle Database on YouTube with videos such as:
Paper.li
Stories from connor-mcdonald.com, blog.redpillanalytics.com and vlamis.com
Videos such as:
Autonomous Data Management
https://www.youtube.com/watch?v=a_C7nebCj3o&feature=emb_title
Better Audio for the Virtual Workplace