Migrating tablespaces across Endian platforms
Posted by John Hallas on August 13, 2015
This is a set of posts about migrating a database from one endian platform to another.
The long-term intention is to move a large (10Tb) 11.1.0.7 database on HP-UX to an OEL Linux server with minimum outage so that will include a database upgrade as well.
This first post is about migrating a self-contained set of schemas using transportable tablespace.
The HP-UX 11.1.0.7 database is called HPUXDB and it was created through dbca with the sample schemas created.. The target database is 11.2.0.4 on OEL 5.8
I already have an 11.2.0.4 installation on the OEL 5.8 server and the target database is called L18
Let’s check the endian versions
SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM; PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT 4 HP-UX IA (64-bit) 13 Linux x86 64-bit Little
Set the tablespace EXAMPLE read only
alter tablespace example read only; Tablespace altered. select file_name from dba_data_files; +DATA/hpuxdb/datafile/example.352.886506805 EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list=>'EXAMPLE', incl_constraints =>TRUE); PL/SQL procedure successfully completed. SELECT * FROM transport_set_violations; no rows selected
Export the data using the keywords transport_tablespaces
expdp directory=data_pump_dir transport_tablespaces=example dumpfile=hpux.dmp logfile=hpux.log Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": /******** AS SYSDBA Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TRANSPORTABLE_EXPORT/INDEX Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/COMMENT Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/TRIGGER Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEX Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Master table SYS.SYS_EXPORT_TRANSPORTABLE_01 successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is: /app/oracle/admin/HPUXDB/dpdump/hpux.dmp ****************************************************************************** Datafiles required for transportable tablespace EXAMPLE: +DATA/hpuxdb/datafile/example.352.886506805 Job SYS_EXPORT_TRANSPORTABLE_01 successfully completed at 13:47:55
sftp the export dump file to the target server – I am using the data_pump_dir directory again.
You also need to copy the datafile(s) of the tablespaces you are migrating.
Mine is on ASM – switch to the ASM database and run asmcmd. I am copying out to /tmp and then copying it across to the target server on /tmp for now
ASMCMD> cp ‘+DATA/hpuxdb/datafile/example.352.886506805’ ‘/tmp/example.dbf’
copying +DATA/hpuxdb/datafile/example.352.886506805 -> /tmp/example.dbf
ASMCMD>
sftp> cd /tmp
sftp> put /tmp/example.dbf /tmp/example.dbf
Uploading /tmp/example.dbf to /tmp/example.dbf
/tmp/example.dbf 100% 100MB 11.1MB/s 11.8MB/s 00:09
Max throughput: 13.9MB/s
Create users who own objects in the tablespace to be migrated. Note that their default tablespace (EXAMPLE) does not exist as yet.
create user sh identified by sh;
grant connect,resource to sh;
grant unlimited tablespace to sh;
grant create materialized view to sh;
create user oe identified by oe;
grant connect,resource to oe;
grant unlimited tablespace to oe;
create user hr identified by hr;
grant connect,resource to hr;
grant unlimited tablespace to hr;
create user ix identified by ix;
grant connect,resource to ix;
grant unlimited tablespace to ix;
create user pm identified by pm;
grant connect,resource to pm;
grant unlimited tablespace to pm;
create user bi identified by bi;
grant connect,resource to bi;
grant unlimited tablespace to bi;
CONVERT TABLESPACE EXAMPLE
TO PLATFORM ‘Linux x86 64-bit’
FORMAT=’/tmp/%U’;
$rmant
Recovery Manager: Release 11.1.0.7.0 - Production on Mon Aug 3 13:54:42 2015
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: HPUXDB (DBID=825022061)
using target database control file instead of recovery catalog
RMAN> CONVERT TABLESPACE EXAMPLE
TO PLATFORM 'Linux x86 64-bit'
FORMAT='/tmp/%U';
Starting conversion at source at 2015-08-03:13:56:18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00005 name=+DATA/hpuxdb/datafile/example.352.886506805
converted datafile=/tmp/data_D-HPUXDB_I-825022061_TS-EXAMPLE_FNO-5_04qdm5k2
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at source at 2015-08-03:13:56:21
sftp> put /tmp/data_D-HPUXDB_I-825022061_TS-EXAMPLE_FNO-5_04qdm5k2 Uploading /tmp/data_D-HPUXDB_I-825022061_TS-EXAMPLE_FNO-5_04qdm5k2 to /tmp/data_D-HPUXDB_I-825022061_TS-EXAMPLE_FNO-5_04qdm5k2 impdp directory=data_pump_dir dumpfile=hpux.dmp logfile=linux.log transport_datafiles=’/tmp/data_D-HPUXDB_I-825022061_TS-EXAMPLE_FNO-5_04qdm5k2′
Import: Release 11.2.0.4.0 - Production on Mon Aug 3 13:24:06 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management and OLAP options
Master table SYS.SYS_IMPORT_TRANSPORTABLE_01 successfully loaded/unloaded
Starting SYS.SYS_IMPORT_TRANSPORTABLE_01 /******** AS SYSDBA directory=data_pump_dir dumpfile=hpux.dmp logfile=linux.log transport_datafiles=/tmp/data_D-HPUXDB_I-825022061_TS-EXAMPLE_FNO-5_02qdm380
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON OE.CUSTOMERSTO BI;
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON OE.WAREHOUSES TO BI;
ORA-31685: Object type MATERIALIZED_VIEW:SH.CAL_MONTH_SALES_MV; failed due to insufficient privileges. Failing sql is:
CREATE MATERIALIZED VIEW SH.CAL_MONTH_SALES_MV (CALENDAR_MONTH_DESC, DOLLARS) USING (CAL_MONTH_SALES_MV(9, 'HPUXDB', 2, 0, 0, SH.TIMES, '2015-07-31 11:55:08', 8, 70899, '2015-07-31 11:55:09', '', 1, '0208', 822277, 0, NULL, 1, "SH", "SALES", '2015-07-31 11:55:08', 33032, 70841, '2015-07-31 11:55:09', '', 1, '88', 822277, 0, NULL), 1183809, 9, ('1950-01-01 12:00:00', 21,
Dropped the users in the example schema – dropped the example tablespace. Added the user and create a BI user and granted CREATE MATERIALIZED VIEW to SH. Then re-run the import
impdp directory=data_pump_dir dumpfile=hpux.dmp logfile=linux.log transport_datafiles='/tmp/data_D-HPUXDB_I-825022061_TS-EXAMPLE_FNO-5_04qdm5k2' Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management and OLAP options Master table SYS SYS_IMPORT_TRANSPORTABLE_01 successfully loaded/unloaded Starting SYS.SYS_IMPORT_TRANSPORTABLE_01/******** AS SYSDBA directory=data_pump_dir dumpfile=hpux.dmp logfile=linux.log transport_datafiles=/tmp/data_D-HPUXDB_I-825022061_TS-EXAMPLE_FNO-5_04qdm5k2 Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TRANSPORTABLE_EXPORT/INDEX Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/COMMENT Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT Processing object type TRANSPORTABLE_EXPORT/TRIGGER Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEX Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job SYS.SYS_IMPORT_TRANSPORTABLE_01 completed at Mon Aug 3 14:04:42 2015 elapsed 0 00:00:52
Post Migration
alter tablespace example read write;
alter the users created earlier to have their default tablespace as example. The imported objects are in EXAMPLE but new ones will go to USERS or whichever the defult tablespace is set to.
Remove all dmp files and interim files that were created (in /tmp on both servers in my demo).
Migrating directly from ASM to ASM
For ease in the example above I migrated out from ASM to a filesystem. Here I will demonstrate a copy going from ASM to ASM.
create user test identified by xxxxxxxxx default tablespace example1; User created. grant connect, resource to test; Grant succeeded. grant unlimited tablespace to test; Grant succeeded. connect test/xxxxxxxx Connected. create table example1_data tablespace example1 as select * from all_objects; Table created. select segment_name, tablespace_name from user_segments;
SEGMENT_NAME TABLESPACE_NAME
——————————
EXAMPLE1_DATA EXAMPLE1
EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list = > 'EXAMPLE1', incl_constraints => TRUE); PL/SQL procedure successfully completed. SELECT * FROM transport_set_violations; no rows selected alter tablespace4 example1 read only;
Now export the metadata
expdp …..
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table “SYS”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/app/oracle/admin/HPUXDB/dpdump/hpux.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE1:
+DATA/hpuxdb/datafile/example1.1542.887621739
Job SYS.SYS_EXPORT_TRANSPORTABLE_01 successfully completed at 09:48:33
Now move the datafile to the target ASM environment. You can copy the dmp file in the same manner if you wish
ASMCMD [+] > cp –port 1522 sys@server.+ASM:+DATA/hpuxdb/datafile/example1.1542.887621739 +DATA/linuxdb/example1
Enter password: **********
copying server:+DATA/hpuxdb/datafile/example1.1542.887621739 -> +DATA/linuxdb/example1
The only problem with copying from ASM to ASM is that the physical file is not located in the directory where you want to copy. It is actually stored in the +DATA/ASM/DATAFILE directory (rather than the LINUXDB directory:
ASMCMD [+data/linuxdb] > ls -l
Type Redund Striped Time Sys Block_Size Blocks Bytes Space Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
DATAFILE UNPROT COARSE AUG 13 13:00:00 N 8192 6401 52436992 53477376 example1 => +DATA/ASM/DATAFILE/example1.362.887637415
PARAMETERFILE UNPROT COARSE AUG 12 22:00:00 N 512 5 2560 1048576 spfileLINUXDB.ora => +DATA/LINUXDB/PARAMETERFILE/spfile.331.886765295
Move it to the correct folder using the cp command within asmcmd then rm from the original folder
Oracle DBA - A lifelong learning experience 
ASM | IT Remote said
[…] ASM Stuff […]