1

I'm attempting to export from a physical Oracle 11.2.0.3.0 database to a amazon rds Oracle database. I used the expdp and exported a full database and incurred no errors upon export.

I've moved the copy over to my server running SQL Developer in my AWS environment, and using the impdp command I incur a slew of errors.

I've seen snippets in my searches for a few things, but having tried suggestions, it seems that isn't the error. 

c:\Windows\system32\> impdp [email protected]:1521/DATABASE file=e:\export\full_export.sql log=e:\export\import.log

Running the above command I get the below. 

Import: Release 11.2.0.1.0 - Production 
Connected to Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
ORA-31626: job does not exist
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTDBY"
ORA-06512: at "SYS.KUPV$FT", line 1009
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_LOGREP_UTIL
ORA-20900: RDS restricted DDL Found: ALTER VIEW SYS.ALL_CAPTURE
ORA-06512: at "RDSADMIN.RDSADMIN", line 367
ORA-06512: at line 2
ORA-04063: view "SYS.DBA_CAPTURE" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGPREP_UTIL"

I've tried different schemas and all end with the same error. Looking AWS disabled the ... AS SYSDBA option with logging in, so I can't do that either.

I've seen some random web posts regarding datapump and RDS not playing nicely, but I can't get anything concrete.

Our DBA's have assured me that the data pump export worked flawlessly, as they could import it to another physical oracle database. So could this just be an RDS issue and not Oracle?

Improve this question
5
  • Have you followed this? docs.aws.amazon.com/AmazonRDS/latest/UserGuide/… and used DBMS_FILE_TRANSFER to copy the exported dump file to the Amazon RDS instance ?
    – Philᵀᴹ
    Commented Sep 26, 2016 at 15:11
  • May be you need to exclude the SYS schema from the export (surely you don't have any user objects in that schema, right?)
    – mustaccio
    Commented Sep 26, 2016 at 17:23
  • 1
    I wonder if the issue is that you have a RDS database. Do you really have access to the physical disk with RDS? Since you are using data pump you need to write to a database directory. If you are testing the data pump process you can also just create a database directory and try to write a simple file using utl_file to see if you can do that. Because if you can't get utl_file to work data pump won't work. Another option is to use the older export and import, which can run from another machine and doesn't require a database directory.
    – Gandolf989
    Commented Sep 26, 2016 at 20:28
  • @Gandolf989 Basically what I have said - read the link I posted
    – Philᵀᴹ
    Commented Sep 27, 2016 at 7:38
  • I submitted a ticket to AWS and recieved this back. "It appears that RDS does not support the use of that particular stored procedure referenced in your stored procedure. As such you'll need to exclude the calling procedure with draws upon the "RDS restricted DDL found: ALTER VIEW SYS.ALL_CAPTURE" So, I'll continue to talk with them and hoping to figure out how to eliminate a stored procedure.
    – Matt King
    Commented Sep 27, 2016 at 17:09

1 Answer 1

Reset to default
1

After hours of trying numerous things I've found this as to answer my question. On a tip of an Oracle Community discussion, I was told to run this below. 

select comp_id, version, status, comp_name
from dba_registry
where comp_id='SDO';

This would tell me if I am or not running Oracle Spatial on my existing database that I ran the export from. Now, why I bring this up is I've asked numerous people in my organization if we're using spatial and numerous people have swore up and down we don't. Come to find out, the database is installed with spatial and that above sql script showed me that yes, it is running spatial.

RDS won't accept spatial data, even if there is no spatial data in it. So I'm in the process of installing an EC2 instance with 11gR2 and actually importing the data that way. This is why the stored procedures failed as above. I just don't know why our AWS team didn't catch this when I submitted my trouble ticket. Oh well.

Thanks for all of the help.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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