run catalog.sql during the day

[kukuluku]

Hello,

I need to ask whether people should run catalog.sql only in maintenance hour.

What happened was we had a situation that we can not drop any SQLPLUS users. It generates following errors:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 7

So Oracle suggested me to run catalog and catproc. So I ran that after 5:00pm thinking not many users in the system and it's not busy.

However, at the beginning of running catalog, it gives LOCK error. I cancelled the job right away, but then no one can access the system... extremely slow.

Now Oracle is saying this is a bug (the LOCK message) and won't be fixed until version 10g (we are on 9.2.0.8).

Now, I have to explain to the managers about this. Can anyone give input about whether catalog should/can be run outside of maintenance time. I had run it before without any problem but got burn bad this time by this bug.

I really appreciate your input.

 

[SantaMufasa]

Kuku, is there a time when your system is lightly used? If so, I would advise users ahead of time that you plan a scheduled maintenance session for about 30 minutes. At the appointed time, do a "shutdown immediate" (which ensures that everyone is off the system), then a "startup restrict", which keeps everyone except for DBAs off the system while you run catalog.sql. Once the script(s) finish, you can do an "ALTER SYSTEM DISABLE RESTRICTED SESSION;", which allows logins by regular users.

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[kukuluku]

Thank you Mufasa. Maybe I wasn't clear...

I just want to know if catalog is a common script that you can run while the system has low usage. I like to know if I just had bad luck this time due to the bug, or is catalog normally run only during maintenance window?

 

[SantaMufasa]

I tend to think is was "bad luck". I've successfully re-run catalog.sql during low-usage times.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[kukuluku]

Thank you for your input. I really appreciate it.