Re: Database Recovery

From: Brian P. Mac Lean <brian.maclean_at_teldta.com>
Date: 1996/10/12
Message-ID: <32608FD9.1202_at_teldta.com>

Claes Nordfeldt wrote:

> 
> Hi !
> 
> During a system maintainence we lost a datafile for the TEMP-tablespace.
> The database was shutdown down. We have no backup to restore and no
> archivelogs. The data in the datafile are not important as it only
> was used for temp-data. Is it possible to open the database without
> the datafile and drop tablespace TEMP and then recreate it ?
> 
> Claes Nordfeldt
> Email: cnordfel_at_seisy.abb.se

This example will take you through the drop tablespace............

[KSH]: ls -Fla
total 4120

drwxr-sr-x   2 oracle   dba          512 Sep 18 08:15 ./
drwxr-sr-x   3 oracle   dba          512 Sep 17 10:16 ../
-rw-r-----   1 oracle   dba      2101248 Sep 18 12:06 bpmpur0101.dbf

[KSH]: mv bpmpur0101.dbf bpmpur0101.dbf.bk [KSH]: sqldba lmode=y
SQL*DBA: Release 7.2.3.0.0 - Production on Wed Sep 18 12:54:54 1996 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. Oracle7 Server Release 7.2.3.0.0 - Production Release With the distributed, replication and parallel query options PL/SQL Release 2.2.3.0.0 - Production
SQLDBA> connect internal
Connected.
SQLDBA> startup
ORACLE instance started.
Database mounted.
ORA-01157: cannot identify data file 7 - file not found ORA-01110: data file 7: '/home/oracle/data/bpm/other/mnt01/bpmpur0101.dbf' Attempting to dismount database........Database dismounted. Attempting to shutdown instance........ORACLE instance shut down. SQLDBA> startup mount;
ORACLE instance started.
Database mounted.
SQLDBA> select * from v$datafile;
FILE# STATUS ENABLED CHECKPOINT BYTES CREATE_BYT NAME

---------- ------- ---------- ---------- ---------- ---------- -----------------
---------------------------------------------------------------
         1 SYSTEM  READ WRITE       7489   10485760   10485760 /home/oracle/data


/bpm/sys/mnt01/bpmsystem01.dbf


         2 ONLINE  READ WRITE       7489   20971520   20971520 /home/oracle/data


/bpm/rbs/mnt01/bpmrbs0101.dbf


         3 ONLINE  READ WRITE       7489   10485760   10485760 /home/oracle/data


/bpm/table/mnt01/bpmdat0101.dbf


         4 ONLINE  READ WRITE       7489    5242880    5242880 /home/oracle/data


/bpm/index/mnt01/bpmidx0101.dbf


         5 ONLINE  READ WRITE       7489    2097152    2097152 /home/oracle/data


/bpm/temp/mnt01/bpmtmp0101.dbf


         6 ONLINE  READ WRITE       7489    2097152    2097152 /home/oracle/data


/bpm/work/mnt01/bpmwrk0101.dbf


         7 ONLINE  READ WRITE       7489          0    2097152 /home/oracle/data

/bpm/other/mnt01/bpmpur0101.dbf

7 rows selected.
SQLDBA> alter database datafile '/home/oracle/data/bpm/other/mnt01/bpmpur0101.dbf' offline;
Statement processed.
SQLDBA> select * from v$datafile;
FILE# STATUS ENABLED CHECKPOINT BYTES CREATE_BYT NAME

---------- ------- ---------- ---------- ---------- ---------- -----------------
---------------------------------------------------------------
         1 SYSTEM  READ WRITE       7489   10485760   10485760 /home/oracle/data


/bpm/sys/mnt01/bpmsystem01.dbf


         2 ONLINE  READ WRITE       7489   20971520   20971520 /home/oracle/data


/bpm/rbs/mnt01/bpmrbs0101.dbf


         3 ONLINE  READ WRITE       7489   10485760   10485760 /home/oracle/data


/bpm/table/mnt01/bpmdat0101.dbf


         4 ONLINE  READ WRITE       7489    5242880    5242880 /home/oracle/data


/bpm/index/mnt01/bpmidx0101.dbf


         5 ONLINE  READ WRITE       7489    2097152    2097152 /home/oracle/data


