ORACLE DATABASE PROBLEM AND SOLUTIONS: Recover database using Drop offline non-system datafile : ORA-01157: cannot identify/lock data file string

Recover database non-system datafile if datafile associated with tablespace do not contain important segments, that can be dropped offline -

ORA-01157: cannot identify/lock data file string - see DBWR trace file

Database Writer (DBWR) is unable to find and lock a Datafile. This may be due to various reasons like

Datafile is deleted or corrupt
Datafile is renamed or moved
Mount point is incorrect
Issues with Read/write permission on Datafile

To solve ORA-01157 we can use one of the following steps:

If datafile is deleted or corrupt and is not of TEMP or UNDO tablespace then we need to recovered it by using a valid backup.
If datafile is deleted or corrupt and is not of TEMP or UNDO tablespace but that tablespace do not contain important segments, that can be dropped offline
If datafile is renamed or moved then we need to get it in its original position
If Mount point is incorrect, simply recreate the mount point
if it is due to permission then we need to grant the permission at OS level

SQL> column TABLESPACE_NAME for a15
SQL> column FILE_NAME for a50
SQL> set lines 200
SQL> column TABLESPACE_NAME for a15
column FILE_NAME for a50
set lines 200
select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;SQL> SQL> SQL> 2

FILE_NUM FILE_NAME TYPE VALIDITY CHK TABLESPACE_NAME STATUS SEQUENCE
---------- -------------------------------------------------- ---------- ---------- ---------------- --------------- ---------- ----------
1 /u01/app/oracle/oradata/ducat/system01.d 3 0 1874371 SYSTEM 8196 30
2 /u01/app/oracle/oradata/ducat/sysaux01.d 3 0 1874371 SYSAUX 4 30
3 /u01/app/oracle/oradata/ducat/undotbs01. 3 0 1874371 UNDOTBS1 4 30
4 /u01/app/oracle/oradata/ducat/users01.db 3 0 1874371 USERS 4 30
5 /u01/app/oracle/oradata/ducat/example01. 3 0 1874371 EXAMPLE 4 30
6 /u01/app/oracle/oradata/ducat/test01.dbf 3 0 1874371 TEST 4 30
7 /u01/app/oracle/oradata/ducat/fda01.dbf 3 0 1874371 FDA 4 30
8 /u01/app/oracle/oradata/ducat/test02.dbf 3 0 1874371 TEST 4 30

8 rows selected.

SQL>
SQL>
SQL> select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE from x$kcvfh;

FILENUMBER STATUS SCN SEQUENCE
---------- ---------- ---------------- ----------
1 8196 1874371 30
2 4 1874371 30
3 4 1874371 30
4 4 1874371 30
5 4 1874371 30
6 4 1874371 30
7 4 1874371 30
8 4 1874371 30

8 rows selected.

SQL>
SQL>
SQL> !rm /u01/app/oracle/oradata/ducat/test02.dbf

SQL> !ls -lrt /u01/app/oracle/oradata/ducat/test02.dbf
ls: cannot access /u01/app/oracle/oradata/ducat/test02.dbf: No such file or directory

SQL>
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 28 52428800 512 1 YES INACTIVE 1853792 03-FEB-18 1853797 03-FEB-18
2 1 29 52428800 512 1 YES INACTIVE 1853797 03-FEB-18 1873800 03-FEB-18
3 1 30 52428800 512 1 NO CURRENT 1873800 03-FEB-18 2.8147E+14

SQL>
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4775
Session ID: 1 Serial number: 5

SQL>

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/u01/app/oracle/oradata/ducat/test02.dbf'

SQL>

In Alert log

alter database open
Sat Feb 03 23:48:41 2018
Errors in file /u01/app/oracle/diag/rdbms/ducat/ducat/trace/ducat_dbw0_4918.trc:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/u01/app/oracle/oradata/ducat/test02.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/ducat/ducat/trace/ducat_ora_4987.trc:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/u01/app/oracle/oradata/ducat/test02.dbf'
ORA-1157 signalled during: alter database open...

SQL>
SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
8 ONLINE ONLINE FILE NOT FOUND 0

SQL>

SQL> column TABLESPACE_NAME for a15
column FILE_NAME for a50
set lines 200
select HXFIL File_num,substr(HXFNM,1,40) File_name,FHTYP Type,HXERR Validity,
FHSCN CHK, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence from X$KCVFH;SQL> SQL> SQL> 2

