Error haciendo IMPDP

orison
24 de Marzo del 2006
Hola a todos.. por favor necesito ayuda en el siguiente error:

impdp martin/[email protected] full=y directory=TEST_DIR dumpfile=DB10G.DMP

sale el error:

UDI-00008: la operacion ha generado un error ORACLE 12154

ORA-12154: TNS:no se ha podido resolver el identificador de conexion especificado

Lo que quiero saber es si es por el archivo DB10G.DMP que es donde esta la data y quiero hacer un volvado complete al Oracle..pero no se donde colocar el DMP, o es que dentro del comando impdp tengo que poner algo asi como “C:/DATOS/DB10G.DMP” algo asi ? o es que es problema de configuración del listener.ora.

Gracias a todos

Rodolfo Reyes
24 de Marzo del 2006
PURPOSE
-------

This document describes how a directory can be specified that is used by
the Oracle10g Export/Import DataPump utilities to write the dumpfile(s),
logfile (if specified) and SQL file (if specified).


SCOPE & APPLICATION
-------------------

For everyone who uses the Export DataPump (expdp) utility to export data from
an Oracle10g database and the Import DataPump (impdp) utilities to import
data into an Oracle10g database.


HOW TO SPECIFY A DIRECTORY IN ORACLE10G EXPORT/IMPORT DATAPUMP
--------------------------------------------------------------


1. SERVER-BASED OR CLIENT-BASED
----------------------------

The parameter DIRECTORY specifies the location to which Export DataPump
or Import DataPump can write the dump file set, the log file, and the
SQL file (Import DataPump only).
As export DataPump and import DataPump are server-based, rather than
client-based, the output files are accessed relative to server-based
directory paths. Data Pump requires you to specify directory paths
as directory objects. A directory object maps a name to a directory path
on the file system.


2. HOW TO CREATE A DIRECTORY
-------------------------

To create a directory, you must have the DBA role or you must have been
granted the CREATE ANY DIRECTORY privilege.

Example (a DBA creates directories on the Windows platform and grants
access to user scott):

SQL> CONNECT system/manager
SQL> CREATE OR REPLACE DIRECTORY my_dir as 'D:expdp_out';
SQL> CREATE OR REPLACE DIRECTORY my_dir_log as 'D:expdp_outlog';
SQL> GRANT read,write ON DIRECTORY my_dir TO scott;
SQL> GRANT read,write ON DIRECTORY my_dir_log TO scott;

Example (a normal user with the CREATE ANY DIRECTORY privilege
creates directories on the Unix platform - this user
automatically has READ and WRITE privilege on that directory):

SQL> CONNECT system/manager
SQL> GRANT CREATE ANY DIRECTORY TO scott;
SQL> CONNECT scott/tiger
SQL> CREATE OR REPLACE DIRECTORY my_dir as '/usr/expdp_out';
SQL> CREATE OR REPLACE DIRECTORY my_dir_log as '/usr/expdp_out/log';

Note that the CREATE DIRECTORY statement does not actually create
the directory for you on disk. If the directory is invalid, a DataPump
job will fail with:

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation


3. HOW TO QUERY THE AVAILABLE DIRECTORIES
--------------------------------------

To query on which directories you have privilege to read and write:

SQL> SELECT privilege, directory_name
FROM user_tab_privs t, all_directories d
WHERE t.table_name(+)=d.directory_name
ORDER BY 2,1;


4. REQUIRED OPERATING SYSTEM PERMISSIONS
-------------------------------------

Note that READ or WRITE permission to a directory object only means
that the Oracle database will read or write that file on your behalf.
You are not given direct access to those files outside of the Oracle
database unless you have the appropriate operating system privileges.
Similarly, the Oracle database requires permission from the operating
system to read and write files in the directories.


5. HOW DATAPUMP DETERMINES THE LOCATION FOR THE FILES
--------------------------------------------------

a. Rule 1:
-------
If a directory object is specified as part of the file specification,
then the location specified by that directory object is used.
Example to create the dumpfile in directory MY_DIR (no logfile is
written):

expdp scott/tiger DUMPFILE=my_dir:exp_scott.dmp NOLOGFILE=Y

