In creating a Local Managed Tablespace database, do I need to run catalog and catpro? Although the information is stored in data file bitmap, doesn't it still need dictionary to be viewed by users?

Tom,

I want to reinstall some Oracle options from my production database. I always made it using Oracle Univesal installer.
But some option (Spatial and Data Mining) I can still see in V$OPTIONS and in sql headear during the start.
I found that the rerun of catalog.sql can help.
It is one of a prod. database of our customers.
What impact can this make on the database and applications

Thanks in advance

Olaf

Tom.

I try to deinstall the options regarding Oracle licenses
(We are still in 30-days test period).
If I still see the Data Mining option in the sql header,
can it causes a problem (it always deinstalled with OUI)

Olaf

By mistake i reexcuted the catproc.sql in the production database. Ofcourse the normal database activities are o.k. But we are not able to access any of the data dictionary views(v$.,DBA..)
Can you please suggest me a way to solve this problem.

Thank you very much..

I rerun the catproc.sql script with non-sys user.
That may be causing the problem.

Thank you very much.

Thank you tom.

I dropped the user schema with cascade option and rerun the catproc.sql. Now everything is fine.

Of course I know that is a big mistake, but it happened because of lot of windows on my screen.

Thank you very much again.

Tom,

You said:

You should never do that in a database where people are logged in running applications.

They recreate all of the plsql packages -- so anyone running plsql in the form of stored procedures, triggers and so on would be massively impacted (it would
probably just "hang" since standard cannot be rebuilt until no one is using it).

They recreate all of the data dictionary views -- which are heavily used by many applications.


There should never be a reason to run either of these scripts on a live instance.They are run only during install and upgrade time.

But Oracle Support ask me to run catalog and catproc sql scripts in this database to recreate the
dbms_registry package in my production database.

So what should I do?

Dawar

Dear tom

while making the following select statment

select count(1) from all_synonyms where synonym name ='EMPS_DATA';

it takes alot of time while we have load(about 2000 users connected) on server more than 5 minutes while if there is no load it takes about 30 seconds is catalog.sql and catproc.sql will have any empact if i run them or do you have another recommedation ?

Dear tom we working on 9i release 1 with patch 4

im trying to run catalog.sql script in my database and im experincing hang on the script run .please tell why its happpening and solution to overcome .


thanks in advance

vinod

Hi Tom,
Can you please help in fixing this PLS-00553 Characterset is not recognized.

I have checked that this error usually comes either in two situations.
1. Environment mismatch Settings
2. Mix up of charactersets

i have checked in one form that the support has advised to execute catproc.sql to overcome it.

I'm in confusion after looking at this thread. Can you please assist us in fixing it on our PROD server.

hi,teacher TOM,
  now I met a very thorny issue, we can not use the expdp，yestoday it was good,but now some errors happened!

[oracle@dbasmk1]:BOSDB1:/home/oracle$ expdp bd/**** directory=DUMPDIR_LINHC dumpfile=bd.dmp logfile=bd_exp_name.log schemas=bd;

Export: Release 11.2.0.2.0 - Production on Wed Sep 4 14:14:02 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORA-31626: job does not exist
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTDBY"
ORA-06512: at "SYS.KUPV$FT", line 991
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTDBY"


i serchered in metalink and not yet found solution

now ,i collected some information following:

SQL> SELECT owner, object_type, object_name, object_id, status
  2    FROM SYS.dba_objects
  3   where object_name = 'DBMS_INTERNAL_LOGSTDBY';

OWNER                          OBJECT_TYPE         OBJECT_NAME                     OBJECT_ID STATUS
------------------------------ ------------------- ------------------------------ ---------- -------
SYS                            PACKAGE             DBMS_INTERNAL_LOGSTDBY               5171 VALID
SYS                            PACKAGE BODY        DBMS_INTERNAL_LOGSTDBY              11943 INVALID


SQL> alter package DBMS_INTERNAL_LOGSTDBY compile;

Warning: Package altered with compilation errors.


select * from dba_errors where name = 'DBMS_INTERNAL_LOGSTDBY';
SYS DBMS_INTERNAL_LOGSTDBY PACKAGE BODY 1 594 13 PL/SQL: ORA-00942: table or view does not exist ERROR 0
SYS DBMS_INTERNAL_LOGSTDBY PACKAGE BODY 2 593 4 PL/SQL: SQL Statement ignored ERROR 0
SYS DBMS_INTERNAL_LOGSTDBY PACKAGE BODY 3 682 16 PL/SQL: ORA-00942: table or view does not exist ERROR 0
SYS DBMS_INTERNAL_LOGSTDBY PACKAGE BODY 4 681 3 PL/SQL: SQL Statement ignored ERROR 0
SYS DBMS_INTERNAL_LOGSTDBY PACKAGE BODY 5 687 18 PL/SQL: ORA-00942: table or view does not exist ERROR 0
SYS DBMS_INTERNAL_LOGSTDBY PACKAGE BODY 6 686 4 PL/SQL: SQL Statement ignored ERROR 0
SYS DBMS_INTERNAL_LOGSTDBY PACKAGE BODY 7 704 51 PL/SQL: ORA-00942: table or view does not exist ERROR 0
SYS DBMS_INTERNAL_LOGSTDBY PACKAGE BODY 8 704 3 PL/SQL: SQL Statement ignored ERROR 0
SYS DBMS_INTERNAL_LOGSTDBY PACKAGE BODY 9 721 15 PL/SQL: ORA-00942: table or view does not exist ERROR 0
SYS DBMS_INTERNAL_LOGSTDBY PACKAGE BODY 10 720 3 PL/SQL: SQL Statement ignored ERROR 0
SYS DBMS_INTERNAL_LOGSTDBY PACKAGE BODY 11 746 43 PL/SQL: ORA-00942: table or view does not exist ERROR 0
SYS DBMS_INTERNAL_LOGSTDBY PACKAGE BODY 12 746 3 PL/SQL: SQL Statement ignored ERROR 0
SYS DBMS_INTERNAL_LOGSTDBY PACKAGE BODY 13 760 17 PL/SQL: ORA-00942: table or view does not exist ERROR 0
SYS DBMS_INTERNAL_LOGSTDBY PACKAGE BODY 14 759 3 PL/SQL: SQL Statement ignored ERROR 0
SYS DBMS_INTERNAL_LOGSTDBY PACKAGE BODY 15 771 43 PL/SQL: ORA-00942: table or view does not exist ERROR 0
SYS DBMS_INTERNAL_LOGSTDBY PACKAGE BODY 16 771 3 PL/SQL: SQL Statement ignored ERROR 0
SYS DBMS_INTERNAL_LOGSTDBY PACKAGE BODY 17 784 15 PL/SQL: ORA-00942: table or view does not exist ERROR 0
SYS DBMS_INTERNAL_LOGSTDBY PACKAGE BODY 18 783 3 PL/SQL: SQL Statement ignored ERROR 0
SYS DBMS_INTERNAL_LOGSTDBY PACKAGE BODY 19 787 19 PL/SQL: ORA-00942: table or view does not exist ERROR 0
SYS DBMS_INTERNAL_LOGSTDBY PACKAGE BODY 20 787 5 PL/SQL: SQL Statement ignored ERROR 0



SELECT owner, object_type, object_name, object_id, status
  FROM SYS.dba_objects
 where object_name = 'DBMS_INTERNAL_LOGSTDBY';
1 SYS PACKAGE DBMS_INTERNAL_LOGSTDBY 5171 VALID
2 SYS PACKAGE BODY DBMS_INTERNAL_LOGSTDBY 11943 INVALID

SELECT B.*
FROM PUBLIC_DEPENDENCY A, SYS.DBA_OBJECTS B
WHERE A.OBJECT_ID = 11943 AND A.REFERENCED_OBJECT_ID = B.OBJECT_ID AND OBJECT_TYPE = 'TABLE'
ORDER BY OWNER, OBJECT_NAME;

1 SYS AUDIT_ACTIONS  3539 3539 TABLE 2010/10/1 18:49:17 2010/10/1 18:49:17 2010-10-01:18:49:17 VALID N N N 1 
2 SYS COL$  21 2 TABLE 2010/10/1 18:46:53 2010/10/1 19:25:01 2010-10-01:18:46:53 VALID N N N 1 
3 SYS DUAL  116 116 TABLE 2010/10/1 18:46:54 2010/10/1 18:47:21 2010-10-01:18:46:54 VALID N N N 1 
4 SYS IND$  19 2 TABLE 2010/10/1 18:46:53 2010/10/1 19:12:51 2010-10-01:18:46:53 VALID N N N 1 
5 SYS INDPART$  573 573 TABLE 2010/10/1 18:47:24 2010/10/1 19:12:51 2010-10-01:18:47:24 VALID N N N 1 
6 SYS JOB$  288 288 TABLE 2010/10/1 18:47:20 2013/8/17 10:46:12 2010-10-01:18:47:20 VALID N N N 1 
7 SYS OBJ$  18 18 TABLE 2010/10/1 18:46:53 2010/10/1 19:55:01 2010-10-01:18:46:53 VALID N N N 1 
8 SYS TAB$  4 2 TABLE 2010/10/1 18:46:53 2010/10/1 19:12:51 2010-10-01:18:46:53 VALID N N N 1 
9 SYS TS$  16 6 TABLE 2010/10/1 18:46:53 2010/10/1 19:55:04 2010-10-01:18:46:53 VALID N N N 1 
10 SYS USER$  22 10 TABLE 2010/10/1 18:46:53 2010/10/1 19:55:03 2010-10-01:18:46:53 VALID N N N 1 
11 SYSTEM LOGMNR_LOG$  1097 1722050 TABLE 2010/10/1 18:47:27 2013/9/4 11:08:55 2013-09-04:10:21:23 VALID N N N 1 
12 SYSTEM LOGMNR_SESSION$  1132 1132 TABLE 2010/10/1 18:47:28 2010/10/1 18:47:28 2010-10-01:18:47:28 VALID N N N 1 


now ,i don't know how to resolve this problem.

Hi Tom,

I have a environment that DBA_REGISTRY and others objects are invalids.

Can I rerun catalog and catproc scripts? If Yes the order would be as below:

$sqlplus "/as sysdba"
SQL> shutdown immediate
SQL> startup upgrade
SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql
SQL > @?/rdbms/admin/utlrp.sql
SQL> shutdown immediate
SQL> startup

Many Regards,

José Leme.

Hi Tom ,

Could u please tell me what would be the Impact on a live database if catalog.sql and catproc.sql are run?

We are using 12.2.0 version two node RAC database.

Regards,
Javed

Thanks.

Except that sometimes Enterprise Manager complains about no snapshots and sql monitor cannot be used. the soln, according to Oracles own blog is to run these 2 scripts.