Recover Database without control files and redo log files

The steps followed to recover the database:

Startup the database with the initialization file. As we do not have the control files, start the database in no mount state.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 209715200 bytes

Fixed Size 1248140 bytes

Variable Size 75498612 bytes

Database Buffers 130023424 bytes

Redo Buffers 2945024 bytes

SQL>

Check the path of control files.

SQL> show parameter control

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_files string K:\ORCL10G\CONTROL\CONTROL01.C

TL, K:\ORCL10G\CONTROL\CONTROL

02.CTL, K:\ORCL10G\CONTROL\CON

TROL03.CTL

Having the details of all the data files at hand recreate the control files.

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL10G" RESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 'K:\ORCL10G\LOG\REDO01.LOG' SIZE 50M,

GROUP 2 'K:\ORCL10G\LOG\REDO02.LOG' SIZE 50M,

GROUP 3 'K:\ORCL10G\LOG\REDO03.LOG' SIZE 50M

DATAFILE

'K:\ORCL10G\DATA\SYSTEM01.DBF',

'K:\ORCL10G\DATA\UNDOTBS01.DBF',

'K:\ORCL10G\DATA\SYSAUX01.DBF',

'K:\ORCL10G\DATA\USERS01.DBF',

'K:\ORCL10G\DATA\EXAMPLE01.DBF',

'K:\ORCL10G\DATA\UNDOTBS02.DBF'

CHARACTER SET WE8MSWIN1252;

Control file created.

Note the RESETLOGS option in the create control file script. This will reset the logs and synchronizes the SCN between the database files, control files and redo log file. Oracle will re-create the redo log files when the database is opened with resetlogs options.

Once the control file is created, try mounting the database. If the database mounts well then everything seems to be fine.

SQL> alter database mount;

Database altered.

The database mounted successfully. Open the database with resetlogs option.

SQL> alter database open resetlogs;

Database altered.

Here we go. The database opened successfully. The only point to consider is that of the data loss. The data in the redo log files, as it existed before the loss, will be lost. Hence the data loss here could be minimum.