ORA-00210 ORA-00202 ORA-27041 , ORA-326 ORA-1547 , ORA-00279 ORA-00289 ORA-00280 , ORA-00326 ORA-00334

 Database Recovery with lost of all controlfile.(With no trace file backup)
USER MANAGED AND SEE ( RMAN FOLLOW THE AUTO PROCESS in BACKGROUND)

1.
------------------------------------------------------------------------------------------------------------------------------
rman target / catalog /


run
{
ALLOCATE CHANNEL t1 DEVICE TYPE disk MAXPIECESIZE=1G;
ALLOCATE CHANNEL t2 DEVICE TYPE disk MAXPIECESIZE=1G;
CONFIGURE CONTROLFILE AUTOBACKUP format for device type disk to '/u02/backup/control_%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u02/backup/snapcf_nctscard.f';
backup as compressed backupset filesperset 10 tag="datafiles" format '/u02/backup/DBF_%d_%T_%U.bak' (database include current controlfile channel t1 ) ;
backup as compressed backupset filesperset 100 tag="archivelog" format '/u02/backup/ARC_%d_%T_%U.BAK' (archivelog all not backed up channel t2) ;
sql 'alter system archive log current';
sql 'alter system archive log current';
backup current controlfile format '/u02/backup/CON_%d_%T_%U.ctl'  ;
backup as backupset Tag="Spfile" spfile format '/u02/backup/spfile_%d_%T_%U.spfile' SPFILE ;
sql "alter database backup controlfile to trace as ''/u02/backup/controlfiletrace.txt'' reuse";
}
-----------------------------------------------------------------------------------------------------------------------


2.
-----------------------------------------------------------------------------------------------------------------------
sqlplus / as sysdba
shutdown immediate
take a cold backup copy and paste
startup

select count(*) from scott.emp;
create table scott.abc as select * from scott.emp;
insert insto scott.abc select * from scott.emp;
/
alter system switch logfile;
create table scott.abc_1 as select * from scott.emp;
create table scott.abc_2 as select * from scott.emp;
-----------------------------------------------------------------------------------------------------------------------


3.
-----------------------------------------------------------------------------------------------------------------------
cd /oradata/orcl/

rm -rf *.ctl       # delete all controlfiles

sqlplus / as sysdba
select count(*) from scott.abc;
insert insto scott.abc select * from emp;
create table scott.abc_3 as select * from scott.emp;
insert insto scott.abc_3 select * from scott.emp;
alter system switch logfile;

-----------------------------------------------------------------------------------------------------------------------


4.
-----------------------------------------------------------------------------------------------------------------------
NOTE: We received error when we try to create user, tablespace or check any v$datafile, v$log views as followings (SOME Times not show this error):

SQL> create table abc as select * from emp;

create table abc as select * from emp
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file:
'/oracle/oracle/oracle/product/10.2.0/db_4/oradata/test/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

At this point we need to shut database If we try to shut immediate than we will get below error:

SQL> shut immediate

ORA-00210: cannot open the specified control file
ORA-00202: control file:
'/oracle/oracle/oracle/product/10.2.0/db_4/oradata/test/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3


SQL> shutdown abort

-----------------------------------------------------------------------------------------------------------------------

5.
-----------------------------------------------------------------------------------------------------------------------
NOTE: Copy all the three controlfile from previous & recently backup with OS commands and mount the database (COPY & PASTE).


SQL> recover database using backup controlfile until cancel;
auto

...
...
...

ERROR at line 1:
ORA-00279: change 501804 generated at 02/14/2014 07:29:28 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 501804 for thread 1 is in sequence #7


----------------------Alert.log File Error------------------------------
Errors with log /oradb/archive/1_7_838562540.dbf
ORA-326 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
Fri Feb 14 06:49:13 2014
ALTER DATABASE RECOVER CANCEL
Signalling error 1152 for datafile 1!
ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ...



SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open



SQL> alter database open resetlog;

showing error with system01.dbf tablespace error
-----------------------------------------------------------------------------------------------------------------------


6. IMPORTANT STEPS
-----------------------------------------------------------------------------------------------------------------------

select * from v$log;
alter database recover datafile list clear;

select file#,name from v$datafile;

alter database recover datafile list  1 , 2 , 3 , 4 , 5;

SQL> alter database recover until cancel using backup controlfile;
alter database recover until cancel using backup controlfile
*
ERROR at line 1:
ORA-00279: change 501804 generated at 02/14/2014 07:29:28 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 501804 for thread 1 is in sequence #7

...

select * from v$log;
select * from v$logfile;

SQL> alter database recover logfile '/oradb/app/oracle/oradata/orcl/redo02.log';
alter database recover logfile '/oradb/app/oracle/oradata/orcl/redo02.log'
*
ERROR at line 1:
ORA-00310: archived log contains sequence 5; sequence 7 required
ORA-00334: archived log: '/oradb/app/oracle/oradata/orcl/redo02.log'


SQL>  alter database recover logfile '/oradb/app/oracle/oradata/orcl/redo03.log';
 alter database recover logfile '/oradb/app/oracle/oradata/orcl/redo03.log'
*
ERROR at line 1:
ORA-00310: archived log contains sequence 6; sequence 7 required
ORA-00334: archived log: '/oradb/app/oracle/oradata/orcl/redo03.log'


SQL> alter database recover logfile '/oradb/app/oracle/oradata/orcl/redo01.log';

Database altered.

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 501876 generated at 02/14/2014 07:30:41 needed for thread 1
ORA-00289: suggestion : /oradb/archive/1_7_838562540.dbf
ORA-00280: change 501876 for thread 1 is in sequence #7


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00326: log begins at change 526199, need earlier change 501876
ORA-00334: archived log: '/oradb/archive/1_7_838562540.dbf'


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from scott.abc;

  COUNT(*)
    ----------
       54



SQL> select count(*) from scott.abc_1;

  COUNT(*)
    ----------
       14

SQL> select count(*) from scott.abc_2;

  COUNT(*)
    ----------
       14

SQL> select count(*) from scott.abc_3;

  COUNT(*)
    ----------
       28

-----------------------------------------------------------------------------------------------------------------------