Infrastructure at your Service

Category Archives: Oracle

Franck Pachot

Server process name in Postgres and Oracle

By | Oracle, Postgres | No Comments

Every database analysis should start with system load analysis. If the host is in CPU starvation, then looking at other statistics can be pointless. With ‘top’ on Linux, or equivalent such as process explorer on Windows, you see the process (and threads). If the name of the process is meaningful, you already have a clue about the active sessions. Postgres goes further by showing the operation (which SQL command), the state (running or waiting), and…

 
Read More
Franck Pachot

12cR2 PDB archive

By | Oracle | No Comments

In 12.1 we had the possibility to unplug a PDB by closing it and generating a .xml file that describes the PDB metadata required to plug the datafiles into another CDB. In 12.2 we got an additional possibility to have this .xml file zipped together with the datafiles, for an easy transport. But that was not working for ASM files. The latest Release Update, Oct 17 includes the patch that fixes this issue and is…

 
Read More
Franck Pachot

JAN18: Database 11gR2 PSU, 12cR1 ProactiveBP, 12cR2 RU

By | Oracle | No Comments

If you want to apply the latest patches (and you should), you can go to the My Oracle Support Recommended Patch Advisor. But sometimes it is not up-todate. For example, for 12.1.0.2 only the PSU is displayed and not the Proactive Bundle Patch, which is highly recommended. And across releases, the names have changed and can be misleading: PSU for 11.2.0.4 (no Proactive Bundle Patch except for Engineered Systems). 12.1.0.2 can have SPU, PSU, or…

 
Read More
Franck Pachot

Multitenant, PDB, ‘save state’, services and standby databases

By | Oracle | No Comments

Creating – and using – your own services has always been the recommendation. You can connect to a database without a service name, though the instance SID, but this is not what you should do. Each database registers its db_unique_name as a service, and you can use it to connect, but it is always better to create your own application service(s). In multitenant, each PDB registers its name as a service, but the recommendation is…

 
Read More
Thomas Rein

Migrate Windows VM with more than 4 Disks from VMware to OVM

By | Database Administration & Monitoring, Oracle | No Comments

Suppose you got an OVA image created on VMware and the VM contains more than 4 Disks and you have to migrate this machine from VMware to OVM. As first step you import the OVA into the OVM in the usual way: You see the that the appliance was imported successfully, we have 5 disks: Now you create your VM from the imported appliance: So far so good, lets have a look on our newly created VM:…

 
Read More
Franck Pachot

Result Cache: when *not* to use it

By | Oracle | One Comment

I encountered recently a case where result cache was incorrectly used, leading to high contention when the application encountered a peak of load. It was not a surprise when I’ve seen that the function was called with an ‘ID’ as argument, which may have thousands of values in this system. I mentioned to the software vendor that the result cache must be used only for frequently calling the function with same arguments, not for random…

 
Read More
Franck Pachot

Testing Oracle SQL online

By | Oracle | One Comment

Want to test some DDL, a query, check an execution plan? You need only a browser. And you can copy-paste, or simply link, your test-case in a forum, a tweet, an e-mail, a tweet. Here is a small list (expecting to grow from your comments) of free online services which can run with an Oracle Database: SQL Fiddle, Rextester, db<>fiddle and Oracle Live SQL  

 
Read More
Franck Pachot

Explain Plan format

By | Oracle | No Comments

The DBMS_XPLAN format accepts a lot of options, which are not all documented. Here is a small recap of available information. The minimum that is displayed is the Plan Line Id, the Operation, and the Object Name. You can add columns and/or sections with options, such as ‘rows’, optionally starting with a ‘+’ like ‘+rows’. Some options group several additional information, such ‘typical’, which is also the default, or ‘basic’, ‘all’, ‘advanced’. You can choose…

 
Read More
Franck Pachot

Unplug an Encrypted PDB (ORA-46680: master keys of the container database must be exported)

By | Oracle | No Comments

In the Oracle Database Cloud DBaaS you provision a multitenant database where tablespaces are encrypted. This means that when you unplug/plug the pluggable databases, you also need to export /import the encryption keys. You cannot just copy the wallet because the wallet contains all CDB keys. Usually, you can be guided by the error messages, but this one needs a little explanation and an example.  

 
Read More