Oracle DataPump export failing. 1

[SantaMufasa]

Sheesh, it must be Friday. Can someone please look over what I'm doing here? We DataPump multiple times just about every day. But we are now experiencing errors on two different instances.

Here are details about my DataPump attempts:[ul][li]I am connecting directly to the machines upon which the Oracle instances are running.[/li][li]I am becoming the "oracle" user:

% sudo su - oracle

% whoami
oracle

[/li][li]Here are the Oracle directories for the instance to which I am connecting:

select * from dba_directories;

OWNER      DIRECTORY_NAME                 DIRECTORY_PATH
---------- ------------------------------ -------------------------------
SYS        DPUMP_A141_LOG_DIR             /opt/oracle/exports/a141/log
SYS        WORK_DIR                       /opt/oracle/v10204db/work
SYS        ORACLE_OCM_CONFIG_DIR          /opt/oracle/v10205db/ccr/state
SYS        DPUMP_A141_DMP_DIR             /opt/oracle/exports/a141/exp
SYS        DATA_PUMP_DIR                  /opt/oracle/v10204db/rdbms/log/

[/li][li]Here are the contents of my Oracle expdp parameter file that I am running:

% cat dp_exp_a141_tidal_data.par

DIRECTORY=DPUMP_A141_DMP_DIR
JOB_NAME=cubsa141_DLH_20110317
DUMPFILE=dp_a141_dlh_tidal_data_20110317-1_%u.dmp
LOGFILE=DPUMP_A141_LOG_DIR:dp_exp_as141_dlh_tidal_data_20110317-1.log
PARALLEL=4
SCHEMAS=TIDAL

[/li][li]Here are the permissions on the target folder:

% pwd

/opt/oracle/exports/a141

% ls -l

drwxr-xr-x 2 oracle dba 4096 2011-03-17 16:28 exp
drwxr-xr-x 2 oracle dba 4096 2011-03-17 16:28 log

[/li][li]Here is an overview of the source schema's contents:

USERNAME    SEGMENTS        BYTES
-------- ----------- ------------
TIDAL            236   58,064,896

[/li][li]Here is a confirmation that there are no Oracle DataPump jobs running currently:

SELECT * FROM DBA_DATAPUMP_JOBS;

no rows selected

[/li][li]Following confirms that there are no existing OPS$ORACLE-owned objects:

select table_name from dba_tables where owner like 'OPS%';

no rows selected

[/li][li]Here is my command-line invocation of DataPump:

% expdp / parfile=/opt/oracle/usr/davehunt/dp_exp_a141_tidal_data.par

Export: Release 10.2.0.5.0 - 64bit Production on Friday, 18 March, 2011 17:31:18

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "OPS$ORACLE.CUBSA141_DLH_20110317"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 866
ORA-01031: insufficient privileges

[/li][li]Notice that my invocation of Oracle DataPump is "expdp / ...", so I'm connecting as the "Almighty", All-privileged Oracle user "SYS".[/li][/ul]What am I missing/doing wrong?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Thargy]

Santa,

http://dbadoc.blogspot.com/2009/08/ora-31633-unable-to-create-master-table.html

might be of relevance.

I vaguely recall that with expdp as opposed to plain old exp, oracle does a lot of internal processing, to make tasks reentrant etc. This must require some sort of journalling and hence the possibility of collisions with journal info from prior activities.

It's late, and I'm shooting from the hip, just trying to help.

Regards

T

 

[SantaMufasa]

Thanks, Thargy. I know it's after midnight there, so I'm very appreciative of your late-night attention on this.

I actually did consider the problem that appears in your link, above. That was my bit about "select table_name from dba_tables where owner like 'OPS%';", just in case I was colliding with an existing DataPump master table.

So, my problem still seems to center around the error:
"ORA-01031: insufficient privileges", which I can't understand since I'm running as Oracle user "SYS".

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[karluk]

I don't use o/s authentication in my databases, although I've experimented with it a little over the years. I don't think that it works the way you are expecting. When you use the "connect /" syntax, you are connecting as a user named OPS$ORACLE, not as SYS. It would be different if you used "connect / as sysdba". Then you really would be SYS and could do whatever you want.

So, let's try setting up o/s authentication for the oracle id:

SQL> connect / as sysdba
Connected.
SQL> create user ops$oracle identified externally default tablespace users;

User created.

SQL> grant create session to ops$oracle;

Grant succeeded.

Now try logging is as OPS$ORACLE and querying what system privileges it has:

SQL> connect /
Connected.
SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
OPS$ORACLE                     CREATE SESSION                           NO

So OPS$ORACLE has only "create session", not the 27 privileges I would get if I queried user_sys_privs as the SYS user.

So it seems to me that if you really want to perform this datapump export as OPS$ORACLE rather than SYS, you will need to grant EXP_FULL_DATABASE to the OPS$ORACLE id.

 

[aking]

This may not be relevant as i only know about running Oracle on windows, and i can't follow all of the command line stuff you've posted:
But I had the same errors about insufficient privileges when first setting up my datapump jobs. They wouldn't run as the user SYS, i had to create another user and grant that user the equivalent rights. Wasted a day getting to that workaround but since then all datapump jobs have worked every night reliably (have 3 10gR2 servers all of which do nightly schema exports).

 

[SantaMufasa]

Gentlepeople,

Thanks for all of your help on this issue.

Karl, I believe that you got me headed down the right direction with your observations re: the OPS$ORACLE user. Although I already had the OPS$ORACLE user created, someone had gone in and REVOKEd from that user these system privileges:[ul][li]CREATE TABLE[/li][li]UNLIMITED TABLESPACE[/li][/ul]...which changes the datapump game entirely for OPS$ORACLE.


Once I regranted those privileges, I was able to successfully perform my DataPump exports.

Hava

for getting me headed straight, Karl.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”