1

I've got a BASH script for exporting from our Oracle 10G database.

#!/bin/bash

#This script should be run with the Oracle account. It exports the necessary files from the database for creating a duplicate of the DB.

#user variables
SID=DATABASESIDHERE                           #SID of database to export
SYSTEMPASSWORD=ENTERSYSTEMPASSWORDHERE           #system user password.
OUTPUTDIR=/u01/expdmpoutput                   #path to directory that files will be put in. Make sure user has correct permissions


#Script variables. Do not change!
OUTPUTDIRNAME=EXPDMPOUTPUT
OUTPUTFILENAME=${SID}-expfull.dmp


echo "Switching to $SID."
export ORACLE_SID=$SID

echo "Ensuring output directory exists on filesystem."
mkdir -p $OUTPUTDIR

echo "Ensuring output directory exists in Oracle."
sqlplus / "as sysdba" <<END
create or replace directory $OUTPUTDIRNAME as '$OUTPUTDIR';
END

echo "Performing EXPDMP of $SID to $OUTPUTDIR"
expdp system/$SYSTEMPASSWORD FULL=Y STATUS=300 DIRECTORY=$OUTPUTDIRNAME DUMPFILE=$OUTPUTFILENAME LOGFILE=$SID-exp.log exclude=TABLESPACE exclude=SCHEMA:\"IN \(\'OUTLN\',\'SYSTEM\',\'SYS\',\'MGMT_VIEW\',\'OLAPSYS\',\'ORDPLUGINS\',\'CTXSYS\',\'ORDSYS\',\'XDB\',\'EXFSYS\',\'ANONYMOUS\',\'ORDDATA\',\'OWBSYS\',\'OWBSYS_AUDIT\',\'APEX_030200\',\'APPSOSSYS\',\'WMSYS\',\'MDSYS\',\'FLOWS_FILES\',\'SYSMAN\',\'DBSNMP\',\'SPATIAL_WFS_ADMIN_USR\',\'SPATIAL_CSW_ADMIN_USR\',\'APEX_PUBLIC_USER\',\'DIP\',\'ORACLE_OCM\',\'MDDATA\',\'XS$NULL\',\'SCOTT\',\'TSMSYS\',\'MDDATA\',\'EDM_FILE_STORAGE_INDEX\'\)\"
echo "Export of $SID complete."

The export works without errors. I copy the .dmp file over to another machine that has an empty Oracle 11G database on it, and I run this import script:

#!/bin/bash

#This script should be run as the oracle account. It takes the output from the ExportDatabaseFull.sh script and imports the files into a fresh Oracle database.

#user variables
SID=DATABASESIDHERE                             #SID of database to import into
SYSTEMPASSWORD=ENTERPASSWORDHERE                #system user password
INPUTDIR=/u01/impdmpinput                       #path to directory that log file is in and logs will be put. Make sure user has correct permissions

#Script variables. Do not change!
INPUTDIRNAME=IMPDMPINPUT
INPUTFILENAME=${SID}-expfull.dmp


echo "Switching to $SID."
export ORACLE_SID=$SID

echo "Ensuring database is started and output directory exists."
sqlplus / "as sysdba" <<END
startup mount;
alter database open;
create or replace directory $INPUTDIRNAME as '$INPUTDIR';
END

echo "Performing IMPDMP into $SID from $OUTPUTDIR/$INPUTFILENAME..."
impdp system/$SYSTEMPASSWORD STATUS=300 FULL=Y DIRECTORY=$INPUTDIRNAME DUMPFILE=$INPUTFILENAME LOGFILE=$SID-imp.log REUSE_DATAFILES=N TRANSFORM=STORAGE:N

echo "Finished importing."

I get some errors in the log file, but they don't look related to the package bodies.

Import: Release 11.2.0.1.0 - Production on Thu Sep 17 14:54:57 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** STATUS=300 FULL=Y DIRECTORY=IMPDMPINPUT DUMPFILE=TNATEST-expfull.dmp LOGFILE=TNATEST-imp.log REUSE_DATAFILES=N TRANSFORM=STORAGE:N 
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
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/ROLE
ORA-31684: Object type ROLE:"SELECT_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"EXECUTE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"DELETE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"RECOVERY_CATALOG_OWNER" already exists
ORA-31684: Object type ROLE:"GATHER_SYSTEM_STATISTICS" already exists
ORA-31684: Object type ROLE:"LOGSTDBY_ADMINISTRATOR" already exists
ORA-31684: Object type ROLE:"AQ_ADMINISTRATOR_ROLE" already exists
ORA-31684: Object type ROLE:"AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"SCHEDULER_ADMIN" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"OEM_ADVISOR" already exists
ORA-31684: Object type ROLE:"OEM_MONITOR" already exists
ORA-31684: Object type ROLE:"WM_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"JAVAUSERPRIV" already exists
ORA-31684: Object type ROLE:"JAVAIDPRIV" already exists
ORA-31684: Object type ROLE:"JAVASYSPRIV" already exists
ORA-31684: Object type ROLE:"JAVADEBUGPRIV" already exists
ORA-31684: Object type ROLE:"EJBCLIENT" already exists
ORA-31684: Object type ROLE:"JAVA_ADMIN" already exists
ORA-31684: Object type ROLE:"JAVA_DEPLOY" already exists
ORA-31684: Object type ROLE:"CTXAPP" already exists
ORA-31684: Object type ROLE:"XDBADMIN" already exists
ORA-31684: Object type ROLE:"AUTHENTICATEDUSER" already exists
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/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
ORA-31684: Object type DIRECTORY:"DATA_PUMP_DIR" already exists
ORA-31684: Object type DIRECTORY:"ORACLE_OCM_CONFIG_DIR" already exists
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/CONTEXT
ORA-31684: Object type CONTEXT:"REGISTRY$CTX" already exists
ORA-31684: Object type CONTEXT:"LT_CTX" already exists
ORA-31684: Object type CONTEXT:"DR$APPCTX" already exists
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
ORA-39083: Object type PROCOBJ failed to create with error:
ORA-29357: object AUTO_TASK_CONSUMER_GROUP already exists
Failing sql is:
BEGIN 
dbms_resource_manager.create_consumer_group('AUTO_TASK_CONSUMER_GROUP','System maintenance task consumer group','ROUND-ROBIN');COMMIT; END; 

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Object type PROCACT_SYSTEM failed to create with error:
ORA-01917: user or role 'SYSMAN' does not exist
Failing sql is:
BEGIN 
SYS.DBMS_UTILITY.EXEC_DDL_STATEMENT('GRANT EXECUTE ON DBMS_AQ TO SYSMAN');COMMIT; END; 
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-31625: Schema TSMSYS is needed to import this object, but is unaccessible
ORA-01435: user does not exist
Failing sql is:
BEGIN 
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'TNATEST', inst_scn=>'39281247982');COMMIT; END; 

ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-31625: Schema SYSMAN is needed to import this object, but is unaccessible
ORA-01435: user does not exist
Failing sql is:
BEGIN 
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'TNATEST', inst_scn=>'39281247982');COMMIT; END; 

ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-31625: Schema MGMT_VIEW is needed to import this object, but is unaccessible
ORA-01435: user does not exist
Failing sql is:
BEGIN 
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'TNATEST', inst_scn=>'39281247982');COMMIT; END; 

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "IFSAPP"."EDM_FILE_STORAGE_TAB"             4.274 GB   14113 rows
. . imported "IFSAPP"."VMOL_BUSINESS_TER"                576.7 MB  679179 rows
. . imported "IFSAPP"."LANGUAGE_SYS_TAB"                 234.6 MB 1925167 rows
. . imported "IFSAPP"."TERM_USAGE_DEFINITION_TAB"        14.68 MB   37416 rows
. . imported "IFSAPP"."VMOL_BASE_USER"                   230.5 MB 1600227 rows
. . imported "IFSAPP"."PDF_ARCHIVE_TAB"                  6.631 MB     110 rows
. . imported "IFSAPP"."VMOL_ZIPCITY_TER"                 162.3 MB 1150960 rows
. . imported "IFSAPP"."LANGUAGE_FILE_IMPORT_TAB"         58.00 MB     286 rows
 /* Note for Stack Overflow question, omitted several thousand rows of table imports for brevity */
. . imported "TNA_TRANSFER"."PARTY_TYPE_ID_PROPERTY"         0 KB       0 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
ORA-39082: Object type ALTER_FUNCTION:"TERCONS"."GET_REQ_SUMMARY" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"TERCONS"."GET_FIRST_PR_FOR_PO" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"TERCONS"."GET_PO_COMPLETELY_AUTH_DATE" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"TERCONS"."GET_PR_COMPLETELY_AUTH_DATE" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"IFSAPP"."GETNONLABORINVOICETOTALS" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"TERCONS"."HAS_CURRENT_ACCESS" created with compilation warnings
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
ORA-39082: Object type ALTER_PROCEDURE:"TERCONS"."TER_PROPHIX_UPDATE" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"TERCONS"."TER_CRM_PARTNER_UPDATE" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"TERCONS"."TER_COGS_UPDATE" created with compilation warnings
ORA-39083: Object type ALTER_PROCEDURE failed to create with error:
ORA-04052: error occurred when looking up remote object TERCONS.PHONEBOOK_TAB@TERPROD
ORA-00604: error occurred at recursive SQL level 3
ORA-02019: connection description for remote database not found
Failing sql is:

ALTER PROCEDURE "TERCONS"."COPY_PHONEBOOK"   COMPILE     PLSQL_OPTIMIZE_LEVEL=  2    PLSQL_CODE_TYPE=  INTERPRETED    PLSQL_DEBUG=  FALSE    PLSCOPE_SETTINGS=  '' REUSE SETTINGS TIMESTAMP '2015-05-12 12:36:54'
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
ORA-39083: Object type VIEW failed to create with error:
ORA-00979: not a GROUP BY expression
Failing sql is:
CREATE  FORCE VIEW "IFSAPP"."INC_INVOICE_UTIL_VOU2A_PUB" ("COMPANY", "IDENTITY", "PARTY_TYPE", "PARTY_TYPE_DB", "INVOICE_ID", "ROW_ID", "CODE_A", "CODE_B", "CODE_C", "CODE_D", "CODE_E", "CODE_F", "CODE_G", "CODE_H", "CODE_I", "CODE_J", "PROJECT_ID", "PROJECT_ACTIVITY_ID", "OPTIONAL_CODE", "AUTO_POSTING_PROC_CODE", "QUANTITY", "AMOUNT", "ACC_CURR_AMOUNT", "THIRD_AMOUNT", "TEXT", "POSTING_TYPE", "VO
ORA-39083: Object type VIEW failed to create with error:
ORA-00979: not a GROUP BY expression
Failing sql is:
CREATE  FORCE VIEW "IFSAPP"."OUT_INVOICE_UTIL_VOU2B_PUB" ("COMPANY", "IDENTITY", "PARTY_TYPE", "PARTY_TYPE_DB", "INVOICE_ID", "ROW_ID", "CODE_A", "CODE_B", "CODE_C", "CODE_D", "CODE_E", "CODE_F", "CODE_G", "CODE_H", "CODE_I", "CODE_J", "OPTIONAL_CODE", "PROJECT_ID", "PROJECT_ACTIVITY_ID", "QUANTITY", "AMOUNT", "ACC_CURR_AMOUNT", "THIRD_AMOUNT", "CURR_AMOUNT_DEBIT", "CURR_AMOUNT_CREDIT", "DOM_AMOUN
ORA-39083: Object type VIEW failed to create with error:
ORA-00979: not a GROUP BY expression
Failing sql is:
CREATE  FORCE VIEW "IFSAPP"."MAN_CUST_INVOICE_VOUCHERS3" ("COMPANY", "IDENTITY", "PARTY_TYPE", "PARTY_TYPE_DB", "INVOICE_ID", "ROW_ID", "CODE_A", "CODE_B", "CODE_C", "CODE_D", "CODE_E", "CODE_F", "CODE_G", "CODE_H", "CODE_I", "CODE_J", "OPTIONAL_CODE", "PROJECT_ID", "PROJECT_ACTIVITY_ID", "AUTO_POSTING_PROC_CODE", "DB_AMOUNT", "QUANTITY", "CURR_AMOUNT", "DOM_AMOUNT", "THIRD_AMOUNT", "CURR_AMOUNT_D
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39112: Dependent object type OBJECT_GRANT:"IFSAPP" skipped, base object type VIEW:"IFSAPP"."INC_INVOICE_UTIL_VOU2A_PUB" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"IFSAPP" skipped, base object type VIEW:"IFSAPP"."INC_INVOICE_UTIL_VOU2A_PUB" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"IFSAPP" skipped, base object type VIEW:"IFSAPP"."INC_INVOICE_UTIL_VOU2A_PUB" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"IFSAPP" skipped, base object type VIEW:"IFSAPP"."INC_INVOICE_UTIL_VOU2A_PUB" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"IFSAPP" skipped, base object type VIEW:"IFSAPP"."INC_INVOICE_UTIL_VOU2A_PUB" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"IFSAPP" skipped, base object type VIEW:"IFSAPP"."OUT_INVOICE_UTIL_VOU2B_PUB" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"IFSAPP" skipped, base object type VIEW:"IFSAPP"."OUT_INVOICE_UTIL_VOU2B_PUB" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"IFSAPP" skipped, base object type VIEW:"IFSAPP"."OUT_INVOICE_UTIL_VOU2B_PUB" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"IFSAPP" skipped, base object type VIEW:"IFSAPP"."OUT_INVOICE_UTIL_VOU2B_PUB" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"IFSAPP" skipped, base object type VIEW:"IFSAPP"."OUT_INVOICE_UTIL_VOU2B_PUB" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"IFSAPP" skipped, base object type VIEW:"IFSAPP"."MAN_CUST_INVOICE_VOUCHERS3" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"IFSAPP" skipped, base object type VIEW:"IFSAPP"."MAN_CUST_INVOICE_VOUCHERS3" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"IFSAPP" skipped, base object type VIEW:"IFSAPP"."MAN_CUST_INVOICE_VOUCHERS3" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"IFSAPP" skipped, base object type VIEW:"IFSAPP"."MAN_CUST_INVOICE_VOUCHERS3" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"IFSAPP" skipped, base object type VIEW:"IFSAPP"."MAN_CUST_INVOICE_VOUCHERS3" creation failed
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
Job "SYSTEM"."SYS_IMPORT_FULL_01" stopped due to fatal error at 18:05:56

When I inspect the database after the import, none of the package bodies were included, but the specs were. This is my issue. What should I change in order to ensure the package bodies get imported?

Some facts

  • There's over 5000 packages, spread between the IFSAPP and TERCONS schemas.
  • In the source database, all packages compile just fine.
  • I found a guy with a similar issue, but he didn't provide a solution.
  • Standalone procedures and functions came across without issue (other than some not compiling due to missing database links)
  • All tables were imported properly
  • All views were imported. Some have compilation errors due relying on functions in the missing package bodies.
  • The database export script completed entirely without issues, and this line was in the log file:

Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY

Improve this question
2
  • Looks like it only exported package spec and not package body. Check the expdp logfile and see if that was the case. If in the expdp log you see a line "Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY" then it surely exported package body. We don't know what is in the expdp, but you can check.
    – Raj
    Sep 21, 2015 at 18:21
  • @Raj I have amended my question, I did have the line Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY.
    – mason
    Sep 21, 2015 at 18:25

1 Answer 1

Reset to default
3

Your import did not complete successfully:

Job "SYSTEM"."SYS_IMPORT_FULL_01" stopped due to fatal error at 18:05:56

The datapump job most likely failed before it could import the package bodies. You can check the alert log to see if there's a more detailed error there.

I would also recommend installing the latest patch set. 11.2.0.1 is pretty old, and it may have a lot of bugs that were fixed in more recent patch sets.

Improve this answer
4
  • How can I tell what caused that? No error message given in the log.
    – mason
    Sep 21, 2015 at 17:32
  • I would start by checking the database alert log.
    – andba77
    Sep 21, 2015 at 17:35
  • Did that, nothing related there.
    – mason
    Sep 21, 2015 at 17:56
  • Maybe it was coincidence, but using 11.2.0.4 instead seemed to resolve that issue. You should probably edit that into your answer instead of just leaving it as a comment.
    – mason
    Sep 29, 2015 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.