Oracle transfer table space (transportable tablespaces) Example (2)-Cross-operating system migration table space (different endianness formats)

 

I. Description

Previous instructions:

Oracle cross-operating system migration

Http://blog.csdn.net/tianlesoftware/article/details/7252788

 

Oracle cross-operating system migration

Http://blog.csdn.net/tianlesoftware/article/details/7252788

 

Oracle transfer tablespace (transportabletablespaces) Example (1) -- migrate tablespace across operating systems (same endianness format)

Http://blog.csdn.net/tianlesoftware/article/details/7299283

 

Let's take a look at the format of endianness:

SQL> select * from V $ transportable_platform order by platform_name;

 

Platform_id platform_name endian_format

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

6 Aix-based systems (64-bit) Big

16 Apple Mac OS big

Apple Mac OS (x86-64) Little

19 HP Ia Open VMS little

15 HP Open VMS little

5 HP Tru64 UNIX little

3 HP-UX (64-bit) Big

4 HP-UX Ia (64-bit) Big

18 IBM power based Linux big

9 IBM zseries based Linux big

10 Linux Ia (32-bit) Little

 

Platform_id platform_name endian_format

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

11 Linux Ia (64-bit) Little

13 Linux x86 64-bit little

7 Microsoft Windows Ia (32-bit) Little

8 Microsoft Windows Ia (64-bit) Little

12 Microsoft Windows x86 64-bit little

17 Solaris operating system (x86) Little

20 Solaris operating system (x86-64) Little

1 Solaris [Tm] OE (32-bit) Big

2 Solaris [Tm] OE (64-bit) Big

 

20 rows selected.

 

The above results show that the endian format is big systems are UNIX systems such as Aix, HP-UNIX, Solaris, and these systems are related to hardware, so it cannot be simulated, therefore, an example of MOS is directly referenced here:

10g: transportable tablespaces extends ssdifferent platforms [ID 243304.1]

 

Ii. Example

 

2.1. Check for restrictions

-- Check TTS usage restrictions

Review the "limitations on transportable tablespace use" section in note371556.1.

Among otherthings, objects that reside in the system tablespace and objects owned by syswill not be transported. This between des but is not limited to users, privileges, PL/SQL stored procedures, and views.

 

If you usespatial indexes, apply the solution in note579136.1 "impdp transportable tablespace fails for spatialindex)" before continuing.

 

2.2. Prepare the database

-- Preparations before using TTS

2.2.1 Check that thetablespace will be self-contained

SQL> execute SYS. dbms_tts.transport_set_check ('tbs1, tbs2 ', true );
SQL> select * From SYS. transport_set_violations;

==> These violations must be resolved before the tablespaces can betransported.

-- Check the self-contained space. TTS operations can be performed only after the verification is passed.

 

2.2.2 set the tablespaceto read only

SQL> alter tablespace reposit read only;
Tablespace altered.

-- Set the tablespace to be migrated to read-only.

 

2.3. Export metadata

-- Derivative tablespace meatdata on the source end (HP-UNIX)

<HP-UX>

-- Use exp

Exp userid = \ '/As sysdba \ 'Transport _ tablespace = y
Tablespaces = reposit
File = TTS. dmp log = exp_tts.log
Statistics = none

Export: Release 10.2.0.4.0-mon Nov 26 11:49:49 2007
...

Note: Table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace reposit...
. Exporting cluster Definitions
. Exporting table Definitions
.. Exporting table mtg_col_dep_chg
.. Exporting table mtg_databases
....
.. Exporting table sybase11_sysusers
. Exporting referential integrity constraints
. Exporting triggers
. End transportable tablespace metadata export
Export terminated successfully without warnings.

Review the exportlog for warnings and errors and resolve issues before continuing. Failure to Doso can result in data loss.

-- You can also use expdp:

DataPump can be used for that purpose too:

Expdp \ '/As sysdba \' directory = tts_dumpdumpfile = tts1_dp.dmp logfile = tts_dump_log: TTS. Log
Transport_tablespaces = tts_1, tts_2 transport_full_check = y

Starting "system". "sys_export_transportable_02": System/******** directory = tts_datafile dumpfile = tts1.dmplogfile = tts_dump_log: TTS. Log transport
_ Tablespaces = tts_1, tts_2 transport_full_check = y
Processing object type transportable_export/plugts_blk
Processing object type transportable_export/table
Processing object type transportable_export/Index
Processing object type transportable_export/index_statistics
Processing object type transportable_export/table_statistics
Processing object type transportable_export/post_instance/plugts_blk
Master table "system". "sys_export_transportable_02" successfullyloaded/unloaded
**************************************** * ****************************** Dumpfile set for system. sys_export_transportable_02 is:
+ Data/tts1.dmp
Job "system". "sys_export_transportable_02" successfullycompleted at 14:00:34

Movement of data and enabling TTS

2.4. Check the endianness of the target database and convert, if necessary

-- Check the endianness format and convert it if needed

 

2.4.1 Case 1: sameendianness (big-> big or little-> little)

-- The endianness format is the same.

The source platform is Sun iSCSI Solaris: endianness big
The target platform is HP-UX (64-bit): endianness big

SQL> select platform_id, platform_namefrom v $ database;

Platform_idplatform_name
-----------------------------------------
3 HP-UX (64-bit)

No conversion is needed for files that (1) do not contain Undo/rollback segmentsand (2) have a source and target OS with the same endianness.
Refer to note.0000884.1 "crossplatform database conversion with same endian" to determine whichfiles contain Undo/rollback segments.

 

2.4.2 Case 2: differentendianness (big-> little or little-> big)

-- Endianness must be converted if the format is different.

The source platform is Microsoft WindowsNT: endianness little
The target platform is HP-UX (64-bit): endianness big

If we move the files and import the tablespace:
-- If we do not convert the data directly to IMP, the following error message will be reported:

. Importing sys's objects into sys
Imp-00017: following statement failed with Oracle error 1565:
"Beginsys. dbms_plugts.beginimptablespace ('tbs _ TTS ', 37, 'sys', 57"
"54175, 1, 2147483645,8, 462754339,1, null, 0, 0, null, null"
"); End ;"
Imp-00003: Oracle error 1565 encountered
ORA-01565: Error in identifying File '/database/db101b2/v101b2/datafile/reposit01.dbf'
ORA-27047: unable to read the header block of File
HP-UX error: 2: no such file or directory
Additional information: 2
ORA-06512: At "SYS. dbms_plugts", line 1540
ORA-06512: At line 1
Imp-00000: Import terminated unsuccessfully

You have to convertthe files; the files can be converted on source or on target:

-- Before starting TTS, you must perform the conversion first. This conversion can be performed on the source side or the target side:

(1) locally on the source before theimport step so that the files are endian compatible:

-- Perform conversion on the source end:

 

<Solaris>

RMAN target =/

Recovery MANAGER: Release 10.2.0.4.0-64bit
Connected to target database: v101b2 (dbid = 3287908659)

RMAN> convert tablespace 'reposit'
2> to platform = "Linux Ia (32-bit )"
3> db_file_name_convert = '/database/db101b2/v101b2/datafile/reposit01.dbf ',
4> '/tmp/reposit01.dbf ';

Starting backup at 26-nov-07
Using target database controlfile instead of recovery catalog
Allocated channel: ora_disk_1
Channel ora_disk_1: SID = 8 devtype = Disk
Channel ora_disk_1: Starting datafile Conversion
Input datafile fno = 00006 name =/database/db101b2/v101b2/datafile/reposit01.dbf
Converted datafile =/tmp/reposit01.dbf
Channel ora_disk_1: datafile Conversion complete, elapsed time: 00:00:01
Finished backup at 26-nov-07

The converted datafile is staged in/tmp directory until it is copied to thetarget server.

-- The converted datafile is stored in the/tmp directory. We copy the datafile in the/tmp directory to the target directory.

 

(2) remotely on the target server after having copiedthem on the server.

-- First copy the datafile from the source end to the target end, and then perform conversion on the target end.

 

Conversion ontarget platform is a way forward when the V $ transportable_platform of thesource does not list the target platform.

When conversionis done on the target platform then convert datafile is used instead of converttablespace, ie:

RMAN> convert datafile
'/Database/db101b2/v101b2/datafile/reposit01.dbf'
To platform = "Linux Ia (32-bit )"
From platform = "HP Tru64 UNIX"
Db_file_name_convert = "/database/db101b2/v101b2/datafile/", "/tmp /";

-- Note that the commands here are different from those on the source end.

 

2.5. Move datafiles and export dump file

$ Ftp tts. dmp
+
/Database/db101b2/v101b2/datafile/reposit01.dbf (no conversion)

Or

/Tmp/reposit01.dbf (converted file ifconversion had been required)

 

2.6. Import metadata

-- Use imp

$ Imp userid = \ '/As sysdba \ 'Transport _ tablespace = y
Datafiles =/database/db101b2/v101b2/datafile/reposit01.dbf
(Or/tmp/reposit01.dbf)
File = TTS. dmp log = imp_tts.log

Import: Release 10.2.0.4.0-on Mon Nov 26 03:37:20 2007

Export File Created by export: v10.02.00 via conventional path
About to import transportable tablespace (s) Metadata...
...
. Importing sys's objects into sys
. Importing omwb's objects into omwb
... Importing table "mtg_col_dep_chg"
...
.. Importing table "sybase11_sysusers"
Import terminated successfully without warnings.

Review the import log for warnings and errors and resolve issues
Before continuing. Failure to do so can result in data loss.

-- Use expdp:

If we exported with DataPump, import mustbe done with that same tool:

Impdp \ '/As sysdba \' directory = tts_dumpdumpfile = tts1_dp.dmp logfile = tts_dump_log: TTS. Log
Transport_datafiles = '/database/oradata/tts1_db1.dbf','/database/oradata/tts2_db1.dbf'

It's not possible to import when tablespace already exists or when targetschema is not created.
If users don't exist, DataPump provides an alternative by using remap_schema (for import utility we can create the schema), ie:
Remap_schema = <source_user >:< target_user>

-- If the impdp user does not exist, use the remap_schema parameter of impdp to convert the user.

 

 

If tablespace already exists in target, wecan use remap_tablespace parameter on impdp (there is no option in importbut rename tablespace at source or the existing one at target ).
Remap_tablespace = (<source_tbs1 >:< target_tbs1>, <source_tbs2 >:< target_tbs2> ,...)

-- If the tablespace already exists on the target, you can use the remap_tablespace parameter to convert the tablespace.

 

 

2.7. Set the imported tablespace To Read Write

-- After the operation, set the tablespace to read/write mode.

SQL> alter tablespace reposit readwrite;
Tablespace altered.

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Skype: tianlesoftware

Email: tianlesoftware@gmail.com

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

LinkedIn: http://cn.linkedin.com/in/tianlesoftware

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940