Administration

Old Obsolete Backup is not Deleted

Published on: by Mustafa Comment

Hi,

Happy new year (after almost a month). I was quite busy and lazy to write a new blog post but today I found the energy.

What I realized newly is a little bit shocking for me because I had no idea! Maybe this is just an ignorance of me but I never read anything about this (I had read entire backup and recovery documentation of oracle) and never heard of it. Here is the problem: “Report Obsolete”  or “Delete Obsolete” commands don’t report or delete an old backup even if it is beyond the retention period.

At first, I thought probably I am missing something about retention period or maybe a datafile is not backed up correctly (which is not much a possibility). After debugging RMAN report obsolete command, some debug logs takes my attention and I think I found the issue but have no idea whether this is a bug or a feature. I will demonstrate it with a single non-cdb database instance to keep things easy. This is a 19.16 database in a virtual machine and this is happening in both Linux and windows. an empty database, here is the schema report:

Let’s create a dummy tablespace first:

Database has no backup at all at that point, Let’s get one:

our new test tablespace TBS_TEST is backup up along with the database. Now, my retention period is default which is REDUNDANCY 1:

that means only 1 last backup for everything (database, tablespace, datafile) is enough and all previous backups are “obsolete”. Now before getting a second backup let’s delete TBS_TEST tablespace:

and get a new backup:

Good, I have two backups now and TBS_TEST is not included on the last one. since my retention policy is “REDUNDANCY 1”, previous backup should be “obsolete”. List backups:

as you see datafile 5 row in the first backup has no Datafile Name anymore because it is dropped. Now, lets report obsolete backups:

Huh! Only autobackup is obsolete and report command doesn’t mention about previous backup! Please remember my retention is redundancy 1 so only last backup should be kept but RMAN is keeping previous backup, the one with dropped tablespace/datafile.

if I keep taking new backups and run report obsolete (or delete obsolete) commands, it doesn’t report the first backup, only the ones after that.

I tried this with a 19.3 databases and it didn’t happen! it marked old backup as obsolete immediately. So as I said, I don’t know if this is my ignorance, lack of knowledge of just missing something or a bug but on different systems (on virtual machines, linux and windows servers) for 19.16, I see this behavior.  Also, it could be a Container database or a non-cdb. You might want to check your systems if there are older backups than your retention periods.

Thanks for reading, wish you all a happy, healthy new year. My next post will be about a ORA-00600 which drives me crazy for a few weeks.

EDIT: I should add one more thing, by the retention policy requirements all archivelogs between old “obsolete” backup and the current one are deleted! so, this old backup file is completely useless.

Autoupgrade doesn’t Upgrade Timezone Version While RU Patching

Published on: by Mustafa Comment

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

Published on: by Mustafa Comment

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

Published on: by Mustafa Comment

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

Published on: by Mustafa Comment

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

Published on: by Mustafa Comment

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

Published on: by Mustafa Comment

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.

Audits are not Purged

Published on: by Mustafa Comment

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

Published on: by Mustafa Comment

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.

How to Convert LONG to Clob PART 2

Published on: by Mustafa Comment

Hi,

I just want to write a small post today. Some time ago, I shared a way to convert long to clob in a query directly: https://mustafakalayci.me/2020/04/06/how-to-convert-a-long-column-to-clob-in-a-query/ Even if this method works, it works really slow and anything slow annoys me!

So, I want to share another method which is much much faster than Sys_dburigen function but THIS IS UNDOCUMENTED and UNSUPPORTED! It is used by internally by oracle so it works fine but you must be careful. Since, no one uses LONG column in their application (right? you don’t! please don’t!) this kind of code is handy for mostly admins because for backward compatibility data dictionary has some LONG data type columns.

What we are going to use is a LIBRARY called UTL_XML_LIB . I almost never create objects under SYS user, so I will create this code under a privileged admin user. first we must grant privileges on UTL_XML_LIB library and then create an EXTERNAL procedure in plsql.

now create external procedure:

“kuxLong2Clob” is a function which returns void (basically it is a procedure) written in C by Oracle. this procedure takes a select statement for your LONG column and rowid. procedure returns CLOB data as an out parameter. I created my_long2clob procedure but to be able to call it via SQL statement, it must be a function, so:

to test it, I won’t create a new table with a LONG column but instead I will use one of in the data dictionary (which I use these kind of codes against). Let’s use SYS.TRIGGER$ table which is the base table for DBA_TRIGGERS view. ACTION# column is a LONG data type. of course we must first grant select privilege on SYS.TRIGGER$ to mustafa user.

I suppressed the output but here it is. really fast long to clob conversion in SQL.

as a CON, it will only work against tables not VIEWs! you cannot use it as “f_my_long2cloc(rowid, ‘DBA_TRIGGERS’, ‘TRIGGER_BODY’)”. Since it requires ROWID, you must be referring underlying table and its rowid.

beyond that, if you want to get LONG as VARCHAR2, similarly you can use “kpdbLong2Varchar2” procedure in DBMS_PDB_LIB library. as UTL_XML_LIB it is very fast. only difference is, it returns VARCHAR2 and trims the overflow part. Where is it in use? Check how DBA_VIEWS shows TEXT_VC column data 😉

I hope this helps to fight against LONG columns in your codes.

Wish you all healthy, happy days.