/bpm/temp/mnt01/bpmtmp0101.dbf


         6 ONLINE  READ WRITE       7489    2097152    2097152 /home/oracle/data


/bpm/work/mnt01/bpmwrk0101.dbf


         7 OFFLINE READ WRITE       7489          0    2097152 /home/oracle/data

/bpm/other/mnt01/bpmpur0101.dbf

7 rows selected.
SQLDBA> alter database open;
Statement processed.
SQLDBA> select * from dba_tablespaces;

TABLESPACE_NAME                INITIAL_EX NEXT_EXTEN MIN_EXTENT MAX_EXTENT PCT_I
------------------------------ ---------- ---------- ---------- ---------- -----

NCREA STATUS
----- ---------

SYSTEM                              12288      12288          1        249
   50 ONLINE
RBS01                               40960      40960          1        249
    0 ONLINE
DAT01                               40960      40960          1        249
    0 ONLINE
IDX01                               40960      40960          1        249
    0 ONLINE
TMP01                              409600     409600          1        249
    0 ONLINE
WRK01                               40960      40960          1        249
    0 ONLINE
PUR01                               40960      40960          1        249

0 ONLINE
7 rows selected.
SQLDBA> drop tablespace pur01;
Statement processed.
SQLDBA> select * from v$datafile;
FILE# STATUS ENABLED CHECKPOINT BYTES CREATE_BYT NAME

---------- ------- ---------- ---------- ---------- ---------- -----------------
---------------------------------------------------------------
         1 SYSTEM  READ WRITE       7490   10485760   10485760 /home/oracle/data


/bpm/sys/mnt01/bpmsystem01.dbf


         2 ONLINE  READ WRITE       7490   20971520   20971520 /home/oracle/data


/bpm/rbs/mnt01/bpmrbs0101.dbf


         3 ONLINE  READ WRITE       7490   10485760   10485760 /home/oracle/data


/bpm/table/mnt01/bpmdat0101.dbf


         4 ONLINE  READ WRITE       7490    5242880    5242880 /home/oracle/data


/bpm/index/mnt01/bpmidx0101.dbf


         5 ONLINE  READ WRITE       7490    2097152    2097152 /home/oracle/data


/bpm/temp/mnt01/bpmtmp0101.dbf


         6 ONLINE  READ WRITE       7490    2097152    2097152 /home/oracle/data

/bpm/work/mnt01/bpmwrk0101.dbf

6 rows selected.
SQLDBA> select * from dba_tablespaces;

TABLESPACE_NAME                INITIAL_EX NEXT_EXTEN MIN_EXTENT MAX_EXTENT PCT_I
------------------------------ ---------- ---------- ---------- ---------- -----

NCREA STATUS
----- ---------

SYSTEM                              12288      12288          1        249
   50 ONLINE
RBS01                               40960      40960          1        249
    0 ONLINE
DAT01                               40960      40960          1        249
    0 ONLINE
IDX01                               40960      40960          1        249
    0 ONLINE
TMP01                              409600     409600          1        249
    0 ONLINE
WRK01                               40960      40960          1        249

0 ONLINE
6 rows selected.
SQLDBA> select * from v$database;
NAME CREATED LOG_MODE CHECKPOINT ARCHIVE_CH

--------- -------------------- ------------ ---------- ----------
BPM       09/17/96 10:17:04    ARCHIVELOG         7490       7465

1 row selected.
SQLDBA> exit
SQL*DBA complete.
[KSH]:

-- 
                       \\|//
                       (0-0)
           +-----oOO----(_)-----------+
           | Brian P. Mac Lean        |
           | Database Analyst         |
           | brian.maclean_at_teldta.com |
           | http://www.teldta.com    |
           +-------------------oOO----+
                      |__|__|
                       || ||
                      ooO Ooo

Received on Sat Oct 12 1996 - 00:00:00 CEST

This message: [ Message body ]
Next message: Ramamurth Sreedhara: "Makefile for Pro*c and C"
Previous message: Martin Douglas: "Re: Database Recovery"
In reply to: Claes Nordfeldt: "Database Recovery"
Next in thread: varad acharya: "Re: Database Recovery"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message