Re: Rebuilding the system schema

From: <fitzjarrell_at_cox.net>
Date: Wed, 21 May 2008 12:25:16 -0700 (PDT)
Message-ID: <5d28a8ce-182b-40aa-8038-a06bbc781fb7@f36g2000hsa.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? Or is it better to run the catalog and catproc scripts?
>
> Does Oracle offer a "Repair" feature through the installer?  This is
> version 10.2
>
> Thanks for the replies....- Hide quoted text -
>
> - Show quoted text -

Yes, it's called the RECYCLEBIN and you may be able to recover dropped tables/indexes if they weren't dropped with the PURGE option. As far as I know objects such as packages/procedures/functions/triggers aren't included in the RECYCLEBIN.

Read here:

http://www.psoug.org/reference/recyclebin.html

and see what you may be able to salvage.

David Fitzjarrell Received on Wed May 21 2008 - 14:25:16 CDT

Original text of this message