domingo, 27 de enero de 2013

Error ORA-39127 cuando estoy haciendo un Export con Datapump

Esta entrada realmente la estoy creando para tener yo un acceso mas rapido, sin tener que pasar por MOS, en nuestro ambiente tuvimos una situacion en donde en nuestra base de datos se instalaron todos los componentes de Oracle, como OLAP y Oracle Text, como no era necesario tenerlos, se tomo la decision de removerlos, pero cuando estabamos corriendo un export , esta fallando con el siguiente error:

ORA-39127: unexpected error from call to "SYS"."DBMS_CUBE_EXP"."SCHEMA_INFO_EXP"
ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 316
ORA-06512: at "SYS.DBMS_METADATA", line 9409

Primero verificamos que ya no existen componentes de OLAP en la base de datos, en este caso, nada mas tenemos RAC y los catalogos necesarios

TESTDB1 >DBA_REGISTRY CONTENTS (VIEW DOES NOT EXISIT IN VERSIONS < 9.2.0)
TESTDB1 >================================================================
TESTDB1 >COMP_ID              COMP_NAME                                VERSION    STATUS
-------------------- ---------------------------------------- ---------- ---------------
CATALOG              Oracle Database Catalog Views            11.2.0.3.0 VALID
CATPROC              Oracle Database Packages and Types       11.2.0.3.0 VALID
RAC                  Oracle Real Application Clusters         11.2.0.3.0 VALID

Y con este query vemos que queda todavia existe informacion en el diccionario de datos refiriendose a OLAP

TESTDB1 >SELECT * FROM SYS.EXPPKGACT$ WHERE PACKAGE = 'DBMS_CUBE_EXP';

PACKAGE                        SCHEMA                              CLASS     LEVEL#
------------------------------ ------------------------------ ---------- ----------
DBMS_CUBE_EXP                  SYS                                     2       1050
DBMS_CUBE_EXP                  SYS                                     4       1050
DBMS_CUBE_EXP                  SYS                                     6       1050

La manera de resolverlo es crear primero un respaldo de la tabla SYS.EXPPKGACT$, por si algo llegara a salir mal, esto lo haces como el usuario SYS

oracle $ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 27 02:46:38 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

TESTDB1 >CREATE TABLE SYS.EXPPKGACT$_BACKUP AS SELECT * FROM SYS.EXPPKGACT$;

Table created.

Una vez que hayas creado el respaldo de la tabla, vas a borrar los datos, con el siguiente delete

TESTDB1 >DELETE FROM SYS.EXPPKGACT$ WHERE PACKAGE = 'DBMS_CUBE_EXP' AND SCHEMA= 'SYS';

3 rows deleted.

TESTDB1 >commit;

Commit complete.

Y ahora si vas a poder realizar tu respaldo con exito, esto lo puedes encontrar en el documento de MOS 1328829.1


Starting "SYSTEM"."DATAPMP__USERS":  system/******** dumpfile=node_TESTDB_users_%U.dmp logfile=node_TESTDB_users.log parfile=/mount/dba01/oracle/recover/export_dp_users.par
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/EVENT/TRIGGER
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
ORA-39165: Schema TEST01 was not found.
Master table "SYSTEM"."DATAPMP_USERS" successfully loaded/unloaded