How to overcome "ORA-01610: recovery using the BACKUP CONTROLFILE option must be done"

SQL> startup mount

ORACLE instance started.

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

This problem can appear after a loss of the controlfile (and will assume you don't have any backup) or if you mess with point-in-time recovery.

Solution:

SQL> recover database using BACKUP CONTROLFILE;

-this command will lead to some interaction about what file (controlfile backup) the instance should use in order to recover.

I will assume that you can't provide any file an the problem persists.

Now take a backup of the current controlfile:\

SQL> alter database backup controlfile to '/home/oracle/back.ctl';

Create a trace of the current controlfile:

SQL> alter database backup controlfile to trace as '/home/oracle/trace.sql';

Bounce the instance to the nomount state:

SQL>shutdown immediate;

SQL>startup nomount;

Now copy the following command from the trace file:

CREATE CONTROLFILE REUSE DATABASE "PIDB" NORESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 1168

LOGFILE

  GROUP 1 '/opt/oracle/oradata/pidb/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/opt/oracle/oradata/pidb/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/opt/oracle/oradata/pidb/redo03.log'  SIZE 50M BLOCKSIZE 512

DATAFILE

  '/opt/oracle/oradata/pidb/system01.dbf',

  '/opt/oracle/oradata/pidb/sysaux01.dbf',

  '/opt/oracle/oradata/pidb/undotbs01.dbf',

  '/opt/oracle/oradata/pidb/users01.dbf',

  '/opt/oracle/oradata/pidb/example01.dbf',

  '/opt/oracle/oradata/smis.dbf',

  '/opt/oracle/oradata/smis2.dbf',

  '/opt/oracle/oradata/smis3.dbf',

  '/opt/oracle/oradata/pidb/STATSPACK.DBF',

  '/opt/oracle/oradata/pidb/FLOW_1046809878404304.dbf',

  '/opt/oracle/oradata/pidb/apex01.dbf',

  '/opt/oracle/oradata/pidb/APEX_2146717548170215.dbf'

CHARACTER SET WE8MSWIN1252

;

and issue it through sqlplus command line.

You can easily observe that the TEMP datafiles are not the, and after:

SQL>alter database open;

you cannot do everything you want, because of the emptiness of TEMP tablespace.

So, create a temporary datafile:

SQL>alter tablespace TEMP add tempfile '/opt/oracle/oradata/pidb/tempo2.dbf' size 1024m autoextend on;

and everything will work fine.

Be aware, if the database is after an incomplete recovery, you should replace the NORESETLOGS word with RESETLOGS, within the create controlfile command, and the further command will be

SQL> alter database open resetlogs;

All done! Enjoy!