Autoupgrade doesn’t Upgrade Timezone Version While RU Patching

Hi,

during some tests, I realized that autoupgrade tool (the magic tool for upgrade and patching) doesn’t upgrade timezone version of database. here is the environment:

Oracle Linux 8.6

ORACLE_HOME 1 :/u01/app/oracle/product/dbhome_1/dbhome_19_3
Database orcl created in 19.3 with default time zone version (V32)

ORACLE_HOME 2 :/u01/app/oracle/product/dbhome_1/dbhome_19_16
Time zone version is upgraded to V39.

this is autoupgrade config file:

as you see, “upg1.timezone_upg” is et to yes so it must be upgraded when “orcl” database is moved to 19.16 home but when autoupgrade completes its job. output of V$TIMEZONE_FILE shows that db time zone version is still v32. interesting part, there is no error in log files actually, on prechecks ( run autoupgrade in analyze mode first of course) it is not mentioning about time zone upgrade. Autoupgrade does not plan to upgrade time zone file and it didn’t 🙂

So I started to dig a little bit. I created a new database via dbca (using custom database template of course) on 19.16 home and timezone file version was v39 as expected. So, no problem about home.

Secondly, I  thought there might be some bugs/problems on 19.3 (base release) home and patched it to 19.8 recreate orcl database and run autoupgrade again but timezone version was still v32.

Thirdly, I installed an 18c home, created “orcl” database and run autoupgrade to move it on 19.16. Viola, it worked, after autoupgrade new timezone file version was v39. in prechecks this time, autoupgarde says that time zone will be updated and it did.

What is different between starting from 18c and 19.3? 18c has v31 as default time zone file and 19c has v32. maybe autoupgrade checks default time zone files first compare them (maybe hardcoded) and decide to make a timezone file upgrade. I don’t know just a speculation.

Or maybe autoupgrade does not upgrade timezone file while patching and only in upgrades! I asked this to Mike Dietrich and he told me this should be working and create an SR. So, I created an SR and a bug has been reported. I hope it will be fixed quickly. Be careful if you patching out of place and always check you time zone files.

wish you healthy, happy days.

EDIT: It turned out my guess is correct. autoupgrade is checking default timezone versions but while upgrading, it checks latest version and upgrade it too. that is why it doesn’t upgrade while RU patching, since both homes are 19c and default is 32 for both.

and again many thanks to Mike Dietrich, for his close interest and fast response. AU team already discussed the situation and will be solved soon.

Unique Columns For V$Sql

Hi,

Until yesterday I was saying SQL_ID and CHILD_NUMBER is enough to get a sql child cursor from V$SQL and today I know I was wrong.

Probably many times you needed the sql statement that a session is running. To do that you query V$session and find the session information and then use SQL_ID column to reach out V$Sql. if you write a query like this:

you can write any filter you want here but you will immediately realized that some session rows are multiplied. Why? Because you didn’t use “CHILD_NUMBER” while reaching v$sql. V$SQL is not main data source for unique sql statements in your database. V$SQLAREA is. So instead of v$sql, you can use v$sqlarea in this query but if you need specific information about the exact sql cursor that session is currently running then you must use v$sql.

Note: I will not go deep Parent-Child cursors here. basically, every sql statements has child cursors. they all same sql but for some reason they have different properties and your session is running a “child cursor”.

so your query should be like this:

and now you reached to exact correct sql child cursor that is running for that session. right? well, I did know so but I find out, it is not! On one of my clients system, I discovered that this query returns 2 rows from v$sql which means for same SQL_ID and CHILD_NUMBER there are 2 rows in v$sql. I couldn’t find anything on docs or google (maybe couldn’t search enough) and started to dig about this sql statements.

here is the necessary information about this sql:

almost everything is same but ADDRESS and CHILD_ADDRESS. CHILD_ADDRESS is the memory address that this cursor resides on so it must be different obviously. Only thing that create this difference is ADDRESS column. ADDRESS column in V$sql is the address of the PARENT cursor which is in V$SQLAREA view. So let’s check this view:

there is only one of them exists. So some of those child cursors in v$sql are not (I am not sure the term) “valid”. they might be used before (and they were in my example) but now they are remaining of a past sql. So which child cursor in v$sql is the sql statements that session is currently running? Now we need to use ADDRESS column too. SQL_ADDRESS column in v$session is the parent address of the sql statement. So, I should add it into my queries too:

thanks for reading. comments are welcome. wish you healthy happy days.

Should Run datapatch After DBCA

Yesterday, I was testing some stuff in database and I needed to create new homes/new databases (new life new beginning). at a point I realized that something is not right about database. One thing to another I found the reason and it was interesting to me.

I used DBCA to create database ( also I used General Purpose template which we shouldn’t use in a production environment. this was just a test and it was a virtual machine). starting from, I don’t know 12.2 I guess, dbca runs datapatch after database creation. datapatch will apply necessary sql patches into your database like RU (Revision Update) or one off patches. I waited dbca to complete its job and it completed without any error. if dbca says “it’s ok” then you don’t look more right? How many times did you check all log files that dbca produced? Anyway, during my actual tests, as I said before, I realized something is not right so I wanted to check if all patches are applied correctly. I checked CDB_REGISTRY_SQLPATCH view (this is a noncdb database by the way so I can use dba version of view too):

at first it seems OK to me but then I realized there were two “RU APPLY” rows with a small time difference. As you see all patch operations are done to container 0, They both applied to same container(which this is a noncdb database). Since it was around 02:40 AM, I went to bed and in the morning I run datapatch (by the way date of virtual machine is not correct). here is the output of datapatch:

pay attention on “Current state of release update SQL Patches:” section. it says for SQL registery: “Applied 19.16.0.0.0 Release_Update 220703022223 with errors on 02-NOV-22 07.04.56.062757 PM”. there is an error on update of this apply. I don’t know what that was and it also says 1 patch installed. Maybe I did something wrong during the installation. I rechecked registry:

here is a third row for 19.16 RU apply and from that point everything worked smoothly. So, there is no harm to run datapatch after creating a new database via dbca. You must run datapatch if you create your database with CREATE DATABASE command anyway. We must be careful out there, anything can happen 🙂 there is no harm run datapatch and see:

“Installation queue:
No interim patches need to be rolled back
No release update patches need to be installed
No interim patches need to be applied”

wish you all healthy, beautiful days.

APPLY with status WITH ERRORS in the PDB

Yesterday, I was doing some tests about noncdb to pdb conversions and during one of these tests I got a strange error. By the way, I am doing those tests because of a side effect of noncdb to pdb. I have already created an SR about it and write a post here later.

here is the environment in my test case; I installed Linux 8.6 on Virtual Box, Oracle 19c (19.16 RU) and two databases on same home, orcl and cdb19. orcl is noncdb database and cdb19 is the container database. I created both databases via dbca at the same time and dbca completed successfully (at least I thought so). Both databases are up and running. So I started noncdb to pdb conversion via autouptrade tool (latest version 22.5). I also tried, db link and unplug/plug methods too.

So after starting autoupgrade it completed successfully but when I login into database and open new pdb, it raises some errors. in pdb_plug_in_violations, I saw “APPLY with status WITH ERRORS in the PDB” error. For some reason, datapatch didn’t run correctly after dbca operation for orcl (noncdb) database. I run datapatch for orcl database again, it did some patching and re-run autoupgrade. After that operation completed and pdb created successfully.

by the way, my main problem still exists. this migrated pdb has its own data dictionary (which shouldn’t be because data dictionary is in cdb$root). anyway if you ever encounter with this error you can try datapatch again.

wish you all healthy happy days.

How to Check Empty Space in a Securefile LOB

Hi,

PS: please check last sql statement in this post for shorter and faster solution. first sql is for showing all information about the lob.

Yesterday, while checking segment space usage data in a client’s database and some of the LOB segments were absurdly big. So, I want to check what is in it and how much free space I can gain.

To do that, I cannot use just DBMS_LOB.GetLength function because for CLOB data type, it takes much more space than original data because of charsets (especially if you use AL32UTF8 charset) so I just want to check currently empty space in lob segment. if you need that kind of data then, DBMS_SPACE is the solution. DBMS_SPACE has a SPACE_USAGE procedure which checks a segment and  return space information but starting 12c, SECUREFILE is the default lob type and for securefile you cannot use same space_usage procedure with the one you use for tables.

space_usage has overloaded versions and second and third overloaded version (order in dbms_space package from top to bottom) is for SECUREFILE lob segments.

here is a sample script I wrote to check currently empty space in securefile lobs:

Caution: I check all LOBS in the database so, you might not want to do that, just modify sql and add necessary filters.

first with function block is to call dbms_space.space_usage procedure and return data in string, on second with clause (get_info) call the function and get lob information, by the way SEGMENT_TYPE for a lob segment is “LOB SEGMENT” but dbms_space.space_usage does not accept it as segment_Type, you must send “LOB” instead, then parse result as columns and list SEGMENT_SIZE_MB which is total size of LOB segment size in megabytes
USED_SIZE_MB which is actual used size in megabytes
EXPIRED_SIZE_MB which is expired data size (for versioning) in megabytes.

to calculate empty space just subtract used size from segment size. this is approximately empty size in your lob segment. if you have a partitioned table then you will get result for each partition (like mustafa.tmp table in the example). I hope this script helps in your job.

EDIT:

I forgot to mentioned why lob segments have empty spaces. biggest reason is delete operation of course. if you delete a big portion of the table (or lob segment) then deleted space will be considered as empty space. So, if you delete on a table, should you shrink the lob segment or table? NO! that space will be used by the table again. if this is your standard operation then empty spaces in table/lob segment will be reused but let’s say you inserted many rows by a mistake or because of a bug in your code and then deleted those rows. if you have enough disk space for the database then there is nothing to worry about (mostly). That table will get many inserts in time and it will use that space eventually but if too much space is reserved in the table / lob segment and maybe you have a disk space issues then you can reclaim it (by shrinking or moving table/lob segment). So, this is not a regular thing.

EDIT 2:

sql above is aiming to show all information about lob like used space, total space, expired space etc. probably , you will just need to see empty space in lob. so you can use this sql statement. this will work faster because no regexp usage and parsing operations. I also subtracted “unexpired bytes” from total size since this space is still in use.

 

 

thanks for reading, your comments are always welcome. wish you healthy days.

catcon.pl cheat sheet

Hello,

Oracle uses a container database architecture permanently anymore, you should be familiar with “catcon.pl” perl file. Let’s say you have container database with 10 different PDBs and you need to run a script file on all of those pdbs (maybe even in root and seed too) so what you need is catcon.pl.

catcon.pl will automatically execute your script file and run it on all or specified containers. Since it is a perl file it must be called with perl binary (perl in linux, perl.exe in windows) and you can find it under $ORACLE_HOME/perl/bin directory and catcon.pl itself is under $ORACLE_HOME/rdbms/admin .

so, you have a sql file to run under the /home/oracle/scripts directory called myfile.sql. let’s see how we can execute it in all pdbs:

that is it. myfile.sql file will be run in all containers (including root and seed) with the command above. Let’s dissect the command:

$ORACLE_HOME/perl/bin/perl => PERL binary to run catcon.pl
$ORACLE_HOME/rdbms/admin/catcon.pl => catcon.pl path
-d /home/oracle/scripts => directory where script is located.
-b MyScript => Prefix for the log files so you can separate them from other scripts logs.
myfile.sql => actual script name

actual script file must be the last parameter. It is that easy. in this example ORACLE_HOME and ORACLE_SID should be set before you run the command otherwise you cannot connect to the database. This is a local connection and by default it is a SYSDBA connection. So whatever you run will be run under SYS user.

To add some more functionality:

  1. -c : list of containers that you want to execute the script:
    ….catcon.pl -c ‘SALES PDB1 TEST’ => execute the script only at SALES, PDB1 and TEST pdbs.
  2. -C : list of containers that you don’t want to execute the script.
  3. -l : log directory
  4. -s : spool the output of script files.
  5. -S : (capital S) don’t execute the script in root and seed. (Edit: this option also don’t set “_oracle_scripts” parameter as true, so if you are creating a user it won’t be Oracle Maintained user)
  6. -u : specify username/password.
  7. -z : ez-connect string
  8. -n : number of workers. if you want to execute a script for 100 pdbs then you can increase the number of workers to execute script simultaneously on different pdbs. default is 1

Can we use catcon.pl to run a script on a remote server? Yes with a combination of -u and -z

run myfile.sql script under /home/oracle/scripts directory on CDBX database and only specified pdbs (PDB1…PDB8) which is on mydbserver.mydomain server using mustafa user and create 2 worker process so script will be run on 2 pdbs at a time and write log files under /home/oracle/logs.

catcon has more parameters but I just write about the ones I used most.

thanks for reading.

Expanded controlfile section n from nnn to nnn records

Hello everyone,

I have been already write revealing information that control file stores: https://mustafakalayci.me/2020/08/11/control-file-content-and-the-limits/.

Data in control file is separated into “SECTIONS” and you can see those sections name (type) via V$CONTROLFILE_RECORD_SECTION view. for example, database separates a section for “BACKUP SET”s. every section has a record size limit . All those information can be queried via v$controlfile_record_section of course.

RECORDS_TOTAL number of records allocated for that section.
RECORDS_USED number of used records.

You might encounter an alert log message from time to time like “Expanded controlfile section 11 from 100 to 200”. this is a message that database need to store more data in control file and currently allocated space is not enough, so oracle expands the control file. in this sample message, 100 is current RECORDS_USED and RECORDS_TOTAL. since all records are filled, it expands to 200, so RECORDS_TOTAL become 200. how about the section? what is section 11? unfortunately there is no SECTION_ID but V$controlfile_record_section always return rows in specific order and you can use this select to learn section ids:

So, 11 is ARCHIVED LOG. which means to be able to store currently required (based on your deletion policy in rman) database need to increase number of records in control file.

Database needs to increase those sections when required data to be stored increase. You can see the alert log messages above when this occur.

wish you all good, healthy days.

Audits are not Purged

Hello,

This week I realized that some of my databases’ audit data are not purged and audit tables get bigger. I already have a scheduler job to purge my audit records but somehow job has been completed successfully but audit records are not deleted. This is 19.15 version, NON-CDB databases.

I am using unified audit in all of my databases and purge audit records based on last archive timestamp. here is a sample code to purge unified audit records:

This code could belong to Tim Hall (www.oracle-base.com)  not sure. This basically sets a date to delete audit records which are before that date. So, to understand the problem I started to digging but didn’t find much (before starting a trace, I found it out).

I checked what the saved last time stamp is:

that is interesting because this is NON-CDB database but it has 2 different database_id for same AUDIT_TRAIL type. One of them is actual id (2158381708) of current database but other one is unknown (at first). Also, for the rows with 2158381708 database id, last_archive_ts is correct but the other one was pretty old. So, some of my databases has this not purged audit problems and all of them has double rows in this data dictionary view. So, I thought there might be a bug and maybe code gets an too many rows error. Simply I deleted foreign database id rows. base table of DBA_AUDIT_MGMT_LAST_ARCH_TS is SYS.DAM_LAST_ARCH_TS$. I deleted rows:

then I re-run audit purge code and it worked!

Of course, I didn’t stop at here. Why there were different rows on this table? while checking database id’s I realized that all databases I had this problem are duplicated databases. I duplicated PROD to create a TEST or ACP for example and every time I duplicate a database I always change database id with “nid” tool.

I learned that after nid, those table remain to store old rows. I deleted old database id rows from all databases and problem solved. purge operation is a success. this is something new that I will add to my duplicate database steps. if you have duplicated database and didn’t check audit rows, you should.

thanks for reading. wish you all healthy, happy, peaceful days.

lsnrctl or new db connection is too slow

Hello everyone,

I know this is not new but I haven’t seen this problem for a long time and it takes a while to find out it.

A team leader (who is an elder brother to me) from the company I started to my first job called me and asked about a problem on their customer. Their customer complained about too slow db connection and no problem after the connection. first things first, I wanted to check database availability but because of some PATH problems (this was a Windows server) it took some time. after fixing environment variables, I finally reached out to database via local sysdba connection and database is up and running. also, connection was fast. by the way database was an 11.2 version which is quite old.

So, I started to focus on LISTENER. I first checked the status but “lsnrctl status”  command was quite slow. then I stopped and started again but these two operations was also slow. I checked the logfile under: $ORACLE_BASE/diag/tnslsnr/<SERVER_NAME>/<LISTENER_NAME>/trace but everything seemed ok! When I tried to logon via listener on the database server (using the net service name) it took around 70 seconds.

I started a listener trace (adding tracing parameters into sqlnet.ora file and restarting the listener) and tried to make a connection again. as soon as I issue my connection command (sqlplus username/pass@db) trace file is created. this means network connection was fast but while reading the trace file, after passing the connection information, listener was waiting for around 60-70 seconds.

I understand that network has no issue, connection arrives immediately but db connections and lsnrctl commands were slow. as I said I already checked, listener trace directory because I know that if listener.log file under trace directory has a big size, this could cause a slow operations on listener but it was around few megabytes. I checked few more things and finally I realized that I never checked “alert” directory under diag ($ORACLE_BASE/diag/tnslsnr/<SERVER_NAME>/<LISTENER_NAME>/alert) and when I opened it, there were over 1900 log.xml file which is the xml version of listener.log file. I deleted them all and it worked like a charm. Listener was checking all xml files to decide to write log data in which xml file. that was the cause and we were able to solve the problem.

Always check your listener log files and keep their size minimum as much as possible.

Wish you all healthy days.

SESSION_CACHED_CURSORS Parameter and How can It Affect the Database

Hello,

Let’s talk about the SESSION_CACHED_CURSORS parameter. It might have significant affect on your system so understanding how it works and what it cause is important.

This parameter limits the number of cached cursors on the session. As you know there is 2 kind of parse SOFT and HARD. Hard parse is creating the cursor from the scratch. Checking grants, syntax and semantics, creating execution plans etc. Soft parse is using a previously created cursor. So, if Oracle has already calculated all information for a query (sql statement, cursor) then, it stores it in the shared pool and can use it over and over again. not calculating everything from scratch is a big performance increment.

Even in a soft parse, Oracle still need to search through library cache and find the cursor first. SESSION_CACHED_CURSOR allows you to cache a cursor in your sessions PGA (and still in SGA). This way if you run a query over an over, it will be found and execute even faster. Basically, SESSION_CACHED_CURSOR is kind of “Softer” parse. Starting from 11g its default value is 50 and in 21c it is the same.

You can see cached cursors via V$OPEN_CURSOR view. Let’s open a new sqlplus session and check its cached information. to check cache information I will use a separate session because I don’t want to affect the main session. here is main session:

so my main sessions SID is 1470. from monitoring session I will check cached objects in the session:

I won’t be able to put all output because it will be much bigger in a minute. simple explanation for cursor types:

OPEN is currently opened (last run) sql statement.
OPEN-PL/SQL is currently opened cursors in plsql. it could be more than one, you can open many cursor remember that.
OPEN-RECURSIVE is recursive sql statements for your actual statement (for example Oracle needs a privilege check if you have access to those objects, it runs a query to find that)
SESSION CURSOR CACHED is half of what we are looking for here. direct sql statements which has been run in this session and cached in session PGA.
PL/SQL CURSOR CACHED is the other big half of what we are looking for here. Sql/plsql statements which has been run in a plsql object which is called in this session.

there are few more types but I won’t go detail all of them right now. if session runs a sql statements directly (select, insert, update, delete, merge etc) it will be cached as SESSION CURSOR CACHED. if session calls a plsql objects, all sql statements in it will be cached as PL/SQL CURSOR CACHED. Remember, to cache an sql statement, it must be run at least 3 times, only then it will be cached. by saying “run” cursor must be opened and closed. if you just run a query 3 times, you will not see it in cache because last one is still opened. run something else and then it will be in the cache.

you can see session cached cursors count via session statistics:

“session cursor cache hits” is how many times a new statement has been found in session cursor cache. if you  run a query 100 times repeatedly, after first 3 (after 3 runs it will be cached) next 97 will be read from cache and session cursor cache hits will increase by 97. “session cursor cache count” is currently number of cached objects (all sql, plsql and dictionary object queries).

as I said by default SESSION_CACHED_CURSORS is 50 so my session can cache up to 50 different statements and if there is more? Oracle will use a Least Recently Used (LRU) algorithm to keep most used (and recently) queries. Previously issued statements will be deleted from the session cache (not from library cache).

Let’s start some tests and run 3000 different queries 3 times (so they can be cached). of course instead of calling 3000 different queries I will use a simple plsql. those queries will be cached as PL/SQL CURSOR CACHED. Also, I will create a tmp table as copy of dba_objects to do my tests:

before continue, you might wonder why I used dynamic sql. if I use a static sql as “select count(*) from t where object_id = i” then i would be a bind variable and every query I run would be same. so, we couldn’t see the difference.

From the monitoring session let’s check V$OPEN_CURSOR for main session (a little bit long output)

as you see, 50 different select statement has been cached which IDs starting from 2951 to 3000. out last 50 queries. also OPEN cursor is our initial plsql code.

so what would happen if we increase the cache? is it a good thing or a bad thing? well, it depends. it is a good thing because you will be doing “softer” parse. your session will access to repeated queries faster but since this information is stored in PGA (session specific memory area) it will increase the usage of PGA. if you don’t have enough PGA it could be a pain for your database. PGA is automatically managed by Oracle. it grows or shrinks based on session needs. So, you might end up too much pga resize operation and even cannot cache that much queries at all.

Let’s check current PGA size for our main session now, from monitoring session:

so our main (testing) session is using 3 MB pga size. now, I will increase the size of session_cached_cursors to 1000 and run previous 3000 queries again.

check the pga size now:

now it uses 8MB of PGA. Numbers could be small but think it this way; we are using 2.5 times higher PGA then previous. Also, if you have 10.000 active sessions, this could be a problem. it is a high usage. if you have enough memory then, there is nothing to be afraid of or at least you should be adjusting your memory components accordingly. there is no correct size or ratio because this is highly depend on your application coding. You must test the values because if your application uses same queries during a business job then, you can have a smaller session_cached_cursors but if not then, you must increase the size based on your memory.

how about the good side? Yes, let’s measure that. Assume we have a business process running with 400 different queries for every run and run this process 1000 times in a day. how it would affect the performance increasing or decreasing the parameter. I will start with 100.

it took 29.38 seconds to run all queries. Now increasing the parameter value to 400 (which is almost sufficient to store all queries I run):

24.63 seconds. 5 seconds shorter which means around %17 percent faster. which is not bad and actually good. think your system is running faster by %17 percent without doing a complex sql tuning or changing the hardware. if you set session_cached_cursors parameter between 100 and 400, elapsed time would be between 24 and 29 accordingly, pga usage too.

finally, you can also query how many times your session used session cached cursors :

as you see currently, 399 different queries (sql statements) has been cached in the session and those queries has been accessed 2.011.646 times.

This is a quite nice trick to me but must be used cautiously because of the increase of PGA usage.

I hope this helps to increase your databases efficiency.

Wish you all healthy days.