Solved

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

Posted on 2006-07-18
4
1,049 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

919 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now