By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

Migrate schemas from RDS1 to RDS2

0

I am trying to migrate some oracle DB schemas from RDS1 to RDS2.

I used the instructions in this link for export /import of few schemas using data pump API since I dont have access to Operating System on RDS.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.DataPump.html#Oracle.Procedural.Importing.DataPumpS3.Step1

When I run the import code I get thousands of errors related to create user, tablespace name, create table, etc. A sample error is below.

I did create a tablespace with different name before running the import sql and created the same users I am importing.

Do you know why the import is trying to recreate the source tablespace and users on destination and generating all these errors when docs ask to create them manually before import? Can't I use different tablespace name on target?

I only need to import tables, and other objects such as triggers, views, sequences, pl/sql code, etc.

Failing sql is: CREATE USER "HR" IDENTIFIED BY VALUES 'S:84CA8478E48BA33;T:5F77E7F445C5B6FD48B0087B48EB4B2C922D506EC' DEFAULT TABLESPACE "HRDB" TEMPORARY TABLESPACE "TEMP" PROFILE "AGCY_APP_PROFILE" ACCOUNT LOCK ORA-39083: Object type USER:"TESTMD" failed to create with error: ORA-02380: profile AGCY_APP_PROFILE does not exist

Failing sql is: CREATE TABLE "HR"."EMP" ("EMP_ID" NUMBER(*,0) NOT NULL ENABLE, "NAME" VARCHAR2(60 BYTE) NOT NULL ENABLE, "ACTIVE" CHAR(1 BYTE) DEFAULT 'Y' NOT NULL ENABLE) PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "HRDB" ORA-39083: Object type TABLE:"HR"."USERS" failed to create with error: ORA-00959: tablespace 'HRDB' does not exist

Topics
Migration & ModernizationDatabaseAnalyticsManagement & Governance
Tags
General Database MigrationsAmazon Relational Database ServiceAWS Management ConsoleDatabaseOracle
Language
English
sam15
asked 7 months ago338 views
1 Answer
1
Accepted Answer

These errors are telling you that you are missing dependencies. If you try to import a user but their profile doesn't already exist, ORA-02380 is expected. If you import a table but its tablespace doesn't exist, ORA-00959 is expected. If you want to tell Oracle to import that table into a different tablespace, then you have to tell Data Pump how to make that decision. Check out REMAP_TABLESPACE in Oracle's documentation.

using data pump API since I dont have access to Operating System on RDS

The expdp and impdp clients have never required access to the operating system where the database is running. They're just clients, like sqlplus or sqlldr. You can use the PL/SQL Data Pump API but you can also just install Oracle Instant Client Tools package on whatever host you use to connect to your RDS instance and use the expdp and impdp clients.

AWS
AWS-User-9938760
answered 7 months ago
profile pictureAWS
EXPERT
Jose Guay
reviewed 7 months ago
  • sam15
    7 months ago

    Hi I am not trying to import a user, that is what the impdp tried to do. The docs say I should create the tablespace and users before the import so that triggers another error because user already exists. It did not mention anything about creating a user profile. Do I need to create this manually before import?

    For SQL client, I use SQL Developer. Are you saying I can run expdp/and impdp in the client after connecting to the RDS instance? I have not seen any example where they do that for the RDS. All use DBMS_DATAPUMP for running the export and import.

  • AWS-User-9938760
    7 months ago

    You'd have to check the Oracle documentation, but I believe profiles are only included in full database exports. If you export the schema, it's just the schema(/user). So you need to pre-create the things that schema depends on, including profiles.

    There's nothing RDS-specific about using the the expdp and impdp command line utilities. They're just programs you install on a computer and when you run them they connect to the database. The computer would need to be one that can access your database via the network, and you would need to define TNS aliases in ${ORACLE_HOME}/network/admin/tnsnames.ora. Then just run impdp <user>@<tnsalias> ..., again just like anywhere else. If it's easier to use the PL/SQL API that certainly works too.

  • sam15
    7 months ago

    Yes, my point is that expdp/impdp are operating system utility and I cant run them using SQL client. You have to run it on the server O/S and with RDS we dont have access to the operating system. I am not sure if you can run them on your Windows 10 local laptop using a TNS string connection similar to how SQL clients connect to database.

  • AWS-User-9938760
    7 months ago

    Windows 10 is an operating system. You can install the expdp and impdp client programs on that operating system and run them there. There has never been a requirement that you can only run them on the server. They're just programs. You run them and they connect to a database.

  • sam15
    7 months ago

    Do you have a link that shows how to use expdp and impdp on a windows client with a TNS string? I assume that files will be created on client in this case.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content