Thursday, July 12, 2012

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

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done ORA-00283: recovery session canceled due to errors

when you are recovering  the database after restoring  then the details of database which resides in the control file is miss matched with the  current status
we need to backup the current control file to trace ..and from the trace file  we need  to recreate the control file of the database   


SQL> startup mount
ORACLE instance started.

Total System Global Area 1.9241E+10 bytes
Fixed Size                  2234616 bytes
Variable Size            3087009544 bytes
Database Buffers         1.6106E+10 bytes
Redo Buffers               45686784 bytes
Database mounted.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

And if you try to open the database it will not open the database because there is mismatch of the  current details of the database   with the details in the control file
normally this error Occurs when we clone the database to the new environment .. after restore of the database successfully .. the clone may start the recover the database according to the point in time recovery but due to the mismatch of the records  it will fail 
we can recover the database by creating the control file ..  

To recreate the control file .. there are two  methods to back up the current control file 

[1] create a trace of the control file [which will contain the command to recreate the control file to match the current database status]
[2]backup the current control file to the particular location .. [it will backup as is control file to the given location]

we need to trace the current control file and from the traced file verify the details like .location paths' of the data file and log file

ERROR
SQL> alter database open;
alter database open
*
ERROR at line 1:

ORA-19838: Cannot use this control file to open database

SQL> recover database using BACKUP CONTROLFILE;
ORA-00279: change 15574860879 generated at 07/11/2012 16:52:24 needed for
thread 1
ORA-00289: suggestion :
/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_29.3
61.788376517
ORA-00280: change 15574860879 for thread 1 is in sequence #29


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 15574861790 generated at 07/11/2012 17:28:36 needed for
thread 1
ORA-00289: suggestion :
/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_30.3
62.788376523
ORA-00280: change 15574861790 for thread 1 is in sequence #30
ORA-00278: log file
'/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_29.
361.788376517' no longer needed for this recovery

ORA-00279: change 15574861794 generated at 07/11/2012 17:28:43 needed for
thread 1
ORA-00289: suggestion :
/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/o1_mf_1_31_%u_.ar
c
ORA-00280: change 15574861794 for thread 1 is in sequence #31
ORA-00278: log file
'/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_30.
362.788376523' no longer needed for this recovery


ORA-00308: cannot open archived log
'/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/o1_mf_1_31_%u_.a
rc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Backup the control file 

[1] take the backup of the current control file to nay  of the directory

SQL> alter database backup controlfile to '/shared/oracle/controlnew01.ctl';

[2] create trace of the current control file 

SQL> alter database backup controlfile to trace as '/shared/oracle/controlfa.ctl';

Once the trace file is create it will contain  the script to recreate the control file
you can open the trace of the control file in the notepad and edit the creating the control file script according to your environment

CREATE CONTROLFILE REUSE DATABASE "FAHDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u06/app/oracle/oradata/redo01.log',
    '/u06/app/oracle/oradata/redo01a.log',
  ) SIZE 2048M BLOCKSIZE 512,
  GROUP 2 (
    '/u06/app/oracle/oradata/redo02.log',
    '/u06/app/oracle/oradata/redo02a.log'
  ) SIZE 2048M BLOCKSIZE 512,
  GROUP 3 (
    '/u06/app/oracle/oradata/redo03.log',
    '/u06/app/oracle/oradata/redo03a.log'
  ) SIZE 2048M BLOCKSIZE 512,
  GROUP 4 (
    '/u06/app/oracle/oradata/redo04.log',
    '/u06/app/oracle/oradata/redo04a.log'
  ) SIZE 2048M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u06/app/oracle/oradata/datafile/system.dbf',
  '/u06/app/oracle/oradata/datafile/sysaux.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_undots.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data01.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data02.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data03.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data04.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data05.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data06.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data07.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data08.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data09.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data10.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx01.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx02.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx03.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_tools.dbf',
  '/u06/app/oracle/oradata/datafile/search_index.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ias_orasdpm_aq.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_archive.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_dq.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_interface.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_nologging.dbf',
  '/u06/app/oracle/oradata/datafile/biacmts.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_seed.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_dyn_ts.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_queues.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_aq.dbf',
  '/u06/app/oracle/oradata/datafile/search_data.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_media.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_summary.dbf'
CHARACTER SET AL32UTF8
;
Once the command was finalized and changed according to the environment the we have to bring down the database  and  startup nomount
From the nomount stage we need to run the above script to create the new control file

[1] startup nomount   which will read the parameter file and allocate the memory size according to it [the instance will be started]

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.9241E+10 bytes
Fixed Size                  2234616 bytes
Variable Size            3087009544 bytes
Database Buffers         1.6106E+10 bytes
Redo Buffers               45686784 bytes

[2] Issue the Create control file script 

SQL> CREATE CONTROLFILE REUSE DATABASE "FAHDB" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 (
  9      '/u06/app/oracle/oradata/redo01.log',
 10      '/u06/app/oracle/oradata/redo01a.log'
 11    ) SIZE 2048M BLOCKSIZE 512,
 12    GROUP 2 (
 13      '/u06/app/oracle/oradata/redo02.log',
 14      '/u06/app/oracle/oradata/redo02a.log'
 15    ) SIZE 2048M BLOCKSIZE 512,
 16    GROUP 3 (
    '/u06/app/oracle/oradata/redo03.log',
 17   18      '/u06/app/oracle/oradata/redo03a.log'
 19    ) SIZE 2048M BLOCKSIZE 512,
 20    GROUP 4 (
 21      '/u06/app/oracle/oradata/redo04.log',
 22      '/u06/app/oracle/oradata/redo04a.log'
 23    ) SIZE 2048M BLOCKSIZE 512
 24  -- STANDBY LOGFILE
 25  DATAFILE
 26    '/u06/app/oracle/oradata/datafile/system.dbf',
  '/u06/app/oracle/oradata/datafile/sysaux.dbf',
 27   28    '/u06/app/oracle/oradata/datafile/fusion_undots.dbf',
 29    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data01.dbf',
 30    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data02.dbf',
 31    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data03.dbf',
 32    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data04.dbf',
 33    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data05.dbf',
 34    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data06.dbf',
 35    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data07.dbf',
 36    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data08.dbf',
 37    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data09.dbf',
 38    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data10.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx01.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx02.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx03.dbf',
 39   40   41   42    '/u06/app/oracle/oradata/datafile/fusion_ts_tools.dbf',
 43    '/u06/app/oracle/oradata/datafile/search_index.dbf',
 44    '/u06/app/oracle/oradata/datafile/fusion_ias_orasdpm_aq.dbf',
 45    '/u06/app/oracle/oradata/datafile/fusion_ts_archive.dbf',
 46    '/u06/app/oracle/oradata/datafile/fusion_ts_dq.dbf',
 47    '/u06/app/oracle/oradata/datafile/fusion_ts_interface.dbf',
 48    '/u06/app/oracle/oradata/datafile/fusion_ts_nologging.dbf',
 49    '/u06/app/oracle/oradata/datafile/biacmts.dbf',
 50    '/u06/app/oracle/oradata/datafile/fusion_ts_seed.dbf',
 51    '/u06/app/oracle/oradata/datafile/fusion_dyn_ts.dbf',
  '/u06/app/oracle/oradata/datafile/fusion_ts_queues.dbf',
 52   53    '/u06/app/oracle/oradata/datafile/fusion_ts_aq.dbf',
 54    '/u06/app/oracle/oradata/datafile/search_data.dbf',
 55    '/u06/app/oracle/oradata/datafile/fusion_ts_media.dbf',
 56    '/u06/app/oracle/oradata/datafile/fusion_ts_summary.dbf'
 57  CHARACTER SET AL32UTF8
;
 58

Control file created.


SQL> alter database open resetlogs;

Database altered.


NOTE:
[1]   when you create the control file the database will be automatically mounted  we need to open .. once the database is completely restored then only you have to proceed with the above step other wise the data file header will have the different DBid  so it will not accept the  data file and it will give the error the data file does  not belong to this database.. 

[2] Once you open with the reset logs the the new database   incarnation will be created .. you can check the list of incarnations by the command list  incarnation in RMAN


3 comments:

  1. dear, i do the same steps to clone my 10g on 11g database. now after creating the control file, as you said, when i try to open with resetlogs, it give the following error:
    ora-39700: database must be opened with UPGRADE option.

    how to solve this issue?

    ReplyDelete
  2. hi bro from 10g to 11g it is not the steps .. we need to do the out of box database upgrade ..

    [1] clone the 10 g database to the 10 g database in the test server
    [2] install the new 11g database in the test server in the different oracle home[
    [3] check the prerequisites and patches to be applied
    [4] run the upgrade script and upgrade to the 11g

    this are the practices u are doing cloning the database from 11g to 11g

    cloning is the option which is sued to replicate the same database from the testing or some other purposes... but upgrade is entirely different which u are going to upgraded the oracle binaries if u have nay queries please post

    ReplyDelete
  3. i was recreating the control file with the control file and when i give the "alter database open resetlog" i got the same error.
    so i followed the above steps by taking backup of control file to trace and editing the create controlfile command and done the alter database open resetlogs options and still the same error

    ReplyDelete