| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Request for advice Re: Oracle export error...
Hi Listers,
(Thanks Abardeen1 for your help!)
However, I need to be cautious because this problem is on our production
database.
This is what I found after investigating the solution details:
I searched for catnoqueue.sql in CATALOG.SQL and CATEXP.SQL which are the scripts that I had executed some weeks ago in a failed attempt to enable full export of the database. I could not find the catnoqueue.sql script in either of those scripts. I'm not sure in what case the catnoqueue.sql script needs to be executed. I found out that I did not have to run those scripts, I just needed to run the full export as a DBA user. However, running the scripts did cause several packages, forms and stored procedures to become invalid due to a time stamp. I'd still like to find out more about what causes these objects to become invalid and why a recompile is necessary. I believe there are several more objects that are invalid which need to be recompiled. I would appreciate some assistance in finding out how to determine what objects are invalid and how to recompile them. I someone has a script they could share, I would appreciate it.
1.) I have two queue tables DEF$_AQCALL and DEF$_AQERROR owned by SYSTEM 2.) I have the following (6) rows in the SYS.EXPACT$ table where
func_package = 'DBMS_AQ_IMPORT_INTERNAL' owned by SYSTEM
NAME FUNC_PROC DEF$_AQCALL AQ_TABLE_EXPORT_CHECK DEF$_AQCALL AQ_EXPORT_CHECK DEF$_AQCALL AQ_EXPORT_CHECK DEF$_AQERROR AQ_TABLE_EXPORT_CHECK DEF$_AQERROR AQ_EXPORT_CHECK DEF$_AQERROR AQ_EXPORT_CHECK
How would I know if the catnoqueue.sql has been run already? I also searched the Oracle FAQs and found a similar case where the export failed while exporting the posttables actions; however, the solution was different. That incident was on an Oracle 7.x database though. I noticed that the first Oracle error that I got was 4045 not 4068 or 1403 as indicated by the bug fix. I'm concerned if this solution will fix my problem.
Perhaps someone can clarify these issues for me. What is the relationship between the dba_queue_table and SYS.EXPACT$ table?
Thanks in advance for your time!
Denmark Weatherburne
"Knowledge is power, but it is only useful if it is shared!"
>From: "A. Bardeen" <abardeen1_at_yahoo.com>
>To: Denmark Weatherburne <denmark_weatherburne_at_hotmail.com>
>Subject: Re: More details re Oracle export error... (w/attachment)
>Date: Mon, 23 Jul 2001 18:20:08 -0700 (PDT)
>
>Denmark,
>
>I've attached the note I mentioned in the previous
>email.
>
>HTH,
>
>-- Anita
>
>--- Denmark Weatherburne
><denmark_weatherburne_at_hotmail.com> wrote:
> > Hi DBA's
> >
> > Here is additional information for your information:
> > We are running Oracle 8.0.5 on Windows NT 4.0
> >
> > The export parameter file I used is pasted below:
> > FULL=Y
> > FILE=D:\Backups\fullexport.dmp
> > LOG=D:\Backups\fullexport.log
> > CONSISTENT=Y
> >
> > I ran the export from DBArtisan.
> >
> > The export log was as follows:
> >
> > . exporting posttables actions
> > EXP-00008: ORACLE error 4045 encountered
> > ORA-04045: errors during recompilation/revalidation
> > of
> > SYS.DBMS_AQ_IMPORT_INTERNAL
> > ORA-06552: PL/SQL: Compilation unit analysis
> > terminated
> > ORA-06553: PLS-905: object SYS.AQ$_SUBSCRIBERS is
> > invalid
> > ORA-06508: PL/SQL: could not find program unit being
> > called
> > ORA-06512: at line 1
> > ORA-06512: at "SYS.DBMS_SYS_SQL", line 787
> > ORA-06512: at "SYS.DBMS_SQL", line 328
> > ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 82
> > ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 133
> > ORA-06512: at line 1
> > EXP-00000: Export terminated unsuccessfully
> >
> > We are using Oracle 8.0.5 on Windows NT 4.0.
> >
> > BTW, Regis, I would really appreciate any script you
> > may have to make this
> > problem resolution and learning process easier.
> >
> > Regards,
> >
> > Denmark Weatherburne
> > "Knowledge is power, but it is only useful if it is
> > shared!"
> >
> >
>_________________________________________________________________
> > Get your FREE download of MSN Explorer at
> > http://explorer.msn.com/intl.asp
> >
> >
> > --------
> > Oracle documentation is here:
> > http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
> > To unsubscribe: send a blank email to
> > oracledba-unsubscribe_at_LAZYDBA.com
> > To subscribe: send a blank email to
> > oracledba-subscribe_at_LAZYDBA.com
> > Visit the list archive:
> > http://www.LAZYDBA.com/odbareadmail.pl
> > Tell yer mates about http://www.farAwayJobs.com
> > By using this list you agree to these
> > terms:http://www.lazydba.com/legal.html
> >
>
>
>__________________________________________________
>Do You Yahoo!?
>Make international calls for as low as $.04/minute with Yahoo! Messenger
>http://phonecard.yahoo.com/
>Article-ID: <Note:1059224.6>
>Circulation: PUBLISHED (EXTERNAL)
>Folder: server.Utilities.ExportImport
>Topic: Draft Articles
>Title: EXPORT FAILS WITH EXP-8, ORA-4068/ORA-1403 ON SYS.
> DBMS_AQ_IMPORT_INTERNAL
>
>Problem Description:
>====================
>
>Your full database export fails when exporting posttables actions
>with the following errors:
>
>. exporting posttables actions
>EXP-00008: ORACLE error 4068 encountered
>ORA-04068: existing state of packages has been discarded
>ORA-04063: has errors
>ORA-04063: package body "SYS.DBMS_AQ_IMPORT_INTERNAL" has errors
>ORA-06508: PL/SQL: could not find program unit being called
>ORA-06512: at line 1
>ORA-06512: at "SYS.DBMS_SYS_SQL", line 787
>ORA-06512: at "SYS.DBMS_SQL", line 328
>ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 82
>ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 133
>ORA-06512: at line 1
>EXP-00000: Export terminated unsuccessfully
>
>or
>
>EXP-8: ORACLE error 1403 encountered
>ORA-1403: no data found
>ORA-6512: at "SYS.DBMS_AQ_IMPORT_INTERNAL", line 69
>ORA-6512: at line 1
>ORA-6512: at "SYS.DBMS_SYS_SQL", line 787
>ORA-6512: at "SYS.DBMS_SQL", line 328
>ORA-6512: at "SYS.DBMS_EXPORT_EXTENSION", line 82
>ORA-6512: at "SYS.DBMS_EXPORT_EXTENSION", line 133
>ORA-6512: at line 1
>EXP-0: Export terminated unsuccessfully
>
>User/Table level exports are fine, re-running of catalog and catproc make
>no difference.
>
>Problem Explanation:
>====================
>
>This can be caused by running the script 'catnoqueue.sql' without first
>dropping all the queue tables in the database. See <Bug:647800> which is
>fixed in 8.0.6.
>
>Problem References:
>===================
><BUG:647800>
>
>Article-ID: <Note:2105044.6>
>Circulation: PUBLISHED (EXTERNAL)
>Folder: server.Utilities.ExportImport
>Topic: Draft Articles
>Title: DELETE FROM SYS.EXPACT$ WHERE FUNC_PACKAGE =
> 'DBMS_AQ_IMPORT_INTERNAL';
>
>Solution Explanation
>====================
>The script 'catnoqueue.sql' has been run which drops the tables DEF$AQCALL
>and DEF$AQERROR. The corresponding rows in SYS.EXPACT$ are not dropped.
>
>Prior to running 'catnoqueue.sql', check for and drop any existing
>queue tables.
>
>To ascertain the existence of any queue tables, from SQL*Plus issue
>the following command to list all queue tables:
>SELECT * FROM dba_queue_tables
>
>To drop the queue tables, from SQL*Plus issue the following command:
>execute dbms_aqadm.drop_queue_table('<queue table name>')
>
>Solution Description:
>=====================
>
>If 'catnoqueue.sql' has already been run, then you need to delete from
>SYS.EXPACT$ rows for non-existent queue tables.
>
>The following steps will allow a full export to run successfully:
>All rows from the SYS.EXPACT$ table for the function package
>DBMS_AQ_IMPORT_INTERNAL need to be deleted.
>
>1. Login into Server Manager as INTERNAL or SYS; or, login into SQL*Plus as
> SYS
>
>2. Enter the following SQL command:
>
> DELETE FROM SYS.EXPACT$
> WHERE FUNC_PACKAGE = 'DBMS_AQ_IMPORT_INTERNAL';
>
>After the statement issue a 'commit' so the statement can take effect.
>
>3. Exit Server Manager or SQL*Plus.
>
>4. Now you should be able to do a full database export.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denmark Weatherburne INET: denmark_weatherburne_at_hotmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Aug 01 2001 - 16:47:59 CDT
 |
 |