Search This Blog

Thursday, August 20, 2015

Transportable tablespace and Cross-Platform Tablespace Conversion

From Oracle 10g Win64 to  Oracle 12c AIX64 

create user test identified by test;
grant connect,resource to test;
create tablespace test datafile 'G:\ORADATA\NETBANK\test01.dbf' size 128M ;
conn test/test


CREATE TABLE test_tab (
  id          NUMBER,
  description VARCHAR2(50),
  CONSTRAINT test_tab_pk PRIMARY KEY (id)
)
tablespace test;

INSERT /*+ APPEND */ INTO test_tab (id, description)
SELECT level,
       'Description for ' || level
FROM   dual
CONNECT BY level <= 10000;

COMMIT;
================================================================================================
EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TEST', incl_constraints => TRUE);
SELECT * FROM transport_set_violations;


set NLS_LANG=AMERICAN_AMERICA.UTF8
set ORACLE_SID=netdb
expdp "'/ as sysdba '"   directory=BACK_DIR dumpfile=test.dmp schemas=test CONTENT=metadata_only  EXCLUDE=STATISTICS


set ORACLE_SID=netdb
sql "alter tablespace test read only";
rman target /
CONVERT TABLESPACE "TEST"  TO PLATFORM 'AIX-Based Systems (64-bit)'   FORMAT='C:\AIX_DATAFILES\test01.dbf';

expdp "'/ as sysdba '" directory=BACK_DIR transport_tablespaces=test dumpfile=TEST_METADATA.dmp


sql "alter tablespace test read write";

DEST SERVER  ( AIX)
===========
create directory BACK_DIR as '/db12c/exports';
export NLS_LANG=AMERICAN_AMERICA.UTF8


create user test identified by test;grant connect,resource to test;

export ORACLE_SID=netdb
impdp "'/ as sysdba '"  directory=BACK_DIR dumpfile=TEST.DMP schemas=test

 mv TEST01.DBF /db12c/databases/H/.
impdp "'/ as sysdba '"  directory=BACK_DIR dumpfile=TEST_METADATA.DMP transport_datafiles='/db12c/databases/H/TEST01.DBF';


ALTER TABLESPACE test READ WRITE;

SELECT tablespace_name, plugged_in, status
FROM   dba_tablespaces
WHERE  tablespace_name = 'TEST';