HP Data Protector Backup Job failed with ORA-01157: cannot identify/lock data file 516 – see DBWR trace file

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. 🙂

Leave a Reply

%d bloggers like this: