Moving data between servers?

webzter

I've got a production Oracle 10g instance.

I've got a QA Oracle 10g instance.

Without needing to tie down a DBA, what is the best option I have for migrating data from production to QA? (We need to get a full sample in QA, it's non-sensitive data, client needs it ad hoc going forward).

I've looked a bit at the exp/imp commands. I've searched online and found some tools that don't seem to do a great job of scripting the data out as sql statements. I'm up for anything that I can do without having to create a db link.

My database is pretty small... less than 20 tables, maybe four sequences (guidance on resetting sequences would be great too if the tool doesn't handle it), and no need to save the data in the QA environment (assume a truncate before insert would be just fine)

Thoughts / suggestions? Thanks!

nerdydeeds

An article on resetting sequences.

There is also the DataPump utility. I used exp/imp years ago (my company had no DBAs) and it worked great. You can probably do everything you want to do in a one-line export command.

I can't see any reason why you'd need to export the data as SQL statements: just use the provided export/import tools and it will take no time at all.

ammoQ

How I do it (old-fashioned, but works)

On the production server in the operating system command line:

exp username/password file=mydump.dmp owner=username

Copy mydump.dmp to the QA system.

On the QA database, using SQL*PLUS or similar:

(first, you have to drop and recreate the target schema)

drop user username cascade;
create user username identified by password default tablespace users;
grant connect, resource to username;

On the operating system command line:

imp username/password file=mydump.dmp full=y

lpope187

Doesn't the full version of Toad have a Schema/Data compare utility? If it is comparable to RedGate DataCompare for SQL Server, it's probably what you're looking for albeit a bit pricey ($1,500 US).

One of the things I don't like about exp/imp the way ammoQ suggested is that it will drop and recreate the schema. That's good if QA/Production have the same schema, but doesn't really work well when you want to push schema changes as well.

I'm rusty with Oracle but for resetting sequences, just drop and recreate them.

ammoQ

@lpope187 said:

One of the things I don't like about exp/imp the way ammoQ suggested is that it will drop and recreate the schema. That's good if QA/Production have the same schema, but doesn't really work well when you want to push schema changes as well.

Technically, it's not really necessary, you could just drop all the tables to get the same result. Recreating the schema is probably easier, IMO.

BTW, Oracle's free SQL Developer can export data to INSERT statements, though this is better suited for ad-hoc activities than regular syncs.