Re: Rebuilding the system schema

From: <fitzjarrell_at_cox.net>
Date: Wed, 21 May 2008 13:06:40 -0700 (PDT)
Message-ID: <f099f5c9-821c-4e01-b78c-3eb79f648efd@i76g2000hsf.googlegroups.com>


On May 21, 12:38 pm, haganahtrai..._at_gmail.com wrote:
> On May 21, 1:20 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On May 21, 12:35 pm, DA Morgan <damor..._at_psoug.org> wrote:
>
> > > haganahtrai..._at_gmail.com wrote:
> > > > We have a test instance and a script that was run agains the system
> > > > schema which dropped some of the objects- it seems that the system
> > > > works but some features do not work such as dropping a user - Is there
> > > > a way to rebuild the system schema to get all of the objects back in?
>
> > > What version?
>
> > > Likely you can rebuild by running the same installation scripts Oracle
> > > uses us as catalog.sql and catproc.sql. But based on the fact that you
> > > already did something more than a little destructive I am very hesitant
> > > to suggest a course of action as you might just make things worse. Is
> > > this a production database?
> > > --
> > > Daniel A. Morgan
> > > Oracle Ace Director & Instructor
> > > University of Washington
> > > damor..._at_x.washington.edu (replace x with u to respond)
> > > Puget Sound Oracle Users Groupwww.psoug.org
>
> > Note that changes to user system objects should not interfer with your
> > ability to execute CREATE USER commands as long as the username
> > running the command has the privilege.  By "system schema" do you mean
> > username = SYSTEM or SYS?
>
> > If system, then do you have an export?
>
> > If SYS then running catalog and catproc plus any optional featues
> > created via dbms_ scripts would be my approach.
>
> > If that does not work then re-create the database and reload it from
> > an export since this is test that should be good enough.
>
> > HTH -- Mark D Powell --- Hide quoted text -
>
> > - Show quoted text -
>
> This was the system user- logged in as system and ran a script that
> drops all of the users objects- killed the process about 1/3 of the
> way through.Looking at our production instance there are over 450
> objects in the system schema )Logged in as system) and on the test
> database there are 389-
>
> I have a backup of the database- could I just remove the system.dbf
> file and replace this with the system.dbf file from my NetBackup
> server?

No, it isn't that simple as you would need to recover the SYSTEM tablespace from your archived redo logs. I can only presume you are not running this test database in archivelog mode so such an act (dropping an old copy of the SYSTEM tablespace datafile) would be futile.

> Or is it better to run the catalog and catproc scripts?
>

Better is a relative term. Again, you *might* be able to recover dropped tables/indexes from the RECYCLEBIN; after that running the catalog.sql and catproc.sql scripts would probably be a viable option.

Possibly you should have prefixed the execution of your script with the following PL/SQL block:

whenever sqlerror exit sql.sqlcode

begin

        if USER like 'SYS%' then
                select cannot_be_sys_or_system from dual;
        end if;

end;
/

@<your script name here>

SQL*Plus would have exited with the following error:

SQL> @error_test_ex

                select cannot_be_sys_or_system from dual;
                       *

ERROR at line 3:
ORA-06550: line 3, column 10:
PL/SQL: ORA-00904: "CANNOT_BE_SYS_OR_SYSTEM": invalid identifier ORA-06550: line 3, column 3:
PL/SQL: SQL Statement ignored

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Data Mining option

and the damage would not have been done.

Forethought is better than hindsight.

David Fitzjarrell Received on Wed May 21 2008 - 15:06:40 CDT

Original text of this message