Re: ORA-04045: catalog.sql and catproc.sql damaged my db.

From: fee pack <defection_at_163.net>
Date: 23 Aug 2002 02:04:21 -0700
Message-ID: <4e732814.0208230104.509c1819@posting.google.com>

(c) Copyright 2001 Oracle Corporation. All rights reserved.

Enter user-name: sys_at_orcl as sysdba
Enter password:

when i try to disable triggers, the following message showed:

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production With the Partitioning option
JServer Release 9.0.1.0.0 - Production

SQL> ALTER SYSTEM SET _SYSTEM_TRIG_ENABLED='FALSE'; ALTER SYSTEM SET _SYSTEM_TRIG_ENABLED='FALSE'

                 *

ERROR at line 1:
ORA-00911: invalid character

SQL> ALTER SYSTEM SET SYSTEM_TRIG_ENABLED='FALSE'; ALTER SYSTEM SET SYSTEM_TRIG_ENABLED='FALSE'

                 *

ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

SQL> "Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:<ak2ljr$ral$1_at_babylon.agtel.net>...
> You should set _system_trig_enabled = false
> (ALTER SYSTEM SET _SYSTEM_TRIG_ENABLED='FALSE')
> as SYSDBA, then run catalog.sql, catproc.sql and then
> re-enable system triggers by setting _system_trig_enabled=true.
> This parameter should always be set to false before almost any
> object owned by SYS is modified (including Java VM) in 8i and
> later, otherwise you will see errors similar to those you got
> due to system triggers kicking in when they shouldn't. Since
> you're on 9i, you can change this parameter dynamically through
> alter system, on 8i you would have to set it in init.ora and bounce
> the instance, update catalog, reset and bounce again (I love new
> 9i dynamic reconfiguration feature!) Entering restricted session
> while you update catalog wouldn't hurt either.
>
> --
> Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
> All opinions are mine and do not necessarily go in line with those of my employer.
>
>
> "fee pack" <defection_at_163.net> wrote in message
> news:4e732814.0208220048.489d18d6_at_posting.google.com...
> > after running the file my.sql (see below), i encounter some error:
> > when i try to edit the table data, the following error displayed:
> >
> > ORA-04045 ... SYS.DBMS_OUTPUT
> > ORA-06508 ...
> > ORA-06512 ...
> > ORA-06508 ...
> > ORA-06512 ...
> >
> >
> > then i go to the package SYS.DBMS_OUTPUT, the status is "invalid", i
> > try to recompile it, the following error displayed:
> >
> > ORA-04045 ORA-04045: errors during recompilation/revalidation of
> > SYS.CDC_ALTER_CTABLE_BEFORE
> > ORA-06508 ...
> > ORA-06512 ...
> > ORA-06508 ...
> > ORA-06512 ...
> >
> > when i try to compile SYS.CDC_ALTER_CTABLE_BEFORE, the error message
> > displayed:
> > ORA-00604 ...
> > ORA-04045 ...LBACSYS.LBAC_EVENTS
> > ORA-06512 ...
> > ORA-06508 ...
> > ORA-06512 ...
> >
> > when i try to compile LBACSYS.LBAC_EVENTS, the error message
> > displayed:
> > ORA-04045 ORA-04045: errors during recompilation/revalidation of
> > SYS.CDC_ALTER_CTABLE_BEFORE
> > ORA-06508 ...
> > ORA-06512 ...
> > ORA-06508 ...
> > ORA-06512 ...
> >
> >
> > it seems SYS.CDC_ALTER_CTABLE_BEFORE call LBACSYS.LBAC_EVENTS, and
> > latter also call the former --- that is a circle!
> >
> >
> > my db works well before running the file my.sql. it seems the
> > execution of catalog.sql and catproc.sql cause the error. i am using
> > 9i, and the document histories show that the catalog.sql and
> > catproc.sql are last updated in Apr,2001.
> >
> >
> > my questions are:
> > 1.with 9i db, do i need to run the two files?
> > 2.how can i recover my db?(i don't have backup, can i use redoXX.log?)
> >
> >
> > ***my.sql***
> >
> > connect sys/change_on_install_at_orcl as sysdba
> >
> > @/opt/oracle/rdbms/admin/catalog.sql
> >
> >
> > connect sys/change_on_install_at_orcl as sysdba
> >
> >
> > CREATE database mydb
> > (
> > ...
> > )
> >
> > @/opt/oracle/rdbms/admin/catproc.sql
> >
> > ***my.sql***
Received on Fri Aug 23 2002 - 04:04:21 CDT