Search

Top 60 Oracle Blogs

Recent comments

Oracle

LOB reads

This is a blog note that started life in September 2001 (which was before I started blogging, I think), got drafted as a blog in January 2014 because of a question on the Oracle-L list server, and has finally got to publication following a question on the ODC database forum. (But the comments in the blog don’t look as if they are relevant to the question.)

The question on Oracle-l was:  Why is the same CLOB datablock read multiple times?

The answer is basically: client programs tend to set a “sensible” fetch buffer size for LOBs and if the buffer size is less than the size of the LOB the block(s) the LOB is in have to be visited multiple times and for nocache LOBs that means the block(s) will be read multiple times.

This can be seen quite easily in SQL*Plus which has two helpful parameters (one dating back to at least v5):

Oracle RMAN Backup Optimization ON and backup archivelog command.

TL;DR: BACKUP OPTIMIZATION ON acts as a NOT BACKED UP 1 TIMES for the BACKUP ARCHIVELOG ALL command.

I always mention “not backed up n times” in my “backup archive log” commands because I think it makes sense to precise exactly how many copies of archived log we want to have available to restore from each backup device

I often configure “backup optimization on” when thinking about the tablespaces I put read-only so that they are not backed up each time(given that there is no suppression of old backups that are unknown to RMAN).

But I’ve never realized that BACKUP OPTIMIZATION and NOT BACKED UP actually both work on datafiles, archivelogs, and backupsets. And then the following is redundant:

Oracle & Postgres JDBC Fetch Size

TL;DR — By default PostgreSQL ResultSet is not a server cursor but a client buffer. The whole result is fetched before the first next() call.

It is usually a good idea to start with default settings rather than trying to ‘tune’ any possible parameter. Defaults are often suited to the most common cases. However, there’s a big exception to it when using JDBC drivers: the default fetch size is probably not the right setting. Here is an example with the same Java code to show the fetching behavior in Oracle and PostgreSQL.

Java example

Here is my Java code which is exactly the same for Oracle and PostgresSQL

Oracle DBA_SQL_PLAN_BASELINE SQL_ID and PLAN_HASH_VALUE

There are probably better ways, so please let me know (@FranckPachot). This is what I use when I want to get the SQL_ID and the PLAN_HASH_VALUE when looking at the SQL Plan Baselines.

The DBA_SQL_PLAN_BASELINES view does not provide them, probably because SQL Plan Management (SPM) is going from a statement and it’s execution plan to the SQL Plan Baselines, but doesn’t need to navigate in the other way. However, we need it when troubleshooting query performance.

Create an Oracle VM on Azure in Less than 5 Minutes

If there’s one thing I’ve been able to prove this week, it’s that even with the sweet 4G LTE, Wi-Fi setup in my RV, Montana still has the worst Wi-Fi coverage in the US.  Lucky for me, I work in the cloud and automate everything, because if there’s one thing I love about automating with scripts, is that I can build out a deployment faster and less resource intensive than anyone can from the portal.

Oracle Virtual Machines in Azure

When you build out an Azure VM, with Oracle, you’ll also need to have the supporting structure and a sufficiently sized additional disk for your database.  This can be a lot of clicks inside a portal, but from a script, a few questions and bam, you have everything you need.

No risk to activate Active Data Guard by mistake with SQL Developer SQLcl

If you have a Data Guard configuration without the Active Data Guard license, you can:

  • apply the redo to keep the physical standby synchronized
  • or open the database read-only to query it

but not at the same time.

Risk with sqlplus “startup”

Being opened READ ONLY WITH APPLY requires the Active Data Guard option. But that this may happen by mistake. For example, in sqlplus you just type “startup”, instead of “startup mount”. The standby database is opened read-only. Then the Data Guard broker (with state APPLY-ON) starts MRP and the primary database records that you are using Active Data Guard. And then DBA_FEATURE_USAGE_STATISTICS flags the usage of: “Active Data Guard — Real-Time Query on Physical Standby”. And the LMS auditors will count the option.

The ways to prevent it are unsupported:

Passwordless Data Pump 19c

That’s a very light bug with a very simple workaround, but it may require a little change in scripts. If you use passwordless authentication (external password file or OS authentication) with Data Pump in 19c it will ask for the password. The solution is just to answer whatever you want because the external authentication will be used anyway.

Example

I create the wallet

mkstore -wrl $ORACLE_HOME/network/admin -create <w4ll3t-P455w0rd
w4ll3t-P455w0rd
CREATE

I create a tnsnames.ora entry that I’ll use to connect:

cat >> $ORACLE_HOME/network/admin/tnsnames.ora  <CDB1A_SYSTEM=(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=CDB1A))(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
CAT

I add a credential for this entry — here SYSTEM user and its password:

Best Practices for Oracle Data Guard on Azure

I keep saying I’m going to start sharing what I’m doing in the Analytics space soon, but heck, there’s too much I need to keep adding to on the Oracle in Azure arena!

So, as most people know, I’m not a big fan of Oracle RAC, (Real Application Cluster).  My opinion was that it was often sold for use cases that it doesn’t serve, (such as HA) and the resource demands between the nodes, as well as what happens when a node is evicted to those that are left are not in the best interest for most use cases.  On the other hand, I LOVE Oracle Data Guard, active or standard, don’t matter, the product is great and it’s an awesome option for those migrating their Oracle databases to Azure VMs.

Ideas for Event Sourcing in Oracle

Log Miner or are there other alternatives?

With microservices, the architects want to dismantle the monolithic database and replicate data rather than share it. Then raises the need to audit the changes where the modifications are done (like the C in CQRS). The Oracle database already does that for recovery purpose, building the redo records before modifying the data blocks, but that’s a physical change vector. We need something logical with more possibilities to filter and transform. There are multiple possible methods for this. But unfortunately, the ones that were built in the database are slowly removed since Oracle has acquired Golden GAte and sells it separately.

Oracle Heterogeneous Services

An example configuring Oracle ODBC gateway to PostgreSQL

That sounds futuristic, like a 2025 post-microservices trend. Monolithic databases are definitely dismantled, we developed in microservices, spread data to many databases. Because we had agile development teams, they have chosen the database technology that fits their need: cheap, easy, free from any infrastructure constraints, and of course cool enough for their CV. The enterprise information system is now a zoo with all species represented.