Introduction
Oracle 23c is not yet available on Oracle Database Appliance (ODA), but it should be ready in 2024. As 23c is the next Long Term release, 19c databases will probably migrate to 23c in the coming years. If you decided to use DB Systems on your ODA, you might think that migration will not be that easy. But it’s not true: it’s rather easy and quite straight through. Let’s try migrating from 19c to 21c, which is probably quite similar to migrating to 23c.
ODA and virtualization
It’s been years now that ODA relies on KVM for virtualization. Each ODA, whatever its generation (from X7 to X10), is deployed as a Bare Metal appliance and can run databases on the host itself, but at any time you can create VMs on it, for databases or for other usages. VMs running databases are called DB Systems, and if you already tried or use OCI, the Oracle public cloud, it’s exactly the same concept. For those who don’t know what’s a DB Systems: it’s basically a VM dedicated to a single database and managed at the system level. On ODA, when you need to increase the resources of a container database, you just configure the DB System with a bigger shape, and the CDB parameters will increase accordingly.
If the database you configure is a container database, your DB System is not really limited to a single database but it will be limited by your Edition and options. Standard Edition 2 and Enterprise Edition without the Multitenant option are limited to 3 pluggable databases in one container. With Enterprise Edition and the Multitenant option, consider the number of pluggable databases nearly unlimited (something like 4096 PDBs).
Multitenant is the key
If you’d like to simplify your future migrations, you will need to use Multitenant. Your pluggable databases are able to move from one container to another without using datapump or RMAN for this. Remember that a pluggable database is basically a set of tablespaces with their metadata inside a local SYSTEM tablespace. Unplugging/plugging a pluggable database is similar to the concept of transportable tablespaces you may have used on previous versions.
Configuring your ODA for DB Systems
For creating a first DB System, you will need a VM template from a link you’ll find in the ODA documentation. It’s called “Oracle Database Appliance KVM DB System Template” and you should download the one corresponding to your ODA patch level. This template is available on MOS as patch 32451228.
Once downloaded, you need to unzip this template and register it in your ODA repository.
cd /opt/dbi/
unzip p32451228_1920000_Linux-x86-64.zip
odacli update-repository -f /opt/dbi/odacli-dcs-19.20.0.0.0-ODAVM.zip
odacli describe-job -i 3c8c3740-aec1-4a15-96f8-0b2e3f1d34cc
...
odacli describe-dbsystem-image | grep -v not-available
DB System Image details
--------------------------------------------------------------------------------
Component Name Supported Versions Available Versions
-------------------- -------------------- --------------------
DBVM 19.20.0.0.0 19.20.0.0.0
GI 19.20.0.0.230718 19.20.0.0.230718
DB 19.20.0.0.230718 19.20.0.0.230718
This template is a kind of Linux boot disk without any Oracle database related stuff. You will also need a GI clone and a DB clone in your repository. You probably already have the latest 19c GI clone in your repository, because you created (or patched) your appliance with this clone. The DB clone should also be available if you created a first 19c Bare Metal database. If it’s not the case, download and register the DB clone in your repository.
You may have noticed that it’s also possible to create 21c DB Systems, although it’s not possible using 21c with Bare Metal. This is because 21c is an innovation release: Oracle didn’t make it available for your Bare Metal setup for stability reasons. But inside a DB System it is OK.
Preparing the CPU pool
A DB System will need CPU resources, they are configured through CPU pools. There are 3 kinds of CPU pools, bare metal CPU pools, VM (multipurpose) CPU pools and DB System CPU pools. One DB System CPU pool is enough for now (configured for 6 cores):
odacli create-cpupool -n cpu4dbs -c 6 -dbs
sleep 30; odacli list-cpupools
Name Type Configured on Cores Associated resources Created Updated
-------------------- ------------------ ------------------------- ----- ------------------------------ ------------------------ ------------------------
cpu4dbs DB_SYSTEM_SHARED dbioda01 6 NONE 2023-11-09 13:31:06 CET 2023-11-09 13:31:55 CET
Creating a 19c DB System
You will need a json file for creating your first DB System. In this file, you will provide:
- hostname and IP of the VM
- CPU pool to use
- users, groups and their ids on the system (should be the same as your appliance deployment)
- Database name and shape (will also define VM’s shape: CPU numbers and RAM size)
- Database’s specs: regional settings and multitenant features
- the network to use
Here is an example:
cat create_dbs19.json
{
"system": {
"name": "dbs19",
"systemPassword": "*******",
"timeZone": "Europe/Zurich",
"diskGroup": "DATA",
"cpuPoolName": "cpu4dbs",
"enableRoleSeparation": true,
"customRoleSeparation": {
"groups": [
{
"name": "oinstall",
"id": 1001,
"role": "oinstall"
},
{
"name": "dbaoper",
"id": 1002,
"role": "dbaoper"
},
{
"name": "dba",
"id": 1003,
"role": "dba"
},
{
"name": "asmadmin",
"id": 1004,
"role": "asmadmin"
},
{
"name": "asmoper",
"id": 1005,
"role": "asmoper"
},
{
"name": "asmdba",
"id": 1006,
"role": "asmdba"
}
],
"users": [
{
"name": "grid",
"id": 1000,
"role": "gridUser"
},
{
"name": "oracle",
"id": 1001,
"role": "oracleUser"
}
]
}
},
"database": {
"name": "CDB19",
"uniqueName": "CDB19_S1",
"domainName": "dbi-lab.ch",
"adminPassword": "*********",
"version": "19.20.0.0.230718",
"edition": "EE",
"type": "SI",
"dbClass": "OLTP",
"shape": "odb2",
"role": "PRIMARY",
"targetNodeNumber": null,
"enableDbConsole": false,
"enableFlashStorage": false,
"redundancy": null,
"characterSet": {
"characterSet": "AL32UTF8",
"nlsCharacterset": "AL16UTF16",
"dbTerritory": "SWITZERLAND",
"dbLanguage": "FRENCH"
},
"rmanBackupPassword": null,
"enableTDE": false,
"isCdb": true,
"pdbName": "pdb19",
"pdbAdminUser": "pdbadmin",
"tdePassword": null
},
"network": {
"domainName": "dbi-lab.ch",
"ntpServers": [
"10.86.0.250"
],
"dnsServers": [
"10.86.0.250"
],
"nodes": [
{
"name": "dbs19",
"ipAddress": "10.86.0.242",
"netmask": "255.255.255.0",
"gateway": "10.86.0.1",
"number": 0
}
],
"publicVNetwork": "pubnet"
},
"grid": {
"language": "en",
"enableAFD": false
}
}
Creating a DB System is quite slow: it will take 30 minutes. But once created, everything is ready to use:
odacli create-dbsystem -p /opt/dbi/create_dbs19.json
Enter password for system "dbs19":
Retype password for system "dbs19":
Enter administrator password for DB "CDB19":
Retype administrator password for DB "CDB19":
...
odacli describe-job -i c42e98e5-6a53-4302-95e7-a067c374df69
Job details
----------------------------------------------------------------
ID: c42e98e5-6a53-4302-95e7-a067c374df69
Description: DB System dbs19 creation
Status: Success
Created: November 13, 2023 3:29:48 PM CET
Message:
Task Name Start Time End Time Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Create DB System metadata November 13, 2023 3:29:48 PM CET November 13, 2023 3:29:49 PM CET Success
Persist new DB System November 13, 2023 3:29:49 PM CET November 13, 2023 3:29:49 PM CET Success
Validate DB System prerequisites November 13, 2023 3:29:49 PM CET November 13, 2023 3:29:52 PM CET Success
Setup DB System environment November 13, 2023 3:29:52 PM CET November 13, 2023 3:29:52 PM CET Success
Create DB System ASM volume November 13, 2023 3:29:52 PM CET November 13, 2023 3:29:58 PM CET Success
Create DB System ACFS mount point November 13, 2023 3:29:58 PM CET November 13, 2023 3:29:58 PM CET Success
Create DB System ACFS filesystem November 13, 2023 3:29:58 PM CET November 13, 2023 3:30:06 PM CET Success
Create DB System VM ACFS snapshots November 13, 2023 3:30:06 PM CET November 13, 2023 3:30:31 PM CET Success
Create temporary SSH key pair November 13, 2023 3:30:31 PM CET November 13, 2023 3:30:31 PM CET Success
Create DB System cloud-init config November 13, 2023 3:30:31 PM CET November 13, 2023 3:30:32 PM CET Success
Provision DB System VM(s) November 13, 2023 3:30:32 PM CET November 13, 2023 3:30:35 PM CET Success
Attach disks to DB System November 13, 2023 3:30:35 PM CET November 13, 2023 3:30:35 PM CET Success
Add DB System to Clusterware November 13, 2023 3:30:35 PM CET November 13, 2023 3:30:35 PM CET Success
Start DB System November 13, 2023 3:30:35 PM CET November 13, 2023 3:30:37 PM CET Success
Wait DB System VM first boot November 13, 2023 3:30:37 PM CET November 13, 2023 3:31:53 PM CET Success
Setup Mutual TLS (mTLS) November 13, 2023 3:31:53 PM CET November 13, 2023 3:32:10 PM CET Success
Export clones repository November 13, 2023 3:32:10 PM CET November 13, 2023 3:32:10 PM CET Success
Setup ASM client cluster config November 13, 2023 3:32:10 PM CET November 13, 2023 3:32:12 PM CET Success
Copy ASM client cluster config November 13, 2023 3:32:12 PM CET November 13, 2023 3:32:12 PM CET Success
Install DB System November 13, 2023 3:32:12 PM CET November 13, 2023 4:03:36 PM CET Success
Cleanup temporary SSH key pair November 13, 2023 4:03:36 PM CET November 13, 2023 4:03:36 PM CET Success
Set DB System as configured November 13, 2023 4:03:36 PM CET November 13, 2023 4:03:37 PM CET Success
Creating a 21c/23c DB System
23c is not yet available on ODA, let’s create a 21c DB System instead.
You will first need to provide 21c GI and DB clones to your ODA repository, previously downloaded from MOS. Patch numbers are 33152235 and 33152237. 21c being no more updated now, you will need to download these files for an older ODA patch release (19.17).
cd /opt/dbi/
unzip p33152235_1917000_Linux-x86-64.zip
unzip p33152237_1917000_Linux-x86-64.zip
odacli update-repository -f /opt/dbi/odacli-dcs-19.17.0.0.0-221028-GI-21.8.0.0.zip
sleep 30 ; odacli update-repository -f /opt/dbi/odacli-dcs-19.17.0.0.0-221028-DB-21.8.0.0.zip
sleep 30; odacli describe-dbsystem-image | grep -v not-available
DB System Image details
--------------------------------------------------------------------------------
Component Name Supported Versions Available Versions
-------------------- -------------------- --------------------
DBVM 19.20.0.0.0 19.20.0.0.0
GI 19.20.0.0.230718 19.20.0.0.230718
21.8.0.0.221018 21.8.0.0.221018
DB 19.20.0.0.230718 19.20.0.0.230718
21.8.0.0.221018 21.8.0.0.221018
My json file will be quite similar to the 19c’s one, let’s show the differences between the 2 files:
diff create_dbs19.json create_dbs23.json
3c3
< "name": "dbs19",
---
> "name": "dbs23",
57,58c57,58
< "name": "CDB19",
< "uniqueName": "CDB19_S1",
---
> "name": "CDB23",
> "uniqueName": "CDB23_S1",
61c61
< "version": "19.20.0.0.230718",
---
> "version": "21.8.0.0.221018",
80c80
< "pdbName": "pdb19",
---
> "pdbName": "pdb23",
94,95c94,95
< "name": "dbs19",
< "ipAddress": "10.86.0.242",
---
> "name": "dbs23",
> "ipAddress": "10.86.0.243",
Nothing special here.
Let’s create this new DB System and wait 30 minutes:
odacli create-dbsystem -p /opt/dbi/create_dbs23.json
Enter password for system "dbs23":
Retype password for system "dbs23":
Enter administrator password for DB "CDB23":
Retype administrator password for DB "CDB23":
odacli describe-job -i f25b1820-f781-4050-8f06-526b2a34a015
Job details
----------------------------------------------------------------
ID: f25b1820-f781-4050-8f06-526b2a34a015
Description: DB System dbs23 creation
Status: Success
Created: November 17, 2023 12:14:05 PM CET
Message:
Task Name Start Time End Time Status
---------------------------------------- ----------------------------------- ----------------------------------- ----------
Create DB System metadata November 17, 2023 12:14:05 PM CET November 17, 2023 12:14:05 PM CET Success
Persist new DB System November 17, 2023 12:14:05 PM CET November 17, 2023 12:14:05 PM CET Success
Validate DB System prerequisites November 17, 2023 12:14:05 PM CET November 17, 2023 12:14:08 PM CET Success
Setup DB System environment November 17, 2023 12:14:08 PM CET November 17, 2023 12:14:08 PM CET Success
Create DB System ASM volume November 17, 2023 12:14:08 PM CET November 17, 2023 12:14:14 PM CET Success
Create DB System ACFS mount point November 17, 2023 12:14:14 PM CET November 17, 2023 12:14:14 PM CET Success
Create DB System ACFS filesystem November 17, 2023 12:14:14 PM CET November 17, 2023 12:14:22 PM CET Success
Create DB System VM ACFS snapshots November 17, 2023 12:14:22 PM CET November 17, 2023 12:14:47 PM CET Success
Create temporary SSH key pair November 17, 2023 12:14:47 PM CET November 17, 2023 12:14:48 PM CET Success
Create DB System cloud-init config November 17, 2023 12:14:48 PM CET November 17, 2023 12:14:49 PM CET Success
Provision DB System VM(s) November 17, 2023 12:14:49 PM CET November 17, 2023 12:14:51 PM CET Success
Attach disks to DB System November 17, 2023 12:14:51 PM CET November 17, 2023 12:14:51 PM CET Success
Add DB System to Clusterware November 17, 2023 12:14:51 PM CET November 17, 2023 12:14:52 PM CET Success
Start DB System November 17, 2023 12:14:52 PM CET November 17, 2023 12:14:53 PM CET Success
Wait DB System VM first boot November 17, 2023 12:14:53 PM CET November 17, 2023 12:16:09 PM CET Success
Setup Mutual TLS (mTLS) November 17, 2023 12:16:09 PM CET November 17, 2023 12:16:27 PM CET Success
Export clones repository November 17, 2023 12:16:27 PM CET November 17, 2023 12:16:27 PM CET Success
Setup ASM client cluster config November 17, 2023 12:16:27 PM CET November 17, 2023 12:16:29 PM CET Success
Copy ASM client cluster config November 17, 2023 12:16:29 PM CET November 17, 2023 12:16:29 PM CET Success
Install DB System November 17, 2023 12:16:29 PM CET November 17, 2023 12:45:38 PM CET Success
Cleanup temporary SSH key pair November 17, 2023 12:45:38 PM CET November 17, 2023 12:45:39 PM CET Success
Set DB System as configured November 17, 2023 12:45:39 PM CET November 17, 2023 12:45:39 PM CET Success
Migrating a 19c PDB to 21c DB System
My 21c/23c DB System is actually the one that will replace my 19c DB System. Instead of migrating my 19c DB System to a 21c DB System, I will try unplugging my 19c PDB and plugging in into my 21c DB System.
Let’s locate my files on my 19c PDB:
ssh [email protected]
su - oracle
. oraenv <<< CDB19
sqlplus / as sysdba
alter session set container=PDB19;
select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/CDB19_S1/0A0AE962C44E83DBE063F200240A6B97/DATAFILE/system.361.1152806347
+DATA/CDB19_S1/0A0AE962C44E83DBE063F200240A6B97/DATAFILE/sysaux.360.1152806347
+DATA/CDB19_S1/0A0AE962C44E83DBE063F200240A6B97/DATAFILE/undotbs1.359.1152806347
+DATA/CDB19_S1/0A0AE962C44E83DBE063F200240A6B97/DATAFILE/users.363.1152806351
exit
exit
exit
My datafiles reside in ASM, let’s find out if I can reach them from the 21c DB System:
ssh [email protected]
su - grid
asmcmd ls +DATA/CDB19_S1/0A0AE962C44E83DBE063F200240A6B97/DATAFILE
SYSAUX.360.1152806347
SYSTEM.361.1152806347
UNDOTBS1.359.1152806347
USERS.363.1152806351
exit
exit
I can see the datafiles on my new DB System, let’s try migrating PDB19 to this new DB System.
ssh [email protected]
su - oracle
. oraenv <<< CDB19
sqlplus / as sysdba
alter pluggable database PDB19 close immediate;
Pluggable database altered.
alter pluggable database PDB19 unplug into '/tmp/PDB19.xml';
Pluggable database altered.
exit
scp /tmp/PDB19.xml [email protected]:/tmp/
exit
ssh [email protected]
su - oracle
. oraenv <<< CDB23
sqlplus / as sysdba
show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB23 READ WRITE NO
create pluggable database PDB19 using '/tmp/PDB19.xml' COPY;
create pluggable database PDB19 using '/tmp/PDB19.xml' COPY
*
ERROR at line 1:
ORA-19505: failed to identify file
"+DATA/CDB19_S1/0A0AE962C44E83DBE063F200240A6B97/DATAFILE/system.361.1152806347"
ORA-17503: ksfdopn:2 Failed to open file
+DATA/CDB19_S1/0A0AE962C44E83DBE063F200240A6B97/DATAFILE/system.361.1152806347
ORA-15260: permission denied on ASM disk group
There is a catch: files are not usable on this DB System. It makes sense, otherwise it could be a security flaw. Let’s change the permission on these files from the Bare Metal ASM instance:
exit
exit
exit
su - grid
asmcmd chmod a+r +DATA/CDB19_S1/0A0AE962C44E83DBE063F200240A6B97/DATAFILE/*
exit
ssh [email protected]
su - oracle
. oraenv <<< CDB23
sqlplus / as sysdba
create pluggable database PDB19 using '/tmp/PDB19.xml' COPY;
Pluggable database created.
show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB23 READ WRITE NO
4 PDB19 MOUNTED
alter pluggable database pdb19 open;
Warning: PDB altered with errors.
show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB23 READ WRITE NO
4 PDB19 MIGRATE YES
exit
The PDB19 is in migrate mode, maybe datapatch should be applied before opening the PDB?
$ORACLE_HOME/OPatch/datapatch -pdbs PDB19
SQL Patching tool version 21.8.0.0.0 Production on Fri Nov 17 15:04:19 2023
Copyright (c) 2012, 2022, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/homes/OraDB21000_home1/cfgtoollogs/sqlpatch/sqlpatch_45518_2023_11_17_15_04_19/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Error: prereq checks failed!
Failure during bootstrap
Prereq check failed, exiting without installing any patches.
Please refer to MOS Note 1609718.1 and/or the invocation log
/u01/app/oracle/homes/OraDB21000_home1/cfgtoollogs/sqlpatch/sqlpatch_45518_2023_11_17_15_04_19/sqlpatch_invocation.log
for information on how to resolve the above errors.
SQL Patching tool complete on Fri Nov 17 15:04:21 2023
Looking into the log file, I can see several errors related to timezone:
[2023-11-17 15:04:21] PDB19: Error in bootstrap log /u01/app/oracle/homes/OraDB21000_home1/cfgtoollogs/sqlpatch/sqlpatch_45518_2023_11_17_15_04_19/bootstrap1_CDB23_PDB19.log:
-> Error at line 15: script sqlpatch_bootstrap.sql
- ORA-01804: failure to initialize timezone information
-> Error at line 25: script sqlpatch_bootstrap.sql
- SP2-1506: START, @ or @@ command has no arguments
-> Error at line 29: script sqlpatch_bootstrap.sql
- ORA-01804: failure to initialize timezone information
-> Error at line 59: script sqlpatch_bootstrap.sql
- ORA-00904: "DBMS_REGISTRY"."SCHEMA_LIST_STRING": invalid identifier
-> Error at line 135: script prvtsqlpatch.plb
- ORA-01804: failure to initialize timezone information
It makes me think about problems I had in the past where timezone should be migrated. In fact, my 19.20 container is more recent than my 21.8 container, just because Oracle stopped providing updates for this innovation release.
Let’s check the timezone version on both containers:
19c:
SQL> select * from v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_42.dat 42 0
21c:
select * from v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_39.dat 39 0
That’s it. I will need to copy the timezone file from my 19c DB System to my 21c DB home:
scp /u01/app/oracle/product/19.0.0.0/dbhome_1/oracore/zoneinfo/timezlrg_42.dat [email protected]:/u01/app/oracle/product/21.0.0.0/dbhome_1/oracore/zoneinfo/
scp /u01/app/oracle/product/19.0.0.0/dbhome_1/oracore/zoneinfo/big/timezlrg_42.dat [email protected]:/u01/app/oracle/product/21.0.0.0/dbhome_1/oracore/zoneinfo/big/
scp /u01/app/oracle/product/19.0.0.0/dbhome_1/oracore/zoneinfo/little/timezlrg_42.dat [email protected]:/u01/app/oracle/product/21.0.0.0/dbhome_1/oracore/zoneinfo/little/
Now let’s remove the PDB and plug it in another time:
drop pluggable database PDB19 including datafiles;
Pluggable database dropped.
create pluggable database PDB19 using '/tmp/PDB19.xml' COPY;
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB23 READ WRITE NO
5 PDB19 MOUNTED
alter pluggable database pdb19 open;
In another session, let’s look at what’s happening:
show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB23 READ WRITE NO
5 PDB19 MIGRATE YES
PDB is being migrated automatically. 10 minutes after, my PDB is migrated but there are some errors:
Warning: PDB altered with errors.
select message, action from PDB_PLUG_IN_VIOLATIONS where status = 'PENDING' and con_id = 5;
MESSAGE
--------------------------------------------------------------------------------
ACTION
--------------------------------------------------------------------------------
Interim patch 34282948/24963304 (DUPLICATE DATABASE COMMAND FAILS WITH ERROR ORA
-00907 WHEN ORIGINAL DB IS IN NON STANDARD NLS LANGUA): Installed in the CDB but
not in the PDB
Call datapatch to install in the PDB or the CDB
'21.8.0.0.0 Release_Update 2209301729' is installed in the CDB but no release up
dates are installed in the PDB
Call datapatch to install in the PDB or the CDB
exit
OK, let’s apply the datapatch now:
$ORACLE_HOME/OPatch/datapatch -pdbs PDB19
SQL Patching tool version 21.8.0.0.0 Production on Fri Nov 17 15:53:06 2023
Copyright (c) 2012, 2022, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/homes/OraDB21000_home1/cfgtoollogs/sqlpatch/sqlpatch_78019_2023_11_17_15_53_06/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
Interim patch 34282948 (DUPLICATE DATABASE COMMAND FAILS WITH ERROR ORA-00907 WHEN ORIGINAL DB IS IN NON STANDARD NLS LANGUA):
Binary registry: Installed
PDB PDB19: Not installed
Current state of release update SQL patches:
Binary registry:
21.8.0.0.0 Release_Update 220930172918: Installed
PDB PDB19:
No release update patches installed
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: PDB19
No interim patches need to be rolled back
Patch 34527084 (Database Release Update : 21.8.0.0.221018 (34527084)):
Apply from 21.1.0.0.0 Feature Release to 21.8.0.0.0 Release_Update 220930172918
The following interim patches will be applied:
34282948 (DUPLICATE DATABASE COMMAND FAILS WITH ERROR ORA-00907 WHEN ORIGINAL DB IS IN NON STANDARD NLS LANGUA)
Installing patches...
Patch installation complete. Total patches installed: 2
Validating logfiles...done
Patch 34527084 apply (pdb PDB19): SUCCESS
logfile: /u01/app/oracle/homes/OraDB21000_home1/cfgtoollogs/sqlpatch/34527084/24956767/34527084_apply_CDB23_PDB19_2023Nov17_15_53_17.log (no errors)
Patch 34282948 apply (pdb PDB19): SUCCESS
logfile: /u01/app/oracle/homes/OraDB21000_home1/cfgtoollogs/sqlpatch/34282948/24963304/34282948_apply_CDB23_PDB19_2023Nov17_15_54_59.log (no errors)
SQL Patching tool complete on Fri Nov 17 16:14:42 2023
It took 20 minutes to complete, but as I already said, 21c is not a production release.
Let’s check the files belonging to this PDB:
show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB23 READ WRITE NO
5 PDB19 READ WRITE YES
alter session set container=pdb19;
Session altered.
select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/CDB23_S1/0A0AE962C44E83DBE063F200240A6B97/DATAFILE/users.385.1153150871
+DATA/CDB23_S1/0A0AE962C44E83DBE063F200240A6B97/DATAFILE/undotbs1.386.1153150871
+DATA/CDB23_S1/0A0AE962C44E83DBE063F200240A6B97/DATAFILE/sysaux.387.1153150871
+DATA/CDB23_S1/0A0AE962C44E83DBE063F200240A6B97/DATAFILE/system.384.1153150871
The files have been copied as expected. This migrated PDB can now live its life as a 21c PDB.
Remove PDB19 from the source CDB
I can keep my PDB19 on my CDB19 as datafiles have been copied, but I can also remove the PDB from its source container:
ssh [email protected]
. oraenv <<< CDB19
sqlplus / as sysdba
show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB19 MOUNTED
drop pluggable database PDB19 including datafiles;
exit
exit
exit
I can also remove my DB System later if PDB19 was the last PDB to be migrated:
odacli list-dbsystems
Name Shape Cores Memory GI version DB version Status Created Updated
-------------------- ---------- ----- ---------- ------------------ ------------------ --------------- ------------------------ ------------------------
dbs23 odb2 2 16.00 GB 21.8.0.0.221018 21.8.0.0.221018 CONFIGURED 2023-11-17 12:14:05 CET 2023-11-17 12:45:39 CET
dbs19 odb2 2 16.00 GB 19.20.0.0.230718 19.20.0.0.230718 CONFIGURED 2023-11-13 15:29:48 CET 2023-11-13 16:03:37 CET
odacli stop-dbsystem -n dbs19
sleep 30 ; odacli delete-dbsystem -n dbs19 --force
sleep 60 ; odacli list-dbsystems
Name Shape Cores Memory GI version DB version Status Created Updated
-------------------- ---------- ----- ---------- ------------------ ------------------ --------------- ------------------------ ------------------------
dbs23 odb2 2 16.00 GB 21.8.0.0.221018 21.8.0.0.221018 CONFIGURED 2023-11-17 12:14:05 CET 2023-11-17 12:45:39 CET
The force option will not prompt for any confirmation and will also remove the datafiles belonging to this DB System.
Conclusion
With DB Systems and Multitenant on ODA, it’s rather easy migrating to a newer version. Just unplug the PDB you want to migrate from the old container, plug it in into the new one, and apply the datapatch. You shouldn’t encounter any trouble with timezone if the patch level of your 23c is higher than the one you’re using on your 19c.