dba studio,urgent!!!!!!!!!!! 1

[mkey]

Hi all,
I think I'm in deep trouble. Without going through too much detail I tried to run the catalog.sql which is located under oracle8/rdbms/admin folder. I do not know what exactly happend but I cannot seem to get in to one of my database in dba studio. So using sys/change_on_install I was able to login. Now what I need to ask you is that there are many schemas under this specific database and has corresponding username and password. How can I set this back as before. I am not really familiar with dba stuff. I'm only a developer. So can you guys tell me what rolls and users i should add. I do have dba privileges.
mkey

 

[karluk]

Hmm, it's not a good idea to run the catalog scripts unnecessarily, but I'm not convinced you've really messed anything up. My understanding of catalog.sql is that it just defines the catalog views. I think that the fixed tables upon which the catalog views are based will remain unaffected. If so, there's a good chance that you don't have to do much of anything to recover.

What specific error message do you get when you try logging into DBA Studio? Perhaps some catalog objects became invalid when you ran catalog.sql.

 

[mkey]

Hi Karluk,
The error messeage I getting is this:
VTADB-1000 This application requires more database privileges than you currently granted to you. Contact your DBA for more information.
Thank you,
mkey

 

[mkey]

Hi,
I am trying to log in as scott/tiger user. I am able to use this username and passwork to login in sql plus. I also added the SELECT_CATALOG_ROLE and DBA roll to my scott user. So I don't understand what's going on!
mkey

 

[karluk]

I created an id and gave it nothing but connect privileges and the DBA role. I can connect using this id using DBA Studio.

Your attempt to grant DBA to Scott may have failed. Can you log in to DBA Studio with another id and verify Scott's privileges?

Another possibility is that you really do have catalog problems, perhaps invalid objects as a side effect of the catalog.sql script. Try running the following query.

select object_name,object_type from dba_objects
where owner='SYS' and status='INVALID';

If you have a lot of invalid catalog objects, you might try recompiling. That sometimes works if there aren't actually any errors in the invalid objects. The command to do this is

execute dbms_utility.compile_schema('SYS');

If you still have a lot of invalid objects, you may have to rerun all the catalog scripts, such as catproc.sql. See your installation guide for details.

 

[mkey]

Hi Karluk,
When I ran this query(select object_name,object_type from dba_objects
where owner='SYS' and status='INVALID' I am not getting any invalid objects. I guess to some extent its a good news. I cannot seem to login as any other user. Including system/manager.
Thank you for all your support.

 

[mkey]

Just a quick question. Can I login a sys/change_on_install and give dba privilege to another user? If I do that do I have to change the password of that user?

 

[karluk]

I would tend to run catproc.sql anyway, just to see what happens. Catalog.sql and catproc.sql are usually run together, so there may be some dependencies.

 

[mkey]

Do I still have to run the execute utility (dbms_utility.compile_schema('SYS')) even though I cannot seem to find any invalid objects?
mkey

 

[karluk]

No. That doesn't seem to be your problem.

 

[mkey]

I am still not sure if I should run the catproc.sql. I just don't want to mess things up anyfurther. I forgot to mention that yesterday when I was running the catlog.sql I didn't execute it completely. I stoped the sql plus halfway through because I was not sure what the catlog.sql was doing. So I just want to make sure that when I run the catproc.sql its not going to mess anything up. This database is not a tester. Eventhough we have backup I am still conserned to run this script.
thankyou,
mkey

 

[karluk]

Naturally that's your decision. It's easy for us to make recommendations, but you're the one that's held responsible for the consequences.

However, I would strongly recommend that you rerun catalog.sql and let it finish. If you look at the script it has lots of drops of public synonyms. If you interrupted the execution after the drop but before the create, you will be missing something that's supposed to be there.

You might also want to check Metalink document 39969.996. Someone posts a forum question about rerunning catalog and catproc. The answer is that it's ok to do so, but with a few caveats.

 

[karluk]

I've thought of a likely explanation for the problem you're having. It's similar to my earlier idea about missing synonyms caused by interrupting the execution of catalog.sql.

Catalog.sql also contains a bunch of grants on the catalog views. If you execute a "create or replace" view without also executing the associated grant(s), you will have missing privileges. I count hundreds of grants to the select_catalog_role. It may very well be that granting select_catalog_role to Scott does no good, because that role doesn't have the right permissions on the underlying catalog views. Hence your VTADB-1000 error.

All in all the evidence strongly points to the cancellation of catalog.sql as being the source of your problem. You probably would have been ok if you had just let it run to completion. Hopefully you will find a way to test this without risking your production database.

 

[mkey]

Hi Karluk,
I got the go ahead to run both the catalog.sql and catproc.sql scripts. There are few schemas under this specific database. Does it matter under what schema I run these scripts? Or should I log in as sys in sql plus and run these scripts?
Thank you!!!mkey

 

[karluk]

You definitely have to run the scripts as SYS, the owner of the catalog. Otherwise you will end up with a duplicate of the Oracle catalog under another schema.

Good luck. I hope this solves your problem.

 

[mkey]

OOPS, i JUMPED THE GUN...I ran against the scott schema. I keep doing things wrong. What should I do now...

 

[karluk]

I doubt that running as Scott is a major problem. You can probably just drop the duplicate views.

 

[mkey]

Hi my friend,
Thank you so much for all your help. It seems everything is back to track. Hopefully in the future I will be more careful. Thank you again for your great support.
mkey

 

[mkey]

Hi Karluk,
Remeber I ran the catalog.sql and catproc.sql on scott schema. Well Most of the packages and package body has invalid objects. Now obviously I need to delete them. If I delete these views packages does it going to create problems in my sys schema. I have chacked my sys schema and everything seems to have valid objects except these following listed once:
DBMS_ASYNCRPC_PUSH
PACKAGE BODY

DBMS_DEFER_INTERNAL_SYS
PACKAGE BODY

DBMS_DEFER_SYS_PART1
PACKAGE BODY

DBMS_IAS_MT_INST
PACKAGE BODY

DBMS_REPCAT_MIG_INTERNAL
PACKAGE BODY

DBMS_REPCAT_RGT
PACKAGE BODY

How can I run these packages? Thank you!!
mkey