Migrate to different Endian
Platform Using Transportable Tablespaces with RMAN (Recovery Manager)
Starting with Oracle Database 10g, you can transport
tablespaces across platforms. In our case, we want to transport from Solaris[tm] OE (64-bit) to Linux x86 64-bit. We have
using an Oracle Database 11gR2 (11.2.0.3).
You can apply this procedure to migrate a database to
different endian platform. The following high-level steps are:
1- Create
a new empty database on the destination platform.
2- Export
transportable metadata for all user tablespaces from the source database.
3- Transfer
data files the endian format of the destination system.
4- Import
transportable metadata for all user tablespaces into the destination database.
5- Import
the remaining database objects and metadata, that were not moved by the
transport operation, from the source database into the destination database.
A- Prerequisites
tasks:
1- The
source and target database must use the same character set and the same
national character set:
On source
database:
SQL> select * from
nls_database_parameters;
PARAMETER VALUE
------------------------------
--------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------
--------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.3.0
On
destination database:
SQL> COLUMN PLATFORM_NAME FORMAT A32
SQL> select * from
nls_database_parameters;
PARAMETER VALUE
----------------------------- -----------------------
NLS_RDBMS_VERSION 12.1.0.2.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET WE8MSWIN1252
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN
20 rows
selected.
2- You
cannot transport a tablespace to a target database in which a tablespace with
the same name already exists. However, you can rename either tha tablespace to
be transported or the destination tablespace before the transport operation.
E.g:
Alter tablespace TBS1 rename to TBS1_OLD;
3- If
the owner(s) of tablespace objects does not exists on target database, the
usernames need to created manually before starting the transportable tablespace
import.
4- You
cannot transport the SYSTEM tablespace or objects owned by the user SYS.
5- Query
the V$TRANSPORTABLE_PLATFORM view to see the platforms that are supported and
to determine each platform’s endian format:
SQL> COLUMN PLATFORM_NAME FORMAT A32
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
7
Microsoft Windows IA (32-bit) Little
10
Linux IA (32-bit) Little
6
AIX-Based Systems (64-bit) Big
3
HP-UX (64-bit) Big
5 HP
Tru64 UNIX Little
4
HP-UX IA (64-bit) Big
11
Linux IA (64-bit) Little
15 HP
Open VMS Little
8
Microsoft Windows IA (64-bit) Little
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- --------------------------------
--------------
9 IBM
zSeries Based Linux Big
13 Linux x86 64-bit Little
16
Apple Mac OS Big
12
Microsoft Windows x86 64-bit Little
17
Solaris Operating System (x86) Little
18 IBM
Power Based Linux Big
19 HP
IA Open VMS Little
20
Solaris Operating System (x86-64 Little
)
21
Apple Mac OS (x86-64) Little
20 rows
selected.
You can see that the endian format are different.
B - Transporting tablespace
1- Prepare
for export of the tablespace
-
Prepare and collect information on the source database
for the related tablespaces which need to be transported:
SQL>
select tablespace_name from dba_tablespaces where tablespace_name not in
('SYSTEM', 'SYSAUX') and contents not in ('UNDO', 'TEMPORARY');
TABLESPACE_NAME
------------------------------
USERS
ISL_IDX_TBSP
ISL_TAB_TBSP
SQL>
-
Check that the tablespace will be self contained:
SQL>
execute sys.dbms_tts.transport_set_check('USERS,ISL_IDX_TBSP,ISL_TAB_TBSP',
true);
PL/SQL
procedure successfully completed.
SQL>
select * from sys.transport_set_violations;
no
rows selected
Note: If some violations appear, these violations must
be resolved before the tablespace can be transported.
-
The tablespace need to be in READ ONLY mode in order
to successfully run a transport tablespace export.
SQL> ALTER
TABLESPACE USERS READ ONLY;
Tablespace altered.
SQL> ALTER
TABLESPACE ISL_IDX_TBSP READ ONLY;
Tablespace altered.
SQL> ALTER
TABLESPACE ISL_TAB_TBSP READ ONLY;
Tablespace altered.
2- Export
the metadata
You can use a traditional export or Datapump.
-
In our case, we are using Datapump:
-
Using Datapump export:
o
First create the directory object to be used for
Datapump:
SQL>
create directory dpdump as '/data/tmp';
Directory
created.
SQL>
grant read,write on directory dpdump to system;
Grant
succeeded.
SQL>
expdp
system directory=dpdump dumpfile=dp_tbs_metadata_exp.dmp
logfile=dp_tbs_metadata_exp.log transport_tablespace=y tablespaces= USERS, ISL_IDX_TBSP,
ISL_TAB_TBSP
Export:
Release 11.2.0.3.0 - Production on Tue Apr 18 17:20:18 2017
Copyright
(c) 1982, 2011, Oracle and/or its affiliates.
All rights reserved.
Password:
Connected
to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
Production
With
the Partitioning, Automatic Storage Management, OLAP, Data Mining
and
Real Application Testing options
Legacy
Mode Active due to the following parameters:
Legacy
Mode Parameter: "transport_tablespace=TRUE" Location: Command Line, Replaced
with: "transport_tablespaces=USERS,ISL_IDX_TBSP,ISL_TAB_TBSP"
Legacy
Mode has set reuse_dumpfiles=true parameter.
Starting
"SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=dpdump
dumpfile=dp_tbs_metadata_exp.dmp logfile=dp_tbs_metadata_exp.log tablespaces=
tablespaces ISL_TAB_TBSP reuse_dumpfiles=true
Processing
object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing
object type TRANSPORTABLE_EXPORT/TABLE
Processing
object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing
object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing
object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing
object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing
object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing
object type
TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/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/TABLE_STATISTICS
Processing
object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master
table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully
loaded/unloaded
******************************************************************************
Dump
file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/data/tmp/dp_tbs_metadata_exp.dmp
******************************************************************************
Datafiles
required for transportable tablespace ISL_IDX_TBSP:
+DATA/islstby/datafile/isl_idx_tbsp.263.941313185
Datafiles
required for transportable tablespace ISL_TAB_TBSP:
+DATA/islstby/datafile/isl_tab_tbsp.257.941312467
Datafiles
required for transportable tablespace USERS:
+DATA/islstby/datafile/users.262.941313019
Job
"SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully
completed at 17:34:35
3- The
endian formats are different, so a conversion is necessary for transporting the
tablespace set:
-
Conversion of USERS tablespace:
rman
target /
Recovery
Manager: Release 11.2.0.3.0 - Production on Tue Apr 18 18:01:32 2017
Copyright
(c) 1982, 2011, Oracle and/or its affiliates.
All rights reserved.
connected
to target database: ISLDB (DBID=3563339455)
RMAN>
convert tablespace USERS to platform="Linux x86 64-bit" parallelism 5
FORMAT '/data/tmp/%U';
Starting
conversion at source at 18-APR-17
using
target database control file instead of recovery catalog
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: SID=99 device type=DISK
allocated
channel: ORA_DISK_2
channel
ORA_DISK_2: SID=108 device type=DISK
allocated
channel: ORA_DISK_3
channel
ORA_DISK_3: SID=116 device type=DISK
allocated
channel: ORA_DISK_4
channel
ORA_DISK_4: SID=123 device type=DISK
allocated
channel: ORA_DISK_5
channel
ORA_DISK_5: SID=132 device type=DISK
channel
ORA_DISK_1: starting datafile conversion
input
datafile file number=00004 name=+DATA/islstby/datafile/users.262.941313019
converted
datafile=/data/tmp/data_D-ISLDB_I-3563339455_TS-USERS_FNO-4_ncs20u4b
channel
ORA_DISK_1: datafile conversion complete, elapsed time: 00:04:25
Finished
conversion at source at 18-APR-17
Starting
Control File and SPFILE Autobackup at 18-APR-17
piece
handle=+DATA/islstby/autobackup/2017_04_18/s_941652372.319.941652375 comment=NONE
Finished
Control File and SPFILE Autobackup at 18-APR-17
-
Conversion of ISL_IDX_TBSP
tablespace:
RMAN>
convert tablespace ISL_IDX_TBSP to platform="Linux x86 64-bit" FORMAT
'/data/tmp/%U';
Starting
conversion at source at 18-APR-17
using
channel ORA_DISK_1
channel
ORA_DISK_1: starting datafile conversion
input
datafile file number=00006
name=+DATA/islstby/datafile/isl_idx_tbsp.263.941313185
converted
datafile=/data/tmp/data_D-ISLDB_I-3563339455_TS-ISL_IDX_TBSP_FNO-6_nes20ue7
channel
ORA_DISK_1: datafile conversion complete, elapsed time: 00:04:15
Finished
conversion at source at 18-APR-17
Starting
Control File and SPFILE Autobackup at 18-APR-17
piece
handle=+DATA/islstby/autobackup/2017_04_18/s_941652678.320.941652681
comment=NONE
Finished
Control File and SPFILE Autobackup at 18-APR-17
RMAN>
convert tablespace ISL_TAB_TBSP to platform="Linux x86 64-bit" FORMAT
'/data/tmp/%U';
Starting
conversion at source at 18-APR-17
using
channel ORA_DISK_1
channel
ORA_DISK_1: starting datafile conversion
input
datafile file number=00007
name=+DATA/islstby/datafile/isl_tab_tbsp.257.941312467
converted
datafile=/data/tmp/data_D-ISLDB_I-3563339455_TS-ISL_TAB_TBSP_FNO-7_ngs20unk
channel
ORA_DISK_1: datafile conversion complete, elapsed time: 17:57:33
Finished
conversion at source at 19-APR-17
Starting
Control File and SPFILE Autobackup at 19-APR-17
piece
handle=+DATA/islstby/autobackup/2017_04_19/s_941717377.321.941717379
comment=NONE
Finished
Control File and SPFILE Autobackup at 19-APR-17
4- The
next step is to copy the data files as well the export dump file to the target
environment. You can notice that if your database is huge, you can transform
the data files on a directory shared by the source and target server.
On the target database, We are using Datapump to
import the transportable tablespace.
o
First create the directory object to be used for
Datapump:
SQL>
create directory dpdump as '/data/tmp';
Directory
created.
SQL>
grant read,write on directory dpdump to system;
Grant
succeeded.
SQL>
Follow by:
impdp
system directory=dpdump dumpfile=dp_tbs_metadata_exp.dmp TRANSPORT_DATAFILES=’/orcl_dump/tmp/ data_D-ISLDB_I-3563339455_TS-USERS_FNO-4_ncs20u4b’,’/orcl_dump/tmp/ data_D-ISLDB_I-3563339455_TS-ISL_IDX_TBSP_FNO-6_nes20ue7’,’/orcl_dump/tmp/ data_D-ISLDB_I-3563339455_TS-ISL_TAB_TBSP_FNO-7_ngs20unk’ logfile=dp_tbs_metadata_imp.log
Import: Release 12.1.0.2.0 - Production on Fri Apr 21
09:31:08 2017
Copyright (c) 1982, 2014, Oracle and/or its
affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition
Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters,
Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing
options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"
successfully loaded/unloaded
Source time zone version is 14 and target time zone
version is 18.
Starting
"SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=mydpdump
dumpfile=dp_tbs_metadata_exp.dmp TRANSPORT_DATA FILES=/orcl_dump/tmp/data_D-ISLDB_I-3563339455_TS-USERS_FNO-4_ncs20u4b,/orcl_dump/tmp/data_D-ISLDB_I-3563339455_TS-ISL_IDX_TBSP_FNO-
6_nes20ue7,/orcl_dump/tmp/data_D-ISLDB_I-3563339455_TS-ISL_TAB_TBSP_FNO-7_ngs20unk
logfile=dp_tbs_metadata_imp.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
ORA-39151: Table "SCOTT"."DEPT"
exists. All dependent metadata and data will be skipped due to
table_exists_action of skip
ORA-39151: Table "SCOTT"."EMP"
exists. All dependent metadata and data will be skipped due to
table_exists_action of skip
ORA-39151: Table "SCOTT"."BONUS"
exists. All dependent metadata and data will be skipped due to
table_exists_action of skip
ORA-39151: Table
"SCOTT"."SALGRADE" exists. All dependent metadata and data
will be skipped due to table_exists_action of skip
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type
TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type
TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type
TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type
TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type
TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/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/TABLE_STATISTICS
Processing object type
TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
ORA-39082: Object type
TRIGGER:"ISL_MTNC"."ISL_ALARM_FILTER_IGNR_TRG" created with
compilation warnings
ORA-39082: Object type
TRIGGER:"ISL_MTNC"."ISL_ALARM_TRG" created with compilation
warnings
ORA-39082: Object type
TRIGGER:"ISL_MTNC"."ISL_ALARM_FILTER_TRG" created with
compilation warnings
ORA-39082: Object type
TRIGGER:"ISL_MTNC"."CRM_UI_AFTER_INSERT_UPDATE" created
with compilation warnings
ORA-39082: Object type
TRIGGER:"ISL_MTNC"."CRM_I18N_AFTER_INSERT_UPDATE" created
with compilation warnings
ORA-39082: Object type TRIGGER:"ISL_MTNC"."CRM_MODEL_AFTER_INSERT_UPDATE"
created with compilation warnings
Job
"SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 10
error(s) at Fri Apr 21 09:36:02 2017 elapsed 0 00:04:42
You
can also use REMAP_SCHEMA if you want to change the ownership of the
transported database objects.
5- As
you can see, there is some objects like sequences who are note imported. If you
are in this case, you must just Import the remaining database objects and
metadata (that were not moved by the transport operation), from the source
database into the destination database.
In our case:
a. Export
first all the required sequences from the source database :
$ expdp system directory=mydir dumpfile=sequence.dump
include=sequence logfile=sequence_only.log schemas=isl_mtnc
Export: Release 11.2.0.3.0 - Production on Sun Apr 23
16:18:11 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition
Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management,
OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=mydir
dumpfile=sequence.dump include=sequence logfile=sequence_only.log
schemas=isl_mtnc
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type
SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Master table
"SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully
loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/export/home/oracle/dump/sequence.dump
Job
"SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at
16:18:28
b. And
then import the sequences to the target database:
$ impdp system/oracle123 directory=mydpdump
dumpfile=sequence.dump
Import: Release 12.1.0.2.0 - Production on Sun Apr 23
16:22:24 2017
Copyright (c) 1982, 2014, Oracle and/or its
affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition
Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters,
Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing
options
Master table
"SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=mydpdump
dumpfile=sequence.dump
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type
SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Job "SYSTEM"."SYS_IMPORT_FULL_01"
successfully completed at Sun Apr 23 16:22:29 2017 elapsed 0 00:00:03
6- Put
the tablespace in read/write mode:
SQL> select tablespace_name,status from
dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
OLD_USERS ONLINE
UNDOTBS2 ONLINE
ISL_IDX_TBSP READ ONLY
ISL_TAB_TBSP READ ONLY
USERS READ ONLY
9 rows selected.
SQL> alter
tablespace USERS read write;
Tablespace altered.
SQL> alter
tablespace ISL_IDX_TBSP read write;
Tablespace altered.
SQL> alter
tablespace ISL_TAB_TBSP read write;
Tablespace altered.
SQL> select tablespace_name,status from
dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
OLD_USERS ONLINE
UNDOTBS2 ONLINE
ISL_IDX_TBSP ONLINE
ISL_TAB_TBSP ONLINE
USERS
ONLINE
9 rows selected.
SQL>
C - Take a full non-rows export of the source database and import them to the target database to create the missing objects who are not transported with TTS, such as sequences, procedures, functions, packages, type etc.
