RE: expdp without password

From: <Joel.Patterson_at_crowley.com>
Date: Tue, 11 Oct 2011 07:50:23 -0400
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA17B51B9740_at_JAXMSG01.crowley.com>

An interpretation of the reason one cannot use SYS (or anyone connected as SYSDBA -- which becomes essentially SYS), is because a consistent export may not be obtained this way. I do not know the reason behind that, but it certainly explains your issue with the duplicate records. Also, it appears that any 'other' user with proper permissions is able to get a consistent export. So EXP_FULL_DATABASE, DBA etc will work for 'other' schemas, or indeed, depending what you wish to export, any permissions that allow that particular type of export -- except SYS / SYSDBA will create a consistent export. Therefore, yes SYSTEM will work as SYSTEM has the privileges... or any other user you create and give privileges. Personally, I use an admin account of my own creation for most tasks and not SYS or SYSTEM.

IMP_FULL_DATABASE is for importing, (verses EXP_FULL_DATABASE). If you have a consistent export, you will have a consistent import. (note in older version of 'exp' there was a 'consistent' parameter... :) which is no longer part of 'expdp').

Joel Patterson
Database Administrator
904 727-2546

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of sreejith s Sent: Tuesday, October 11, 2011 4:10 AM
To: aprilcsims_at_gmail.com
Cc: ORACLE-L
Subject: Re: expdp without password

Hi,
Came across this thread while searching through Oracle L archives.

I have referred this note which clearly says one should not use SYS / SYSDBA for datapump export and import. However , neither this note, nor documentation mention clearly which user can be used for a consistent export and import ( Perhaps, I missed it ? ) . Suppose I have to export multiple schemas using "SCHEMAS" parameter in datapump export, I cannot take a schema level export.In this case which user can I use ? SYSTEM ? or any user with IMP_FULL_DATABASE privilege ?

Also, I have noticed during our past import for a highly transactional database ( more than 1.5 TB) size, we usually get the following error during import and import terminates.

*ORA-39014: One or more workers have prematurely exited.

ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31671: Worker process DW01 had an unhandled exception.
ORA-12801: error signaled in parallel query server P000, instance

clvxd02q:FLYVXS2 (2)

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
ORA-06512: at "SYS.KUPW$WORKER", line 1423
ORA-06512: at line 2

*
On analysis, we found that there is a duplicate value for a primary key / unique index for one of the table which we are importing. We are using *FLASHBACK_TIME="to_TIMESTAMP(to_char(systimestamp,'DD-MM-YYYY HH24:MI:SS'), 'DD-MM-YYYY HH24:MI:SS')"* in our export parameter file. When we check the production database table, there is no duplicate .The workaround we did was EXCLUDE index & constraints first, delete the duplicates , then do import with INCLUDE=index & constraint.

 I am just wondering if the use of FLASHBACK_TIME along with SYSDBA could be the reason for not getting a consistent export.

Any help / suggestions on this ?

Best Regards,
Sreejith

On Tue, Sep 27, 2011 at 6:52 PM, April Sims <aprilcsims_at_gmail.com> wrote:

> Using sysdba for exports is NOT recommended since 10g. Below is a direct
> quote from MOS Note: *How to Connect AS SYSDBA when Using Export or Import
> [ID 277237.1]*
> "SYSDBA is used internally in the Oracle database and has specialized
> functions. Its behavior is not the same as for generalized users. For
> example, the SYS user cannot do a transaction level consistent read
> (read-only transaction). Queries by SYS will return changes made during the
> transaction even if SYS has set the transaction to be READ ONLY. Therefore
> export parameters like CONSISTENT, OBJECT_CONSISTENT, FLASHBACK_SCN, and
> FLASHBACK_TIME cannot be used.
> Starting with Oracle10g, the export shows a warning that the export is not
> consistent when the export is started with CONSISTENT=Y and connects to the
> database with the user SYS (or as SYSDBA):
> EXP-00105: parameter CONSISTENT is not supported for this user
>
> Note that Oracle automatically provides read consistency to a query so that
> all the data that the query sees comes from a single point in time
> (statement-level read consistency). For export this means that the export
> of
> table data is consistent. However, if a table contains nested tables, the
> outer table and each inner table are exported as separate transactions. And
> if a table is partitioned, each partition is exported as a separate
> transaction. If a nested table or a partitioned table was updated during
> the
> export, the data that is exported while connected as the SYS schema could
> be
> inconsistent.
>
> Typically, there is no need to invoke Export or Import as SYSDBA, except in
> the following situations:
> - at the request of Oracle technical support;
> - when exporting a transportable tablespace set with the old-style export
> utility (Oracle9i and Oracle8i);
> - when importing a transportable tablespace set with the old-style import
> utility (Oracle10g, Oracle9i, and Oracle8i)."
>
>
> On Tue, Sep 27, 2011 at 6:45 AM, <Joel.Patterson_at_crowley.com> wrote:
>
> >
> > I was wondering if you can export, (expdp), without a password, (or
> wallet
> > etc.) similar to using other utilities such as 'Sqlplus / as sysdba', or
> > 'rman target /'?
> >
> > I have a password script and can create a centralized password file
> > (unencrypted), but since I have been able to do most everything else
> without
> > a password, I would rather not implement it just because of this issue.
> >
> > I've googled (with mixed results -- seen an answer that did not work).
> Did
> > not find it (an example) in the manual.
> >
> > Best Regards,
> >
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> April C. Sims
> IOUG SELECT Journal Executive Editor
> http://aprilcsims.wordpress.com
> Twitter, LinkedIn
> Oracle Database 11g – Underground Advice for Database Administrators
> <
> http://www.amazon.com/Oracle-Database-Underground-Advice-Administrators/dp/1849680000/ref=sr_1_1?ie=UTF8&s=books&qid72289339&sr=8-1#noop>
> https://www.packtpub.com/oracle-11g-database-implementations-guide/book
> OCP 8i, 9i, 10g, 11g DBA
> Southern Utah University
> aprilcsims_at_gmail.com
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 11 2011 - 06:50:23 CDT