IMPORT - how many passes?
25/12/2017 11:47
I'm trying to import all the schemas* from our production database intoa new instance (which will eventualy beome the new production databaseonce I get it all to work).

On the new installation I create the database, create the tablespaces,create the users/schemas giving them quotas on the tablespaces theyneed. So far so good.

Next I want to run import - how many passes is typical for this? Iunderstand I can't do it in one pass, beause I'll be trying to grant rights to objects that don't exist yet, and apply foreign keys to tableswhere the parent data may not exist yet.

It seems like I need three passes, but I keep reading that a two-passimport is the usual way to go. Here's what I think I need: 1) rows=n, constraints=n, grants=n, indexes=n 2) rows=y, constraints=n, grants=y, indexes=n, ignore=y 3) rows=n, constraints=y, show=y, grants=n, indexes=n, ignore=y Step three should build a text file that can be used to create theconstraints and indexes. Is this the way to do it, or am I barking upthe wrong tree? Or alternatively, anybody got a standard set of parfiles for doing thissort of thing? details: Current production Oracle 8.1.6 on winNTNew database Oracle 9.2 on Win2kThe export file is from a full export, size just under 2 Gig.


Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
25/12/2017 11:47 - Hopefully, you don't have anything but the bare minimum owned by SYSTEM.

As for CTXSYS, if you are using ConText, then don't you want thatupdated as well once you pull over all of your ConText stuff? This is only true if you leave the development objects there. But youare refreshing everything from production right? So why not drop all ofthe development objects and then import. The import process guarantees this. It doesn't enable any constraintsuntil after everything has been imported. Views don't get created untilthe end. So the tables are there when it gets time for views to becreated. Want to see exactly the steps that import will take? Run importwith SHOW=Y and LOG=some_log_file. The contents of some_log_file willshow the DDL statements, in the order that import will take. The only time you should have a problem with this is if you leave tablesand constraints there. But why not drop the tables? You don't want thedata that is in there anyway because you will be refreshing it fromproduction via your dump file. In fact, you can even do the DROP USERcommand because the import file contains the DDL to create all theusers/grants/etc. When I do refreshes from production I do exactly this.

I don't want a developer leaving data that they have logicallycorrupted in the natural course of their development. The whole pointof refreshing from production is to get to a point that has nocorruptions like this so that you can do development on it withoutaffecting production.

HTH,Brian

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
25/12/2017 11:47 - Well, for one, I'm moving the data from 8.1.6 to 9.2. Wouldn't a fullimport overwrite the data in the system and ctxsys tables (and someothers)? Also, wouldn't I get a boatload of errors as foreign keys fail to findtheir parent records, grant statements fail to find the referencedobjects, views fail to find their underlying tables, etc.

I can see a full import working if it's done just the right order, but Idon't see how to guarantee it.

In any case, I did try a full import at first, but everytime I tried itthe import process would hang. Others here suggested importing just theapplication data, so that's the path that I'm on now. If there's aneasy way to just import the whole thing at once I'd be more than happyto use it.


Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
25/12/2017 11:47 - Why not do export with FULL=Y and import with FULL=Y? This is only onepass and gets everything.

HTH,Brian

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
25/12/2017 11:47 - There MAY be other considerations when refreshing prod into test with full.

Do you have snapshots in your prod database? Do you want them to use thesame source in test as they're using in prod? Do you have any code in prodthat updates/inserts/deletes anything in another database across a link? Ifso will you want that same code to fire in test? Do you have objects intest that you don't want to get rid of i.e. objects for the next release?How will you preserve those objects? And how would you like to handle anydifferences in users or roles between prod and test? Brian Peasland <dba@remove_spam.peasland.com> wrote in messagenews:3FA808A9.3E62376A@remove_spam.peasland.com...


Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
25/12/2017 11:47 - Several reasons, the principal one being that it won't work.


Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
25/12/2017 11:47 - Van, interesting points. Here's what's going on in my case: no.

no.

I don't think so, it's a brand new install of 9.2. There may be some9.2 objects that I don't want to have overwritten by their 8.1.6counterparts, but I don't know what they are...


Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
25/12/2017 11:47 - Can you elaborate *why* it won't work? I've done many, many refreshes ofa development or test database with production data and doing a FULLexport and a FULL import has always worked for me. Why won't it work foryou? Cheers,Brian

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

eDiscover
Helpforce eDiscover provides technical articles updated each dayHelpforce eDiscover RSS feed contains the latest technical articles in RSS
Click the logo to go back to the main page
Search eDiscover
  
Categories

Click an icon to go to that category

Helpforce eDiscover contains articles about Microsoft Windows Helpforce eDiscover contains articles about Apple products and MacOS Helpforce eDiscover contains articles about Linux and POSIX operating systems Helpforce eDiscover contains articles about Helpforce Helpforce has a large variety of technical information and articles for you to read Helpforce eDiscover contains articles about databases, MYSQL, SQL Server Oracle Helpforce eDiscover contains articles about Java, JVM and the JRE Helpforce eDiscover contains articles about the QNX operating system Helpforce eDiscover contains articles about Oracle Solaris and Open Solaris Helpforce eDiscover contains articles about RISC OS, Acorn and the BBC Micro Helpforce eDiscover contains articles about Amiga and AmigaOS

Type your comment into the box below