ORA-01110 ORA-01157 cannot identify/lock data file 227 - see DBWR trace file

Problem:

Tue Dec 04 18:28:58 2018
Errors in file /d01/app/oracle/diag/rdbms/bacdbst/bacdb/trace/bacdb_pr00_18214.trc:
ORA-01111: name for data file 227 is unknown - rename to correct file
ORA-01110: data file 227: '/d01/app/oracle/product/12.1.0/db_1/dbs/UNNAMED00227'
ORA-01157: cannot identify/lock data file 227 - see DBWR trace file
ORA-01111: name for data file 227 is unknown - rename to correct file
ORA-01110: data file 227: '/d01/app/oracle/product/12.1.0/db_1/dbs/UNNAMED00227'
Managed Standby Recovery not using Real Time Apply
Tue Dec 04 18:28:58 2018
Recovery Slave PR00 previously exited with exception 1111
Tue Dec 04 18:28:58 2018
Errors in file /d01/app/oracle/diag/rdbms/bacdbst/bacdb/trace/bacdb_mrp0_18206.trc:
ORA-01111: name for data file 227 is unknown - rename to correct file
ORA-01110: data file 227: '/d01/app/oracle/product/12.1.0/db_1/dbs/UNNAMED00227'
ORA-01157: cannot identify/lock data file 227 - see DBWR trace file
ORA-01111: name for data file 227 is unknown - rename to correct file
ORA-01110: data file 227: '/d01/app/oracle/product/12.1.0/db_1/dbs/UNNAMED00227'
Tue Dec 04 18:28:58 2018
MRP0: Background Media Recovery process shutdown (bacdb)
Completed: alter database recover managed standby database disconnect
RFS[15]: Selected log 6 for thread 1 sequence 85731 dbid 3693154305 branch 923676036
Tue Dec 04 18:29:09 2018
Archived Log entry 72318 added for thread 1 sequence 85730 ID 0xffffffffdc211e01 dest 1:

Solution

in Primary:

backup as copy datafile 227 format='/export/home/oracle/%U.bak' tag='lost file backup';

copy the backup file to Primary server

scp data_D-BACDB_I-3693154305_TS-ORBDT2_FNO-227_0itjus5k.bak oracle@10.11.1.133:/export/home/oracle/

in Standby:

alter database recover managed standby database cancel;


RMAN> catalog datafilecopy '/export/home/oracle/data_D-BACDB_I-3693154305_TS-ORBDT2_FNO-227_0mtjuud2.bak';

RMAN> report schema;

227  0        STLBAS:ORBDT2        ***     /d01/app/oracle/product/12.1.0/db_1/dbs/UNNAMED00227



RMAN> switch datafile 227 to copy;

datafile 227 switched to datafile copy "/export/home/oracle/data_D-BACDB_I-3693154305_TS-ORBDT2_FNO-227_0itjus5k.bak"

RMAN>

RMAN> report schema;

227  5120     STLBAS:ORBDT2        ***     /export/home/oracle/data_D-BACDB_I-3693154305_TS-ORBDT2_FNO-227_0itjus5k.bak


RMAN> copy datafile 227 to '+DATA/BACDB/STLBAS/orbdt206.dbf​';

RMAN> copy datafile 227 to '+DATA/BACDB/STLBAS/orbdt206.dbf';

Starting backup at 04-DEC-18
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting datafile copy
input datafile file number=00227 name=/export/home/oracle/data_D-BACDB_I-3693154305_TS-ORBDT2_FNO-227_0mtjuud2.bak
output file name=+DATA/BACDB/STLBAS/orbdt206.dbf tag=TAG20181204T193437 RECID=130 STAMP=994016080
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 04-DEC-18

RMAN>
RMAN> report schema;

227  500      STLBAS:ORBDT2        ***     /export/home/oracle/data_D-BACDB_I-3693154305_TS-ORBDT2_FNO-227_0mtjuud2.bak

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    10240    TEMP                 32767       +DATA/bacdb/temp01.dbf
2    244      PDB$SEED:TEMP        32767       +DATA/bacdb/fdb039e555681306e04400163e5bea5f/datafile/pdbseed_temp012016-09-27_04-41-20-pm.dbf
3    20       PDBTEST:TEMP         32767       +DATA/pdbtest_temp012016-09-27_04-41-20-pm.dbf

RMAN> switch datafile 227 to copy;

datafile 227 switched to datafile copy "+DATA/BACDB/STLBAS/orbdt206.dbf"

RMAN> report schema;


