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.

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.

Marked as unrecoverable datafile in V$datafile even if they are not!

Hello,

it has been a while since I posted. I lost a close relative to me and it wasn’t easy. So, let’s continue.

I run orachk on one my development databases yesterday and result was very informative. Interestingly, I had a “FAILED” check that indicates some of the datafiles are not recoverable! This is a serious issue so I checked. Check detail indicates result of the query below shouldn’t return a row:

and there were rows returned indeed. this database is not in force logging mode because it is just the development database and customer agrees with that. for out of curiosity I checked the production database and there were some datafiles returned from the query above. that concerns me. production database is in force logging mode . When I checked unrecoverable_time column, I realized that dates are really old and this is odd to me because I get full backup every day. How could these files be unrecoverable?

I run “report unrecoverable” command on RMAN and it shows nothing (for both production and development database). That is good but why those datafiles marked as unrecoverable and still listed as that way? if you do a unrecoverable operation on a table then, datafiles that those tables are stored in will be marked as unrecoverable in V$datafile.

of course, it is turned out this is a lack of knowledge of me about unrecoverable_time column on v$datafile. here is 19c documentation:

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-DATAFILE.html#GUID-7BF7955C-9705-40F4-B2F6-5D7F3A32DD30

UNRECOVERABLE_TIME DATE Timestamp of the last unrecoverable change. This column is updated only if the database is in ARCHIVELOG mode.

This column is “updated only”. so, if you did a unrecoverable operation like sqlloder direct load on a table, related datafile will be marked as unrecoverable and unrecoverable_time column will be updated on v$datafile. Even after a full backup, this won’t be updated back to null! it will just keep the last unrecoverable time information.

Better way to check unrecoverable datafiles could be this:

This also make me think why ORAchk runs the first query at all since it is not provide an exact information about unrecoverable datafiles. Maybe a minor bug or maybe still there is something that I don’t know.

Edit: I realized that I didn’t mention how to clear unrecoverable_time and unrecoverable_change# columns. it won’t be clear if you take a full (or any kind of) backup. these information is stored in control file and as I mentioned before, this column is only updated. So, you need to recreate your control file and this operation requires a shutdown.

Generate “create controlfile” statement, shutdown database, startup in nomount mode, recreate controlfile, recover database if needed and then open the database. one post step is to catalog recovery area because this new control file won’t have any recovery file information.

alter database backup controlfile to trace as ‘/tmp/my_control_file2.ctl’ noresetlogs ;
shutdown immediate;
startup nomount;
create controlfile … — use noresetlogs option for not to loose any data
alter database open;

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

How to Convert LONG to Clob PART 2

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.

Zero Data Loss DISASTER Database for Standard Edition 2

Hi,

As you know Data Guard is an Enterprise Edition option and only accepted method to protect your data against a “DISASTER” (DR) is data guard. How about Standard Edition 2(SE2)? Is this possible? Well I did it so, king of yes 🙂 with some risk of course.

Warining: I don’t take any responsibility for wrong settings. Wrong actions can damage your database. Please be careful.

if you google Oracle SE2 Data guard or Manual Log Shipping, you will find many blog post and documents about it. Even youtube has videos about this but almost all of them (at least all I’ve seen and read) are not “Zero Data Loss”. Steps are quite easy:

  1. you have a production database based on SE2
  2. you use another server as your DR server. (it could be on the same server if you use duplicate snapshot method)
  3. copy all your backup
  4. restore database and keep it in MOUNT mode
  5. copy your archivelog ever X minutes to DR server
  6. recover database using rman or sqlplus.

And that is it. This is a valid method. Only GAP is in this, you are transferring your archive logs but not redo logs. This means, you will certainly loose some data in case of a disaster because changes in redo logs will be lost (if you cannot reach out the server). So, what can we do? basic answer is by copying redo logs too but how? Redo logs are changing constantly. Even if you copy redo logs with in a 1 minute interval, you are still be able to lost 1 minute data.

To achieve this, you must force database to write your both redo logs and archive logs onto DR server. Not copying files in an interval, force database to write it there. Probably, you already understood what I did. to use this kind of method you should (must) have a strong, reliable, stable, fast network connection between your production and disaster servers. Because, we will send redo logs and archivelogs instantly to DR server.

I am using 19.15 databases on Oracle Linux 8 (virtual box). Same things can be done in other operating systems too. Logic will be same.

Here are the steps that I am about to do:

  1. On the DR server, create a folder to share over network via NFS.
  2. On the PROD server, Mount this shared folder on PROD server.
  3. On the PROD server, Add an archive log destination to this shared folder.
  4. On the PROD server, Add redo log members for each group to this shared folder.
  5. On the DR server, create a copy of prod database via rman (not duplicate)
  6. On the DR server, catalog archive logs, which resides on shared folder and recover database.
  7. In a disaster situation, copy redo logs to their original location with original name and recover database.

that’s it. Let me demonstrate those steps.

I created 2 virtual machines. They have both Oracle DB Software and one of them (PROD) has a database and I want to use other virtual machine as DR.

  1. Oracle home is under /u01/app/oracle/product/19c/dbhome_1
  2. I (always) use OMF (Oracle Managed Files).
  3. data files are under /u01/app/oracle/oradata
  4. recovery area is under /u01/app/oracle/fast_recovery_area
  5. SID of database is cdb
  6. PROD server ip is 192.168.56.101
  7. DR server ip is       192.168.56.104

1- On the DR server, create a folder to share over network via NFS

As the oracle user (Oracle db software owner user), create necessary directories.

this is the folder that I will share over the network and mount it (map it) on the production server.

to share over network, as root user:

2- On the PROD server, Mount this shared folder on PROD server

as oracle user, create a directory to mount shared directory on DR. /u01/app/oracle/fast_recovery_area/DR_LOGS will be used for mount point. directories in it is for OMF.

as root user:

at this point if I put a file into /u01/app/oracle/fast_recovery_area/DR_LOGS directory on PROD server, it will be written to /u01/PROD_LOGS on DR server

3- On the PROD server, Add an archive log destination to the shared folder

