March 5, 2018

EXPDP using an External Password Store - facing a new 'release' of a well known performance issue

After a while without blogging, here a new blog post talking about a very well known performance issue:

export (exp as well as expdp) is usually slower when using an TCP based TNS-Alias instead of setting ORACLE_SID. Remember? ;-) 

In a little more detail:
An "expdb system/manager@db directory= ..." takes (usually) more time than an
"export ORACLE_SID=DB; expdp system/manager directory= ..."

But what if You have to use an external password store - a wallet - to avoid clearly readable passwords either in a file or at the command line? One part of the whole procedure is, to define the TNS alias in tnsnames.ora - and most of us define a TCP based alias. This is - imho - not the best way of connecting a local database - IPC or BEQ are way better for that.

So, I solved an expdp performance issue by using an Bequeath based TNS alias:

Created a new wallet:
mkstore -wrl /u01/app/oracle/wallet -create

Created an alias to connect to the DB:
mkstore -wrl /u01/app/oracle/wallet -createCredential db_system_beq.world system manager

Added a BEQ connect description to my tnsnames.ora:
DB_SYSTEM_BEQ.WORLD =
  (DESCRIPTION =
    (ADDRESS =
          (PROTOCOL = BEQ)
          (PROGRAM = oracle)
          (ARGV0 = oracleDB)
          (ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=BEQ)))')
    )
    (CONNECT_DATA =
        (SERVICE_NAME = DB)
    )

  )

Set the environment for my db and issued an expdp:
export ORACLE_SID=DB; expdb /@db_system_beq.world directory= ...

Result:

Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Mon Mar 5 15:04:15 2018 elapsed 0 00:00:43 

Result when using a TCP based TNS alias:

Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Mon Mar 5 14:58:03 2018 elapsed 0 00:01:31 

Try it - and post Your results as comment.


Share:

0 comments:

Post a Comment

Copyright © Robert Crames' Oracle Database and Middleware Blog | Powered by Blogger
Design by SimpleWpThemes | Blogger Theme by NewBloggerThemes.com