Invalid system views after upgrading from 12c to 18c – X_$KGLOB and X_$KGLCURSOR

I was working on a client upgrade from 12c to 18c. The upgrade was done manually. After the dbupgrade 2 system views got invalid, X_$KGLCURSOR and X_$KGLOB

OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------ -------------------------------------------------- ---------------------------------------- -------
SYS X_$KGLOB VIEW INVALID
SYS X_$KGLCURSOR VIEW INVALID

Man system objects invalid after an upgrade is never good.

Troubleshooting, I got the view text and tried to execute its sql

SQL> select TEXT from dba_Views where view_name = 'X_$KGLOB';

TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT "ADDR","INDX","INST_ID","CON_ID","KGLHDADR","KGLHDPAR","KGLHDCLT","KGLNAOWN","KGLNAOBJ","KGLFNOBJ","KGLNADLK","KGLNAHSH","KGLNAHSV","KGLNATIM",
"KGLNAPTM","KGLHDNSP","KGLHDNSD","KGLHDLMD","KGLHDPMD","KGLHDFLG","KGLHDOBJ","KGLHDLDC","KGLHDIVC","KGLHDEXC","KGLHDLKC","KGLHDKMK","KGLHDDMK","KGLHDA
MK","KGLOBFLG","KGLOBSTA","KGLOBTYP","KGLOBTYD","KGLOBHMK","KGLOBHS0","KGLOBHS1","KGLOBHS2","KGLOBHS3","KGLOBHS4","KGLOBHS5","KGLOBHS6","KGLOBHS7","KG
LOBHD0","KGLOBHD1","KGLOBHD2","KGLOBHD3","KGLOBHD4","KGLOBHD5","KGLOBHD6","KGLOBHD7","KGLOBPC0","KGLOBPC6","KGLOBTP0","KGLOBT00","KGLOBT01","KGLOBT02"
,"KGLOBT03","KGLOBT04","KGLOBT05","KGLOBT35","KGLOBT06","KGLOBT07","KGLOBT08","KGLOBT09","KGLOBT10","KGLOBT11","KGLOBT12","KGLOBT13","KGLOBT14","KGLOB
T15","KGLOBT16","KGLOBT17","KGLOBT18","KGLOBT19","KGLOBT20","KGLOBT21","KGLOBT22","KGLOBT23","KGLOBT24","KGLOBT25","KGLOBT26","KGLOBT28","KGLOBT29","K
GLOBT30","KGLOBT31","KGLOBT27","KGLOBT32","KGLOBT33","KGLOBWAP","KGLOBWCC","KGLOBWCL","KGLOBWUI","KGLOBWDW","KGLOBT42","KGLOBT43","KGLOBT44","KGLOBT45
","KGLOBT46","KGLOBT47","KGLOBT49","KGLOBT50","KGLOBT52","KGLOBT53","KGLOBTL0","KGLOBTL1","KGLOBTS0","KGLOBTS1","KGLOBTN0","KGLOBTN1","KGLOBTN2","KGLO
BTN3","KGLOBTN4","KGLOBTN5","KGLOBTS2","KGLOBTS3","KGLOBTS5","KGLOBTT0","KGLOBCCE","KGLOBCCEH","KGLOBCLA","KGLOBCLC","KGLOBCCC","KGLOBTS4","KGLOBCBCA"
,"KGLOBT48","KGLOBDSO","KGLOBDEX","KGLOBDPX","KGLOBDLD","KGLOBDIV","KGLOBDPS","KGLOBDDR","KGLOBDDW","KGLOBDBF","KGLOBDRO","KGLOBDCP","KGLOBDEL","KGLOB
DFT","KGLOBDEF","KGLOBDUI","KGLOBDCL","KGLOBDAP","KGLOBDCC","KGLOBDPL","KGLOBDJV","KGLOBACS","KGLOBTS6","KGLOBTS7","KGLOBT54","KGLOBT55","KGLOBT56","K
GLOBT57","KGLOBDCO","KGLOBDCI","KGLOBDRR","KGLOBDRB","KGLOBDWR","KGLOBDWB","KGLOBT58","KGLOBDOR","KGLHDMTX","KGLHDMVL","KGLHDMSP","KGLHDMGT","KGLHDDMT
X","KGLHDDVL","KGLHDDSP","KGLHDDGT","KGLHDBID","KGLHDBMTX","KGLHDBVL","KGLHDBSP","KGLHDBGT","KGLOBT59","KGLOBDCU","KGLOBPROP","KGLNACON","KGLNAO1N","K
GLHDMAR","KGLMPFLG","KGLOBAQP","KGLOBT60","KGLOBT61","KGLIMSCANS","KGLIMBYTESUNCOMP","KGLIMBYTESINMEM" FROM X$KGLOB


1 row selected.
SQL> SELECT "ADDR","INDX","INST_ID","CON_ID","KGLHDADR","KGLHDPAR","KGLHDCLT","KGLNAOWN","KGLNAOBJ","KGLFNOBJ","KGLNADLK","KGLNAHSH","KGLNAHSV","KGLNATIM","KGLNAPTM","KGLHDNSP","KGLHDNSD","KGLHDLMD","KGLHDPMD","KGLHDFLG","KGLHDOBJ","KGLHDLDC","KGLHDIVC","KGLHDEXC","KGLHDLKC","KGLHDKMK","KGLHDDMK","KGLHDAMK","KGLOBFLG","KGLOBSTA","KGLOBTYP","KGLOBTYD","KGLOBHMK","KGLOBHS0","KGLOBHS1","KGLOBHS2","KGLOBHS3","KGLOBHS4","KGLOBHS5","KGLOBHS6","KGLOBHS7","KGLOBHD0","KGLOBHD1","KGLOBHD2","KGLOBHD3","KGLOBHD4","KGLOBHD5","KGLOBHD6","KGLOBHD7","KGLOBPC0","KGLOBPC6","KGLOBTP0","KGLOBT00","KGLOBT01","KGLOBT02","KGLOBT03","KGLOBT04","KGLOBT05","KGLOBT35","KGLOBT06","KGLOBT07","KGLOBT08","KGLOBT09","KGLOBT10","KGLOBT11","KGLOBT12","KGLOBT13","KGLOBT14","KGLOBT15","KGLOBT16","KGLOBT17","KGLOBT18","KGLOBT19","KGLOBT20","KGLOBT21","KGLOBT22","KGLOBT23","KGLOBT24","KGLOBT25","KGLOBT26","KGLOBT28","KGLOBT29","KGLOBT30","KGLOBT31","KGLOBT27","KGLOBT32","KGLOBT33","KGLOBWAP","KGLOBWCC","KGLOBWCL","KGLOBWUI","KGLOBWDW","KGLOBT42","KGLOBT43","KGLOBT44","KGLOBT45","KGLOBT46","KGLOBT47","KGLOBT49","KGLOBT50","KGLOBT52","KGLOBT53","KGLOBTL0","KGLOBTL1","KGLOBTS0","KGLOBTS1","KGLOBTN0","KGLOBTN1","KGLOBTN2","KGLOBTN3","KGLOBTN4","KGLOBTN5","KGLOBTS2","KGLOBTS3","KGLOBTS5","KGLOBTT0","KGLOBCCE","KGLOBCCEH","KGLOBCLA","KGLOBCLC","KGLOBCCC","KGLOBTS4","KGLOBCBCA","KGLOBT48","KGLOBDSO","KGLOBDEX","KGLOBDPX","KGLOBDLD","KGLOBDIV","KGLOBDPS","KGLOBDDR","KGLOBDDW","KGLOBDBF","KGLOBDRO","KGLOBDCP","KGLOBDEL","KGLOBDFT","KGLOBDEF","KGLOBDUI","KGLOBDCL","KGLOBDAP","KGLOBDCC","KGLOBDPL","KGLOBDJV","KGLOBACS","KGLOBTS6","KGLOBTS7","KGLOBT54","KGLOBT55","KGLOBT56","KGLOBT57","KGLOBDCO","KGLOBDCI","KGLOBDRR","KGLOBDRB","KGLOBDWR","KGLOBDWB","KGLOBT58","KGLOBDOR","KGLHDMTX","KGLHDMVL","KGLHDMSP","KGLHDMGT","KGLHDDMTX","KGLHDDVL","KGLHDDSP","KGLHDDGT","KGLHDBID","KGLHDBMTX","KGLHDBVL","KGLHDBSP","KGLHDBGT","KGLOBT59","KGLOBDCU","KGLOBPROP","KGLNACON","KGLNAO1N","KGLHDMAR","KGLMPFLG","KGLOBAQP","KGLOBT60","KGLOBT61","KGLIMSCANS","KGLIMBYTESUNCOMP","KGLIMBYTESINMEM" FROM X$KGLOB;
SELECT "ADDR","INDX","INST_ID","CON_ID","KGLHDADR","KGLHDPAR","KGLHDCLT","KGLNAOWN","KGLNAOBJ","KGLFNOBJ","KGLNADLK","KGLNAHSH","KGLNAHSV","KGLNATIM","KGLNAPTM","KGLHDNSP","KGLHDNSD","KGLHDLMD","KGLHDPMD","KGLHDFLG","KGLHDOBJ","KGLHDLDC","KGLHDIVC","KGLHDEXC","KGLHDLKC","KGLHDKMK","KGLHDDMK","KGLHDAMK","KGLOBFLG","KGLOBSTA","KGLOBTYP","KGLOBTYD","KGLOBHMK","KGLOBHS0","KGLOBHS1","KGLOBHS2","KGLOBHS3","KGLOBHS4","KGLOBHS5","KGLOBHS6","KGLOBHS7","KGLOBHD0","KGLOBHD1","KGLOBHD2","KGLOBHD3","KGLOBHD4","KGLOBHD5","KGLOBHD6","KGLOBHD7","KGLOBPC0","KGLOBPC6","KGLOBTP0","KGLOBT00","KGLOBT01","KGLOBT02","KGLOBT03","KGLOBT04","KGLOBT05","KGLOBT35","KGLOBT06","KGLOBT07","KGLOBT08","KGLOBT09","KGLOBT10","KGLOBT11","KGLOBT12","KGLOBT13","KGLOBT14","KGLOBT15","KGLOBT16","KGLOBT17","KGLOBT18","KGLOBT19","KGLOBT20","KGLOBT21","KGLOBT22","KGLOBT23","KGLOBT24","KGLOBT25","KGLOBT26","KGLOBT28","KGLOBT29","KGLOBT30","KGLOBT31","KGLOBT27","KGLOBT32","KGLOBT33","KGLOBWAP","KGLOBWCC","KGLOBWCL","KGLOBWUI","KGLOBWDW","KGLOBT42","KGLOBT43","KGLOBT44","KGLOBT45","KGLOBT46","KGLOBT47","KGLOBT49","KGLOBT50","KGLOBT52","KGLOBT53","KGLOBTL0","KGLOBTL1","KGLOBTS0","KGLOBTS1","KGLOBTN0","KGLOBTN1","KGLOBTN2","KGLOBTN3","KGLOBTN4","KGLOBTN5","KGLOBTS2","KGLOBTS3","KGLOBTS5","KGLOBTT0","KGLOBCCE","KGLOBCCEH","KGLOBCLA","KGLOBCLC","KGLOBCCC","KGLOBTS4","KGLOBCBCA","KGLOBT48","KGLOBDSO","KGLOBDEX","KGLOBDPX","KGLOBDLD","KGLOBDIV","KGLOBDPS","KGLOBDDR","KGLOBDDW","KGLOBDBF","KGLOBDRO","KGLOBDCP","KGLOBDEL","KGLOBDFT","KGLOBDEF","KGLOBDUI","KGLOBDCL","KGLOBDAP","KGLOBDCC","KGLOBDPL","KGLOBDJV","KGLOBACS","KGLOBTS6","KGLOBTS7","KGLOBT54","KGLOBT55","KGLOBT56","KGLOBT57","KGLOBDCO","KGLOBDCI","KGLOBDRR","KGLOBDRB","KGLOBDWR","KGLOBDWB","KGLOBT58","KGLOBDOR","KGLHDMTX","KGLHDMVL","KGLHDMSP","KGLHDMGT","KGLHDDMTX","KGLHDDVL","KGLHDDSP","KGLHDDGT","KGLHDBID","KGLHDBMTX","KGLHDBVL","KGLHDBSP","KGLHDBGT","KGLOBT59","KGLOBDCU","KGLOBPROP","KGLNACON","KGLNAO1N","KGLHDMAR","KGLMPFLG","KGLOBAQP","KGLOBT60","KGLOBT61","KGLIMSCANS","KGLIMBYTESUNCOMP","KGLIMBYTESINMEM" FROM X$KGLOB
*
ERROR at line 1:
ORA-00904: "KGLOBT24": invalid identifier
SQL> select TEXT from dba_Views where view_name = 'X_$KGLCURSOR'

TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT "ADDR","INDX","INST_ID","CON_ID","KGLHDADR","KGLHDPAR","KGLHDCLT","KGLNAOWN","KGLNAOBJ","KGLFNOBJ","KGLNADLK","KGLNAHSH","KGLNAHSV","KGLNATIM",
"KGLNAPTM","KGLHDNSP","KGLHDNSD","KGLHDLMD","KGLHDPMD","KGLHDFLG","KGLHDOBJ","KGLHDLDC","KGLHDIVC","KGLHDEXC","KGLHDLKC","KGLHDKMK","KGLHDDMK","KGLHDA
MK","KGLOBFLG","KGLOBSTA","KGLOBTYP","KGLOBTYD","KGLOBHMK","KGLOBHS0","KGLOBHS1","KGLOBHS2","KGLOBHS3","KGLOBHS4","KGLOBHS5","KGLOBHS6","KGLOBHS7","KG
LOBHD0","KGLOBHD1","KGLOBHD2","KGLOBHD3","KGLOBHD4","KGLOBHD5","KGLOBHD6","KGLOBHD7","KGLOBPC0","KGLOBPC6","KGLOBTP0","KGLOBT00","KGLOBT01","KGLOBT02"
,"KGLOBT03","KGLOBT04","KGLOBT05","KGLOBT35","KGLOBT06","KGLOBT07","KGLOBT08","KGLOBT09","KGLOBT10","KGLOBT11","KGLOBT12","KGLOBT13","KGLOBT14","KGLOB
T15","KGLOBT16","KGLOBT17","KGLOBT18","KGLOBT19","KGLOBT20","KGLOBT21","KGLOBT22","KGLOBT23","KGLOBT24","KGLOBT25","KGLOBT26","KGLOBT28","KGLOBT29","K
GLOBT30","KGLOBT31","KGLOBT27","KGLOBT32","KGLOBT33","KGLOBWAP","KGLOBWCC","KGLOBWCL","KGLOBWUI","KGLOBWDW","KGLOBT42","KGLOBT43","KGLOBT44","KGLOBT45
","KGLOBT46","KGLOBT47","KGLOBT49","KGLOBT50","KGLOBT52","KGLOBT53","KGLOBTL0","KGLOBTL1","KGLOBTS0","KGLOBTS1","KGLOBTN0","KGLOBTN1","KGLOBTN2","KGLO
BTN3","KGLOBTN4","KGLOBTN5","KGLOBTS2","KGLOBTS3","KGLOBTS5","KGLOBTT0","KGLOBCCE","KGLOBCCEH","KGLOBCLA","KGLOBCLC","KGLOBCCC","KGLOBTS4","KGLOBCBCA"
,"KGLOBT48","KGLOBDSO","KGLOBDEX","KGLOBDPX","KGLOBDLD","KGLOBDIV","KGLOBDPS","KGLOBDDR","KGLOBDDW","KGLOBDBF","KGLOBDRO","KGLOBDCP","KGLOBDEL","KGLOB
DFT","KGLOBDEF","KGLOBDUI","KGLOBDCL","KGLOBDAP","KGLOBDCC","KGLOBDPL","KGLOBDJV","KGLOBACS","KGLOBTS6","KGLOBTS7","KGLOBT54","KGLOBT55","KGLOBT56","K
GLOBT57","KGLOBDCO","KGLOBDCI","KGLOBDRR","KGLOBDRB","KGLOBDWR","KGLOBDWB","KGLOBT58","KGLOBDOR","KGLHDMTX","KGLHDMVL","KGLHDMSP","KGLHDMGT","KGLHDDMT
X","KGLHDDVL","KGLHDDSP","KGLHDDGT","KGLHDBID","KGLHDBMTX","KGLHDBVL","KGLHDBSP","KGLHDBGT","KGLOBT59","KGLOBDCU","KGLOBPROP","KGLNACON","KGLNAO1N","K
GLHDMAR","KGLMPFLG","KGLOBAQP","KGLOBT60","KGLOBT61","KGLIMSCANS","KGLIMBYTESUNCOMP","KGLIMBYTESINMEM" FROM X$KGLCURSOR


