Here we have to create a scenario where we lost all control files and redo log files and there is no backup. We only have data files and initialization file. The database is in no archive log mode.
The steps followed to recover the database:
The steps followed to recover the database:
[oracle@linux1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 17 03:15:59 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
Step 2. Let have delete all redolog files and control files at OS level.
[oracle@linux1 db1]$ ll
total 2076740
-rw-r----- 1 oracle oinstall 10076160 Jan 17 03:16 control01.ctl
-rw-r-----. 1 oracle oinstall 362422272 Jan 17 03:16 example01.dbf
-rw-r----- 1 oracle oinstall 52429312 Jan 17 03:16 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jan 17 03:04 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jan 17 03:04 redo03.log
-rw-r-----. 1 oracle oinstall 52436992 Jan 17 03:16 srcdata01.dbf
-rw-r-----. 1 oracle oinstall 587210752 Jan 17 03:16 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 807411712 Jan 17 03:16 system01.dbf
-rw-r-----. 1 oracle oinstall 20979712 Jan 17 02:41 temp01.dbf
-rw-r-----. 1 oracle oinstall 52436992 Jan 17 03:16 trgdata01.dbf
-rw-r-----. 1 oracle oinstall 68165632 Jan 17 03:16 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 9183232 Jan 17 03:16 users01.dbf
[oracle@linux1 db1]$ rm redo0*
[oracle@linux1 db1]$ ll
total 1923128
-rw-r----- 1 oracle oinstall 10076160 Jan 17 03:16 control01.ctl
-rw-r-----. 1 oracle oinstall 362422272 Jan 17 03:16 example01.dbf
-rw-r-----. 1 oracle oinstall 52436992 Jan 17 03:16 srcdata01.dbf
-rw-r-----. 1 oracle oinstall 587210752 Jan 17 03:16 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 807411712 Jan 17 03:16 system01.dbf
-rw-r-----. 1 oracle oinstall 20979712 Jan 17 02:41 temp01.dbf
-rw-r-----. 1 oracle oinstall 52436992 Jan 17 03:16 trgdata01.dbf
-rw-r-----. 1 oracle oinstall 68165632 Jan 17 03:16 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 9183232 Jan 17 03:16 users01.dbf
[oracle@linux1 db1]$ pwd
/u01/app/oracle/oradata/db1
[oracle@linux1 db1]$ rm control01.ctl
[oracle@linux1 db1]$ ll
total 1913288
-rw-r-----. 1 oracle oinstall 362422272 Jan 17 03:16 example01.dbf
-rw-r-----. 1 oracle oinstall 52436992 Jan 17 03:16 srcdata01.dbf
-rw-r-----. 1 oracle oinstall 587210752 Jan 17 03:16 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 807411712 Jan 17 03:16 system01.dbf
-rw-r-----. 1 oracle oinstall 20979712 Jan 17 02:41 temp01.dbf
-rw-r-----. 1 oracle oinstall 52436992 Jan 17 03:16 trgdata01.dbf
-rw-r-----. 1 oracle oinstall 68165632 Jan 17 03:16 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 9183232 Jan 17 03:16 users01.dbf
[oracle@linux1 db1]$
[oracle@linux1 db1]$ cd ../..
[oracle@linux1 oracle]$ cd fast_recovery_area/db1/
[oracle@linux1 db1]$ ll
total 19360
-rw-r----- 1 oracle oinstall 9748480 Jan 17 02:41 __control02.ctl
-rw-r----- 1 oracle oinstall 10076160 Jan 17 03:16 control02.ctl
[oracle@linux1 db1]$ rm control02.ctl
[oracle@linux1 db1]$ ll
total 9520
-rw-r----- 1 oracle oinstall 9748480 Jan 17 02:41 __control02.ctl
[oracle@linux1 db1]$ rm __control02.ctl
[oracle@linux1 db1]$ ll
total 0
[oracle@linux1 db1]$
Step 3. Now login again and and startup database.
[oracle@linux1 db1]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 17 03:23:49 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2253784 bytes
Variable Size 314575912 bytes
Database Buffers 88080384 bytes
Redo Buffers 8462336 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL>
Step 4. Alert log giving following error that control files missing.
ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/fast_recovery_area/db1/control02.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/db1/control01.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE MOUNT...
Step 5. Now again shutdown the database and startup the database in nomount mode.
SQL> shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2253784 bytes
Variable Size 314575912 bytes
Database Buffers 88080384 bytes
Redo Buffers 8462336 bytes
SQL>
Step 6. Run following command to create control file.
SQL> CREATE CONTROLFILE REUSE DATABASE "DB1" RESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
MAXDATAFILES 100
4 MAXINSTANCES 8
5 MAXLOGHISTORY 292
6 LOGFILE
7 GROUP 1 '/u01/app/oracle/oradata/db1/redo01.log' SIZE 50M,
8 GROUP 2 '/u01/app/oracle/oradata/db1/redo02.log' SIZE 50M, ,
9 GROUP 3 '/u01/app/oracle/oradata/db1/redo03.log' SIZE 50M,
10 DATAFILE
11 12 '/u01/app/oracle/oradata/db1/example01.dbf',
13 '/u01/app/oracle/oradata/db1/srcdata01.dbf',
14 '/u01/app/oracle/oradata/db1/sysaux01.dbf',
15 '/u01/app/oracle/oradata/db1/system01.dbf',
16 --'/u01/app/oracle/oradata/db1/temp01.dbf',
17 '/u01/app/oracle/oradata/db1/trgdata01.dbf',
18 '/u01/app/oracle/oradata/db1/undotbs01.dbf',
19 '/u01/app/oracle/oradata/db1/users01.dbf'
20 CHARACTER SET WE8MSWIN1252;
Control file created.
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL>
You can see that all control files and redo log files has been created.
/u01/app/oracle/fast_recovery_area/db1
[oracle@linux1 db1]$ ll
total 9840
-rw-r----- 1 oracle oinstall 10076160 Jan 17 03:35 control02.ctl
[oracle@linux1 db1]$
[oracle@linux1 db1]$ cd ../..
[oracle@linux1 oracle]$ cd oradata/db1/
[oracle@linux1 db1]$ ll
total 2076740
-rw-r----- 1 oracle oinstall 10076160 Jan 17 03:36 control01.ctl
-rw-r-----. 1 oracle oinstall 362422272 Jan 17 03:34 example01.dbf
-rw-r----- 1 oracle oinstall 52429312 Jan 17 03:36 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jan 17 03:34 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jan 17 03:34 redo03.log
-rw-r-----. 1 oracle oinstall 52436992 Jan 17 03:34 srcdata01.dbf
-rw-r-----. 1 oracle oinstall 587210752 Jan 17 03:34 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 807411712 Jan 17 03:34 system01.dbf
-rw-r-----. 1 oracle oinstall 20979712 Jan 17 02:41 temp01.dbf
-rw-r-----. 1 oracle oinstall 52436992 Jan 17 03:34 trgdata01.dbf
-rw-r-----. 1 oracle oinstall 68165632 Jan 17 03:34 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 9183232 Jan 17 03:34 users01.dbf
[oracle@linux1 db1]$
The database opened successfully. The only point to consider is that of the data loss the data in the redo log files,
No comments:
Post a Comment