RE: Database Link in local server

If space is not an issue, and if a reasonable periodicity of frozen point in time is acceptable whence to source the data, the simplest thing is old school cloning of your DR database.  

Temporarily cancel recovery and shut down the DR database. Copy every bit of it. On the copy, startup rename, recover point in time. Restart DR and resume recovery only after the rename is complete on the clone.  

Whether you want two container databases or to use pluggable databases for schemaX and schemaY or to just do the transportable tablespace dance is an operational analysis of which way of making sausage is easiest for you.  

You can do this all “roll your own,” or investigate whether one or more of the various snapshot technologies might work for you.  

I wrote this up in tedious detail circa 1995 in “How to get the most from your standby recovery server.” Nothing other than space prevents you from saving multiple vintages of clone with different database names. Creating aggregations and summaries on frozen source tables can be incredibly effective in reducing the horsepower required to do analysis reporting and non-detail data feeds. Popular periodicity of the time was end of business yesterday (often the completion of the “generate receivables” process in accounting systems), and similarly end of month and end of quarter. If you run reports or data pulls that take more than a day and your desired cycle is daily, you can have multiple daily clones with more than one name. Storage is cheap. There does become a processes limit to servers if you’re trying to run many independent container databases simultaneously for reporting and data pulling.  

Good luck.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Cee Pee Sent: Thursday, April 29, 2021 6:30 PM
To: Oracle-L Freelists
Subject: Database Link in local server    

We have a standby DB in a DR server open for read only. A user wants to be able to pull the data in the server in some schemas (call them schemaA, schemaB) and want to write data in other schemas (say schemaX, schemaY) after manipulation.  

Without replication ($$), one thought that comes to mind is having another DB, say workDB, in the same server which then can connect via a local DB link to the standby DB. I am assuming performance will not be bad because the data comes from the same server. Of course there will be additional resource usage, esp IO and CPU that need to be considered.    

One important thing that comes to my mind is the statistics. Is there a way to make the 'workDB' see the statistics in the remote database (the standbyDB) when the workDB queries come up with execution plans? This may sound silly, but you never know unless you ask.   

19c, multi TB size warehouse.  

CP.

--
http://www.freelists.org/webpage/oracle-l