From: yong321@yahoo.com
Newsgroups: comp.databases.oracle.misc
Subject: Re: HELP! deleted system database file :(
Date: Fri, 02 Feb 2001 22:04:04 GMT
Organization: Deja.com
Lines: 109
Message-ID: <95fasc$oro$1@nnrp1.deja.com>
References: <95a5hj$8v3$1@nnrp1.deja.com> <95cd94$69k$1@nnrp1.deja.com> <3A7AB94F.B6A4F231@edcmail.cr.usgs.gov> <95fagv$om3$1@nnrp1.deja.com>
NNTP-Posting-Host: 163.188.121.48
X-Article-Creation-Date: Fri Feb 02 22:04:04 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


In article <95fagv$om3$1@nnrp1.deja.com>,
  yong321@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@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@yahoo.com


Sent via Deja.com
http://www.deja.com/