1 row selected.
SQL> SELECT "ADDR","INDX","INST_ID","CON_ID","KGLHDADR","KGLHDPAR","KGLHDCLT","KGLNAOWN","KGLNAOBJ","KGLFNOBJ","KGLNADLK","KGLNAHSH","KGLNAHSV","KGLNATIM",
"KGLNAPTM","KGLHDNSP","KGLHDNSD","KGLHDLMD","KGLHDPMD","KGLHDFLG","KGLHDOBJ","KGLHDLDC","KGLHDIVC","KGLHDEXC","KGLHDLKC","KGLHDKMK","KGLHDDMK","KGLHDA
MK","KGLOBFLG","KGLOBSTA","KGLOBTYP","KGLOBTYD","KGLOBHMK","KGLOBHS0","KGLOBHS1","KGLOBHS2","KGLOBHS3","KGLOBHS4","KGLOBHS5","KGLOBHS6","KGLOBHS7","KG
LOBHD0","KGLOBHD1","KGLOBHD2","KGLOBHD3","KGLOBHD4","KGLOBHD5","KGLOBHD6","KGLOBHD7","KGLOBPC0","KGLOBPC6","KGLOBTP0","KGLOBT00","KGLOBT01","KGLOBT02"
,"KGLOBT03","KGLOBT04","KGLOBT05","KGLOBT35","KGLOBT06","KGLOBT07","KGLOBT08","KGLOBT09","KGLOBT10","KGLOBT11","KGLOBT12","KGLOBT13","KGLOBT14","KGLOB
T15","KGLOBT16","KGLOBT17","KGLOBT18","KGLOBT19","KGLOBT20","KGLOBT21","KGLOBT22","KGLOBT23","KGLOBT24","KGLOBT25","KGLOBT26","KGLOBT28","KGLOBT29","K
GLOBT30","KGLOBT31","KGLOBT27","KGLOBT32","KGLOBT33","KGLOBWAP","KGLOBWCC","KGLOBWCL","KGLOBWUI","KGLOBWDW","KGLOBT42","KGLOBT43","KGLOBT44","KGLOBT45
","KGLOBT46","KGLOBT47","KGLOBT49","KGLOBT50","KGLOBT52","KGLOBT53","KGLOBTL0","KGLOBTL1","KGLOBTS0","KGLOBTS1","KGLOBTN0","KGLOBTN1","KGLOBTN2","KGLO
BTN3","KGLOBTN4","KGLOBTN5","KGLOBTS2","KGLOBTS3","KGLOBTS5","KGLOBTT0","KGLOBCCE","KGLOBCCEH","KGLOBCLA","KGLOBCLC","KGLOBCCC","KGLOBTS4","KGLOBCBCA"
,"KGLOBT48","KGLOBDSO","KGLOBDEX","KGLOBDPX","KGLOBDLD","KGLOBDIV","KGLOBDPS","KGLOBDDR","KGLOBDDW","KGLOBDBF","KGLOBDRO","KGLOBDCP","KGLOBDEL","KGLOB
DFT","KGLOBDEF","KGLOBDUI","KGLOBDCL","KGLOBDAP","KGLOBDCC","KGLOBDPL","KGLOBDJV","KGLOBACS","KGLOBTS6","KGLOBTS7","KGLOBT54","KGLOBT55","KGLOBT56","K
GLOBT57","KGLOBDCO","KGLOBDCI","KGLOBDRR","KGLOBDRB","KGLOBDWR","KGLOBDWB","KGLOBT58","KGLOBDOR","KGLHDMTX","KGLHDMVL","KGLHDMSP","KGLHDMGT","KGLHDDMT
X","KGLHDDVL","KGLHDDSP","KGLHDDGT","KGLHDBID","KGLHDBMTX","KGLHDBVL","KGLHDBSP","KGLHDBGT","KGLOBT59","KGLOBDCU","KGLOBPROP","KGLNACON","KGLNAO1N","K
GLHDMAR","KGLMPFLG","KGLOBAQP","KGLOBT60","KGLOBT61","KGLIMSCANS","KGLIMBYTESUNCOMP","KGLIMBYTESINMEM" FROM X$KGLCURSOR
2 3 4 5 6 7 8 9 10 11 12 13 14 15 
SQL> /
X","KGLHDDVL","KGLHDDSP","KGLHDDGT","KGLHDBID","KGLHDBMTX","KGLHDBVL","KGLHDBSP","KGLHDBGT","KGLOBT59","KGLOBDCU","KGLOBPROP","KGLNACON","KGLNAO1N","K
*
ERROR at line 13:
ORA-00904: "KGLHDMAR": invalid identifier

