Data pump expdp failed with DMSYS related errors ORA-39126 ORA-06512 etc

Today one of our data pump export/import jobs failed with errors attached at the bottom.  The process working fine before our 11g upgrade. 
I did a little research and found metalink doc 304449.1 has perfect solution.


The problem is we removed some unused database options before we upgrade from 10g to 11g.
The reason is because with all these unnecessary options, the upgrade scripts will run almost two hours. Removing them the upgrade will finish in 15 minutes. 


It turns out DMSYS data mining option is among them, but somehow Oracle didn't cleanly remove the option with some left over records in data pump export table.


The solution in this case is delete these records,


SQL> DELETE FROM exppkgact$ WHERE SCHEMA='DMSYS';
SQL> commit;


There are other potential causes for the same error. You can check the metalink doc for more info.


Database Data Pump Export fails with PLS-00201 identifier DMSYS.DBMS_MODEL_EXP must be declared [ID 304449.1]


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_11":  userid=system/********@TEST parfile=/home/oracle/dba/sql/DWS.par 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS [] 
ORA-31642: the following SQL statement fails: 
BEGIN "DMSYS"."DBMS_DM_MODEL_EXP".SCHEMA_CALLOUT(:1,0,1,'11.02.00.00.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 1245
ORA-04063: package body "DMSYS.DBMS_DM_MODEL_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "DMSYS.DBMS_DM_MODEL_EXP"
ORA-06512: at "SYS.DBMS_METADATA", line 5300
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 8159
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
70000007ddbc258     19028  package body SYS.KUPW$WORKER
70000007ddbc258      8191  package body SYS.KUPW$WORKER
70000007ddbc258     12728  package body SYS.KUPW$WORKER
70000007ddbc258      4618  package body SYS.KUPW$WORKER
70000007ddbc258      8902  package body SYS.KUPW$WORKER
70000007ddbc258      1651  package body SYS.KUPW$WORKER
70000007eaf9060         2  anonymous block

Oracle won't do partition pruning on MAX/MIN query of partition key.

  Oracle doesn’t do a partition pruning on MAX/MIN query on partition key. Even it makes perfect sense for Oracle to scan only the partition that has MAX/MIN value. And this is not something new, the user community certainly noticed this.

http://www.oramoss.com/blog/2009/06/no-pruning-for-minmax-of-partition-key.html

  Right now, all we can do is some work around. For example one of our database use this query to figure out MAX AGG_DATE as part of daily ETL process. AGG_DATE is partition key of the table and not indexed.
The old execution plan looks like this,
Ouch and yes, the Pstart is 1 and Pstop is 1149. Oracle scanned all 1149 partitions of the table and took a very long time as expected.


SQL>  explain plan for SELECT max(AGG_DATE) from (SELECT "A1"."AGG_DATE" FROM "WEB_APPS"."COUNTER_DAY_AGG" "A1" order by AGG_DATE desc );
Explained.
SQL>  select * from table(dbms_xplan.display());


PLAN_TABLE_OUTPUT
------------------------------------
Plan hash value: 4125776214
----------------------------------------------------------------------------------| Id  | Operation            | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                            |     1 |     8 |    10M  (2)| 34:45:45 |       |       |
|   1 |  SORT AGGREGATE      |                            |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|                            |  5196M|    38G|    10M  (2)| 34:45:45 |     1 |  1149 |
|   3 |    TABLE ACCESS FULL |            COUNTER_DAY_AGG |  5196M|    38G|    10M  (2)| 34:45:45 |     1 |  1149 |
----------------------------------------------------------------------------------

Since this our daily job, the work around I put in is where clause. 

The plan looks better after that, Pstart is now KEY instead 1. In our case it will scan 7 daily partitions.

The stats give bogus running time estimate. The actual run time reduced from 20 minutes to 1 minute. 

SQL>  explain plan for SELECT MAX("A1"."AGG_DATE") FROM "ODS_WEB_APPS"."COUNTER_DAY_AGG" "A1" where AGG_DATE > sysdate-7;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------Plan hash value: 1669369268

----------------------------------------------------------------------------------| Id  | Operation                 | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT          |                            |     1 |     8 |    10M  (4)| 36:09:51 |       |       |

|   1 |  SORT AGGREGATE           |                            |     1 |     8 |            |          |       |       |

|   2 |   PARTITION RANGE ITERATOR|                            |  6919K|    52M|    10M  (4)| 36:09:51 |   KEY |  1149 |

|*  3 |    TABLE ACCESS FULL      |            COUNTER_DAY_AGG |  6919K|    52M|    10M  (4)| 36:09:51 |   KEY |  1149 |

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

Of course there's one trade off of this work around. It will limit the script's ability to catch up failed or missed loading. The script use this query to find out max loading date and catch up load from that date. So if our loading didn't run for more than 7 days, the script won't be able to catchup. I guess that's something we can live with, it's not possible that we didn't notice our daily ETL job was not running for past 7 days  :) Even in worst case scenario that really happens, we can still deal with it individually. 

ORA-01591 and quick solution

One of the user reported they got this error from application.

ORA-01591: lock held by in-doubt distributed transaction 4.7.533420

We don't really see this error often. So I did a little research. 

The error message doc from Oracle has pretty good explanation but didn't provide a solution how to resolve this.

ORA-01591:	lock held by in-doubt distributed transaction string
Cause:	Trying to access resource that is locked by a dead two-phase commit transaction that is in prepared state.
Action:	DBA should query the pending_trans$ and related tables, and attempt to repair network connection(s) to coordinator and commit point. If timely repair is not possible, DBA should contact DBA at commit point if known or end user for correct outcome, or use heuristic default if given to issue a heuristic commit or abort command to finalize the local portion of the distributed transaction.

What I end up did is pretty easy,  rollback force didn't do the trick. The DBMS_TRANSACTION helped.

SQL> select local_tran_id from dba_2pc_pending;

LOCAL_TRAN_ID

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

4.7.533420

SQL> rollback force '4.7.533420';

Rollback complete.

SQL> select local_tran_id from dba_2pc_pending;

LOCAL_TRAN_ID

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

4.7.533420

SQL> exec dbms_transaction.purge_lost_db_entry('4.7.533420');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select local_tran_id from dba_2pc_pending;

no rows selected

ORA-12547 and procmap error while running sqlplus

If you got following error message while trying to run sqlplus on IBM AIX 5L

This is an exact match of 

ORA-12547 connecting to sqlplus / as sysdba on IBM AIX 5L [ID 372143.1]

Basically because the /proc is not mounted on your server.


/DB/../10204-64/network/admin  PROD 341 >sqlplus / as sysdba
/usr/bin/procmap : no such process : 373080
/usr/bin/procmap : no such process : 373080
/usr/bin/procmap : no such process : 373080


SQL*Plus: Release 10.2.0.4.0 - Production on Tue Sep 28 15:39:02 2010


Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


ERROR:
ORA-12547: TNS:lost contact

Hot Backup datafile copy problem with CIO mount option on AIX

We encountered some Hot Backup error after we followed IBM's suggestion to remove CIO mount option from our JFS2 data volumes. 

The error is like follows:

scp /DB/VPROD/data01/sysaux01.dbf .

> cp: /DB/VPROD/data01/sysaux01.dbf: A system call received a parameter that is not valid.

This happens after we put database in hot backup mode and trying to copy datafiles.

So this approves what IBM told us, Oracle will use CIO no matter if the file system is mounted using CIO option. But this created another problem that AIX will not allow non-CIO system operation to access file opened with CIO.

There you go we back to square one, mounted the data volume back with CIO option in order to facilitate our Hot Backup. Another reason to use RMAN backup I guess.

ORA-00064: object is too large to allocate on this O/S (1,16777216)

Not sure how many of you run into this problem. It happens to one of our production database after we trying to increase the SGA size to 320G.

Oracle version is 10.2.0.4, AIX 5.3 L6 32CPUs 750G RAM

The error is

  ORA-00064: object is too large to allocate on this O/S (1,16777216)

It seems pretty conclusive OS limit problem.  However our OS ulimit for Oracle user is set to unlimited.
Actually the problem is  _ksmg_granule_size  SGA units of granules

Granule size is determined by total SGA size. On most platforms, the size of a granule is 4 MB if the total SGA size is less than 1 GB, and granule size is 16MB for larger SGAs.

In our case since we increased our SGA so big, even 16MB is not big enough to fix our needs.
After increased _ksmg_granule_size to 32MB, we are able to start the instance with 350MB SGA.

  alter system set "_ksmg_granule_size"=33554432 scope=spfile;

Oracle instance slow startup

Recently we noticed one of our production database take longer than usual to startup.
In some cases, it took 3 to 4 hours for alter database open to complete.
The case is particularly bad for our TEST and DEV database after they got refreshed with production. Our production is very powerful 32 CPUs box, when production took like 20 to 30 minutes to open. TEST and DEV will take hours.


Sat Feb 27 06:48:55 2010
alter database open
-snip-
Sat Feb 27 09:54:22 2010
Completed: alter database open

We engaged Oracle support and they suggested to do a trace.

SQL> conn / as sysdba
SQL> startup mount
SQL> alter session set events '10046 trace name context forever, level 12';
SQL> alter database open;
Once the instance is opened, immediately turn off the 10046 tracing through that session.
SQL> alter session set events '10046 trace name context off';


The trace revealed that, database is querying two advanced queue tables used by STREAMS. The two tables are highly fragmented, for example table aq$_qt_cap_st_D had just 150 records and had 8000 + extents.


strmadmin.aq$_qt_cap_st_p
strmadmin.aq$_qt_cap_st_d

For TEST and DEV we can easily go around the issue by truncating the two tables because we are not using STREAMS on them. For production, table re-org are in order, for that we choose to use Online Redefinition.