Oracle Database Migration from non-cdb to pdb - components issue
We decide to create a new CDB container instance with version 12.2.0.1 in order to plug into non-cdb database which is converted to pdb.
The task is very simple, we have to create a new pluggable database with NOCOPY option due to the fact that all data files are in place and we don't need to use COPY option because all datafiles are on the correct location.
Create new PDB with NOCOPY option
sqlplus /nolog
conn / as sysdba
create pluggable database TESTPDB using '/opt/oracle/oradata/TESTPDB_template.xml' NOCOPY TEMPFILE REUSE;
|
Check the status of newly created PDB
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 TESTPDB READ WRITE NO SQL> |
We consider to test Export process with expdp by using following options
Parameter files -> TESTPDB.par
userid="sys/test123@//localhost:1521/TESTPDB as sysdba" full=YES exclude=SCHEMA:"not like 'TEST_%'" directory=backup flashback_time=systimestamp metrics=Y LOGTIME=ALL exclude=TABLE_DATA:"in ('UTLOP_TRACE','TEST_LOG_MESSAGES')" |
Start export process with expdp
expdp parfile=/opt/oracle/backup/bin/TESTPDB.par dumpfile=TESTPDB.dmp logfile=TESTPDB.log |
During the export process we face the below errors:
08-AUG-19 16:27:51.231: ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [ORA-00942: table or view does not exist
08-AUG-19 16:27:51.231: ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.KUPW$WORKER", line 12098 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.DBMS_METADATA", line 9039 ORA-06512: at "SYS.DBMS_METADATA", line 2792 ORA-06512: at "SYS.DBMS_METADATA", line 3423 ORA-06512: at "SYS.DBMS_METADATA", line 4760 ORA-06512: at "SYS.DBMS_METADATA", line 5079 ORA-06512: at "SYS.DBMS_METADATA", line 9020 ORA-06512: at "SYS.KUPW$WORKER", line 14367 08-AUG-19 16:27:51.231: ----- PL/SQL Call Stack -----
object line object handle number name 0x88aaf1d0 32239 package body SYS.KUPW$WORKER.WRITE_ERROR_INFORMATION 0x88aaf1d0 12119 package body SYS.KUPW$WORKER.DETERMINE_FATAL_ERROR 0x88aaf1d0 14693 package body SYS.KUPW$WORKER.FETCH_XML_OBJECTS 0x88aaf1d0 3689 package body SYS.KUPW$WORKER.UNLOAD_METADATA 0x88aaf1d0 13063 package body SYS.KUPW$WORKER.DISPATCH_WORK_ITEMS 0x88aaf1d0 2311 package body SYS.KUPW$WORKER.MAIN 0x887543e8 2 anonymous block 08-AUG-19 16:27:51.231: DBMS_METADATA.SET_FILTER 08-AUG-19 16:27:51.231: DBMS_METADATA.SET_FILTER 08-AUG-19 16:27:51.231: DBMS_METADATA.SET_FILTER 08-AUG-19 16:27:51.231: In FETCH_XML_OBJECTS 08-AUG-19 16:27:51.231: End seqno is: 24 08-AUG-19 16:27:51.232: KUPF$FILE.OPEN_CONTEXT 08-AUG-19 16:27:51.232: KUPF$FILE.OPEN_CONTEXT 08-AUG-19 16:27:51.232: DBMS_METADATA.FETCH_XML_CLOB 08-AUG-19 16:27:51.232: DBMS_METADATA.FETCH_XML_CLOB 08-AUG-19 16:27:51.232: In procedure DETERMINE_FATAL_ERROR with ORA-00942: table or view does not exist ORA-06512: at "SYS.DBMS_METADATA", line 9039 ORA-06512: at "SYS.DBMS_METADATA", line 2792 ORA-06512: at "SYS.DBMS_METADATA", line 3423 ORA-06512: at "SYS.DBMS_METADATA", line 4760 ORA-06512: at "SYS.DBMS_METADATA", line 5079 ORA-06512: at "SYS.DBMS_METADATA", line 9020 08-AUG-19 16:27:51.572: W-2 Startup took 223 seconds 08-AUG-19 16:27:52.495: ORA-39126: Worker unexpected fatal error in KUPW$WORKER.FETCH_XML_OBJECTS [ORA-00942: table or view does not exist 08-AUG-19 16:27:52.495: ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.KUPW$WORKER", line 12098 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.DBMS_METADATA", line 9039 ORA-06512: at "SYS.DBMS_METADATA", line 2792 ORA-06512: at "SYS.DBMS_METADATA", line 3423 ORA-06512: at "SYS.DBMS_METADATA", line 4760 ORA-06512: at "SYS.DBMS_METADATA", line 5079 ORA-06512: at "SYS.DBMS_METADATA", line 9020 ORA-06512: at "SYS.KUPW$WORKER", line 14367 08-AUG-19 16:27:52.495: ----- PL/SQL Call Stack ----- object line object handle number name 0x88aaf1d0 32239 package body SYS.KUPW$WORKER.WRITE_ERROR_INFORMATION 0x88aaf1d0 12119 package body SYS.KUPW$WORKER.DETERMINE_FATAL_ERROR 0x88aaf1d0 14693 package body SYS.KUPW$WORKER.FETCH_XML_OBJECTS 0x88aaf1d0 3689 package body SYS.KUPW$WORKER.UNLOAD_METADATA 0x88aaf1d0 13063 package body SYS.KUPW$WORKER.DISPATCH_WORK_ITEMS 0x88aaf1d0 2311 package body SYS.KUPW$WORKER.MAIN 0x887543e8 2 anonymous block 08-AUG-19 16:27:52.495: DBMS_METADATA.SET_FILTER 08-AUG-19 16:27:52.495: DBMS_METADATA.SET_FILTER 08-AUG-19 16:27:52.495: DBMS_METADATA.SET_FILTER 08-AUG-19 16:27:52.495: In FETCH_XML_OBJECTS 08-AUG-19 16:27:52.495: End seqno is: 24 08-AUG-19 16:27:52.495: KUPF$FILE.OPEN_CONTEXT 08-AUG-19 16:27:52.495: KUPF$FILE.OPEN_CONTEXT 08-AUG-19 16:27:52.495: DBMS_METADATA.FETCH_XML_CLOB 08-AUG-19 16:27:52.495: DBMS_METADATA.FETCH_XML_CLOB 08-AUG-19 16:27:52.495: In procedure DETERMINE_FATAL_ERROR with ORA-00942: table or view does not exist ORA-06512: at "SYS.DBMS_METADATA", line 9039 ORA-06512: at "SYS.DBMS_METADATA", line 2792 ORA-06512: at "SYS.DBMS_METADATA", line 3423 ORA-06512: at "SYS.DBMS_METADATA", line 4760 ORA-06512: at "SYS.DBMS_METADATA", line 5079 ORA-06512: at "SYS.DBMS_METADATA", line 9020 08-AUG-19 16:27:52.501: Job "SYS"."SYS_EXPORT_FULL_02" stopped due to fatal error at Thu Aug 8 16:27:52 2019 elapsed 0 00:03:45
|
The above errors is related to DV compunents -> Oracle Database Vault DV
Check compunent on CDB and PDB
CDB
COMP_NAME COMP_ID VERSION STATUS ------------------------------ -------------------- -------------------- ---------- Oracle Database Catalog Views CATALOG 12.2.0.1.0 VALID Oracle Database Packages and T CATPROC 12.2.0.1.0 VALID JServer JAVA Virtual Machine JAVAVM 12.2.0.1.0 VALID Oracle XDK XML 12.2.0.1.0 VALID Oracle Database Java Packages CATJAVA 12.2.0.1.0 VALID OLAP Analytic Workspace APS 12.2.0.1.0 OPTION OFF Oracle Real Application Cluste RAC 12.2.0.1.0 OPTION OFF Oracle XML Database XDB 12.2.0.1.0 VALID Oracle Workspace Manager OWM 12.2.0.1.0 VALID Oracle Text CONTEXT 12.2.0.1.0 VALID Oracle Multimedia ORDIM 12.2.0.1.0 VALID Spatial SDO 12.2.0.1.0 OPTION OFF Oracle OLAP API XOQ 12.2.0.1.0 OPTION OFF Oracle Label Security OLS 12.2.0.1.0 VALID Oracle Database Vault DV 12.2.0.1.0 VALID
PDB
COMP_NAME COMP_ID VERSION STATUS ------------------------------ -------------------- -------------------- ---------- Oracle Database Catalog Views CATALOG 12.2.0.1.0 VALID Oracle Database Packages and T CATPROC 12.2.0.1.0 VALID JServer JAVA Virtual Machine JAVAVM 12.2.0.1.0 VALID Oracle XDK XML 12.2.0.1.0 VALID Oracle Database Java Packages CATJAVA 12.2.0.1.0 VALID Oracle XML Database XDB 12.2.0.1.0 VALID Oracle Workspace Manager OWM 12.2.0.1.0 VALID
|
As you can see from the above, DV exist only on CDB
Based on our investigation and research we have to remove DV from CDB
How To uninstall / install Database Vault in a 12c database ? (Doc ID 2112167.1)
Please check metalink procedure which is valid only for NON-CDB database, if you like to make it working for CDB and PDB you have to modify sql file, prior execute, make sure that you have set RECYCLEBIN to off and db has been restart.
Edit $ORACLE_HOME/rdbms/admin/dvremov.sql
Warning - action is on your own risk!!!
---- original value
--Check that the database is not a multitenant container database (CDB).
DECLARE cdb_status VARCHAR2(10); ora_error EXCEPTION; PRAGMA EXCEPTION_INIT(ora_error, -47993); BEGIN select cdb into cdb_status from v$database; IF (UPPER(cdb_status) LIKE 'YES') THEN RAISE ora_error; END IF; END; / |
To make it working for CDB and PDB you can modify like this
--Check that the database is not a multitenant container database (CDB).
DECLARE cdb_status VARCHAR2(10); ora_error EXCEPTION; PRAGMA EXCEPTION_INIT(ora_error, -47993); BEGIN select cdb into cdb_status from v$database; IF (UPPER(cdb_status) LIKE 'NO') THEN ---- from YES to NO RAISE ora_error; END IF; END; /
|
You need to execute DV remove only from CDB
If you check for invalid objects after this process you should see 3 invalid objects
OWNER OBJECT_NAME OBJECT_TYPE ********* SYS CDB_DV_STATUS VIEW PUBLIC CONFIGURE_DV SYNONYM PUBLIC CDB_DV_STATUS SYNONYM |
Execute drop of 2 objects
drop view SYS.CDB_DV_STATUS; drop PUBLIC SYNONYM CDB_DV_STATUS; |
Execute the recompile process with
EXEC UTL_RECOMP.recomp_parallel(); |
Once you are ready with DV remove process you can go with next action from the list -
How To Reload Datapump Utility EXPDP/IMPDP (Doc ID 430221.1)
You should execute it only on CDB
sqlplus /nolog
conn / as sysdba
@?/rdbms/admin/dpload.sql
@?/rdbms/admin/utlrp.sql
|
Check for invalid objects
Re-execute export process with expdp
Export: Release 12.2.0.1.0 - Production on Fri Aug 9 13:43:11 2019
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. ;;; Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production 09-AUG-19 13:43:18.845: ;;; **************** 09-AUG-19 13:43:18.848: ;;; Parfile values: 09-AUG-19 13:43:18.851: ;;; parfile: logtime=ALL 09-AUG-19 13:43:18.854: ;;; parfile: metrics=Y 09-AUG-19 13:43:18.856: ;;; parfile: flashback_time=systimestamp 09-AUG-19 13:43:18.859: ;;; parfile: directory=backup 09-AUG-19 13:43:18.861: ;;; parfile: exclude=SCHEMA:"not like 'TEST_%'", 09-AUG-19 13:43:18.868: ;;; parfile: full=Y 09-AUG-19 13:43:18.871: ;;; parfile: userid=sys/**@localhost:1521/TESTPDB AS SYSDBA 09-AUG-19 13:43:18.875: ;;; **************** 09-AUG-19 13:43:25.082: Starting "SYS"."SYS_EXPORT_FULL_04": sys/**@localhost:1521/TESTPDB AS SYSDBA parfile=/opt/oracle/backup/bin/TESTPDB.par dumpfile=TESTPDB .dmp logfile=TESTPDB.log 09-AUG-19 13:43:25.719: W-1 Startup took 8 seconds 09-AUG-19 13:43:46.192: W-1 Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA 09-AUG-19 13:43:48.357: W-1 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA 09-AUG-19 13:43:48.676: W-1 Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA 09-AUG-19 13:43:52.125: W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA 09-AUG-19 13:44:03.927: W-1 Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY 09-AUG-19 13:45:18.593: W-1 Completed 795 PACKAGE_BODY objects in 83 seconds ----- CUT 09-AUG-19 13:58:55.160: W-1 Completed 1 DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA objects in 2 seconds 09-AUG-19 13:58:55.301: W-1 Completed 40 DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA objects in 4 seconds 09-AUG-19 13:58:55.452: W-1 Completed 17 DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA objects in 17 seconds 09-AUG-19 13:58:55.592: W-1 Completed 1951 DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA objects in 301 seconds 09-AUG-19 13:59:39.492: W-1 Master table "SYS"."SYS_EXPORT_FULL_04" successfully loaded/unloaded 09-AUG-19 13:59:39.614: ****************** 09-AUG-19 13:59:39.615: Dump file set for SYS.SYS_EXPORT_FULL_04 is: 09-AUG-19 13:59:39.618: /opt/oracle/backup/exports/TESTPDB/TESTPDB.dmp 09-AUG-19 13:59:39.685: Job "SYS"."SYS_EXPORT_FULL_04" successfully completed at Fri Aug 9 13:59:39 2019 elapsed 0 00:16:23
|
yessssss, all works as expected...
If you execute @?/rdbms/admin/dpload.sql by mistake on PDB, you need to execute the following grant on pdb in order to fix issues with permissions on some of the system objects
grant CREATE ANY TABLE to sys; grant ALTER ANY TABLE to sys; grant DROP ANY TABLE to sys; grant SELECT ANY TABLE to sys; grant LOCK ANY TABLE to sys; grant ALTER ANY INDEX to sys; grant ALTER ANY TRIGGER to sys; grant UPDATE ANY TABLE to sys; grant EXECUTE ANY TYPE to sys; |
For more information Metalink note ->> DataPump Import (IMPDP) Fails With Errors ORA-1031 UDI-31626 If DBA Role Is Missing (Doc ID 1459430.1)
Once you are ready with the grants, you can execute recompilation
EXEC UTL_RECOMP.recomp_parallel();
|
Check for invalid objects:
select owner, object_name,object_type from dba_objects where status !='VALID' ;
|
:) keep going!!!
Great article! Thanks for the knowledge sharing! :)