moving oracle databases across platforms without export/import roger schrag database specialists,...
TRANSCRIPT
![Page 1: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/1.jpg)
Moving Oracle Databases Across Platforms without Export/Import
Roger SchragDatabase Specialists, Inc.www.dbspecialists.com
![Page 2: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/2.jpg)
Today's Session
Overview of cross-platform transportable tablespaces
Case study:– Project background– Nine steps– Actual commands used and screen output– Comparison to export/import method
Feature restrictions and limitations Things to keep in mind
![Page 3: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/3.jpg)
White Paper
Contains all of the material we will discuss today and more
Code samples and sample output are easier to read
Easier to cut and paste the code for testing on your system
Download from: www.dbspecialists.com/presentations
![Page 4: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/4.jpg)
Transportable Tablespaces
Introduced in Oracle 8i Quick way to copy data from one database to
another database on same platform:– Use export/import to copy metadata– Use FTP, SCP, etc. to copy data files
Fast—only metadata is exported and imported Great for:
– Sharing data between databases– Flowing data from one database to another– Moving a database to a new server (same platform)
![Page 5: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/5.jpg)
Cross-platform Transportable Tablespaces
Enhancement added in Oracle 10g release 1 allows transporting tablespaces between databases on different platforms
Process is similar to existing transportable tablespace feature– Additional restrictions– Possible extra steps
Enhancement added in Oracle 10g release 2 allows transporting entire database at once between platforms – Even more restrictions– Not covered in today’s session
![Page 6: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/6.jpg)
Process Overview
1. Verify platform support2. Identify tablespaces3. Check data types4. Check schemas and names5. Read-only on source6. Export metadata from source7. Copy and convert files8. Import metadata to target9. Copy additional objects manually
![Page 7: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/7.jpg)
Export/Import Method
How we used to move data across platforms One of the only supported methods before
Oracle 10g Effective but slow Not practical for large databases
![Page 8: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/8.jpg)
Uses for Cross-platform Transportable Tablespaces
Moving an existing database permanently to a server on a different platform– A one-time thing
Information sharing between databases on different platforms– Scheduled to happen automatically at regular
intervals– Not an uncommon need in today’s heterogeneous
environments
![Page 9: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/9.jpg)
Case Study
Project: Move database to new platform permanently with minimal down time
Database: Oracle 10g release 2 Existing server: Sun E450 / Solaris New server: Dell / RHEL 4 Database size: 8 Gb (small)
– Export/import method feasible but slower– We will compare speed of the two methods
![Page 10: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/10.jpg)
Oracle Versions
Project used Oracle 10.2.0.2.0 Other versions should behave similarly Minor differences to be expected:
– New features– Bug fixes– Usability improvements
Check documentation, Metalink for changes since this project took place (December, 2006)
![Page 11: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/11.jpg)
Data Pump
Introduced in Oracle 10g New and improved export/import tool:
– More features– Easier to manage long jobs– Faster (?)
“Original” export/import still supported Either data pump or export/import may be used
to transport tablespaces across platforms
![Page 12: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/12.jpg)
Choose Data Pump Because…
It’s new and cool. Data pump can transport BINARY_FLOAT and
BINARY_DOUBLE data types while export/import cannot.
When export/import transports a tablespace containing a materialized view, the materialized view gets converted into a regular table. Data pump transports materialized views as materialized views.
Helping Oracle Corporation find bugs in their products is fun.
![Page 13: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/13.jpg)
Choose Export/Import Because…
It works. It is stable and predictable. Some documented data pump bugs have a
recommended workaround published by Oracle Support that reads: “Use export/import.”
Data pump cannot transport XMLTypes, but export/import can.
Data pump’s performance and job management improvements are not significant when transporting tablespaces.
![Page 14: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/14.jpg)
Case Study
1. Verify platform support2. Identify tablespaces3. Check data types4. Check schemas and names5. Read-only on source6. Export metadata from source7. Copy and convert files8. Import metadata to target9. Copy additional objects manually
![Page 15: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/15.jpg)
Verify Platform Support
v$database shows what platform the database is running on.
v$transportable_platform shows all platforms supported by Oracle for cross-platform tablespace transport.– 10.2.0.2.0 supports 17 platforms including varieties
of: Linux, Solaris, HP-UX, AIX, Mac OS, Tru64, Open VMS, and Windows.
![Page 16: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/16.jpg)
Verify Platform Support
On the source database:
SQL> SELECT A.platform_id, A.platform_name, B.endian_format 2 FROM v$database A, v$transportable_platform B 3 WHERE B.platform_id (+) = A.platform_id;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT----------- ----------------------------------- -------------- 2 Solaris[tm] OE (64-bit) Big
SQL>
Solaris Operating Environment (SPARC) 64 bit is supported.– The endian_format column in this query’s output
would be blank if the platform were not supported.
![Page 17: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/17.jpg)
Verify Platform Support
On the target database:
SQL> SELECT A.platform_id, A.platform_name, B.endian_format 2 FROM v$database A, v$transportable_platform B 3 WHERE B.platform_id (+) = A.platform_id;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT----------- ----------------------------------- -------------- 10 Linux IA (32-bit) Little
SQL>
Linux (Intel architecture) 32 bit is supported. File conversion will be required because the
two platforms have different endian formats.
![Page 18: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/18.jpg)
Case Study
1. Verify platform support2. Identify tablespaces3. Check data types4. Check schemas and names5. Read-only on source6. Export metadata from source7. Copy and convert files8. Import metadata to target9. Copy additional objects manually
![Page 19: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/19.jpg)
Identify Tablespaces
Choose tablespaces to transport:– Can transport multiple tablespaces at once– Cannot transport part of a tablespace
Application data only:– No SYSTEM, undo, or temporary tablespaces
For this project: Transport all data in the DBRD schema.– All DBRD segments reside in the TAB1 and IND1
tablespaces
![Page 20: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/20.jpg)
Self-Containment
Set of tablespaces to be transported must be self-contained.– Objects in the tablespace set cannot reference or
depend on objects outside the set. Following would not work:
– Transport TAB1 and IND1– Table in TAB2 has index in IND1
Self-containment problems will cause metadata export to fail.
![Page 21: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/21.jpg)
A Few Self-Containment Problems
Table in tablespace set with LOB segment outside the set
LOB segment in tablespace set with table it belongs to outside the set
Table or index partition in tablespace set with one or more partitions outside the set
Table in tablespace set with index enforcing primary or unique key outside the set
Table in tablespace set with foreign key to table outside the set (only a problem if constraints are transported)
![Page 22: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/22.jpg)
Strict Self-Containment
Objects in the tablespace set do not reference objects outside of the set, and objects outside of the set do not reference objects in the set.
Strict self-containment is not required to transport tablespaces.
Transporting without strict self-containment poses possible side-effects. Example:– Transport TAB1 and IND1– Table in TAB1 and non-unique index in IND2– Table gets transported without index
![Page 23: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/23.jpg)
Check Self-Containment
SQL> BEGIN 2 SYS.dbms_tts.transport_set_check 3 ('TAB1,IND1', incl_constraints=>TRUE, full_check=>FALSE); 4 END; 5 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM SYS.transport_set_violations;
no rows selected
SQL>
No violations, so we can proceed with this tablespace set– Above did not check for strict self-containment
![Page 24: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/24.jpg)
Sample Violations
SQL> SELECT * FROM SYS.transport_set_violations;
VIOLATIONS-------------------------------------------------------------------------Index MY_SCHEMA.MY_INDEX in tablespace IND1 points to tableMY_SCHEMA.MY_TABLE in tablespace TAB2
SQL>
SQL> SELECT * FROM SYS.transport_set_violations;
VIOLATIONS-------------------------------------------------------------------------Constraint MY_CHILD_TABLE_FK1 between table MY_SCHEMA.MY_PARENT_TABLE in tablespace TAB2 and table MY_SCHEMA.MY_CHILD_TABLE in tablespace TAB1
SQL>
![Page 25: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/25.jpg)
Case Study
1. Verify platform support2. Identify tablespaces3. Check data types4. Check schemas and names5. Read-only on source6. Export metadata from source7. Copy and convert files8. Import metadata to target9. Copy additional objects manually
![Page 26: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/26.jpg)
Check Data Types
Check tablespace set for columns with problematic data types:– Data pump can’t transport XMLTypes– Original export can’t transport BINARY_FLOAT or
BINARY_DOUBLE– Manual conversion could be required for RAW,
LONG RAW, BFILE, ANYTYPE, user-defined data types
Proactive step; not strictly required
![Page 27: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/27.jpg)
Check Data Types
SQL> SELECT B.data_type, COUNT(*) 2 FROM dba_tables A, dba_tab_columns B 3 WHERE A.owner = 'DBRD' 4 AND B.owner = A.owner 5 AND B.table_name = A.table_name 6 GROUP BY B.data_type 7 ORDER BY B.data_type;
DATA_TYPE COUNT(*)------------ ----------CLOB 3DATE 153NUMBER 207VARCHAR2 237
No problematic data types here
![Page 28: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/28.jpg)
Check Data Types
SQL> SELECT B.owner, B.table_name 2 FROM dba_xml_tables A, all_all_tables B 3 WHERE B.owner = A.owner 4 AND B.table_name = A.table_name 5 AND B.tablespace_name IN ('TAB1', 'IND1');
no rows selected
SQL>
No XMLTypes here
![Page 29: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/29.jpg)
Case Study
1. Verify platform support2. Identify tablespaces3. Check data types4. Check schemas and names5. Read-only on source6. Export metadata from source7. Copy and convert files8. Import metadata to target9. Copy additional objects manually
![Page 30: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/30.jpg)
Check Schemas and Names
Schemas on target database must already exist.– You can “remap” schemas (transport objects into a
different schema) Tablespaces cannot be present on target
database with same names as tablespaces being transported.
Objects cannot be present on target database with same name and owner as objects being transported.
![Page 31: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/31.jpg)
Transported Schemas
On source database:SQL> SELECT owner, COUNT(*) 2 FROM dba_segments 3 WHERE tablespace_name IN ('TAB1', 'IND1') 4 GROUP BY owner;
OWNER COUNT(*)------------------------------ ----------DBRD 188
On target database:SQL> SELECT username 2 FROM dba_users 3 WHERE username = 'DBRD';
no rows selected
![Page 32: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/32.jpg)
Create Schema on Target
SQL> CREATE USER dbrd IDENTIFIED BY password;
User created.
SQL> GRANT connect, resource TO dbrd;
Grant succeeded.
SQL> GRANT create library TO dbrd;
Grant succeeded.
SQL> REVOKE unlimited tablespace FROM dbrd;
Revoke succeeded.
SQL>
![Page 33: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/33.jpg)
Check Tablespace Names
Verify tablespaces don’t already exist on target with same names as tablespaces being transported:SQL> SELECT tablespace_name 2 FROM dba_tablespaces 3 WHERE tablespace_name IN ('TAB1', 'IND1');
no rows selected
Could rename tablespaces (on source or target) if there had been duplicates:SQL> ALTER TABLESPACE old_tablespace_name RENAME TO new_name;
Tablespace altered.
![Page 34: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/34.jpg)
Check Object Names
Verify objects don’t already exist on target in same schemas with same names as objects being transported.– We know there can’t be duplicates on the target
database because we just created the DBRD schema there.
If there had been duplicates:– Could rename objects (on source or target)– Could transport objects into different schemas on
target
![Page 35: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/35.jpg)
Case Study
1. Verify platform support2. Identify tablespaces3. Check data types4. Check schemas and names5. Read-only on source6. Export metadata from source7. Copy and convert files8. Import metadata to target9. Copy additional objects manually
![Page 36: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/36.jpg)
Make Tablespaces Read-only
Tablespaces on source database must be put in read-only mode for long enough to:– Extract metadata– Copy data files
On source database:SQL> ALTER TABLESPACE tab1 READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE ind1 READ ONLY;
Tablespace altered.
SQL>
![Page 37: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/37.jpg)
Case Study
1. Verify platform support2. Identify tablespaces3. Check data types4. Check schemas and names5. Read-only on source6. Export metadata from source7. Copy and convert files8. Import metadata to target9. Copy additional objects manually
![Page 38: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/38.jpg)
Export Metadata
Extract metadata from source database Use original export or data pump Runs quickly (under 2 minutes in case study) Export file will be small (640 Kb in case study) On source database server: $ exp "'/ as sysdba'" file=PROD417_tab1ind1.dmp \ > transport_tablespace=y tablespaces=tab1,ind1
![Page 39: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/39.jpg)
Export Metadata
Export: Release 10.2.0.2.0 - Production on Mon Dec 18 12:58:00 2006Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bitProductionWith the Partitioning, OLAP and Data Mining optionsExport done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character setNote: table data (rows) will not be exportedAbout to export transportable tablespace metadata...For tablespace TAB1 .... exporting cluster definitions. exporting table definitions. . exporting table COMMON_BANNER_SETS. . exporting table COMMON_BANNER_TYPES.... . exporting table TXN_COMMENTS. . exporting table TXN_LINESFor tablespace IND1 .... exporting cluster definitions. exporting table definitions. exporting referential integrity constraints. exporting triggers. end transportable tablespace metadata exportExport terminated successfully without warnings.
![Page 40: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/40.jpg)
Case Study
1. Verify platform support2. Identify tablespaces3. Check data types4. Check schemas and names5. Read-only on source6. Export metadata from source7. Copy and convert files8. Import metadata to target9. Copy additional objects manually
![Page 41: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/41.jpg)
Copy Files to Target Server
Need to copy:– Dump file created by original export or data pump– All data files in tablespace set
Use any method: FTP, SCP, split mirror, unmount/mount, snapshot restore…
Change file names or paths as needed File conversion:
– Only necessary if source and target platforms have different endian format
– RMAN converts files on source or target server
![Page 42: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/42.jpg)
File Conversion
Only necessary if source and target platforms have different endian format– Necessary in our case because going from Big
endian to Little File conversion performed by RMAN
– db_file_name_convert parameter Convert files on source or target server:
– RMAN syntax varies slightly.– Consider load impact and server speed when
choosing where to convert files.
![Page 43: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/43.jpg)
Conversion on Source Server
$ rman
Recovery Manager: Release 10.2.0.2.0 - Production on Tue Dec 19 16:47:30 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> CONNECT TARGET
connected to target database: PROD417 (DBID=3437408061)
RMAN> CONVERT TABLESPACE tab1, ind12> TO PLATFORM = "Linux IA (32-bit)"3> DB_FILE_NAME_CONVERT ('/u03/oradata/PROD417/', '/u03/stage/');
Starting backup at 19-DEC-06using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=149 devtype=DISKchannel ORA_DISK_1: starting datafile conversion
![Page 44: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/44.jpg)
Source Server (continued)
input datafile fno=00008 name=/u03/oradata/PROD417/tab103.dbfconverted datafile=/u03/stage/tab103.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:05:36channel ORA_DISK_1: starting datafile conversioninput datafile fno=00006 name=/u03/oradata/PROD417/ind101.dbfconverted datafile=/u03/stage/ind101.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:03:55channel ORA_DISK_1: starting datafile conversioninput datafile fno=00005 name=/u03/oradata/PROD417/tab101.dbfconverted datafile=/u03/stage/tab101.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:55channel ORA_DISK_1: starting datafile conversioninput datafile fno=00007 name=/u03/oradata/PROD417/tab102.dbfconverted datafile=/u03/stage/tab102.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:02:17Finished backup at 19-DEC-06
RMAN> EXIT
Recovery Manager complete.$
![Page 45: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/45.jpg)
Conversion on Target Server
$ rman
Recovery Manager: Release 10.2.0.2.0 - Production on Wed Dec 20 10:11:38 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> CONNECT TARGET
connected to target database: PROD463 (DBID=2124019545)
RMAN> CONVERT DATAFILE '/u01/stage/tab101.dbf',2> '/u01/stage/tab102.dbf',3> '/u01/stage/tab103.dbf',4> '/u01/stage/ind101.dbf'5> FROM PLATFORM "Solaris[tm] OE (64-bit)"6> DB_FILE_NAME_CONVERT ('/u01/stage/',7> '/u01/oradata/PROD463/');
Starting backup at 20-DEC-06using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=145 devtype=DISKchannel ORA_DISK_1: starting datafile conversion
![Page 46: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/46.jpg)
Target Server (continued)
input filename=/u01/stage/tab103.dbfconverted datafile=/u01/oradata/PROD463/tab103.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:05:47channel ORA_DISK_1: starting datafile conversioninput filename=/u01/stage/ind101.dbfconverted datafile=/u01/oradata/PROD463/ind101.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:04:16channel ORA_DISK_1: starting datafile conversioninput filename=/u01/stage/tab101.dbfconverted datafile=/u01/oradata/PROD463/tab101.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:16channel ORA_DISK_1: starting datafile conversioninput filename=/u01/stage/tab102.dbfconverted datafile=/u01/oradata/PROD463/tab102.dbfchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:26Finished backup at 20-DEC-06
RMAN> EXIT
Recovery Manager complete.$
![Page 47: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/47.jpg)
Case Study
1. Verify platform support2. Identify tablespaces3. Check data types4. Check schemas and names5. Read-only on source6. Export metadata from source7. Copy and convert files8. Import metadata to target9. Copy additional objects manually
![Page 48: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/48.jpg)
Import Metadata
“Plug in” the tablespaces– Copied data files become part of target database– Schema objects get added to data dictionary
Runs quickly (under 2 minutes in case study) On target database server:
$ imp "'/ as sysdba'" file=PROD417_tab1ind1.dmp transport_tablespace=y \> datafiles=/u01/oradata/PROD463/ind101.dbf, \> /u01/oradata/PROD463/tab101.dbf, \> /u01/oradata/PROD463/tab102.dbf, \> /u01/oradata/PROD463/tab103.dbf
![Page 49: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/49.jpg)
Import Metadata
Import: Release 10.2.0.2.0 - Production on Wed Dec 20 16:32:51 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – ProductionWith the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional pathAbout to import transportable tablespace(s) metadata...import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set. importing SYS's objects into SYS. importing SYS's objects into SYS. importing DBRD's objects into DBRD. . importing table "COMMON_BANNER_SETS". . importing table "COMMON_BANNER_TYPES".... . importing table "TXN_COMMENTS". . importing table "TXN_LINES"About to enable constraints.... importing SYS's objects into SYSImport terminated successfully without warnings.
![Page 50: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/50.jpg)
Import Metadata
Could specify fromuser and touser parameters to import objects into different schemas– remap_schema if using data pump
Transported data is now available in target database read-only
Can change tablespaces to read-write mode:SQL> ALTER TABLESPACE tab1 READ WRITE;
Tablespace altered.
SQL> ALTER TABLESPACE ind1 READ WRITE;
Tablespace altered.
SQL>
![Page 51: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/51.jpg)
Case Study
1. Verify platform support2. Identify tablespaces3. Check data types4. Check schemas and names5. Read-only on source6. Export metadata from source7. Copy and convert files8. Import metadata to target9. Copy additional objects manually
![Page 52: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/52.jpg)
Copy Additional Objects
Many types of schema objects are not transported with this method. Some examples:– Synonyms– Stored procedures– PL/SQL packages
Copy desired objects another way:– TOAD– dbms_metadata– Original export with rows=n– …
These objects copy quickly because there is no segment data involved.
![Page 53: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/53.jpg)
Comparison of Methods
Export/Import
Tablespace Transport
Export time 37 min 2 min
File transfer time 8 min 13 min
File conversion time n/a 14 min
Import time 42 min 2 min
Approximate total time 87 min 31 min
Export file size 4100 Mb 640 Kb
Target database extra TEMP tablespace requirement
1200 Mb n/a
![Page 54: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/54.jpg)
Comparison of Methods
Cross-platform tablespace transport reduced running time by 60% for our project.
Could have saved even more time:– Shrink data files on source database– Faster network– Faster or more sophisticated I/O system
Transporting larger volumes of data should yield even more impressive savings.– So will transports that don’t require file conversion.
![Page 55: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/55.jpg)
Restrictions
Only supported for certain platforms Tablespaces cannot be transported from
Standard Edition databases (but you can transport tablespaces into Standard Edition databases)
Source and target database must be Oracle 10g or later– compatible parameter set to 10.0 or higher
A tablespace transported from a pre-Oracle 10g database into Oracle 10g must be put in read-write mode on Oracle 10g at least once before transport across platforms
![Page 56: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/56.jpg)
Restrictions
You cannot plug a tablespace into a database that doesn’t have a buffer cache for the tablespace’s block size.– Add a suitable buffer cache dynamically with a
statement like:
SQL> ALTER SYSTEM SET db_32k_cache_size = 160m;
System altered.
SQL>
You cannot plug a tablespace into a database that uses a different character set.
![Page 57: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/57.jpg)
Things to Keep in Mind
Transportable tablespace feature focuses on moving data, not schema objects:– Moves data quickly and efficiently– Many schema object types are left up to you to copy
by other means– Table 19-3 in Oracle 10gR2 Database Utilities
manual lists which object types are transported
![Page 58: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/58.jpg)
Things to Keep in Mind
Self-containment nuances:– Strict and non-strict self-containment– Default behavior is non-strict– Can lead to missing indexes
File transfer and conversion time are roughly proportional to file size:– RMAN does not appear to skip unused blocks.– Consider shrinking data files to highwater mark on
source database to speed up transport.
![Page 59: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/59.jpg)
Disclaimer
Cross-platform tablespace transport worked well for our project, but remember:– The feature is still pretty new.– The feature is not fully mature at this point.
Expect:– New functionality and enhancements– Refinement of existing functionality– Possible reduction in limitations and restrictions
Evaluate thoroughly in a test environment before using in production!
![Page 60: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/60.jpg)
In Conclusion
Transportable tablespaces introduced in Oracle 8i
Enhanced in Oracle 10g to allow transport across platforms
Makes it faster and more efficient to:– Move a database from one platform to another– Share information between databases on different
platforms
![Page 61: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/61.jpg)
White Paper
Contains all of the material we discussed today and more
Code samples and sample output are easier to read
Easier to cut and paste the code for testing on your system
Download from: www.dbspecialists.com/presentations
![Page 62: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/62.jpg)
About Database Specialists
Database Specialists, Inc. provides Oracle database consulting in Solaris, Linux, HP-UX, AIX, and Windows environments.
Our DBA Pro offering and Database Rx™ tools provide remote database support and 24/7 coverage at an attractive price point.
We specialize in short term projects including upgrades, performance tuning and health checks.
Our Oracle DBAs each have a minimum of 10 years of Oracle experience with a focus on Oracle technology, mission-critical production support and RAC environments.
Database Specialists is US-based.
Database Specialists helps you increase uptime, improve performance,
minimize risk, and reduce costs
Database Specialists helps you increase uptime, improve performance,
minimize risk, and reduce costs
![Page 63: Moving Oracle Databases Across Platforms without Export/Import Roger Schrag Database Specialists, Inc](https://reader031.vdocuments.us/reader031/viewer/2022032708/56649e715503460f94b705fb/html5/thumbnails/63.jpg)
Moving Oracle Databases Across Platforms without Export/Import
Roger SchragDatabase Specialists, Inc.www.dbspecialists.com