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.

Photo by frank mckenna on Unsplash
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 (corrected example below on Mar 9, 2021):
Example:
upg1.target_pdb_copy_option=file_name_convert=('+DATA', '+DATA')
.
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
If I read the screenshot of the documentation correctly, the last case (convert to OMF) should have used “upg1.target_pdb_copy_option=file_name_convert=NONE”, which would have used the db_create_file_dest parameter to move the files to the desired location.
Did I misinterpret that?
Unfortunately the db_create_file_dest will be ignored – see the last example of my blog post.
Cheers,
Mike
Upon re-reading the documentation, I noticed my error, but also I find that the documentation is… potentially misleading.
In the beginning it says:
On the target CDB, if you have the parameters DB_CREATE_FILE_DEST or PDB_FILE_NAME_CONVERT set, and you want these parameters on the target CDB to take effect, then set the value of prefix.target_pdb_copy_option=file_name_convert=NONE
I had interpreted this as meaning “If you set “prefix.target_pdb_copy_option=file_name_convert=NONE” then we WILL COPY the datafiles but NOT USE file_name_convert-logic and instead use the db_create_file_dest path and OMF”. This would distinguish it from the default of “NOCOPY”.
Then, at the end of the documentation for target_pdb_copy_option they give an example where they use this exact syntax (“file_name_convert=NONE”) and there they write that this will NOT COPY anything.
I’m confused. 😉
Hi Jan,
I see your point and the confusion. I forwarded your comment to the doc team.
Thanks a lot!
Mike
Hi Mike
When using target_pdb_copy_option, the source database will also be upgraded. So the autoupgrade process appears to upgrade the source datafiles first, then copy across to the target path before plugging into the CDB. Do you know a way to keep the source database untouched, so in my case leaving the 11g database intact whilst providing a 19c pluggable?
Regards
Dave.
Hi Dave,
this is intended. If you want the source “untouched”, you need to clone it at first by yourself. AU can’t do this for you.
Cheers,
Mike
Hi Mike,
How would you handle the conversion from filesystem to ASM on different servers. For instance, I have a non-cdb database on prem using /u01/oradata but want to move to Oracle Cloud on ASM pdb?
Thanks,
Kevin
Hi Kevin,
I think you need to use db_create_file_dest on the ASM diskgroup, and TARGET_PDB_COPY_OPTION=NONE.
Cheers,
Mike
Hi Mike,
I would like to upgrade an 12 DB to 19c and would like to copy the DB Files using target_pdb_copy_option.
12er Database
=============
[oracle@orasrv12 data]$ pwd
/db_files/db/data
[oracle@orasrv12 data]$ ls R12DB/datafile/
o1_mf_sysaux_j31mt4wn_.dbf o1_mf_tisch1_j31nfglt_.dbf o1_mf_users_j31mvoh5_.dbf
o1_mf_system_j31mr3k3_.dbf o1_mf_tisch2_j31nfw05_.dbf
o1_mf_temp_j31myck6_.tmp o1_mf_undotbs1_j31mvl8y_.dbf
19.10 Database
=============
[oracle@orasrv12 data]$ pwd
/db_files/db/data
[oracle@orasrv12 data]$ ls R19DB/
BBC2DBA1DF7157ECE0535C32A8C0DEA4 datafile PMIGDB1
The file should be copied to /db_files/db/data/R19DB/PMIGDB1
upg1.dbname=R12DB
upg1.start_time=NOW
upg1.source_home=/u01/app/oracle/product/12c/home1
upg1.target_home=/u01/app/oracle/product/19c/home2
upg1.sid=R12DB
upg1.log_dir=/home/oracle/upgrade
upg1.upgrade_node=orasrv12.darkwing.net
upg1.target_version=19
upg1.restoration=no
upg1.target_cdb=R19DB
upg1.target_pdb_name=PMIGDB1
upg1.target_pdb_copy_option=file_name_convert=(‘R12DB’, ‘R19DB/PMIGDB1’)
But upgrade crashes
create pluggable database “PMIGDB1” as clone using ‘/home/oracle/upgrade/R12DB/100/noncdbtopdb/PMIGDB1.xml’ COPY file_name_convert=(‘R12DB’, ‘R19DB/PMIGDB1’) tempfile reuse
2021-02-20T21:39:07.821927+01:00
**************************************************************
Undo Create of Pluggable Database PMIGDB1 with pdb id – 4.
**************************************************************
ORA-1276 signalled during: create pluggable database “PMIGDB1” as clone using ‘/home/oracle/upgrade/R12DB/100/noncdbtopdb/PMIGDB1.xml’ COPY file_name_convert=(‘R12DB’, ‘R19DB/PMIGDB1’) tempfile reuse…
Can You give me a hand?
Thx
Christian
Hi Christian,
I would need to see all logs – and it usually the best approach to open an SR please.
My suspicion is that the path for R12DB is not correctly specified. Above, it says: “R12DB/datafile” – but in your conversion you are using one the couple “R12DB” and “R19DB/PMIGDB1” which would assume that you’d have your files in R19DB/PMIGDB1/datafile.
Cheers,
Mike
Hi Mike,
I’m struggling with the target_pdb_copy_option=file_name_convert= option.
If I put
upg1.target_pdb_copy_option=file_name_convert=(‘+DATA/dbname/sales’, ‘+DATA/dbname/depsales’) in my autoupgrade config files autoupgrades stops.
It complains that I’m using OMF and therefore the target can only be a diskgroup.
The path behinde is not allowed.
If I set the target to another disgroup, as +DATA2, everything works fine.
This makes sense, in one way.
When using OMF and PDBs in ASM the whole Path under the diskgroup is set by the database.
Did I miss something?
Regards
Christian
Hi Christian,
I think you will need to use: upg1.target_pdb_copy_option=file_name_convert=(‘+DATA’,’+DATA’)
I will update the blog post as this is not obvious.
Cheers,
Mike
Hello Mike,
Does autoupgrade can upgrade from standalone (asm, OMF) to pdb in a RAC (OMF) (file_convert=NONE) ? We tried but in seems that without converting source database to RAC before, we issued internal error from autoupgrade.
Hi Pierre,
yes, it can. You please need to open an SR in case you have issues.
And upload the zip file generated by AutoUpgrade with the -zip option.
Cheers,
Mike
Hi Mike, Greetings. I am struggling with non-cdb to pdb conversion since i mistakenly mentioned in the config file non omf coversion where as i use omf on the cdb.
upg1.target_pdb_copy_option=file_name_convert=(‘sales’, ‘pdbsales’)
autoupgrade failed during non-cdb to pdb conversion
SQL> old 1: create pluggable database “&pdbName” &asClone using ‘&xmlFilePath’ &fileNameConvertOption tempfile reuse
new 1: create pluggable database “PDBSALES” as clone using ‘/home/oracle/upglogs/sales/sales1/103/noncdbtopdb/PDBSALES.xml’ COPY file_name_convert=(‘sales’, ‘pdbsales’) tempfile reuse
create pluggable database “PDBSALES” as clone using ‘/home/oracle/upglogs/sales/sales1/103/noncdbtopdb/PDBSALES.xml’ COPY file_name_convert=(‘sales’, ‘pdbsales’) tempfile reuse
*
ERROR at line 1:
ORA-01276: Cannot add file +DATA/pdbsales/datafile/system.281.1079091923. File
has an Oracle Managed Files file name.
Now i went back to cfg and changed it to
upg1.target_pdb_copy_option=file_name_convert=(‘+DATA’,’+DATA’)
If i resume the job its not re-reading the change and fails same as the .xml was generated before this change and it uses again and tries to create the pdb and fails.
How to move forward with this autoupgrade which failed after upgrade from 11.2 to 19 but before plugging in as pdb. The autoupgrade keep cutting a new log when i say resume -job 103 and the xml timestamp is old and should have the old info.
-rwx—— 1 oracle asmadmin 7556 Jul 28 23:52 PDBSALES.xml
-rwx—— 1 oracle oinstall 926 Jul 28 23:53 createpdb_sales_PDBSALES_20210729071739.log
-rwx—— 1 oracle oinstall 926 Jul 29 07:17 createpdb_sales_PDBSALES_20210729073328.log
-rwx—— 1 oracle oinstall 926 Jul 29 07:37 createpdb_sales_PDBSALES_20210729073817.log
-rwx—— 1 oracle oinstall 926 Jul 29 07:38 createpdb_sales_PDBSALES_20210729080927.log
-rw-r–r– 1 oracle oinstall 926 Jul 29 08:09 createpdb_sales_PDBSALES.log
I do not have access to MOS hence any detailed reply is highly appreciated. Thank you so much for all your wonderful teachings..
Hi Swamy,
has the PDB been plugged in yet? Please connect to your receiving CDB and check. If the PDB is not there, all is fine.
Then you proceed with a new config file and a new AutoUpgrade session to plugin ONLY:
https://mikedietrichde.com/2020/08/04/oracle-autoupgrade-between-two-servers-and-plugin/
If the PDB is already plugged in, then you need to proceed manually.
Cheers,
Mike
Hi!
unplug/plug pdb
upg1.target_pdb_copy_option.mypdb=file_name_convert=(‘CDB19’, ‘CDB21’)
How can i do MOVE TEMPFILE REUSE not COPY pdb to enother directory with autoupgrade.jar?
Hi AlexVin,
I think AU does not implement the MOVE.
Thanks,
Mike
Good day, Mike.
Thank you for your great video on auto upgrade tool. I really enjoyed it and used it to upgrade our three 11g databases to 19c.
I have another project to migrate our 2 node RAC database (non CDB) on 12.1.0.2 (OEL 6) to 19c (CDB), also 2 node RAC , but with limited downtime. The source database has one physical standby database.
I am proposing the following plan of actions but not sure it makes sense!
Step 1
======
Install 19c binaries on destination server and create CDB
Step 2
=======
Instance 12.1.0.2 binaries on destination server and set up data guard to source database, and create second standby database called standby test (for example)
Step 3
======
Once both source and destination 2 node RAC database (non CDB) are in sync, I will perform a failover to destination database (on 12c!) standby test.
I want to believe the source database (and first standby database) will be unaffected.
Step 4
======
On destination server, I will follow your great video and prepare a config file and upgrade the 12.1.0.2 to 19c.
Please does the following step make any sense?
Thank you
Anthony
Hi Anthony,
yes, you can do this certainly and avoid the copy time to the new server with this technique. But you still will have downtime for the upgrade, and of course the plugin. AutoUpgrade will help you as you can see in the Seminar Episode #14.
Thanks
Mike