Oracle 21c Enhancements for TTS Export/Import

By -

 In Oracle version 21c, new features have been introduced for transportable tablespace (TTS) export/import. This article explores these capabilities in detail.

Improvement 1: Parallel Execution of TTS Export/Import

In Oracle 19c, TTS export/import operations could not be executed in parallel, as demonstrated by the following:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

ORA-39002: invalid operation

ORA-39047: Jobs of type TRANSPORTABLE cannot use multiple execution streams.

Oracle addressed this limitation in version 21c, enabling parallel execution for TTS export/import:

[oracle@oLinux7 ~]$ expdp directory=drm dumpfile=TTS_DUMP%U.dmp transport_tablespaces=tbs1,tbs2 transport_full_check=y parallel=2

Export: Release 21.0.0.0.0 – Production on Tue Oct 12 06:48:54 2021
Version 21.3.0.0.0

Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE

Master table “USEF”.”SYS_EXPORT_TRANSPORTABLE_01” successfully loaded/unloaded
******************************************************************************

Dump file set for USEF.SYS_EXPORT_TRANSPORTABLE_01 is:
  /home/oracle/TTS_DUMP01.dmp
  /home/oracle/TTS_DUMP02.dmp

******************************************************************************

Datafiles required for transportable tablespace TBS1:
  /oracle21c/base/oradata/DB21C/CD8420A04746726FE0530488200ADFC8/datafile/o1_mf_tbs1_jpbro11o_.dbf

Datafiles required for transportable tablespace TBS2:
  /oracle21c/base/oradata/DB21C/CD8420A04746726FE0530488200ADFC8/datafile/o1_mf_tbs2_jpbro6x9_.dbf

Job “USEF”.”SYS_EXPORT_TRANSPORTABLE_01” successfully completed at Tue Oct 12 06:49:17 2021 elapsed 0 00:00:16

Improvement 2: Resume Functionality for Interrupted TTS Export/Import

In Oracle 19c, if a TTS export/import operation was interrupted abnormally, the entire process had to be restarted as resuming was not supported:

[oracle@oLinux7 ~]$expdp  attach=SYS_EXPORT_TRANSPORTABLE_01

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

ORA-39002: invalid operation

ORA-39025: jobs of type TRANSPORTABLE are not restartable

This limitation has been resolved in Oracle 21c. Here’s how resuming works in the updated version:

Session 1: Starting the Export Job

[oracle@oLinux7 ~]$ expdp usef/a@192.168.56.4:1521/pdb1 directory=drm dumpfile=tbl_dmp_resume.dmp transport_tablespaces=tbs1,tbs2 transport_full_check=y reuse_dumpfiles=true

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production
Starting “USEF”.”SYS_EXPORT_TRANSPORTABLE_01”
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS

Session 2: Simulating an Interruption

SQL> alter system kill session '382,50546' immediate;

ORA-00031: session marked for kill

Resuming the Job

[oracle@oLinux7 ~]$ expdp attach=SYS_EXPORT_TRANSPORTABLE_01

Export: Release 21.0.0.0.0 – Production on Tue Oct 12 07:25:50 2021
Version 21.3.0.0.0

Export> status

Job: SYS_EXPORT_TRANSPORTABLE_01
  Operation: EXPORT
  Mode: TRANSPORTABLE
  State: IDLING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Job heartbeat: 4
  Dump File: /home/oracle/tbl_dmp_resume.dmp
    bytes written: 28,672

Export> START_JOB
Export> CONTINUE_CLIENT

Job SYS_EXPORT_TRANSPORTABLE_01 has been reopened at Tue Oct 12 07:25:56 2021
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table “USEF”.”SYS_EXPORT_TRANSPORTABLE_01” successfully loaded/unloaded
******************************************************************************

Dump file set for USEF.SYS_EXPORT_TRANSPORTABLE_01 is:
  /home/oracle/tbl_dmp_resume.dmp

Job “USEF”.”SYS_EXPORT_TRANSPORTABLE_01” successfully completed at Tue Oct 12 07:30:28 2021 elapsed 0

These enhancements make TTS export/import operations in Oracle 21c more efficient and user-friendly, particularly for handling large-scale database migrations.


Comments

Post a Comment

Popular posts from this blog

Oracle 23ai — Speed up IMPDP Using NOVALIDATE Constraints

By -
  When executing the   IMPDP   command, some operations do not support parallel execution. Creating constraints is one such operation. Oracle must validate the current data in the table before creating a constraint, which requires a full table scan for each validation. This process can be very time-consuming. Consider the following constraints: ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK1 CHECK (col3 > 50 ); ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK2 CHECK (col9 IN ( 5 , 15 )); We drop the  MYTB  table and recreate it using a dump file: SQL > drop table mytb; Table dropped. W -1 Processing object type TABLE_EXPORT / TABLE / TABLE_DATA W -1 . . imported "VAHID"."MYTB" 19 GB 337830912 rows in 83 seconds using external_table W -1 Processing object type TABLE_EXPORT / TABLE / CONSTRAINT / CONSTRAINT W -1 Completed 2 CONSTRAINT objects in 208 seconds As shown, creating the two constraints ta...
Read more

Staging Tables in Oracle 23ai

By -
  As you may know, using   staging tables   is a common practice in   Data Warehouse   environments, primarily during the data loading process into the   Warehouse Database . Typically, raw data received from   source databases   is first loaded into a temporary area, referred to as   staging tables . After cleansing inconsistent and invalid data and transforming it into a standard format, the cleaned data is stored in the final operational tables. In addition to initial data loading,   staging tables   are also used for updates and are often truncated before reuse. In   Oracle 23ai , a new minor feature has been introduced for this purpose, allowing users to create tables specifically designed as   staging tables . This is achieved by adding the   FOR STAGING   clause to the   CREATE TABLE   statement. Creating Staging Tables Using the   FOR STAGING   clause, staging tables can be created as ...
Read more

Oracle 23ai — Track Table and Partition Scan Access

By -
Image
  In Oracle 23ai, a new view named   [dba|all|user]_table_access_stats   has been introduced, enabling users to view the access history of tables and partitions. This view shows how many times each table or partition has been scanned in each instance and the last time a table was accessed. SQL > create table jadval(id number, name varchar2( 100 ), last_name varchar2( 100 )); Table created. SQL > insert into jadval values ( 1 , 'payan' , 'rafat' ); 1 row created. SQL > select TABLE_NAME, PARTITION_NAME, INSTANCE_ID, READ_COUNT, LAST_ACCESSED_TIME from user_table_access_stats; no rows selected SQL > select * from jadval; ID NAME LAST_NA ---------- ------- ------- 1 payan rafat SQL > select TABLE_NAME, PARTITION_NAME, INSTANCE_ID, READ_COUNT, LAST_ACCESSED_TIME from user_table_access_stats; TABLE_NAME PARTITION_NAME INSTANCE_ID READ_COUNT LAST_ACCESSED_TIME ---------- --------------- ----------- --------...
Read more