Controlfile is lost but everything else is in place(spfile,database files)
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string ORCL
################################################################################
--Here i recreated the controlfile from other databases running in the same DB host to trace and modified content of the controlfile trace for the database file PATH and NAME.
--sample as below
[oracle@orkxr2decmdb01 dbs]$ cat /oracle/app/diag/rdbms/blaoid2/BLAOID2/trace/control_file.sql
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'+DATA/ORCL/ONLINELOG/group_1.576.823609463',
'+DATA/ORCL/ONLINELOG/group_1.729.823609365'
) SIZE 512M BLOCKSIZE 512,
GROUP 2 (
'+DATA/ORCL/ONLINELOG/group_2.551.823609557',
'+DATA/ORCL/ONLINELOG/group_2.634.823609655'
) SIZE 512M BLOCKSIZE 512
DATAFILE
'+DATA/ORCL/DATAFILE/SYSAUX.511.823608335',
'+DATA/ORCL/DATAFILE/SYSTEM.743.823607991',
'+DATA/ORCL/DATAFILE/UNDOTBS1.585.823607993',
'+DATA/ORCL/DATAFILE/UNDOTBS2.423.823608337',
'+DATA/ORCL/DATAFILE/USERS.481.823607241'
CHARACTER SET AL32UTF8;
/
################################################################################
SQL> @/oracle/app/diag/rdbms/trace/control_file.sql
Control file created.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL MOUNTED PRIMARY
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;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DATA/ORCL/datafile/system.743.823607991'
#####################################################################################
but in RMAN max archive sequence was 21only
RMAN> list archivelog all;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
22 1 130 A 04-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_16/thread_1_seq_130.790.823609333
23 1 1 A 16-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_16/thread_1_seq_1.690.823612289
20 1 2 A 16-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_20/thread_1_seq_2.562.823955047
8 1 3 A 20-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_3.314.824833347
9 1 4 A 20-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_4.313.824833347
10 1 5 A 24-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_5.310.824833351
11 1 6 A 29-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_6.320.824837921
12 1 7 A 30-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_7.626.824838523
13 1 8 A 30-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_8.596.824838529
14 1 9 A 30-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_9.762.824838537
15 1 10 A 30-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_10.683.824838543
16 1 11 A 30-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_11.628.824838547
17 1 12 A 30-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_12.689.824838555
18 1 13 A 30-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_13.522.824838687
19 1 14 A 30-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_14.434.824838837
5 1 15 A 30-AUG-13
Name: +DATA/ORCL/archivelog/2013_09_02/thread_1_seq_15.312.825037213
6 1 16 A 02-SEP-13
Name: +DATA/ORCL/archivelog/2013_09_02/thread_1_seq_16.316.825098333
7 1 17 A 02-SEP-13
Name: +DATA/ORCL/archivelog/2013_09_02/thread_1_seq_17.324.825098457
1 1 18 A 02-SEP-13
Name: +DATA/ORCL/archivelog/2013_09_03/thread_1_seq_18.322.825161951
2 1 19 A 03-SEP-13
Name: +DATA/ORCL/archivelog/2013_09_03/thread_1_seq_19.323.825162591
3 1 20 A 03-SEP-13
Name: +DATA/ORCL/archivelog/2013_09_03/thread_1_seq_20.778.825163231
4 1 21 A 03-SEP-13
Name: +DATA/ORCL/archivelog/2013_09_03/thread_1_seq_21.327.825163525
#####################################################################################
-- i tried to give the archivelog sequence 21 but it is looking for next sequence which doesnot exists so give the redolog file
SQL> recover database using backup controlfile;
ORA-00279: change 15642916 generated at 09/03/2013 12:05:24 needed for thread 1
ORA-00289: suggestion : +DATA
ORA-00280: change 15642916 for thread 1 is in sequence #22
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATA/ORCL/archivelog/2013_09_03/thread_1_seq_21.327.825163525
ORA-00310: archived log contains sequence 21; sequence 22 required
ORA-00334: archived log:
'+DATA/ORCL/archivelog/2013_09_03/thread_1_seq_21.327.825163525'
###########################################################################
ASMCMD> pwd
+DATA/ORCL/ONLINELOG
ASMCMD> ls -ltr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'
Type Redund Striped Time Sys Name
ONLINELOG UNPROT COARSE SEP 03 14:00:00 Y group_1.576.823609463
ONLINELOG UNPROT COARSE SEP 03 14:00:00 Y group_1.729.823609365
ONLINELOG UNPROT COARSE SEP 03 14:00:00 Y group_2.551.823609557
ONLINELOG UNPROT COARSE SEP 03 14:00:00 Y group_2.634.823609655
ONLINELOG UNPROT COARSE SEP 03 14:00:00 Y group_3.395.824839135
ONLINELOG UNPROT COARSE SEP 03 14:00:00 Y group_3.421.824839137
ONLINELOG UNPROT COARSE SEP 03 14:00:00 Y group_4.617.824839139
ONLINELOG UNPROT COARSE SEP 03 14:00:00 Y group_4.699.824839143
ONLINELOG UNPROT COARSE SEP 03 14:00:00 Y group_5.315.825095933
ONLINELOG UNPROT COARSE SEP 03 14:00:00 Y group_5.319.825095935
############################################################################
SQL> recover database using backup controlfile;
ORA-00279: change 15642916 generated at 09/03/2013 12:05:24 needed for thread 1
ORA-00289: suggestion : +DATA
ORA-00280: change 15642916 for thread 1 is in sequence #22
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATA/ORCL/ONLINELOG/group_1.576.823609463
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL READ WRITE PRIMARY
SQL> exit
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string ORCL
################################################################################
--Here i recreated the controlfile from other databases running in the same DB host to trace and modified content of the controlfile trace for the database file PATH and NAME.
--sample as below
[oracle@orkxr2decmdb01 dbs]$ cat /oracle/app/diag/rdbms/blaoid2/BLAOID2/trace/control_file.sql
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'+DATA/ORCL/ONLINELOG/group_1.576.823609463',
'+DATA/ORCL/ONLINELOG/group_1.729.823609365'
) SIZE 512M BLOCKSIZE 512,
GROUP 2 (
'+DATA/ORCL/ONLINELOG/group_2.551.823609557',
'+DATA/ORCL/ONLINELOG/group_2.634.823609655'
) SIZE 512M BLOCKSIZE 512
DATAFILE
'+DATA/ORCL/DATAFILE/SYSAUX.511.823608335',
'+DATA/ORCL/DATAFILE/SYSTEM.743.823607991',
'+DATA/ORCL/DATAFILE/UNDOTBS1.585.823607993',
'+DATA/ORCL/DATAFILE/UNDOTBS2.423.823608337',
'+DATA/ORCL/DATAFILE/USERS.481.823607241'
CHARACTER SET AL32UTF8;
/
################################################################################
SQL> @/oracle/app/diag/rdbms/trace/control_file.sql
Control file created.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL MOUNTED PRIMARY
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;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DATA/ORCL/datafile/system.743.823607991'
#####################################################################################
but in RMAN max archive sequence was 21only
RMAN> list archivelog all;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
22 1 130 A 04-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_16/thread_1_seq_130.790.823609333
23 1 1 A 16-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_16/thread_1_seq_1.690.823612289
20 1 2 A 16-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_20/thread_1_seq_2.562.823955047
8 1 3 A 20-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_3.314.824833347
9 1 4 A 20-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_4.313.824833347
10 1 5 A 24-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_5.310.824833351
11 1 6 A 29-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_6.320.824837921
12 1 7 A 30-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_7.626.824838523
13 1 8 A 30-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_8.596.824838529
14 1 9 A 30-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_9.762.824838537
15 1 10 A 30-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_10.683.824838543
16 1 11 A 30-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_11.628.824838547
17 1 12 A 30-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_12.689.824838555
18 1 13 A 30-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_13.522.824838687
19 1 14 A 30-AUG-13
Name: +DATA/ORCL/archivelog/2013_08_30/thread_1_seq_14.434.824838837
5 1 15 A 30-AUG-13
Name: +DATA/ORCL/archivelog/2013_09_02/thread_1_seq_15.312.825037213
6 1 16 A 02-SEP-13
Name: +DATA/ORCL/archivelog/2013_09_02/thread_1_seq_16.316.825098333
7 1 17 A 02-SEP-13
Name: +DATA/ORCL/archivelog/2013_09_02/thread_1_seq_17.324.825098457
1 1 18 A 02-SEP-13
Name: +DATA/ORCL/archivelog/2013_09_03/thread_1_seq_18.322.825161951
2 1 19 A 03-SEP-13
Name: +DATA/ORCL/archivelog/2013_09_03/thread_1_seq_19.323.825162591
3 1 20 A 03-SEP-13
Name: +DATA/ORCL/archivelog/2013_09_03/thread_1_seq_20.778.825163231
4 1 21 A 03-SEP-13
Name: +DATA/ORCL/archivelog/2013_09_03/thread_1_seq_21.327.825163525
#####################################################################################
-- i tried to give the archivelog sequence 21 but it is looking for next sequence which doesnot exists so give the redolog file
SQL> recover database using backup controlfile;
ORA-00279: change 15642916 generated at 09/03/2013 12:05:24 needed for thread 1
ORA-00289: suggestion : +DATA
ORA-00280: change 15642916 for thread 1 is in sequence #22
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATA/ORCL/archivelog/2013_09_03/thread_1_seq_21.327.825163525
ORA-00310: archived log contains sequence 21; sequence 22 required
ORA-00334: archived log:
'+DATA/ORCL/archivelog/2013_09_03/thread_1_seq_21.327.825163525'
###########################################################################
ASMCMD> pwd
+DATA/ORCL/ONLINELOG
ASMCMD> ls -ltr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'
Type Redund Striped Time Sys Name
ONLINELOG UNPROT COARSE SEP 03 14:00:00 Y group_1.576.823609463
ONLINELOG UNPROT COARSE SEP 03 14:00:00 Y group_1.729.823609365
ONLINELOG UNPROT COARSE SEP 03 14:00:00 Y group_2.551.823609557
ONLINELOG UNPROT COARSE SEP 03 14:00:00 Y group_2.634.823609655
ONLINELOG UNPROT COARSE SEP 03 14:00:00 Y group_3.395.824839135
ONLINELOG UNPROT COARSE SEP 03 14:00:00 Y group_3.421.824839137
ONLINELOG UNPROT COARSE SEP 03 14:00:00 Y group_4.617.824839139
ONLINELOG UNPROT COARSE SEP 03 14:00:00 Y group_4.699.824839143
ONLINELOG UNPROT COARSE SEP 03 14:00:00 Y group_5.315.825095933
ONLINELOG UNPROT COARSE SEP 03 14:00:00 Y group_5.319.825095935
############################################################################
SQL> recover database using backup controlfile;
ORA-00279: change 15642916 generated at 09/03/2013 12:05:24 needed for thread 1
ORA-00289: suggestion : +DATA
ORA-00280: change 15642916 for thread 1 is in sequence #22
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATA/ORCL/ONLINELOG/group_1.576.823609463
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL READ WRITE PRIMARY
SQL> exit
