[12] Quick DB Sync between Clarity environments (Oracle Only)

Many a times, I need to Take Production copy and replicate to some other environment to make it Production like. My pain that Export to be done strictly in off peak hours and it's Long process, takes hours and the Exp dmp file is too big to transfer and then import also eating up lots of disk space. Reason : Slices and Datamart tables are quite huge ones and makes this thing crazy.

In my case it was OK not to have Slices & Datamart tables Synch'd. So I thought if we can Exp & Imp all tables except Slices & datamart ones. I researched a bit and came up with a approach that would give me just what I wanted. In my case, Export was done in 20 minutes. File size was 2.2 GB ( when zipped it was 240 MB ). It was easy to transfer 240 mb file to different server and next 38 minutes to import into target database. CMN_CAPTIONS_NLS was largest table with 3.3 million records.

I have documented the complete process that I did. You can download from here. I am not database expert so not sure if there is any better way of doing it. May be if someone has better approach or suggesitons, please leave comment for this Post.

* I see another benefit with this approach. Apart from the backups that DBA has scheduled and goes into tape, I can schedule Exp job on another server which can keeps DMP of last 15 days. Typically backup of all those 15 days will not take more than 50 GB of space. And when needed you can get any of those 15 versions in very quick time ( as compared to Tape to be brought from different physical location, restored on separate server which typically takes 2 days)