Re: Block copy a table to another DB
- From: Jure Bratina <jure.bratina@xxxxxxxxx>
- To: rjoralist3@xxxxxxxxxxxxxxxxxxxxx
- Date: Fri, 9 Dec 2016 09:30:28 +0100
Hi Rich,
You mentioned that:
the table's blocks span nearly all of the tablespace's datafiles, and
that tablespace contains nearly all of the non-system data,
and
Scenarios like that are what I was (unsuccessfully) trying to imply would
be more difficult and/or time-consuming than a full DB restore
If I understand correctly, you'd like to restore only datafiles belonging
to tablespaces which contain the tables with purged LOBs. Any you'd like to
avoid restoring the full database. Have you considered restoring only the
desired tablespaces/datafiles (along with SYSTEM, UNDO and SYSAUX)?
Examples of such scenarios:
https://iarsov.com/oracle/performing-partial-restore-and-recovery/
https://www.pythian.com/blog/how-to-recover-a-subset-of-an-oracle-database/
or a similar functionality, rman duplicate database with SKIP TABLESPACE :
http://docs.oracle.com/database/121/BRADV/rcmdupad.htm#GUID-0C6616AE-FF8A-41E1-B0DE-EED457E020FC
That way you avoid putting the source tablespaces in read-only mode as
required by TTS, and you also avoid restoring the full database. And since
you said that "the table's blocks span nearly all of the tablespace's
datafiles", you'd have to have a similar amount of available storage
anyway, regardless of which technique you use to "copy" the data.
If you have a (physical) standby database you might also consider stopping
recovery on it, opening it read-write, perform the testing, flash it back
to a time before opening it read-write, and restart redo apply.
If that's a recurring task, you might employ other strategies to preserve
storage and speed up cloning, e.g.:
- Delphix
- use of read-write ACFS snapshots (only from 12c on for database files):
http://allthingsoracle.com/12c-database-backups-using-acfs-snapshots/
- if you are on multitenant, PDB Snapshot copy:
http://blog.dbi-services.com/pdb-snapshot-copy-for-continuous-integration-testing/
Regards,
Jure Bratina
On Thu, Nov 17, 2016 at 4:03 PM, Rich J <rjoralist3@xxxxxxxxxxxxxxxxxxxxx>
wrote:
On 2016/11/17 08:19, Tefft, Michael J wrote:
Transportable tablespace should do this – but you would need to make the
tablespace read-only for a bit, or get it from a backup.
Scenarios like that are what I was (unsuccessfully) trying to imply would
be more difficult and/or time-consuming than a full DB restore.
Thanks!
Rich
Other related posts:
