ORA-01116: error in opening database file 1

Leave a reply

Restore the lost datafile or system file in Oracle database

We lost the system datafile as shown below. We delete the system file for show the recovery as shown below:

SQL> select name from v$datafile;

NAME

------------------------------------------------------------------------------------------------------

/u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_ljmhx1p8_.dbf

/u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_ljmhxv11_.dbf

/u01/app/oracle/oradata/CDB1/datafile/o1_mf_undotbs1_ljmhyb88_.dbf

/u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_ljmjo2wq_.dbf

/u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_ljmjo2x5_.dbf

/u01/app/oracle/oradata/CDB1/datafile/o1_mf_users_ljmhycgd_.dbf

/u01/app/oracle/oradata/CDB1/datafile/o1_mf_undotbs1_ljmjo2xn_.dbf

/u01/app/oracle/oradata/CDB1/05B95F620FF328CEE065E6AD45DD4114/datafile/o1_mf_system_ljmkgx7c_.dbf

/u01/app/oracle/oradata/CDB1/05B95F620FF328CEE065E6AD45DD4114/datafile/o1_mf_sysaux_ljmkgx7k_.dbf

/u01/app/oracle/oradata/CDB1/05B95F620FF328CEE065E6AD45DD4114/datafile/o1_mf_undotbs1_ljmkgx7k_.dbf

/u01/app/oracle/oradata/CDB1/05B95F620FF328CEE065E6AD45DD4114/datafile/o1_mf_users_ljmkhk54_.dbf



11 rows selected.





[oracle@Linux1 datafile]$ dir

o1_mf_sysaux_ljmhxv11_.dbf  o1_mf_system_ljmjo2wq_.dbf	  o1_mf_undotbs1_ljmjo2xn_.dbf

o1_mf_sysaux_ljmjo2x5_.dbf  o1_mf_temp_ljmhzyxg_.tmp	  o1_mf_users_ljmhycgd_.dbf

o1_mf_system_ljmhx1p8_.dbf  o1_mf_undotbs1_ljmhyb88_.dbf  temp012023-09-19_21-18-07-158-PM.dbf

[oracle@Linux1 datafile]$ rm -rf o1_mf_system_ljmjo2wq_.dbf

[oracle@Linux1 datafile]$ rm -rf o1_mf_system_ljmhx1p8_.dbf

[oracle@Linux1 datafile]$ ls

o1_mf_sysaux_ljmhxv11_.dbf  o1_mf_temp_ljmhzyxg_.tmp      o1_mf_users_ljmhycgd_.dbf

o1_mf_sysaux_ljmjo2x5_.dbf  o1_mf_undotbs1_ljmhyb88_.dbf  temp012023-09-19_21-18-07-158-PM.dbf

o1_mf_undotbs1_ljmjo2xn_.dbf

Steps to recover the system datafile from RMAN backup:

  1. As we lost system file, Try to shutdown the database but it show the system tablespace is lost.


[oracle@Linux1datafile]$ sqlplus / as sysdba



SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 20 20:15:35 2024

Version 19.3.0.0.0



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





Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0



SQL> create table tb ( id1 number);

create table tb ( id1 number)

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01116: error in opening database file 1

ORA-01110: data file 1: 

'/u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_ljmhx1p8_.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3





SQL> shutdown immediate;

ORA-01116: error in opening database file 1

ORA-01110: data file 1: '/u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_ljmhx1p8_.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> shutdown

ORA-01116: error in opening database file 1

ORA-01110: data file 1: '/u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_ljmhx1p8_.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

2. As system file is lost, we need to bring database in mount stage. So we first shutdown abort then start the database in mount stage to start the restore and recovery process.

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.



Total System Global Area  717222648 bytes

Fixed Size		    9138936 bytes

Variable Size		  528482304 bytes

Database Buffers	  171966464 bytes

Redo Buffers		    7634944 bytes

Database mounted.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

3. Connect the RMAN for restore and recovery process.



[oracle@Linux1 datafile]$ rman target /



Recovery Manager: Release 19.0.0.0.0 - Production on Sat Jan 20 20:22:05 2024

Version 19.3.0.0.0



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



connected to target database: CDB1 (DBID=1121703817, not open)



RMAN> list backup of tablespace system;



using target database control file instead of recovery catalog



List of Backup Sets

===================





BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

2       Full    1.24G      DISK        00:00:12     20-JAN-24      

        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20240120T200936

        Piece Name: /u01/app/oracle/fast_recovery_area/CDB1/backupset/2024_01_20/o1_mf_nnndf_TAG20240120T200936_ltqps90f_.bkp

  List of Datafiles in backup set 2

  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- --------- ----------- ------ ----

  1       Full 3305950    20-JAN-24              NO    /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_ljmhx1p8_.dbf

  

  

  

  

  

  RMAN> restore datafile 1;



Starting restore at 20-JAN-24

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=24 device type=DISK



channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_ljmhx1p8_.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/backupset/2024_01_20/o1_mf_nnndf_TAG20240120T200936_ltqps90f_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2024_01_20/o1_mf_nnndf_TAG20240120T200936_ltqps90f_.bkp tag=TAG20240120T200936

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

Finished restore at 20-JAN-24



  

SQL> select file#,checkpoint_change#,status,recover from v$datafile_header;



     FILE# CHECKPOINT_CHANGE# STATUS  REC

---------- ------------------ ------- ---

	 1	      3305950 ONLINE  YES

	 3	      3306293 ONLINE  NO

	 4	      3306293 ONLINE  NO

	 5		    0 ONLINE

	 6	      2138071 ONLINE

	 7	      3306293 ONLINE  NO

	 8	      2138071 ONLINE

	 9	      3303544 ONLINE

	10	      3303544 ONLINE

	11	      3303544 ONLINE

	12	      3303544 ONLINE





RMAN> recover datafile 1;



Starting recover at 20-JAN-24

using channel ORA_DISK_1



starting media recovery

media recovery complete, elapsed time: 00:00:01



Finished recover at 20-JAN-24

4. Open the database in read and write mode

SQL> alter database open;



Database altered.

5. Verify the datafile present in location

[oracle@Linux1 datafile]$ ls

o1_mf_sysaux_ljmhxv11_.dbf  o1_mf_temp_ljmhzyxg_.tmp      o1_mf_users_ljmhycgd_.dbf

o1_mf_sysaux_ljmjo2x5_.dbf  o1_mf_undotbs1_ljmhyb88_.dbf  temp012023-09-19_21-18-07-158-PM.dbf

o1_mf_system_ltqql83d_.dbf  o1_mf_undotbs1_ljmjo2xn_.dbf

Leave a Reply