Pages

Monday, June 18, 2018

Restoring database after corruption without RMAN Backup. RECOVER DATABASE ALLOW 1 CORRUPTION;

===============================
General
===============================
After electrical issues on site,  the server was abruptly rebooted, and when it came up, Oracle could not be started due to a corruption in one of datafiles.
There was no backups either, as the storage of the backup was now offline, due to a corruption.

===============================
Evidences
===============================
Errors in alert.log file /software/oracle/diag/rdbms/igt/igt/trace/igt_ora_14392.trc:
ORA-01172: recovery of thread 1 stuck at block 110573 of file 5
ORA-01151: use media recovery to recover block, restore backup if needed
ORA-1172 signalled during: ALTER DATABASE OPEN...

===============================
Bad attempt to bring Oracle up
===============================
sqlplus / as sysdba

SQL> SHUTDOWN IMMEDIATE;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> STARTUP NOMOUNT;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2166536 bytes
Variable Size             700449016 bytes
Database Buffers          360710144 bytes
Redo Buffers                5611520 bytes

SQL> ALTER DATABASE MOUNT;
Database altered.

RECOVER DATABASE;
SQL> RECOVER DATABASE;
ORA-00283: recovery session canceled due to errors
ORA-10877: error signaled in parallel recovery slave PR02
ORA-00600: internal error code, arguments: [3020], [5], [110573], [21082093],
[], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 5, block# 110573)
ORA-10564: tablespace IGT_INDEX
ORA-01110: data file 5: '/oracle_db/db1/

===============================
Solution
===============================
>SHUTDOWN IMMEDIATE;
ORA-01109: database not open

>STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2166536 bytes
Variable Size             700449016 bytes
Database Buffers          360710144 bytes
Redo Buffers                5611520 bytes

>RECOVER DATABASE ALLOW 1 CORRUPTION;
Database open

>SHUTDOWN IMMEDIATE;

>STARTUP;

SELECT tablespace_name, segment_type, owner, segment_name
FROM DBA_SEGMENTS
WHERE file_id = 5
  AND block BETWEEN  110572 and 110574;

 Then drop and recreate the bad index.

1 comment: