Wednesday May 25, 2016

Transportable Tablespaces - Characters Sets - Same same but different?

All credits go to Don Wolf, an Oracle Advanced Customer Support engineer from Ohio as he dug out this information :-) Thanks Don!

Do database character sets have to match EXACTLY for Transportable Tablespaces?

That sounds like a simple question. When you look into our big slide deck the answer will be a straight "Yes". No doubts. Regardless if you would like to do Transportable Tablespaces or Full Transportable Export/Import your sources and your target's database character sets must be equal. Otherwise Data Pump won't allow you to process the meta data import.

But Don was wondering about slightly differing information in MOS notes and the documentation.
And asked me if I can clarify things. I couldn't. 

  • MOS Note:1454872.1
    Transportable Tablespace (TTS) Restrictions and Limitations: Details, Reference, and Version Where Applicable

    tells you:
  •  And the above documentation link then tells you:
    • The source and the destination databases must use compatible database character sets. That is, one of the following must be true:
      • The database character sets of the source and the target databases are the same.
      • The source database character set is a strict (binary) subset of the target database character set, and the following three conditions are true:
        • The source database is in version 10.1.0.3 or higher.
        • The tablespaces to be transported contain no table columns with character length semantics or the maximum character width is the same in both the source and target database character sets.
        • The tablespaces to be transported contain no columns with the CLOB data type, or the source and the target database character sets are both single-byte or both multibyte.
      • The source database character set is a strict (binary) subset of the target database character set, and the following two conditions are true:
        • The source database is in a version lower than 10.1.0.3.
        • The maximum character width is the same in the source and target database character sets.
          .
  • And furthermore from "Choosing a Character Set" section of Oracle 12.1  Database Globalization Support Guide:
    • Oracle Database does not maintain a list of all subset-superset pairs but it does maintain a list of binary subset-superset pairs that it recognizes in various situations such as checking compatibility of a transportable tablespace or a pluggable database. Table A-11 and Table A-12 list the binary subset-superset pairs recognized by Oracle Database.
    • In those tables the only binary subset-superset pairs involving AL32UTF8 are:
      • UTF8/AL32UTF8
      • US7ASCII/AL32UTF8
        .
  • This is not 100% congruent to the information provided in
    MOS Note 371556.1 - How to Migrate to different Endian Platform Using Transportable Tablespaces With RMAN
    saying: 
    "
    The source and target database must use the same character set and national character set."
    .

What is correct, what's not?

First of all the wording of "compatible character sets" seems to be gotten introduced with Oracle Database 11.2. 

In fact the scope for the target system has become broader in Oracle Database 11.2.  These rule here are correct as soon as your target database is an Oracle Database 11.2 or newer database release.

  • The source and the destination databases must use compatible database character sets. That is, one of the following must be true:
    • The database character sets of the source and the target databases are the same.
    • The source database character set is a strict (binary) subset of the target database character set, and the following three conditions are true:
      • The source database is in version 10.1.0.3 or higher.
      • The tablespaces to be transported contain no table columns with character length semantics or the maximum character width is the same in both the source and target database character sets.
      • The tablespaces to be transported contain no columns with the CLOB data type, or the source and the target database character sets are both single-byte or both multibyte.
    • The source database character set is a strict (binary) subset of the target database character set, and the following two conditions are true:
      • The source database is in a version lower than 10.1.0.3.
      • The maximum character width is the same in the source and target database character sets.
        .

--Mike

Wednesday Apr 20, 2016

Data Pump - Exclude Stats differently for TTS and FTEX

Nice little best practice for statistics and Data Pump when doing either Transportable Tablespaces or Full Transportable Export-Import (credits to Roy and Dean Gagne).
.

Transport Statistics via a Staging Table

First of all we always recommend to exclude statistics when doing a Data Pump export as the import of such stats takes way longer than transporting them via a stats table. If you are unfamiliar with transporting stats between databases please see the Oracle Performance Tuning Guide with a nice tutorial

The basic steps to transport statistics from one database to another fast and efficient consist of: 

  1. Create a staging table in your source database with DBMS_STATS.CREATE_STAT_TABLE
  2. Export your local stats into this staging table by using DBMS_STATS.EXPORT_SCHEMA_STATS
  3. Export the staging table and import it into your destination database with Data Pump
  4. Import the statistics held in the staging table by using DBMS_STATS.IMPORT_SCHEMA_STATS

For the regular Data Pump exports we always recommend to set:

EXCLUDE=STATISTICS

to avoid performance penalties during impdp.

But this does not affect Transportable Tablespaces and Full Transportable Export/Import.
.

How to exclude Statistics for TTS and FTEX?

For reasons I don't know the metadata heterogeneous object for "transportable" is different than all of the others Therefore in order to exclude statistics for Transportable Tablespaces and Full Transportable Export/Import you must set:

EXCLUDE=TABLE_STATISTICS,INDEX_STATISTICS

Friday Feb 10, 2012

Note:1389592.1 Public - Speed Up xTTS

As we have already mentioned in our recent workshops the MOS Note:1389592.1 is public now:

  • MOS Note:1389592.1
    Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backups

This technique currently works for migrations to Linux64 and is supported by Oracle Support for Exadata migrations only. It utiliuzes RMAN incremental backups to reduce the large amount of downtime it takes to copy and convert the datafiles cross Endianness.

Two requirements must be met to use this new functionality:

  • Oracle 11.2.0.2 plus Exadata BP12 (patch 12982245)
  • One-off patch 13340675 contains the RMAN extension and is currently available on top of BP12 on Oracle 11.2.0.2

Let me know if you have further questions - and this restriction on Oracle 11.2.0.2 with BP12 applies to the intermediate instance you'll use to roll forward your converted incremental backups only. Source might be Oracle 10.2 whereas target might be also an Oracle 11.2.0.3 installation.

Thursday May 12, 2011

Recap: Upgrade Workshop in Warsaw

Thanks to everybody who did attend on a bright nice sunny day to our workshop in Warsaw today - in the most unusal venue I've had so far for a workshop: a cinema :-)

And please download the newest version of the slides via this link.

Warsaw


Thanks :-)
About

Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle

Based in Germany. Interlink between customers/partners and the Upgrade Development. Running workshops between Arctic and Antartica. Assisting customers in their reference projects onsite and remotely. Connect via:

- -

Search

Archives
« June 2016
SunMonTueWedThuFriSat
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
17
18
19
21
22
23
24
25
26
27
28
29
30
  
       
Today
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
Workshop Map
x Oracle related Tech Blogs
This week on my Rega & Pono
Upgrade Reference Papers