Question Re: DB jobs getting failed after system refresh in Technology Q&A

DB jobs getting failed after system refresh

Former Member — Wed, 08 Apr 2009 08:21:34 GMT

Dear DBA Experts,

I have did the system refresh from PRD to QAS. Everything is going smooth. SAP also get started. After that I found that DB jobs are getting failed. For that I have run the below commands from OS level. The problem was with temp data file. So I have run the below commands.

Our OS is AIX and DB is oracle 10g and ERP is ECC6.0

Please find the commands I have run

ora<sid>

cd /oracle/SID/sapdata2/temp_1

orasid > touch temp.data1

orasid > dd if=/dev/zero of=temp.data1 bs=8k count=262144

262144+0 records in.

262144+0 records out.

orasid > sqlplus "/as sysdba"

SQL > ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/SID/sapdata2/temp_1/temp.data1' SIZE 2048M REUSE AUTOEXTEND OFF;

tablespace altered

The above command, I have run. After that I am unable to run any of the DB jobs.

The below error is coming.

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

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

ORA-01187: cannot read from file 255 because it failed verification tests

ORA-01110: data file 255: '/oracle/BQS/sapdata2/temp_1/temp.data1'

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 SAPSR3.TBTCS

So I run check & verification the Job from BRTools

Even I am getting same error.

BR0801I BRCONNECT 7.00 (24)

BR0477I Oracle pfile /oracle/BQS/102_64/dbs/initBQS.ora created from spfile /ora

cle/BQS/102_64/dbs/spfileBQS.ora

BR0805I Start of BRCONNECT processing: ceahrwzo.chk 2009-04-08 13.07.44

BR0484I BRCONNECT log file: /oracle/BQS/sapcheck/ceahrwzo.chk

BR0280I BRCONNECT time stamp: 2009-04-08 13.07.44

BR0301E SQL error -1187 at location BrTspListGet-15, SQL statement:

'OPEN curs_36 CURSOR FOR'

'SELECT TABLESPACE_NAME, BYTES, 0 FROM DBA_FREE_SPACE UNION ALL SELECT TABLESPAC

E_NAME, BYTES_FREE + BYTES_USED, 0 FROM V$TEMP_SPACE_HEADER UNION ALL SELECT TAB

LESPACE_NAME, NVL(BYTES_USED, 0) * -1, 0 FROM GV$TEMP_EXTENT_POOL UNION AL SELEC

T TABLESPACE_NAME, BYTES, 1 FROM DBA_UNDO_EXTENTS WHERE STATUS IN ('EXPIRED', 'U

NEXPIRED') ORDER BY 1'

ORA-01187: cannot read from file 255 because it failed verification tests

ORA-01110: data file 255: '/oracle/BQS/sapdata2/temp_1/temp.data1'

BR0806I End of BRCONNECT processing: ceahrwzo.chk 2009-04-08 13.07.44

BR0280I BRCONNECT time stamp: 2009-04-08 13.07.44

BR0804I BRCONNECT terminated with errors

###############################################################################

BR0292I Execution of BRCONNECT finished with return code 3

Could anyone, please suggest how to overcome this issue ASAP.

Regards,

Haseem.

Re: DB jobs getting failed after system refresh

stefan_koehler — Wed, 08 Apr 2009 08:31:51 GMT

Hello Haseem,

> ORA-01187: cannot read from file 255 because it failed verification tests

> ORA-01110: data file 255: '/oracle/BQS/sapdata2/temp_1/temp.data1'

I think the temporary file does not exist correctly on OS level.

Please delete all temporary files in your database and add one the "normal" way (without dd). I know that you are doing this, because of the sparse files.. but just to verify..

Could you please post the alert.log?

Regards

Stefan

Re: DB jobs getting failed after system refresh

Former Member — Wed, 08 Apr 2009 08:36:37 GMT

Hi,

It seems temp datafiles are not available for the DB. Please check below links it may helps you.

http://www.dba-oracle.com/t_ora_01187_cannot_read_from_file_failed_verification_tests.htm

http://www.dbasupport.com/forums/archive/index.php/t-18195.html

Thanks

Sushil

Re: DB jobs getting failed after system refresh

Former Member — Sun, 12 Apr 2009 04:46:16 GMT

Do a

select * from dba_temp_files; .

If you are getting the same eror, seems the temp file is corrupted.

Simply drop the temp tablespace, create a new one

CREATE TEMPORARY TABLESPACE temp

TEMPFILE '/oradata/mytemp_01.tmp' SIZE <> M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

and then make it a database default

Alter 'database default temporary tablespace PSAPTEMP.

Then again check select * from dba_temp_files.

let us know the results.

Thanks

Rishi