Oracle

Franck Pachot's picture

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.

dbakevlar's picture

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.

Franck Pachot's picture

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:

Franck Pachot's picture

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:

dbakevlar's picture

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.

Franck Pachot's picture

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.

Franck Pachot's picture

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.

Jonathan Lewis's picture

opt_estimate 5

If you’ve been wondering why I resurrected my drafts on the opt_estimate() hint, a few weeks ago I received an email containing an example of a query where a couple of opt_estimate() hints were simply not working. The critical features of the example was that the basic structure of the query was of a type that I had not previously examined. That’s actually a common type of problem when trying to investigate any Oracle feature from cold – you can spend days thinking about all the possible scenarios you should model then the first time you need to do apply your knowledge to a production system the requirement falls outside every model you’ve examined.

Before you go any further reading this note, though, I should warn you that it ends in frustration because I didn’t find a solution to the problem I wanted to fix – possibly because there just isn’t a solution, possibly because I didn’t look hard enough.

dbakevlar's picture

Oracle and Microsoft’s Cross-Cloud Partnership

A couple weeks back, Oracle and Microsoft announced their cross-cloud partnership.  This was wonderful news to me, as I’ve been working on numerous Oracle projects at Microsoft with Azure.

The Gist

To know that there is now a partnership between the two clouds and that there’s also a large amount of documentation about working between the two clouds is very helpful vs. the amount I’ve been working on based off just my knowledge.  Just as anyone appreciates a second set of eyes, I now have two company’s worth!

Jonathan Lewis's picture

Assumptions

Over the last few days I’ve been tweeting little extracts from Practical Oracle 8i, and one of the tweets contained the following quote:

This lead to the question:

To prevent automated spam submissions leave this field empty.
Syndicate content