Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Alvin.

Asked: February 25, 2002 - 9:43 pm UTC

Last updated: November 15, 2011 - 10:48 am UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Hi,
I got a big trouble when I shutdown database
here bellow is the trace file:

Oracle8i Enterprise Edition Release 8.1.6.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
ORACLE_HOME = /opt/oracle/product/8.1.6
System name: SunOS
Node name: gis-db
Release: 5.7
Version: Generic_106541-12
Machine: sun4u
Instance name: gis
Redo thread mounted by this instance: 1
Oracle process number: 140
Unix process pid: 7729, image: oracle@gis-db

*** 2002-02-24 02:00:13.286
*** SESSION ID:(146.4766) 2002-02-24 02:00:13.283
Error in executing triggers on instance shutdown
*** 2002-02-24 02:00:13.286
ksedmp: internal or fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-29516: Aurora assertion failure: Assertion failure at eox.c:187
Uncaught exception Root of all Java exceptions:
ORA-06512: at "SYS.DBMS_JAVA", line 0
ORA-06512: at line 2
SHUTDOWN: waiting for active calls to complete.
*** 2002-02-24 02:10:34.401
SHUTDOWN: waiting for active calls to complete.
*** 2002-02-24 02:15:45.502
SHUTDOWN: waiting for active calls to complete.
*** 2002-02-24 02:20:56.564
SHUTDOWN: waiting for active calls to complete.

Regards,

Alvin

*****

I use shutdown immediate....
I did cold backup at 2:00 am last Sunday(cron), but I found that the DB still shutting down in the Monday morning(I did not dump full trace file), so, I used shutdown abort and then startup the db.

I can't believe, two days for shutdown?? there must be something wrong.


and Tom said...

(looks like 6 hours, not 2 days but anyway....)

Well, here is my opinion on unattended shutdowns.

Shutdowns should NEVER be done in a script -- never. Shutdowns should never be done unattended. When do things break? During shutdown and startup -- thats when most things "go wrong". Suppose you updated your init.ora during the week and added a parameter. Unbeknownest to you -- you made a typo. Now when you shutdown and startup -- the startup fails. When your users come in monday morning -- you are "hosed", the system is down, they have to wait for you to arrive and fix it.

There is NEVER a need to shutdown the database for something as simple as a backup. NEVER. Hot backups are not significantly more difficult then cold. If you don't shutdown/startup -- you'll not only have significantly more uptime, you won't experience any nasty surprises. The only time my database instances have been down unexpectedly was due to unattended startups/shutdowns and something going wrong.

Every notice how lightbulbs always seem to burnout when you turn them on?

So my advice -- don't shutdown unless you are sitting there. I remember the last time I rebooted by server remotely. It was about 7 years ago now. I had to drive 45 miles on a sunday morning just to hit "ctl-d" cause the disk failed an fschk and was waiting for someone to hit ctl-d to continue. So -- i don't reboot my servers unless I'm sitting there and I don't shutdown my databases unless I'm sitting there.




Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

help with ORA-29516

Jim Hill, March 19, 2002 - 10:14 am UTC

I was looking for help with ORA-29516.

Tom Kyte
March 19, 2002 - 10:30 am UTC

The course of action is clear then:

$ oerr ora 29516
29516, 00000, "Aurora assertion failure: %s"
// *Cause: An internal error occurred in the Aurora module.
// *Action: Contact Oracle Worldwide Support.




SHUTDOWN: waiting for active calls to complete.

jim, February 25, 2005 - 10:37 am UTC

We now test for this scenario, and if it occurs, the on-call person gets paged (by the server) + we have simple scripts that run continuously checking for DB up, and if for some reason one is not available when expected, again, the on-call person is notified automatically and immediately by the server.



Tom Kyte
February 25, 2005 - 5:53 pm UTC

so, you are saying "we have someone sitting there" :)

ORA6550 by DB export

Kevin Burgess, November 04, 2011 - 1:45 pm UTC

Hi Tom,

We have a mixture of 10g and 11g databases on our network in three versions: 10.1.0.1.0, 10.2.0.4, 11.2.0.1.0.

While performing a perfectly normal export I receive the following errors:

