More information on AutoUpgrade’s TARGET_PDB_COPY_OPTION

A few weeks ago I blogged about the 3 new parameter options to plugin a non-CDB right after the upgrade and make it a PDB. As I explained, you must create the receiving CDB by yourself. But from your emails and comments, we realized that the examples in the documentation are not sufficient. Hence, today I’d like to give more information on AutoUpgrade’s TARGET_PDB_COPY_OPTION parameter.

What does this parameter do?

At first, this AutoUpgrade parameter (not an init.ora parameter!) is optional. If you don’t set it, the NOCOPY option will be used. Your upgraded database stays in place. AutoUpgrade will plug it into your existing CDB you specified with the AutoUpgrade parameter TARGET_CDB.

Now the questions you’ve asked were mostly regarding “How do I set the parameter if I’m in ASM?” and “How do I use it with OMF in file system?” besides “Can I use it to switch to OMF?“.

And I’m so happy that I work for such a great team where the developers really listen and react. Plus in addition, we have a very responsive doc writer (thanks Doug!!) who understands and wants to improve the documentation constantly.

Find more examples in the documentation

Please find the revised part of the documentation here:

I know, this is long. But let me pick the examples and show them.

File system to file system

This is the most simple example – and the one I use typically in the Hands-On Lab and on the blog as my databases currently sit in file system.

Example:

upg1.target_pdb_copy_option=file_name_convert=('/old/path/non_CDB', '/new/path/CDB/new_PDB')

You can also just mask patterns as I do often – so instead of (‘/u02/oradata/FTEX’,’/u02/oradata/CDB2/pdb1′) you can shrink this to the diff, e.g. (‘FTEX’,’CDB2/pdb1′)as /u02/oradata is at the same position in both paths.

.

ASM to ASM / OMF to OMF

This is also not complicated. But there is something VERY important to know. You need to be at least on Oracle 19.6.0 or on Oracle 18.10.0 to have this work. And this is not an AutoUpgrade issue but bug 29399046 which prevents this conversion with OMF named files.

I typically don’t use OMF in file system.. Life can be so simple without OMF. My CDB looks like this with OMF – and especially the GUID in the path for each PDB makes it a bit rough I’d say:

select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------
/u02/oradata/CDB/CDB2/datafile/o1_mf_system_hkcyjsfn_.dbf
/u02/oradata/CDB/CDB2/878B6F392367587EE0532AB2A8C01897/datafile/o1_mf_system_hkcyns6d_.dbf
/u02/oradata/CDB/CDB2/datafile/o1_mf_sysaux_hkcykdyk_.dbf
/u02/oradata/CDB/CDB2/878B6F392367587EE0532AB2A8C01897/datafile/o1_mf_sysaux_hkcyo56c_.dbf
/u02/oradata/CDB/CDB2/datafile/o1_mf_undotbs1_hkcykwg9_.dbf
/u02/oradata/CDB/CDB2/878B6F392367587EE0532AB2A8C01897/datafile/o1_mf_undotbs1_hkcyol4f_.dbf
/u02/oradata/CDB/CDB2/datafile/o1_mf_users_hkcylb50_.dbf

But back to the bug I’m referring to above:

 

If you are either on 19.6.0 or newer or 18.10.0 or newer, then this will work:

Example:

upg1.target_pdb_copy_option=file_name_convert=('+DATA/dbname/sales', '+DATA/dbname/depsales')

.

Keep the structure

In case you’d like to keep the structure, you can use the value NONE.

Example:

upg1.target_pdb_copy_option=file_name_convert=NONE

.

non-OMF to OMF?

[Corrected]
Actually this was a customer’s question: “Can I move my non-CDB straight with AutoUpgrade with the plugin operation from non-OMF to OMF?”

Yes. You. Can.

I didn’t try this in ASM but I know that it works fine. But what if you have a receiving CDB on OMF, but your non-CDB isn’t? For this test I set:

SQL> show parameter create

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u02/oradata/CDB
db_create_online_log_dest_1          string      /u02/oradata/CDB

and I use this parameter in my config.cfg file for AutoUpgrade:

upg1.target_pdb_copy_option=file_name_convert=NONE

Afterwards, when AutoUpgrade is done with the job, everything is OMF.

select file#, name from v$datafile order by file#

     FILE# NAME
---------- ------------------------------------------------------------------------------------------
    1 /u02/oradata/CDB/CDB2/datafile/o1_mf_system_hkgm94rv_.dbf
    2 /u02/oradata/CDB/CDB2/878B6F392367587EE0532AB2A8C01897/datafile/o1_mf_system_hkgmbnk0_.dbf
    3 /u02/oradata/CDB/CDB2/datafile/o1_mf_sysaux_hkgm8oss_.dbf
    4 /u02/oradata/CDB/CDB2/878B6F392367587EE0532AB2A8C01897/datafile/o1_mf_sysaux_hkgmc0y7_.dbf
    5 /u02/oradata/CDB/CDB2/datafile/o1_mf_undotbs1_hkgm8bcy_.dbf
    6 /u02/oradata/CDB/CDB2/878B6F392367587EE0532AB2A8C01897/datafile/o1_mf_undotbs1_hkgmcfqk_.dbf
    7 /u02/oradata/CDB/CDB2/datafile/o1_mf_users_hkgm7yo4_.dbf
    8 /u02/oradata/CDB/CDB2/AAFB094521FF3DE9E055000000000001/datafile/o1_mf_system_hkgpcxn6_.dbf
    9 /u02/oradata/CDB/CDB2/AAFB094521FF3DE9E055000000000001/datafile/o1_mf_sysaux_hkgpcxnn_.dbf
   10 /u02/oradata/CDB/CDB2/AAFB094521FF3DE9E055000000000001/datafile/o1_mf_users_hkgpcxnn_.dbf
   11 /u02/oradata/CDB/CDB2/AAFB094521FF3DE9E055000000000001/datafile/o1_mf_undotbs1_hkgpcxno_.dbf

.

Further Information and Links

• AutoUpgrade and Plugin – with a single command (plus Video)
• Youtube Video: AutoUpgrade to Oracle 19c and plug in a CDB

–Mike