Database update statistic failed during Export phase

[Former Member]

NW2004s system with Oracle 10G on windows 2003 server and clustered enviornment.

-

---

During System copy ,when it is checking uodated statistic ,it is giving the follwoing error.

-

---

BR0301E SQL error -20000 in thread 4 at location stats_tab_collect-20, SQL statement:

'BEGIN DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => '"SAPDAT"', TABNAME => '"/BIC/FZPCA_CLI"', ESTIMATE_PERCENT => 10, METHOD_OPT => 'FOR COLUMNS SIZE 225 "KEY_ZPCA_CLIP" FOR COLUMN

ORA-20000: index "SAPDAT"."/BIC/FZPCA_CLI~010" or partition of such index is in unusable state

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

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

ORA-06512: at line 1

BR0886E Checking/collecting statistics failed for table SAPDAT./BIC/FZPCA_CLI

[stefan_koehler · Accepted solution]

Hi Sarkar,

your index "/BIC/FZPCA_CLI~010" is marked as unusable (maybe it have been done manually or it got corrupted or you have done some load actions which made the index unusable).

You can verifiy this by running the following query:

sqlplus "/ as sysdba"
SQL> SELECT INDEX_NAME, TABLE_NAME, STATUS from dba_indexes where INDEX_NAME = '/BIC/FZPCA_CLI~010';

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1069.htm#i1578369

If the status is "UNUSABLE" you have to rebuild the index... this is the only way how you can fix it (or you can delete the index).

If it is a partitioned index, maybe you only have to rebuild the corresponding partitions.

Regards

Stefan

[Former Member]

When I am running optimizer statistic from db13 ,it is running fine and completed successfully.

[Former Member]

Hi Sarkar,

when we were performing Export we also got an Error at the same phase( UPdate Database Statistics) although the problem was not the same but it was related to BRConnect

we ran a Script sapdba_role.sql (need to give the SID of your system at SAP&&1 use SAPSID)

you will find this script in Kernel

check out if this script is usefull in your case!

regards

Abdul

[Former Member]

Now I have installed new NW2004s non-unicode system.Database and application are installed in one machine.

I want to take export form a clustered box but both app and database are running in one machine.

When checked in the machine it is showing saploc and sapmnt are stored in different machine.

My query is

Is it possible to take export form clustered box and import it in a single box ?

Another query is when I am starting export it is looking profile in C:\ instead of E:\ . I have specified E:\ at time of giving profile parameter.

[Former Member]

Sorry ,the system is non unicode and I was trying with unicode installation master cd.Now can you kindly tell me that form non unicode system ,if I take export and try to import it in unicode system it is possible or not ?If not then what I have to do first?

[stefan_koehler]

Hello Sarkar,

>> Now can you kindly tell me that form non unicode system ,if I take export and try to import it in unicode system it is possible or not

If you want to convert the data from non-unicode to unicode you have to do much more things as import and export. (SPUMG, SUMG, etc..)

You can not export and import easily some data from a non-unicode to a unicode system.

You have to follow the unicode conversion guide for your system.

But wait ... as you post first your system is a NW2004s? There is no non unicode support.. really strange....