227  500      STLBAS:ORBDT2        ***     +DATA/BACDB/STLBAS/orbdt206.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    10240    TEMP                 32767       +DATA/bacdb/temp01.dbf
2    244      PDB$SEED:TEMP        32767       +DATA/bacdb/fdb039e555681306e04400163e5bea5f/datafile/pdbseed_temp012016-09-27_04-41-20-pm.dbf
3    20       PDBTEST:TEMP         32767       +DATA/pdbtest_temp012016-09-27_04-41-20-pm.dbf
4    48       OFFSHR:TEMP          32767       +DATA/bacdb/offshr/temp012016-05-25_01-45-13-pm.dbf
5    8738     EMOB:TEMP            32767       +DATA/bacdb/emob/temp012016-05-25_01-45-13-pm.dbf
6    32767    STLBAS:TEMP          32767       +DATA/bacdb/stlbas/temp01.dbf
7    32767    STLBAS:TEMP          32767       +DATA/bacdb/stlbas/temp02.dbf
8    30720    STLBAS:TEMP          30720       +DATA/bacdb/stlbas/temp03.dbf

Now start redo apply on standby :-

ORA-00604: error occurred at recursive SQL level 1 ORA-08102: index key not found, obj# 348, file 3, block 508 (2)

Following Error comes from alert log

Errors in file /u01/app/oracle/diag/rdbms/backupbaedw/backupedw/trace/backupedw_smon_33054.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 348, file 3, block 508 (2)
Wed Nov 28 13:33:23 2018
Dumping diagnostic data in directory=[cdmp_20181128133323], requested by (instance=1, osid=33054 (SMON)), summary=[abnormal process termination].
Wed Nov 28 13:38:24 2018
Errors in file /u01/app/oracle/diag/rdbms/backupbaedw/backupedw/trace/backupedw_smon_33054.trc:
Wed Nov 28 13:38:24 2018
Errors in file /u01/app/oracle/diag/rdbms/backupbaedw/backupedw/trace/backupedw_smon_33054.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 348, file 3, block 508 (2)
Wed Nov 28 13:38:24 2018
Dumping diagnostic data in directory=[cdmp_20181128133824], requested by (instance=1, osid=33054 (SMON)), summary=[abnormal process termination].
/u01/app/oracle/diag/rdbms/backupbaedw/backupedw/trace/backupedw_smon_33054.trc


Solution

[oracle@BACKUP-BAEDW ~]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 28 13:41:11 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> analyze table SMON_SCN_TIME validate structure;

Table analyzed.

SQL> Select index_name,index_type,owner from dba_indexes where table_name='SMON_SCN_TIME' ;

INDEX_NAME INDEX_TYPE OWNER
--------------------------------------------------------------------------------
SMON_SCN_TIME_SCN_IDX NORMAL SYS
SMON_SCN_TIME_TIM_IDX NORMAL SYS


SQL> Set long 10000000
SQL> Select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_TIM_IDX','SYS') FROM DUAL ;

DBMS_METADATA.GET_DDL('INDEX','SMON_SCN_TIME_TIM_IDX','SYS')
--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "SYS"."SMON_SCN_TIME_TIM_IDX" ON "SYS"."SMON_SCN_TIME" ("T
IME_MP")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX"


SQL>

SQL> Select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_SCN_IDX','SYS') FROM DUAL ;

DBMS_METADATA.GET_DDL('INDEX','SMON_SCN_TIME_SCN_IDX','SYS')
--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "SYS"."SMON_SCN_TIME_SCN_IDX" ON "SYS"."SMON_SCN_TIME" ("S
CN")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX"


SQL>

Once you have the required DDL for the Index.Drop the index


SQL> Drop index sys.SMON_SCN_TIME_TIM_IDX ;

Index dropped.

SQL> Drop index sys.SMON_SCN_TIME_SCN_IDX ;

Index dropped.

Re-run Analyze Command


SQL> Analyze table SMON_SCN_TIME validate structure cascade online ;

Table analyzed.

Re-run Analyze Command

SQL> Analyze table SMON_SCN_TIME validate structure cascade online ;

Table analyzed.

SQL>

Now check the alert log, hope the errors are gone!!!!!!!!!!!!!!!

Reference:  How to Resolve ORA-08102 Reported on Table SMON_SCN_TIME (Doc ID 978502.1)

ORA-31693 ORA-02354: error in exporting/importing data ORA-01555: snapshot too old

Error:

ORA-31693: Table data object "MIS"."MASTER_MISDB" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 4 with name "_SYSSMU4_4250244621$" too small


[oracle@said]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 26 10:15:18 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show parameter undo

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled      boolean FALSE
undo_management      string AUTO
undo_retention      integer 7200
undo_tablespace      string UNDOTBS1
SQL>

SQL> select max(maxquerylen) from v$undostat;

MAX(MAXQUERYLEN)
----------------
  204662
 
Solution:
 
1. Modify the current UNDO_RETENTION for the database:
 

SQL> ALTER SYSTEM SET UNDO_RETENTION = 205000 scope=both sid='*';

SQL> show parameter undo

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled      boolean FALSE
undo_management      string AUTO
undo_retention      integer 205000
undo_tablespace      string UNDOTBS1

2. Perform the export again.