Oracle autoupgrade on Windows and plugin to a Container DB with virtual accounts

In a project I recently had to upgrade an Oracle 12.2.-DB to 19.9. and at the same time migrate from the non-container architecture to the container architecture. The interesting part here is to do this on Windows. Actually both steps (upgrade and plugin) are possible using the autoupgrade tool , which is the preferred tool to do Oracle upgrades today.

Before doing the upgrade at the customer, I performed 2 tests in my personal environment:

– Upgrade 12.2. to 19.9. and the plugin to a container-DB in 2 separate steps
– Upgrade 12.2. to 19.9. and the plugin to a container-DB in 1 step (i.e. calling “autoupgrade -mode deploy” only once)

Doing the upgrade and the plugin in 2 steps requires to manually create the Windows services again after the upgrade finished using the oradim-utility. I.e. first step is the upgrade, which automatically uses a temporary Windows service. At the end of the upgrade new services have to be created manually, because autoupgrade has no clue about the password of the user running the Oracle services. I.e. I used the following config file upgrade_config_122_199_SIMC.cfg.txt:

# Global parameter
# ================

global.autoupg_log_dir=D:Downloadsoracle19cautoupgradelogs
global.target_home=D:appcblvirtualproduct19.9.0dbhome_1
global.target_version=19


# Database parameter SIMC
# =======================

upg1.dbname=simc
upg1.start_time=NOW
upg1.source_home=D:appcblvirtualproduct12.2.0dbhome_1
upg1.sid=simc
upg1.upgrade_node=dbi-pc-cbl
# upg1.add_after_upgrade_pfile=D:Downloadsoracle19cautoupgradepfileadd_after_init.ora

The commands for the upgrade were

%ORACLE_HOME%jdkbinjava -jar autoupgrade.jar -config configupgrade_config_122_199_SIMC.cfg.txt -mode analyze
%ORACLE_HOME%jdkbinjava -jar autoupgrade.jar -config configupgrade_config_122_199_SIMC.cfg.txt -mode fixups
%ORACLE_HOME%jdkbinjava -jar autoupgrade.jar -config configupgrade_config_122_199_SIMC.cfg.txt -mode deploy

And finally executed the oradim-commands on the cmd-prompt:

oradim -delete -sid SIMC
oradim -new -sid SIMC -startmode auto -spfile -shutmode immediate

The second step then was to plugin the non-container-DB with the following config file:

# Global parameter
# ================

global.autoupg_log_dir=D:Downloadsoracle19cautoupgradelogs3
global.target_home=D:appcblvirtualproduct19.9.0dbhome_1
global.target_version=19


# Database parameter SIMC
# =======================

upg1.dbname=simc
upg1.start_time=NOW
upg1.source_home=D:appcblvirtualproduct19.9.0dbhome_1
upg1.sid=simc
upg1.upgrade_node=dbi-pc-cbl
# upg1.add_after_upgrade_pfile=D:Downloadsoracle19cautoupgradepfileadd_after_init.ora
upg1.target_cdb=cdbsimc
upg1.target_pdb_name=PDBSIMC
upg1.target_pdb_copy_option=file_name_convert=('D:APPCBLVIRTUALORADATASIMC','D:APPCBLVIRTUALORADATACDBSIMCSIMC')

As the source and target ORACLE_HOME are the same, no upgrade actually happens, but just the plugin.

During installation of Oracle I did choose to use the virtual account. This worked fine for the migration in 2 steps. Later on we’ll see that the virtual account causes issues during plugin when doing the upgrade and plugin in 1 step.

See this blog with more information about the virtual account on Windows.

Doing the upgrade and plugin in one step has the following advantage:

There’s no need to manually create the Windows service, because the services of the pre-created container-DB are already there.

To do the upgrade and plugin in one step I used the following autoupgrade-config-file after creating an empty container db CDBSIMC:

# Global parameter
# ================

global.autoupg_log_dir=D:Downloadsoracle19cautoupgradelogs2
global.target_home=D:appcblvirtualproduct19.9.0dbhome_1
global.target_version=19


# Database parameter SIMC
# =======================

upg1.dbname=simc
upg1.start_time=NOW
upg1.source_home=D:appcblvirtualproduct12.2.0dbhome_1
upg1.sid=simc
upg1.upgrade_node=dbi-pc-cbl
# upg1.add_after_upgrade_pfile=D:Downloadsoracle19cautoupgradepfileadd_after_init.ora
upg1.target_cdb=cdbsimc
upg1.target_pdb_name=PDBSIMC
upg1.target_pdb_copy_option=file_name_convert=('D:APPCBLVIRTUALORADATASIMC','D:APPCBLVIRTUALORADATACDBSIMCSIMC')

The upgrade went through, but the plugin failed with the following error:

createpdb_simc_PDBSIMC.log:

Start of SQL*Plus output

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 29 22:54:33 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

SQL> SQL> Connected.
SQL> SQL> old   1: create pluggable database "&pdbName" &asClone using '&xmlFilePath' &fileNameConvertOption tempfile reuse
new   1: create pluggable database "PDBSIMC" as clone using 'D:Downloadsoracle19cautoupgradelogs2simcsimc105noncdbtopdbPDBSIMC.xml' COPY file_name_convert=('D:APPCBLVIRTUALORADATASIMC','D:APPCBLVIRTUALORADATACDBSIMCSIMC') tempfile reuse
create pluggable database "PDBSIMC" as clone using 'D:Downloadsoracle19cautoupgradelogs2simcsimc105noncdbtopdbPDBSIMC.xml' COPY file_name_convert=('D:APPCBLVIRTUALORADATASIMC','D:APPCBLVIRTUALORADATACDBSIMCSIMC') tempfile reuse
*
ERROR at line 1:
ORA-19505: failed to identify file
"D:APPCBLVIRTUALORADATASIMCSYSTEM01.DBF"
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.

REMARK: As you can see the plugin is actually a “Clone”. I.e. the original datafiles are kept to be able to go back.

The reason for the issue was that the 12.2.-Window-service runs under permission of the group ORA_OraDB12Home1_SVCACCTS and hence permission to access the associated DB-files are also granted to that group. This does not change during the upgrade to 19c, but of course can be adjusted with oradim. As we skipped the manual step with oradim, the 19c-services for the container DB CDBSIMC (which runs in group ORA_OraDB19Home1_SVCACCTS) do not have access to the DB-files to clone them.

The workaround in my environment was to grant full rights on the db-files to group ORA_OraDB19Home1_SVCACCTS prior to the upgrade/plugin.

Afterwards my upgrade and plugin went through with the following content in the createpdb_simc_PDBSIMC.log:

Start of SQL*Plus output

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 30 01:15:55 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

SQL> SQL> Connected.
SQL> SQL> old   1: create pluggable database "&pdbName" &asClone using '&xmlFilePath' &fileNameConvertOption tempfile reuse
new   1: create pluggable database "PDBSIMC" as clone using 'D:Downloadsoracle19cautoupgradelogs5simcsimc100noncdbtopdbPDBSIMC.xml' COPY file_name_convert=('D:APPCBLVIRTUALORADATASIMC','D:APPCBLVIRTUALORADATACDBSIMCSIMC') tempfile reuse

Pluggable database created.

The best solution here is of course to use the same local account for all Oracle DB-services and not the virtual account. If you plan to use the virtual account then it is a good idea from the beginning to use the same value for the registry entry ORACLE_SVCUSER for all installed versions. E.g. I used ORA_DBSVCACCTS instead of the defaults ORA_OraDB12Home1_SVCACCTS (for 12.2.) and ORA_OraDB19Home1_SVCACCTS (for 19c) during my tests successfully.