Normally the sapinst routine guides you through the convertion (if you don't use distribution monitor or some other tools)

Regards

Stefan

[Former Member]

Yes I chaecked ops$ user are there.Can you tell me that without stopping R3 ,export is possible or not.IF not then after stopping R3 I will post the message again.

[stefan_koehler]

Hello Sarkar,

>> Can you tell me that without stopping R3 ,export is possible or not

Possible yes... but you will get no consistent copy.

I would also prefer to see the following things:

sqlplus "/ as sysdba"
SQL> select username from dba_users;
SQL> show parameter OS_AUTHENT_PREFIX
SQL> show parameter REMOTE_OS_AUTHENT

Please post the content of the sqlnet.ora file and please tell us the user which started the export.

Btw. is the export started on the db server or on a different one?

Regards

Stefan

[Former Member]

Now I have started EXPORT Preparation phase.System is up and running .Getting the following error.

-

---

ERROR 2007-12-05 09:04:53

CJS-30023 Process call '
sapbwqgui4\sapmnt\BWQ\SYS\exe\uc\NTAMD64\R3ldctl.exe -p F:\Export\ABAP\DATA -l R3ldctlExport.log' exits with error code 2. For details see log file(s) R3ldctlExport.log.

ERROR 2007-12-05 09:04:53

FCO-00011 The step runR3ldctl with step key |NW_Prepare_Export|ind|ind|ind|ind|0|0|NW_ABAP_Prepare_Export_Dialog|ind|ind|ind|ind|4|0|NW_ABAP_Prepare_Export|ind|ind|ind|ind|0|0|runR3ldctl was executed with status ERROR .

-

---

R3ldctlExport.log'

-

---

DbSl Trace: OCI-call 'OCISessionBegin' failed with rc=1017

DbSl Trace: CONNECT failed with sql error '1017'

ERROR: DbSlConnect rc= 99

DbSl Trace: OCI-call 'OCISessionBegin' failed with rc=1017

DbSl Trace: CONNECT failed with sql error '1017'

[stefan_koehler]

Hi Sarkar,

the ORA-01017 means that your logindata is incorrect.

=> http://www.cryer.co.uk/brian/oracle/ORA01017.htm

As i can see you are running your database under windows. I have no experience under windows.. but with which user did you start the export?

I think the os_authentication failed, because you don't have any OP$ user in your database.

Can you please run the following query:

SQL> select username from dba_users;

and please tell us your windows user which started the export.

Regards

Stefan

[Former Member]

Thanks this time it work.

I am trying again to run update statistic once again .not closing this thread ,will post messages if I get any error in export phase ,hope I will get help form you .Please monitor this thread

[Former Member]

You have type Code before and at the end .I am not getting .Please explain ,how to run this .

This error I am getting .

-

-

---

C:Documents and Settings wqadm>brspace -f idrebuild -a rebuild -i "SAPDAT"."/B

IC/FZPCA_CLI~010"

BR1001I BRSPACE 7.00 (30)

BR0154E Unexpected option value 'ûf' found at position 1

BR0154E Unexpected option value 'idrebuild' found at position 2

BR0153E Unknown option '-a' found at position 3

BR0154E Unexpected option value 'rebuild' found at position 4

BR0153E Unknown option '-i' found at position 5

BR0154E Unexpected option value 'SAPDAT./BIC/FZPCA_CLI~010' found at position 6

BR0700E Fatal errors occurred - terminating processing...

BR1008I End of BRSPACE processing: sdwsxjzj.log 2007-12-04 14.15.59

BR0280I BRSPACE time stamp: 2007-12-04 14.15.59

BR1007I BRSPACE terminated with errors

[stefan_koehler]

Hi,

i think you have some "special" charactes from copying my command. But follow the brtools menu.

> brtools
-> 3 - Segment management
--> 2 - Rebuild indexes
---> Enter 7
---> Enter your index name
---> continue the rebuild

or try the following simplified command:

brspace –f idrebuild -i /BIC/FZPCA_CLI~010

Regards

Stefan

[Former Member]

After running the sql statement it is showing following result

-

---

INDEX_NAME TABLE_NAME STATUS

-

---

-

---

-

---

/BIC/FZPCA_CLI~010 /BIC/FZPCA_CLI UNUSABLE

CREATE [UNIQUE] INDEX <index_name> ON <table_name>

This statement I can use or please tell me how to do it form sapgui which command.

[stefan_koehler]

Hi Sarkar,

now we are sure that your index is unusable, thanks for the quote.

Now you have to rebuild it... i think you don't know if it is partitioned, or?

The easiest way is to rebuilt the index by brtools:

brspace –f idrebuild -a rebuild -i "SAPDAT"."/BIC/FZPCA_CLI~010" 

http://help.sap.com/saphelp_nw70/helpdata/en/64/f73f7515a46049a51df47ee9f6bba8/content.htm

The brspace command will find out the type of index and will rebuild it.

Regards

Stefan