By default for OMF, if there is no value for log_archive_dest_n parameters, then Oracle internally use log_archive_dest_1 parameter as LOCATION=USE_DB_RECOVERY_FILE_DEST. that way, archivelogs are written under /u01/app/oracle/fast_recovery_area directory with sub directory structure of course (/u01/app/oracle/fast_recovery_area(<SID>/archivelog/YYYY_MM_DD).

but! if you assign a value to any of those parameters, then it will just use that location to create archivelogs. so I will set 2 log_archive_dest_n parameter, one location for PROD server and one for shared folder.

by setting these 2 parameters (log_archive_dest 1 and 2) we make sure that oracle will create 2 copies at the same time to 2 different location and one of them is actually out DR server. As you see there is one more parameter which is very important. log_archive_min_succeed_dest, by default is 1 and if you don’t set this as 2, in an access problem situation (network might have issues or DR server could be shutdown), Oracle will just write the first location and pass the inaccessible location. this will cause not writing necessary archive logs to DR server. if we want “zero data loss” DR db then oracle must write archive log to dr server. (also you can use MANDATORY clause).

4- On the PROD server, Add redo log members for each group to the shared folder

as the archive logs, we need to do the same things for redo log too.

as log_archive_dest_n parameter, if we set any of db_create_online_log_dest_n parameter, we need to set all three of them. 2 locations are on PROD server and 1 location is on DR server. this is not a mandatory step actually but it is a good practice. Because, we can just add redo log members (last 3 command above) to the shared location and this is enough but if one day you need to create another log group and forget to add a log member to shared folder then, you could be exposed to data loss! so set it any way.

then, add 1 member for each log group to shared folder. I use standard naming here because I need to know the groups of files by their name (we will use this later).

lastly, I create pfile to move to DR server, so we will create our DR database.

5- On the DR server, create a copy of prod database via rman

on PROD server, database “cdb” is up and running. Let’s start:

I don’t want to go all the details about standard copy backups and restore a database so, I won’t put outputs here.

On PROD server, take a backup of database via RMAN and then copy everything to DR server via scp.

from shell:

copy all backups, archivelogs etc and also pfile.

then go to DR server and create necessary directories & restore your database:

these directories will be needed for database. restore database:

at this point we have a copy prod database on DR in MOUNT mode.

somet very important thing to do

DO THIS ON DR SERVER ONLY! we are deleting any copied redo logs because during recovery mode, there must not be any redo log!

6- On the DR server, catalog archive logs, which resides on shared folder and recover database

from now on, you can create a recover.sql script and call it ever n minute from cron to recover database regularly. content of recover.sql is:

we must catalog newly arrived archivelogs first and then recover database. I want to remind that /u01/PROD_LOGS directory is the one that we shared over network and map it on to prod server.

every recovery command will end up with an ERROR:

this is an expected behavior. since we don’t apply redo logs, Oracle is looking for the next archive log file in the sequence but couldn’t find it. our recovery is now working. after archive log with sequence 18 is arrived, recover command will raise the same error above for sequence 19.

7- In a disaster situation, copy redo logs to their original location with original name and recover database

from now on, our archive and redo log files are stored on DR server too. if there is a disaster situation, you must open the database with applying all logs including redo logs. this is the only moment that we will apply redo logs.

these are the redo log locations on PROD:

/u01/app/oracle/oradata/CDB/onlinelog
/u01/app/oracle/fast_recovery_area/CDB/onlinelog
/u01/app/oracle/fast_recovery_area/DR_LOGS/CDB/onlinelog (this one is the network directory on DR server)

actual directory that redo files are written is /u01/PROD_LOGS on DR server. Those directories must be existed on DR server too (we already created them in scripts above). We must copy redo logs under /u01/PROD_LOGS to actual locations now. I deliberately named redo logs as redoN.log so that I can identify which redo file is belong to which redo group. a very simple sql script and shell script will copy redo logs to original locations:

calling put_redo_logs.sh file will run an sql script which will produce copy shell commands.

After that, you can run recover.sql one more time and this time RMAN will not raise any error and you will see “Recovery Completed” message. now you can open your database and use it.

Now, why did I delete copied redo log files via scp command on DR server? if you recover a redo log in mount mode, control file will raise some flags and all recover commands will start from the sequence of applied redo log, this will cause problems after some time because archivelogs will be deleted eventually because of retention policy. This also means, if you apply a redo log during recovery, to have a healthy copy of database, you must restore both control file & database all over again.

Pros

obviously, you have a DR db with full consistent data.

Cons

This approach contains high risk because if database cannot write archived or redo log to shared folder then production database will hang! You might have some issues about network or DR server might be restarted or shutdown. You might loose disk drives on DR server which shared folder resides on etc etc…

I will write another post about those situations and make some tests. if loosing some data for a small time then, still refreshable pluggable database would be a better option. I will also write a post about it.

if you don’t have a reliable network, this option could be catastrophic! So, try it carefully. I hope this helps in some way.

Wish you all healthy, happy days.

How to Restore Dropped PDB

Hi,

it seems easy, right? Well, not that much but not that hard either. if you drop a pdb in a cdb, you cannot restore it using current database because when you drop the pdb, all information about the pdb is lost. So, what is the solution? you need a point in time recovered duplicated database.

I have a cdb named CDB and had a pluggable database named PDB1. this database is a 21c database and installation of CDB is pretty standard.

db_create_file_dest     = /u01/app/oracle/oradata
db_recovery_file_dest = /u01/app/oracle/fast_recovery_area

Let’s say I dropped it by mistake and make a recover:

I created the pdb and create a table in it. I also get the time because it will be required while restoring pdb. now, let’s drop it by mistake:

I don’t add outputs for now. Okay, now I lost my pdb. to restore it, we must create a duplicate database, recovered until a specific time. step by step:

1- create a new pfile for duplicate database (let’s called out db name as “dup”)

2- create password file:

3- startup the instance

4- use rman and create duplicate database

you can get different error messages at this point but they all have enough information to solve the problem. if you get this error:

RMAN-05501: aborting duplication of target database
RMAN-05657: There are multiple database IDs present.

then you must set dbid before run the duplicate database command.

5- after duplicate database command completed (which will take for a while) your new database called “dup” will be ready and in mount mode.

at this point if you query pdbs (containers), a strange name will appear “_###_UNKNOWN_PDB_#_4” (4 is pdb number so it might  be different on your systems). after Opening your database with resetlogs (this is point in time recovery) it will be converted to correct name.

here it is. after this point we have 2 container databases on the server, cdb and dup. so, we can simply unplug and plug pdb1 into database CDB again.

I use “move” while plugging it into CDB database because I want its datafiles move to db_create_file_dest locations of CDB database.

that’s it. I found this solution is a little bit tiresome and I believe next releases will have easier solutions but still we can complete what we need in a few commands.

thanks for reading, I hope this helps.

wish you all healthy days.

CONTAINER_DATA for Common Users

Hello Everyone (if anyone reading this 🙂 ),

it has been a long year and I am glad it is over. As always, we hope the for “a time of period” like new year, birthday etc etc but hope is the only thing that we can hold on. I hope whole world get rid of this illness called corona, racism, wars (and again etc etc, list goes on)… Wish everyone a healthy, peaceful year.

In my personal blog, I mentioned vary of things and I realized that I didn’t mention about container database structure at all. Main reason for this is I was lazy on multi-tenant architecture and didn’t work on it, yeah, pretty much this was the reason 🙂

I wanted to start with common user’s container_data attribute (or property I am not sure). As you know, in multi-tenant architecture there two types of users COMMON and LOCAL. Common users are defined in the CDB (not in a pluggable database) and they are known by every current and future pdb. Local users are defined in a pluggable database and can access to only pluggable database they are defined. By saying “defined”, I mean “created”. So, local users don’t aware of other pluggable databases or container database (cdb) but common users are aware that they are in a CDB and can connect to PDBs on that CDB if they have privileges.

By default, newly created common users doesn’t see whole data in container data views or tables. what is “container data views”? As you noticed (even if you use non-cdb database 12c and above) there are some new columns on data dictionary and v$ views like CON_ID. in a multi-tenant database when a privileged user like SYS queried V$SESSION, user can see whole sessions in all containers (cdb$root and pdbs). Let’s see:

I will be running those queries on a 21.3 version. I have 3 PDBs on my CDB:

these are just dummy PDBs that I use for testing purpose on VM. Now Lets connect as SYS and query some views:

I connected as SYS and I am in the cdb$root container. I queried V$SESSION and V$DATAFILE views by grouping con_id column. I am able to see sessions on different PDBs, in my example, con_id 3 and 5 has 1 session each. V$DATAFILE query shows count of all data files per PDBs.

Now, let’s create a common user and give necessary privileges and query with that user:

I created C##MY_ADMIN user and grant DBA for all containers. So, this user can connect to any pdb and query any data dictionary view. for example, c##my_admin can connect to pdb1, pdb2 etc and query v$session in all of them and is able to see whole session for current pdb that user in it but problem is when c##my_admin connected to root (cdb$root), he/she won’t be able to see whole sessions in CDB (including PDB sessions). just the ones in cdb$root:

as you see, only sessions in con_id 1 which is cdb$root are listed. By the way, con_id 0 is CDB itself and only background processes are in there and they are listed in v$session all the time.

again, v$datafile lists only files in current container which is cdb$root. Please consider that c##my_admin is a DBA in CDB so it can connect to any PDB and query their data. it has necessary privileges for that. It has just don’t have “privilege” to query PDBs’ data in the CDB$ROOT container.

That is what CONTAINER_DATA does. it allows a common user to query some or all pdbs data in cdb$root. you must add necessary pdbs to common user’s container_data property:

this alter must be done by a privileged user in cdb$root container. Here, I set container_data of c##my_admin user as PDB1, PDB2 (con_ids are 3 and 4) and CDB$ROOT.

there are few important keys here:

  1. this command must be run in cdb$root.
  2. CDB$ROOT must be in container_data list because it is the root and common user will query data in it so, it must be able to see it
  3. CONTAINER=CURRENT is mandatory because by default it is ALL and this command can be run only in cdb$root which is “current”

so, c##my_admin will be able to see whole sessions in v$session for cdb$root, pdb1 and pdb2 (not PROXY_PDB which its con_id is 5). Let’s see:

so, c##my_admin is able to query other pdbs data in cdb$root. There is no session in pdb2 by the way, that is why no session for con_id 4 in v$session.

instead of giving all container data view access you can specified necessary view as this:

by setting container_data to default it will reset everything. now, I just add container_data info just for V_$session view (which is based view for v$session, you can not grant on v$session) and query v$session and datafile again with c##my_admin:

here it is. we can see session rows for other pdbs in v$session but not for v$datafile.

if you want to set all pdbs at once:

you can use ALL clause for pdb list. remember when you use SET as “ALTER USER … SET CONTAINER_DATA” it will overwrite previous container_data property. if you want to add a new PDB into container_data list then you can use ADD instead of SET:

You can query who has access to what using CDB_CONTAINER_DATA data dictionary view.

I hope this will helpful, any comments is appreciated.

Thanks for reading, wish you healthy days.

How to Demonize Apex V Function!

Hi,

Answer of the question in the subject is “Using too much V in SQL statements”. Let’s see why and what should you do.

First things first, I am NOT a APEX developer. I am a DBA and Database Developer but some of my clients are using APEX and I am dealing with many sql functions that uses V function in apex. Since the day 1, I did not like this V function because developers using it in very different ways and it returns VARCHAR2 but it uses for all kind of compares without any conversion functions (this is not on the V function of course).

So, what is the problem here? At my clients’ codes, V function is used heavily in sql statements. Some queries that I dealt with lead me to investigate V function because even if the query has no performance issue, still, it takes too much to complete. When I checked the V function, I find out that it is not DETERMINISTIC (which shouldn’t be because of the logic of DETERMINISTIC but I will talk about this later). Let’s do some tests but before I must explain a few things. I hacked the code of V a little bit and add some logging codes to demonstrate how it is worked and performance issues about it but I won’t explain how I do it (probably many of you can do it easily) and share any source code of it. whenever a V function is called my logging code will write the parameter name of v function into “my_log” table.

I added my log codes, set an apex session in my sql developer session and run these selects:

I delete log table first then call a select statement with four V function in select list run over DUAL table and then check the log table, as you see it is called 4 times. So same data is read 4 times and 4 plsql function call is occurred. this caused too much context switch between SQL and PLSQL and this can reduce your performance dramatically.  Especially, Where clause is extremely dangerous, why? Because V function might run for all rows in the table which will cause a disaster.

as you can see,  this query called  V function 79.647 times why? because there are 79.645 rows in the TMP table. Since I use V in Where clause, condition ( object_id = v(‘OBJECT_ID’) ) executed for every rows in the table (79.645 rows) and only 1 of the rows meets the condition so select list runs 1 times and there are 2 more V function (same with the one in where clause) so totally V called 79.647 times. I hope you see the problem here. in your application code if you use V function in your sql statements, it might be called too much!

How about an index? yep that will help:

so it run only 3 times at this execution because this time search operation (where condition) is run on an index not table so V function is called 1 times in where clause and 2 times in select list. this means you should have indexes at least for your condition columns.

is that enough? Let me show you a pseudo code on a database:

so many same V function call in select statement. creating and index will help you about where clause how about select list? since all items in select list cause a function call (even with same parameters) let’s test performance:

calling this query for 10.000 times (or you can thing this as calling sql 1 times for 10.000 rows) takes 3.2 seconds (20 same V function). if we change the code to this:

this query takes only 0.5 seconds. timings might significant but think about 1000 different queries that run on your database 1000 times per day and calculate spend of time for just calling V function. it is a lot! your significant amount of db time might be wasted.

Let’s improve the code a little bit more:

even better. if you increase number of call, you will see that first 2 plsql code timings will increase linearly but the last code (code just above) will give you almost same performance. Remember, V is a function not a variable. Use variables instead of using V if you can.

Even if these might help your system, main problem is still there: V is not DETERMINISTIC. you might change your codes as I suggested above or create more indexes but many developers also use V in VIEWS which is another and for me a bigger disaster. it is harder to modify the code you cannot use bind variables etc… What would happen if V was deterministic? I modified V and made it deterministic and let’s see the result with first test case without index:

pay attention that there is no index but V is just called 2 times (1 for where clause and 1 for select list, event if select list has 2 copy). it was 79.647 without DETERMINISTIC if you remember the first result.

So making V as deterministic will solve almost every kind of problem but only problem is what I looking for here is not deterministic. Why? Let me explain what deterministic is first. if a function always returns same value with same parameters then this function is a deterministic function like TO_CHAR or SUBSTR. this allow oracle to know that if calls with same parameters are repeated, it can be cached and used first calculation result for on going calls. This way, if same function with same parameters are called in a SQL statement, that function is called only 1 times but this is not V. Result of V is not deterministic because application might change the value of a V item and this means V call with same parameters can return different results because it is just return a context variable value.

So, it shouldn’t be deterministic but it should be something that acting like deterministic for an sql statement. I don’t think any apex developer wants to see a change of V values during a select statement. I think they assume values of V won’t change during the sql statement. So, there should be something like DETERMINISTIC_FOR_CALL in Oracle database. we should be able to define plsql objects as “deterministic” while they are used in a sql statement. Their result should be calculated once and used during the sql call. I think this is also necessary for any plsql object that we can call on SQL. because current structure of plsql is kind of violating the consistency. A select statement returns rows as they were when the sql call started. even if the rows are changed during the sql call, result of the statement won’t be affected from those changes but PLSQL breaks that. when a function calculated for every row, a select statement inside the plsql object can return different results because it will be affected by on going changes. This is a database option. Database should provide us this functionality, Apex team has nothing to do about it.

By the way, I am really frustrated about data type conversions about V. since V returns VARCHAR2, many developer does not care about type conversions. if you compare a V value with a number then you should use “TO_NUMBER(V(…))” for example or at least (as I search for it) NV function (number result of V). this is another issue to me.

For now, I suggest to avoid using V in a SQL statement as much as possible and use bind variable if you can.  Let’s say you have some SQL with so many V call with same parameter, what can you do?  you can “force” oracle to cache them:

there are better way to write this sql but I just want to demonstrate a way. as you see this query takes 3.26 seconds to complete because of too many V calls. you can rewrite your query and cache the V results first and then use them as this:

I am adding a NO_MERGE hint to prevent optimizer to merge queries before query run. as you see this version returns in 0.19 seconds 15 times faster becuase V functions are called only 2 times (2 different V item is selected OBJECT_ID and USER_NAME).

Edit:

Another and maybe better solution for this problem is using your own V function and create it as deterministic:

this is faster than previous join method. only thing that you should change V calls with MY_V.

I hope, this helps you on your systems. Now after those tests, I must connect with my clients and explain them to change their coding to gain significant db time.

wish you all healthy days.