Oracle Application Express Hands-On in Brasília!

Hello all!

I’m happy to announce here the next GUOB Meetup in Brasília focused on APEX .

The meeting will be conducted by

When?
Next October 9th!
2PM to 6PM

Where?
Ed. Corporate Financial Center (Oracle).
Setor Comercial Norte Q 2 – North Wing, Brasilia – Federal District

Cost? FREE
Just confirm you presence here.

IMPORTANT: bring your laptop.

Agenda:
2PM – Autonomous Database and Oracle Application Express – Monica Godoy
4PM – Coffee-break
4:30PM – Dynamic Actions in Action – Anderson Ferreira
5:30PM – Open Mic
6PM – Closure

NewScreenshot 2019-09-23 às 16.42.37

Oracle Application Express Hands-On in São Paulo!

Hello all!

I’m happy to announce here the next GUOB Meetup in São Paulo focused on APEX .

The meeting will be conducted by Monica Godoy, Product Manager do Oracle Application Express.

When?
Next October 7th!
6PM to 8PM

Where?
Rua Dr. José Áureo Bustamante, 455

Cost? FREE
Just confirm you presence here.

IMPORTANT: bring your laptop.

Agenda:
6PM – Autonomous Database and Oracle Application Express – Monica Godoy
7:30PM – Open Mic
8PM – Closure

NewScreenshot 2019-09-23 às 16.48.00

2º Meetup GUOB 2019 – Indexes and Partitioning

Hello all!

I’m happy to announce here the next Meetup to happen in Campinas nex October 5th. It’s being organized by GUOB and PL/SQL Camp.

When?
Next October 5th!

Where?
Av João Erbolato, 376, Jd Chapadão · Campinas

Cost? FREE
Just confirm your presence here.

IMPORTANT: bring your laptop.

Agenda
Oracle Indexes Myths“, by Ricardo Portilho Proni.
Oracle Partitioning for Devs: Why, When and How“, by Franky Weber Faust.

Schedule
09:00 – Opening
09:30 – Session “Oracle Indexes Myths
10:30 – Coffee break
11:00 – Session “Oracle Partitioning for Devs: Why,When and How
12:00 – Closure

Speakers
Ricardo Portilho Proni
Franky Weber Faust

Don’t miss it!

ORA-00600: internal error code, arguments: [kghfrempty:ds]

Hi all,
Annoying thing, just because that sounded veeery unsual to me. I would expect a different error.

I have seen this “[kghfrempty:ds]” in past a couple times, and if you did see it too you may have noticed, this is almost as much generic as the ORA-600 itself. It happens because this is usually in the top of KGH errors in general. Some examples:

1.

kghnerror <- kghfrempty <- kghfrempty_ex <- qerhjFreeSpace
<- qerhjFetch <- qersoProcessULS <- qersoFetch <- opifch2 <- kpoal8
<- opiodr <- kpoodrc <- rpiswu2 <- kpoodr <- upirtrc
<- kpurcsc <- kpufch0 <- kpufch <- OCIStmtFetch2 <- qksanExecSql <- qksanAnalyzeSql

2.

kghfrempty <- kghfrempty_partial <- kghgex <- kghfnd <- kghalo <- kghgex <- kghalf <- ktmrProcessCRClone 
<- ktmbRead <- ktmrget_int <- ktmrget <- kdmsTransGet <- kdst_fetch_imc  <- kdsttgr  <- qertbFetch

And as being it can be related either to analyze bugs (as per stack 1), In-Memory Column Store – IMC (as per stack 2), create views, rollback over cluster, trigger creation, use function based indexes, or several other generic situations (there is no pattern, as you can see).

So what do to if you get this message? Well, generically, read all the MOS notes available and try to match you case. IF you match, pray to have a bug for your current version, or open an SR to either have a backport or start a new investigation.

A few examples of things to check:

  • ALERT: Bug 13605839 ORA-600 [ktbsdp1] ORA-600 [kghfrempty:ds] ORA-600 [kdBlkCheckError]. Corruption in Rollback with Clusterwide Global Transactions in RAC (Doc ID 1527740.1)
  • SQL Analyze Fails with ORA-600[kghfrempty:ds] (Doc ID 2247180.1)
    Bug 21556276 – dataguide createviewonpath hit ora 600 [kghfrempty:ds] (Doc ID 21556276.8)
  • Bug 20878625 – ORA-600[kghfrempty:ds] with IMC enabled (Doc ID 20878625.8)
  • Bug 19212166 – ORA-600 [KGHFREMPTY:DS] With Cache Line Alignment Enabled (Doc ID 19212166.8)
  • Bug 6797925 – ORA-600 [kghfrempty:ds] During Creation Of Trigger In XML Schema (Doc ID 6797925.8)
  • 11.1: ORA-600 [17147] AND [kghfrempty:ds] On Select Using Functional Indexes (Doc ID 884882.1)
  • ORA-600 [kghfrempty:ds] And ORA-600 [kghrcdepth:ds] While Gathering Statistics (Doc ID 1480132.1)

