Oracle: duplicate schema to another database

duplicationoraclerman

I want to copy an Oracle schema from a production database to a validation database.
Both databases are Oracle 11g R1 standard edition and need less interrupting time as I can (but I can do if it absolutely needed).

Both are installed on it's own proper server (Linux RedHat 5 Enterprise), instance names differ, tablespace and datafiles are already exist, both use archive logs.

What I want to do is select a whole schema on the production database and duplicate it to the validation database. The process must include all tables, indexes and all dependent schemas objects. There are some Lobs in some tables but no "exotical" objects (like types), no packages, no procedures, no functions, …
All objects reside in two well identified tablespaces (one for tables and datas, one for indexes).

I can't duplicate all instance. So RMan duplicate command is not an option.

Currently a process exist based on exp/imp utility but it's so slow, need database unavailability (in order to preserve data integrity) and error prone (mainly because full human's hand process).
I've the feeling that use RMan Oracle utility would be fine but I need some some external advises or comments to define the right way to do it. Datapumps utilities could be a valuable second choice (I'm currently evaluating).

Thanks for your advises
Thomas

Best Answer

Use expdp with the flashback_scn or flashback_time parameters, along with SCHEMAS= to export the schema at a consistent point in time.

This is the same as using CONSISTENT=Y in the legacy exp utility. Examples here.

If the data for the schema in question is in a tablespace of its own with no other objects from other schemas, you can use rman to do a tablespace point in time recovery.

Related Question