top of page
Search

ORA-01578 / ORA-01110: data file 1: SYSTEM tablespace got corrupted.

Lost or corrupted SYSTEM tablespace datafile compels every DBA to be on high alert.


SCENARIO - 1 : Follow when the below conditions are met

  • You have RMAN L0 backup with archive logs

  • Archive log is enabled.

Below are the errors reported in the database alert log


ORA-19563: datafile header validation failed for file /proddata/oradata/ANWPROD/datafile/o1_mf_system_kqkysndg_.dbf

ORA-01251: Unknown File Header Version read for file number 1

ORA-01578: ORACLE data block corrupted (file # 1, block # 114965)

ORA-01110: data file 1: '/proddata/oradata/ANWPROD/datafile/o1_mf_system_kqkysndg_.dbf'

2023-10-17T17:49:50.244086+05:30


ORA-01578 and ORA-01110 errors typically indicate datafile or data block corruption in the database. These errors are usually detected during the database's routine health checks or starting/stopping the database.



Use the below command to get the alert log location.

select value from v$diag_info where name='Diag Trace';

Check if the database backup is available and validate the backup if it can be restored.

RMAN> List backup of database summary;

RMAN> Restore datafile 1 validate.

RMAN> list backup of database summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
6       B  0  A DISK        17-OCT-23       1       1       NO         TAG20231017T173349
7       B  0  A DISK        17-OCT-23       1       1       NO         TAG20231017T173349
8       B  0  A DISK        17-OCT-23       1       1       NO         TAG20231017T173349

RMAN> restore datafile 1 validate;

Starting restore at 17-OCT-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=87 device type=DISK

channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /proddata/fast_recovery_area/ANWPROD/backupset/2023_10_17/o1_mf_nnnd0_TAG20231017T173349_llwy1npy_.bkp
channel ORA_DISK_1: piece handle=/proddata/fast_recovery_area/ANWPROD/backupset/2023_10_17/o1_mf_nnnd0_TAG20231017T173349_llwy1npy_.bkp tag=TAG20231017T173349
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
Finished restore at 17-OCT-23 

Before proceeding to restore and recover, DB should be in a mounted state.

"shutdown immediate" does not work as it does a database verification check and it would fail. So "shut abort"

the database and start in mount mode.

[oracle@analyzenow datafile]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 17 17:55:50 2023
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> shut immediate;
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/proddata/oradata/ANWPROD/datafile/o1_mf_system_kqkysndg_.dbf'
ORA-01210: data file header is media corrupt
SQL> select name, open_mode from v$database;
 
NAME      OPEN_MODE
--------- --------------------
ANWPROD   READ WRITE
 
 
SQL> shut abort;
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> startup mount;
ORACLE instance started.
 
Total System Global Area 1543500832 bytes
Fixed Size                  9135136 bytes
Variable Size            1090519040 bytes
Database Buffers          436207616 bytes
Redo Buffers                7639040 bytes
Database mounted.

Connect to RMAN and restore and recover

[oracle@analyzenow datafile]$ rman target /
 
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 17 17:57:28 2023
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: ANWPROD (DBID=1102687737, not open)
 
RMAN> restore datafile 1;
 
Starting restore at 17-OCT-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 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 /proddata/oradata/ANWPROD/datafile/o1_mf_system_kqkysndg_.dbf
channel ORA_DISK_1: reading from backup piece /proddata/fast_recovery_area/ANWPROD/backupset/2023_10_17/o1_mf_nnnd0_TAG20231017T173349_llwy1npy_.bkp
channel ORA_DISK_1: piece handle=/proddata/fast_recovery_area/ANWPROD/backupset/2023_10_17/o1_mf_nnnd0_TAG20231017T173349_llwy1npy_.bkp tag=TAG20231017T173349
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 17-OCT-23
 
RMAN> recover datafile 1;
 
Starting recover at 17-OCT-23
using channel ORA_DISK_1
 
starting media recovery
media recovery complete, elapsed time: 00:00:00
 
Finished recover at 17-OCT-23

Then open the database and validate the system [oracle@analyzenow datafile]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 17 17:58:32 2023 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> alter database open; Database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ANWP1 READ WRITE NO [oracle@analyzenow datafilsqlplus test/*****@anwp1 SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 17 18:03:12 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Tue Oct 17 2023 17:41:47 +05:30 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> CREATE TABLE Employess ( EmpID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ); Table created. SQL> drop table Employees; Table dropped.


SCENARIO - 2: Follow when the below conditions are met

  • Have full RMAN incremental backup with archive logs.

  • The archive log is disabled.


Unable to open the database as system datafile is corrupted as below

ORACLE instance started.

Total System Global Area 1543500832 bytes
Fixed Size                  9135136 bytes
Variable Size            1090519040 bytes
Database Buffers          436207616 bytes
Redo Buffers                7639040 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1:
'/proddata/oradata/ANWPROD/datafile/o1_mf_system_kqkysndg_.dbf'

Check what are the backup available:

RMAN> list backup of database summary;
List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
19      B  0  A DISK        27-OCT-23       1       1       NO         TAG20231027T173642
20      B  0  A DISK        27-OCT-23       1       1       NO         TAG20231027T173642
21      B  0  A DISK        27-OCT-23       1       1       NO         TAG20231027T173642

Please make sure you validate the database before restoring

RMAN> restore validate database from tag TAG20231027T173642;

Restore the database from the tag you select from the backups available

RMAN> restore  database from tag TAG20231027T173642;

Starting restore at 27-OCT-23
using channel ORA_DISK_1

datafile 4 not processed because file is offline
skipping datafile 5; already restored to file /proddata/oradata/ANWPROD/datafile/o1_mf_system_kqkz7rvt_.dbf
skipping datafile 6; already restored to file /proddata/oradata/ANWPROD/datafile/o1_mf_sysaux_kqkz7rw4_.dbf
skipping datafile 8; already restored to file /proddata/oradata/ANWPROD/datafile/o1_mf_undotbs1_kqkz7rwd_.dbf
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 00009 to /proddata/oradata/ANWPROD/EDD60D9849805E3DE0536438A8C0E19F/datafile/o1_mf_system_kqkzynvl_.dbf
channel ORA_DISK_1: restoring datafile 00010 to /proddata/oradata/ANWPROD/EDD60D9849805E3DE0536438A8C0E19F/datafile/o1_mf_sysaux_kqkzynx0_.dbf
channel ORA_DISK_1: restoring datafile 00012 to /proddata/oradata/ANWPROD/EDD60D9849805E3DE0536438A8C0E19F/datafile/o1_mf_users_kqkzzd10_.dbf
channel ORA_DISK_1: restoring datafile 00013 to /proddata/oradata/ANWPROD/EDD60D9849805E3DE0536438A8C0E19F/datafile/o1_mf_undotbs2_ll2r4235_.dbf
channel ORA_DISK_1: reading from backup piece /proddata/fast_recovery_area/ANWPROD/EDD60D9849805E3DE0536438A8C0E19F/backupset/2023_10_27/o1_mf_nnnd0_TAG20231027T173642_lmq9ylb5_.bkp
channel ORA_DISK_1: piece handle=/proddata/fast_recovery_area/ANWPROD/EDD60D9849805E3DE0536438A8C0E19F/backupset/2023_10_27/o1_mf_nnnd0_TAG20231027T173642_lmq9ylb5_.bkp tag=TAG20231027T173642
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
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 /proddata/oradata/ANWPROD/datafile/o1_mf_system_kqkysndg_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /proddata/oradata/ANWPROD/datafile/o1_mf_sysaux_kqkyv1lq_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /proddata/oradata/ANWPROD/datafile/o1_mf_users_kqkyvvtf_.dbf
channel ORA_DISK_1: restoring datafile 00014 to /proddata/oradata/ANWPROD/datafile/undotbs2.dbf
channel ORA_DISK_1: reading from backup piece /proddata/fast_recovery_area/ANWPROD/backupset/2023_10_27/o1_mf_nnnd0_TAG20231027T173642_lmq9z1jv_.bkp
channel ORA_DISK_1: piece handle=/proddata/fast_recovery_area/ANWPROD/backupset/2023_10_27/o1_mf_nnnd0_TAG20231027T173642_lmq9z1jv_.bkp tag=TAG20231027T173642
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 27-OCT-23

Once restore is finished, recover the database from the same tag


RMAN> recover database from tag TAG20231027T173642;

Starting recover at 27-OCT-23
using channel ORA_DISK_1
datafile 4 not processed because file is offline

starting media recovery

archived log for thread 1 with sequence 95 is already on disk as file /proddata/fast_recovery_area/ANWPROD/archivelog/2023_10_27/o1_mf_1_95_lmq9zcrv_.arc
archived log for thread 1 with sequence 96 is already on disk as file /proddata/fast_recovery_area/ANWPROD/onlinelog/o1_mf_3_kqkyx6w9_.log
archived log file name=/proddata/fast_recovery_area/ANWPROD/archivelog/2023_10_27/o1_mf_1_95_lmq9zcrv_.arc thread=1 sequence=95
archived log file name=/proddata/fast_recovery_area/ANWPROD/onlinelog/o1_mf_3_kqkyx6w9_.log thread=1 sequence=96
media recovery complete, elapsed time: 00:00:01
Finished recover at 27-OCT-23

RMAN> exit


Recovery Manager complete.

Then, you have to open the database with resetlogs



[oracle@analyzenow ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 27 18:12:42 2023
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> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 ANWP1                          MOUNTED

SQL> alter database open resetlogs;

Database altered.

SQL>

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ANWP1                          READ WRITE NO

         SQL> select * from test;

A
----------
1






26 views0 comments

Recent Posts

See All

Comments


Contact Me

Tel: 7989359581

Lakshminarayana0071@gmail.com

  • Facebook Social Icon
  • LinkedIn Social Icon
  • Twitter Social Icon

Thanks for submitting!

© 2023 by Phil Steer . Proudly created with Wix.com

bottom of page