Solved

How to re-import data in Oracle 11g on Oracle Linux 6.5

Posted on 2014-10-20
20
513 Views
Last Modified: 2014-11-16
Greetings,

I need to re-import the data pump file(by Oracle Enterprise Manager) to an Oracle 11g database running on Oracle Linux 6.5.

Please provide detailed instructions.

Thanks.

Mark
0
Comment
Question by:mrong
  • 10
  • 9
20 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
What do you mean by "re-import"?

If you mean:
Get rid of everything currently in the database and do the import again:
Drop and recreate the schemas you want imported, then run the import for those schemas.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
You can also take a look at:
http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL936

TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]

REPLACE drops the existing table and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
0
 

Author Comment

by:mrong
Comment Utility
slightwv,

I mean to import and replace all the data from previous imported dmp files.
Can I use the oracle em feature(Data Movement->Import from export file to do it?
Thanks.

Mark
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>I mean to import and replace all the data from previous imported dmp files.

I've never tried TABLE_EXISTS_ACTION=REPLACE but from the docs, it should do it.

When I've needed to do something like this, I drop/recreate the user then import.  This way I know I'm getting everything in the import file and not leaving any potential new stuff around.

>>Can I use the oracle em feature(Data Movement->Import from export file to do it?

Don't know.  Don't use the GUI for much of anything.  The GUI should have all the options available that the command line has but I cannot say for sure.
0
 

Author Comment

by:mrong
Comment Utility
Can I use TABLE_EXISTS_ACTION to replace all the tables?
Thanks.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>Can I use TABLE_EXISTS_ACTION to replace all the tables?

That is what the documentation says it does.  I have to believe the docs since I've never personally done it.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
Comment Utility
Also to add TABLE_EXISTS_ACTION=REPLACE will very much do replace the table data, but the only exception to use this option is you must not have any referential integrity (constraints) on the your tables as it simply deletes and inserts the data again, in case of referential integrity constraints might prevent the deletion in case the child-parent order is not followed(and as far as i remember the import of table data will happen in alphabetical order)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>> on the your tables as it simply deletes and inserts the data again

Small correction:  REPLACE drops the table.  TRUNCATE deletes the data.

The comment on referential integrity constraints is correct and also in the docs:
When you use TRUNCATE or REPLACE, ensure that rows in the affected tables are not targets of any referential constraints.
0
 

Author Comment

by:mrong
Comment Utility
What sql stmt do I need to run to drop the schema?
The user is root. Do I have to drop the root user in order to drop the schema?
Thanks.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
root is a Unix user (the main Unix user)

A schema in Oracle is a database user that owns objects.

What schemas in the import file do you want to import?

If you are not familiar with the schemas in the file and how the database has been built, you might be better off just doing the replace.

If the schemas have a lot of grants, the import might not get them all depending on who granted them.

If you drop a user, all grants are removed.  Depending on the export, you might need to re-grant them.  Again, depends on the export.

You would need to test this before doing anything in production.

All that said, to drop a schema(user), and recreate it:
First grab the DDL to recreate it:
select dbms_metadata.get_ddl('USER','SOMEUSER') from dual;

The drop them:
drop user SOMEUSER cascade;

Where SOMEUSER is the schema you are dealing with.

The recreate them using the DDL from the select above.

Then import the data.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:mrong
Comment Utility
I need to drop and then import ROOT schema.
Thanks.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Just to clarify:  You have a database user named 'ROOT'?

If so, the steps are the same as posted above.
0
 

Author Comment

by:mrong
Comment Utility
Yes, ROOT is a database user.

Please let me know if any mistakes from the steps below.
1. spool the output from get_ddl into rebuild_root.sql file
2. drop the users/schema by: drop user SOMEUSER cascade;
3. re-create the users by running rebuild_root.sql
4. reimport the ROOT Schema and import the data again.

Thanks.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Step 3: not users plural since there is only the one user:
drop user root cascade;

Step 4 should be able to be done in one step as long as the export file only contains that user.

Other than that it looks good with the one exception I noted above:
If the schemas have a lot of grants, the import might not get them all depending on who granted them.

If you drop a user, all grants are removed.  Depending on the export, you might need to re-grant them.  Again, depends on the export.
0
 

Author Comment

by:mrong
Comment Utility
I will try slightwv's suggestion and update this case soon. thanks.
0
 

Author Comment

by:mrong
Comment Utility
slightwv,
Below are what I did on the Oracle demo server. Please let me know if you see any problems. Thanks.

1. SELECT DBMS_METADATA.GET_DDL('USER','ROOT') FROM dual;


DBMS_METADATA.GET_DDL('USER','ROOT')
--------------------------------------------------------------------------------

   CREATE USER "ROOT" IDENTIFIED BY VALUES 'S:FA1C56EED0DDECDC3E3FD2320F53518451
C10B980B9C63AC96AE35BC8AB0;34754AB434AE34CE'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"

2. drop user ROOT cascade

3. Use the dll in step1 to recreate ROOT user

4. Use oem datapump to create schema and data import. Below is the sql if I click 'show sql' from oem
declare
  h1   NUMBER;

begin
     
h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'SCHEMA', job_name => 'IMPORT ', version => 'COMPATIBLE');
   
dbms_datapump.set_parallel(handle => h1, degree => 1);
   
dbms_datapump.add_file(handle => h1, filename => 'IMPORT.LOG', directory => 'DATA_PUMP_DIR', filetype => 3);
   
dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
   
dbms_datapump.add_file(handle => h1, filename => 'expAiMfull.dmp', directory => 'DATA_PUMP_DIR', filetype => 1);
   
dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_TABLESPACE', old_value => '/ORACLE/ORADATA/MAX/UD1/DATAMAX_01.DBF', value => '+DATA/MAX/DATAFILE/MAX01.266.860253321.DBF');
   
dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''ROOT'')');
   
dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
   
dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');
   
dbms_datapump.set_parameter(handle => h1, name => 'TABLE_EXISTS_ACTION', value => 'REPLACE');
   
dbms_datapump.set_parameter(handle => h1, name => 'SKIP_UNUSABLE_INDEXES', value => 0);
   
dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
   
dbms_datapump.detach(handle => h1);
end;

/
5. Ran the following after the import completed.
Exec sys.utl_recomp.recomp_serial(‘ROOT’)
Select object_name from dba_objects where status <> ‘VALID’ and owner = ‘ROOT’;
No row return.
0
 

Author Comment

by:mrong
Comment Utility
the import job said failed, but the tables look fine. Below is the import.log file. It is just a warning caused user ROOT was already created in step 3 above ?

Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"ROOT" already exists

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/SEQUENCE/SEQUENCE

Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "ROOT"."AE_X"                       287.8 MB  641774 rows

xxxxxxx imported all other tablesXXXXXXX

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/FUNCTION/FUNCTION

Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE

Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION

Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE

Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW

Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER

Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/MATERIALIZED_VIEW_LOG

Job "oracle"."11-16-2014 DATA IMPORT" completed with 1 error(s) at 08:36:10
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Looks like the import tried to recreate the ROOT user.  Looks like you could have skipped that step.
0
 

Author Comment

by:mrong
Comment Utility
Should I let the import to recreate the user instead of using dll to do it? Thanks.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>Should I let the import to recreate the user instead of using dll to do it?

If you want no errors, then let the import do it.

The benefit to doing it manually is you have the opportunity to change things like the password.

If you are reloading something like a test database from a production export, you typically don't have the same passwords.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now