Transport Tablespace Migration with Zero Downtime

For Oracle DBA database migration is common activity in day-to-day activity. Sometimes We need to migrate only one application data in the database. Though we can so such migration with expdp/impdp but sometimes we have a requirement of migrating data with low or no downtime. That's where Transport tablespace with zero downtime come into picture. 

In this blog we will show how we can perform the migration of tablespace with zero downtime. 

1) Perform the integrity check for tablespcae

execute dbms_tts.transport_set_check('CON_RPT_DATA,CON_RPT_INDEX',TRUE);

Make sure there is no violations. 

2) In order to start actual migration, we need to create auxillary database. For that we need to create auxialiary database parameter file. (Remember DBNAME can be same as source, but unique name for DB needs to be different)

cat auxinst.ora

db_name=TEST3BD

compatible=11.2.0.4.0

db_unique_name=PERCTEST

db_block_size=8192

db_files=200

sga_target=2G

shared_pool_size=150M

large_pool_size=10M

db_create_file_dest='/zfssa/backup2/TEST3BD/TTS'

3) Next step is to set the auxiliary instance parameter file in RMAN

Set the env for source

Connect to RMAN as 

rman target /

show all;

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/zfssa/backup2/TEST3BD/TTS’;

Show all 

After work is finished – change the configuration to default –

Note - CONFIGURE CHANNEL DEVICE TYPE DISK CLEAR

Set auxiliary instance parameter file to ‘/zfssa/backup2/TEST3BD/TTS/tmp/auxinst.ora’

Create a physical directory auxdest as 

mkdir –p /zfssa/backup2/TEST3BD/TTS/auxdest

4) Create Auxiliary instance

Run the command to create auxiliary instance for CON_RPT_DATA and CON_RPT_INDEX as below:

RMAN> transport tablespace CON_RPT_DATA, CON_RPT_INDEX tablespace destination ‘/zfssa/backup2/TEST3BD/TTS/auxdest’

Here are the steps RMAN will take to do so:

1. RMAN connects to the source-database

2. RMAN creates an AUXILIARY instance and an AUXILIARY database which constists of a SYSTEM- and an UNDO-tablespace and the tablespace(s) we want to export. For this RMAN uses a backupset of the source database.

3. RMAN recovers the AUXILIARY database to the desired point in time

4. RMAN uses export-datapump (expdp) to create a dumpfile-set:

this consists of:

- the the datafiles which represent the transportable-tablespace-set,

- the dumpfile of the export con taining the metadata of the tablespace-set

- a sql-script which can be used to import the metadata into the target system

5. RMAN removes the AUXILIARY instance from the host.

Notice the SCN number here , if you need it up to which time it take data from source.

Taking meta export dump for CON_RPT_DATA  and CON_RPT_INDEX 

Created the impdp script as below to run in target 

At last, it deletes the auxiliary instance

5)Import the metadata into target database.

Create a DBA DIRECTORY for import as below in the target where you need to run the import 

Set the env for Target 
Sqlplus '/as sysdba'

Create or replace dumpdir as ‘/zfssa/backup2/TEST3BD/TTS’  
Note – Path for directory is where your dumpfile was created from auxiliary instance

Now you are all set to run the import 

Set the env for target 

Before starting the import, make sure you set the even in the spfile  of target

*. event='10298 trace name context forever, level 32'

SQL> alter system set events '10298 trace name context forever,level 32';

System altered.

impdp system/<password> directory=datapumpdir dumpfile= 'dmpfile.dmp' transport_datafiles= /zfssa/backup2/TEST3BD/TTS/o1_mf_con_rpt__bkc9yvp9_.dbf, /zfssa/backup2/TEST3BD/TTS/o1_mf_con_rpt__bkcbpgsj_.dbf

6)After import is completed – Need to move datafiles to ASM.

Make the tablespace READ WRITE first 

Set the env for taget 

Sqlplus ‘/as sysdba’

Alter tablespace con_rpt_data read write;

Alter tablespace con_rpt_index read write;

select tablespace_name , status from dba_tablespaces where tablespace_name in ('CON_RPT_DATA','CON_RPT_INDEX');

Now even though we need to move the data file and rename the datafiles for tablespace 

Alter tablespace con_rpt_data offline

Alter tablespace con_rpt_index offline;

Select file_name from dba_data_files where tablespace_name in (‘CON_RPT_DATA’,’CON_RPT_INDEX’);

Set the env for target 

Rman target /

Copy datafile  ‘< path of the datafile from zfssa >’ to ‘+DATA’;     #####  FOR CON_RPT_DATA 

Copy datafile  ‘< path of the datafile from zfssa >’ to ‘+DATA’;     ##### FOR CON_RPT_INDEX

Now rename all the files , which you copied as below 

SQL> alter database rename file ‘</zfssa location of the file> ‘to ‘+DATA/TEST4BD/datafile/<actual_file_name>’

Cross check that files are in ASM 

Select file_name from dba_data_files where tablespace_name in (‘CON_RPT_DATA’,’CON_RPT_INDEX’);

Once all looks good – do the below 

In the target again, it is time to make the tablespace online and check, validate the objects

Alter tablespace con_rpt_data online

Alter tablespace con_rpt_index online;

Validate all is good in target 

You can learn more about oracle database administration: 

Pro Oracle GoldenGate for the DBA

Oracle Security Tools Installation and Configuration

Expert Oracle Exadata 

Oracle Database 12c Release 2 Real Application Clusters Handbook: Concepts, Administration, Tuning & Troubleshooting

Expert Oracle RAC 12c

 ==========================================================

Please check our other blogs for Migration