This forum is currently locked. You can't register or post questions at this time. (read more)

Oracle 11g impdp

All posts relating to Oracle database administration.

Moderator: Tim...

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

Oracle 11g impdp

Postby peterx » Tue Dec 17, 2013 7:09 am

hi Tim,

if i use:

Code: Select all

expdp system/123456@TESTDB full=Y directory=DATA_PUMP_DIR dumpfile=Full_Exp.dmp logfile=Full_Exp.log
impdp system/123456@TESTDB full=Y directory=DATA_PUMP_DIR dumpfile=Full_Exp.dmp logfile=Full_Exp.log


to do the export and import to the same db, i've got many warning about "already exists"

if only for self db backup or restore, is this a problem?

and the option full=Y is for all the db object. Then how to import all the object without warning?

thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle 11g impdp

Postby Tim... » Tue Dec 17, 2013 8:05 am

Hi.

Well, export and import is not really a proper backup. It's known as a logical backup. If you are doing this for the purpose of backing up, you really need to use RMAN.

If you want to remove the error messages, then you need to drop the relevant users before re-importing them. You will always get some clashes for the relevant SYS objects, which you can't drop. I typically exclude some users, like SYS, SYSTEM etc.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

Re: Oracle 11g impdp

Postby peterx » Tue Dec 17, 2013 8:10 am

hi Tim,

thanks for help.

Yes, just test the impdp with option:
impdp system/123456@TESTDB TABLE_EXISTS_ACTION=REPLACE full=Y directory=DATA_PUMP_DIR dumpfile=Full_Exp.dmp logfile=Full_Exp.log

it just crush my EM !!!

I use PIT recovery to fix it. I also think rman is stable method for the restore.
I just ask this question, because i walk around your doc and just think about how to use the "full=y".

You just give me the method, i think in DEV, UAT environment, we can :
use full db export and import with - exclude SYS, SYSTEM to restore.

very thanks.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle 11g impdp

Postby Tim... » Tue Dec 17, 2013 8:25 am

Hi.

Well, I use RMAN to backup Dev and UAT. If I need to transfer data between production and those environments I use an RMAN clone. It's *much* quicker than export/import.

Not sure what you want me to help with. The drop of the users or the exclude?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

Re: Oracle 11g impdp

Postby peterx » Tue Dec 17, 2013 8:40 am

hi Tim,

it is ok, you have gave me the answer already.

I also use RMAN backup as normal(in fact i never use expdp full. I use expdp - schema in some environment).
If create data guard or clone EBS, i will use RMAN clone also.

So..., i just think what is the use of expdp with "full=y".
you already tell me:
1. It is not a proper backup
2. If i really use impdp, and don't want "warning", just exclude sys and system.

So..., you let me more clear --> don't use expdp for backup and restore --> this is my conclusion(in fact, i like using RMAN too).

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle 11g impdp

Postby Tim... » Tue Dec 17, 2013 8:55 am

Hi.

Full export/import is handy for several things.

- Moving the whole database between platforms where conversion is not possible or difficult.
- Moving the whole database between endians.
- To upgrade a database without doing an upgrade. Export from old, import into new.
- Long term copy of data in a *more* portable format.
- Transportable database in Oracle 12c.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

Re: Oracle 11g impdp

Postby peterx » Tue Dec 17, 2013 11:56 am

hi Tim,

if
- Moving the whole database between platforms

also can exclude sys and system with option full=y ?

thanks.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle 11g impdp

Postby Tim... » Tue Dec 17, 2013 1:15 pm

Yes.

Remember, when you export FULL, you do not take all the contents of SYS and SYSTEM. If only takes certain pieces, like directory object definitions etc. You may not want to exclude them if this is a real transfer, or you may have to recreate some of the SYS owned objects that were present in the source server.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 0 guests

cron