BUUT I just noticed some undocumented scenario causing this and it might help you on fixing this as well.
I noticed I was getting this error for different queries but always from same application. So I addressed a complete review on application layer and noticed the JDBC 7 on this application. Happens we had just upgraded from 12.1 to 19c.

SO, after upgrading jdbc, it started to work again properly and no MOS SR was required. So lesson learned: When you receive this kghfrempty:ds], speacially after migration, also check for your application JDBC version.

For compatibility Matrix: Starting With Oracle JDBC Drivers – Installation, Certification, and More! ( Doc ID 401934.1 )

Hope it helps, cheers!

Oracle Container for Kubernetes Hands-On – Porto Alegre

Hello all!

I’m happy to announce here the next Meetup in Porto Alegre speaking over Oracle Container for Kubernetes  and how Oracle works with Cloud Native.

The meeting will be conducted by Diogo Shibata, from Oracle.

When?
Next October 2nd!

Where?
ATTIVE Coworking – Rua Carlos Gardel nº 55 – Bela Vista – Porto Alegre

Cost? FREE
Just confirm you presence here.

IMPORTANT: bring your laptop.

WhatsApp Image 2019-09-23 at 22.54.55.jpeg

guobguors

Oracle cloud computing hand on

Oracle and others cloud providers are doing nice stuff building their cloud strategies.

Oracle is granting U$ 300 to quickly deploy OLTP /DW and other technology platforms.

With cloud and distributed infrastructure we can code, test, deploy and quickly build any lab purpose as a free* offer.

Let’s review and run the fastest & free sandbox machines for cloud improvements purpose.

cloud 1.PNG

It runs good enogth 🙂

cloud 2

Quickly deploy machines. And receive classic middleware connection files.

\o/

cloud 3

It run’s so easy,  but it may cost money/digital value as it ‘soon it burn’ credit at Oracle.

Why not to test it and build muilt-cloud solution?

Don’t forget security and data privacy standards and Oracle availability.

With this solution at cloud is so simple to run a ‘DB as a button’, it spent 2 minutes in my lab. It actually available on What is amazing time for typical lab or tech hand.

Let’s test it and CHEERS.

ORA-65532: cannot alter or drop automatically created indexes

Hello all,
Quickly walking through this, as it was new to me (as for everybody, I guess): So what if I’m not happy with Oracle algorithm and I want to drop and AUTO Index on 19c?

Should be easy, like “drop index XXXX;” right? Wrong. See the struggle:

SQL> drop index GREPORA."SYS_AI_9xu652x5fyu5i";
drop index GREPORA."SYS_AI_9xu652x5fyu5i"
                 *
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

OK, so let’s have a look on my auto created indexes. You can see them with flag AUTO as YES on query below:

SQL> select owner, index_name, auto, tablespace_name from dba_indexes natural where auto='YES';
OWNER              INDEX_NAME    AUTO    TABLESPACE_NAME
________ _______________________ _______ __________________
GREPORA  SYS_AI_9xu652x5fyu5i    YES     GREPORA
GREPORA  SYS_AI_few32swe423dw    YES     GREPORA
GREPORA  SYS_AI_94osd824n202f    YES     GREPORA

Ok, so I cannot drop, let me alter it and set it as unusable, then, as it would archive my intend anyway:

SQL> alter index ADMIN."SYS_AI_9xu652x5fyu5i" unusable;
alter index ADMIN."SYS_AI_9xu652x5fyu5i" unusable
 *
ERROR at line 1:
ORA-65532: cannot alter or drop automatically created indexes

Hmmm, so it means I cannot alter it as well?
Well, kind of, there is something I can do:

SQL> alter index ADMIN."SYS_AI_9xu652x5fyu5i" rebuild tablespace GREPORA2 online;
Index altered.

Noice, thanks for nothing. What is changed?
Well, you can use a workaround to move it to a new tablespace and then drop the tablespace:

SQL> drop tablespace GREPORA2 including contents;
Tablespace dropped.

And index will be gone:

