Damir Vadas, Oracle as I learned: ORA-27038: created file already exists in expdp

expdp/impdp were new export/import features introduced in Oracle 10g and they were first messenger that days of "old" exp/imp utility, that was with us from the very Oracle beginning, are numbered. And really that happened in Oracle 11g with short notice that exp/imp are no longer sported. Beside many new features that any new Oracle tool brings, there are (at least in first release) some disadvantages. One of it was that expdp was not able to overwrite existing file (what was not a problem for exp).

The problem

Error was manifested in a way:

Export: Release 10.2.0.5.0 - Production on Srijeda, 24 Ožujak, 2011 15:19:00

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Release 10.2.0.5.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "E:\dmp\DB1_SCHEMAS_5.expdp"
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists

About that error there were many articles posted around Internet. If you are dealing with just one file then erase it ant problem is solved. But if you use expdp as logical backup, based on retention in days, then mentioned task might not be so easy. So I'll try to post mine approach with 7 days retention policy situation (which suit mine needs). Mine client was using Oracle on Windows, so this was another challenge to "sharp" batch scripts knowledge (I admit that windows batch scripts are still to me Terra incognita in many aspects).

The solution

First let me say, that if you want to have more consistent "schema" expdp export, then single dump file is not enough. "Schema" based expdp, has more then known limitations:

Public synonyms are not exported at all
Some other schema objects are not exported as well (in mine case DIRECTORY is the only interesting thing to monitor).

So in mine case I'd rather speak about "backup set" because it is compound of five files:

expdp dump file
expdp log file
create DIRECTORY script
grant DIRECTORY script
create PUBLIC SYNONYMS script

7 days retention policy

"7 days retention policy" means that every backup files set is overwritten every 7th day. Here is typical directory structure where backup files are placed. Listing shows files after full 7 days cycle:

e:\dmp>dir
 Volume in drive E is BCKP
 Volume Serial Number is 50AB-BS33

 Directory of e:\dmp

26.03.2011  20:20    <DIR>          .
26.03.2011  20:20    <DIR>          ..
20.03.2011  06:00         1.241.561 DB1_SCHEMAS_1.EXPDP
20.03.2011  06:00             4.905 db1_schemas_1.log
20.03.2011  06:00               413 DB1_SCHEMAS_1_directories.sql
20.03.2011  06:00               407 DB1_SCHEMAS_1_directories_grant.sql
20.03.2011  06:00             1.491 DB1_SCHEMAS_1_public_synonyms.sql
21.03.2011  06:00         1.241.561 DB1_SCHEMAS_2.EXPDP
21.03.2011  06:00             4.905 db1_schemas_2.log
21.03.2011  06:00               413 DB1_SCHEMAS_2_directories.sql
21.03.2011  06:00               407 DB1_SCHEMAS_2_directories_grant.sql
21.03.2011  06:00             1.491 DB1_SCHEMAS_2_public_synonyms.sql
22.03.2011  06:00         1.241.561 DB1_SCHEMAS_3.EXPDP
22.03.2011  06:00             4.905 db1_schemas_3.log
22.03.2011  06:00               413 DB1_SCHEMAS_3_directories.sql
22.03.2011  06:00               407 DB1_SCHEMAS_3_directories_grant.sql
22.03.2011  06:00             1.491 DB1_SCHEMAS_3_public_synonyms.sql
23.03.2011  06:00         1.241.561 DB1_SCHEMAS_4.EXPDP
23.03.2011  06:00             4.905 db1_schemas_4.log
23.03.2011  06:00               413 DB1_SCHEMAS_4_directories.sql
23.03.2011  06:00               407 DB1_SCHEMAS_4_directories_grant.sql
23.03.2011  06:00             1.491 DB1_SCHEMAS_4_public_synonyms.sql
24.03.2011  06:00         1.241.561 DB1_SCHEMAS_5.EXPDP
24.03.2011  06:00             4.905 db1_schemas_5.log
24.03.2011  06:00               413 DB1_SCHEMAS_5_directories.sql
24.03.2011  06:00               407 DB1_SCHEMAS_5_directories_grant.sql
24.03.2011  06:00             1.491 DB1_SCHEMAS_5_public_synonyms.sql
25.03.2011  06:00         1.245.110 DB1_SCHEMAS_6.EXPDP
25.03.2011  06:00             4.905 db1_schemas_6.log
25.03.2011  06:00               413 DB1_SCHEMAS_6_directories.sql
25.03.2011  06:00               407 DB1_SCHEMAS_6_directories_grant.sql
25.03.2011  06:00             1.491 DB1_SCHEMAS_6_public_synonyms.sql
26.03.2011  06:00         1.245.284 DB1_SCHEMAS_7.EXPDP
26.03.2011  06:00             4.907 db1_schemas_7.log
26.03.2011  06:00               413 DB1_SCHEMAS_7_directories.sql
26.03.2011  06:00               407 DB1_SCHEMAS_7_directories_grant.sql
26.03.2011  06:00             1.491 DB1_SCHEMAS_7_public_synonyms.sql
              35 File(s)      8.748.713 bytes
               2 Dir(s)  82.779.652.096 bytes free

