New slots for FREE OCI Certifications exams are OPEN!

Breaking news all!

New slots for the OCI free exam certifications are open, the earliest that I saw a opening was on July 6th, even the less we now have more time to study and no charge at all!

Click on the link  to book your exam for free.

In the last week all slots for the exams were full and I tried to schedule one for me but could not find any stop until May 15th which this program would end but now they are open but dont know until when so rush and schedule yours while they are available.

If you don’t know what I’m talking about.  Last March 30th , it was announced that OCI training and certification exams were free for 6 OCI related programs.

This lo’s of people are taking advantage of it as you have quality training (not enough for the certification alone but I will get to it), and most important the with no exam fee.

The trainings are good, video recordings similar to the ones I mentioned here in this post. Why I say it is not enough, because you have access only to the videos, not to any E-Kit related material which is complementary to the training so if you want to practice and get a bit deeper, which you will need if you want to pass, you will have to do some digging on your own.

I managed to take 2 certifications Autonomous Database Specialist and Oracle Cloud Infrastructure Foundations Associate. I scheduled 2 more as they are for July I will have time to prepare for them.

Also please read the OCI related FAQ for this program which will clear several points, like you have one free change per exam and If you schedule an exam directly through the Pearson VUE website, you won’t receive this discount.

Hope it helps and good luck

Elisson Almeida

ALTERNATE Archive log destination configuration that can help you – at least it helped me =)

Hi all,

I was working on a database that was waiting on a backup policy to be configured on the Netbackup side, so basically it had no backups. No news here right =)

The dev team did not wait for much to hammer down the database which caused the archive log generation spiked high.

They were planning to work all weekend long and I needed to make sure that the database was available.

I had a spare diskgroup that I could use when the FRA got full but I did not wanted to monitor the space usage along the weekend to change the archive log destination, also did not want set it up ahead of it causing to double the archive log being sent to both locations wasting space.

So, one active location at a particular time and if /when the 1st location filled up, it would switch to the secondary location.

The configuration that helped me with was the ALTERNATE parameter in the log_archive_dest_n.

The parameter “Specifies an alternate archiving destination to be used when the original destination fails.”

If you want you can read the all details of the parameter here 

Most of the configurations using this parameter are related to the Dataguard standby configurations but it works on this scenario as well.

This is how I used it:
*I used the noreopen configuration as I knew that the space issue would not be resolved during the weekend

alter system set log_archive_dest_1='location=use_db_recovery_file_dest noreopen alternate=log_archive_dest_2' scope=both sid'*';
alter system set log_archive_dest_2='+NEW_FLASH´ sid='*';
alter system set log_archive_dest_state_2= 'ALTERNATE' scope=both sid='*';

On the database, you can see the status of the archive destinations:

select dest_id, dest_name, status from v$archive_dest_status where status <> 'INACTIVE';

DEST_ID DEST_NAME STATUS
---------- -------------------- ---------
1 LOG_ARCHIVE_DEST_1 VALID
2 LOG_ARCHIVE_DEST_2 UNKNOWN

I saw this error in the alert when the space on the 1st destination exhausted (also some hiccups on the database´s services):

ORA-17502: ksfdcre:4 Failed to create file +FLASH
ORA-15041: diskgroup "+FLASH" space exhausted
ORA-16038: log 8 sequence# 1059 cannot be archived
ORA-19504: failed to create file ""

Checking the log_archive_dest parameter status, the 1st one got disabled and only the 2nd was is valid, the hickups were baralley noticed andthe database kept working all the time.

select dest_id, dest_name, status from v$archive_dest_status where status <> 'INACTIVE';

DEST_ID DEST_NAME STATUS
---------- -------------------- ---------
1 LOG_ARCHIVE_DEST_1 DISABLED
2 LOG_ARCHIVE_DEST_2 VALID

When the space issue was resolved, all I needed to do was to enable the 1st location and set the 2nd one again to alternate.

select dest_id, dest_name, status from v$archive_dest_status where status <> 'INACTIVE';

DEST_ID DEST_NAME STATUS
---------- -------------------- ---------
1 LOG_ARCHIVE_DEST_1 VALID
2 LOG_ARCHIVE_DEST_2 UNKNOWN

Thanks and hope it helps

Elisson Almeida

ASM: Process PPAx holds disk from being dropped

So I was trying to remove some no longer needed disks from a cluster: 1st I umounted the disgroups from the other cluster nodes and from the last one I executed the DROP DISKGROUP DG_NAME INCLUDING CONTENTS. Nothing new right, but the SA told me that the disks were still being accessed.

I checked with kfod and kfed and also using asmcmd lsdsk -g –candidate, the disks were there, avaliable to be used in another diskgroup but they were no a part of any diskgroup.

I tried to check using asmcmd lsod but it was not returning anything while using lsop and fuser I was able to see the process that was holding the disks, which was an asm process PPA7:
*the disks that I´m was looking for was from the db5*

ASMCMD> lsod --process ppa
Instance Process OSPID Path
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db1_data1p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db1_data3p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db1_flash1p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db1_redos1p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db1_redos6p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db1_redos7p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db2_data1p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db2_data3p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db2_flash1p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db2_redos1p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db2_redos2p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db3_data1p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db3_data6p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db3_flash1p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db3_redos1p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db3_redos2p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_ocrvoting2p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_ocrvoting5p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_ora_data1p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_ora_data2p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_ora_flash1p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_ora_redo1p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_ora_redo2p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_ora_redo3p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_ora_redo4p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db4_data1p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db4_data6p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db4_flash1p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db4_flash2p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db4_redos1p1
1 oracle@server01 (PPA7) 38908 /dev/mapper/mp_db4_redos2p1

[oracrs@server01 ~]$ fuser /dev/mapper/mp_db5_redos2p1
/dev/dm-99: 38908
[oracrs@server01 ~]$ lsof /dev/mapper/mp_db5_redos2p1
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
asm_ppa7_ 38908 oracrs 270u BLK 253,99 0t0 70663 /dev/mapper/../dm-99

Checking MOS I found some notes like Database Process Hold Locks Dropped ASM Disk (Doc ID 2159941.1) but still would not help me as there was no work around the issue.

So basicly I needed to kill the process but could I? It was a production system and kill ASM processes is not something that one should take lightly.

In the documentation the PPA process is found under the
Parallel Query Slave Process which made my life easier, right? Not really.

How to make sure that killing a process would not make the instance to go down?

There is a script and blog post from Tanel Poder which also helped me , and it is worth the reading,

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SYS@+ASM1>SELECT indx,ksuprpnm,TO_CHAR(ksuprflg,'XXXXXXXXXXXXXXXX')
FROM x$ksupr
WHERE BITAND(ksuprflg,4) = 4 ORDER BY indx
/ 2 3 4

INDX KSUPRPNM TO_CHAR(KSUPRFLG,
---------- ------------------------------------------------ -----------------
2 oracle@server01 (PMON) E
3 oracle@server01 (CLMN) E
4 oracle@server01 (PSP0) 6
5 oracle@server01 (VKTM) 6
6 oracle@server01 (GEN0) 6
7 oracle@server01 (MMAN) 6
13 oracle@server01 (PMAN) 6
15 oracle@server01 (LMON) 6
16 oracle@server01 (LMD0) 6
17 oracle@server01 (LMS0) 6
20 oracle@server01 (LCK1) 6
21 oracle@server01 (DBW0) 6
22 oracle@server01 (LGWR) 6
23 oracle@server01 (CKPT) 6
24 oracle@server01 (SMON) 6
25 oracle@server01 (LREG) 6
27 oracle@server01 (RBAL) 6
28 oracle@server01 (GMON) 6
31 oracle@server01 (IMR0) 6
32 oracle@server01 (LCK0) 6
38 oracle@server01 (ASMB) 6

Cutting the sotry short as my process was not the query output, I did some other testing and I was comfortable enough to kill it and release the disks to the SA team.

Hope it helps

Elisson Almeida

Oracle Patching with OPlan

Everyone that I’ve worked with knows that I don’t like patching (and sometimes I try to imagine who does), but they are necessary to corrects bugs and improve the Oracle software stability.

When you have a single node server with one database, the patch planing is no brainer but when you have a RAC with multiple nodes, different Oracle homes and so on, the planning and preparations start to get more complex and it is easy to miss or overlook a step in the planning which can lead to issues during your patching.

So to help me with all that I use oplan. Oplan is a tool which comes with along OPatch and you can get its latest version in patch 6880880

More informations on oplan can be found here: Oracle Software Patching with OPLAN (Doc ID 1306814.1)

OK, so what do I used it most for?

Generating the apply patching steps, which are very in handy:

$ORACLE_HOME/OPatch/oplan/oplan generateApplySteps <bundle patch location>

And my favorite, rollback steps, which I have done more times that I would like to admit:

$ORACLE_HOME/OPatch/oplan/oplan generateRollbackSteps <bundle patch location>

Also as rollback, I do tar of the oracle binaries being patched prior as there times even the rollback did not work :-/

Both files will be created under the directory below and you will see an html and text files.

$ORACLE_HOME/cfgtoollogs/oplan/<TimeStamp>/

This process is to help you organise your steps, read it through prior executing to make sure it makes sense in your environment

Oplan has its limitations, from the Oracle note which I mentioned above:

Data Guard configurations are not supported.
OPlan can be used to create patch plans for Oracle home's running Oracle Data Guard configurations, but OPlan does not consider such an environment usable as 'Data Guard Standby-First Patch Apply' alternative. See the following for additional information on 'Data Guard Standby-First Patch Apply'

<Document 1265700.1> Oracle Patch Assurance - Data Guard Standby-First Patch Apply

Shared Oracle Home Configurations are not supported.

Single Instance Databases running in the same configuration are not supported

Even so I would still use it as it generates a plan based on your target environment adding more information that you would need to do manually if you were only to read the README files from the patching

Hope it helps.

Thanks and until next time

Elisson Almeida

Parallel file transfer on Linux

Hi all,

I had a request to copy a ton of files from one file system to another,  I know that there are tools that can help with that like rsync but due to some requirements and me wanted to do some scripting I put something together  to help with this request. This is not the 1st time I do something like this but it is the 1st time I share 🙂

What I’m sharing is now what I did for the request I mentioned but you will get an idea

The script will copy a list of files from one server to another. This list I usually create by using find like this

find /Directory_which_I_want_to_copy -type f > file_list.txt

The script will receive some parameters as listed below

parallel_xfer.ksh    

Also a requirement for this to work is that you can ssh to the target server without a password.

It will keep X parallel sessions running at all times until there are new files to start copying it, After all copies are started, it will monitor them until completion. Also the script assumes that the source and target directory destination is the same but this is easily changed if needed.

The logging needs to be improved but it will show the file it started as well their processes count

Hope it helps

Elisson

#!/bin/ksh
DBLIST=${1}
DEST_DIR=${2}
SERVER=${3}
NUM_SESS=${4}
STARTED_COUNT=0
RUN_COUNT=0

trim() {
    local var=$@
    var="${var#"${var%%[![:space:]]*}"}"   # remove leading whitespace characters
    var="${var%"${var##*[![:space:]]}"}"   # remove trailing whitespace characters
    echo "$var"
}


FILE_COUNT="$(cat ${DEST_DIR}/$DBLIST | wc -l)"
cd ${DEST_DIR}
for FILE in $(cat $DBLIST)
do
 STARTED_COUNT=$((${STARTED_COUNT}+1))
 if [ ${RUN_COUNT} -le ${NUM_SESS} ]
 then
   sftp -Cq USER@${SERVER}:${FILE} ${DEST_DIR}/. >/dev/null 2>/dev/null &
   echo "`date` - Transferring file ${FILE} to ${DEST_DIR} - ${STARTED_COUNT}/$(trim ${FILE_COUNT})"
   sleep 5
 fi
 echo "\n"

 RUN_COUNT=$(ps -ef | grep " ${$} " | grep sftp | grep -v grep | wc -l)

 while [ ${RUN_COUNT} -ge ${NUM_SESS} ]
 do
  RUN_COUNT=$(ps -ef | grep " ${$} " | grep sftp | grep -v grep | wc -l)
  echo "`date` - $(trim ${RUN_COUNT}) transfer processes running"
  echo "`date` - Amount of GB transferred `du -sg ${DEST_DIR}`\n"
  sleep 60
 done
done

while [ $(ps -ef | grep " ${$} " | grep sftp | grep -v grep | wc -l) -gt 0  ]
do
 RUN_COUNT=$(ps -ef | grep " ${$} " | grep sftp | grep -v grep | wc -l)
 echo "`date` - $(trim ${RUN_COUNT}) transfer processes running"
 echo "`date` - Amount of GB transferred - `du -sg ${DEST_DIR}`\n"
 sleep 60
done
echo "`date` - Transfered completed"

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

Quick ways to transfer files to Oracle Support – MOS directly from the Database Server

Hi all,

This will be a quick one but helped me a lot last week.

I was working on an Ora-0600 issue on a 2 node RAC cluster. Working with Oracle Support I was asked to transfer a TFA data as well ADRCI data so they could move forward with the SR, very common process right?

After I generated all the files, it was over 3gb of data to be sent over. If I was to get the file from the database server to my laptop I would need to transfer these files multiple times due to the jump servers in the middle.

This time I was luck as the database server was able to ping the site https://transport.oracle.com so I tried sending the files directly to MOS.

I will show you how I did it. This is not a mistery but can really save you time

You have 2 options which are straight forward

Using curl

curl -T FILE_YOU_WANT_TO_SEND -u MOS_USER https://transport.oracle.com/upload/issue/SR_NUMBER/

Using tfacfl

tfactl upload -sr SR_NUMBER -user MOS_USER FILE_YOU_WANT_TO_SEND

On both you need to provide your MOS credentials the SR number and the file you want o upload

After the upload is done, you will see in the attachments in the SR that the files is with the status like “Transferring to the SR” after a few minutes later you will see the update on the SR saying that the file was uploaded to the SR.

Hope this helps!

Elisson Almeida

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

Restore table statistics using dbms_stats

Hi all, another quick as reference. Table statistics as everyone knows are  very important when the CBO is creating the execution plan. When you have new statistics Oracle will invalid the current execution plans affected by them and create new execution plans based on the new statistics. Most of the time it gets right  if that causes your execution plan to change for worse?

In this situations dba_tab_stats_history   view and dbms_stats.restore_table_stats procedures are your friends.

To validate set and check and set the stats history:

select dbms_stats.get_stats_history_retention from dual; 
exec dbms_stats.alter_stats_history_retention(30);

On dba_tab_stats_history you can view the last statistics available:

select table_name, stats_update_time, from dba_tab_stats_history where table_name='TABLE1' and owner='USER' order by 2 desc;

Use the dbms_stats.restore_table_stats to restore the statistics:

exec dbms_stats.restore_table_stats(ownname=>'USER',tabname=>'TABLE1',AS_OF_TIMESTAMP=>'DATE from column STATS_UPDATE_TIME on dba_tab_stats_history');

Hope it helps!

Elisson Almeida