Describing the source tables used by the views, the columns that generated the error do not exist there. What?!

Looking up MOS – found this note, Invalid x_$ Objects After Upgrade (Doc ID 361757.1)

Although this note does not mention these views explicitly there is a part of that says that

"This is NOT a dictionary corruption, but the result of an unsupported operation.

.....

These x_$ views do not compile because they are based on x$ tables which have changed (columns dropped) and/or do not exist (X$ table dropped).

New (default) 10g & 11g databases do not have views with the prefix x_$.

Please note that this solution can be applied to any and all x_$ views found invalid after upgrade/migration.
These tables are built on x$ tables in the previous release and the same rules still apply."

So these are save to ignore or drop them.

Hope this helps.

Elisson Almeida

Waiting for ASM to startup after upgrading to Oracle 18c

Hi,
Something strange happened to me and I would like to share with you. I was upgrading one of my Virtual Machine which was an Oracle Restart – SIHA,  running 11.2.0.4 to 18c on Oracle Linux 6.9.

The upgrade went fine using the GUI no issues nor strange log messages, but the issue happened when I rebooted the VM. For some reason (which I don’t knew) the startup was hanging when trying to start HAS and then it outputted the error below:

PRCR-1070 :Failed to check if resource ora.asm is registered
CRS-0184 : Cannot communicate with the CRS daemon
Waiting for ASM to startup

Looking up on MOS I found this note – Oracle Linux 6 server hangs on boot with error: Waiting for ASM to startup (Doc ID 2495023.1)

The startup was hanging waiting on acfssihamount but as ASM was not up yet and it could not start.

To fix it you need to boot OS in rescue mode and execute

chroot /mnt/sysimage

Once you do that you will be able to change the startup configuration settings using chkconfig and disable acfssihamount

chkconfig --list acfssihamount
chkconfig acfssihamount off
chkconfig --list acfssihamount

After this exit out the rescue mode and the system should boot normally.

Hope it helps!

Elisson Almeida

Value FROM v$option where parameter = ‘Unified Auditing’

Hi all,
So, just a quick post to clarify once it cost me a few mins to explain this to a client and, indeed, Oracle could have made it easier.

So, facing the info below, doesn’t mean that you don’t have Unified Auditing enabled:

SELECT value FROM v$option WHERE parameter = 'Unified Auditing';
VALUE
-----------------
FALSE

It does mean you just don’t have PURE Unified auditing, but a MIXED configuration, where old fashioned Auditing is also accepted.

See, this way, the MIXED Auditing is by default it is enabled in 12c. The idea is that you use apart from traditional auditing all the features of unified auditing. Once we are comfortable with the unified concept, we can migrate exiting audit setup to unified policy , we can enable pure auditing. This serves as a good mediator for an easy and hassle free switch to the preferred Unified auditing.

HOWEVER, let’s agree that Oracle could have made the value column a little bit m ore meaningful, if this was the case. The actual values actually mean:

FALSE – > MIXED AUDTING
TRUE –> PURE AUDITING

OK, but what if I want to enable ONLY the PURE Auditing, disabling the ‘Classic’ Auditing and changing this value on v$option to TRUE? Well, then you need to relink library for your instance. Note, for RAC linking need to be done all the nodes.

Steps to Change to PURE Unified Auditing Only:

SELECT value FROM v$option WHERE parameter = 'Unified Auditing';
VALUE
-----------------
FALSE

sqlplus / as sysdba <<
shutdown immediate;
exit;

cd $ORACLE_HOME/rdbms/lib
 
make -f ins_rdbms.mk unaiaud_on ioracle
 
sqlplus / as sysdba<<
startup
 
SELECT value FROM v$option WHERE parameter = 'Unified Auditing';
VALUE
-----------------
TRUE

Reference: https://docs.oracle.com/database/121/DBSEG/auditing.htm#DBSEG824

Hope it helps, cheers!

Oracle New Visual Online Tools

Hi all,
So, after a long time of same old documentation style, Oracle started sharing some nice and visual pages for some specifics. Here are 2 examples:

1. Oracle Move to Cloud  (http://www.oracle.com/goto/move)
This one was shown to me by Ricardo Gonzalez, and it’s a GREAT tool if you are planning migrations to Oracle Cloud in general.

The part I like most is where you select the source and destination formats/versions you want and all recommended migration methods are suggested. Of course additional validation on database are required for each method, but it can potentially remind you of things you may be forgetting.

NewScreenshot 2019-05-08 às 16.56.46

Something else I like very much as well is the section with executive summaries of each migration tool. It’s basically ready for a print screen to put in the slides you are going to present to your boss… 🙂

NewScreenshot 2019-05-08 às 16.57.16.png

Also, you have handy some links to papers for the most different migration scenarios, as per:

NewScreenshot 2019-05-08 às 16.57.24

2. Oracle Database Features: (https://apex.oracle.com/database-features/)
I actually got it from this post by Portilho, and found it great! See how it looks like:

NewScreenshot 2019-05-08 às 16.52.14

And by selecting any of the features you can see more info in a very simple way, as per below. By the way, with one click you can be directed to the documentation (the old fashioned one):

NewScreenshot 2019-05-08 às 16.55.08

 

Hope you enjoy those as much as I did and hope Oracle continue providing those sort of interfaces to us!

Cheers!

Oracle: “Invisible” Locks!

Have you ever heard:
“My session was killed. I’m trying again. There is no locks. But My SQL doesn’t run!”

By default, when a session is disconnected from Oracle, the SQLs uncommitted are undone (rollback). So, in case of a long routing there is a probably a rollback in place.

How Oracle rollback take that long? Well, it’s Oracle mechanism. The RDBMS basically assume you know what your are doing and start writing the new blocks so the commit will be very quick. Which, however, is bad in case of rollbacks.

How to check if this is your case?

select ktuxeusn, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", ktuxesiz, ktuxesta
from x$ktuxe
where ktuxecfl = 'DEAD'
4 /

KTUXEUSN Time KTUXESIZ KTUXESTA
---------- -------------------- ---------- ----------------
31 07-APR-2018 16:48:53 17705 ACTIVE
48 07-APR-2018 16:48:53 108856 ACTIVE

Hm.. What can I do? Not much really, we need to wait the rollback to complete.
One thing that can be done to increase the rollback is changing the fast_start_parallel_rollback parameter, as per:

SQL> show parameters roll

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
rollback_segments string
transactions_per_rollback_segment integer 5
SQL> alter system set fast_start_parallel_rollback=HIGH;

System altered.

Hope it helps!
Cheers!

DG Broker ORA-16766: Redo Apply is stopped after a Database Restart

So, the other day I was engaged to check a dataguard, while checking its status with the dg broker I found this error

Error: ORA-16766: Redo Apply is stopped

Checking for it on dg broker:

DGMGRL> show configuration verbose

Configuration - DGCONFIG

Protection Mode: MaxPerformance
Databases:
PRIMARY - Primary database
STANDBY - Physical standby database
Error: ORA-16766: Redo Apply is stopped

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

GMGRL> show database verbose STANDBY

Database - STANDBY

Enterprise Manager Name: STANDBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 3 seconds ago)
Apply Lag: 1 hour(s) 50 minutes 6 seconds (computed 0 seconds ago)
Apply Rate: 828.00 KByte/s
Real Time Query: OFF
Instance(s):
STANDBY

Database Error(s):
ORA-16766: Redo Apply is stopped

Properties:
DGConnectIdentifier = 'STANDBY'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '900'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'STANDBY'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SB_SERVER)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STANDBY_DGMGRL)(INSTANCE_NAME=STANDBY)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'STANDBY_%r_%t_%s.arc'
TopWaitEvents = '(monitor)'