e:\dmp>

All files has similar names with only distinct-numbered day of week when backup was taken. Number for that means (in mine case Win CP 1250):

1 = Sunday
2 = Monday
3 = Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday

dayofweek.vbs

Because I didn't find any other way to get day of week in Windows scripts, I use small VB script which I called "dayofweek.vbs". This script which has just one line is here:

WScript.Echo DatePart("w", Date)

Backup script

Now when I get all the parts, I can show the whole backup script, which handle expdp automatically:

@ECHO ON
@ECHO Expdp tab_dwh, tab_apex i tab_int schema (7 times a week including public synonyms and directories!)
@ECHO OFF
@for /f %%d in ('cscript dayofweek.vbs //nologo') do (
  @ECHO "Working export dump for day %%d"
  @ECHO OFF
  @ECHO set timi off head off^&echo. select DIRECTORY_PATH from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR'; | sqlplus -s "/ as sysdba" | findstr . > result.tmp
  FOR /F %%i IN (result.tmp) DO (
  echo Y | del %%i\db1_schemas_%%d.expdp 
  echo Y | del %%i\db1_schemas_%%d.log
  )
  @del result.tmp
  
  @ECHO ON  
  expdp '/ as sysdba' DUMPFILE=db1_schemas_%%d.expdp LOGFILE=db1_schemas_%%d.log  DIRECTORY=DATA_PUMP_DIR SCHEMAS=TAB_DWH,TAB_APEX,TAB_INT,TOOLS VERSION=10.2.0
  
  @REM PUBLIC SYNONYMS
  @ECHO OFF
  (
  echo set cmdsep on
  echo set cmdsep '"'; --"
  echo "set linesize 160"
  echo "set head off"
  echo "set feedback off"
  echo "select 'create or replace public synonym ' || synonym_name || ' for ' || table_owner || '.' || table_name || ';' from dba_synonyms where owner='PUBLIC' and table_owner IN ('TOOLS','TAB_APEX','TAB_INT','TAB_DWH') order by table_owner, synonym_name;"
  ) | sqlplus -s "/ as sysdba" | findstr . > e:\dmp\D1_SCHEMAS_%%d_public_synonyms.sql

  @REM DIRECTORIES
  @ECHO OFF
  (
  echo set cmdsep on
  echo set cmdsep '"'; --"
  echo "set linesize 160"
  echo "set head off"
  echo "set feedback off"
  echo "select 'create directory ' ||  DIRECTORY_NAME || ' AS ' ||'''' || DIRECTORY_PATH || ''' ;' from dba_directories d, sys.dba_tab_privs p where p.grantee in ('TOOLS','TAB_APEX','TAB_INT','TAB_DWH')   and D.DIRECTORY_NAME = p.TABLE_NAME and P.OWNER = 'SYS';"
  ) | sqlplus -s "/ as sysdba" | findstr . > e:\dmp\DB1_SCHEMAS_%%d_directories.sql

  @ECHO OFF
  (
  echo set cmdsep on
  echo set cmdsep '"'; --"
  echo "set linesize 160"
  echo "set head off"
  echo "set feedback off"
  echo "select 'grant ' || privilege || ' on directory ' || p.owner || '.' || p.table_name || ' to ' || p.grantee || DECODE (GRANTABLE, 'YES', ' with grant option ', '') || ';' from dba_directories d, sys.dba_tab_privs p where p.grantee in ('TOOLS','TAB_APEX','TAB_INT','TAB_DWH')   and D.DIRECTORY_NAME = p.TABLE_NAME   and P.OWNER = 'SYS' ;"
  ) | sqlplus -s "/ as sysdba" | findstr . > e:\dmp\DB1_SCHEMAS_%%d_directories_grant.sql
  @exit
)

Initial problem (ORA-27038) is solved in accented lines, where script is automatically deleting previous parent dump file. In script, log file is deleted as well, even thought, log files would be overwritten without any problems ... but I show that example to make code more clear.

This script automatically gather Windows directory value for expdp DIRECTORY which is used in export. This value is used to initially delete old value and to save other "non expdp" data in the same directory.

A you can see, for sending data to sqlplus I use two different methods. One that I've explained in one of mine previous posts How to redirect sqlplus result in Windows batch script (explained in first sql which gather DOS path for Oracle DIRECTORY value) and other when I create sql scripts for PUBLIC SYNONYMS and additional schema DIRECTORY values. This second method is something that I recently found out and looks more practical and convenient for use then first method.

1 month retention policy

If you want to use 1 month policy (instead of 7 days) use VB script which I called "dayofmonth.vbs" instead o previous one. Here is it's content:

WScript.Echo DatePart("m", Date)

11g news

Because overwrite problem with expdp was more then obvious, in Oracle 11g, news is that in expdp you can define additional parameter REUSE_DUMPFILES which handle overwrite issues. When set to "Y", any existing dumpfiles will be overwritten automatically. When the default values of "N" is used, an error is issued if the dump file already exists.

Cheers!

Sunday, March 27, 2011

ORA-27038: created file already exists in expdp