Transcript Moving Oracle Databases Across Platforms without Export/Import

Moving Oracle Databases
Across Platforms without
Export/Import
Roger Schrag
Database Specialists, Inc.
www.dbspecialists.com
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
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
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)
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
Process Overview
1.
2.
3.
4.
5.
6.
7.
8.
9.
Verify platform support
Identify tablespaces
Check data types
Check schemas and names
Read-only on source
Export metadata from source
Copy and convert files
Import metadata to target
Copy additional objects manually
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
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
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
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)
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
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.
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.
Case Study
1. Verify platform support
2.
3.
4.
5.
6.
7.
8.
9.
Identify tablespaces
Check data types
Check schemas and names
Read-only on source
Export metadata from source
Copy and convert files
Import metadata to target
Copy additional objects manually
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.
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.
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.
Case Study
1. Verify platform support
2. Identify tablespaces
3.
4.
5.
6.
7.
8.
9.
Check data types
Check schemas and names
Read-only on source
Export metadata from source
Copy and convert files
Import metadata to target
Copy additional objects manually
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
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.
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)
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
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
Sample Violations
SQL> SELECT * FROM SYS.transport_set_violations;
VIOLATIONS
------------------------------------------------------------------------Index MY_SCHEMA.MY_INDEX in tablespace IND1 points to table
MY_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>
Case Study
1. Verify platform support
2. Identify tablespaces
3. Check data types
4.
5.
6.
7.
8.
9.
Check schemas and names
Read-only on source
Export metadata from source
Copy and convert files
Import metadata to target
Copy additional objects manually
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
Check Data Types
SQL>
2
3
4
5
6
7
SELECT
FROM
WHERE
AND
AND
GROUP BY
ORDER BY
B.data_type, COUNT(*)
dba_tables A, dba_tab_columns B
A.owner = 'DBRD'
B.owner = A.owner
B.table_name = A.table_name
B.data_type
B.data_type;
DATA_TYPE
COUNT(*)
------------ ---------CLOB
3
DATE
153
NUMBER
207
VARCHAR2
237
 No problematic data types here
Check Data Types
SQL>
2
3
4
5
SELECT
FROM
WHERE
AND
AND
B.owner, B.table_name
dba_xml_tables A, all_all_tables B
B.owner = A.owner
B.table_name = A.table_name
B.tablespace_name IN ('TAB1', 'IND1');
no rows selected
SQL>
 No XMLTypes here
Case Study
1. Verify platform support
2. Identify tablespaces
3. Check data types
4. Check schemas and names
5.
6.
7.
8.
9.
Read-only on source
Export metadata from source
Copy and convert files
Import metadata to target
Copy additional objects manually
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.
Transported Schemas
 On source database:
SQL>
2
3
4
SELECT
FROM
WHERE
GROUP BY
owner, COUNT(*)
dba_segments
tablespace_name IN ('TAB1', 'IND1')
owner;
OWNER
COUNT(*)
------------------------------ ---------DBRD
188
 On target database:
SQL> SELECT username
2 FROM
dba_users
3 WHERE username = 'DBRD';
no rows selected
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>
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.
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
Case Study
1.
2.
3.
4.
Verify platform support
Identify tablespaces
Check data types
Check schemas and names
5. Read-only on source
6.
7.
8.
9.
Export metadata from source
Copy and convert files
Import metadata to target
Copy additional objects manually
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>
Case Study
1.
2.
3.
4.
5.
Verify platform support
Identify tablespaces
Check data types
Check schemas and names
Read-only on source
6. Export metadata from source
7. Copy and convert files
8. Import metadata to target
9. Copy additional objects manually
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
Export Metadata
Export: Release 10.2.0.2.0 - Production on Mon Dec 18 12:58:00 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit
Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About 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_LINES
For tablespace IND1 ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
Case Study
1.
2.
3.
4.
5.
6.
Verify platform support
Identify tablespaces
Check data types
Check schemas and names
Read-only on source
Export metadata from source
7. Copy and convert files
8. Import metadata to target
9. Copy additional objects manually
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
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.
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, ind1
2>
TO PLATFORM = "Linux IA (32-bit)"
3>
DB_FILE_NAME_CONVERT ('/u03/oradata/PROD417/', '/u03/stage/');
Starting backup at 19-DEC-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=149 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
Source Server (continued)
input datafile fno=00008 name=/u03/oradata/PROD417/tab103.dbf
converted datafile=/u03/stage/tab103.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time:
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=/u03/oradata/PROD417/ind101.dbf
converted datafile=/u03/stage/ind101.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time:
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00005 name=/u03/oradata/PROD417/tab101.dbf
converted datafile=/u03/stage/tab101.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time:
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00007 name=/u03/oradata/PROD417/tab102.dbf
converted datafile=/u03/stage/tab102.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time:
Finished backup at 19-DEC-06
RMAN> EXIT
Recovery Manager complete.
$
00:05:36
00:03:55
00:01:55
00:02:17
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-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
Target Server (continued)
input filename=/u01/stage/tab103.dbf
converted datafile=/u01/oradata/PROD463/tab103.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/stage/ind101.dbf
converted datafile=/u01/oradata/PROD463/ind101.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/stage/tab101.dbf
converted datafile=/u01/oradata/PROD463/tab101.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/stage/tab102.dbf
converted datafile=/u01/oradata/PROD463/tab102.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed
Finished backup at 20-DEC-06
RMAN> EXIT
Recovery Manager complete.
$
time: 00:05:47
time: 00:04:16
time: 00:01:16
time: 00:01:26
Case Study
1.
2.
3.
4.
5.
6.
7.
Verify platform support
Identify tablespaces
Check data types
Check schemas and names
Read-only on source
Export metadata from source
Copy and convert files
8. Import metadata to target
9. Copy additional objects manually
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
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 –
Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
About 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 SYS
Import terminated successfully without warnings.
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>
Case Study
1.
2.
3.
4.
5.
6.
7.
8.
Verify platform support
Identify tablespaces
Check data types
Check schemas and names
Read-only on source
Export metadata from source
Copy and convert files
Import metadata to target
9. Copy additional objects manually
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.
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
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.
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 readwrite mode on Oracle 10g at least once before
transport across platforms
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.
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
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.
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!
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
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
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
Moving Oracle Databases
Across Platforms without
Export/Import
Roger Schrag
Database Specialists, Inc.
www.dbspecialists.com