Oracle DBA – A lifelong learning experience

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

Advertisements

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

 
%d bloggers like this: