_ALLOW_RESETLOGS_CORRUPTION Parameter Usage in Case of Corruption with ORA-03113: end-of-file on communication channel

Hi,

I will explain _ALLOW_RESETLOGS_CORRUPTION Parameter Usage in Case of Corruption in this post.

 

You can read the following post to learn more details about Oracle database corruptions.

 

ORA-01578: ORACLE data block corrupted (file # string, block # string) and How to Solve Block Corruption Error

 

 

When I startup database , I got the following error.

SQL> startup
ORA-03113: end-of-file on communication channel

 

Instance can be started in mount mode, but database cannot open.

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 1.6034E+10 bytes
Fixed Size 2269072 bytes
Variable Size 2449473648 bytes
Database Buffers 1.3556E+10 bytes
Redo Buffers 26480640 bytes
Database mounted.

 

 

When I run the recover database command, I got the following error.

SQL> ALTER DATABASE RECOVER DATABASE UNTIL CANCEL;
ALTER DATABASE RECOVER DATABASE UNTIL CANCEL
*
ERROR at line 1:
ORA-00279: change 8411468 generated at 04/25/2020 22:00:18 needed for thread 2
ORA-00289: suggestion : +RECO
ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'MSDB'
ORA-00280: change 8411468 for thread 2 is in sequence #207

 

I have started to incomplete recovery, but I got the following errors.

 

SQL> ALTER DATABASE RECOVER CANCEL;
ALTER DATABASE RECOVER CANCEL
*
ERROR at line 1:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DATA/MSDB/datafile/system.256.871197447'

 

 

In this cases, Oracle offers a hidden parameter _ALLOW_RESETLOGS_CORRUPTION which will allow us to open database.

 

Firstly set undo_management parameter to manual as follows.

SQL> show parameter undo

NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

SQL> alter system set undo_management=manual scope=spfile;
System altered.

 

Then set _allow_resetlogs_corruption parameter to TRUE as follows.

SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;
System altered.

 

Shutdown instance.

SQL> shutdown immediate;

 

 

Startup instance in mount mode.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.6034E+10 bytes
Fixed Size 2269072 bytes
Variable Size 2449473648 bytes
Database Buffers 1.3556E+10 bytes
Redo Buffers 26480640 bytes
Database mounted.

 

Open database in resetlogs or noresetlogs mode.

SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.

 

 

Do you want to learn more details about RMAN, then read the following articles.

https://ittutorial.org/rman-backup-restore-and-recovery-tutorials-for-beginners-in-the-oracle-database/

 

 409 views last month,  11 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Check Also

Alter System Flush Buffer Cache in Oracle

Hi, I will explain Alter System Flush Buffer Cache in Oracle in this post.  3,937 views …

Leave a Reply