HP data protector daily database backup job failed after restoring database, with “ORA-01157: cannot identify/lock data file 516 – see DBWR trace file” OR “ORA-01110: data file 516: ‘/oracle//sapdata1/temp_4/temp.data4′” oracle errors.
Solution: Drop and recreate specified tempfile at desired location mentioned in error.
HP data protector backup job logs are as follows:
BR0051I BRBACKUP 7.20 (35) BR0055I Start of database backup: beomxhha.anf 2014-08-13 07.30.42 BR0484I BRBACKUP log file: /oracle/ECP/sapbackup/beomxhha.anf BR0477I Oracle pfile /oracle//11203/dbs/init.ora created from spfile /oracle//11203/dbs/spfile.ora BR0280I BRBACKUP time stamp: 2014-08-13 07.30.47 BR0301E SQL error -1157 at location BrDbfInfoGet-1, SQL statement: 'OPEN curs_8 CURSOR FOR' 'SELECT TS.TABLESPACE_NAME, TS.STATUS, TS.BLOCK_SIZE, TS.CONTENTS, TS.EXTENT_MANAGEMENT, TS.BIGFILE, DF.FILE_NAME, DF.FILE_ID, NVL(DF.STATUS, ' '), NVL(DF.BYTES, 0), NVL(DF.AUTOEXTENSIBLE, 'NO'), NVL(DF.MAXBYTES, 0), NVL(DF.INCREMENT_BY, 0) FROM DBA_TABLESPACES TS, DBA_DATA_FILES DF WHERE TS.TABLESPACE_NAME = DF.TABLESPACE_NAME UNION ALL SELECT TS.TABLESPACE_NAME, TS.STATUS, TS.BLOCK_SIZE, TS.CONTENTS, TS.EXTENT_MANAGEMENT, TS.BIGFILE, TF.FILE_NAME, TF.FILE_ID * -1, NVL(TF.STATUS, ' '), NVL(TF.BYTES, 0), NVL(TF.AUTOEXTENSIBLE, 'NO'), NVL(TF.MAXBYTES, 0), NVL(TF.INCREMENT_BY, 0) FROM DBA_TABLESPACES TS, DBA_TEMP_FILES TF WHERE TS.TABLESPACE_NAME = TF.TABLESPACE_NAME ORDER BY 1, 7' ORA-01157: cannot identify/lock data file 516 - see DBWR trace file ORA-01110: data file 516: '/oracle//sapdata1/temp_4/temp.data4' BR0314E Collection of information on database files failed BR0056I End of database backup: beomxhha.anf 2014-08-13 07.30.47 BR0280I BRBACKUP time stamp: 2014-08-13 07.30.47 BR0054I BRBACKUP terminated with errors
Work around for the mentioned error are as follows:
Note: I would request you to consider full backup of database before doing this activity, for future safety.
Verify ‘temp.data4’ tempfile available at specified location:
ll -l /oracle//sapdata1/temp_4/temp.data4
OR verify ‘temp_4’ directory exist or not. In case of ‘temp_4’ directory not exist recreate it with mkdir linux command from oracle user.
ll -l /oracle//sapdata1/temp_4/
Verify tempfile status with the help of following query:
select file#,status,name from v$tempfile; OR select file_name,status from dba_temp_files;
Drop mentioned tempfile and recreate it, with the help of following SQL command:
alter database tempfile '/oracle//sapdata1/temp_4/temp.data4' drop; OR alter database datafile 516 OFFLINE DROP;
alter tablespace temp add tempfile '/oracle//sapdata1/temp_4/temp.data4' size 10G autoextend on maxsize unlimited;
After dropping and recreating tempfile, HP data protector test backup run successful.
Stay Tune. 🙂