Re: datafile corrupt, shutdown abort, datafile online

From: Steve Howard <stevedhoward_at_gmail.com>
Date: 26 Apr 2007 12:39:37 -0700
Message-ID: <1177616377.407666.46820@r30g2000prh.googlegroups.com>

On Apr 26, 12:25 am, Steve Robin <ocma..._at_gmail.com> wrote:
> Well I know some of you will not like me asking this question.
> But I still ask because it will clear my doubt.
> My database is in NOARCHIVELOG mode, my datafile is corrupted and
> database is shutdown abort. Is there any way to start database without
> making it offline.
>
> Database : oracle 9i
> Operating System : any platform

While not advisable, it is *technically* possible. As long as you have not cycled through your online redo logs, *and* you have a cold backup of the datafile taken after the oldest online redo log, you can restore that cold backup datafile and recover it using online logs.

/*FOR TEST, TAKE COLD BACKUP HERE */ C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 26 15:06:25 2007

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

SQL> archive log list

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     15
Next log sequence to archive   17
Current log sequence           17

SQL> create table rep.t0427(c number) tablespace users;

Table created.

SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

/* DELETE DATAFILE IN HERE */ C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 26 15:18:02 2007

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size                  1247900 bytes
Variable Size              83887460 bytes
Database Buffers           79691776 bytes
Redo Buffers                2945024 bytes

Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: 'C:\ORACLE\PRODUCT\ORADATA\TEST\USERS01.DBF'

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

Database altered.

SQL> archive log list;

Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     18
Current log sequence           20

SQL> Yeah, I know the stars have to align in just the correct formation, but it is possible, however unlikely.

Regards,

Steve Received on Thu Apr 26 2007 - 14:39:37 CDT