Solved

export failed

Posted on 2013-01-02
12
1,819 Views
Last Modified: 2013-01-04
What is the problem with the export?

[oracle@wtu dbs]$ export ORACLE_SID=orcl
[oracle@wtu dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 2 21:19:34 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1707446272 bytes
Fixed Size                  1336988 bytes
Variable Size            1124075876 bytes
Database Buffers          570425344 bytes
Redo Buffers               11608064 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@wtu dbs]$ export ORACLE_SID=pros
[oracle@wtu dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 2 21:21:05 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area   83697664 bytes
Fixed Size                  1334660 bytes
Variable Size              75498108 bytes
Database Buffers            4194304 bytes
Redo Buffers                2670592 bytes
Database mounted.
Database opened.
SQL> select username from dbs_users;
select username from dbs_users
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select username from dba_user;
select username from dba_user
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select username from dba_users;

USERNAME
------------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
SCOTT
TBS
R_CAT
OUTLN
FLOWS_FILES
MDSYS

USERNAME
------------------------------
ORDSYS
EXFSYS
WMSYS
APPQOSSYS
APEX_030200
OWBSYS_AUDIT
ORDDATA
CTXSYS
ANONYMOUS
XDB
ORDPLUGINS

USERNAME
------------------------------
OWBSYS
SI_INFORMTN_SCHEMA
OLAPSYS
ORACLE_OCM
XS$NULL
MDDATA
DIP
APEX_PUBLIC_USER
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR

32 rows selected.

SQL> alter user scott account unlock;

User altered.

SQL> conn scott/tiger
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

SQL> conn / as sysdba
Connected.
SQL> create directory mydir as '/home/oracle/Desktop/mydir';

Directory created.

SQL> desc dba_directories;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 DIRECTORY_NAME                            NOT NULL VARCHAR2(30)
 DIRECTORY_PATH                                     VARCHAR2(4000)

SQL> select directory_name from dba_directories;

DIRECTORY_NAME
------------------------------
MYDIR
XMLDIR
DATA_PUMP_DIR
ORACLE_OCM_CONFIG_DIR

SQL> select directory_path from dba_directories;

DIRECTORY_PATH
--------------------------------------------------------------------------------
/ade/b/1191423112/oracle/rdbms/xml
/home/oracle/Desktop/mydir
/u01/app/oracle/admin/pros/dpdump/
/u01/app/oracle/product/112/ccr/state

SQL> host
[oracle@wtu dbs]$
[oracle@wtu dbs]$ cd /home/oracle/Desktop/mydir
[oracle@wtu mydir]$ ls
[oracle@wtu mydir]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 2 21:47:35 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user moon identified by light;

User created.

SQL> grant create session to moon;

Grant succeeded.

SQL> grant create table to moon;

Grant succeeded.

SQL> alter user moon quota 1M on users;

User altered.

SQL> alter user monn quota 1M on system;
alter user monn quota 1M on system
           *
ERROR at line 1:
ORA-01918: user 'MONN' does not exist


SQL> alter user moon quota 1M on system;

User altered.

SQL> grant read,write on directory mydir to moon;

Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

SQL> host
[oracle@wtu mydir]$ expdp scott/tiger directory=mydir dumpfile=exp.dmp logfile=exp.log

Export: Release 11.2.0.1.0 - Production on Wed Jan 2 22:06:39 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3263
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4488
ORA-06512: at line 1

[oracle@wtu mydir]$
0
Comment
Question by:walkerdba
  • 4
  • 2
  • 2
  • +2
12 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38739092
you can try to look at this thread which is related to your question/issue :

https://forums.oracle.com/forums/thread.jspa?threadID=2314328
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38739098
also have a look at this : http://www.orafaq.com/maillist/oracle-l/2007/01/27/2160.htm

may be some dictionary objects are invalid and hence may be the error.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 38739305
you created the directory reference in the database
does the directory also exist on the host

> you can create directories in the database pointing to non-existing host directories

try this before running expdp:
alter system set streams_pool_size=1M;
0
 
LVL 36

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 100 total points
ID: 38739335
don't you need to set what you are exporting ?

expdp scott/tiger schemas=scott directory=mydir dumpfile=exp.dmp logfile=exp.log
0
 
LVL 12

Expert Comment

by:praveencpk
ID: 38739386
did you grant these roles to scott before exporting?

you have created your own directory and given read/write to the new user "MOON" and you are using scott for export, did u give read/write permission to scott on directory.

and also you have to mention you want schema,table or full DB export.

check this doc for more options on expdp/impdp

http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38739875
Check the following doc on Oracle Support:
UDE-31623 Error With DataPump Export [ID 1080775.1]
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:walkerdba
ID: 38741626
Can you tell me what is wrong here...

[oracle@new dbs]$ export ORACLE_SID=data
[oracle@new dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 3 11:26:05 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system
SQL> startup spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdata.ora';
SP2-0714: invalid combination of STARTUP options
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdata.ora';

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  146472960 bytes
Fixed Size                  1335080 bytes
Variable Size              92274904 bytes
Database Buffers           50331648 bytes
Redo Buffers                2531328 bytes
Database mounted.
Database opened.
SQL> alter user scott account unlock;

User altered.

SQL> alter user scott identified by scott;

User altered.

SQL> conn scott/scott
Connected.
SQL> select * from cat;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

SQL> conn / as sysdba
Connected.
SQL> create or replace directory test as '/home/oracle/Desktop/test';

Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY test TO scott;

Grant succeeded.

SQL> create user moon identified by light;

User created.

SQL> grant read,write on directory test to moon;

Grant succeeded.

SQL> grant create session to moon;

Grant succeeded.

SQL> grant create session to scott;

Grant succeeded.

SQL> grant create table to moon;

Grant succeeded.

SQL> alter user moon quota 1M on user;
alter user moon quota 1M on user
                            *
ERROR at line 1:
ORA-02156: invalid TEMPORARY tablespace identifier


SQL> alter user moon quota 1M on system;

User altered.

SQL> alter user moon quota 1M on users;

User altered.

SQL> host
[oracle@new dbs]$ expdp scott/tiger@data tables=EMP,DEPT directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

Export: Release 11.2.0.1.0 - Production on Thu Jan 3 11:49:36 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

UDE-12545: operation generated ORACLE error 12545
ORA-12545: Connect failed because target host or object does not exist

[oracle@new dbs]$ expdp scott/tiger@data directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

Export: Release 11.2.0.1.0 - Production on Thu Jan 3 11:50:34 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

UDE-12545: operation generated ORACLE error 12545
ORA-12545: Connect failed because target host or object does not exist

[oracle@new dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 3 11:51:25 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc dba_directories;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 DIRECTORY_NAME                            NOT NULL VARCHAR2(30)
 DIRECTORY_PATH                                     VARCHAR2(4000)

SQL> select directory_name from dba_directories;

DIRECTORY_NAME
------------------------------
TEST
XMLDIR
DATA_PUMP_DIR
ORACLE_OCM_CONFIG_DIR

SQL>
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 38741643
>>scott/tiger@data

This is doing a sqlnet connection to the 'data' database as defined in your tnsnames.ora file.

Try without using the '@data' after the password.
0
 

Author Comment

by:walkerdba
ID: 38742691
Export is working fine..
Now I want to import to moon schema..

But it is not working fine...

[oracle@new dbs]$
[oracle@new dbs]$ expdp scott/scott directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

Export: Release 11.2.0.1.0 - Production on Thu Jan 3 19:09:08 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/Desktop/test/EMP_DEPT.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:10:27

[oracle@new dbs]$ impdp moon/light directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

Import: Release 11.2.0.1.0 - Production on Thu Jan 3 19:34:32 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31655: no data or metadata objects selected for job
ORA-39154: Objects from foreign schemas have been removed from import
Master table "MOON"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "MOON"."SYS_IMPORT_FULL_01":  moon/******** directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
Job "MOON"."SYS_IMPORT_FULL_01" successfully completed at 19:34:37

[oracle@new dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 3 19:34:48 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> conn moon/light
Connected.
SQL> select * from tab;

no rows selected

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@new dbs]$ export ORACLE_SID=data
[oracle@new dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 3 19:35:54 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME
---------
DATA

SQL> conn moon/light
Connected.
SQL> select * from tab;

no rows selected

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@new dbs]$ impdp moon/light directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

Import: Release 11.2.0.1.0 - Production on Thu Jan 3 19:38:17 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31655: no data or metadata objects selected for job
ORA-39154: Objects from foreign schemas have been removed from import
Master table "MOON"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "MOON"."SYS_IMPORT_FULL_01":  moon/******** directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
Job "MOON"."SYS_IMPORT_FULL_01" successfully completed at 19:38:21

[oracle@new dbs]$
0
 
LVL 12

Accepted Solution

by:
praveencpk earned 300 total points
ID: 38742941
you should use REMAP_SCHEMA parameter to import scott to moon.

i.e
oracle@new dbs]$ impdp system/***** REMAP_SCHEMA=SCOTT:MOON directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
0
 

Author Comment

by:walkerdba
ID: 38743182
Working fine..



[oracle@new dbs]$ impdp scott/scott REMAP_SCHEMA=SCOTT:MOON directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

Import: Release 11.2.0.1.0 - Production on Fri Jan 4 02:01:20 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31631: privileges are required
ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remappings.

[oracle@new dbs]$ impdp moon/light REMAP_SCHEMA=SCOTT:MOON directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

Import: Release 11.2.0.1.0 - Production on Fri Jan 4 02:01:47 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MOON"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "MOON"."SYS_IMPORT_FULL_01":  moon/******** REMAP_SCHEMA=SCOTT:MOON directory=test dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MOON"."DEPT"                               5.937 KB       4 rows
. . imported "MOON"."EMP"                                8.570 KB      14 rows
. . imported "MOON"."SALGRADE"                           5.867 KB       5 rows
. . imported "MOON"."BONUS"                                  0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "MOON"."SYS_IMPORT_FULL_01" successfully completed at 02:01:55

[oracle@new dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 4 02:02:03 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "SYS"
SQL> conn moon/light
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

SQL>


Thank You
0
 

Author Closing Comment

by:walkerdba
ID: 38743185
Thank YOu..
0

Featured Post

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.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 recover a database from a user managed backup

707 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