From: yong321@yahoo.com Newsgroups: comp.databases.oracle.misc Subject: Re: HELP! deleted system database file :( Date: Fri, 02 Feb 2001 21:57:58 GMT Organization: Deja.com Lines: 118 Message-ID: <95fagv$om3$1@nnrp1.deja.com> References: <95a5hj$8v3$1@nnrp1.deja.com> <95cd94$69k$1@nnrp1.deja.com> <3A7AB94F.B6A4F231@edcmail.cr.usgs.gov> NNTP-Posting-Host: 163.188.121.48 X-Article-Creation-Date: Fri Feb 02 21:57:58 2001 GMT X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt) X-Http-Proxy: 1.1 x63.deja.com:80 (Squid/1.1.22) for client 163.188.121.48 X-MyDeja-Info: XMYDJUIDy0h8797 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@yahoo.com In article <3A7AB94F.B6A4F231@edcmail.cr.usgs.gov>, Brian Peasland 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/