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 Ranarved.
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/anardb/oradata/AZKKDB
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/anardb/oradata/AZKKDB/backup/controlfile.trc';
Database altered.
SQL> alter database backup controlfile to '/export/anardb/oradata/AZKKDB/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 anar as select * from dba_objects;
create table anar 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/anardb/oradata/AZKKDB/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 "AZKKDB" NORESETLOGS ARCHIVELOG
 MAXLOGFILES 16
 MAXLOGMEMBERS 3
 MAXDATAFILES 100
 MAXINSTANCES 8
 MAXLOGHISTORY 292
LOGFILE
 GROUP 1 '/export/anardb/oradata/AZKKDB/redo01.log' SIZE 50M,
 GROUP 2 '/export/anardb/oradata/AZKKDB/redo02.log' SIZE 50M,
 GROUP 3 '/export/anardb/oradata/AZKKDB/redo03.log' SIZE 50M
DATAFILE
 '/export/anardb/oradata/AZKKDB/system01.dbf',
 '/export/anardb/oradata/AZKKDB/undotbs01.dbf',
 '/export/anardb/oradata/AZKKDB/sysaux01.dbf',
 '/export/anardb/oradata/AZKKDB/users01.dbf',
 '/export/anardb/oradata/AZKKDB/cooldata01.dbf',
 '/export/anardb/oradata/AZKKDB/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 "AZKKDB" 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/anardb/oradata/AZKKDB/redo01.log' SIZE 50M,
 9 GROUP 2 '/export/anardb/oradata/AZKKDB/redo02.log' SIZE 50M,
10 GROUP 3 '/export/anardb/oradata/AZKKDB/redo03.log' SIZE 50M
11 DATAFILE
12 '/export/anardb/oradata/AZKKDB/system01.dbf',
13 '/export/anardb/oradata/AZKKDB/undotbs01.dbf',
14 '/export/anardb/oradata/AZKKDB/sysaux01.dbf',
15 '/export/anardb/oradata/AZKKDB/users01.dbf',
16 '/export/anardb/oradata/AZKKDB/cooldata01.dbf',
17 '/export/anardb/oradata/AZKKDB/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/anardb/oradata/AZKKDB/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: