luni, 28 iulie 2014

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!



Un comentariu: