Friday, May 15, 2015

ORA-39095

I kicked off an expdp with the following parameters. I set the filesize parameter but forgot to set the dumpfile parameter to match:

content=all
directory=data_pump_dir
dumpfile=andrew.dmp
exclude=table:"='DOCUMENTIMAGE'"
filesize=1000000000
full=y          
logfile=andrew.log

The job produced 1 file with the filesize I had specified:

D:\oracle\11.2.0\admin\AKTPROD\dpdump>dir andrew.dmp
Volume in drive D is Datas
Volume Serial Number is ECE8-9011
 
Directory of D:\oracle\11.2.0\admin\AKTPROD\dpdump
 
05/15/2015  12:20 PM       999,997,440 ANDREW.DMP
               1 File(s)    999,997,440 bytes
               0 Dir(s)  414,961,401,856 bytes free
 
D:\oracle\11.2.0\admin\AKTPROD\dpdump>

Then it failed with an ORA-39095:

ORA-39095: Dump file space has been exhausted: Unable to allocate 4096 bytes
Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at 12:20:13

I changed the dumpfile parameter as follows and reran the expdp:

content=all
directory=data_pump_dir
dumpfile=andrew%u.dmp
exclude=table:"='DOCUMENTIMAGE'"
filesize=1000000000
full=y
logfile=andrew.log

The %u was replaced by the numbers 01 through 99 inclusive as required and the expdp worked, producing 88 files:

D:\oracle\11.2.0\admin\AKTPROD\dpdump>dir andrew*.dmp
Volume in drive D is Datas
Volume Serial Number is ECE8-9011
 
Directory of D:\oracle\11.2.0\admin\AKTPROD\dpdump
 
05/15/2015  01:07 PM       999,997,440 ANDREW01.DMP
05/15/2015  12:59 PM       999,997,440 ANDREW02.DMP
05/15/2015  01:01 PM       999,997,440 ANDREW03.DMP
05/15/2015  01:01 PM       999,997,440 ANDREW04.DMP
05/15/2015  01:01 PM       999,997,440 ANDREW05.DMP
05/15/2015  01:02 PM       999,997,440 ANDREW06.DMP
05/15/2015  01:02 PM       999,997,440 ANDREW07.DMP
05/15/2015  01:02 PM       999,997,440 ANDREW08.DMP
05/15/2015  01:02 PM       999,997,440 ANDREW09.DMP
05/15/2015  01:03 PM       999,997,440 ANDREW10.DMP
Etc.
05/15/2015  01:32 PM       999,997,440 ANDREW80.DMP
05/15/2015  01:33 PM       999,997,440 ANDREW81.DMP
05/15/2015  01:33 PM       999,997,440 ANDREW82.DMP
05/15/2015  01:33 PM       999,997,440 ANDREW83.DMP
05/15/2015  01:34 PM       999,997,440 ANDREW84.DMP
05/15/2015  01:34 PM       999,997,440 ANDREW85.DMP
05/15/2015  01:35 PM       999,997,440 ANDREW86.DMP
05/15/2015  01:36 PM       999,997,440 ANDREW87.DMP
05/15/2015  01:38 PM       829,038,592 ANDREW88.DMP
              88 File(s) 87,828,815,872 bytes
               0 Dir(s)  326,559,186,944 bytes free
 
D:\oracle\11.2.0\admin\AKTPROD\dpdump>

I will try to look at what happens if you need more than 99 files in a future post.

Monday, May 11, 2015

ORA-01075

I tried to log on to an Oracle 11.2 test database, which the developers could no longer use, and saw the following error:

MDMDEV1 /export/home/oracle > sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 28 09:41:24 2014
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
ERROR:
ORA-01075: you are currently logged on
 
Enter user-name:

I looked in the alert log and saw several of the following messages:

Fri Mar 28 09:36:46 2014
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /oracle/app/oracle/product/diag/rdbms/mdmdev1/MDMDEV1/trace/MDMDEV1_smon_522.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","select count(*) from edition$","sga heap(1,0)","kglsim object batch")

I closed the database like this:

MDMDEV1 /export/home/oracle > sqlplus -prelim
 
SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 28 09:45:45 2014
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
Enter user-name: / as sysdba
SQL> shutdown abort
ORACLE instance shut down.
SQL>

Then I opened the database again:

MDMDEV1 /export/home/oracle > sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 28 09:49:39 2014
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
 
Total System Global Area  534462464 bytes
Fixed Size                  2225832 bytes
Variable Size             390072664 bytes
Database Buffers          134217728 bytes
Redo Buffers                7946240 bytes
Database mounted.
Database opened.
SQL> 

... and the developers could login again.