Solved

how to do a full db migration using data pump (db running in noarchive mode)

Posted on 2006-07-18
4
1,057 Views
Last Modified: 2011-09-20
Hi everybody,

I have to servers (10g rel2) on two different locations. I want a certain instance, which is actually in production, to be fully copied onto an other server for testing/developement purposes.
I've never done such thing before (I've been a plain pl/sql developer so far).
The production instance is running in NOARCHIVELOG mode.

My first idea was to
- stop the PROD database,
- make a full dump using the new datapump export utilyty (it is said to be more efficient than the older export import utility) from linux command line (as I figured out, only some simple parameter file has to be assembled)
- restart the PROD server
- copy the files to the TEST server (I guess via FTP)
- somehow import it with the corresponding datapump util

My questions:
- It would be desireable not to stop the PROD server if it was not necessary. According to my understanding it will be necessary to stop it, since it's running in NOARCHIVE mode and you wont get a constistent dump while it's running.
Or maybe it could be possible to make a full dump (for a dev/test copy) while running?

- Also I saw in the documentation that there's an API (dbms_datapump or something like that), with which you can copy one database instance to an other database (without command line and file copy).
Do you think this could be as good as my original plan (or even better) regarding performance?

- Any comment, hint, trick, possible errors regarding such a migration are welcomed.

- Also it was beautiful if you could provide me with some sample code just to save me some time.

Thanks for your help
Gergo
0
Comment
Question by:bpgergo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 8

Assisted Solution

by:gvsbnarayana
gvsbnarayana earned 200 total points
ID: 17131617
Hi,
  To take an export(using data pump as well), you don't need the database running in archive log mode.
Your idea is correct all except for stopping the database.If you stop the prod server, then you will not be able to connect to the database and hence no data pump export.
You can close all the applications that are connecting to the database, if possible and initiate the datapump export. Otherwise, you can export the database with respect to a SCN (you can query using select current_scn from v$database) to have a consistent back up and supply this scn to the datapump script.
HTH
Regards,
Badri.
0
 

Author Comment

by:bpgergo
ID: 17136592
Badri, thanks.

So I can export up until a certain SCN and that will be consistent even if the database is running and in NOARCHIVELOG mode? And when I import it I get a constistent state corresponding to that certain SCN?
Is this goes only for command line version or the dbms_datapump package is similar?

And again: is it possible to to do the migration without mediate dump files, i.e. submiting a job in the source database what runs a pl/sql program which, using this dbms_datapump package connects to the target db through dblink and duplicates itself?

Also, which option do you think fits my needs better?

(These are all the questions I would like to clear before I close this thread and start to do this migration. Should additional quetions raise during the process, they go into a new thread.)

Thanks, again.
Best,
Gergo
0
 
LVL 18

Accepted Solution

by:
rbrooker earned 50 total points
ID: 17142739
Hi,

to create a test instance from a production instance, the easiest way that i have found ( and the least hassle ) is to do a backup / restore.
NOARCHIVELOG mode dictates that it would be a cold backup ( shut down, copy files ).

once the files have been copied to the test instance location, you can replace the test instance with the restored copy of production. ( reading your last post, the test instance is created and running )

I have always found this to be easier than an export / import.  and more reliable.

good luck.

:)
0
 
LVL 8

Expert Comment

by:gvsbnarayana
ID: 17147511
Hi,
  Creating tables on the other database using dblinks and taking a datapump export/import are two different mechanisms.
You can very easily create tables along with data on the second instance using dblinks but you will have problems related to indexes, constraints. If you take a datapump export, then you can get all these without any problem.
How big is your database? If your database is very big, then using a backup and restore will be easier..a little learning might be needed for you as you haven't done previously.
Yes... The data being export will be consistent with respect to that SCN. You may need to have bigger undo tablespace, if there is transactions going on the database while taking backup. If the instance experience any such problems, you can see them in the log file.
HTH
Regards,
Badri.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question