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.
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:
Now copy the following command from the trace file:
CREATE CONTROLFILE REUSE DATABASE "PIDB" NORESETLOGS ARCHIVELOG
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
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!