Database Status:
ERROR

The standby state was APPLY-ON but when checked the standby if the MRP process was running I found none!

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 4 17:05:12 2019

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

17:05:12 SYS@STANDBY AS SYSDBA> SELECT PROCESS, STATUS FROM v$managed_standby;

PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
RFS IDLE
RFS IDLE
RFS IDLE
RFS WRITING

8 rows selected.

Elapsed: 00:00:00.01

Checking further I found that the database was bounced while the MRP was running and that was the only explanation that I could see to see a difference between the dg broker also I could not find any error in the dg broker log $ORACLE_HOME/rdbms/log/drc*.log

After setting the state to apply-off and them to apply-on the issue was resolved.

DGMGRL> edit database STANDBY set state='apply-off';
Succeeded.
DGMGRL> edit database STANDBY set state='apply-on';
Succeeded.
DGMGRL> show database verbose STANDBY;

Database - STANDBY

Enterprise Manager Name: STANDBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 0 Byte/s
Real Time Query: OFF
Instance(s):
STANDBY

Properties:
DGConnectIdentifier = 'STANDBY'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '900'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'STANDBY'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SB_SERVER)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STANDBY_DGMGRL)(INSTANCE_NAME=STANDBY)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'STANDBY_%r_%t_%s.arc'
TopWaitEvents = '(monitor)'

Database Status:
SUCCESS

DGMGRL> show configuration

Configuration - DGCONFIG

Protection Mode: MaxPerformance
Databases:
PRIMARY - Primary database
STANDBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> exit

sqlplus / as sysdba
17:17:54 SYS@STANDBY AS SYSDBA> SELECT PROCESS, STATUS FROM v$managed_standby;

PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
ARCH CLOSING
RFS IDLE
RFS IDLE
RFS IDLE
RFS IDLE
MRP0 APPLYING_LOG

9 rows selected.

Elapsed: 00:00:00.00

Hope it helps, unti the next one.

Elisson Almeida

 

Oracle Security: Immediate Protection for JVM Exploits (CVE-2018-3110)

Hello all!

Now that CVE-2018-3110 is a hot topic, I think this is a pretty interesting topic to go on.

So, we all know this is consistently one of the components with more CVEs for Oracle Databases. Basically because you can create Java objects in the database (which I think is an abomination :D) and run this code there, usually doing some tricks to escalate privileges to DBA, to PDB, to CDB, to host and other CDBs…

The problem is that (before 18c) OJVM PSU Patches are not RAC Rolling installable. Which means will need a maintenance window to apply fixes for this component. Quite bad, hãn… And if you discover a vulnerability and the PSU window is only in a month or so?

Well, we have a solution 🙂
It is well described in MOS Oracle Recommended Patches — “Oracle JavaVM Component Database PSU and Update” (OJVM PSU and OJVM Update) Patches (Doc ID 1929745.1), under the name “Mitigation Patch”.

It basically consists in install a patch Patch 19721304: SCRIPT TO LOCK DOWN JAVA DEVELOPMENT, which is a Rolling Patch, which allows you to disable any new Java object to be created. This is, so, assuming exploits can be done by creating new java objects on DB (as most of Java CVEs). Also, this patch don’t have any version requirement (after 9i).

Having the patch, however, doesn’t mean you are automatically protected against any vulnerability, but means you can protect yourself temporary by disabling new java objects creation with “exec dbms_java_dev.disable;” anytime.

The Mitigation Patch does not remove Java objects or block any java execution, it only disable the creation of new Java objects, so if the exploit is already planted, it’s not a viable solution. The mitigation patch can be used in any scenario where the PSU or proper JVM fixes cannot be applied at the moment but it’s wanted to prevent against JVM vulnerabilities.

Now, before downloading the patch, first check if it’s not already installed to your home, as it’s part of some Boundle Patches, like “Database Bundle Patch : 12.1.0.2.180417 (27338029)“.

Important Note: The intent is to be like a “workaround” while the PSU is planned. This was not built to be definitive solution. The idea is to just disable new Java objects to be created until the fix is applied on the proper planned maintenance window.

Hope it helps!

Managing database changes with Sqitch

Nowadays everybody talks about continuous integration, automated test tools, and stuff like that. But almost every time it’s about testing applications. Let’s talk a little about Sqitch, “a database change manager” as they use to call themselves.

Let’s say you’re starting a new project and want to ensure every database change are managed and secured with tests? Just like with an app, you can write tests to ensure everything works and you can have a “revert” method for the changes too. Suppose you are changing a field type and start creating a backup column (fallback ;)), but something goes wrong on the meantime and for some reason, you can’t do a simple rollback… revert it and be happy.

What if your CI says that the application it’s fine and can be released to the production server but “a wild bug appears” and you NEED to revert to a previous version of your service, easy huh? With Docker and one command line, the service is up and running again. But what about the database changes your team did? Just call the revert and be happy :).

Have I mentioned that you can have multiples connection URI at the project and have different environments to apply the changes with a single command?!. Let’s see a few commands to create a simple MySQL database…

Create a folder, initialize GIT in it and initialize the Sqitch project.

mkdir awesomeapp
cd awesomeapp
git init .
sqitch init flipr --uri https://github.com/theory/sqitch-mysql-intro/ --engine mysql

Now you have a ready to go sqitch structure. Now let’s do some basic creates.

sqitch add createcustomeruser -n 'Creation of Customer table'

Add the create table script to the file inside the “deploy” dir.

CREATE TABLE customer (
id INTEGER PRIMARY KEY,
name VARCHAR(50)
);

Add the verify script, can be a simple select. Add the revert script, guess what’s the command, and after all, you can run the commands below and see the result.

sqitch deploy db:mysql://root@/awesomeapp
sqitch verify db:mysql://root@/awesomeapp
sqitch revert db:mysql://root@/awesomeapp

This was a quick explanation about Sqitch so go ahead and read more at their docs and tutorials, they are pretty good.

12c New Feature: Unused Privileges

Did you know 12c now has a mechanism to examine which privileges are actually being used by an user, module or for the database as a whole, as opposed to merely the privileges granted?
This is a great security improvement in order give users the privileges that they need precisely without granting too much.

Here’s how is works?

1. First you have to set up a capture process using the new package DBMS_PRIVILEGE_CAPTURE.

# Example:

BEGIN 
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE( 
        name         => 'all_priv_analysis_pol', 
        description  => 'database-wide policy to analyze all privileges', 
        type         => DBMS_PRIVILEGE_CAPTURE.G_DATABASE); 
END;
/

This starts the capture process database wide. Let it run for a bit.

2. Now you can either generate a report:

# Example

BEGIN 
DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT( 
        name         => 'all_priv_analysis_pol'); 
END;
/

Or examine the new views:

DBA_USED_PRIVS 
DBA_UNUSED_PRIVS 
DBA_USED_OBJPRIVS 
DBA_UNUSED_OBJPRIVS

3. To turn off you proceed with the following:

BEGIN 
DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE( 
        name         => 'all_priv_analysis_pol'); 
END;
/

So now you ensure that you use the best practice of least privilege for users.

Cheers!

11g Feature: Fine Grained Dependency Tracking (FGDT)

Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.

So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.

Ready? Here it goes:

11g Feature: Fine Grained Dependency Tracking (FGDT)

In previous versions, object dependencies were managed at the object level. This way, altering an object automatically invalidated all dependent objects.
Oracle 11g has more granular dependency management, so only changes that directly affect an object will cause an invalidation.

This is not a Feature under control of Dev or DBA, but I judged important to mention here, as per has important impact to development and deployment processes.

More details can be found here: http://www.orafaq.com/node/2683