I was helping a customer the other day together with Mike. They were upgrading from 18c to 19c and had to convert the database to a PDB as well. At first glance, it seemed pretty straightforward, but things got complicated because:

They have standby databases and want the Data Guard setup to survive the plug-in operation.
They are using ASM.
They are using TDE Tablespace Encryption and have also encrypted their SYSTEM and SYSAUX tablespace.
The DBA that will carry out the upgrade and plug-in is not allowed to have the TDE Keystore password. They have separation of duties, so only the security admins have the keystore password.

Can you do that? Yes, you can! Let me tell you how.

Upgrade

First, upgrade the database. You can easily maintain the Data Guard setup during an upgrade. I wrote a blog post about a little while ago. In addition, to upgrade a database with encrypted tablespaces you don’t need the keystore password. You must configure the database to use an auto login keystore, and that’s it. If you are concerned about the use of an auto-login keystore, you can simply remove it again after the upgrade.

External Store for a Keystore Password

The plug-in operation will require the keystore password. But the DBA doesn’t know it – so we need to find a solution for that. The solution is to store the keystore password in an external store. I also wrote a blog post about that a while ago. When you have it configured you can exchange the commands that require a keystore password, like:

SQL> ADMINISTER KEY MANAGEMENT ... KEYSTORE IDENTIFIED BY "S3c3tPassw0rd";

With this:

SQL> ADMINISTER KEY MANAGEMENT ... KEYSTORE IDENTIFIED BY EXTERNAL STORE;

The database will get the keystore password from an external store, which is basically a file in the file system which is encrypted with a password that only the database know.

The security admins would need to do this in the CDB that will receive the non-CDB database. They can do it in advance, so they can relax while the DBA carries out the operation in a maintenance window. If the TDE keystore is already configured using the WALLET_ROOT parameter, you can use the feature right away. Otherwise, you need a database restart to configure it.

Like with the auto-login keystore, if you are concerned about the security, you can simply disable it again after the operation.

Plug In

Now things get complicated. When you plug in your non-CDB database the manifest file contains information on where the data files are located – but only on the primary database. This is an extract of a manifest file (the one you create with DBMS_PDB.DESCRIBE):

<PDB>
  ...
  <tablespace>
    <name>SYSTEM</name>
    ...
	<file>
      <path>+DATA/SALES1/DATAFILE/system.311.1058127529</path>

After plug-in, the CDB can start to use the data files right away. It reads from the manifest files where the data files are located. But there is no information on where files are located on the standby database. To overcome this you must create aliases in the ASM instance on the standby host. The aliases will point back to the original data files (used by the standby database). So, when the plug-in happens and redo start to flow to the standby database, it will know which data files to recover. If you are storing data files in a regular file system, you could use soft links to serve the same purpose.

The procedure is already very well described:

I won’t repeat the procedure as the above articles are really good. But these articles don’t consider the situation where your SYSTEM and/or SYSAUX tablespace is encrypted.

If that is the case, you must import your encryption keys into CDB$ROOT before you execute the CREATE PLUGGABLE DATABASE command. In Reusing the Source Standby Database Files When Plugging a non-CDB as a PDB into the Primary Database of a Data Guard Configuration (Doc ID 2273304.1) it should happen right before step 17.2.2:

SQL> alter session set container=CDB$ROOT;
SQL> administer key management import keys ... keystore identified by external store ... ;
SQL> --Continue with step 17.2.2
SQL> create pluggable database .... ;

Dots and Underscores

When you follow the MOS notes you might wonder why the dots in the ASM aliases are replaced with underscores. At first glance, I had no idea, but it worked. I later learned the following:

The format for an ASM filename is [filetype|tablespacename].[ASM file number].[file incarnation], but basically it is three pieces of name separated by periods. We can’t create any filename or alias that mimics that format. So the scripts change those periods to underscores (‘_’). That is allowed.

ORA-15032 and ORA-15046

Most likely you get this error because there are already existing aliases on the ASM file. Only one alias is allowed per file.

You can verify that by using the ls command in ASMCMD. If it is an alias the Name column will look similar to this alias1 => +DATA/......
Ensure the database is not using the alias. If it does, rename the file in the database.
Remove the alias from ASM. It is strongly recommended to use rmalias. Although also possible with rm I consider it much safer to use rmalias.

Conclusion

You can upgrade and convert your database to a PDB without comprising your standby database. In addition to that, you can configure your database in such a way that you don’t even need to type in the TDE keystore password.

Does this look familiar?

All over Linux you can use arrow up to get the latest command and edit it. But not in SQL*Plus! Something I used to forget many times a day…

I was recently in a presentation where the presenter had a really bad day – typing-wise, that is. He kept making typos all the time and he always ended up copying the text over in an editor, do the changes, and paste back. In the end you could really feel his frustrations (even though it was a virtual presentation). I really felt with that guy, probably because it could just as well be me. Oh, that feeling you get after typing a lengthy SQL only to find a stupid typo!!!

I gave up on my typing skills a long time ago and I am now happy user of SQLcl. So when I make a typo I just do this:

But I still meet a lot of people that are stuck in the last millenium aka SQL*Plus. So, the big question is:

Why aren’t you using SQLcl?

With the alias function in SQLcl I can do this:

With tab completion I can do this:

And look at the beautifully formatted query result I get in SQLcl using set sqlformat ansiconsole:

Still not convinced? Check out this post from fellow Product Manager Jeff Smith in which he sells you SQLcl in 1 minute.

May this be the day where you did your last SQL*Plus typo!

My Aliases

For your reference here are the aliases that I used in the demo:

pdbs = select con_id, name, open_mode, restricted from v$pdbs order by con_id
sc = exec execute immediate 'alter session set container='||:one
scr = alter session set container=CDB$ROOT
ts = select p.con_id, p.name, vt.name, ct.encrypted, ct.status, ct.contents, ct.compress_for from v$tablespace vt, v$pdbs p, cdb_tablespaces ct where vt.con_id=p.con_id(+)  and vt.name = ct.tablespace_name(+) and vt.con_id = ct.con_id (+) order by vt.con_id, vt.name

Tag: alias

Upgrade & Plug In: With ASM, Data Guard, TDE and no Keystore Password