on 04-08-2009 9:21 AM
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.
hi
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.