Oracle database cross platform migration (Different ENADIAN format)

Oracle database cross platform migration (Different ENADIAN format)

Why use Transportable Tablespaces (TTS)?

• "Oracle transportable tablespaces are the fastest way for moving large volumes of data between two Oracle databases."

• "Using transportable tablespaces, Oracle data files (containing table data, indexes, and almost every other Oracle database object) can be directly transported from one database to another. Furthermore, like import and export, transportable tablespaces provide a mechanism for transporting metadata in addition to transporting data."

"The transportable tablespace feature is useful in a number of scenarios, including:

• Exporting and importing partitions in data warehousing tables
• Publishing structured data on CDs
• Copying multiple read-only versions of a tablespace on multiple databases
• Archiving historical data
• Performing tablespace point-in-time-recovery (TSPITR)
• Migrating databases among RDBMS versions and OS platforms

Supported platforms

We can query the V$TRANSPORTABLE_PLATFORM view to see the platforms that are supported and to determine each platform's endian format (byte ordering).

SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;

PLATFORM_ID PLATFORM_NAME                    ENDIAN_FORMAT
----------- -------------------------------- --------------
          1 Solaris[tm] OE (32-bit)          Big
          2 Solaris[tm] OE (64-bit)          Big

Check that the tablespace will be self contained:

SQL> execute sys.dbms_tts.transport_set_check('TBS1,TBS2', true);
SQL> select * from sys.transport_set_violations;

• The tablespaces need to be in READ ONLY mode in order to successfully run a transport tablespace export:

SQL> ALTER TABLESPACE TBS1 READ ONLY;
SQL> ALTER TABLESPACE TBS2 READ ONLY;

• Using Datapump export:
  First create the directory object to be used for Datapump, like in:

CREATE OR REPLACE DIRECTORY dpump_dir AS '/tmp/subdir' ;
GRANT READ,WRITE ON DIRECTORY dpump_dir TO system;

Then initiate Datapump Export:

expdp system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = TBS1,TBS2

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

set lines 123

col PLATFORM_NAME for a40

SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

SQL> select name from v$tablespace;

set lines 123

col FILE_NAME for a60

select tablespace_name, file_name from dba_data_files where Tablespace_name in(‘ARCDATA01’,DATA01’,’DATA02’,’DATA03’,’INDEX01’,’INDEX02’);èother than system sysaux undo and temp

set serveroutput on

EXECUTE  DBMS_TTS.TRANSPORT_SET_CHECK(‘TEST’,TRUE);

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

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

=====Check All tablesapce and there  datafiles ========

select tablespace_name, file_name from dba_data_files where Tablespace_name  not in ('SYSTEM','UNDOTBS','SYSAUX');

TABLESPACE_NAME                FILE_NAME

------------------------------ ------------------------------------------------------------

USERS                          /opt/app/d1reg1d1/ora01/D0REGWEB/dbf/users_01.dbf

TOOLS                          /opt/app/d1reg1d1/ora01/D0REGWEB/dbf/tools_01.dbf

REGDAT01                       /opt/app/d1reg1d1/ora01/D0REGWEB/dbf/regdat01_1.dbf

Check INVALID OBJECT on source ==è

select count(*),OBJECT_TYPE,OWNER from DBA_INVALID_OBJECTS group by OBJECT_TYPE,OWNER;

select * from dba_directories;  ========è Check directory

#######Check TRANSPOTABLE##########

set serveroutput on

EXECUTE  DBMS_TTS.TRANSPORT_SET_CHECK('REGDAT01,REGDAT02,REGIDX01,REGIDX02,INTGDAT01,INTGDAT02,INTGIDX01,INTGIDX02',TRUE);

======* EXPDP tablespaces and users metadata *===============

cd /opt/app/d1spe1d1/D1SPOE_DATA_NEW

vi  exp_tablespace.sql

expdp parfile=exp_tablespace.sql

DUMPFILE=expdata_d1spoe_tablespaceC.dmp DIRECTORY=EXP_DRI_DUMP TRANSPORT_TABLESPACES = TABLESPACE_NAME1,TABLESPACE_NAME2,.... LOGFILE=expdata_d1spoe_tablespaceB.log

expdp DUMPFILE=expdata_d1spoe_role_users.dmp DIRECTORY=EXP_DRI_DUMP INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE content=METADATA_ONLY LOGFILE=expdata_D1SPOE_role_users.log

expdp parfile=exp_d1spoe_metadata.sql

vi exp_d1spoe_metadata.sql

content=METADATA_ONLY directory=EXP_DRI_DUMP dumpfile=expdata_d1spoe_metadatafull.dmp logfile=expdata_d1spoe_metadatafull.log schemas=DPVIEW,CYN_CPDP_ADMIN,……

EXCLUDE=USER,ROLE,ROLE_GRANT,PROFILE,TABLE,INDEX,CONSTRAINT

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

If below error encounter during EXPDP

ORA-39006: internal error

ORA-39213: Metadata processing is not available

Follow Below,

SQL> connect / as sysdba

SQL> @$ORACLE_HOME/javavm/install/initjvm.sql

SQL> @$ORACLE_HOME/xdk/admin/initxml.sql

SQL> @$ORACLE_HOME/rdbms/admin/catjava.sql

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

execute sys.dbms_metadata_util.load_stylesheets;

=====* Copy all datafiles pertaining to the tablespace to backup location, use below SQL for COPY commands *==========

Copy The datafile  which are in read only mode in backup partition

select 'cp '||file_name ||'  /opt/app/d1reg1d1/backup/expdp_dir/' from dba_data_files where TABLESPACE_NAME not in ('SYSTEM','UNDOTBS','SYSAUX');

“'CP'||FILE_NAME||'/OPT/APP/D1SPE1D1/EXP_DRI_DUMP'”

======* Online applications tablespaces *==================

alter tablespace TSPESPACE_NMAE1 read write;

alter tablespace TSPESPACE_NMAE1 read write;

==========ON TARGET=======

RMAN CONVERT DATABASE command is run, specifying a destination platform and how to name the output files.

RMAN> CONVERT DATAFILE

'/opt/app/q1c1d150/oraflsh01/migration_data/D1SPOE/intgidx01_01.dbf,

'/opt/app/q1c1d150/oraflsh01/migration_data/D1SPOE/intgidx02_01.dbf,

------

------

------

------

FROM PLATFORM 'Solaris[tm] OE (64-bit)'

PARALLELISM 4 DB_FILE_NAME_CONVERT '/opt/app/q1c1d150/oraflsh01/migration_data/D1SPOE/','/opt/app/q1c1d150/oradata01/D1SPOE/';

==================CREATE DIRECTORY and PROVIDE GRANT ==============

SQL> create or replace directory expdp_dir as '/opt/app/q1c1d150/oraflsh01/migration_data/D1SPOE';

SQL> grant read,write on directory expdp_dir to sys;

=================IMPORT USER SCHAMA DUMP===========================

impdp directory=expdp_dir dumpfile=expdata_D1SPOE_role_users.dmp logfile=impdata_D1SPOE_role_users_new.log

Need to  re-create all user  coz default tablespace is not present

CREATE USER "GSMUSER" IDENTIFIED BY VALUES '124066CFFBA2A110' DEFAULT TABLESPACE "TEST" TEMPORARY TABLESPACE "TEMP";

==============IMPORT TRANSPORTABLE TABLESPACE DUMP and datafile==

impdp  DIRECTORY=expdp_dir DUMPFILE=expdata_D1SPOE_tablespaceC.dmp logfile=imp_D1SPOE_tablespace_tr.log TRANSPORT_DATAFILES='/opt/app/q1c1d150/oradata01/D1SPOE/intgidx01_01.dbf','/opt/app/q1c1d150/oradata01/D1SPOE/intgidx02_01.dbf',……………;

==========================IMPORT Metadata============================

impdp directory=expdp_dir dumpfile=expdata_D1SPOE_metadatafull.dmp logfile=impD1SPOE_metadatafull.log ignore=y

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

====>Change all datafile from read only to read write mode on  source and target

====>Check object count of tablespace level  from source and target

select count(SEGMENT_NAME) ,OWNER from dba_segments where SEGMENT_TYPE like 'LOB%' and OWNER not in ('SYS','SYSTEM') and TABLESPACE_NAME not in ('SYSTEM','SYSAUX','UNDOTBS1') group by OWNER;

===è

select count(*),OWNER,SEGMENT_TYPE from dba_segments where OWNER not in ('SYS','SYSTEM',' ') group by  SEGMENT_TYPE,OWNER;

==è select count(*),OWNER,SEGMENT_TYPE from dba_segments where OWNER not in ('SYSTEM','SYS') group by OWNER,SEGMENT_TYPE; 

==è SQL> select count(*),SEGMENT_TYPE from dba_segments where OWNER not in ('SYSTEM','SYS') group by SEGMENT_TYPE;

  COUNT(*) SEGMENT_TYPE

---------- ------------------

       601 LOBINDEX

        12 NESTED TABLE

       601 LOBSEGMENT

       444 TABLE

       605 INDEX