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.
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";
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.
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:
- 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)
- Reusing the Source Standby Database Files When Plugging a PDB into the Primary Database of a Data Guard Configuration (Doc ID 2273829.1)
- Blog post by Data Guard Product Manager, Pieter Van Puymbroeck
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
lscommand 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
rmI consider it much safer to use
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.