SQL> select owner,index_name,auto,tablespace_name from dba_indexes natural where auto='YES';
OWNER              INDEX_NAME    AUTO    TABLESPACE_NAME
________ _______________________ _______ __________________
GREPORA  SYS_AI_few32swe423dw    YES     GREPORA
GREPORA  SYS_AI_94osd824n202f    YES     GREPORA

OK, so that’s bad, right… What the worse part? If you simply drop it, Oracle will probably recreate it, based on algorithm, right?
Gee, thanks for nothing (again)!

This, though, is easier to change: Simply alter the Audo Indexing to report mode:

exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT');

This is interesting right?
Ok, we have more options, we can even exclude specific schemas from AUTO_INDEX, choose a default tablespace, define retentions and a lot of things.

As usual, the best reference I could find: https://oracle-base.com/articles/19c/automatic-indexing-19c

Just one thing you might want to consider and it’s not on Tim Hall’s post. Specially if the index is bad for one specific query, is to avoid using it during that specific query. We can do it with session/system parameter:

“_optimizer_use_auto_indexes”=OFF

Well, hope it helps you. Cheers!

Getting Oracle version – new utility on 18c oraversion

While scripting on an environment with different Oracle versions, I often needed to get the Oracle version for the target database as usually the SQL to be executed is version depended.

I used different methods to grab the database  version some you can see below:

SQL> select 
substr(
substr(
banner, 
instr(banner,' ',-(length(banner)-instr(banner,'.')),1)+1, 
instr(banner,' ',+instr(banner,'.'),1)-instr(banner,' ',-(length(banner)-instr(banner,'.')),1)-1
),
1, 
instr(
substr(
banner, 
instr(banner,' ',-(length(banner)-instr(banner,'.')),1)+1,
instr(banner,' ',+instr(banner,'.'),1)-instr(banner,' ',-(length(banner)-instr(banner,'.')),1)-1
),
'.'
)-1
) version
from v$version
where rownum = 1
; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

VERSION
--------------------------------------------------------------------------------
18

Or like this

SQL> select substr(version,1,instr(version,'.')-1) version from product_component_version where product like 'Oracle%';

VERSION
--------------------------------------------------------------------------------
18

But on Oracle 18c there is a new utility called oraversion which you can get the same result as the above queries.

[oracle@server01 ~]$ oraversion
This program prints release version information.
These are its possible arguments:
-compositeVersion: Print the full version number: a.b.c.d.e.
-baseVersion: Print the base version number: a.0.0.0.0.
-majorVersion: Print the major version number: a.
-buildStamp: Print the date/time associated with the build.
-buildDescription: Print a description of the build.
-help: Print this message.
[oracle@server1 ~]$

[oracle@server01 ~]$ oraversion -majorVersion
18
[oracle@server01 ~]$

This could be somewhat useful but I though it was worth sharing.

Until next time.

Elisson Almeida

Make IT 2019: Slovenian Oracle Users Group (SIOUG) Conference 2019!

Hello all,

I’m very happy to announce I’ll be at this year Slovenia Oracle Users Group annual event, in Portorož, at Kongresni center Portus. When? October 14-15th.

This is one of 6 events on Oracle Groundbreakers EMEA Tour 2019.

There will be:

  • 65+ Slots
  • 6+ Tracks
  • 2+ Workshops

More info:

 

Just a few pics from 2018 to give a taste…

NoSQL Brasil 2019! October 5th!

Hi all,
Just passing by to remind you: Don’t lose this chance!

We’ll have a great and unique event in Brasil to discuss NoSQL Solutions and I will be there too! Didn’t know? Check all about NoSQL Brasil here!

NewScreenshot 2019-09-03 às 19.45.34.png

When? October 5th, 2019!

Where? FIAP Auditorium. 1222, Lins de Vasconcelos Av. São Paulo-SP.

Complete Agenda? Here!

How to confirm me presence? >>>Click here<<<<!

The team of speakers is awesome: Otavio Santana, Gonzalo Urday, Leandro Domingues and Igor de Paula. I’m glad to be among those exponents, sharing about Oracle NoSQL.

My session will be “Oracle NoSQL 101 for Oracle RDBMS DBAs“, where I’ll be sharing specifically from a RDBMS DBA perspective: What are the advantages of NoSQL in comparison to classic RDBMSs? How to integrate traditional data with new NoSQL structures? This session compares Oracle NoSQL with solutions for different proposes like Document, Columnar, Key-Value, Cache Systems e Graph databases, besides explore concepts like Data Integration and Oracle Maximum Availability (MAA) for Oracle NoSQL.

See you there!