Thursday 25 September 2014

ORA-01116: error in opening database file 1 ORA-01110: data file 1: '/u01/app/oracle/TESTDB/oradata/dbf/system01.dbf'



How to recover database when system datafile is lost using RMAN backup?
or 
how to resolve ORA-01116: error in opening database file 1  ORA-01110: data file 1: '/u01/app/oracle/TESTDB/oradata/dbf/system01.dbf'?

Cause: ORA-01116: error in opening database file 1 means either system datafile is either missing or corrupted
Action: we need to perform recovery as shown below

DB Name: TESTDB
[oracle@avalance_testserv dbf]$   ls
redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[oracle@avalance_testserv dbf]$  rm system01.dbf
SQL> select * from sys.aud$;
select * from sys.aud$
                  *
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/TESTDB/oradata/dbf/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Note: Since the normal shutdown is not possible in this case we shall use shut abort
SQL> shut abort
ORACLE instance shut down
[oracle@avalance_testserv dbf]$ sqlplus / as sysdba
SQL> startup mount
[oracle@avalance_testserv dbf]$ rman target/
RMAN> restore datafile 1;
Starting restore at 25-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 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/TESTDB/oradata/dbf/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/backupset/ORA_rman_full_backup_ttdtest_0lpjddnk_1_1.bck
channel ORA_DISK_1: piece handle=/u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/backupset/ORA_rman_full_backup_ttdtest_0lpjddnk_1_1.bck tag=FULL_OPEN_BACKUP_DISK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 25-SEP-14
RMAN> recover datafile 1;
Starting recover at 25-SEP-14
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 22 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_22_859142042.arc
archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_23_859142042.arc
archived log for thread 1 with sequence 24 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_24_859142042.arc
archived log for thread 1 with sequence 25 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_25_859142042.arc
archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_26_859142042.arc
archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_27_859142042.arc
archived log for thread 1 with sequence 28 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_28_859142042.arc
archived log for thread 1 with sequence 29 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_29_859142042.arc
archived log for thread 1 with sequence 30 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_30_859142042.arc
archived log for thread 1 with sequence 31 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_31_859142042.arc
archived log for thread 1 with sequence 32 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_32_859142042.arc
archived log for thread 1 with sequence 33 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_33_859142042.arc
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_22_859142042.arc thread=1 sequence=22
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_23_859142042.arc thread=1 sequence=23
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_24_859142042.arc thread=1 sequence=24
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_25_859142042.arc thread=1 sequence=25
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_26_859142042.arc thread=1 sequence=26
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_27_859142042.arc thread=1 sequence=27
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_28_859142042.arc thread=1 sequence=28
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_29_859142042.arc thread=1 sequence=29
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_30_859142042.arc thread=1 sequence=30
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_31_859142042.arc thread=1 sequence=31
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-SEP-14
sql>alter database open;
It is suggested to take RMAN backup immediately after the activity is over. please follow the link to take RMAN backup: http://orcl11gdba.blogspot.in/2014/05/taking-database-backup-using-rman-method.html

No comments:

ORA-600 [kwqitnmphe:ltbagi], [1], [0] reported in the alert log file.

ORA-00600 [kwqitnmphe:ltbagi] Cause: This issue arises in 12.1.0.2. The error occurs because there are still Historical Messages without...