In article <95fagv$om3$1_at_nnrp1.deja.com>,
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
Sorry. I clicked the button too quick. I should have said ALTER
DATABASE CREATE DATAFILE '/path/yoursystemdatafile.dbf' works only if
the redo log info is saved, either in archive log or online redo.
Yong Huang
yong321_at_yahoo.com
Sent via Deja.com
http://www.deja.com/
Received on Fri Feb 02 2001 - 16:04:04 CST