EXP-00008: ORACLE-Fehler 6550 aufgetreten
ORA-06550: Zeile 1, Spalte 19:
PLS-00201: Bezeichner 'SYS.DBMS_DEFER_IMPORT_INTERNAL' muss deklariert werden
ORA-06550: Zeile 1, Spalte 7:
PL/SQL: Statement ignored
ORA-06512: in "SYS.DBMS_SQL", Zeile 1575
ORA-06512: in "SYS.DBMS_EXPORT_EXTENSION", Zeile 97
ORA-06512: in "SYS.DBMS_EXPORT_EXTENSION", Zeile 126
ORA-06512: in Zeile 1
. . Export der Tabelle                   DEF$_AQERROR
EXP-00008: ORACLE-Fehler 6510 aufgetreten
ORA-06510: PL/SQL: Unbehandelte benutzerdefinierte Exception
ORA-06512: in "SYS.DBMS_EXPORT_EXTENSION", Zeile 50
ORA-06512: in "SYS.DBMS_EXPORT_EXTENSION", Zeile 126
ORA-06512: in Zeile 1
. . Export der Tabelle                  DEF$_CALLDEST
EXP-00008: ORACLE-Fehler 6550 aufgetreten
ORA-06550: Zeile 1, Spalte 19:
PLS-00201: Bezeichner 'SYS.DBMS_DEFER_IMPORT_INTERNAL' muss deklariert werden
ORA-06550: Zeile 1, Spalte 7:
PL/SQL: Statement ignored
ORA-06512: in "SYS.DBMS_SQL", Zeile 1575
ORA-06512: in "SYS.DBMS_EXPORT_EXTENSION", Zeile 97
ORA-06512: in "SYS.DBMS_EXPORT_EXTENSION", Zeile 126
ORA-06512: in Zeile 1
. . Export der Tabelle               DEF$_DEFAULTDEST
EXP-00008: ORACLE-Fehler 6510 aufgetreten
ORA-06510: PL/SQL: Unbehandelte benutzerdefinierte Exception
ORA-06512: in "SYS.DBMS_EXPORT_EXTENSION", Zeile 50
ORA-06512: in "SYS.DBMS_EXPORT_EXTENSION", Zeile 126
ORA-06512: in Zeile 1
. . Export der Tabelle               DEF$_DESTINATION
EXP-00008: ORACLE-Fehler 6550 aufgetreten
ORA-06550: Zeile 1, Spalte 19:
PLS-00201: Bezeichner 'SYS.DBMS_DEFER_IMPORT_INTERNAL' muss deklariert werden
ORA-06550: Zeile 1, Spalte 7:
PL/SQL: Statement ignored
ORA-06512: in "SYS.DBMS_SQL", Zeile 1575
ORA-06512: in "SYS.DBMS_EXPORT_EXTENSION", Zeile 97
ORA-06512: in "SYS.DBMS_EXPORT_EXTENSION", Zeile 126
ORA-06512: in Zeile 1
. . Export der Tabelle                     DEF$_ERROR
EXP-00008: ORACLE-Fehler 6510 aufgetreten
ORA-06510: PL/SQL: Unbehandelte benutzerdefinierte Exception
ORA-06512: in "SYS.DBMS_EXPORT_EXTENSION", Zeile 50
ORA-06512: in "SYS.DBMS_EXPORT_EXTENSION", Zeile 126
ORA-06512: in Zeile 1

The command used to perform the export was:
exp userid=username/password file=c:\export\export.dmp full=y

I have raised the compatibility for the instances to match the software version installed in the hope that could resolve it but to no avail.  My colleagues have been aware of this problem but because the instances still 'run' they have been left ignored much to my dismay.

We have an upcoming migration of 120 databases to 11g in the next year and I would like to use this as an opportunity to rectify this issue.

Many thanks for your time Tom and as always, I look forward to your answer.

Regards,

Kev

Tom Kyte
November 07, 2011 - 10:59 am UTC

does dbms_export_extension exist in your database?
do you have the ability to execute it?

Datapump export

Kevin Burgess, November 05, 2011 - 4:23 am UTC

Hello again Tom!

I tried to use the same database but this time exporting the database using datapump.  The command used was:

expdp user/password directory=datapump file=expdp.dmp full=y

This is on an 11.2.0.1.0 instance and the compatibility level for the database is set to that as well. It is exactly the same database that produced the errors listed above while performing "normal" export.

This is a copy of the entire dp export before it failed:

