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/
Received on Fri Feb 02 2001 - 15:57:58 CST
