This was first published on https://blog.dbi-services.com/12cr2-upgrade-by-remote-clone-with-tde-in-dbaas (2016-11-23)
Republishing here for new followers. The content is related to the the versions available at the publication date
Upgrading from 12.1 to 12.2 is easy in Oracle Public Cloud DBaaS because you are in multitenant. Here is how to clone a 12.1 PDB to 12.2 service. I’ve a service HP121 in 12.1.0.2 with one pluggable database PDB1 and a service HP122 in 12.2.0.1 with an empty CDB (only CDB$ROOT and PDB$SEED containers).
The Oracle Public Cloud uses Transparent Data Encryption to secure the datafiles. When you move the pluggable databases you need to export/import the encryption keys.
Here is the key:
18:42:58 HP121 SQL>select wrl_type,wrl_parameter,wallet_type from v$encryption_wallet; WRL_TYPE WRL_PARAMETER WALLET_TY -------- ---------------------------------------- --------- FILE /u01/app/oracle/admin/HP121/tde_wallet/ AUTOLOGIN 18:42:58 HP121 SQL>select key_id from v$encryption_keys where creator_pdbname='PDB1'; KEY_ID ------------------------------------------------------------------------------ AQqCc8XWV09uvxkaw0Bm5XUAAAAAAAAAAAAAAAAAAAAAAAAAAAAAThe instance uses an auto-login wallet and you cannot export the keys from that:
18:42:58 HP121 SQL>administer key management export encryption keys with secret "oracle" to '/tmp/cdb2pdb1.p12' identified by "Ach1z0#d" with identifier in (select key_id from v$encryption_keys where creator_pdbname='PDB1'); administer key management export encryption keys with secret "oracle" to '/tmp/cdb2pdb1.p12' identified by "Ach1z0#d" with identifier in (select key_id from v$encryption_keys where creator_pdbname='PDB1') * ERROR at line 1: ORA-28417: password-based keystore is not open
You need to open it with the password:
18:42:58 HP121 SQL>administer key management set keystore close; keystore altered. 18:42:58 HP121 SQL>administer key management set keystore open identified by "Ach1z0#d"; keystore altered. 18:42:58 HP121 SQL>select wrl_type,wrl_parameter,wallet_type from v$encryption_wallet; WRL_TYPE WRL_PARAMETER WALLET_TY -------- ---------------------------------------- --------- FILE /u01/app/oracle/admin/HP121/tde_wallet/ PASSWORDAnd then you can export it:
18:42:58 HP121 SQL>administer key management export encryption keys with secret "oracle" to '/tmp/cdb2pdb1.p12' identified by "Ach1z0#d" with identifier in (select key_id from v$encryption_keys where creator_pdbname='PDB1'); keystore altered.
I copy the file /tmp/cdb2pdb1.p12 to the destination (scp) and then I can import it, giving the same ‘secret’ identifier. Here again i have to open the wallet with password because it cannot be imported when opened
18:43:04 HP122 SQL>administer key management set keystore close; keystore altered. 18:43:04 HP122 SQL>administer key management set keystore open identified by "Ach1z0#d"; keystore altered. 18:43:04 HP122 SQL>administer key management import encryption keys with secret "oracle" from '/tmp/cdb2pdb1.p12' identified by "Ach1z0#d"; keystore altered.
We need to create a database link to the source (don’t forget to open the port for the listener):
18:43:04 HP122 SQL>select dbms_tns.resolve_tnsname('//HP121/HP121.demnov.oraclecloud.internal') from dual; DBMS_TNS.RESOLVE_TNSNAME('//HP121/HP121.DEMNOV.ORACLECLOUD.INTERNAL') -------------------------------------------------------------------------------- (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=HP121.demnov.oraclecloud.internal)(CID= (PROGRAM=oracle)(HOST=HP122.compute-demnov.oraclecloud.internal)(USER=oracle)))( ADDRESS=(PROTOCOL=TCP)(HOST=10.196.202.47)(PORT=1521))) 18:43:04 HP122 SQL>create database link HP121@HP121 connect to system identified by "Ach1z0#d" using '//HP121/HP121.demnov.oraclecloud.internal'; Database link created. 18:43:04 HP122 SQL>select host_name from v$instance@HP121@HP121; HOST_NAME ---------------------------------------------------------------- HP121.compute-demnov.oraclecloud.internal
You need to have the source PDB1 opened read-only, and the cloning is only one command:
18:43:09 HP122 SQL>create pluggable database PDB1 from PDB1@HP121@HP121 keystore identified by "Ach1z0#d"; Pluggable database created.
Now that you have the PDB you can open it (because you have imported the TDE key) but the dictionary is still in 12.1 so you have to run:
[oracle@HP122 ~]$ dbupgrade -c PDB1This is described in previous post: http://blog.dbi-services.com/12cr2-how-long-to-upgrade-a-pdb/
Hi, I read your blogs and I like it very much!
I try to clone remotly pdb from 12.1 to 12.2 and want to upgrade it after that but I get:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> set timing on SQL> create pluggable database PDB_SQA1 from OLD_SQA_JUL13@nyioldsqa; create pluggable database PDB_SQA1 from OLD_SQA_JUL13@nyioldsqa * ERROR at line 1: ORA-65346: The PDB version is lower and components (APEX) are missing in CDB
Hi, Thanks for your feedback. Having APEX in ROOT causes a lot of problems like this one. Mike Dietrich detailed that: https://mikedietrichde.com/2015/11/19/why-you-should-remove-apex-from-the-cdbroot/ Regards, Franck.
Hi Hillel,
Did you get any workaround for this “ORA-65346: The PDB version is lower and components (APEX) are missing in CDB” I too faced same problem .