技术博客 数据库、运维与工程实践笔记

How Convert full Database 10g Linux-x86-64bit to AIX-64bit different Endian Format

2024-06-05Linux/AIX / Oracle / RAC

In this post I’ll show you how to migrate full Oracle Database 10g on Linux (Little Endian) for AIX (Big Endian).I choose Oracle 10g, so this post remains in force for higher versions.To use transportable database feature, we need know which to recreate an entire database from one platform on another the platforms have the same endian order.I have following scenario. Source Host: libanoPlatform: Linux OEL 5.5 x64Endian Format: LittleDatabase Release: 10.2.0.5Filesystem: Ext3 Destination Host: butaoPlatform: AIX 6.1 64bitEndian Format:BigDatabase Release: 10.2.0.5Filesystem: jfs2 We can check if is compatible (same endian format) executing query below on source host.

12345678910111213141516 SELECT*FROM V$DB_TRANSPORTABLE_PLATFORM;PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT———– —————————————- ————–7 Microsoft Windows IA (32-bit) Little10 Linux IA (32-bit) Little5 HP Tru64 UNIX Little11 Linux IA (64-bit) Little15 HPOpenVMS Little8 Microsoft Windows IA (64-bit) Little13 Linux x86 64-bit Little12 Microsoft Windows x86 64-bit Little17 Solaris Operating System (x86) Little20 Solaris Operating System (x86-64) Little19 HP IAOpenVMS Little 11rowsselected.

AIX Platform don’t appear here because source platform is Little Endian. So we can discard the use of transportable database feature.But we still have hope. We can use Cross-Platform Transportable Tablespace.CONVERT does not perform in-place conversion of datafiles. It produces output files in the correct format for use on the destination platform, but does not alter the contents of the source datafiles.If you can convert datafile on source platform you use the CONVERT TABLESPACE command, but if you can convert datafile on destination platform you must use the CONVERT DATAFILE command.The most important thing in this migration, you know the restrictions.Restrictions on CONVERT TABLESPACE and CONVERT DATAFILENote the following restrictions on CONVERT TABLESPACE and CONVERT DATAFILE:

  • Both source and destination databases must be running with the COMPATIBLE initialization parameter set to 10.0 or higher.
  • Not all combinations of source and destination platforms are supported. To determine whether your source and destination platforms are supported, query V$TRANSPORTABLE_PLATFORM. If both the source and destination platforms are listed in this view, then CONVERT can be used to prepare datafiles from one platform for use on the other.
  • The Tablespace must be made read-write at least once in release 10g before it can be transported to another platform using CONVERT. Hence, any read-only Tablespace (or previously transported Tablespace) from a previous release must first be made read-write before they can be transported to another platform.
  • RMAN does not process user datatypes that require endian conversions.
  • Prior to release 10g, CLOBs were created with a variable-width character set and stored in an endian-dependent format. The CONVERT command does not perform conversions on these CLOBs. Instead, RMAN captures the endian format of each LOB column and propagates it to the target database. Subsequent reads of this data by the SQL layer interpret the data correctly based upon either endian format, and write the data out in an endian-independent format if the Tablespace is writeable. CLOBs created in Oracle Database Release 10g are stored in character set AL16UTF16, which is platform-independent.

Kick-off

Verify Platform Support…

Source Host/Databasev$database shows what platform the database is running on.v$transportable_platform shows all platforms supported by Oracle for cross-platform tablespace transport.

12345 SETLINESIZE 100COL PLATFORM_NAMEFORA40SELECTA.platform_id, A.platform_name, B.endian_formatFROM v$databaseA, v$transportable_platform BWHERE B.platform_id (+) = A.platform_id;

Output of Source Platform / Host: libano:

123 PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT———– —————————————- ————–13 Linux x86 64-bit Little

Output Destination Platform / Host : butao:

123 PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT———– —————————————- ————–6 AIX-Based Systems (64-bit) Big

In this test the database has only one user to be migrated. You can use the same procedure to migrate all users of the database (except SYS-Users) at the same time.Set of Tablespace to be transported must be self-contained. Objects in the Tablespace set cannot reference or depend on objects outside the set (i.e. all segments from user, must be migrated same time). Self-containment problems will cause metadata export to fail.##### Self-Containments Then let’s Check Self-ContainmentLet us identify which is the Tablespace are necessary for our migration.In this example I’ll migrate the user/schema named SOE.First step is identifying which Tablespace will be transportable:

12345678910111213 SELECT TABLESPACE_NAME FROMDBA_SEGMENTS WHEREOWNER='SOE' GROUPBYTABLESPACE_NAME ORDERBY1; TABLESPACE_NAME —————————— SOE SOE_FAST_PART SOE_INDX SOE_LOB SOE_SLOW_PART

Let’s take a look which type of segment we have:

1234567891011121314151617 | SELECT SEGMENT_TYPE,TABLESPACE_NAMEFROMDBA_SEGMENTSWHEREOWNER='SOE'GROUPBYSEGMENT_TYPE,TABLESPACE_NAMEORDERBY2; SEGMENT_TYPE TABLESPACE_NAME—————— ——————————TABLE SOETABLEPARTITION SOETABLEPARTITION SOE_FAST_PARTINDEX SOE_INDXINDEX SOE_LOBLOBINDEX SOE_LOBLOBSEGMENT SOE_LOBTABLE SOE_LOBTABLEPARTITION SOE_SLOW_PART

