Today my experiment will be upgrading 12.2 (Oct 2021 RU & OJVM), running in the non-CDB mode, to the latest 19c version (currently it is 19.13) in multitenant architecture. The idea of this scenario is to validate and test not only the upgrade process but especially the downgrade strategy.
I will keep the COMPATIBLE parameter of my 19c target as 12.2.0 so I can try my fallback strategy without any issues.
The first important thing to note here is that the non-cdb to pdb conversion is not reversible. So that's why I plan to use transportable tablespaces as my fallback plan.
However, before anything, I've already blogged about transportable tablespace not working to previous releases. So my plan will be breaking the fallback to 12.2 in 2 stages:
My playground will be:
Source DB (12.2):
-
- Running on server odbfcl-12-2-0-1
- 12.2 with:
- 33192662;OJVM RELEASE UPDATE 12.2.0.1.211019 (33192662)
- 33261817;Database Oct 2021 Release Update : 12.2.0.1.211019 (33261817)
- Script used to create this database:
dbca -silent -createDatabase \ -gdbName orcl \ -templateName General_Purpose.dbc \ -characterSet AL32UTF8 \ -createAsContainerDatabase false \ -databaseConfigType SINGLE \ -databaseType MULTIPURPOSE \ -datafileDestination /u01/app/oracle/oradata \ -dvConfiguration false \ -emConfiguration NONE \ -enableArchive false \ -memoryMgmtType AUTO_SGA \ -totalMemory 4000 \ -nationalCharacterSet AL16UTF16 \ -olsConfiguration false \ -recoveryAreaDestination /u01/app/oracle/fra \ -sampleSchema false \ -sid orcl \ -storageType FS \ -useOMF false \ -sysPassword "Rodrigo.123" \ -systemPassword "Rodrigo.123"
Target DB (19.13.0):
-
- Running on server odbfcl-19-0-0-0
- 19c with:
- 33192694;OJVM RELEASE UPDATE: 19.13.0.0.211019 (33192694)
- 33192793;Database Release Update : 19.13.0.0.211019 (33192793)
- 29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
- Script used to create this database:
dbca -silent -createDatabase \ -gdbName cdb19c \ -templateName My_Template.dbt \ -characterSet AL32UTF8 \ -createAsContainerDatabase true \ -numberOfPDBs 1 \ -pdbAdminPassword "Rodrigo.123" \ -pdbName PDB01 \ -useLocalUndoForPDBs true \ -databaseConfigType SINGLE \ -databaseType MULTIPURPOSE \ -datafileDestination /u01/app/oracle/oradata \ -dvConfiguration false \ -emConfiguration NONE \ -enableArchive false \ -memoryMgmtType AUTO_SGA \ -totalMemory 4000 \ -nationalCharacterSet AL16UTF16 \ -olsConfiguration false \ -recoveryAreaDestination /u01/app/oracle/fra \ -sampleSchema false \ -sid cdb19c \ -storageType FS \ -useOMF false \ -sysPassword "Rodrigo.123" \ -systemPassword "Rodrigo.123" \ -initParams compatible=12.2.0
Note that my target DB, I haven't used the General_Purpose template as I want this database to have a lower COMPATIBLE (12.2.0). For more details on how to create a 19c with lower compatibility, check this article.
Fallback DB (12.2):
I will need a transient fallback database for this exercise. It will be exactly the same as the Source DB but in CDB mode.
-
- Running on server odbfcl-12-2-0-1
- 12.2 with:
- 33192662;OJVM RELEASE UPDATE 12.2.0.1.211019 (33192662)
- 33261817;Database Oct 2021 Release Update : 12.2.0.1.211019 (33261817)
- Script used to create this database:
dbca -silent -createDatabase \ -gdbName cdb12c \ -templateName General_Purpose.dbc \ -characterSet AL32UTF8 \ -createAsContainerDatabase true \ -numberOfPDBs 1 \ -pdbAdminPassword "Rodrigo.123" \ -pdbName PDB01 \ -useLocalUndoForPDBs true \ -databaseConfigType SINGLE \ -databaseType MULTIPURPOSE \ -datafileDestination /u01/app/oracle/oradata \ -dvConfiguration false \ -emConfiguration NONE \ -enableArchive false \ -memoryMgmtType AUTO_SGA \ -totalMemory 4000 \ -nationalCharacterSet AL16UTF16 \ -olsConfiguration false \ -recoveryAreaDestination /u01/app/oracle/fra \ -sampleSchema false \ -sid cdb12c \ -storageType FS \ -useOMF false \ -sysPassword "Rodrigo.123" \ -systemPassword "Rodrigo.123"
Preparing my source environment
In my 12c non-cdb source system, I will create some user data, so I can check them later after the migration.
[oracle@odbfcl-12-2-0-1 oradata]$ export ORACLE_SID=orcl
[oracle@odbfcl-12-2-0-1 oradata]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Oct 28 15:49:29 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> CREATE USER TEST IDENTIFIED BY "oracle";
User created.
SQL> GRANT DBA TO TEST;
Grant succeeded.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
SQL> CREATE TABLESPACE TOOLS DATAFILE '/u01/app/oracle/oradata/orcl/tools01.dbf' size 10M;
Tablespace created.
SQL> CREATE TABLE TEST.T1 TABLESPACE TOOLS AS SELECT * FROM DBA_TABLES;
Table created.
SQL> select count(*) from TEST.T1;
COUNT(*)
----------
2108
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Upgrading first
The first step is to perform the upgrade of the 12c non-cdb to 19c cdb. I will use AutoUpgrade as it automates most of the tasks for me.
My config file has:
global.autoupg_log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade upg1.log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade/12c upg1.sid=orcl upg1.source_home=/u01/app/oracle/product/12.2.0.1/dbhome_1 upg1.target_cdb=cdb19c upg1.target_home=/u01/app/oracle/product/19.0.0.0/dbhome_1 upg1.target_version=19 upg1.target_pdb_name=pdb12c upg1.upgrade_node=localhost upg1.run_utlrp=yes upg1.timezone_upg=no
As I'm also moving to a new server, I will break the AutoUpgrade in the 3 steps approach. Please note you don't need to break it into 3 phases if you have the new Oracle Home version already deployed on that same server.
So first, let me start with the analyze phase:
[oracle@odbfcl-12-2-0-1 ~]$ $ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -config ./upgrade.cfg -mode analyze AutoUpgrade 21.2.210721 launched with default options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 databases will be analyzed Type 'help' to list console commands upg> Job 101 completed ------------------- Final Summary -------------------- Number of databases [ 1 ] Jobs finished [1] Jobs failed [0] Jobs pending [0] Please check the summary report at: /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
Next, the fixups:
[oracle@odbfcl-12-2-0-1 ~]$ $ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -config ./upgrade.cfg -mode fixups AutoUpgrade 21.2.210721 launched with default options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 databases will be processed Type 'help' to list console commands upg> Job 102 completed ------------------- Final Summary -------------------- Number of databases [ 1 ] Jobs finished [1] Jobs failed [0] Jobs pending [0] Please check the summary report at: /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log
The last step is to stop the database and move all the database and configuration files (including datafiles, controlfiles, spfile, pwfile) and also the files generated by AutoUpgrade tool (upgrade.cfg and /u01/app/oracle/cfgtoollogs/autoupgrade/ folder) to the new server. Note again you won't need this if you are upgrading to the same server.
[oracle@odbfcl-12-2-0-1 ~]$ export ORACLE_SID=orcl [oracle@odbfcl-12-2-0-1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu Oct 28 16:21:49 2021 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
After everything is placed, I need to start up the database in 19c Oracle Home in upgrade mode:
[oracle@odbfcl-19-0-0-0 ~]$ export ORACLE_SID=orcl [oracle@odbfcl-19-0-0-0 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 28 17:02:46 2021 Version 19.13.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to an idle instance. SQL> startup upgrade; ORACLE instance started. Total System Global Area 3154113392 bytes Fixed Size 8901488 bytes Variable Size 687865856 bytes Database Buffers 2449473536 bytes Redo Buffers 7872512 bytes Database mounted. Database opened. SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.13.0.0.0
And finally, I will call the upgrade phase of AutoUpgrade on the target machine:
[oracle@odbfcl-19-0-0-0 ~]$ $ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -config ./upgrade.cfg -mode upgrade AutoUpgrade 21.2.210721 launched with default options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 1 databases will be processed Type 'help' to list console commands upg> lsj +----+-------+---------+---------+-------+--------------+--------+------------+ |Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE| +----+-------+---------+---------+-------+--------------+--------+------------+ | 103| orcl|DBUPGRADE|EXECUTING|RUNNING|21/10/28 17:04|17:07:13|10%Upgraded | +----+-------+---------+---------+-------+--------------+--------+------------+ Total jobs 1 upg> Job 103 completed ------------------- Final Summary -------------------- Number of databases [ 1 ] Jobs finished [1] Jobs failed [0] Jobs pending [0] Please check the summary report at: /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log [oracle@odbfcl-19-0-0-0 ~]$
Now just checking if my database was really upgraded:
[oracle@odbfcl-19-0-0-0 ~]$ export ORACLE_SID=cdb19c
[oracle@odbfcl-19-0-0-0 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 28 18:02:44 2021
Version 19.13.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
4 PDB12C READ WRITE NO
SQL> alter session set container=PDB12C;
Session altered.
SQL> select count(*) from TEST.T1;
COUNT(*)
----------
2108
SQL> select cname, prv_version, version from registry$;
CNAME PRV_VERSION VERSION
----------------------------------- ------------------------------ ------------------------------
Oracle Database Catalog Views 12.2.0.1.0 19.0.0.0.0
Oracle Database Packages and Types 12.2.0.1.0 19.0.0.0.0
Oracle XML Database 12.2.0.1.0 19.0.0.0.0
Oracle Workspace Manager 12.2.0.1.0 19.0.0.0.0
JServer JAVA Virtual Machine 12.2.0.1.0 19.0.0.0.0
Oracle XDK 12.2.0.1.0 19.0.0.0.0
Oracle Database Java Packages 12.2.0.1.0 19.0.0.0.0
Oracle Text 12.2.0.1.0 19.0.0.0.0
Oracle Multimedia 12.2.0.1.0 19.0.0.0.0
OLAP Analytic Workspace 12.2.0.1.0 19.0.0.0.0
Oracle OLAP API 12.2.0.1.0 19.0.0.0.0
Spatial 12.2.0.1.0 19.0.0.0.0
Oracle Label Security 12.2.0.1.0 19.0.0.0.0
Oracle Database Vault 12.2.0.1.0 19.0.0.0.0
Oracle Real Application Clusters 12.2.0.1.0 19.0.0.0.0
15 rows selected.
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.2.0
noncdb_compatible boolean FALSE
Downgrade
Now that I'm on 19c, let's imagine a fallback scenario. After running my production in this environment for some time, I want to move back to the original 12c database. I can't simply restore a backup as I already had some production workload executed in this new database. So a downgrade is the only option I have.
First, I will add a new table to my tablespace:
SQL> alter session set container=PDB12C;
Session altered.
SQL> create table test.t2 as select * from dba_tables;
Table created.
SQL> select count(*) from test.t2;
COUNT(*)
----------
2191
SQL>
Let's start the downgrading process. I followed the official documentation but there is also a MOS Note for it.
The overall steps are:
- Taking a backup of my PDB before starting.
- Cleaning out the UNIFIED_AUDIT_TRAIL on the PDB.
- Starting the PDB in downgrade mode.
- Calling PDB downgrade
- $ORACLE_HOME/bin/dbdowngrade -c 'PDB12C'
- $ORACLE_HOME/bin/dbdowngrade -c 'PDB12C'
- Shut down and unplug the PDB from the 19c.
- Plug back the PDB on the 12c cdb fallback database.
- Run catrelod.sql
First, I need to clean (or backup) the UNIFIED_AUDIT_TRAIL in that container:
[oracle@odbfcl-19-0-0-0 ~]$ export ORACLE_SID=cdb19c
[oracle@odbfcl-19-0-0-0 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 28 18:36:47 2021
Version 19.13.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
SQL> alter session set container=PDB12C;
Session altered.
SQL> select count(*) from UNIFIED_AUDIT_TRAIL;
COUNT(*)
----------
26848
SQL> EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, use_last_arch_timestamp => FALSE);
PL/SQL procedure successfully completed.
SQL>
The next step is to start up the pluggable database in downgrade mode:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
4 PDB12C READ WRITE YES
SQL> alter pluggable database PDB12C close;
Pluggable database altered.
SQL> alter pluggable database PDB12C open downgrade;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
4 PDB12C MIGRATE YES
Finally, calling the $ORACLE_HOME/bin/dbdowngrade wrapper:
[oracle@odbfcl-19-0-0-0 ~]$ sh $ORACLE_HOME/bin/dbdowngrade -c 'PDB12C' Downgrading containers catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/downgrade/catdwgrd_catcon_27280.lst] catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/downgrade/catdwgrd*.log] files for output generated by scripts catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/downgrade/catdwgrd_*.lst] files for spool files, if any catcon.pl: completed successfully [oracle@odbfcl-19-0-0-0 ~]$ grep '^ORA-' /u01/app/oracle/product/19.0.0.0/dbhome_1/cfgtoollogs/downgrade/catdwgrd*.log [oracle@odbfcl-19-0-0-0 ~]$
As you can see above. No errors were returned.
Now, the next step is to unplug this PDB from 19c CDB and plug it back on a 12c CDB. As I've shown before, I've created a "fallback database" to hold it, as my original 12c database was in non-cdb architecture.
Let's unplug it:
[oracle@odbfcl-19-0-0-0 ~]$ export ORACLE_SID=cdb19c
[oracle@odbfcl-19-0-0-0 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 28 19:17:28 2021
Version 19.13.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
4 PDB12C MIGRATE YES
SQL> alter pluggable database PDB12C close immediate;
Pluggable database altered.
SQL> alter pluggable database PDB12C unplug into '/home/oracle/PDB12C.unplug.xml';
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
4 PDB12C MOUNTED
SQL> drop pluggable database PDB12C;
Pluggable database dropped.
SQL>
Now I move the XML file and all the datafiles to the source system. Remember this is not required if they reside on the same server. Finally, I will plug it back to the 12c CDB:
[oracle@odbfcl-12-2-0-1 ~]$ export ORACLE_SID=cdb12c
[oracle@odbfcl-12-2-0-1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Oct 28 19:28:50 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
SQL> create pluggable database PDB12C using '/home/oracle/PDB12C.unplug.xml' nocopy;
Pluggable database created.
SQL> alter pluggable database PDB12C open upgrade;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
4 PDB12C MIGRATE YES
And the last step is to run catrelod.sql, to recreate the database objects for the 12c version:
SQL> alter session set container=PDB12C; Session altered. SQL> set termout off SQL> spool /home/oracle/catrelod.log SQL> @?/rdbms/admin/catrelod.sql SQL> spool off SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@odbfcl-12-2-0-1 ~]$ grep '^ORA-' /home/oracle/catrelod.log [oracle@odbfcl-12-2-0-1 ~]$
Now I'm with my CDB is back in 12c:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB12C MIGRATE YES
SQL> shut immediate;
Pluggable Database closed.
SQL> startup;
Pluggable Database opened.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB12C READ WRITE NO
SQL> select count(*) from TEST.T1;
COUNT(*)
----------
2108
SQL> select count(*) from TEST.T2;
COUNT(*)
----------
2191
SQL>
Full Transportable Tablespace
Perfect. At this point, we are back to 12c, however not exactly as before as now we are in multitenant architecture. The next and final step, if I really want to fall back to the same scenario I was before, would be using TTS to move all my data back to the original system. Let's try it:
[oracle@odbfcl-12-2-0-1 ~]$ export ORACLE_SID=cdb12c [oracle@odbfcl-12-2-0-1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu Oct 28 20:17:22 2021 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> alter session set container=PDB12C; Session altered. SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS TOOLS 6 rows selected. SQL> alter tablespace TOOLS read only; Tablespace altered. SQL> alter tablespace USERS read only; Tablespace altered. SQL>
I will also change the default tablespace of my dba "TEST" user from USER to SYSTEM as I will run expdp with it, and datapump needs to create some temporary tables:
SQL> alter session set container=PDB12C; Session altered. SQL> alter user test default tablespace system; User altered.
Now calling expdp for transportable:
[oracle@odbfcl-12-2-0-1 ~]$ expdp test/oracle@localhost:1521/pdb12c full=y transportable=always Export: Release 12.2.0.1.0 - Production on Fri Oct 29 11:21:31 2021 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "TEST"."SYS_EXPORT_FULL_01": test/********@localhost:1521/pdb12c full=y transportable=always Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS Processing object type DATABASE_EXPORT/STATISTICS/MARKER Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK Processing object type DATABASE_EXPORT/END_PLUGTS_BLK Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/TABLESPACE Processing object type DATABASE_EXPORT/PROFILE Processing object type DATABASE_EXPORT/SYS_USER/USER Processing object type DATABASE_EXPORT/SCHEMA/USER Processing object type DATABASE_EXPORT/RADM_FPTM Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA Processing object type DATABASE_EXPORT/RESOURCE_COST Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT Processing object type DATABASE_EXPORT/XS_SECURITY/SCHEMA/XS_ACL Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER . . exported "SYS"."KU$_USER_MAPPING_VIEW" 6.078 KB 37 rows . . exported "SYSTEM"."REDO_DB" 25.59 KB 1 rows . . exported "ORDDATA"."ORDDCM_DOCS" 252.9 KB 9 rows . . exported "WMSYS"."WM$WORKSPACES_TABLE$" 12.10 KB 1 rows . . exported "WMSYS"."WM$HINT_TABLE$" 9.984 KB 97 rows . . exported "LBACSYS"."OLS$INSTALLATIONS" 6.960 KB 2 rows . . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$" 7.078 KB 11 rows . . exported "SYS"."DAM_CONFIG_PARAM$" 6.531 KB 14 rows . . exported "SYS"."TSDP_SUBPOL$" 6.328 KB 1 rows . . exported "WMSYS"."WM$NEXTVER_TABLE$" 6.375 KB 1 rows . . exported "LBACSYS"."OLS$PROPS" 6.234 KB 5 rows . . exported "WMSYS"."WM$ENV_VARS$" 5.976 KB 2 rows . . exported "SYS"."TSDP_PARAMETER$" 5.953 KB 1 rows . . exported "SYS"."TSDP_POLICY$" 5.921 KB 1 rows . . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$" 5.984 KB 1 rows . . exported "WMSYS"."WM$EVENTS_INFO$" 5.812 KB 12 rows . . exported "LBACSYS"."OLS$AUDIT_ACTIONS" 5.757 KB 8 rows . . exported "LBACSYS"."OLS$DIP_EVENTS" 5.539 KB 2 rows . . exported "LBACSYS"."OLS$AUDIT" 0 KB 0 rows . . exported "LBACSYS"."OLS$COMPARTMENTS" 0 KB 0 rows . . exported "LBACSYS"."OLS$DIP_DEBUG" 0 KB 0 rows . . exported "LBACSYS"."OLS$GROUPS" 0 KB 0 rows . . exported "LBACSYS"."OLS$LAB" 0 KB 0 rows . . exported "LBACSYS"."OLS$LEVELS" 0 KB 0 rows . . exported "LBACSYS"."OLS$POL" 0 KB 0 rows . . exported "LBACSYS"."OLS$POLICY_ADMIN" 0 KB 0 rows . . exported "LBACSYS"."OLS$POLS" 0 KB 0 rows . . exported "LBACSYS"."OLS$POLT" 0 KB 0 rows . . exported "LBACSYS"."OLS$PROFILE" 0 KB 0 rows . . exported "LBACSYS"."OLS$PROFILES" 0 KB 0 rows . . exported "LBACSYS"."OLS$PROG" 0 KB 0 rows . . exported "LBACSYS"."OLS$SESSINFO" 0 KB 0 rows . . exported "LBACSYS"."OLS$USER" 0 KB 0 rows . . exported "LBACSYS"."OLS$USER_COMPARTMENTS" 0 KB 0 rows . . exported "LBACSYS"."OLS$USER_GROUPS" 0 KB 0 rows . . exported "LBACSYS"."OLS$USER_LEVELS" 0 KB 0 rows . . exported "SYS"."AUD$" 23.75 KB 2 rows . . exported "SYS"."DAM_CLEANUP_EVENTS$" 0 KB 0 rows . . exported "SYS"."DAM_CLEANUP_JOBS$" 0 KB 0 rows . . exported "SYS"."TSDP_ASSOCIATION$" 0 KB 0 rows . . exported "SYS"."TSDP_CONDITION$" 0 KB 0 rows . . exported "SYS"."TSDP_FEATURE_POLICY$" 0 KB 0 rows . . exported "SYS"."TSDP_PROTECTION$" 0 KB 0 rows . . exported "SYS"."TSDP_SENSITIVE_DATA$" 0 KB 0 rows . . exported "SYS"."TSDP_SENSITIVE_TYPE$" 0 KB 0 rows . . exported "SYS"."TSDP_SOURCE$" 0 KB 0 rows . . exported "SYSTEM"."REDO_LOG" 0 KB 0 rows . . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$" 0 KB 0 rows . . exported "WMSYS"."WM$CONSTRAINTS_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$CONS_COLUMNS$" 0 KB 0 rows . . exported "WMSYS"."WM$LOCKROWS_INFO$" 0 KB 0 rows . . exported "WMSYS"."WM$MODIFIED_TABLES$" 0 KB 0 rows . . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$RIC_LOCKING_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$RIC_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$" 0 KB 0 rows . . exported "WMSYS"."WM$UDTRIG_INFO$" 0 KB 0 rows . . exported "WMSYS"."WM$VERSION_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$VT_ERRORS_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$" 0 KB 0 rows . . exported "MDSYS"."RDF_PARAM$" 6.515 KB 3 rows . . exported "SYS"."AUDTAB$TBS$FOR_EXPORT" 5.960 KB 2 rows . . exported "SYS"."DBA_SENSITIVE_DATA" 0 KB 0 rows . . exported "SYS"."DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows . . exported "SYS"."FGA_LOG$FOR_EXPORT" 0 KB 0 rows . . exported "SYS"."NACL$_ACE_EXP" 0 KB 0 rows . . exported "SYS"."NACL$_HOST_EXP" 6.976 KB 2 rows . . exported "SYS"."NACL$_WALLET_EXP" 0 KB 0 rows . . exported "SYS"."SQL$TEXT_DATAPUMP" 0 KB 0 rows . . exported "SYS"."SQL$_DATAPUMP" 0 KB 0 rows . . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP" 0 KB 0 rows . . exported "SYS"."SQLOBJ$DATA_DATAPUMP" 0 KB 0 rows . . exported "SYS"."SQLOBJ$PLAN_DATAPUMP" 0 KB 0 rows . . exported "SYS"."SQLOBJ$_DATAPUMP" 0 KB 0 rows . . exported "SYSTEM"."SCHEDULER_JOB_ARGS" 0 KB 0 rows . . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 9.515 KB 12 rows . . exported "WMSYS"."WM$EXP_MAP" 7.718 KB 3 rows . . exported "WMSYS"."WM$METADATA_MAP" 0 KB 0 rows Master table "TEST"."SYS_EXPORT_FULL_01" successfully loaded/unloaded ****************************************************************************** Dump file set for TEST.SYS_EXPORT_FULL_01 is: /u01/app/oracle/admin/cdb12c/dpdump/CF6972DD148B3675E0531201000A270A/expdat.dmp ****************************************************************************** Datafiles required for transportable tablespace TOOLS: /u01/app/oracle/oradata/CDB12C/orcl/tools01.dbf Datafiles required for transportable tablespace USERS: /u01/app/oracle/oradata/CDB12C/orcl/users01.dbf Job "TEST"."SYS_EXPORT_FULL_01" successfully completed at Fri Oct 29 11:23:19 2021 elapsed 0 00:01:46 [oracle@odbfcl-12-2-0-1 ~]$
Now, finally, I will import the data back to a non-cdb 12c. I don't recommend you to use the original one as the target database here. Instead, create a new empty in non-cdb mode. That way, you won't have any conflict with existing objects and you can even compare later with the original for any difference.
As the USERS tablespace already exists on a out-of-box database, I will just rename it to USERS_OLD to avoid conflict on the FTTS import.
[oracle@odbfcl-12-2-0-1 ~]$ export ORACLE_SID=orcl [oracle@odbfcl-12-2-0-1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 29 11:42:53 2021 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> alter tablespace USERS rename to USERS_OLD; Tablespace altered. SQL> alter database move datafile '/u01/app/oracle/oradata/orcl/users01.dbf' to '/u01/app/oracle/oradata/orcl/users01_old.dbf'; Database altered.
I will position the datafiles and the dump file created by the expdp ftts process in the location I want.
Calling now the impdp:
[oracle@odbfcl-12-2-0-1 ~]$ export ORACLE_SID=orcl
[oracle@odbfcl-12-2-0-1 ~]$ cp -av /u01/app/oracle/admin/cdb12c/dpdump/CF6972DD148B3675E0531201000A270A/expdat.dmp /u01/app/oracle/admin/orcl/dpdump/expdat.dmp
'/u01/app/oracle/admin/cdb12c/dpdump/CF6972DD148B3675E0531201000A270A/expdat.dmp' -> '/u01/app/oracle/admin/orcl/dpdump/expdat.dmp'
[oracle@odbfcl-12-2-0-1 ~]$ impdp \"/ as sysdba\" transport_datafiles=/u01/app/oracle/oradata/orcl/tools01.dbf,/u01/app/oracle/oradata/orcl/users01.dbf
Import: Release 12.2.0.1.0 - Production on Fri Oct 29 16:26:22 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" transport_datafiles=/u01/app/oracle/oradata/orcl/tools01.dbf,/u01/app/oracle/oradata/orcl/users01.dbf
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
ORA-31685: Object type USER:"SYS" failed due to insufficient privileges. Failing sql is:
ALTER USER "SYS" IDENTIFIED BY VALUES 'S: ;T: ' TEMPORARY TABLESPACE "TEMP"
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
>>> Cannot set an SCN larger than the current SCN. If a Streams Capture configuration was imported then the Apply that processes the captured messages needs to be dropped and recreated. See My Oracle Support article number 1380295.1.
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
ORA-39083: Object type PROCOBJ:"SYS"."XMLDB_NFS_JOBCLASS" failed to create with error:
ORA-27477: "SYS"."XMLDB_NFS_JOBCLASS" already exists
Failing sql is:
BEGIN
dbms_scheduler.create_job_class('"XMLDB_NFS_JOBCLASS"',NULL,NULL,128,NULL,
NULL
);COMMIT; END;
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SYS"."KU$_EXPORT_USER_MAP" 6.054 KB 36 rows
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
. . imported "SYSTEM"."REDO_DB_TMP" 25.59 KB 1 rows
. . imported "ORDDATA"."ORDDCM_DOCS_TRANSIENT" 252.9 KB 9 rows
. . imported "WMSYS"."E$WORKSPACES_TABLE$" 12.10 KB 1 rows
. . imported "WMSYS"."E$HINT_TABLE$" 9.984 KB 97 rows
. . imported "WMSYS"."E$WORKSPACE_PRIV_TABLE$" 7.078 KB 11 rows
. . imported "SYS"."AMGT$DP$DAM_CONFIG_PARAM$" 6.531 KB 14 rows
. . imported "SYS"."DP$TSDP_SUBPOL$" 6.328 KB 1 rows
. . imported "WMSYS"."E$NEXTVER_TABLE$" 6.375 KB 1 rows
. . imported "WMSYS"."E$ENV_VARS$" 5.976 KB 2 rows
. . imported "SYS"."DP$TSDP_PARAMETER$" 5.953 KB 1 rows
. . imported "SYS"."DP$TSDP_POLICY$" 5.921 KB 1 rows
. . imported "WMSYS"."E$VERSION_HIERARCHY_TABLE$" 5.984 KB 1 rows
. . imported "WMSYS"."E$EVENTS_INFO$" 5.812 KB 12 rows
. . imported "LBACSYS"."OLS_DP$OLS$AUDIT" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$COMPARTMENTS" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$GROUPS" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$LAB" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$LEVELS" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$POL" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$POLS" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$POLT" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$PROFILE" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$PROG" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$USER" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$AUD$" 25.03 KB 11 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_EVENTS$" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_JOBS$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_ASSOCIATION$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_CONDITION$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_FEATURE_POLICY$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_PROTECTION$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_SENSITIVE_DATA$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_SENSITIVE_TYPE$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_SOURCE$" 0 KB 0 rows
. . imported "SYSTEM"."REDO_LOG_TMP" 0 KB 0 rows
. . imported "WMSYS"."E$BATCH_COMPRESSIBLE_TABLES$" 0 KB 0 rows
. . imported "WMSYS"."E$CONSTRAINTS_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$CONS_COLUMNS$" 0 KB 0 rows
. . imported "WMSYS"."E$LOCKROWS_INFO$" 0 KB 0 rows
. . imported "WMSYS"."E$MODIFIED_TABLES$" 0 KB 0 rows
. . imported "WMSYS"."E$MP_GRAPH_WORKSPACES_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$MP_PARENT_WORKSPACES_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$NESTED_COLUMNS_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$RESOLVE_WORKSPACES_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$RIC_LOCKING_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$RIC_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$RIC_TRIGGERS_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$UDTRIG_DISPATCH_PROCS$" 0 KB 0 rows
. . imported "WMSYS"."E$UDTRIG_INFO$" 0 KB 0 rows
. . imported "WMSYS"."E$VERSION_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$VT_ERRORS_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$WORKSPACE_SAVEPOINTS_TABLE$" 0 KB 0 rows
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
. . imported "MDSYS"."RDF_PARAM$TBL" 6.515 KB 3 rows
. . imported "SYS"."AMGT$DP$AUDTAB$TBS$FOR_EXPORT" 5.960 KB 2 rows
. . imported "SYS"."DP$DBA_SENSITIVE_DATA" 0 KB 0 rows
. . imported "SYS"."DP$DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$FGA_LOG$FOR_EXPORT" 0 KB 0 rows
. . imported "SYS"."NACL$_ACE_IMP" 0 KB 0 rows
. . imported "SYS"."NACL$_HOST_IMP" 6.976 KB 2 rows
. . imported "SYS"."NACL$_WALLET_IMP" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQL$TEXT" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQL$" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$AUXDATA" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$DATA" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$PLAN" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$" 0 KB 0 rows
. . imported "SYSTEM"."SCHEDULER_JOB_ARGS_TMP" 0 KB 0 rows
. . imported "SYSTEM"."SCHEDULER_PROGRAM_ARGS_TMP" 9.515 KB 12 rows
. . imported "WMSYS"."E$EXP_MAP" 7.718 KB 3 rows
. . imported "WMSYS"."E$METADATA_MAP" 0 KB 0 rows
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/XS_SECURITY/SCHEMA/XS_ACL
ORA-39083: Object type XS_ACL:"SYS"."NETWORK_ACL_CF6973C1EF8236DEE0531201000A24BE" failed to create with error:
ORA-46212: XS entity with this name already exists.
Failing sql is:
DECLARE
ace_list XS$ACE_LIST;
BEGIN
ace_list := XS$ACE_LIST(
XS$ACE_TYPE(
privilege_list => XS$NAME_LIST('"RESOLVE"'),
principal_name=>'"ORACLE_OCM"',
principal_type=>XS_ACL.PTYPE_DB));
xs_acl.create_acl(
name=>'"SYS"."NETWORK_ACL_CF6973C1EF8236DEE0531201000A24BE"',
ace_list=>ace_list,
sec_class=>'"SYS"."NETWORK_SC"',
description=>'OCM User Resolve Network Access using UTL_INADDR');
END;
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/END_PLUGTS_BLK
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" completed with 5 error(s) at Fri Oct 29 16:27:19 2021 elapsed 0 00:00:55
I got 5 errors, but in this case that can be safely ignored as they are related to objects that already exist.
Now checking my tables:
[oracle@odbfcl-12-2-0-1 ~]$ export ORACLE_SID=orcl
[oracle@odbfcl-12-2-0-1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 29 16:31:43 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select count(*) from test.t1;
COUNT(*)
----------
2108
SQL> select count(*) from test.t2;
COUNT(*)
----------
2191
SQL>
And that's it! As you can see, there is no mystery about moving forward and back from 19c PDB to 12.2 NON-CDB.
Don't forget to always try the full upgrade/downgrade process in a "prod like" environment before.
Have you enjoyed? Please leave a comment or give a 👍!
2 comments
Thanks Rodrigo for sharing such valuable info.
Ahmed
Thanks for sharing. Excellent material.