Unable to shutdown immediate database – Oracle

oracle

whenever I want to shutdown the database, I get an error saying datafile header is media corrupt:

SQL> shutdown immediate
ORA-01122: database file 13 failed verification check
ORA-01110: data file 13: '/oradata/obvs/entry_data02.dbf'
ORA-01210: data file header is media corrupt

I do not mind losing those files, just need to be able to shut down the database.

I tried reseaching online, but they all say in order for me to fix them I need to shutdown the database.

Best Answer

Since you do not mind losing these files, you can just simply drop the whole tablespace after making the datafile(s) in it offline as below.

Steps to reproduce:

SQL> create tablespace test datafile '/oradata/RYMIN19/test.dbf' size 100M;

Tablespace created.

SQL> create table bp.t1 tablespace test as select * from dba_objects;

Table created.

SQL> !dd if=/dev/urandom of=/oradata/RYMIN19/test.dbf conv=notrunc bs=8192 count=2 oflag=sync
2+0 records in
2+0 records out
16384 bytes (16 kB, 16 KiB) copied, 0.00262083 s, 6.3 MB/s

SQL> shu immediate
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/oradata/RYMIN19/test.dbf'
ORA-01210: data file header is media corrupt

One way to drop:

SQL> shu abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2147481656 bytes
Fixed Size                  8898616 bytes
Variable Size             956301312 bytes
Database Buffers         1174405120 bytes
Redo Buffers                7876608 bytes
Database mounted.
SQL> alter database datafile '/oradata/RYMIN19/test.dbf' offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

If you have more files in this tablespace, execute the above offline drop for all before dropping the tablespace. You will need to do some manual cleanup in the filesystem because the database is unable to delete such a file, as shown in the alert log:

2020-12-30T23:33:33.458679+01:00
drop tablespace test including contents and datafiles
2020-12-30T23:33:34.322107+01:00
Setting Resource Manager plan SCHEDULER[0x4D5B]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
2020-12-30T23:33:36.724601+01:00
Errors in file /u01/app/oracle/diag/rdbms/rymin19/RYMIN19/trace/RYMIN19_ora_513540.trc:
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: '/oradata/RYMIN19/test.dbf'
ORA-01565: error in identifying file '/oradata/RYMIN19/test.dbf'
ORA-27048: skgfifi: file header information is invalid
Additional information: 2
2020-12-30T23:33:36.724680+01:00
Errors in file /u01/app/oracle/diag/rdbms/rymin19/RYMIN19/trace/RYMIN19_ora_513540.trc:
ORA-01259: unable to delete datafile /oradata/RYMIN19/test.dbf
Completed: drop tablespace test including contents and datafiles

Cleanup:

SQL> !ls -l /oradata/RYMIN19/test.dbf
-rw-r-----. 1 oracle oinstall 104865792 Dec 30 23:32 /oradata/RYMIN19/test.dbf

SQL> !rm /oradata/RYMIN19/test.dbf

Shutdown immediate works as expected:

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
Related Question