:—
:—

The statement below can be used to determine whether Tablespace are self-contained, with referential integrity constraints taken into consideration (indicated by TRUE).

123456 BEGINSYS.dbms_tts.transport_set_check('SOE,SOE_FAST_PART,SOE_INDX,SOE_LOB,SOE_SLOW_PART', incl_constraints=>TRUE, full_check=>FALSE);END;/PL/SQLproceduresuccessfully completed.

After invoking this PL/SQL package, you can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the set of Tablespace is self-contained, this view is empty.

12 SELECT*FROMSYS.transport_set_violations;norowsselected

Good no problem with self-containedRestriction with Data TypesCheck Tablespace set for columns with problematic data types:Data pump can’t transport XMLTypes (i.e. expdp utility)Original export can’t transport BINARY_FLOAT or BINARY_DOUBLE (i.e. export utility)Manual conversion could be required for RAW, LONG RAW, BFILE, ANYTYPE, user-defined data types Lets verify if we have problem with data types.

123456789101112131415161718 SELECT tc.data_type,COUNT(*)FROM dba_tables t, dba_tab_columns tcWHERE t.owner ='SOE'AND tc.owner = t.ownerAND tc.table_name = t.table_nameGROUPBYtc.data_typeORDERBY1;DATA_TYPE COUNT(*)—————————————- ———-BLOB 3CHAR 1DATE 32INTERVALYEAR(2)TOMONTH 1NUMBER 88NVARCHAR2 2ROWID 1TIMESTAMP(6)WITHLOCALTIMEZONE 1VARCHAR2 122

Good … no problem with Data Types.Let’s check if any of the Tablespace have some Data Type problematic.

123456 SELECTxt.owner,at.table_nameFROM dba_xml_tables xt, all_all_tablesatWHERE at.owner = xt.ownerAND at.table_name = xt.table_nameAND at.tablespace_nameIN('SOE','SOE_FAST_PART','SOE_INDX','SOE_LOB','SOE_SLOW_PART')norowsselected

It’s good .. no data type problematicChecking External ObjectsUsing Transportable Database or Transportable Tablespace its necessary check if Database have External tables, directory or BFILES.This is mandatory because these objects will not migrate automatically.To complete this step will use package DBMS_TDB.CHECK_EXTERNAL if the database has external tables, directories, or BFILEs, return TRUE. Otherwise, return FALSE.

123456789101112131415 SETSERVEROUTPUTONDECLAREexternal BOOLEAN;BEGINexternal := DBMS_TDB.CHECK_EXTERNAL;END;/The following external tables existinthedatabase:SOE.TBE_ACTLOG_PARTNER, SOE.TBE_ASSOCIATIONS_PARTNER, SOE.TBE_AUDITOCC_PARTNER,SOE.TBE_CLIENT_SCHEDULES_PARTNER, SOE.TBE_DB_PARTNER, SOE.TBE_DRIVES_PARTNER,SOE.TBE_DRMEDIA_PARTNER, SOE.TBE_EVENTS_PARTNER, SOE.TBE_LIBRARIES_PARTNER,SOE.TBE_LIBVOLUMES_PARTNER, SOE.TBE_LOG_PARTNER The following directories existinthedatabase:SYS.EXT_PARTNER_DIR, SYS.DIR_EXP_CONV, SYS.DATA_PUMP_DIR, SYS.ORACLE_OCM_CONFIG_DIR

We have objects, and will need migrate manually.### Start Migration In this migration I will use the Data Pump (expdp and impdp).Typically, I see the guys using the following steps to migrate.

  1. Export Schema
  2. Exports the Tablespace
  3. Converts Tablespace with Rman
  4. Move the files to the destination host.
  5. Creates users in the destination database and provides the necessary grants.
  6. Import tables attaching Tablespace in destination database
  7. Import other objects such as procedures, packages, etc…

    8. But this migration I will use a different way.

  8. Export Schema
  9. Export External Tables
  10. Export Tablespace
  11. Convert Tablespace with Rman
  12. Move the files to destination host.
  13. Import Schema
  14. Import External Tables
  15. Import Tables attaching tablespace in destination database.

    16. You can ask: Why you import the schema first? Why not attach the Tablespace with tables and after import schema?Simple answer, it’s more easy to me recompile objects invalidated of than recreate all users manually.Remember, to attach Tablespace in database user must be created.I choose this way, because it saves time.A Data Pump schema import will recreate the user and execute all of the associated security privileges (grants, user password history), whereas the old imp utility required the DBA to create the user ID before importing.Then when you migrate only one user it’s easy recreate user and executes associated security privileges, but when you migrate entire database we can have a many users. In my last migration the database had more than 3,000 users with different passwords and privileges. I know which I can edit the dump file of the export and recreate user with same password and grant, but it’s a hard work.Using my strategy of migration when you import schema all objects will be invalidated, because no tables in schema and import will finish with many errors.But don’t worry, it’s easily solved when attach Tablespace in database and recompiling all objects invalidated.I don’t need worry anymore with map users and grant. The impdp do this automatically.##### Exporting Source Database To use Data pump will need a Logical Directory in database.Them my stage area to dump is ‘/u01/app/oracle/flashrecovery_area/DBCONV/export’

12345 SQL>CREATEDIRECTORY DIR_EXP_CONVAS'/u01/app/oracle/flashrecovery_area/DBCONV/export';Directory created.SQL>GRANTREAD,WRITEONDIRECTORY DIR_EXP_CONVTOSYSTEM;Grantsucceeded.SQL>

We need change default Tablespace to another Tablespace that which already exists in destination database.This is necessary because import (impdp) will fail on creation of user if default Tablespace not exists.In my case I choose USERS.First I need identify and create command to configure correct default tablespace to user on destination database.

1234567891011 SELECT'ALTER USER ' USERNAME ' DEFAULT TABLESPACE ' DEFAULT_TABLESPACE ';'CMD FROMDBA_USERS WHEREUSERNAMEIN('SOE'); CMD ————————————————– ALTERUSERSOEDEFAULTTABLESPACE SOE;

Now I can change default tablespace to Tablespace USERS before export.

12 SQL>ALTERUSERSOEDEFAULTTABLESPACE USERS;Useraltered.

Before export database or convert datafiles, we need put all Tablespace from user migrate in READ-ONLY, this will prevent DML in my schema during process migration.This step is mandatory.

12345 SELECT 'ALTER TABLESPACE "' TABLESPACE_NAME '" READ ONLY;'as"CMD READ ONLY"FROMDBA_SEGMENTSWHEREOWNER='SOE'GROUPBYTABLESPACE_NAMEORDERBY1
1234567891011121314151617181920212223242526272829 SQL>SELECT 'ALTER TABLESPACE "' TABLESPACE_NAME '" READ ONLY;'as"CMD READ ONLY"FROMDBA_SEGMENTSWHEREOWNER='SOE'GROUPBYTABLESPACE_NAMEORDERBY1 2 3 4 56 / CMDREADONLY————————————————————ALTERTABLESPACE"SOE"READONLY;ALTERTABLESPACE"SOE_FAST_PART"READONLY;ALTERTABLESPACE"SOE_INDX"READONLY;ALTERTABLESPACE"SOE_LOB"READONLY;ALTERTABLESPACE"SOE_SLOW_PART"READONLY; SQL>ALTERTABLESPACE"SOE"READONLY;Tablespace altered. SQL>ALTERTABLESPACE"SOE_FAST_PART"READONLY;Tablespace altered. SQL>ALTERTABLESPACE"SOE_INDX"READONLY;Tablespace altered. SQL>ALTERTABLESPACE"SOE_LOB"READONLY;Tablespace altered. SQL>ALTERTABLESPACE"SOE_SLOW_PART"READONLY;Tablespace altered.

Let’s identify how many objects are in my schema, this will help us at end of migration. We need be sure if all object were migrated.

1234567891011121314151617181920212223242526272829303132 SELECTCOUNT(OBJECT_NAME)FROMDBA_OBJECTSWHEREOWNER='SOE'; COUNT(OBJECT_NAME)——————246 SELECTCOUNT(OBJECT_TYPE),OBJECT_TYPEFROMDBA_OBJECTSWHEREOWNER='SOE'GROUPBYOBJECT_TYPEORDERBYOBJECT_TYPE; COUNT(OBJECT_TYPE) OBJECT_TYPE—————— ——————-4FUNCTION30INDEX3 JAVA CLASS1 JAVA SOURCE3 LOB1 PACKAGE1 PACKAGE BODY2PROCEDURE3SEQUENCE34TABLE160TABLEPARTITION1TRIGGER1 TYPE2VIEW 14rowsselected.

The first export I will export entire schema with users, grants and objects except tables and indexes.Excluding/Including an object, will also exclude/include it’s dependent objects, so constraints from tables not be export at this time.

123456789101112131415161718192021222324252627282930313233343536373839 [oracle@libano ~]$exportNLS_DATE_FORMAT="DD-MM-YYYY HH24:MI:SS"[oracle@libano ~]$ expdp DIRECTORY=DIR_EXP_CONV DUMPFILE=exp_schema_soe.dmp SCHEMAS=SOE EXCLUDE=TABLE,INDEXES Export: Release 10.2.0.5.0 – 64bit Production on Monday, 17 January, 2011 17:33:57 Copyright (c) 2003, 2007, Oracle. All rights reserved. Username: systemPassword: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting"SYSTEM"."SYS_EXPORT_SCHEMA_01": system/****** DIRECTORY=DIR_EXP_CONV DUMPFILE=exp_schema_soe.dmp SCHEMAS=SOE EXCLUDE=TABLE,INDEXESEstimateinprogress using BLOCKS method…Processing objecttypeSCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 0 KBProcessing objecttypeSCHEMA_EXPORT/USERProcessing objecttypeSCHEMA_EXPORT/SYSTEM_GRANTProcessing objecttypeSCHEMA_EXPORT/ROLE_GRANTProcessing objecttypeSCHEMA_EXPORT/DEFAULT_ROLEProcessing objecttypeSCHEMA_EXPORT/TABLESPACE_QUOTAProcessing objecttypeSCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing objecttypeSCHEMA_EXPORT/TYPE/TYPE_SPECProcessing objecttypeSCHEMA_EXPORT/SEQUENCE/SEQUENCEProcessing objecttypeSCHEMA_EXPORT/PACKAGE/PACKAGE_SPECProcessing objecttypeSCHEMA_EXPORT/FUNCTION/FUNCTIONProcessing objecttypeSCHEMA_EXPORT/PROCEDURE/PROCEDUREProcessing objecttypeSCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPECProcessing objecttypeSCHEMA_EXPORT/FUNCTION/ALTER_FUNCTIONProcessing objecttypeSCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDUREProcessing objecttypeSCHEMA_EXPORT/VIEW/VIEWProcessing objecttypeSCHEMA_EXPORT/PACKAGE/PACKAGE_BODYProcessing objecttypeSCHEMA_EXPORT/JAVA_SOURCE/JAVA_SOURCEProcessing objecttypeSCHEMA_EXPORT/JAVA_CLASS/JAVA_CLASSMaster table"SYSTEM"."SYS_EXPORT_SCHEMA_01"successfully loaded/unloaded****************************************************************************DumpfilesetforSYSTEM.SYS_EXPORT_SCHEMA_01 is:/u01/app/oracle/flashrecovery_area/DBCONV/export/exp_schema_soe.dmpJob"SYSTEM"."SYS_EXPORT_SCHEMA_01"successfully completed at 17:34:27

As exclude tables from export schema, I will need export External Tables separately.Identifying External Tables ….

123 SELECTOWNER '.' TABLE_NAME ','CMDFROMDBA_EXTERNAL_TABLESWHEREOWNER ='SOE'
1234567891011121314151617181920212223 CMD————————————————————–SOE.TBE_ACTLOG_PARTNER,SOE.TBE_ASSOCIATIONS_PARTNER,SOE.TBE_AUDITOCC_PARTNER,SOE.TBE_CLIENT_SCHEDULES_PARTNER,SOE.TBE_DB_PARTNER,SOE.TBE_DRIVES_PARTNER,SOE.TBE_DRMEDIA_PARTNER,SOE.TBE_EVENTS_PARTNER,SOE.TBE_LIBRARIES_PARTNER,SOE.TBE_LIBVOLUMES_PARTNER,SOE.TBE_LOG_PARTNER,SOE.TBE_MEDIA_PARTNER,SOE.TBE_OCCUPANCY_PARTNER,SOE.TBE_PATHS_PARTNER,SOE.TBE_QTD_SCRATCH_PARTNER,SOE.TBE_STGPOOLS_PARTNER,SOE.TBE_SUMMARY_PARTNER,SOE.TBE_VOLHISTORY_PARTNER,SOE.TBE_VOLUMES_PARTNER 19rowsselected.

And exporting ….

1234567891011121314151617181920212223242526272829303132 [oracle@libano ~]$ cat exp_exttalbes.parDUMPFILE=exp_exttables_soe.dmpLOGFILE=exp_exttables_soe.logTABLES=SOE.TBE_ACTLOG_PARTNER,SOE.TBE_ASSOCIATIONS_PARTNER,SOE.TBE_AUDITOCC_PARTNER,SOE.TBE_CLIENT_SCHEDULES_PARTNER,SOE.TBE_DB_PARTNER,SOE.TBE_DRIVES_PARTNER,SOE.TBE_DRMEDIA_PARTNER,SOE.TBE_EVENTS_PARTNER,SOE.TBE_LIBRARIES_PARTNER,SOE.TBE_LIBVOLUMES_PARTNER,SOE.TBE_LOG_PARTNER,SOE.TBE_MEDIA_PARTNER,SOE.TBE_OCCUPANCY_PARTNER,SOE.TBE_PATHS_PARTNER,SOE.TBE_QTD_SCRATCH_PARTNER,SOE.TBE_STGPOOLS_PARTNER,SOE.TBE_SUMMARY_PARTNER,SOE.TBE_VOLHISTORY_PARTNER,SOE.TBE_VOLUMES_PARTNER [oracle@libano ~]$ expdp parfile=exp_exttalbes.par Export: Release 10.2.0.5.0 – 64bit ProductiononSaturday, 22 January, 2011 18:07:22 Copyright (c) 2003, 2007, Oracle. Allrights reserved. Username: systemPassword: Connectedto: OracleDatabase10g Enterprise Edition Release 10.2.0.5.0 – 64bit ProductionWiththe Partitioning, OLAP, Data MiningandRealApplication Testing optionsStarting"SYSTEM"."SYS_EXPORT_TABLE_01": system/****** parfile=exp_exttalbes.parEstimateinprogress using BLOCKS method…Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 0 KBProcessing object type TABLE_EXPORT/TABLE/TABLEMastertable"SYSTEM"."SYS_EXPORT_TABLE_01"successfully loaded/unloaded****************************************************************************Dump filesetforSYSTEM.SYS_EXPORT_TABLE_01is:/u01/app/oracle/flashrecovery_area/DBCONV/export/exp_exttables_soe.dmpJob"SYSTEM"."SYS_EXPORT_TABLE_01"successfully completedat18:07:30

All Oracle Directories must be created manually ….The script above will generate command to recreate Oracle directories, except defaults Oracle Directory.

1234567891011 setlinesize 100SELECT'CREATE DIRECTORY ' DIRECTORY_NAME ' AS ''' DIRECTORY_PATH ''';'CMDFROMDBA_DIRECTORIESWHEREDIRECTORY_NAMENOTIN('ORACLE_OCM_CONFIG_DIR','DATA_PUMP_DIR')/ CMD—————————————————————————————————-CREATEDIRECTORY DIR_TXT_FILESAS'/u01/app/external_table/partner';CREATEDIRECTORY EXT_PARTNER_DIRAS'/u01/app/external_table/partner';CREATEDIRECTORY DIR_EXP_CONVAS'/u01/app/oracle/flashrecovery_area/DBCONV/export';

No sys-object privileges are granted during the import (using imp or impdp), so we must manually grant access.This script will create the commands needed to grant access due.

12345678910111213 SELECTgrantor, grantee, table_schema, table_name, privilegeFROMall_tab_privsWHEREgrantee ='SOE' GRANTOR GRANTEE TABLE_SCHEMA TABLE_NAME PRIVILEGE——————– ——————– ——————– ——————– ———-SYS SOE SYS DBMS_LOCK EXECUTESYS SOE SYS EXT_PARTNER_DIR WRITESYS SOE SYS EXT_PARTNER_DIR READSYS SOE SYS EXT_PARTNER_DIR EXECUTESYS SOE SYS DIR_TXT_FILES WRITESYS SOE SYS DIR_TXT_FILES READSYS SOE SYS DIR_TXT_FILES EXECUTE

This script will generate command to Oracle Directory

12345678910111213 SELECT'GRANT ' tp.privilege ' ON DIRECTORY ' tp.table_name ' TO ' tp.grantee ';'CMDFROMdba_tab_privs tp,dba_objects DOWHEREdo.object_name = tp.table_nameANDobject_type ='DIRECTORY'ANDgrantee ='SOE'/
12345678 CMD———————————————————————–GRANTEXECUTEONDIRECTORY EXT_PARTNER_DIRTOSOE;GRANTREADONDIRECTORY EXT_PARTNER_DIRTOSOE;GRANTWRITEONDIRECTORY EXT_PARTNER_DIRTOSOE;GRANTEXECUTEONDIRECTORY DIR_TXT_FILESTOSOE;GRANTREADONDIRECTORY DIR_TXT_FILESTOSOE;GRANTWRITEONDIRECTORY DIR_TXT_FILESTOSOE;

And this script will generate command another sys-objects.

12345678910111213141516171819 SELECT'GRANT ' tp.privilege ' ON ' tp.owner '.' tp.table_name ' TO ' tp.grantee ';'CMDFROMDBA_TAB_PRIVS tp,dba_objects DOWHEREdo.object_name = tp.table_nameANDobject_type <>'DIRECTORY'ANDgrantee ='SOE'GROUPBYtp.privilege,tp.owner,tp.table_name,tp.grantee/
1234 CMD———————————————————–GRANTEXECUTEONCTXSYS.CTX_DDLTOSOE;GRANTEXECUTEONSYS.DBMS_LOCKTOSOE;

Let’s export Objects from Transportable Tablespace.

12345678910111213141516171819202122232425262728293031 [oracle@libano ~]$ expdp DIRECTORY=DIR_EXP_CONV DUMPFILE=exp_transport_soe.dmp TRANSPORT_FULL_CHECK=Y TRANSPORT_TABLESPACES=SOE,SOE_FAST_PART,SOE_INDX,SOE_LOB,SOE_SLOW_PART Export: Release 10.2.0.5.0 – 64bit Production on Monday, 17 January, 2011 17:39:48 Copyright (c) 2003, 2007, Oracle. All rights reserved. Username: systemPassword: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting"SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/****** DIRECTORY=DIR_EXP_CONV DUMPFILE=exp_transport_soe.dmp TRANSPORT_FULL_CHECK=Y TRANSPORT_TABLESPACES=SOE,SOE_FAST_PART,SOE_INDX,SOE_LOB,SOE_SLOW_PARTProcessing objecttypeTRANSPORTABLE_EXPORT/PLUGTS_BLKProcessing objecttypeTRANSPORTABLE_EXPORT/TABLEProcessing objecttypeTRANSPORTABLE_EXPORT/INDEXProcessing objecttypeTRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINTProcessing objecttypeTRANSPORTABLE_EXPORT/INDEX_STATISTICSProcessing objecttypeTRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINTProcessing objecttypeTRANSPORTABLE_EXPORT/TRIGGERProcessing objecttypeTRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEXProcessing objecttypeTRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICSProcessing objecttypeTRANSPORTABLE_EXPORT/TABLE_STATISTICSProcessing objecttypeTRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLEProcessing objecttypeTRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEXProcessing objecttypeTRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEXProcessing objecttypeTRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKMaster table"SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"successfully loaded/unloaded****************************************************************************DumpfilesetforSYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:/u01/app/oracle/flashrecovery_area/DBCONV/export/exp_transport_soe.dmpJob"SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"successfully completed at 17:40:36

Let’s convert datafiles to this stage area ‘/u01/app/oracle/flashrecovery_area/DBCONV/stage/’ with RMAN.

12345678910111213141516171819202122232425262728293031323334353637 CONVERTTABLESPACE SOE,SOE_FAST_PART,SOE_INDX,SOE_LOB,SOE_SLOW_PART TOPLATFORM ="AIX-Based Systems (64-bit)" DB_FILE_NAME_CONVERT ('/u01/app/oracle/oradata/dbconv/','/u01/app/oracle/flashrecovery_area/DBCONV/stage/'); PARALLELISM = 2 RMAN>CONVERTTABLESPACE SOE,SOE_FAST_PART,SOE_INDX,SOE_LOB,SOE_SLOW_PART TOPLATFORM ="AIX-Based Systems (64-bit)" DB_FILE_NAME_CONVERT ('/u01/app/oracle/oradata/dbconv/','/u01/app/oracle/flashrecovery_area/DBCONV/stage/'); PARALLELISM = 22> 3> Starting backupat17-01-2011 17:45:50 using targetdatabasecontrol fileinsteadofrecovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=137 devtype=DISK channel ORA_DISK_1: starting datafile conversion input datafile fno=00005name=/u01/app/oracle/oradata/dbconv/soe.dbf converted datafile=/u01/app/oracle/flashrecovery_area/DBCONV/stage/soe.dbf channel ORA_DISK_1: datafile conversion complete, elapsedtime: 00:02:06 channel ORA_DISK_1: starting datafile conversion input datafile fno=00006name=/u01/app/oracle/oradata/dbconv/soe_indx01.dbf converted datafile=/u01/app/oracle/flashrecovery_area/DBCONV/stage/soe_indx01.dbf channel ORA_DISK_1: datafile conversion complete, elapsedtime: 00:01:15 channel ORA_DISK_1: starting datafile conversion input datafile fno=00009name=/u01/app/oracle/oradata/dbconv/soe_lob01.dbf converted datafile=/u01/app/oracle/flashrecovery_area/DBCONV/stage/soe_lob01.dbf channel ORA_DISK_1: datafile conversion complete, elapsedtime: 00:00:15 channel ORA_DISK_1: starting datafile conversion input datafile fno=00007name=/u01/app/oracle/oradata/dbconv/soe_fast_part01.dbf converted datafile=/u01/app/oracle/flashrecovery_area/DBCONV/stage/soe_fast_part01.dbf channel ORA_DISK_1: datafile conversion complete, elapsedtime: 00:00:15 channel ORA_DISK_1: starting datafile conversion input datafile fno=00008name=/u01/app/oracle/oradata/dbconv/soe_slow_part01.dbf converted datafile=/u01/app/oracle/flashrecovery_area/DBCONV/stage/soe_slow_part01.dbf channel ORA_DISK_1: datafile conversion complete, elapsedtime: 00:00:07 Finished backupat17-01-2011 17:49:50 RMAN> Recovery Manager complete.

Moving Files

Moving files to destination host…

123456789101112 [oracle@libano DBCONV]$scp-rexportstage oracle@butao:/u01/app/db_convertoracle@butao's password:exp_schema_soe.dmp 100% 208KB 208.0KB/s 00:00export.log 100% 1819 1.8KB/s 00:00exp_transport_soe.dmp 100% 832KB 832.0KB/s 00:00soe_indx01.dbf 100% 2249MB 21.4MB/s 01:45soe_lob01.dbf 100% 364MB 22.8MB/s 00:16soe_slow_part01.dbf 100% 161MB 20.1MB/s 00:08soe.dbf 100% 3341MB 21.7MB/s 02:34soe_fast_part01.dbf 100% 181MB 22.6MB/s 00:08exp_exttables_soe.dmp 100% 112KB 112.0KB/s 00:00exp_exttables_soe.log 100% 938 0.9KB/s 00:00

Importing on Destination Database

Checking if user exists on destination database:Note: User must be not exists.

12345 SELECTUSERNAMEFROMDBA_USERSWHEREUSERNAME ='SOE'; norowsselected

Creating Oracle Directory to be used with impdp …

123456789 SQL>CREATEDIRECTORY DIR_IMP_CONVAS'/u01/app/db_convert/export'; Directory created. SQL>GRANTREAD,WRITEONDIRECTORY DIR_IMP_CONVTOSYSTEM; Grantsucceeded. SQL>

Creating all Oracle Directories from source database in destination database…

12345678910 SQL>CREATEDIRECTORY DIR_TXT_FILESAS'/u01/app/external_table/partner';Directory created. SQL>CREATEDIRECTORY EXT_PARTNER_DIRAS'/u01/app/external_table/partner';Directory created. SQL>CREATEDIRECTORY DIR_EXP_CONVAS'/u01/app/oracle/flashrecovery_area/DBCONV/export';Directory created. SQL>

Importing Schema SOE in destination database…

1234567891011121314151617181920212223242526272829303132333435363738394041 oracle@butao:/u01/app/db_convert/export> impdp DIRECTORY=DIR_IMP_CONV DUMPFILE=exp_schema_soe.dmp LOGFILE=imp_schema_soe.log Import: Release 10.2.0.5.0 – 64bit ProductiononSaturday, 22 January, 2011 14:38:32 Copyright (c) 2003, 2007, Oracle. Allrights reserved. Username: systemPassword: Connectedto: OracleDatabase10g Enterprise Edition Release 10.2.0.5.0 – 64bit ProductionWiththe Partitioning, OLAP, Data MiningandRealApplication Testing optionsMastertable"SYSTEM"."SYS_IMPORT_FULL_01"successfully loaded/unloadedStarting"SYSTEM"."SYS_IMPORT_FULL_01": system/******** DIRECTORY=DIR_IMP_CONV DUMPFILE=exp_schema_soe.dmp LOGFILE=imp_schema_soe.logProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/TABLESPACE_QUOTAORA-39083: Object type TABLESPACE_QUOTA failedtocreatewitherror:ORA-00959: tablespace'SOE'doesnotexistFailing sqlis:DECLARE TEMP_COUNT NUMBER; SQLSTR VARCHAR2(200);BEGIN SQLSTR :='ALTER USER "SOE" QUOTA UNLIMITED ON "SOE"'; EXECUTEIMMEDIATE SQLSTR;EXCEPTION WHENOTHERSTHEN IF SQLCODE = -30041THEN SQLSTR :='SELECT COUNT(*) FROM USER_TABLESPACES WHERE TABLESPACE_NAME = ''SOE'' AND CONTENTS = ''TEMPORARY'''; EXECUTEIMMEDIATE SQLSTRINTOTEMP_COUNT;Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TYPE/TYPE_SPECProcessing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCEProcessing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPECProcessing object type SCHEMA_EXPORT/FUNCTION/FUNCTIONProcessing object type SCHEMA_EXPORT/PROCEDURE/PROCEDUREProcessing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPECORA-39082: Object type ALTER_PACKAGE_SPEC:"SOE"."ORDERENTRY"createdwithcompilation warningsProcessing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTIONProcessing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDUREORA-39082: Object type ALTER_PROCEDURE:"SOE"."LOAD_BLOB_FROM_FILE"createdwithcompilation warningsProcessing object type SCHEMA_EXPORT/VIEW/VIEWORA-39082: Object typeVIEW:"SOE"."PRODUCTS"createdwithcompilation warningsORA-39082: Object typeVIEW:"SOE"."PRODUCT_PRICES"createdwithcompilation warningsProcessing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODYORA-39082: Object type PACKAGE_BODY:"SOE"."ORDERENTRY"createdwithcompilation warningsProcessing object type SCHEMA_EXPORT/JAVA_SOURCE/JAVA_SOURCEProcessing object type SCHEMA_EXPORT/JAVA_CLASS/JAVA_CLASSJob"SYSTEM"."SYS_IMPORT_FULL_01"completedwith6 error(s)at14:38:44

Some errors be raised, “Object type TABLESPACE_QUOTA failed to create with error” this error is because Tablespace SOE don’t yet exists.It’s completely normal.Granting privileges on Oracle Directories to user SOE.

1234567891011121314151617 SQL>GRANTEXECUTEONDIRECTORY EXT_PARTNER_DIRTOSOE;Grantsucceeded. SQL>GRANTREADONDIRECTORY EXT_PARTNER_DIRTOSOE;Grantsucceeded. SQL>GRANTWRITEONDIRECTORY EXT_PARTNER_DIRTOSOE;Grantsucceeded. SQL>GRANTEXECUTEONDIRECTORY DIR_TXT_FILESTOSOE;Grantsucceeded. SQL>GRANTREADONDIRECTORY DIR_TXT_FILESTOSOE;Grantsucceeded. SQL>GRANTWRITEONDIRECTORY DIR_TXT_FILESTOSOE;Grantsucceeded.

Importing External tables…

123456789101112131415 oracle@butao:/u01/app/db_convert/export> impdp DIRECTORY=DIR_IMP_CONV DUMPFILE=exp_exttables_soe.dmp LOGFILE=imp_exttable_soe.log Import: Release 10.2.0.5.0 – 64bit ProductiononSaturday, 22 January, 2011 18:57:02 Copyright (c) 2003, 2007, Oracle. Allrights reserved. Username: systemPassword: Connectedto: OracleDatabase10g Enterprise Edition Release 10.2.0.5.0 – 64bit ProductionWiththe Partitioning, OLAP, Data MiningandRealApplication Testing optionsMastertable"SYSTEM"."SYS_IMPORT_FULL_01"successfully loaded/unloadedStarting"SYSTEM"."SYS_IMPORT_FULL_01": system/******** DIRECTORY=DIR_IMP_CONV DUMPFILE=exp_exttables_soe.dmp LOGFILE=imp_exttable_soe.logProcessing object type TABLE_EXPORT/TABLE/TABLEJob"SYSTEM"."SYS_IMPORT_FULL_01"successfully completedat18:57:14

Attaching Tablespaces on destination database and importing tables…

1234567891011121314151617181920212223242526272829303132333435 imp_soe.par DIRECTORY=DIR_IMP_CONVDUMPFILE=exp_transport_soe.dmpLOGFILE=imp_transport_soe.logTRANSPORT_DATAFILES='/u01/app/db_convert/stage/soe.dbf','/u01/app/db_convert/stage/soe_fast_part01.dbf','/u01/app/db_convert/stage/soe_indx01.dbf','/u01/app/db_convert/stage/soe_lob01.dbf','/u01/app/db_convert/stage/soe_slow_part01.dbf' oracle@butao:/u01/app/db_convert/stage> impdp parfile=imp_soe.par Import: Release 10.2.0.5.0 – 64bit ProductiononSaturday, 22 January, 2011 14:52:56 Copyright (c) 2003, 2007, Oracle. Allrights reserved. Username: systemPassword: Connectedto: OracleDatabase10g Enterprise Edition Release 10.2.0.5.0 – 64bit ProductionWiththe Partitioning, OLAP, Data MiningandRealApplication Testing optionsMastertable"SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"successfully loaded/unloadedStarting"SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** parfile=imp_soe.parProcessing object type TRANSPORTABLE_EXPORT/PLUGTS_BLKProcessing object type TRANSPORTABLE_EXPORT/TABLEProcessing object type TRANSPORTABLE_EXPORT/INDEXProcessing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINTProcessing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICSProcessing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINTProcessing object type TRANSPORTABLE_EXPORT/TRIGGERProcessing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEXProcessing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICSProcessing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICSProcessing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLEProcessing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEXProcessing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEXProcessing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLKJob"SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"successfully completedat15:01:43

This step can take long time because Oracle always will Analyzes the Index and Tables.If you can’t this happens, you can use note below…EXCLUDE=STATISTICS Or EXCLUDE=INDEX_STATISTICS During Datapump Import Still Analyzes The Indexes [ID 793585.1]Checking Tablespace after import and change to READ WRITE…

1234567891011121314151617181920212223242526272829303132333435363738 selectTABLESPACE_NAME, STATUS FROMDBA_TABLESPACES WEHRE TABLESPACE_NAMELIKE'%SOE%'; TABLESPACE_NAME STATUS —————————— ——— SOE_SLOW_PART READONLY SOE_LOB READONLY SOE_INDX READONLY SOE_FAST_PART READONLY SOE READONLY SELECT'ALTER TABLESPACE ' TABLESPACE_NAME ' READ WRITE;'CMD FROMDBA_TABLESPACES WHERETABLESPACE_NAMELIKE'%SOE%'ANDSTATUS='READ ONLY'; CMD ———————————————————– ALTERTABLESPACE SOE_SLOW_PARTREADWRITE; ALTERTABLESPACE SOE_LOBREADWRITE; ALTERTABLESPACE SOE_INDXREADWRITE; ALTERTABLESPACE SOE_FAST_PARTREADWRITE; ALTERTABLESPACE SOEREADWRITE; SQL>ALTERTABLESPACE SOE_SLOW_PARTREADWRITE; Tablespace altered. SQL>ALTERTABLESPACE SOE_LOBREADWRITE; Tablespace altered. SQL>ALTERTABLESPACE SOE_INDXREADWRITE; Tablespace altered. SQL>ALTERTABLESPACE SOE_FAST_PARTREADWRITE; Tablespace altered. SQL>ALTERTABLESPACE SOEREADWRITE; Tablespace altered.

Compiling Objects Invalidated ….

12345678910111213 setpagesize 0 linesize 120 headingonfeedbackoffSELECT'ALTER ' decode(object_type,'PACKAGE BODY','PACKAGE',object_type) ' "' owner '"."' object_name '" compile' decode(object_type,'PACKAGE BODY',' BODY') ';'FROMdba_objectsWHEREstatus ='INVALID'andobject_typein('PACKAGE','PACKAGE BODY','PROCEDURE','VIEW','TRIGGER','SYNONYM','FUNCTION');
12345678910 ALTERPACKAGE"SOE"."ORDERENTRY"compile;ALTERPROCEDURE"SOE"."LOAD_BLOB_FROM_FILE"compile;ALTERVIEW"SOE"."PRODUCTS"compile;ALTERVIEW"SOE"."PRODUCT_PRICES"compile;ALTERPACKAGE"SOE"."ORDERENTRY"compile BODY;SQL>ALTERPACKAGE"SOE"."ORDERENTRY"compile;ALTERPROCEDURE"SOE"."LOAD_BLOB_FROM_FILE"compile;ALTERVIEW"SOE"."PRODUCTS"compile;ALTERVIEW"SOE"."PRODUCT_PRICES"compile;ALTERPACKAGE"SOE"."ORDERENTRY"compile BODY;

Checking again ….

1234567891011121314 SQL>SELECT'ALTER ' 2 decode(object_type,'PACKAGE BODY','PACKAGE',object_type) ' "' 3 owner '"."' 4 object_name '" compile' 5 decode(object_type,'PACKAGE BODY',' BODY') ';'6 FROMdba_objects7 WHEREstatus ='INVALID'8 andobject_typein('PACKAGE',9 'PACKAGE BODY',10 'PROCEDURE',11 'VIEW',12 'TRIGGER','SYNONYM','FUNCTION'); norowsselected

Good … all objects validated …Verifying if all Objects still on destination Database…

12345678910111213141516171819202122232425 SELECTCOUNT(OBJECT_NAME)FROMDBA_OBJECTSWHEREOWNER='SOE'; COUNT(OBJECT_NAME)——————246 COUNT(OBJECT_TYPE) OBJECT_TYPE—————— ——————-4FUNCTION30INDEX3 JAVA CLASS1 JAVA SOURCE3 LOB1 PACKAGE1 PACKAGE BODY2PROCEDURE3SEQUENCE34TABLE160TABLEPARTITION1TRIGGER1 TYPE2VIEW14rowsselected.

Good .. all objects were migrated.You can start your application and pray no error being raised by Oracle.If everything is ok … you can celebrate.Advertisements