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
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY. – mason Sep 21 '15 at 18:25