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

From: fee pack <defection_at_163.net>
Date: 26 Aug 2002 00:41:45 -0700
Message-ID: <4e732814.0208252341.310e6f4a@posting.google.com>

i have recovered my db, thanks a lot!!!

there are some error messages showed when i run catalog.sql and catproc.sql i could not copy all of them...

when running catalog.sql:

create or replace public synonym ALL_TAB_COLUMNS for ALL_TAB_COLUMNS

                         *

ERROR at line 1:
ORA-00922: missing or invalid option

and some error message indicating that an object is being used by another object.

when running catporc.sql:

BEGIN
*
ERROR at line 1:

ORA-24001: cannot create QUEUE_TABLE, SYS.AQ_SRVNTFN_TABLE already exists
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 2280
ORA-06512: at "SYS.DBMS_AQADM", line 56
ORA-06512: at line 2

BEGIN
*
ERROR at line 1:

ORA-24006: cannot create QUEUE, SYS.AQ_SRVNTFN_TABLE_Q already exists
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 2584
ORA-06512: at "SYS.DBMS_AQADM", line 116
ORA-06512: at line 2

PL/SQL procedure successfully completed.

create or replace type ku$_simple_col_t as object *
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:<ak4qkp$ivu$1_at_babylon.agtel.net>...
> "fee pack" <defection_at_163.net> wrote in message
> news:4e732814.0208221911.279160a3_at_posting.google.com...
>
> > do u mean i can recover my db by following these steps?
> > step
> > 1.ALTER SYSTEM SET _SYSTEM_TRIG_ENABLED='FALSE'
> > 2.run catalog.sql, catproc.sql
> > 3.ALTER SYSTEM SET _SYSTEM_TRIG_ENABLED='true'
> >
> > by the way, i am using 9i, do i need catalog.sql and catproc.sql?
>
> You can recover your db by following these steps, but you only
> need to do this *once*. You don't need to run catalog or catproc
> normally, this is only done when database is created or updated
> with a patchset. And you indeed need both scripts - they create
> all standard database objects needed for it to operate. You just
> don't need to run them now and then. So, correct sequence for
> recovering your db from the state it is in now is this:
>
> -- to prevent unprivileged users from logging in or doing things
> -- while you do maintenance; quiesce may take some time to
> -- execute as it will wait until all unprivileged sessions finish their
> -- active transactions/fetches and go to inactive state.
> alter system enable restricted session;
> alter system quiesce restricted;
> -- disable system triggers
> alter system set _system_trig_enabled='false';
> @?/rdbms/admin/catalog
> @?/rdbms/admin/catproc
> -- this script is only needed if you need Java VM
> @?/javavm/install/initjvm
> -- re-enable system triggers
> alter system set _system_trig_enabled='true';
> -- restore normal mode of operation
> alter system unquiesce;
> alter system disable restricted session;
>
> As a matter of fact, since you seem to be not very proficient with
> Oracle maintenance, I would recommend using GUIs provided with
> Oracle for maintenance and DBA tasks, like creating new tablespaces.
> Otherwise you face risk of damaging your database. The script
> above should restore system catalog to stable state and you SHOULD
> NOT touch it afterwards unless you are installing a patchset and
> strictly following patchset installation instructions.
>
> > any idea for my script?
> > does the following sequence make sense:
> > 1.run catalog.sql
> > 2.create tablespace
> > 3.run catproc.sql
>
> No, it doesn't. Simple operation of tablespace creation has nothing to
> do with recreating system catalog. Remove steps 1 and 3 and you'll
> be allright here :) Though I still recommend you to use GUI for
> creating tablespace - it provides you with all options you can set for
> operation in a user-friendly manner, so you don't have to familiarize
> yourself with CREATE TABLESPACE command and all of its options.
Received on Mon Aug 26 2002 - 02:41:45 CDT