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-23Then 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 TAG20231027T173642Please 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-23Once 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
Comments