Re: HELP! deleted system database file :(

But in your example, you create the
/lurch/disk_2/oradata/TESTBK/system03.dbf datafile, you then delete it, and then you recover it. But you are recovering an empty datafile!!!!! How will *just recreating* this datafile perform recovery if the datafile contains data? Your example has yet to prove your point. A true test would be to create the datafile, make sure that it contains a segment, delete it, and then try your recovery method.

HTH,
Brian

yong321_at_yahoo.com wrote:
>
> When you cited my message, you omitted "the deleted system datafile is
> not the first or only one for your SYSTEM tablespace". So if that
> condition is met, here's how:
>
> SVRMGR> select * from v$database;
> DBID NAME CREATED RESETLOGS_ RESETLOGS PRIOR_RESE
> PRIOR_RES LOG_MOD
> E CHECKPOINT ARCHIVE_CH CONTROL CONTROLFI CONTROLFIL CONTROLFIL
> CONTROLFI OP
> EN_RESETL VERSION_T OPEN_MODE
> ---------- --------- --------- ---------- --------- ---------- ---------
> -------
> ----- ---------- ---------- ------- --------- ---------- ---------- ----
> ----- --
> --------- --------- ----------
> 1706358543 TESTBK 2001-02-0 422468 2001-02-0 298626 2000-12-
> 0 NOARCHI
> VELOG 422469 0 CURRENT 2001-02-0 10676 422486
> 2001-02-0 NO
> T ALLOWED 2001-02-0 READ WRITE
> 1 row selected.
> SVRMGR> select * from dba_data_files where tablespace_name = 'SYSTEM';
> FILE_NAME
> FILE_ID TABLESPACE_NAME BYTES BLOCKS
> STATUS RELAT
> IVE_F AUT MAXBYTES MAXBLOCKS INCREMENT_ USER_BYTES USER_BLOCK
> ------------------------------------------------------------------------
> --------
> ---------- ------------------------------ ---------- ---------- -------
> -- -----
> ----- --- ---------- ---------- ---------- ---------- ----------
> /lurch/disk_2/oradata/TESTBK/system02.dbf
> 12 SYSTEM 134217728 16384
> AVAILABLE
> 12 NO 0 0 0 134209536 16383
> /lurch/disk_2/oradata/TESTBK/system01.dbf
> 1 SYSTEM 136314880 16640
> AVAILABLE
> 1 NO 0 0 0 136306688 16639
> 2 rows selected.
> SVRMGR> alter tablespace system add
> datafile '/lurch/disk_2/oradata/TESTBK/syste
> m03.dbf' size 1m;
> Statement processed.
> SVRMGR> alter system switch logfile;
> Statement processed.
> SVRMGR> host rm /lurch/disk_2/oradata/TESTBK/system03.dbf
> SVRMGR> alter system switch logfile;
> ORA-03113: end-of-file on communication channel
> SVRMGR> select * from dual;
> select * from dual
> *
> ORA-03114: not connected to ORACLE
> SVRMGR> connect internal
> Connected.
> SVRMGR> startup
> ORACLE instance started.
> Total System Global Area 27881456 bytes
> Fixed Size 69616 bytes
> Variable Size 10854400 bytes
> Database Buffers 16777216 bytes
> Redo Buffers 180224 bytes
> Database mounted.
> ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
> ORA-01110: data file 11: '/lurch/disk_2/oradata/TESTBK/system03.dbf'
> SVRMGR> alter database create
> datafile '/lurch/disk_2/oradata/TESTBK/system03.db
> f';
> Statement processed.
> SVRMGR> alter database open;
> alter database open
> *
> ORA-01113: file 11 needs media recovery
> ORA-01110: data file 11: '/lurch/disk_2/oradata/TESTBK/system03.dbf'
> SVRMGR> recover datafile '/lurch/disk_2/oradata/TESTBK/system03.dbf';
> Media recovery complete.
> SVRMGR> alter database open;
> Statement processed.
> SVRMGR> select * from dual;
> D
> -
> X
> 1 row selected.
>
> I need to mention that you should immediately switch logfile after you
> realize you deleted a system datafile, unless you're running in archive
> log mode (my TESTBK is not).
>
> Yong Huang
> yong321_at_yahoo.com
>
> In article <3A7AB94F.B6A4F231_at_edcmail.cr.usgs.gov>,
> Brian Peasland <peasland_at_edcmail.cr.usgs.gov> wrote:
> >
> > > ALTER DATABASE CREATE DATAFILE 'yoursystem.dbf
> > > as '/fullpath/yoursystem.pdf'
> >
> > Will this work? It seems to me that this will just create the datafile
> > with nothing in it. Since this is the system tablespace, none of the
> > data dictionary tables (like SYS.SOURCE$) will be in this tablespace.
> > The poster will have to recover from a previous backup.
> >
> > HTH,
> > Brian
> >
> > --
> > ========================================
> > Brian Peasland
> > Raytheons Systems at
> > USGS EROS Data Center
> > These opinions are my own and do not
> > necessarily reflect the opinions of my
> > company!
> > ========================================
>
> Sent via Deja.com
> http://www.deja.com/
 

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================