FILE_NUM FILE_NAME TYPE VALIDITY CHK TABLESPACE_NAME STATUS SEQUENCE
---------- -------------------------------------------------- ---------- ---------- ---------------- --------------- ---------- ----------
1 /u01/app/oracle/oradata/ducat/system01.d 3 0 1874921 SYSTEM 8196 31
2 /u01/app/oracle/oradata/ducat/sysaux01.d 3 0 1874921 SYSAUX 4 31
3 /u01/app/oracle/oradata/ducat/undotbs01. 3 0 1874921 UNDOTBS1 4 31
4 /u01/app/oracle/oradata/ducat/users01.db 3 0 1874921 USERS 4 31
5 /u01/app/oracle/oradata/ducat/example01. 3 0 1874921 EXAMPLE 4 31
6 /u01/app/oracle/oradata/ducat/test01.dbf 3 0 1874921 TEST 4 31
7 /u01/app/oracle/oradata/ducat/fda01.dbf 3 0 1874921 FDA 4 31
8 0 4 0 0 0

8 rows selected.

SQL>

SQL> select name, recover, fuzzy, checkpoint_change# from v$datafile_header;

NAME REC FUZ CHECKPOINT_CHANGE#
-------------------------------------------------- --- --- ------------------
/u01/app/oracle/oradata/ducat/system01.dbf NO YES 1874921
/u01/app/oracle/oradata/ducat/sysaux01.dbf NO YES 1874921
/u01/app/oracle/oradata/ducat/undotbs01.dbf NO YES 1874921
/u01/app/oracle/oradata/ducat/users01.dbf NO YES 1874921
/u01/app/oracle/oradata/ducat/example01.dbf NO YES 1874921
/u01/app/oracle/oradata/ducat/test01.dbf NO YES 1874921
/u01/app/oracle/oradata/ducat/fda01.dbf NO YES 1874921
0

8 rows selected.

SQL>
SQL> select NAME,STATUS from v$datafile;

NAME STATUS
-------------------------------------------------- -------
/u01/app/oracle/oradata/ducat/system01.dbf SYSTEM
/u01/app/oracle/oradata/ducat/sysaux01.dbf ONLINE
/u01/app/oracle/oradata/ducat/undotbs01.dbf ONLINE
/u01/app/oracle/oradata/ducat/users01.dbf ONLINE
/u01/app/oracle/oradata/ducat/example01.dbf ONLINE
/u01/app/oracle/oradata/ducat/test01.dbf ONLINE
/u01/app/oracle/oradata/ducat/fda01.dbf ONLINE
/u01/app/oracle/oradata/ducat/test02.dbf ONLINE

8 rows selected.

SQL>

SQL> alter database datafile 8 offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name,open_mode,log_mode from v$database;

NAME OPEN_MODE LOG_MODE
-------------------------------------------------- -------------------- ------------
DUCAT READ WRITE NOARCHIVELOG

SQL>

*In this case we loss data related to Tablespace/datafile .

Using RMAN Recovery advisor:

[oracle@localhost ~]$
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Feb 3 23:30:52 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: DUCAT (DBID=3782973220, not open)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
5 HIGH OPEN 03-FEB-18 One or more non-system datafiles are missing

RMAN> list failure detail;

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
5 HIGH OPEN 03-FEB-18 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 5
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
2434 HIGH OPEN 03-FEB-18 Datafile 8: '/u01/app/oracle/oradata/ducat/test02.dbf' is missing
Impact: Some objects in tablespace TEST might be unavailable

RMAN>

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
5 HIGH OPEN 03-FEB-18 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 5
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
2434 HIGH OPEN 03-FEB-18 Datafile 8: '/u01/app/oracle/oradata/ducat/test02.dbf' is missing
Impact: Some objects in tablespace TEST might be unavailable

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
1. If file /u01/app/oracle/oradata/ducat/test02.dbf was unintentionally renamed or moved, restore it
2. If you have an export of tablespace TEST, offline its data files, open the database read/write, then drop and re-create the tablespace and import the data.
3. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
no automatic repair options available

RMAN>

ORACLE DATABASE PROBLEM AND SOLUTIONS

Sunday, February 4, 2018

Recover database using Drop offline non-system datafile : ORA-01157: cannot identify/lock data file string - see DBWR trace file

No comments:

Post a Comment