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.

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.

The Right and Wrong Ways to Add a NOT NULL Constraint

I tested these examples in an Oracle 11.2 database. The first one shows how to add a NOT NULL constraint retrospectively. You start by creating a table:

SQL> create table andrew (col1 varchar2(1))
  2  /

Table created.

SQL>

Then at some point in the future, you add a NOT NULL constraint like this: 

SQL> alter table andrew modify (col1 not null)
  2  /

Table altered.

SQL>

Doing it this way, the constraint is obvious when you describe the table:

SQL> desc andrew
Name                       Null?    Type
-------------------------- -------- ------------------
COL1                       NOT NULL VARCHAR2(1)

SQL>

… and, if you try to add a null value, the error message is self-explanatory:

SQL> insert into andrew values (null)
  2  /
insert into andrew values (null)
                           *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("ORACLE"."ANDREW"."COL1")

SQL>

The second example, which I saw recently, shows how NOT to do it. You start, as before, by creating a table:

SQL> create table fred (col1 varchar2(1))
  2  /

Table created.

SQL>

… then you add a CHECK constraint as follows:

SQL> alter table fred
  2  add constraint con1
  3  check (col1 is not null)
  4  /

Table altered.

SQL>

You cannot see this constraint when you describe the table:

SQL> desc fred
Name                       Null?    Type
-------------------------- -------- ------------------
COL1                                VARCHAR2(1)

SQL>

… and when you try to add a null value, the error message is not very helpful:

SQL> insert into fred values (null)
  2  /
insert into fred values (null)
*
ERROR at line 1:
ORA-02290: check constraint (ORACLE.CON1) violated

SQL>

A Simple Introduction to ADDM and AWR

ADDM was introduced in Oracle 10 and stands for Automatic Database Diagnostic Monitor. You can use it to help solve performance problems. I was looking through some old copies of Oracle Magazine, found an article about it, written by Kimberly Floss in May/June 2004, and decided to give it a try. The article provided some SQL to produce an ADDM report so I ran it against an Oracle 12 database which nobody was using:

SQL> select dbms_advisor.get_task_report

  2  (task_name, 'TEXT', 'ALL')

  3  as addm_report

  4  from dba_advisor_tasks

  5  where task_id=

  6  (select max(t.task_id)

  7   from dba_advisor_tasks t,

  8     dba_advisor_log l

  9   where t.task_id = l.task_id

 10  and t.advisor_name = 'ADDM'

 11  and l.status = 'COMPLETED')

 12  /

 

ADDM_REPORT                                                                     

--------------------------------------------------------------------------------

          ADDM Report for Task 'ADDM:1254537384_1_154'                         

          --------------------------------------------                         

                                                                                

Analysis Period                                                                

---------------                                                                 

AWR snapshot range from 153 to 154.                                            

Time period starts at 02-APR-15 18.00.56                                       

Time period ends at 02-APR-15 19.00.11                                          

                                                                                

Analysis Target                                                                

---------------                                                                 

Database 'ORCL1' with DB ID 1254537384.                                        

Database version 12.1.0.1.0.                                                   

ADDM performed an analysis of instance orcl1, numbered 1 and hosted at         

WIN-HUVS4H99T56.                                                               

                                                                                

Activity During the Analysis Period                                            

-----------------------------------                                            

Total database time was 0 seconds.                                             

The average number of active sessions was 0.                                   

                                                                                

                                                                                

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

                                                                                

There are no findings to report.                                               

                                                                                

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

                                                                                

          Additional Information                                               

          ----------------------                                               

                                                                                

Miscellaneous Information                                                      

-------------------------                                                      

There was no significant database activity to run the ADDM.                    

                                                                                

The database's maintenance windows were active during 99% of the analysis      

period.                                                                        

                                                                                

                                                                                

 

SQL> 

This said that there was nothing to report, as you might expect. The following lines refer to an AWR snapshot:

AWR snapshot range from 153 to 154.

Time period starts at 02-APR-15 18.00.56 
Time period ends at 02-APR-15 19.00.11 

AWR stands for Automatic Workload Repository, which was also introduced in Oracle 10. A snapshot of this is taken every hour by default and ADDM uses these snapshots to produce its reports.

 

In the example above, the ADDM report had no recommendations but, in the normal course of events, you will be running ADDM on databases with problems. In this case, you might see output like this, which came from a Windows server trying to host 6 good-sized Oracle 11 databases with only 12 gigabytes of RAM:                                         

 

          Findings and Recommendations

          ----------------------------

 

Finding 1: Virtual Memory Paging

Impact is .22 active sessions, 100% of total activity.

------------------------------------------------------

Significant virtual memory paging was detected on the host operating system.

 

   Recommendation 1: Host Configuration

   Estimated benefit is .22 active sessions, 100% of total activity.

   -----------------------------------------------------------------

   Action

      Host operating system was experiencing significant paging but no

      particular root cause could be detected. Investigate processes that do

      not belong to this instance running on the host that are consuming

      significant amount of virtual memory. Also consider adding more physical

      memory to the host. 

The next example came from a third-party application which was doing lots of COMMIT’s (sometimes up to 1000 each second): 

Finding 3: Commits and Rollbacks

Impact is .47 active sessions, 34.34% of total activity.

--------------------------------------------------------

Waits on event "log file sync" while performing COMMIT and ROLLBACK operations

were consuming significant database time.

 

   Recommendation 1: Application Analysis

   Estimated benefit is .47 active sessions, 34.34% of total activity.

   -------------------------------------------------------------------

   Action

      Investigate application logic for possible reduction in the number of

      COMMIT operations by increasing the size of transactions.

   Rationale

      The application was performing 9966 transactions per minute with an

      average redo size of 1240 bytes per transaction.

 

   Recommendation 2: Host Configuration

   Estimated benefit is .47 active sessions, 34.34% of total activity.

   -------------------------------------------------------------------

   Action

      Investigate the possibility of improving the performance of I/O to the

      online redo log files.

   Rationale

      The average size of writes to the online redo log files was 3 K and the

      average time per write was 10 milliseconds.

   Rationale

      The total I/O throughput on redo log files was 217 K per second for

      reads and 446 K per second for writes.

   Rationale

      The redo log I/O throughput was divided as follows: 0% by RMAN and

      recovery, 67% by Log Writer, 0% by Archiver, 0% by Streams AQ and 32% by

      all other activity.

 

   Symptoms That Led to the Finding:

   ---------------------------------

      Wait class "Commit" was consuming significant database time.

      Impact is .47 active sessions, 34.34% of total activity. 

You might take quite a while to work out problems like these but, by using ADDM, you can diagnose them quickly. I will look at further examples in future posts.

Overloading

I was looking at a 3^rd party application running in an Oracle 11.2 database. I listed the source code of one of the packages and noticed that it contained 5 versions of the same procedure. I have never done any Oracle development so I am not a PL/SQL expert and this seemed strange to me at first. Then I thought about what I have been learning about Java recently and realized that this was Oracle doing overloading. This allows more than one procedure to have a given name as long as each version has a different set of parameters. Oracle then chooses the appropriate version based on the parameters supplied. You can see what I mean in the example below. A package is created called PACKAGE1. It contains two versions of a procedure called PROCEDURE1. Each version displays the parameters supplied to it. So, if you supply 1 parameter, Oracle chooses to run the version which accepts (and displays) 1 parameter. Conversely, if you supply 2 parameters, Oracle selects the version which accepts (and displays) 2 parameters:

SQL> set serveroutput on

SQL> create or replace package package1 is

  2  procedure procedure1

  3  (parameter1 in number);

  4  procedure procedure1

  5  (parameter1 in number,

  6   parameter2 in number);

  7  end package1;

  8  /

 

Package created.

 

SQL> create or replace package body package1 is

  2  procedure procedure1

  3  (parameter1 in number)

  4  is

  5  begin

  6  dbms_output.put_line('parameter1 = '||parameter1);

  7  end;

  8  procedure procedure1

  9  (parameter1 in number,

10   parameter2 in number)

11  is

12  begin

13  dbms_output.put_line('parameter1 = '||parameter1);

14  dbms_output.put_line('parameter2 = '||parameter2);

15  end;

16  end package1;

17  /

 

Package body created.

 

SQL> exec package1.procedure1(1);

parameter1 = 1

 

PL/SQL procedure successfully completed.

 

SQL> exec package1.procedure1(1,2);

parameter1 = 1

parameter2 = 2

 

PL/SQL procedure successfully completed.

 

SQL>

ORA-00604 and ORA-00001

This happened in an Oracle 11.1.0.6.0 database:

 

A developer reported problems when running a CREATE OR REPLACE TYPE statement in a development database. It was failing with an ORA-00604 followed by an ORA-00001. These messages could be seen again and again in the alert log:

ORA-00604: error occurred at recursive SQL level 3

ORA-00001: unique constraint (SYS.I_OBJ1) violated

I could see no reason for this so I did some research on the Internet then suggested that the data dictionary might be corrupted. This was quite possible as a number of people have the SYSTEM password for the database in question so somebody could easily have done something silly with it.

 

A second colleague reported the same errors when compiling packages and dropping and creating global temporary tables. Another DBA looked at the problem this time. He tried to recompile all invalid objects in the database and noticed that the compilation of one procedure displayed several errors before failing with a core dump. He amended the procedure a bit at a time to fix the errors until the compilation no longer caused a core dump. He then purged the recycle bin and asked the 2^nd developer to try again. He reported that the problem had gone away.

 

I reran the CREATE OR REPLACE TYPE statement provided by the 1^st developer and it worked too.