If you lost all CONTROLFILE(create controlfile command)


bash-3.00$ ls -lrt
total 3720312
-rw-r-----   1 oracle   oinstall 69214208 Nov 27 17:41 temp02.dbf
-rw-r-----   1 oracle   oinstall 10493952 Nov 27 17:41 ttstest.dbf
-rw-r-----   1 oracle   oinstall 52429312 Nov 27 17:45 redo02.log
-rw-r-----   1 oracle   oinstall 52429312 Nov 27 18:14 redo03.log
drwxr-xr-x   2 oracle   oinstall    1024 Nov 27 18:14 arch
-rw-r-----   1 oracle   oinstall 5251072 Nov 27 18:20 users01.dbf
-rw-r-----   1 oracle   oinstall 52436992 Nov 27 18:20 cooldata02.dbf
-rw-r-----   1 oracle   oinstall 419438592 Nov 27 18:20 cooldata01.dbf
-rw-r-----   1 oracle   oinstall 367009792 Nov 27 21:37 sysaux01.dbf
-rw-r-----   1 oracle   oinstall 173023232 Nov 27 21:41 undotbs01.dbf
-rw-r-----   1 oracle   oinstall 629153792 Nov 27 21:45 system01.dbf
-rw-r-----   1 oracle   oinstall 52429312 Nov 27 21:45 redo01.log
-rw-r-----   1 oracle   oinstall 7061504 Nov 27 21:49 control03.ctl
-rw-r-----   1 oracle   oinstall 7061504 Nov 27 21:49 control01.ctl
-rw-r-----   1 oracle   oinstall 7061504 Nov 27 21:49 control02.ctl

bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Nov 27 21:49:58 2013

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> host pwd
/export/d3120/oradata/JUNKDB

SQL> host mkdir backup

SQL> alter database begin backup;

Database altered.

SQL> host cp *.dbf backup/      

SQL> alter database end backup;

Database altered.

SQL>

SQL> alter database backup controlfile to trace as '/export/d3120/oradata/JUNKDB/backup/controlfile.trc';

Database altered.

SQL> alter database backup controlfile to '/export/d3120/oradata/JUNKDB/backup/control01.ctl';

Database altered.

SQL> host rm *.dbf

SQL> host ls
arch           control01.ctl  control02.ctl  control03.ctl  redo01.log     redo02.log     redo03.log     backup

SQL> host rm *.ctl

SQL> host ls
arch        redo01.log  redo02.log  redo03.log  backup

SQL> create table eser as select * from dba_objects;
create table eser as select * from dba_objects
                                   *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/export/d3120/oradata/JUNKDB/system01.dbf'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3

SQL>

SQL> shu abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size                  2126904 bytes
Variable Size             513641416 bytes
Database Buffers          738197504 bytes
Redo Buffers                4325376 bytes
ORA-00205: error in identifying control file, check alert log for more info

bash-3.00$ cp ./backup/*.dbf .
bash-3.00$ ls -lrt
total 3686554
-rw-r-----   1 oracle   oinstall 52429312 Nov 27 17:45 redo02.log
-rw-r-----   1 oracle   oinstall 52429312 Nov 27 18:14 redo03.log
drwxr-xr-x   2 oracle   oinstall    1024 Nov 27 18:14 arch
drwxr-xr-x   2 oracle   oinstall    1024 Nov 27 21:54 backup
-rw-r-----   1 oracle   oinstall 52429312 Nov 27 21:56 redo01.log
-rw-r-----   1 oracle   oinstall 419438592 Nov 27 21:58 cooldata01.dbf
-rw-r-----   1 oracle   oinstall 52436992 Nov 27 21:58 cooldata02.dbf
-rw-r-----   1 oracle   oinstall 367009792 Nov 27 21:59 sysaux01.dbf
-rw-r-----   1 oracle   oinstall 629153792 Nov 27 21:59 system01.dbf
-rw-r-----   1 oracle   oinstall 69214208 Nov 27 21:59 temp02.dbf
-rw-r-----   1 oracle   oinstall 10493952 Nov 27 21:59 ttstest.dbf
-rw-r-----   1 oracle   oinstall 173023232 Nov 27 21:59 undotbs01.dbf
-rw-r-----   1 oracle   oinstall 5251072 Nov 27 21:59 users01.dbf
bash-3.00$

vi ./backup/controlfile.trc

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "JUNKDB" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/export/d3120/oradata/JUNKDB/redo01.log'  SIZE 50M,
  GROUP 2 '/export/d3120/oradata/JUNKDB/redo02.log'  SIZE 50M,
  GROUP 3 '/export/d3120/oradata/JUNKDB/redo03.log'  SIZE 50M
DATAFILE
  '/export/d3120/oradata/JUNKDB/system01.dbf',
  '/export/d3120/oradata/JUNKDB/undotbs01.dbf',
  '/export/d3120/oradata/JUNKDB/sysaux01.dbf',
  '/export/d3120/oradata/JUNKDB/users01.dbf',
  '/export/d3120/oradata/JUNKDB/cooldata01.dbf',
  '/export/d3120/oradata/JUNKDB/cooldata02.dbf'
CHARACTER SET WE8ISO8859P9
;

SQL> shu abort
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size                  2126904 bytes
Variable Size             513641416 bytes
Database Buffers          738197504 bytes
Redo Buffers                4325376 bytes
SQL>
SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "JUNKDB" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL>   2    3    4    5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/export/d3120/oradata/JUNKDB/redo01.log'  SIZE 50M,
  9    GROUP 2 '/export/d3120/oradata/JUNKDB/redo02.log'  SIZE 50M,
10    GROUP 3 '/export/d3120/oradata/JUNKDB/redo03.log'  SIZE 50M
11  DATAFILE
12    '/export/d3120/oradata/JUNKDB/system01.dbf',
13    '/export/d3120/oradata/JUNKDB/undotbs01.dbf',
14    '/export/d3120/oradata/JUNKDB/sysaux01.dbf',
15    '/export/d3120/oradata/JUNKDB/users01.dbf',
16    '/export/d3120/oradata/JUNKDB/cooldata01.dbf',
17    '/export/d3120/oradata/JUNKDB/cooldata02.dbf'
18  CHARACTER SET WE8ISO8859P9
19  ;

Control file created.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/export/d3120/oradata/JUNKDB/system01.dbf'

SQL> recover database;
Media recovery complete.
SQL>
SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: