Oracle Container Pluggable Database ERROR at line 1: ORA-01157: cannot identify/lock data file 90 – see DBWR trace file ORA-01110: data file 90:

In this post, Pluggable database ORA-01157 error we will examine.
I encountered an error on when opening the database like below

The data file may have been renamed at the operating system level, moved to a different directory or disk drive with or without awareness.In this case, restore and recover the data file or move the data file to its original name.

SQL> alter pluggable database all open read write;

*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 90 – see DBWR trace file
ORA-01110: data file 90:

First Method

Alter session pdb

STARTUP MOUNT;

ALTER DATABASE DATAFILE ‘full_path_file_name’ OFFLINE DROP;

ALTER DATABASE OPEN;

DROP TABLESPACE tablespace_name INCLUDING CONTENTS;

CREATE TABLESPACE tablespace_name DATAFILE ‘datafile_full_path_name’ SIZE required_size;

Rebuild all pre existing objects in the table area.

Second Method
We will now restore lost datafile files from the database, which is in archivelog mode.

SQL> ALTER SYSTEM CHECK DATAFILES;
System altered.

SQL> shu immediate;
Database closed.
Database dismounted.

startup
ORACLE instance started.

show pdbs;

 2 PDB$SEED                     READ ONLY  NO
     3 TEST1                        MOUNTED
     4 TEST2                        MOUNTED
     5 TEST3                        MOUNTED
     6 TEST4                        MOUNTED
     7 TEST5                        MOUNTED
     8 TEST6                        MOUNTED
     9 TEST7                        MOUNTED
    10 TEST8                        MOUNTED
    11 TEST9                        MOUNTED
    12 TEST10                       MOUNTED

SQL> alter pluggable database all open read write;

alter pluggable database all open read write
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 90 – see DBWR trace file
ORA-01110: data file 90:

Test11 failed to open

 2 PDB$SEED                     READ ONLY  NO
     3 TEST1                        READ WRITE
     4 TEST2                        READ WRITE
     5 TEST3                        READ WRITE
     6 TEST4                        READ WRITE
     7 TEST5                        READ WRITE
     8 TEST6                        READ WRITE
     9 TEST7                        READ WRITE
    10 TEST8                        READ WRITE
    11 TEST9                        READ WRITE
    12 TEST10                       READ WRITE
    CON_ID CON_NAME                 OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
        13 TEST11                         MOUNTED
        14 TEST12                         READ WRITE NO
        15 TEST13                         READ WRITE NO
        16 TEST14                         READ WRITE NO

Switch to rman cli

RESTORE DATAFILE 90;

Starting restore at 08-APR-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1214 instance=geodb1 device type=DISK

creating datafile file number=90 name=/u01/app/oracle/product/prod1_12201_n/dbs/sde_tbs
restore not done; all files read only, offline, excluded, or already restored
Finished restore at 08-APR-20

RECOVER DATAFILE 90;

Starting recover at 08-APR-20
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 2660 is already on disk as file +RECOC1/GEODB/ARCHIVELOG/2020_04_08/thread_1_seq_2660.12881.1037201613
archived log for thread 1 with sequence 2661 is already on disk as file +RECOC1/GEODB/ARCHIVELOG/2020_04_08/thread_1_seq_2661.37487.1037201617
archived log for thread 1 with sequence 2662 is already on disk as file +RECOC1/GEODB/ARCHIVELOG/2020_04_08/thread_1_seq_2662.15688.1037209027
archived log for thread 1 with sequence 2663 is already on disk as file +RECOC1/GEODB/ARCHIVELOG/2020_04_08/thread_1_seq_2663.26211.1037213495
archived log for thread 1 with sequence 2664 is already on disk as file +RECOC1/GEODB/ARCHIVELOG/2020_04_08/thread_1_seq_2664.38657.1037215497
archived log for thread 2 with sequence 2760 is already on disk as file +RECOC1/GEODB/ARCHIVELOG/2020_04_08/thread_2_seq_2760.23400.1037201617
archived log for thread 2 with sequence 2761 is already on disk as file +RECOC1/GEODB/ARCHIVELOG/2020_04_08/thread_2_seq_2761.21958.1037206865
archived log file name=+RECOC1/GEODB/ARCHIVELOG/2020_04_08/thread_2_seq_2760.23400.1037201617 thread=2 sequence=2760
archived log file name=+RECOC1/GEODB/ARCHIVELOG/2020_04_08/thread_1_seq_2660.12881.1037201613 thread=1 sequence=2660
archived log file name=+RECOC1/GEODB/ARCHIVELOG/2020_04_08/thread_1_seq_2661.37487.1037201617 thread=1 sequence=2661
archived log file name=+RECOC1/GEODB/ARCHIVELOG/2020_04_08/thread_1_seq_2662.15688.1037209027 thread=1 sequence=2662
archived log file name=+RECOC1/GEODB/ARCHIVELOG/2020_04_08/thread_1_seq_2663.26211.1037213495 thread=1 sequence=2663
Finished recover at 08-APR-20

SQL> ALTER PLUGGABLE DATABASE TEST11 OPEN;

Pluggable database altered.

SQL> show pdbs;

 2 PDB$SEED                     READ ONLY  NO
     3 TEST1                        READ WRITE
     4 TEST2                        READ WRITE
     5 TEST3                        READ WRITE
     6 TEST4                        READ WRITE
     7 TEST5                        READ WRITE
     8 TEST6                        READ WRITE
     9 TEST7                        READ WRITE
    10 TEST8                        READ WRITE
    11 TEST9                        READ WRITE
    12 TEST10                       READ WRITE
    CON_ID CON_NAME                 OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
        13 TEST11                         READ WRITE
        14 TEST12                         READ WRITE NO
        15 TEST13                         READ WRITE NO
        16 TEST14                         READ WRITE NO

select * from dba_objects order by object_name;
select * from dba_objects order by object_name;
*
ERROR at line 1:

ORA-01157: cannot identify/lock data file 1026 – see DBWR trace file
ORA-01110: data file 1026: ‘/Oracle/oradata/temp2_01.tmp’
Solution:

alter database tempfile ‘/Oracle/oradata/temp2_01.tmp’ drop;

select tablespace_name, file_name from dba_temp_files;

alter tablespace temp2 add tempfile ‘/Oracle/oradata/temp2_01.tmp’ size 5m;

Congrat, Have a nice day.