Oracle Database Migration from non-cdb to pdb - components issue

posted on 12 Aug 2019 by in Blog Welcome, Oracle, Oracle 12c

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

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).

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!!!

Attachments:

noncdb_dblink_1_.jpg (30 KB)

2 comments

Comment from: polya Member

Great article! Thanks for the knowledge sharing! :)

Comment from: kkovachki Member

Thanks PolyaPro, where is your new post? :)

Form is loading...