b. Rule 2:
-------
If a directory object is not specified for a file, then the directory
object named by the DIRECTORY parameter is used. Example to create the
dump file in directory MY_DIR and the logfile in MY_DIR_LOG:

expdp scott/tiger DIRECTORY=my_dir DUMPFILE=exp_scott.dmp
LOGFILE=my_dir_log:exp_scott.log

c. Rule 3:
-------
If a directory object is not specified, and if no directory object
was named by the DIRECTORY parameter, then the value of the environment
variable, DATA_PUMP_DIR, is used. This environment variable is defined
using operating system commands on the client system where the Data Pump
Export and Import utilities are run. The value assigned to this
client-based environment variable must be the name of a server-based
directory object, which must first be created on the server system.
Example to create the dump file in directory MY_DIR and the logfile
in MY_DIR_LOG:

On the client machine where expdp is started, set the environment
variable:

C:> set DATA_PUMP_DIR=MY_DIR
C:> expdp scott/[email protected]_db_alias DUMPFILE=exp_scott.dmp
LOGFILE=my_dir_log:exp_scott.log

Note that the interpretation of the name of the directory in the
environment variable DATA_PUMP_DIR is case sensitive. Specifying an
incorrect value for the DATA_PUMP_DIR environment variable (e.g.:
set DATA_PUMP_DIR=My_Dir) can give errors, such as:

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name My_Dir is invalid

4. Rule 4:
-------
If none of the previous three conditions yields a directory object
and you are a privileged user (i.e. uses who have the EXP_FULL_DATABASE
role and IMP_FULL_DATABASE role), then Data Pump attempts to use
the value of the default server-based directory object, DATA_PUMP_DIR.
It is important to understand that Data Pump does not create the
DATA_PUMP_DIR directory object; it merely attempts to use its value
when a privileged user has not provided a directory object using
any of the mechanisms previously described. This default directory
object must first be created by a DBA. Do not confuse this with the
client-based environment variable of the same name.
Example to create all files in the directory DATA_PUMP_DIR:

First unset the environment variable DATA_PUMP_DIR that was set in the
previous example:

C:> set DATA_PUMP_DIR=

Then create a directory with the name DATA_PUMP_DIR

SQL> CONNECT SYSTEM/MANAGER
SQL> CREATE OR REPLACE DIRECTORY data_pump_dir AS 'D:expdp_out';
SQL> GRANT read, write ON DIRECTORY data_pump_dir TO scott;

C:> expdp system/[email protected]_db_alias DUMPFILE=exp_scott.dmp
LOGFILE=exp_scott.log SCHEMAS=scott

Note that user SCOTT who isn't a privileged user, cannot use this last
condition. Possible errors:
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39145: directory object parameter must be specified and non-null
Solution for user SCOTT: as described in previous condition, user SCOTT
can set the environment variable DATA_PUMP_DIR to MY_DIR:
C:> set DATA_PUMP_DIR=MY_DIR
Or in this specific case where user SCOTT also has the read and write
privilege on directory DATA_PUMP_DIR:
C:> set DATA_PUMP_DIR=DATA_PUMP_DIR

Jor-El
24 de Marzo del 2006
Mu bien Rodolfo, sabes copiar-pegar, pero me dá la impresión de que lo que está cascando es esto :

@db10g


Lee bien el error, anda...............

orison
24 de Marzo del 2006
Buenas.. aca dice todo del comando expdp pero de IMPDP ? Gracias.


orison
24 de Marzo del 2006
Buenas.. estoy ejecutando otro modo de hacer un impdp y es asi:

impdp system/123456 dumpfile=EXPDAT.DMP remap_tablespace-system:example3 logfile=example3imp.log job_name=example3

el error que me sale es:

ORA-39001: valor de argumento no vßlido
ORA-39000: especificaci¾n de archivo de volcado err¾nea
ORA-39143: el archivo de volcado "E:oraclexeapporacleadminXEdpdumpEXPDAT.
DMP" puede ser un archivo de volcado de exportaci¾n original

ORISON
24 de Marzo del 2006
Holas.. el DMP fue creado en Oracle 8.1.6 puede ser ese el problema ? es que de verdad todo esto de impdp lo debo de hacer desde un Oracle con version 8.1.6 para que funcione ? gracias