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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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.
