Thinking Out Loud

September 30, 2012

Cross Platform Transportable Tablespace (XTTS) and Data Pump

Filed under: 11g,DataPump,oracle — mdinh @ 12:00 am

There’s a requirement for cross platform database migration from Solaris Operating System (SPARC) (64-bit) to Solaris Operating System (x86-64) and ideas started flying around.

CONVERT DATABASE cannot be used because of endian change.

Dataguard was considered and there are limitations as well.

Data Guard Support for Heterogeneous Primary and Logical Standbys in Same Data Guard Configuration [ID 1085687.1]
Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration [ID 413484.1]

Because of source and target, neither options were available.

Two other options were explored: Cross Platform Transportable Tablespace (XTTS) and Data Pump.

Here’s a brief overview for XTTS:

  1. Verify tablespace is self-contained.
  2. Set the tablespace to READ ONLY.
  3. Export metadata at source
  4. Endian Conversion – Convert at SOURCE: convert tablespace / Convert at TARGET: convert datafile
  5. Transfer datafiles and export dump file to target.
  6. Set the source tablespace to READ WRITE.
  7. Import metadata at target
  8. Set the imported tablespace to READ WRITE

For the test case, endian conversion was performed at source using convert tablespace because it’s much simpler with 3 tablespaces and 45 data files.

The platform name can be determined using the SQL:

SELECT tp.platform_id, tp.PLATFORM_NAME, ENDIAN_FORMAT,d.name
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME(+)
ORDER BY name, endian_format, platform_id;

Using RMAN for Conversion:

configure device type disk parallelism 6 backup type to backupset;
configure archivelog deletion policy to none;
convert tablespace tbs1,tbs2,tbs3
to platform="Solaris Operating System (x86-64)"
db_file_name_convert '/oracle/oradata/source','/oracle/oradata/target';

Caveat – TRANSPORT_TABLESPACES do not contain: db_link,function,package,procedure,sequence,synonym,view, to name a few.

Summary of results:

XTTS took 593 minutes and Data Pump took 663 minutes.

However, REF_CONSTRAINT took 337 minutes.

Is it necessary to enable REF_CONSTRAINT if the tablesspace is in READ ONLY or the export is consistent???

Without enabling REF_CONSTRAINT, the total Data Pump time is reduced to 326 minutes.

RMAN CONVERT tablespace total 704GB for all data files and total USED tablespace is 692GB.

Of interest, the total USED tablespace is 445GB when imported at target.

That’s a difference 0f 247GB !!!

Action

Mins

expdp schema parallel(18) 68
impdp table parallel(21) 68
index parallel(16) 159
constraint 15
stats parallel(24) 16
subtotal 326
ref_constraint 337
total 663
expdp_tts 183
dbf conversion parallel(6) 274
impdp_tts 136
total 593

References:

Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges) [ID 351598.1]
Best Practices for Using Transportable Tablespaces (TTS) [ID 1457876.1]
Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backups [ID 1389592.1]

September 19, 2012

SQL*Plus Timing Start

Filed under: Uncategorized — mdinh @ 1:49 pm

I just learned about this feature.

Example using SQL*Plus 11.2

12:56:58 MDINH@db01:PRIMARY> timing start test1
12:57:07 MDINH@db01:PRIMARY>
12:57:21 MDINH@db01:PRIMARY> timing start test2
12:57:23 MDINH@db01:PRIMARY>
12:57:36 MDINH@db01:PRIMARY> timing stop test2
timing for: test2
Elapsed: 00:00:16.33
12:57:40 MDINH@db01:PRIMARY> timing stop test1
timing for: test1
Elapsed: 00:00:48.96
12:57:56 MDINH@db01:PRIMARY>

September 9, 2012

Overconfidence precedes carelessness

Filed under: 11g,oracle — mdinh @ 3:27 am

I had manager who inspired confidence by asking, “If you don’t have confidence in yourself, then who will?”

At the same time, overconfidence precedes carelessness and the mistake humbles us.

So there I was, applying patch 14351543 for Oracle Solaris on x86-64 (64-bit) and patching completed successfully.

Verifying the update...
Patch 14351543 successfully applied
Log file location: /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/14351543_Sep_05_2012_08_44_45/apply2012-09-05_08-44-44AM_1.log
OPatch succeeded.

Starting the listener error. Uh Oh!

> lsnrctl start
ld.so.1: lsnrctl: fatal: libclntsh.so.11.1: open failed: No such file or directory
Killed

It’s a good thing Oracle has a rollback feature, but completed with warning.

