\nIn a previous post<\/a>, tribute to transportable tablespaces (TTS), I said that TTS is also used to move data quickly from operational database to a datawarehouse ODS. For sure, you don’t transport directly from the production database because TTS requires that the tablespace is read only. But you can transport from a snapshot standby. Both features (transportable tablespaces and Data Guard snapshot standby) are free in Enterprise Edition without option. Here is an exemple to show that it’s not difficult to automate
\n<\/p>\n
I have a configuration with the primary database “db1a”<\/p>\n
I’ve a few tables in the tablespace USERS and this is what I want to transport to ODS database:<\/p>\n With Data Guard it is easy to open temporarily the standby database. Just convert it to a snapshot standby with a simple command:<\/p>\n Here you can start to do some Extraction\/Load but better to reduce this window where the standby is not in sync. The only thing we will do is export the tablespace in the fastest way: TTS.<\/p>\n First, we put the USERS tablespace in read only:<\/p>\n and create a directory to export metadata:<\/p>\n Then export is easy<\/p>\n I’ve the metadata in \/tmp\/expdat.dmp and the data in \/u02\/oradata\/db1\/users01.dbf. I copy this datafile directly in his destination for the ODS database:<\/p>\n I’m ready to import it into my ODA database, but I can already re-sync the standby database because I extracted everything I wanted.<\/p>\n The duration depends on the time to flashback the changes (and we did no change here as we only exported) and the time to apply the redo stream generated since the convert to snapshot standby (which duration has been minimized to its minimum).<\/p>\n This whole process can be automated. We did that at several customers and it works well. No need to change anything unless you have new tablespaces.<\/p>\n Here is the import to the ODS database and I rename the USERS tablespace to ODS_USERS:<\/p>\n Everything is there. You have all your data in ODS_USERS. You can have other data\/code in this database. Only the ODS_USERS tablespace have to be dropped to be re-imported. You can have your staging tables here adn even permanent tables.<\/p>\n In 12.1 it is even easier because the multitenant architecture gives the possibility to transport the pluggable databases in one command, through file copy or database links. It is even faster because metadata are transported physically with the PDB SYSTEM tablespace. I said multitenant architecture here, and didn’t mention any option. Multitenant option is needed only if you want multiple PDBs managed by the same instance. But if you want the ODS database to be an exact copy of the operational database, then you don’t need any option to unplug\/plug.<\/p>\n In 12.1 you need to put the source in read only, so you still need a snapshto standby. And from my test, there’s no problem to convert it back to physical standby after a PDB has been unplugged. In next release, we may not need a standby because it has been announced that PDB can be cloned online.<\/p>\nDGMGRL> show configuration
\n
\nConfiguration - db1
\n
\n Protection Mode: MaxPerformance
\n Members:
\n db1a - Primary database
\n db1b - Physical standby database
\n
\nFast-Start Failover: DISABLED
\n
\nConfiguration Status:
\nSUCCESS (status updated 56 seconds ago)
\n
\nDGMGRL> show database db1b
\n
\nDatabase - db1b
\n
\n Role: PHYSICAL STANDBY
\n Intended State: APPLY-ON
\n Transport Lag: 0 seconds (computed 0 seconds ago)
\n Apply Lag: 0 seconds (computed 0 seconds ago)
\n Average Apply Rate: 0 Byte\/s
\n Real Time Query: ON
\n Instance(s):
\n db1
\n
\nDatabase Status:
\nSUCCESS<\/code><\/p>\nSQL> select segment_name,segment_type,tablespace_name from user_segments;
\n
\nSEGMENT_NAME SEGMENT_TY TABLESPACE
\n------------ ---------- ----------
\nDEPT TABLE USERS
\nEMP TABLE USERS
\nPK_DEPT INDEX USERS
\nPK_EMP INDEX USERS
\nSALGRADE TABLE USERS<\/code><\/p>\nSnapshot standby<\/h3>\n
\nDGMGRL> connect connect system\/oracle@\/\/db1b
\nDGMGRL> convert database db1b to snapshot standby;
\nConverting database \"db1b\" to a Snapshot Standby database, please wait...
\nDatabase \"db1b\" converted successfully<\/code><\/p>\nExport<\/h3>\n
SQL> connect system\/oracle@\/\/db1b
\nConnected.
\n
\nSQL> alter tablespace users read only;
\nTablespace altered.<\/code><\/p>\nSQL> create directory TMP_DIR as '\/tmp';
\nDirectory created.<\/code><\/p>\nSQL> host expdp system\/oracle@db1b transport_tablespaces=USERS directory=TMP_DIR
\nStarting \"SYSTEM\".\"SYS_EXPORT_TRANSPORTABLE_01\": system\/********@db1b transport_tablespaces=USERS directory=TMP_DIR
\n
\nProcessing object type TRANSPORTABLE_EXPORT\/TABLE_STATISTICS
\nProcessing object type TRANSPORTABLE_EXPORT\/STATISTICS\/MARKER
\nProcessing object type TRANSPORTABLE_EXPORT\/PLUGTS_BLK
\nProcessing object type TRANSPORTABLE_EXPORT\/POST_INSTANCE\/PLUGTS_BLK
\nProcessing object type TRANSPORTABLE_EXPORT\/TABLE
\nProcessing object type TRANSPORTABLE_EXPORT\/CONSTRAINT\/CONSTRAINT
\nMaster table \"SYSTEM\".\"SYS_EXPORT_TRANSPORTABLE_01\" successfully loaded\/unloaded
\n******************************************************************************
\nDump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
\n \/tmp\/expdat.dmp
\n******************************************************************************
\nDatafiles required for transportable tablespace USERS:
\n \/u02\/oradata\/db1\/users01.dbf
\nJob \"SYSTEM\".\"SYS_EXPORT_TRANSPORTABLE_01\" successfully completed at Wed Oct 19 21:03:36 2016 elapsed 0 00:00:52<\/code><\/p>\n[oracle@VM118 ~]$ cp \/u02\/oradata\/db1\/users01.dbf \/u02\/oradata\/ODS\/users01.dbf
\n<\/code>
\nThis is physical copy, which is the fastest data movement we can do.<\/p>\nRe-sync the physical standby<\/h3>\n
DGMGRL> convert database db1b to physical standby;
\nConverting database \"db1b\" to a Physical Standby database, please wait...
\nOperation requires shut down of instance \"db1\" on database \"db1b\"
\nShutting down instance \"db1\"...
\nConnected to \"db1B\"
\nDatabase closed.
\nDatabase dismounted.
\nORACLE instance shut down.
\nOperation requires start up of instance \"db1\" on database \"db1b\"
\nStarting instance \"db1\"...
\nORACLE instance started.
\nDatabase mounted.
\nConnected to \"db1B\"
\nContinuing to convert database \"db1b\" ...
\nDatabase \"db1b\" converted successfully
\nDGMGRL><\/code><\/p>\nImport<\/h3>\n
SQL> host impdp system\/oracle transport_datafiles=\/u02\/oradata\/db2B\/users02.dbf directory=TMP_DIR remap_tablespace=USERS:ODS_USERS
\nMaster table \"SYSTEM\".\"SYS_IMPORT_TRANSPORTABLE_01\" successfully loaded\/unloaded
\nStarting \"SYSTEM\".\"SYS_IMPORT_TRANSPORTABLE_01\": system\/******** transport_datafiles=\/u02\/oradata\/ODS\/users01.dbf directory=TMP_DIR remap_tablespace=USERS:ODS_USERS
\nProcessing object type TRANSPORTABLE_EXPORT\/PLUGTS_BLK
\nProcessing object type TRANSPORTABLE_EXPORT\/TABLE
\nProcessing object type TRANSPORTABLE_EXPORT\/CONSTRAINT\/CONSTRAINT
\nProcessing object type TRANSPORTABLE_EXPORT\/TABLE_STATISTICS
\nProcessing object type TRANSPORTABLE_EXPORT\/STATISTICS\/MARKER
\nProcessing object type TRANSPORTABLE_EXPORT\/POST_INSTANCE\/PLUGTS_BLK
\nJob \"SYSTEM\".\"SYS_IMPORT_TRANSPORTABLE_01\" completed with 3 error(s) at Wed Oct 19 21:06:18 2016 elapsed 0 00:00:10
\n<\/code><\/p>\n12c<\/em> pluggable databases<\/h3>\n
![\"CaptureBreakfastNov23\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureBreakfastNov231.png\")
<\/a>