Re: Rebuilding the system schema
Date: Thu, 22 May 2008 06:25:42 -0700 (PDT)
On May 21, 1: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 -
Since you are on 10.2 I suggest looking in the recycle bin as suggested and restoring everything you can from there. Then if you have a recent export file you can run a user import for system with ignore=n to bring any missing objects such as packages.
If you resort to recovery you will have to restore your entire database so that you can run a point in time recovery to just before the wrong script was run.
Before running any recovery operations I strongly suggest you read the Backup and Recovery Guide.
HTH -- Mark D Powell -- Received on Thu May 22 2008 - 08:25:42 CDT