Help with Dump EXP / IMP

[claudiofreis]

Hello gentlemen I'm having to learn how to do an exp and idea dump that I had never done
I make the export but when I import it it generates several mistakes but that probably occurs in the export the user I already created and gave Grants to create tables, sessions, synonyms, procedures, views, sequences I hope they can help me was given this the commands


and with this it generates several errors at the end of the File generated:

Select all

ORA-02304: literal do identificador de objeto invalido 
IMP:00017:  a instrucao a seguir falhou com o erro 2304 ORACLE: 
CREATE TYPE " IMF_MOVTO_REGIAO_CIDADE" TIMESTAMP '2013-01-14....

and at the end of the IMP he generated

Select all

IMP -0017: a instrucao a seguir falhou com varios erros iguais a este 2430 oracle: 
"Alter table "estabelecimento" ENABLE CONSTRAINT "FK_SAF_1247"

I need to export everything from the source user and import into another user
att.
Claudio

[stcoutinho]

Hello claudiofreis,

Benvendo to the Glufke Forum!

About the problem you report, the source and destination database version would be exactly the same release? For example, are both releases 11.0.2? If there is a difference of versions between source and destination, you must use the oldest exp / IMP program.

For example, if your source base is 10G and the 11G destination, you need to do your Export / Import with the Exp / Imp release programs.

Another point: rather than make an Export / Import Full of the database, you can limit your dump to the schema you want to do this Export / Import.

For Export, you can use this syntax (example):

Select all

exp system/manager@XPTO FILE=EXP_ORIGEM.DMP OWNER=<nome_schema_a_exportar> DIRECT=Y ROWS=Y GRANTS=Y STATISTICS=NONE COMPRESS=Y BUFFER=10000000 LOG=EXP_ORIGEM.LOG

To import, you can use this syntax (example):

Select all

imp system/manager@XPTO FILE=EXP_ORIGEM.DMP FROMUSER=<nome_schema_exportado> TOUSER=<nome_do_schema_destino> IGNORE=Y ROWS=Y GRANTS=Y BUFFER=10000000 LOG=IMP_ORIGEM.LOG

Make sure the Schema receives this Dump by Import does not have objects from another previous import attempt.

Another point: If the version of your database is 11g, be careful with the tables that have never been populated with data. In Release 11G, Oracle does not allocate space in Tablespace for a recent-up table. Only when it is populated a first time with records, then Oracle will allocate spaces (segments) on Tablespace in question. As Oracle uses the segment information to relate the tables you need to export, then your dump can be immcomplete.

For this case I mentioned above, I suggest you make a

Select all

ALTER TABLE <tabela> ALLOCATE EXTENT

command for all "Data virgin" tables in your source database before you run the Export. This will ensure that all Schema tables are related to your Export.

Hugs and good clip,

Sergio Coutinho

[Michel Pessoa]

If the Oracle version is 10g or higher I advise the execution of the Data Pump. Follow passes below:

[color=# 00BF00] Export: [/color]

We need to create a directory in the database, for Example:

Select all

CREATE OR REPLACE DIRECTORY BACKUP_muito AS 'C:\BACKUP';

'C: \ backup' is the path chosen for this directory. In this location that the DMP file will be generated

Backup_muito was the given name for this directory.

so that the user can access a directory it is necessary for the same permission. The permission is granted in this way:

Select all

GRANT READ, WRITE ON DIRECTORY BACKUP_muito TO NAME_SCHEMA;

Select all

NAME_SCHEMA

is the user who will receive permission.

Open the command prompt to generate the dump itself.

Select all

EXPDP NAME_SCHEMA DIRECTORY=BACKUP_muito DUMPFILE=NOME_DO_ARQUIVO.DMP SCHEMAS=NAME_SCHEMA

Select all

NAME_SCHEMA

is the user chosen to log in.

Select all

DUMPFILE=NOME_DO_ARQUIVO.DMP

is the file that will be generated. As a suggestion, choose a nomenclature that identifies the customer / product and the backup date.

Select all

SCHEMAS=NAME_SCHEMA

This is the schema that you want to back up.



[color=# 00BF00] Import: [/color]

Command to run at the command prompt :

Select all

IMPDP NAME_SCHEMA DIRECTORY=BACKUP_muito DUMPFILE=NOME_DO_ARQUIVO.DMP REMAP_SCHEMA=USER_OLD:USER_NEW REMAP_TABLESPACE=TABLESPACE_OLD:TABLESPACE_NEW

follows the same line of export reasoning.

Select all

REMAP_SCHEMA=USER_OLD:USER_NEW

Changes the name of the source schema to the destination, separating by two points (:).

Select all

REMAP_TABLESPACE=TABLESPACE_OLD:TABLESPACE_NEW

Changes the source tablespace to the destination, separating two points (:).

[al3ks4nder]

Sorry to relive the topic .. but I am researching and trying to resolve a message that is giving at the time of trying the EXPDP.

Select all

Conectado a: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production 
ORA-39002: operação inválida 
ORA-39070: Não é possível abrir o arquivo de log. 
ORA-39087: o nome do diretório ORACLE é inválido

I do not know what can happen .. I am new to the subject and I still have difficulties .. The option to do the EXPDP was that the normal dump was generating problems in the sequences and made an error of integrity when saving a new record ..

Thanks from now