cancel
Showing results for 
Search instead for 
Did you mean: 

SQL error -20003 at location stats_tab_collect-20

Former Member
0 Kudos

Hello All,

we are executing ths statistics with the followin command

brconnect -u / -c -f stats -t all -f collect

but the proccess report errors like:

BR0301E SQL error -20003 at location stats_tab_collect-20, SQL statement:

'BEGIN DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => '"SAPSR3"', TABNAME => '"/BEV3/CHCMVWPRO"', ESTIMATE_PERCENT => NULL, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', DEGREE => NULL, CASCADE => TRUE, NO_INVALIDATE => FALSE); END;'

ORA-20003: Specified bug number (5099019) does not exist

ORA-06512: at "SYS.DBMS_STATS", line 14457

ORA-06512: at "SYS.DBMS_STATS", line 14477

ORA-06512: at line 1

BR0886E Checking/collecting statistics failed for table SAPSR3./BEV3/CHCMVWPRO

BR0301E SQL error -20003 at location stats_tab_collect-20, SQL statement:

'BEGIN DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => '"SAPSR3"', TABNAME => '"/BEV3/CHCTRGI"', ESTIMATE_PERCENT => 1, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', DEGREE => NULL, CASCADE => TRUE, NO_INVALIDATE => FALSE); END;'

ORA-20003: Specified bug number (5099019) does not exist

ORA-06512: at "SYS.DBMS_STATS", line 14457

ORA-06512: at "SYS.DBMS_STATS", line 14477

ORA-06512: at line 1

BR0886E Checking/collecting statistics failed for table SAPSR3./BEV3/CHCTRGI

How we can fix this error?

Thanks

Hernando

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi All the above reported problem is solved after applying the latest opatch below are the steps followed,

as updated in the note -

Note 1137346 - Oracle 10.2.0: Patches/patch collections for Oracle 10.2.0.4

List of patch collections that are released for SAP and must be installed

Patch collection for platform MS Windows (32-bit)

10204_Patch26_WIN32.zip (Patch: 8880857) 8880857)

10204_Patch22_WIN32.zip (Patch: 8559466)

I have downloaded the "10204_Patch26_WIN32.zip (Patch: 8880857) 8880857)" and then unzip it, as i already have the latest OPATCH

I have set the environment variable to the opatch directory, following the read me, in the patch zip file and continued with the steps mentioned in the read me as below,

no further adjustments i have done *not even i have set the parameter "_fix_control" or anything as mentioned in other notes all has been taken care.

3.3.2 Patch Installation Instructions for Single Instance

Follow these steps:

Cleanly shut down all processes running from the ORACLE_HOME.

Set the ORACLE_HOME environment variable to the correct Oracle home that needs to be patched.

> set ORACLE_HOME=<Oracle Home Path>

Go to the directory where you downloaded the patch:

> cd 8880857

Ensure that the directory containing the OPatch script appears in your PATH setting. Enter the following command to run OPatch:

> opatch apply

Thanks and regards

Rajendra Ponangi

Former Member
0 Kudos

hello All,

the problem was solved as follow:

1) I Rolled back the 8599814 patch and any others applied post this patch.

2) Via opatch reapply the missing patches in sequence one by one. Be sure to address all recommended patches in the note:

1137346 - Oracle Database 10g: Patches for Release 10.2.0.4, I followed the post installation steps of patch 8599814

***************

Post-install steps:

startup database

connect using sqlplus as SYSDBA

start $ORACLE_HOME/rdbms/admin/dbmsstat.sql

start $ORACLE_HOME/rdbms/admin/prvtstas.plb

start $ORACLE_HOME/rdbms/admin/prvtstat.plb

exit sqlplus

***************

3) As orasid, execute the following:

relink all

4) I set up the oracle parameters according to SAP note 830576 - Parameter recommendations for Oracle 10g, specially event and fixcontrol adding '5099019:ON'.

5) I updated the statistics according to note 838725 - Oracle Database 10g New database statistics

brconnect -u / -c -f stats -t oradict_stats

All finished OK

thanks for your help and suggestions.

Hernando Polania

0 Kudos

Hi Hernando,

Have you tried to search for notes?

or the top right search box in the Forum.

I'm asking because looking for notes using something as "difficult" as ORA-20003 brings one SAP note:

Note 838725 - Oracle Database 10g: New database statistics

Would you mind to read it and tell us the result?

Thanks

Former Member
0 Kudos

hello,

from note 838725 - Oracle Database 10g: New database statistics, we ran the command :

brconnect -u / -c -f stats -t oradict_stats

but the same error were shown

BR0280I BRCONNECT time stamp: 2009-09-11 17.35.11

BR1311I Starting collection of Oracle dictionary statistics...

BR0285I This function can take several seconds/minutes - be patient...

BR0280I BRCONNECT time stamp: 2009-09-11 17.35.24

BR0301E SQL error -20003 at location stats_oradict_collect-4, SQL statement:

'BEGIN DBMS_STATS.GATHER_DICTIONARY_STATS (ESTIMATE_PERCENT => NULL, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', GRANULARITY => 'ALL', CASCADE => TRUE, OPTIONS => 'GATHER', NO_INVALIDATE => FALSE); END;'

ORA-20003: Specified bug number (5099019) does not exist

ORA-06512: at "SYS.DBMS_STATS", line 14611

ORA-06512: at "SYS.DBMS_STATS", line 14957

ORA-06512: at "SYS.DBMS_STATS", line 17261

ORA-06512: at "SYS.DBMS_STATS", line 17303

ORA-06512: at line 1

BR1313E Collection of Oracle dictionary statistics failed

thanks

Former Member
0 Kudos

hello,

i find if oracle patch 5099019 is installed and I found it in patch 8599814, but I don´t know how can I set the oracle parameter FIXCONTROL '5099019:ON'

If I set the followind string in oracle parameter

fixcontrol='5099019:ON','5705630:ON','5765456:3','6121403:ON','6329318:ON','6399597:ON','6430500:ON','6440977:ON','6626018:ON','6670551:ON','6972291:ON','7325597:ON','7692248:ON','7891471:ON'

the DB doesn´t start

how I can set this parameter to oracle 10.2.0.4 on solaris 10

thanks

Hernando

Former Member
0 Kudos

Hernando,

Do give us the messgae you get when you say Database does not start.

FIXCONTROL is a special hidden parameter introduced in 10.2.0.2 which can be used to enable or disable certain bug fixes.

It is not updated with bugfixes, You are setting it manually in order to enable or disable certain bugfixes with

FIXCONTROL='bug_number:ON|OFF'

TODO multiples we do for example

alter session set "_fix_control"='xxxxxx:OFF','xxxxxxx:OFF';

Why do you have so many of them ?? Did you carefully read all fo the notes sassociated with them

Pravin

Former Member
0 Kudos

hello, to update, I receive the following recommendation

The error "ORA-20003: Specified bug number (5099019) does not exist

seems caused by fix of bug 5099019 is not installed correctly.

Could you please first check if the merge fix 8299095 has been

installed successfully? Check lsinventory and check if the post

installation steps have been performed well.

***************

Post-install steps:

startup database

connect using sqlplus as SYSDBA

start $ORACLE_HOME/rdbms/admin/dbmsstat.sql

start $ORACLE_HOME/rdbms/admin/prvtstas.plb

start $ORACLE_HOME/rdbms/admin/prvtstat.plb

exit sqlplus

***************

Once those steps have been performed would you please check the databasefor invalid objects per the instructions in OSS note 648203?

**********************************************************************************

I followed the steps in recommendation:

the fix is installed on patch 8599814.

I started the DB and ran the SQL scripts:

SQL> start $ORACLE_HOME/rdbms/admin/dbmsstat.sql

Package created.

No errors.

Synonym created.

Grant succeeded.

create role gather_system_statistics

*

ERROR at line 1:

ORA-01921: role name 'GATHER_SYSTEM_STATISTICS' conflicts with another

user or

role name

Grant succeeded.

Grant succeeded.

Library created.

SQL> start $ORACLE_HOME/rdbms/admin/prvtstas.plb

Package created.

No errors.

Package body created.

No errors.

SQL> start $ORACLE_HOME/rdbms/admin/prvtstat.plb

Package body created.

No errors.

PL/SQL procedure successfully completed.

*********************************************

According to note 648203 I search for invalid objects

SQL> SELECT SUBSTR(OWNER, 1, 20) OWNER,SUBSTR(OBJECT_NAME, 1, 35)

OBJECT_NAME,

2 SUBSTR(OBJECT_TYPE, 1, 20) OBJECT_TYPE

3 FROM DBA_OBJECTS

4 WHERE STATUS = 'INVALID';

no rows selected

SQL> SELECT SUBSTR(COMP_ID, 1, 20) COMP_ID,

2 SUBSTR(COMP_NAME, 1, 40) COMP_NAME,

3 STATUS

4 FROM DBA_REGISTRY;

COMP_ID

-

-


COMP_NAME

-

-


STATUS

-

-


CATALOG

Oracle Database Catalog Views

VALID

CATPROC

Oracle Database Packages and Types

VALID

SQL> SELECT O.NAME FROM OBJ$ O, REGISTRY$ R

2 WHERE O.STATUS > 1 AND

3 (O.CTIME BETWEEN R.DATE_LOADING AND R.DATE_LOADED OR

4 O.MTIME BETWEEN R.DATE_LOADING AND R.DATE_LOADED OR

5 O.STIME BETWEEN R.DATE_LOADING AND R.DATE_LOADED);

no rows selected

********************************************

According to note 838725 I executed the following script:

SQL> begin DBMS_STATS.GATHER_DICTIONARY_STATS(

2 ESTIMATE_PERCENT => NULL,

3 METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',

4 GRANULARITY => 'ALL',

5 CASCADE => TRUE,

6 OPTIONS => 'GATHER',

7 NO_INVALIDATE => FALSE);

8 end;

9 /

begin DBMS_STATS.GATHER_DICTIONARY_STATS(

*

ERROR at line 1:

ORA-20003: Specified bug number (5099019) does not exist

ORA-06512: at "SYS.DBMS_STATS", line 14611

ORA-06512: at "SYS.DBMS_STATS", line 14957

ORA-06512: at "SYS.DBMS_STATS", line 17261

ORA-06512: at "SYS.DBMS_STATS", line 17303

ORA-06512: at line 1

And, if I run the command from the same note

volta:oracmd 53% brconnect -u / -c -f stats -t oradict_stats

BR0801I BRCONNECT 7.00 (43)

BR0805I Start of BRCONNECT processing: ceblmvwb.sta 2009-09-12 18.08.17

BR0484I BRCONNECT log file: /oracle/CMD/sapcheck/ceblmvwb.sta

BR0280I BRCONNECT time stamp: 2009-09-12 18.08.18

BR0807I Name of database instance: CMD

BR0808I BRCONNECT action ID: ceblmvwb

BR0809I BRCONNECT function ID: sta

BR0810I BRCONNECT function: stats

BR0812I Database objects for processing: ORADICT_STATS

BR1314I Oracle dictionary statistics will be collected with default

options

BR0126I Unattended mode active - no operator confirmation required

BR0280I BRCONNECT time stamp: 2009-09-12 18.08.18

BR1311I Starting collection of Oracle dictionary statistics...

BR0285I This function can take several seconds/minutes - be patient...

BR0280I BRCONNECT time stamp: 2009-09-12 18.08.28

BR0301E SQL error -20003 at location stats_oradict_collect-4, SQL

statement:

'BEGIN DBMS_STATS.GATHER_DICTIONARY_STATS (ESTIMATE_PERCENT => NULL,

METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', GRANULARITY => 'ALL',

CASCADE => TRUE, OPTIONS => 'GATHER', NO_INVALIDATE => FALSE); END;'

ORA-20003: Specified bug number (5099019) does not exist

ORA-06512: at "SYS.DBMS_STATS", line 14611

ORA-06512: at "SYS.DBMS_STATS", line 14957

ORA-06512: at "SYS.DBMS_STATS", line 17261

ORA-06512: at "SYS.DBMS_STATS", line 17303

ORA-06512: at line 1

BR1313E Collection of Oracle dictionary statistics failed

BR0806I End of BRCONNECT processing: ceblmvwb.sta 2009-09-12 18.08.28

BR0280I BRCONNECT time stamp: 2009-09-12 18.08.28

BR0804I BRCONNECT terminated with errors

********************

I couldn´t run the statistics from my system.

Hernando

Former Member
0 Kudos

You never did mention if enabled fixcontrol

SQL> select distinct bugno from v$session_fix_control;

This will list all the bugs that can be turned off or on

A value of 0 for the field value indicates the fix is off.

Pravin

Former Member
0 Kudos

hello Pravin

the select did you mentioned not show the bugs 5099019 or 8599814 (according to note 1165319 - Optimizer merge fix for Oracle 10.2.0.4) if I run opatch lsinventory is show that the patch is applied

Patch 8599814 : applied on Sat Sep 12 17:37:32 COT 2009

Unique Patch ID: 11582665

Created on 23 Jul 2009, 08:14:59 hrs US/Pacific

Bugs fixed:

8201796, 8284438, 7236148, 7272039, 5648287, 7430745, 7592450, 7592168

8526100, 8355120, 7366749, 5709135, 5099019, 6430500, 7692248, 7257160

8599814, 6319761, 7325597, 6642751, 6670551, 7441785, 6471770, 5251842

7149751, 7370410, 7657126, 7454131, 6329318, 7664560, 6808012, 7509689

6741425, 8467800, 6972291, 7280289, 7463138, 5714944, 7278117, 7300599

7891471, 6988848, 7228971, 7654407, 7690331, 7695742, 7430474, 7138405

8284633, 6120483, 7522608, 7429070, 6440977, 7585472, 7439957, 6845871

6833602, 8357689, 8299095, 7273832, 6221403, 8318020, 8366255, 6399597

6626018, 7331569, 8247017, 7295298, 7229351, 6526370, 7188932, 5394888

4598439, 6399168, 7155655, 6151380, 7716219, 7013768, 6934892, 6917874

7116357, 7007924, 7211965, 8348514