Real-Time Cascade Standby Container Databases without Oracle Managed Files

OK, the title might not be the best… I just would like to add more detail to content you can already find in other blogs (E.g. this nice one from Philippe Fierens http://pfierens.blogspot.com/2020/04/19c-data-guard-series-part-iii-adding.html).

I have this Cascade Standby configuration:

Years ago I wrote this whitepaper about cascaded standbys:
https://fr.slideshare.net/ludovicocaldara/2014-603-caldarappr
While it is still relevant for non-CDBs, things have changed with Multitenant architecture.

In my config, the Oracle Database version is 19.7 and the databases are actually CDBs. No Grid Infrastructure, non-OMF datafiles.
It is important to highlight that a lot of things have changed since 12.1. And because 19c is the LTS version now, it does not make sense to try anything older.

First, I just want to make sure that my standbys are aligned.

Primary:

1st Standby alert log:

2nd Standby alert log:

Then, I create a pluggable database (from PDB$SEED):

On the first standby I get:

On the second:

So, yeah, not having OMF might get you some warnings like: WARNING: File being created with same name as in Primary
But it is good to know that the cascade standby deals well with new PDBs.

Of course, this is not of big interest as I know that the problem with Multitenant comes from CLONING PDBs from either local or remote PDBs in read-write mode.

So let’s try a relocate from another CDB:

This is what I get on the first standby:

and this is on the cascaded standby:

So absolutely the same behavior between the two levels of standby.
According to the documentation: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-PLUGGABLE-DATABASE.html#GUID-F2DBA8DD-EEA8-4BB7-A07F-78DC04DB1FFC
I quote what is specified for the parameter STANDBYS={ALL|NONE|…}:
“If you include a PDB in a standby CDB, then during standby recovery the standby CDB will search for the data files for the PDB. If the data files are not found, then standby recovery will stop and you must copy the data files to the correct location before you can restart recovery.”

“Specify ALL to include the new PDB in all standby CDBs. This is the default.”

Specify NONE to exclude the new PDB from all standby CDBs. When a PDB is excluded from all standby CDBs, the PDB’s data files are unnamed and marked offline on all of the standby CDBs. Standby recovery will not stop if the data files for the PDB are not found on the standby. […]”

So, in order to avoid the MRP to crash, I should have included STANDBYS=NONE
But the documentation is not up to date, because in my case the PDB is skipped automatically and the recovery process DOES NOT STOP:

However, the recovery is marked ENABLED for the PDB on the standby, while usind STANDBYS=NONE it would have been DISABLED.

So, another difference with the doc who states:
“You can enable a PDB on a standby CDB after it was excluded on that standby CDB by copying the data files to the correct location, bringing the PDB online, and marking it as enabled for recovery.”

This reflects the findings of Philippe Fierens in his blog (http://pfierens.blogspot.com/2020/04/19c-data-guard-series-part-iii-adding.html).

This behavior has been introduced probably between 12.2 and 19c, but I could not manage to find exactly when, as it is not explicitly stated in the documentation.
However, I remember well that in 12.1.0.2, the MRP process was crashing.

In my configuration, not on purpose, but interesting for this article, the first standby has the very same directory structure, while the cascaded standby has not.

In any case, there is a potentially big problem for all the customers implementing Multitenant on Data Guard:

With the old behaviour (MRP crashing), it was easy to spot when a PDB was cloned online into a primary database, because a simple dgmgrl “show configuration” whould have displayed a warning because of the increasing lag (following the MRP crash).

With the current behavior, the MRP keeps recovering and the “show configuration” displays “SUCCESS” despite there is a PDB not copied on the standby (thus not protected).

Indeed, this is what I get after the clone:

I can see that the Data Guard Broker is completely silent about the missing PDB. So I might think my PDB is protected while it is not!

I actually have to add a check on the standby DBs to check if I have any missing datafiles:

This check should be implemented and put under monitoring (custom metrics in OEM?)

The missing PDB is easy to spot once I know that I have to do it. However, for each PDB to recover (I might have many!), I have to prepare the rename of datafiles and creation of directory (do not forget I am using non-OMF here).

Now, the datafile names on the standby got changed to …/UNNAMEDnnnnn.

So I have to get the original ones from the primary database and do the same replace that db_file_name_convert would do:

and put this in a rman script (this will be for the second standby, the first has the same name so same PATH):

Then, I need to stop the recovery, start it and stopping again, put the datafiles online and finally restart the recover.
These are the same steps used my Philippe in his blog post, just adapted to my taste 🙂

For the second part, I use this HEREDOC to online all offline datafiles:

and finally:

Now, I do not have anymore any datafiles offline on the standby:

I will not publish the steps for the second standby, they are exactly the same (same output as well).

At the end, for me it is important to highlight that monitoring the OFFLINE datafiles on the standby becomes a crucial point to guarantee the health of Data Guard in Multitenant. Relying on the Broker status or “PDB recovery disabled” is not enough.

On the bright side, it is nice to see that Cascade Standby configurations do not introduce any variation, so cascaded standbys can be threated the same as “direct” standby databases.

HTH

Ludovico

How to get the Data Guard broker configuration from a SQL query?

Everybody knows that you can get the Data Guard configuration in dgmgrl with the command:

but few know that this information is actually available in x$drc:

The table design is not very friendly, because it is a mix of ATTRIBUTE/VALUE pairs (hence many rows per object) and specific columns.

So, in order to get something usable to show the databases and their status, the only solution is to make use of PIVOT().

To get it in a friendly format, I recommend using SQLcl and settting

This is what I get on a simple two databases configuration (primary/standby):

and with a real-time cascade standby:

(interesting to note the leading :RTC_ON in the ship_to attribute).

Although it is much easier to get this information from DGMGRL, get it programmatically is more sure/flexible using the SQL interface, as you know what you want to get, no matter how the dgmgrl syntax changes.

Looking forward to have the REST APIs in a future version of Data Guard 🙂

Ludovico

Awk to format the files .ora (listener.ora, tnsnames.ora, etc)

Tired of formatting the tnsnames.ora to make it more readable, I have taken the nice awk examples from Jeremy Schneider: https://ardentperf.com/2008/11/28/parsing-listenerora-with-awk-and-sed/ and created a function to format all files .ora (lisp-like config files).

Example, before:

and after:

The AWK script:

I have included the function in the COE github repo. More functions to come (hopefully).

Ludo

Multitenant Pills: Partial PDB cloning (and cleanup)

When consolidating to multitenant, there are several consolidation patterns.

  • Big, complex databases usually have special requirements for which it might be a good choice to go to single-tenant (a single PDB in one CDB)
  • Small, relatively easy databases are the best candidate for consolidation to multitenant
  • Schema consolidated databases require special attention, but in general there are several advantages to convert individual schemas (or group of schemas) to individual PDBs

For the latter, there are some techniques to convert a schema in a PDB.

  • export/import (obviously), with eventually Golden Gate to do it online
  • Transportable tablespaces (if the schemas follow strict 1-to-1 tablespace separation
  • partial PDB cloning

We will focus on the last one for this blog post.

Situation

Here we have a PDB with some schemas, each of them has a dedicated tablespace, but accidentally, two of them have also some objects on a common tablespace.

This happens frequently when all the users have quota on the default database tablespace and they do not have necessarily a personal default tablespace.

This is the typical situation where transportable tablespaces become hard to achieve without some upfront segment movement, as tablespaces are not self-contained.

Thankfully, Oracle Multitenant allows us to clone a PDB from a remote one and specify only a subset of tablespaces.

Here is a full example script with some checks and fancy parameters:

This is an example output:

If the clone process succeeds, at the end we should have the new ABC pluggable database with ABC and DATA tablespaces only.

Yeah!

Any Cleanup needed?

What happened to the users? Actually, they are all still there:

And the segments in the two skipped tablespaces are not there:

So the table definitions are also gone?

Not at all! The tables are still there and reference to tablespaces that do not exist anymore. Possible?

Actually, the tablespaces definition are still there if we look at v$tablespace:

If we give a look at the DBA_TABLESPACES view definition, there are a few interesting filters:

What is their meaning?

So the first WHERE clause skips all the INVALID TABLESPACES (when you drop a tablespace it is still stored in ts$ with this state), the second skips the definition of TEMPORARY TABLESPACE GROUPS, the third one is actually our candidate.

Indeed, this is what we get from ts$ for these tablespaces:

So the two tablespaces are filtered out because of this new multitenant flag.

If we try to drop the tablespaces, it succeeds:

But the user GHI, who has no objects anymore, is still there.

So we need to drop it explicitly.

Automate the cleanup

This is an example PL/SQL that is aimed to automate the cleanup.

Actually:

  • Users that had segments in one of the excluded tablespaces but do not have any segments left are just LOCKED (for security reasons, you can guess why).
  • Tablespaces that meet the “excluded PDB” criteria, are just dropped

This is the output for the clone procedure we have just seen:

The PL/SQL block can be quite slow depending on the segments and tablespaces, so it might be a good idea to have a custom script instead of this automated one.

What about user DEF?

The automated procedure has not locked the account DEF. Why?

Actually, the user DEF still has some segments in the DATA tablespace. Hence, the procedure cannot be sure what was the original intention: copy the user ABC ? The clone procedure allows only to specify the tablespaces, so this is the only possible result.

Promo: If you need to migrate to Multitenant and you need consulting/training, just contact me, I can help you 🙂

 

Multitenant Pills – Change DBID for an existing PDB

When you plug the same PDB many times you have to specify “AS COPY” in the syntax:

Otherwise, you will get an error similar to:

There are case, however, where you cannot do it. For example, it the existing PDB should have been the clone, or if you are converting a copy of the same database from Non-CDB to PDB using autoupgrade (with autoupgrade you cannot modify the CREATE PLUGGABLE DATABASE statement).

In this case the solution might be to change the DBID of the existing PDB, via unplug/plug:

Ludo

Parallel Oracle Catalog/Catproc creation with catpcat.sql

With Oracle 19c, Oracle has released a new script, annotated for parallel execution, to create the CATALOG and CATPROC in parallel at instance creation.

I have a customer who is in the process of migrating massively to Multitenant using many CDBs, so I decided to give it a try to check how much time they could save for the CDB creations.

I have run the tests on my laptop, on a VirtualBox VM with 4 vCPUs.

Test 1: catalog.sql + catproc.sql

In this test, I use the classic way (this is also the case when DBCA creates the scripts):

The catalog is created first on CDB$ROOT and PDB$SEED. Then the catproc is created.

Looking at the very first occurrence of BEGIN_RUNNING (start of catalog for CDB$ROOT) and the very last of END_RUNNING in the log (end of catproc in PDB$SEED), I can see that it took ~ 44 minutes to complete:

 

Test 2: catpcat.sql

In this test, I use the new catpcat.sql using catctl.pl, with a parallelism of 4 processes:

This creates catalog and catproc first on CDB$ROOT, than creates them on PDB$SEED. So, same steps but in different orders.

By running vmstat in the background, I noticed during the run that most of the time the creation was running serially, and when there was some parallelism, it was short and compensated by a lot of process synchronizations (waits, sleeps) done by the catctl.pl.

At the end, the process took ~ 45 minutes to complete.

So the answer is no: it is not faster to run catpcat.sql with parallel degree 4 compared to running catalog.sql and catproc.sql serially.

HTH

Ludo

Oracle Home Management – part 7: Putting all together

Last part of the blog series… let’s see how to put everything together and have a single script that creates and provisions Oracle Home golden images:

Review of the points

The scripts will:

  • let create a golden image based on the current Oracle Home
  • save the golden image metadata into a repository (an Oracle schema somewhere)
  • list the avilable golden images and display whether they are already deployed on the current host
  • let provision an image locally (pull, not push), either with the default name or a new name

Todo:

  • Run as root in order to run root.sh automatically (or let specify the sudo command or a root password)
  • Manage Grid Infrastructure homes

Assumptions

  • There is an available Oracle schema where the golden image metadata will be stored
  • There is an available NFS share that contains the working copies and golden images
  • Some variables must be set accordingly to the environment in the script
  • The function setoh is defined in the environment (it might be copied inside the script)
  • The Instant Client is installed and “setoh ic” correctly sets its environment. This is required because there might be no sqlplus binaries available at the very first deploy
  • Oracle Home name and path’s basename are equal for all the Oracle Homes

Repository table

First we need a metadata table. Let’s keep it as simple as possible:

Helpers

The script has some functions that check stuff inside the central inventory.

e.g.

checks if a specific Oracle Home (name) is present in the central inventory. It is helpful to check, for every golden image in the matadata repository, if it is already provisioned or not:

Variables

Some variables must be changed, but in general you might want to adapt the whole script to fit your needs.

Image creation

The image creation would be as easy as creating a zip file, but there are some files that we do not want to include in the golden image, therefore we need to create a staging directory (working copy) to clean up everything:

Home provisioning

Home provisioning requires, beside some checks, a runInstaller -clone command, eventually a relink, eventually a setasmgid, eventually some other tasks, but definitely  run root.sh. This last task is not automated yet in my deployment script.

 

Usage

Examples

List installed homes:

Create a golden image 12_1_0_2_BP170718 from the Oracle Home named OraDB12Home2 (tha latter having been installed manually without naming convention):

List the new golden image from the metadata repository:

Reinstalling the same home with the new naming convention:

Installing the same home in a new path for manual patching from 170718 to 180116:

New home situation:

Patch manually the home named  12_1_0_2_BP180116 with the January bundle patch:

Create the new golden image from the home patched with January bundle patch:

Full source code

Full source code of ohctl

I hope you find it useful! The cool thing is that once you have the golden images ready in the golden image repository, then the provisioning to all the servers is striaghtforward and requires just a couple of minutes, from nothing to a full working and patched Oracle Home.

Why applying the patch manually?

If you read everything carefully, I automated the golden image creation and provisioning, but the patching is still done manually.

The aim of this framework is not to patch all the Oracle Homes with the same patch, but to install the patch ONCE and then deploy the patched home everywhere. Because each patch has different conflicts, bugs, etc, it might be convenient to install it manually the first time and then forget it. At least this is my opinion 🙂

Of course, patch download, conflict detection, etc. can also be automated (and it is a good idea, if you have the time to implement it carefully and bullet-proof).

In the addendum blog post, I will show some scripts made by Hutchison Austria and why I find them really useful in this context.

Blog posts in this series:

Oracle Home Management – part 1: Patch soon, patch often vs. reality
Oracle Home Management – part 2: Common patching patterns
Oracle Home Management – part 3: Strengths and limitations of Rapid Home Provisioning
Oracle Home Management – part 4: Challenges and opportunities of the New Release Model
Oracle Home Management – part 5: Oracle Home Inventory and Naming Conventions
Oracle Home Management – part 6: Simple Golden Image blueprint
Oracle Home Management – part 7: Putting all together
Oracle Home Management – Addendum: Managing and controlling the patch level (berx’s work)

Oracle Home Management – part 6: Simple Golden Image Blueprint

As I explained in the previous blog posts, from a manageability perspective, you should not change the patch level of a deployed Oracle Home, but rather install and patch a new Oracle Home.

With the same principle, Oracle Homes deployed on different hosts should have an identical patch level for the same name. For example, an Oracle Home /u01/app/oracle/product/EE12_1_0_2_BP171018 should have the same patch level on all the servers.

To guarantee the same binaries and patch levels everywhere, the simple solution that I am shoing in this series is to store copies of the Oracle Homes somewhere and use them as golden images. (Another approach, really different and cool, is used by Ilmar Kerm: he explains it here https://ilmarkerm.eu/blog/2018/05/oracle-home-management-using-ansible/ )

For this, we will use a Golden Image store (that could be a NFS share mounted on the Oracle Database servers, or a remote host accessible with scp, or other) and a metadata store.

golden-image-storeWhen all the software is deployed from golden images, there is the guarantee that all the Homes are equal; therefore the information about patches and bugfixes might be centralized in one place (golden image metadata).

A typical Oracle Home lifecycle:

  • Install the software manually the first time
  • Create automatically a golden image from the Oracle Home
  • Deploy automatically the golden image on the other servers

When a new patch is needed:

  • Deploy automatically the golden image to a new Oracle Home
  • Patch manually (or automatically!) the new Oracle Home
  • Create automatically the new golden image with the new name
  • Deploy automatically the new golden image to the other servers

The script that automates this lifecycle does just two main actions:

  • Automates the creation of a new golden image
  • Deploys an existing image to an Oracle Home (either with a new path or the default one)
  • (optional: uninstall an existing Home)

Let’s make a graphical example of the previously described steps:

oh-mgmt-lifecycleHere, the script ohctl takes two actions: -c (creates a Golden Image) and -i (installs a Golden Image)).

The create action does the following steps:

  • Copies the content to a working directory
  • Cleans up logs, audits, etc.
  • Creates the zip file
  • Stores the zip file in a shared NFS repository
  • Inserts the metadata of the new golden image in a repository

The install action does the following steps:

  • Checks if the image is already deployed (plus other security checks)
  • Creates the new path based on the name of the image or the new name passed as argument
  • Unzips the content in the new Oracle Home
  • Runs the runInstaller –clone to attach the home in the central inventory and (optionally) set a new Home name
  • (optionally) Relinks the oracle binary with the RAC option
  • Run setasmgid if found
  • Other environment-specific tasks (e.g. dealing with TNS_ADMIN links)

By following this pattern, Oracle Home names and paths are clean and the same everywhere. This facilitates the deployment and the patching.

You can find the Oracle Home cloning steps in the Oracle Database documentation:

Cloning an Oracle Home

In the next blog post I will explain parts of the ohctl source code and give some examples of how I use it (and publish a link to the full source code 🙂 )

 

Oracle Home Management – part 5: Oracle Home Inventory and Naming Conventions

Having the capability of managing multiple Oracle Homes is fundamental for the following reasons:

  • Out-of-place patching: cloning and patching a new Oracle Home usually takes less downtime than stopping the DBs and patching in-place
  • Better control of downtime windows: if the databases are consolidated on a single server, having multiple Oracle Homes allows moving and patching one database at a time instead of stopping everything and doing a “big bang” patch.

Make sure that you have a good set of scripts that help you to switch correctly from one environment to the other one. Personally, I recommend TVD-BasEnv, as it is very powerful and supports OFA and non-OFA environments, but for this blog series I will show my personal approach.

Get your Home information from the Inventory!

I wrote a blog post sometimes ago that shows how to get the Oracle Homes from the Central Inventory (Using Bash, OK, not the right tool to query XML files, but you get the idea):

Getting the Oracle Homes in a server from the oraInventory

With the same approach, you can have a script to SET your environment:

It uses a different approach from the oraenv script privided by Oracle, where you set the environment based on the ORACLE_SID variable and getting the information from the oratab. My setoh function gets the Oracle Home name as input. Although you can convert it easily to set the environment for a specific ORACLE_SID, there are some reason why I like it:

  • You can set the environment for an Oracle Home that it is not associated to any database (yet)
  • You can set the environment for an upgrade to a new release without changing (yet) the oratab
  • It works for OMS, Grid and Agent homes as well…
  • Most important, it will let you specify correctly the environment when you need to use a  fresh install (for patching it as well)

So, this is how it works:

In the previous example, there are two Database homes that have been installed without a specific naming convention (OraDb11g_home1, OraDB12Home1) and two that follow a specific one (12_1_0_2_BP170718_RON, 12_1_0_2_BP180116_OCW).

Naming conventions play an important role

If you want to achieve an effective Oracle Home management, it is important that you have everywhere the same ORACLE_HOME paths, names and patch levels.

The Oracle Home path should not include only the release number:

If we have many Oracle Homes with the same release, how shall we call the other ones? There are several variables that might influence the naming convention:

Edition (EE, SE), RAC Option or other options, the patch type (formerly PSU, BP: now RU and RUR), eventual additional one-off patches.

Some ideas might be:

The new release model will facilitate a lot the definition of a naming convention as we will have names like:

Of course, the naming convention is not universal and can be adapted depending on the customer (e.g., if you have only Enterprise Editions you might omit this information).

Replacing dots with underscores?

You will see, at the end of the series, that I use Oracle Home paths with underscores instead of dots:

Why?

From a naming perspective, there is no need to have the Home that corresponds to the release number. Release, version and product information can be collected through the inventory.

What is really important is to have good naming conventions and good manageability. In my ideal world, the Oracle Home name inside the central inventory and the basename of the Oracle Home path are the same: this facilitates tremendously the scripting of the Oracle Home provisioning.

Sadly, the Oracle Home name cannot contain dots, it is a limitation of the Oracle Inventory, here’s why I replaced them with underscores.

In the next blog post, I will show how to plan a framework for automated Oracle Home provisioning.

 

Oracle Home Management – part 4: Challenges and Opportunities of the New Release Model

Starting with the upcoming next release (18c), the Oracle Database will be a yearly release. (18c, 19c, etc). New yearly releases will contain only new features ready to go, and eventually some new features for performance improvements (plus bug fixes and security fixes from the previous version.)

Quarterly, instead of Patch Set Updates (PSU) and Bundle Patches (BP), there will be the new Release Updates (RU). They will contain critical fixes, optimizer changes, minor functional enhancements, bug fixes, security fixes. The new Release Updates will be equivalent to what we have now with Bundle Patches.

The Release Updates will be released during the whole lifetime of the feature release, according to the roadmap (2 years or 5 years depending on whether the release is in Long Term Support (LTS) or not). There will be a Long Term Support release every few years. The first two will probably be Oracle 19c and Oracle 23c (I am deliberately supposing that the c will still be relevant 🙂 ).

Beside Release Updates, there will be the new Release Update Revisions (RUR), that according to what I have read until now, will be released “at least” quarterly. Release Update Revisions will contain only regression fixes for bugs introduced by RUs and new security fixes, very close to what we have now with Patch Set Updates.

Release Update Revisions will cover ONLY 6 months, after that it will be necessary to upgrade to a newer Release Update or to a newer major release. Oracle introduced this change to reduce the complexity of their release management.

This leads to a few important things:

  • There will be no more than two RURs for each RU (e.g. 18.2 will have only 18.2.1 and 18.2.2)
  • If applying a RUR, after 6 months at latest, the DBs must be patched to a greater level of RU.
  • Applying the second RUR of each RU (e.g. 18.2.2 -> 18.3.2 -> 18.4.2) is the most conservative approach whilst keeping up to date with the latest critical fixes.

On top of that, one-off patches will still exist. For more information,  please read the note Release Update Introduction and FAQ (Doc ID 2285040.1)

new-release-modelHow will the new release model impact the patching strategy?

It is clear that it will be complex to keep the same major upgrade frequency as today (I expect it to increase). There have been from 3 to 5 years between each major release so far, and switching to a yearly release is a big change.

But the numbering will be easier: 18.3.2 is much more readable/maintainable than 12.2.0.3.BP180719 and, despite it does not contain an explicit date, it keeps it easy to understand the “distance” with the latest release.

So we will have, on one side, the need to upgrade more frequently. But on the other side, the upgrades might be easier than how they are now. One thing is sure, however: we will deal with many more Oracle Homes with different patch levels.

The new release model will bring us a unique opportunity to reinvent our procedures and scripts for Oracle Home management, to achieve a standardized and automated way to solve common problems like:

  • Multiple Oracle Homes coexistence (environment, naming conventions)
  • Automated binaries setup (via golden images or other automatic provisioning)
  • Database patches
  • Database upgrades

In the next post, I will show my idea of how Oracle Homes could be managed (with either the current or the new release model), making their coexistence easier for the DBAs.

Bonus: calculating the distance between releases

For a given release YY.x.z, the distance from its first release is ( x + z -1 ) quarters.

E.g.18.3.2 will be ( 3 + 2 – 1 ) = 4 quarters after the initial release date.

Across versions, assuming that each yearly release will be released in the same quarter, the distance between versions YY1.x1.z1 and YY2.x2.z2  is:

( YY2 – YY1 ) * 4 + ( x2 + z2 ) – ( x1 + z1 ) quarters

E.g. : between 18.4.1 and 20.1.2 the distance will be:

( 20 – 18 ) * 4 + ( 1 + 2 ) – ( 4 + 1 ) = 6 quarters