control file re-create

Step 1. First take controlfile to trace for new , are else you can use old / backup trace file also. Note : Database should be up. sql> alter database backup controlfile to trace; go to udump directory (/t01/oracle/proddb/9.2.0/admin/DEV_erpprod/udump) $ls -ltr ---------> take latest .trc file $ vi < .trc file name > Take the data from STARTUP NOMOUNT CREATE CONTROLFILE ...... ............. .............. CHARACTER SET US7ASCII ; :wq ~/new10g.trc .............> save at /home/new10g.trc Note : In that .trc file before STARTUP NOMOUNT data you can delete , and after ; (semicolon) the data also you can delete after that u can save the file . ================================================================= sample new10g.trc it is should be like this - for create new controlfile. STARTUP NOMOUNT CREATE CONTROLFILE SET DATABASE "NEWTEST" RESETLOGS NOARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 5 MAXDATAFILES 512 MAXINSTANCES 8 MAXLOGHISTORY 7260 LOGFILE GROUP 1 ( '/p01/oracle/testdata/log01a.dbf', '/p01/oracle/testdata/log01b.dbf' ) SIZE 10M, GROUP 2 ( '/p01/oracle/testdata/log02a.dbf', '/p01/oracle/testdata/log02b.dbf' ) SIZE 10M -- STANDBY LOGFILE DATAFILE '/p01/oracle/testdata/system01.dbf', '/p01/oracle/testdata/system02.dbf', '/p01/oracle/testdata/system03.dbf', '/p01/oracle/testdata/system04.dbf', '/p01/oracle/testdata/system05.dbf', '/p01/oracle/testdata/system06.dbf', '/p01/oracle/testdata/system07.dbf', '/p01/oracle/testdata/system08.dbf', '/p01/oracle/testdata/system09.dbf', '/p01/oracle/testdata/system10.dbf', '/p01/oracle/testdata/system11.dbf', '/p01/oracle/testdata/undo01.dbf', '/p01/oracle/testdata/a_archive01.dbf', '/p01/oracle/testdata/a_int01.dbf', '/p01/oracle/testdata/a_media01.dbf', '/p01/oracle/testdata/a_nolog01.dbf', '/p01/oracle/testdata/a_queue01.dbf', '/p01/oracle/testdata/a_queue02.dbf', '/p01/oracle/testdata/a_ref01.dbf', '/p01/oracle/testdata/a_ref02.dbf', '/p01/oracle/testdata/a_summ01.dbf', '/p01/oracle/testdata/a_txn_data01.dbf', '/p01/oracle/testdata/a_txn_data02.dbf', '/p01/oracle/testdata/a_txn_data03.dbf', '/p01/oracle/testdata/a_txn_ind01.dbf', '/p01/oracle/testdata/a_txn_ind02.dbf', '/p01/oracle/testdata/a_txn_ind03.dbf', '/p01/oracle/testdata/a_txn_ind04.dbf', '/p01/oracle/testdata/a_txn_ind05.dbf', '/p01/oracle/testdata/ctxd01.dbf', '/p01/oracle/testdata/odm.dbf', '/p01/oracle/testdata/olap.dbf', '/p01/oracle/testdata/owad01.dbf', '/p01/oracle/testdata/portal01.dbf', '/p01/oracle/testdata/userdata.dbf', '/p01/oracle/testdata/useridx.dbf', '/p01/oracle/testdata/sysaux01.dbf' CHARACTER SET US7ASCII ; =============================================================== Note : see the file owner group permissions , like this #chown -R oramgr:dba new10g.trc *** move the existing old control file to backup sql> shut immediate; $cd /t01/oracle/proddata> mv cntr101.dbf cntr101.dbf.bak $ cd /t01/oracle/proddata> mv cntr102.dbf cntr102.dbf.bak Note: move all your controlfiles to backup , because this is created new control files sql>@/home/new10g.trc sql>alter database open resetlogs; ----- current redolog sequence is start with 1 Now database is open Successfully , you can check it should be open stage sql> select status from v$instance;