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.

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>


2nd October 2016:
Checked for relevance.
Shared on Twitter.

Friday, May 08, 2015

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.

Friday, May 01, 2015

Overloading

I was looking at a 3rd 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 2nd developer to try again. He reported that the problem had gone away.
 
I reran the CREATE OR REPLACE TYPE statement provided by the 1st developer and it worked too.