RollbackSession removing interim patch '14351543' from inventory
--------------------------------------------------------------------------------
The following warnings have occurred during OPatch execution:
1) OUI-67200:Make failed to invoke "/usr/ccs/bin/make -f ins_rdbms.mk irenamedg ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1"... .'ld: fatal: library -lclntsh: not found
ld: fatal: File processing errors. No output written to /u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms/lib/renamedg
make: Fatal error: Command failed for target `/u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms/lib/renamedg'
'
2) OUI-67124:Re-link fails on target "irenamedg".
--------------------------------------------------------------------------------
OPatch Session completed with warnings.
Log file location: /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/14351543_Sep_05_2012_08_54_03/rollback2012-09-05_08-54-03 AM_1.log
OPatch completed with warnings.

Listener and database started up okay. In addition, Oracle support provided instructions to relink all, reviewed the log file and found to be okay.

In the future, to prevent single point of failure, clone Oracle home.

My notes for cloning Oracle home thanks to references from FRITS HOOGLAND WEBLOG: http://bit.ly/RLmeCo

September 5, 2012

Patch History – DBA_REGISTRY_HISTORY

Filed under: 11g,oracle — mdinh @ 4:15 am

Just a quick note to show patch history.

No history just yet until catbunble.sql has been executed.

SQL> @pr 'select * from DBA_REGISTRY_HISTORY';
==============================
PL/SQL procedure successfully completed.

Executing catbundle.sql

SQL> @?/rdbms/admin/catbundle.sql psu apply
...
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_HA01_APPLY_2012Sep04_11_15_44.log
SQL> @pr 'select * from DBA_REGISTRY_HISTORY';
==============================
ACTION_TIME                   : 04-SEP-12 11.16.55.741436 AM
ACTION                        : APPLY
NAMESPACE                     : SERVER
VERSION                       : 11.2.0.3
ID                            : 3
BUNDLE_SERIES                 : PSU
COMMENTS                      : PSU 11.2.0.3.3
==============================
PL/SQL procedure successfully completed.

Some more examples:

SQL> @pr 'select * from DBA_REGISTRY_HISTORY order by action_time desc';
==============================
ACTION_TIME                   : 13-MAY-12 08.39.12.947464 PM
ACTION                        : APPLY
NAMESPACE                     : SERVER
VERSION                       : 11.2.0.3
ID                            : 0
BUNDLE_SERIES                 : PSU
COMMENTS                      : Patchset 11.2.0.2.0
==============================
ACTION_TIME                   : 13-MAY-12 08.38.28.893140 PM
ACTION                        : UPGRADE
NAMESPACE                     : SERVER
VERSION                       : 11.2.0.3.0
ID                            :
BUNDLE_SERIES                 :
COMMENTS                      : Upgraded from 11.2.0.2.0
==============================
ACTION_TIME                   : 13-MAY-12 08.38.16.903012 PM
ACTION                        : VIEW INVALIDATE
NAMESPACE                     :
VERSION                       :
ID                            : 8289601
BUNDLE_SERIES                 :
COMMENTS                      : view invalidation
==============================
ACTION_TIME                   : 11-DEC-10 09.23.47.556462 PM
ACTION                        : APPLY
NAMESPACE                     : SERVER
VERSION                       : 11.2.0.2
ID                            : 0
BUNDLE_SERIES                 : PSU
COMMENTS                      : Patchset 11.2.0.2.0
==============================
ACTION_TIME                   : 11-DEC-10 09.21.30.935646 PM
ACTION                        : UPGRADE
NAMESPACE                     : SERVER
VERSION                       : 11.2.0.2.0
ID                            :
BUNDLE_SERIES                 :
COMMENTS                      : Upgraded from 10.2.0.4.0
==============================
ACTION_TIME                   : 11-DEC-10 09.21.19.113562 PM
ACTION                        : VIEW INVALIDATE
NAMESPACE                     :
VERSION                       :
ID                            : 8289601
BUNDLE_SERIES                 :
COMMENTS                      : view invalidation
==============================
ACTION_TIME                   : 05-OCT-08 07.32.45.614748 PM
ACTION                        : UPGRADE
NAMESPACE                     : SERVER
VERSION                       : 10.2.0.4.0
ID                            :
BUNDLE_SERIES                 :
COMMENTS                      : Upgraded from 10.2.0.3.0
==============================
ACTION_TIME                   : 10-MAY-08 07.15.22.132383 PM
ACTION                        : CPU
NAMESPACE                     : SERVER
VERSION                       : 10.2.0.3.0
ID                            : 6864068
BUNDLE_SERIES                 :
COMMENTS                      : CPUApr2008
==============================
ACTION_TIME                   : 20-MAY-07 07.35.03.588584 PM
ACTION                        : UPGRADE
NAMESPACE                     : SERVER
VERSION                       : 10.2.0.3.0
ID                            :
BUNDLE_SERIES                 :
COMMENTS                      : Upgraded from 10.2.0.2.0
==============================

PL/SQL procedure successfully completed.

September 1, 2012

GoldenGate Integrated Extract and Conflicting PSU

Filed under: GoldenGate,oracle — mdinh @ 3:01 pm

Note to self to always check the document below before applying any Patch Set Update (PSU) to save time and headaches.

11.2.0.3 Database specific bundle patch for Integrated Extract 11.2.x [ID 1411356.1]

For non-Exadata customers on 11.2.0.3.0,the latest recommended patch of IE (Integrated Extract) is 14160397.
For non-Exadata customers on 11.2.0.3.3 psu, the patch number is 14351543 – but not all platforms are available for 11.2.0.3.3 yet.

Have fun patching.

Create a free website or blog at WordPress.com.