Monday Jun 27, 2016

Full Transportable Export/Import - Migrating an 11.2.0.4 database to Oracle Database 12c- into the Oracle Cloud

Full Transportable Export/Import - one of the coolest features in Oracle Database 12c 

We blogged about Full Transportable Export/Import a while back. It is - no doubt - one of the coolest features in Oracle Database 12c. And it is part of our Hands-On Lab exercise (Hands On Lab - Upgrade, Migrate, Consolidate to Oracle Database 12c) as well.

It utilizes the technique of Transportable Tablesspaces - cross-platform, cross- Endianness, cross-version - but lets Oracle Data Pump do all the "dirty" work of rebuilding everything kept in your SYSTEM and SYSAUX tablespace including views, synonyms, public objects, grants, sequences etc etc etc.

You can even combine it with RMAN Incremental Backups - of course cross-platform and cross-Endianness as well - in order to decrease the downtime for large databases.
Please find our presentation about the combination of these feature here in the Slides Download Center to the right:

In addition: You can use this Oracle Database 12c feature with your source database Oracle 11.2.0.3 or 11.2.0.4. Just the destination database you are upgrading or migrating to must be at least an Oracle 12.1.0.1 database (or newer of course). 

See the feature in action

Roy recorded a 20 minute video demonstrating how to:

  • Migrate an Oracle Database 11.2.0.4 database into an Multitenant's pluggable database using
  • Full Transportable Export/Import
  • into the Oracle DBaaS Cloud
    .

And of course the feature works also without migrating into a Container database environment - and without migrating to the Oracle DBaaS Cloud. 

If you need to migrate a database from Oracle 11.2.0.3 or newer to Oracle Database 12c Full Transportable Export/Import can become your best friend.

--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

Thursday Jul 18, 2013

Full Transportable Export/Import White Paper

One of the really cool and helpful features in Oracle Database 12c is called Full Transportable Export/Import. It combines the basics of transportable tablespaces - of course cross-platform, cross-endianess, cross-version - with Data Pump taking care on all the stuff not stored in tables and indexes, such as views, synonyms, trigger, packages etc. And you can even reduce downtime by combining the feature with incrementally converted RMAN backups.

And the best thing: this will work with an Oracle Database 11.2.0.3/4 to Oracle Database 12c - so you don't have to be on Oracle Database 12c in order to leverage from that cool new feature.

See the brand new White Paper about Full Transportable Export/Import here:
http://www.oracle.com/technetwork/database/enterprise-edition/full-transportable-wp-12c-1973971.pdf

-Mike

Monday Dec 05, 2011

Exclude DBMS_SCHEDULER Jobs from expdp?

You have never thought about excluding DBMS_SCHEDULER jobs from a Data Pump export? Me neither but I've recently got a copy of an email for such a customer case from Roy who owns Data Pump as well. And this is the code example from Dean Gagne:

exclude.par:
exclude=procobj:"IN (SELECT NAME FROM sys.OBJ$ WHERE TYPE# IN (47,48,66,67,68,69,71,72,74))"

  • This will work only on export
  • It's an all or nothing approach

Quite interesting, isn't it? 

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
« July 2016
SunMonTueWedThuFriSat
     
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
      
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