Re: expdp without password

From: sreejith s <sreejithsna_at_gmail.com>
Date: Tue, 11 Oct 2011 13:40:23 +0530
Message-ID: <CALPgMHsyuiDvzws5+FVVk5vUOcKq4-xE6CDL2mn=9nEVmpbj0Q_at_mail.gmail.com>



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
Received on Tue Oct 11 2011 - 03:10:23 CDT

Original text of this message