Re: Full Export Problem

There may not be much point in exporting sys or system if you do not have non-oracle tables under those users. I think that the reasons for my saying this will become more clear as you continue.

First, by only exporting sys, system, and lists of tables, you will not export the create tablespace, create user, or create role commands (probabley others that I do not recall). For a recovery, you will need to keep this information scripted.

I really don't see what you would gain by exporting sys or system. This is assuming that you do not have non-oracle tables under those users. If you were doing this to recover the users and roles you could run into problems. Simply importing this export with ignore=y after creating the database could cause undesirable results (if it will even work).

For the sake of discussion, I am going to assume that the tables you are exporting from the data warehouse are owned by one user called DW. It doesn't matter if this is really many users or not. Below, you will find a list of objects that will not be exported which are owned by DW. There are probabley others as well.

1. synonyms
2. procedures, packages, functions
3. views
4. database links
5. snapshots
6. scheduled jobs

Since you did not say why you were going to use seperate export files, I will assume that you are running into a size (or will) limit on the export files. I would suggest a similar but slightly different approach.

1. Maintain a set of scripts that will create the database; run catalog, catproc, and other scripts; create the users; create the roles; create any public objects (anything not owned by DW that you need to keep ie public synonyms). The creation of users, roles, and tablespaces (other than system, rollback, users, tools, temp) could be dynamically generated.
2. Write a script that will drop any triggers that exist on tables owned by DW.
3. export DW w/ rows=n compress=n direct=y options
4. export groups of tables seperately as needed until all tables have been exported.

When you need to restore this database, you will run through the creation scripts that you have been maintaining (and generating dynamically). All that will be left to restore will be the DW schema. Then follow the next steps.

1. import the export files that was done with rows=n. This will create all of the tables (no rows), triggers, views, synonyms, db links, jobs, etc...
2. execute the script to drop all triggers (you don't need them slowing down or even stoping the import).
3. import the remaining export files w/ ignore=y parm. This will also recreate the triggers that were dropped.

If you have ref interigity in place between some of the tables, you might also want to generate a script which will dynamically generate the DDL needed to replace all foreign keys, unique, primary, and check constraints. You would also need to specifiy constraints=n on the export. You would apply this script after the imports have finished.

I am actualy doing this process to refresh dev and test databases with production data. Granted, all that I do is drop the schema on the target database, recreate the user, and import. I ran into size limitations on the export file. I have scripts already that will generate the DDL of the indexes and constraints. Indexes will be created unrecoverably and constraints are generated with syntax not to verify the existing data. I can email you the code that I have if you are interested.

stuco_at_mailcity.com wrote:
>
> I have a data warehouse that is pretty big (50 GB and growing) and I am
> currently planning my export jobs. This is how I wish to proceed:
>
> export 1 SYS,SYSTEM
> export 2 Table set 1
> export 3 Table set 2
> export 4 Table set 3
> . .
> . .
> . .
>
> I also wish to replace a 'Full" export with this strategy. My question is can
> a full export broken up into multiple smaller exports be used to fully
> recover from a database crash. In other words, can I totally rebuild a
> database without doing an export using ORACLE's FULL=Y parameter?
>
> Thank you so much for your help in this matter.
>
> Stuart Cowen
> Paladin Consulting, Inc.
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Mon Nov 02 1998 - 12:11:41 CST