;;; 
Export: Release 11.2.0.1.0 - Production on Fr Nov 4 20:06:35 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
;;; 
Angemeldet bei: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
;;; Legacy-Modus aktiv wegen folgenden Parametern:
;;; Legacy-Modusparameter: "file=full_dump2.dmp" Speicherort: Command Line, Ersetzt durch: "dumpfile=full_dump2.dmp"
;;; Legacy-Modus hat reuse_dumpfiles=true Parameter festgelegt.
"DPUMP"."SYS_EXPORT_FULL_02":  userid=dpump/********@eur directory=datapump dumpfile=full_dump2.dmp full=y reuse_dumpfiles=true  wird gestartet
Schätzung erfolgt mit Methode BLOCKS...
Objekttyp DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA wird verarbeitet
Gesamte Schätzung mit BLOCKS Methode: 2.343 GB
Objekttyp DATABASE_EXPORT/TABLESPACE wird verarbeitet
Objekttyp DATABASE_EXPORT/PROFILE wird verarbeitet
Objekttyp DATABASE_EXPORT/SYS_USER/USER wird verarbeitet
Objekttyp DATABASE_EXPORT/SCHEMA/USER wird verarbeitet
Objekttyp DATABASE_EXPORT/ROLE wird verarbeitet
Objekttyp DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT wird verarbeitet
Objekttyp DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT wird verarbeitet
Objekttyp DATABASE_EXPORT/SCHEMA/ROLE_GRANT wird verarbeitet
Objekttyp DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE wird verarbeitet
Objekttyp DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA wird verarbeitet
Objekttyp DATABASE_EXPORT/RESOURCE_COST wird verarbeitet
Objekttyp DATABASE_EXPORT/TRUSTED_DB_LINK wird verarbeitet
Objekttyp DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE wird verarbeitet
Objekttyp DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT wird verarbeitet
Objekttyp DATABASE_EXPORT/DIRECTORY/DIRECTORY wird verarbeitet
Objekttyp DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT wird verarbeitet
Objekttyp DATABASE_EXPORT/CONTEXT wird verarbeitet
Objekttyp DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM wird verarbeitet
Objekttyp DATABASE_EXPORT/SCHEMA/SYNONYM wird verarbeitet
Objekttyp DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC wird verarbeitet
Objekttyp DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT wird verarbeitet
Objekttyp DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM wird verarbeitet
Objekttyp DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ wird verarbeitet
Objekttyp DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM wird verarbeitet
Objekttyp DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA wird verarbeitet
ORA-39126: Unerwarteter Worker-Abbruchfehler in KUPW$WORKER.UNLOAD_METADATA [PROCACT_SCHEMA:"ZWINGMANN_AHLBORN_S"] 
ORA-00942: Tabelle oder View nicht vorhanden
ORA-06512: in "SYS.DBMS_SYS_ERROR", Zeile 95
ORA-06512: in "SYS.KUPW$WORKER", Zeile 8165
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
000007FF11D1F360     18990  package body SYS.KUPW$WORKER
000007FF11D1F360      8192  package body SYS.KUPW$WORKER
000007FF11D1F360      2823  package body SYS.KUPW$WORKER
000007FF11D1F360      8847  package body SYS.KUPW$WORKER
000007FF11D1F360      1649  package body SYS.KUPW$WORKER
000007FF11D220E8         2  anonymous block
Job "DPUMP"."SYS_EXPORT_FULL_02" wegen Abbruchfehler um 20:46:40 gestoppt


I am assuming that the problems are related to each other somehow but I am truly at a loss to find that relationship!

Many thanks for your help Tom!

Regards,

Kev

Tom Kyte
November 07, 2011 - 11:43 am UTC

I'll ask you to work with support on this one - no obvious hits in the support database, sounds like something was not installed properly or someone played around with some sys owned objects.

DBMS_EXPORT_EXTENSION

A reader, November 07, 2011 - 11:53 pm UTC

Hi Tom,

Thanks for your swift answer.

The package exists and I have the ability to execute it.

Regards,

Kev
Tom Kyte
November 08, 2011 - 7:27 am UTC

I'll have to ask you to work with support on this one - they'll have to dig around a bit to see what is "mis-installed"

For Kev

Chris, November 09, 2011 - 3:23 am UTC

Kev,
I've seen similar errors when the user doing the export doesn't have execute on DBMS_METADATA - might be worth a quick check.
Chris

Development issues

A reader, November 15, 2011 - 7:55 am UTC

Hi Chris,

Thanks for the tip but that package is also installed and I have the right to execute it so I don't think it is that.

Since my last post though I have looked quite deep into it and it seems to be a problem that the application developers need to get to grips with. It appears that the integrity of the database has somehow been compromised through what I can only describe as bad constraints.

The export of the database exposes a warning about "exporting questionable statistics" and this, from my perspective at least, seems to be a good reason why an import cannot be successfully completed without warnings.

At least, that is the theory!

I will keep you informed if I find anything else.

Regards,

Kev
Tom Kyte
November 15, 2011 - 10:48 am UTC

exporting questionable statistics just means the character set is probably different between the client and server - it doesn't affect the processing of export.

So much for that theory!

Kev, November 16, 2011 - 2:50 am UTC

Thanks Tom but at least with that answer you have solved another problem! Thanks!

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.