Oracle Database Discussions

Hi,

I am working on a test database (Oracle 10.2.0) . I found out that the disk on which the control files were present got corrupted.
I am trying to recreate the controlfile, to startup the database.
I am trying to run the following steps, but still get an error.

I cannot run any query on the database like -

alter database backup controlfile to trace;

as this gives me an error.

These are the steps -

1.
SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/usr/test1/ctl01/ctl_01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3


2. SQL> shutdown abort;
ORACLE instance shut down.


SQL> startup nomount;
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1261860 bytes
Variable Size 163581660 bytes
Database Buffers 369098752 bytes
Redo Buffers 2928640 bytes

3.

SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" RESETLOGS NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 3
MAXDATAFILES 200
MAXINSTANCES 6
MAXLOGHISTORY 584
LOGFILE
GROUP 1 (
*'/usr/test/rdo1/gp_01_redo_01.rdo',*
*'/usr/test/rdo2/gp_01_redo_02.rdo'*
*) SIZE 5M,*
GROUP 2 (
*'/usr/test/rdo2/gp_02_redo_02.rdo',*
*'/usr/test/rdo1/gp_02_redo_01.rdo'*
*) SIZE 5M,*
GROUP 3 (
*'/usr/test/rdo1/gp_03_redo_01.rdo',*
*'/usr/test/rdo2/gp_03_redo_02.rdo'*
*) SIZE 5M*
DATAFILE
*'/usr/test/system/sys_01.dbf',*
*'/usr/test/undo/undo_01.dbf',*
*'/usr/test/system/sysaux01.dbf',*
*'/usr/test/system/users.dbf',*
*'/usr/test/intr/intermedia_01.dbf',*
*'/usr/test/data/sm_tbl_01.dbf',*
*'/usr/test/data/md_tbl_01.dbf',*
*'/usr/test/indx/sm_idx_01.dbf',*
*'/usr/test/indx/md_idx_01.dbf',*
*'/usr/test/lob/lob_01.dbf',*
*'/usr/test/system/users_1.dbf'*
CHARACTER SET US7ASCII;

Control file created.


4.

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: '/usr/test/system/sys_01.dbf'


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 167197704 generated at 03/30/2011 11:04:42 needed for thread
1
ORA-00289: suggestion : /usr/ora10/dbs/arch1_9_747140614.dbf
ORA-00280: change 167197704 for thread 1 is in sequence #9


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/usr/ora10/dbs/arch1_9_747140614.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log '/usr/ora10/dbs/arch1_9_747140614.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/usr/test/system/sys_01.dbf'

6.

SQL> col member format a50
SQL> select a.group#,a.member,b.status from v$logfile a, v$log b where a.group#=b.group#;

GROUP# MEMBER STATUS
------------------------------------------------
1 /usr/test/rdo1/gp_01_redo_01.rdo UNUSED
1 /usr/test/rdo2/gp_01_redo_02.rdo UNUSED

2 /usr/test/rdo2/gp_02_redo_02.rdo UNUSED
2 /usr/test/rdo1/gp_02_redo_01.rdo UNUSED

3 /usr/test/rdo1/gp_03_redo_01.rdo CURRENT
3 /usr/test/rdo2/gp_03_redo_02.rdo CURRENT


6 rows selected.

7.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 167197704 generated at 03/30/2011 11:04:42 needed for thread
1
ORA-00289: suggestion : /usr/ora10/dbs/arch1_9_747140614.dbf
ORA-00280: change 167197704 for thread 1 is in sequence #9


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
*/usr/test/rdo1/gp_03_redo_01.rdo*
ORA-00310: archived log contains sequence 10; sequence 9 required
ORA-00334: archived log: '/usr/test/rdo1/gp_03_redo_01.rdo'


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/usr/test/system/sys_01.dbf'


The sys_01.dbf file seems to need more recovery, though I am not sure what exactly needs to be done.
Please help.

Thanks.

Edited by: Apps_Oracle on Mar 30, 2011 12:49 PM