ORA-01589: must use RESETLOGS or NORESETLOGS option for database open 01589

Question

I have oracle_19C. I get this error unable to extend index string.string partition string by string in tablespace string for users tablespace, then I create DATAFILE by mistake as shown as:

ALTER TABLESPACE System ADD DATAFILE 'G:\oracleDB\ORCL\USERS15.DBF' SIZE 200M;
ALTER TABLESPACE System ADD DATAFILE 'G:\oracleDB\ORCL\USERS16.DBF' SIZE 200M;
ALTER TABLESPACE System ADD DATAFILE 'G:\oracleDB\ORCL\USERS17.DBF' SIZE 200M;
ALTER DATABASE DATAFILE 'G:\oracleDB\ORCL\USERS15.DBFF' autoextend on maxsize unlimited  ;

After the above commands, I realized that I created DATAFILE for System TABLESPACE and not for USERS TABLESPACE. So I deleted the created files(USERS15.DBF, USERS16.DBF, USERS17.DBF) with delete+shift button.

For now I cant connect to db with this error ORA-01033: ORACLE initialization or shutdown in progress. when I want recover like this tutorial I get below error:

SQL> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 19: 'G:\oracleDB\ORCL\USERS15.DBF'
ORA-01122: database file 19 failed verification check
ORA-01110: data file 19: 'G:\oracleDB\ORCL\USERS15.DBF'
ORA-01210: data file header is media corrupt

I use this tutorial and I recover datafile user15.dbf, 16,17.

shutdown immediate
startup nomount
alter database datafile ''G:\oracleDB\ORCL\USERS15.DBF' offline;
alter database datafile ''G:\oracleDB\ORCL\USERS16.DBF' offline;
alter database datafile ''G:\oracleDB\ORCL\USERS17.DBF' offline;

But I cant alter database open;, That give me below error:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open 01589. 00000 - "must use RESETLOGS or NORESETLOGS option for database open" *Cause: Either incomplete or backup control file recovery has been performed. After these types of recovery you must specify either the RESETLOGS option or the NORESETLOGS option to open your database. *Action: Specify the appropriate option.

How can I fix without loose data. How can I backup my table for restor to another server? Note:I haven't any backup and I have sqlPlus.

Answer 1

You may get lucky if you still have all the necessary redo in the online redo logs, but if the below does not work, your database most likely can not be recovered with ordinary tools.

startup mount
alter database create datafile 'G:\oracleDB\ORCL\USERS15.DBF' as 'G:\oracleDB\ORCL\USERS15.DBF';
alter database create datafile 'G:\oracleDB\ORCL\USERS16.DBF' as 'G:\oracleDB\ORCL\USERS16.DBF';
alter database create datafile 'G:\oracleDB\ORCL\USERS17.DBF' as 'G:\oracleDB\ORCL\USERS17.DBF';
recover datafile 'G:\oracleDB\ORCL\USERS15.DBF', 'G:\oracleDB\ORCL\USERS16.DBF', 'G:\oracleDB\ORCL\USERS17.DBF';
alter database open;

The above recreates the datafiles as empty using their initial size then recovers them using the online redo logs. It will not work if you don't have the necessary redo anymore (e.g. you had multiple log switches and they were overwritten).

Answer 2

After the above commands, I realized that I created DATAFILE for System TABLESPACE and not for USERS TABLESPACE. So I deleted the created files(USERS15.DBF, USERS16.DBF, USERS17.DBF) with delete+shift button.

What you do in Oracle you must generally fix in Oracle. Adding the datafiles changed the control files: Oracle is expecting those datafiles to be present. When you deleted them using the OS, you made it impossible for Oracle to open its SYSTEM tablespace, which contains the entire data dictionary.

Note that in general, Oracle datafiles are not meant to be deleted, ever. There are some specific situations in which you can delete them, but you must ALWAYS do so through the SQL command line and never first from the operating system, as you have now learned the hard way.

From the documentation, the following restrictions apply to deleting any datafile:

• The database must be open.
• If a data file is not empty, it cannot be dropped. If you must remove a data file that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the data file.
• You cannot drop the first or only data file in a tablespace. Therefore, DROP DATAFILE cannot be used with a bigfile tablespace.
• You cannot drop data files in a read-only tablespace that was migrated from dictionary managed to locally managed. Dropping a data file from all other read-only tablespaces is supported.
• You cannot drop data files in the SYSTEM tablespace.
• If a data file in a locally managed tablespace is offline, it cannot be dropped.

Also note that there is no way to drop datafiles from the SYSTEM tablespace, under any circumstances. The most you could do, once you had added the files, was to reduce their size as small as possible (say 1M) before the database started putting data or objects into them.

The tutorial you referenced is recovering from an existing RMAN backup of the database. If you hadn't already backed up the database correctly before adding the new datafiles then you have nothing to recover from. Oracle cannot otherwise recover files that you deleted from the OS. If you have a valid RMAN backup that predates the addition of the files, you can use point-in-time recovery to restore things to their state just before the files were added.

In order to complete a recovery, the following must be true:

• Your database must be running in ARCHIVELOG mode.
• You must have backups of all data files from before the target SCN for DBPITR and archived logs for the period between the SCN of the backups and the target SCN.
• If the backups were created using transparent encryption, and if a password-protected software keystore was used, then the keystore password must be provided before the restore operation is performed. Use the SET command with the DECRYPTION WALLET OPEN IDENTIFIED BY option to specify the password that must be used to open the password-based keystore. If a user with the SYSBACKUP privilege is performing the recovery, and a password-protected software keystore is used, grant the SYSKM privilege to this user.
• If the backups were created using password-mode encryption, then you must provide the password used to decrypt backups before you run the RESTORE and RECOVER commands. Use the SET DECRYPTION IDENTIFIED BY command to specify the password used to decrypt the backups.