Oracle DBA – A lifelong learning experience

expdp to ASM and exclude table syntax – two workarounds

Posted by John Hallas on September 2, 2013

A short entry showing two problems that I have come across recently with datapump export and the  workarounds I used.

Using ASM disk for exports

Quite often it is hard to find sufficient space to export a large amount of data, especially if trying to use a filesystem. However with the growing use of ASM there is often plenty of disk space to be found in that area. The stumbling block is that the ASM area cannot hold trace files, audit files, alert logs, export files, tar files, and core files and therefore you get an error message even though you have created an export directory on the ASM disk group as requested. This is the same from 11GR1 through to 12C

I have created the directory and also created the folder within the ASM diskgroup

asmcmd –p

mkdir ‘+DATA/john_exp’

sqlplus

create directory john_exp as ‘‘+DATA/john_exp’  -- grant permissions if necessary

expdp schemas=system directory=john_exp dumpfile=john_exp.dmp content=metadata_only  logfile=john_exp.log

Export: Release 11.2.0.3.0 - Production on Mon Sep 2 13:13:59 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-29283: invalid file operation

ORA-06512: at "SYS.UTL_FILE", line 536

ORA-29283: invalid file operation

The trick is to use another, probably existing directory, such as DATA_PUMP_DIR which is placed on a filesystem  and point the logfile there, using the syntax ‘logfile=directory:logfile’. An alternative option is to use the expdp parameter nologfile=yes

expdp schemas=system directory=john_exp dumpfile=john_exp.dmp content=metadata_only  logfile=data_pump_dir:john_exp.log

Export: Release 11.2.0.3.0 - Production on Mon Sep 2 13:14:20 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA schemas=system directory=john_exp dumpfile=john_exp.dmp content=metadata_only logfile=data_pump_dir:john_exp.log

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

….

******************************************************************************

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

+DATA/john_exp/john_exp.dmp

Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:16:49

Syntax to exclude a table in a data pump export

After spending ages trying to get the syntax of an EXCLUDE table parameter working correctly from the command line of a Linux server I finally gave up and actually read the manual. There I found the following helpful advice :-

Some operating systems require that quotation marks on the command line be preceded by an escape character, such as the backslash. If the backslashes were not present, the command-line parser that Export uses would not understand the quotation marks and would remove them, resulting in an error. In general, Oracle recommends that you place such statements in a parameter file because escape characters are not necessary in parameter files.

My example was a schema export but we did not need a very large table to be included. This is the syntax which should work and indeed did when using a parameter file

expdp schemas=sm7 directory=data_pump_dir  dumpfile=sm7.dmp logfile=sm7 content=all exclude=table:”=’SYSATTACHMENT'”

LRM-00116: syntax error at 'table:' following '='

The working parameter file was called by expdp parfile =sm7.par

schemas=sm7

directory=data_pump_dir

content=all

dumpfile=sm7.dmp

logfile=sm7.log

exclude=table:”=’SYSATTACHMENT'”

For information this is the revised syntax that works from the command line. However I for one will be using the parameter file in future

expdp schemas=sm7 directory=data_pump_dir  dumpfile=sm7.dmp logfile=sm7 content=all exclude=table:\”=\’SYSATTACHMENT\’\”

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 201 other followers

%d bloggers like this: