ORA-01194: file 1 needs more recovery to be consistent

Error Log

SQL> alter database open resetlogs;
alter database open resetlogs 

*

ERROR at line 1: 

ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/u01/app/oracle/oradata/RTS_NEW/system_new.dbf'

SOLUTION

[oracle@localhost ~]$ sqlplus / as sysdba

SQL> shutdown abort;

SQL> startup nomount;

SQL> alter database mount;

SQL> alter database open;

SQL> recover database using backup controlfile until cancel; 

ORA-01507: database not mounted

SQL> shutdown abort;

SQL> startup mount;

SQL> recover database using backup controlfile until cancel; 

... Specify log: {<RET>=suggested | filename | AUTO | CANCEL} AUTO

Above command will apply all the available archive logs automatically. Now try to open database with resetlogs:

SQL> alter database open resetlogs;

If the error persists due to insufficient archive logs, do the following workaround:

SQL> shutdown immediate 

ORA-01109: database not open 

Database dismounted. 

ORACLE instance shut down.

Startup database in mount mode:

SQL> startup mount 

ORACLE instance started. 

Total System Global Area 530288640 bytes 

Fixed Size 2131120 bytes 

Variable Size 310381392 bytes 

Database Buffers 209715200 bytes 

Redo Buffers 8060928 bytes 

Database mounted.

Change “_allow_resetlogs_corruption” parameter to TRUE and undo_management parameter to MANUAL:

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

SQL> ALTER SYSTEM SET undo_management=MANUAL SCOPE = SPFILE;

After doing above changes, shutdown database, and startup:

SQL> shutdown immediate 

ORA-01109: database not open Database dismounted. 

ORACLE instance shut down.

SQL> startup mount 

ORACLE instance started. 

Total System Global Area 530288640 bytes 

Fixed Size 2131120 bytes 

Variable Size 310381392 bytes 

Database Buffers 209715200 bytes 

Redo Buffers 8060928 bytes 

Database mounted.

Now try resetlogs:

SQL> alter database open resetlogs; 

Database altered.

SQL> shutdown immediate 

ORA-01109: database not open 

Database dismounted. 

ORACLE instance shut down.

SQL> startup

When database started successfully and up then perform the following steps
Create new undo tablespace and set “undo_tablespace” parameter to the new undo tablespace and change “undo_management” parameter to AUTO:

Undo space once allocated will be available for reuse but will not be deallocated to the OS. The best way to shrink Undo tablespace is to switch to a new Undo tablespace and drop the old Undo tablespace. 

The steps are:

Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.

SQL> show parameter control;

SQL> CREATE UNDO TABLESPACE undo datafile '/u01/app/oracle/oradata/sh/undo_df1.dbf' size 200m autoextend on maxsize 30G;

Tablespace created.

SQL> alter system set undo_tablespace = undo scope=spfile; 

System altered.

SQL> alter system set undo_management=auto scope=spfile; 

System altered.

Now bounce your database.

SQL> shutdown immediate 

SQL> startup

Cheers!! Database started successfully.

Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline.

SQL> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

If there are Undo segments with a status other than OFFLINE in the tablespace to be dropped, we need to wait till they become OFFLINE. You may have to wait for the duration of the tuned_undoretention (from v$undostat) to ensure all Undo segments have become OFFLINE.

For example:

SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDO';

Bringing Rollback Segment ONLINE OR OFFLINE.

ALTER ROLLBACK SEGMENT UNDO3 ONLINE;

ALTER ROLLBACK SEGMENT UNDO3 OFFLINE;

If does change rollback segment to OFFLINE mode.

To change your database to NOARCHIVE mode, you can do the following:

ALTER SYSTEM SET LOG_ARCHIVE_START=FALSE SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE OPEN;

SQL> select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDO';

At this point, you can do a ALTER ROLLBACK SEGMENT <segment_name> OFFLINE for each rollback segment you want offline and then do a ALTER ROLLBACK SEGMENT <segment_name> ONLINE for each rollback segment you want online.

To switch back to ARCHIVELOG mode, perform the following:

ALTER SYSTEM SET LOG_ARCHIVE_START=TRUE SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Verify with ARCHIVE LOG LIST;

Archive any logs with ARCHIVE LOG ALL;

If all the Undo segments in the old Undo tablespace to the dropped is of status OFFLINE, then drop the tablespace.

SQL> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

Verify and then drop:

SQL> drop tablespace [tablespace_name] including contents and datafiles;

For example:

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

If face below errors by taking expdp command.

ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW00" prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-00600: internal error code, arguments: [17183], [0x7FAD49A1E198], [], [], [], [], [], [], [], [], [], []

To solve the problem, the value of the open_cursors parameter should be increased and the value of the parameter “_optimizer_cost_based_transformation” should be set to off.

SQL> alter system set open_cursors=1024 scope=both sid='*';

System altered.

SQL> alter system set "_optimizer_cost_based_transformation"=off scope=both sid='*';

System altered.

If all above are not possible then

First

export data with exp command

[oracle@localhost ~]$ exp

then create dummy schema and test to import data in it.

 

[oracle@localhost ~]$ imp t/t@sh file=/u01/dumps/exp_sh_25092021.dmp statistics=none