Posted in 2016

Migrating a Tablespace with CONVERT and Transportable tablespace

  1. First you have to set the tablespace to read only.
[oracle@hackbook ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 7 08:58:09 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.
 Connected to:
 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter session set container=rcat ;

Session altered.

SQL> alter tablespace corruption read only ;

Database altered.
  1. convert the tablespace to new platform
[oracle@hackbook ~]$ rman target sys/oracle@rcat

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Jul 7 09:00:48 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: CCLOUD1 (DBID=1191441626)

RMAN> convert tablespace "CORRUPTION" to platform 'Linux IA (64-bit)' format '/tmp/%U' ;

Starting conversion at source at 07-JUL-16
 using target database control file instead of recovery catalog
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: SID=46 device type=DISK
 channel ORA_DISK_1: starting datafile conversion
 input datafile file number=00010 name=/u01/app/oradata/CLOUDDB1/36994AC799462E62E0530F02000AFF86/datafile/o1_mf_corrupti_cqnms1yb_.dbf
 converted datafile=/tmp/data_D-CCLOUD1_I-1191441626_TS-CORRUPTION_FNO-10_0hra39o8
 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02
 Finished conversion at source at 07-JUL-16

RMAN>

Recovery Manager complete.
  1. export the tablespace, here I had some troubles because I didn’t know PDBs need to have their own directories, an answer from Franck Pachot in the community helped.
[oracle@hackbook log]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 7 09:24:25 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter session set container=rcat ;

Session altered.

SQL> create directory backup_dir as '/u01/backup' ;

Directory created.

[oracle@hackbook log]$ expdp dumpfile=save.dmp transport_tablespaces=corruption directory=backup_dir

Export: Release 12.1.0.2.0 - Production on Thu Jul 7 09:25:02 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Username: system/oracle@rcat

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/********@rcat dumpfile=save.dmp transport_tablespaces=corruption directory=backup_dir 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
 /u01/backup/save.dmp
******************************************************************************
Datafiles required for transportable tablespace CORRUPTION:
 /u01/app/oradata/CLOUDDB1/36994AC799462E62E0530F02000AFF86/datafile/o1_mf_corrupti_cqnms1yb_.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu Jul 7 09:28:33 2016 elapsed 0 00:03:20
  1. Transfer and import the datafile and dumpfile to your new destination database, the users in the tablespaces have to exist or use REMAP schema if they don’t you can remap them to existing schemas. As I do not have any different platform, I end this blog post only theoretically.
impdp system/oracle@rcatIA parfile=import.par

Contents of import.par:

dumpfile=save.dmp

directory=backup_dir

transport_datafiles='/new/path/o_corruption.dbf'

logfile=import.log

 

Advertisements

Author:

Database administrator who loves to work with Oracle software. (Sometimes not)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s