UNDO表空间丢失恢复一例

数据库: Oracle 9204, 操作系统: Linux
undo表空间数据文件undotbs01.dbf被意外删除, 下面是恢复的过程:

blue_stone@blueice:~$ sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Jul 20 20:08:50 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  147920392 bytes
Fixed Size                   452104 bytes
Variable Size             121634816 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL> show parameter control

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- -----------
-------------------
control_file_record_keep_time        integer                7
control_files                        string                 /home/oracl
e/oradata/orcl/cont
                                                            rol01.ctl,
/home/oracle/oradat
                                                            a/orcl/cont
rol02.ctl, /home/or
                                                            acle/oradat
a/orcl/control03.ct
                                                            l
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01507: database not mounted


SQL> alter dtabase mount;
alter dtabase mount
      *
ERROR at line 1:
ORA-00940: invalid ALTER command


SQL> alter database mount;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/home/oracle/oradata/orcl/undotbs01.dbf'

#试图创建新的文件, 替代旧的文件, 并进行恢复, 导致redo log的问题.
SQL> alter database create datafile '/home/oracle/oradata/orcl/undotbs0
1.dbf' ;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/home/oracle/oradata/orcl/undotbs01.dbf'


SQL> recovery;
SP2-0042: unknown command "recovery" - rest of line ignored.
SQL> recover
ORA-00279: change 5077 generated at 02/18/2006 20:42:06 needed for thre
ad 1
ORA-00289: suggestion : /home/oracle/product/9.2.0/dbs/arch1_1.dbf
ORA-00280: change 5077 for thread 1 is in sequence #1


Specify log: {=suggested | filename | AUTO | CANCEL}
auto;
ORA-00308: cannot open archived log 'auto;'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/home/oracle/product/9.2.0/dbs/arc
h1_1.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log '/home/oracle/product/9.2.0/dbs/arc
h1_1.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/home/oracle/oradata/orcl/undotbs01.dbf'


SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
   FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ------------
---- ------------- ---------
         1          1         10  104857600          1 NO  INACTIVE
  169629 18-FEB-06
         2          1         11  104857600          1 NO  CURRENT
  288971 01-APR-06
         3          1          9  104857600          1 NO  INACTIVE
  157549 18-FEB-06


SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
            374846

SQL> recover database;
ORA-00279: change 5077 generated at 02/18/2006 20:42:06 needed for thread 1
ORA-00289: suggestion : /home/oracle/product/9.2.0/dbs/arch1_1.dbf
ORA-00280: change 5077 for thread 1 is in sequence #1


Specify log: {=suggested | filename | AUTO | CANCEL}


SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1
SQL> create undo tablespace undotbs2 datafile '/home/oracle/oradata/orcl/undotbs02.dbf';
create undo tablespace undotbs2 datafile '/home/oracle/oradata/orcl/undotbs02.dbf'
*
ERROR at line 1:
ORA-01109: database not open


SQL> recover database until cancel;
ORA-00279: change 5077 generated at 02/18/2006 20:42:06 needed for thread 1
ORA-00289: suggestion : /home/oracle/product/9.2.0/dbs/arch1_1.dbf
ORA-00280: change 5077 for thread 1 is in sequence #1


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/home/oracle/product/9.2.0/dbs/arch1_1.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log '/home/oracle/product/9.2.0/dbs/arch1_1.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/home/oracle/oradata/orcl/system01.dbf'


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/home/oracle/oradata/orcl/system01.dbf'


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/home/oracle/oradata/orcl/undotbs01.dbf'


SQL> archivelog list
SP2-0734: unknown command beginning "archivelog..." - rest of line ignored.
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /home/oracle/product/9.2.0/dbs/arch
Oldest online log sequence     9
Current log sequence           11
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1
SQL> alter system set undo_management='MANUAL';
alter system set undo_management='MANUAL'
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL> alter system set undo_management='MANUAL' scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  147920392 bytes
Fixed Size                   452104 bytes
Variable Size             121634816 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/home/oracle/oradata/orcl/undotbs01.dbf'


SQL> alter database datafile offline drop '/home/oracle/oradata/orcl/undotbs01.dbf';
alter database datafile offline drop '/home/oracle/oradata/orcl/undotbs01.dbf'
                        *
ERROR at line 1:
ORA-02236: invalid file name


SQL> alter database datafile'/home/oracle/oradata/orcl/undotbs01.dbf' offline drop;

Database altered.

SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-16068: redo log file activation identifier mismatch
ORA-00312: online log 1 thread 1: '/home/oracle/oradata/orcl/redo01.log'


SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-16068: redo log file activation identifier mismatch
ORA-00312: online log 1 thread 1: '/home/oracle/oradata/orcl/redo01.log'


SQL> alter database open force;
alter database open force
                    *
ERROR at line 1:
ORA-02288: invalid OPEN mode


SQL> startup database force;
SP2-0714: invalid combination of STARTUP options
SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup force;
ORACLE instance started.

Total System Global Area  147920392 bytes
Fixed Size                   452104 bytes
Variable Size             121634816 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-16068: redo log file activation identifier mismatch
ORA-00312: online log 1 thread 1: '/home/oracle/oradata/orcl/redo01.log'


SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1         10  104857600          1 NO  INACTIVE                169629 18-FEB-06
         2          1         11  104857600          1 NO  CURRENT                 288971 01-APR-06
         3          1          9  104857600          1 NO  INACTIVE                157549 18-FEB-06

SQL> alter database drop logfile member '/home/oracle/oradata/orcl/redo01.log';
alter database drop logfile member '/home/oracle/oradata/orcl/redo01.log'
*
ERROR at line 1:
ORA-00361: cannot remove last log member /home/oracle/oradata/orcl/redo01.log for group 1


SQL> alter database drop logfile '/home/oracle/oradata/orcl/redo01.log';

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16068: redo log file activation identifier mismatch
ORA-00312: online log 2 thread 1: '/home/oracle/oradata/orcl/redo02.log'


SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-16068: redo log file activation identifier mismatch
ORA-00312: online log 2 thread 1: '/home/oracle/oradata/orcl/redo02.log'


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL>

SQL> conn / as sysdba
Connected.
SQL> select name, type from v$tablespace;
select name, type from v$tablespace
             *
ERROR at line 1:
ORA-00904: "TYPE": invalid identifier

SQL> select tablespace_name from v$tablespace;
select tablespace_name from v$tablespace
       *
ERROR at line 1:
ORA-00904: "TABLESPACE_NAME": invalid identifier


SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
CWMLITE
DRSYS
EXAMPLE
INDX
ODM
TOOLS
USERS
XDB
STATSPACK
AQUA
AQUA_IDX

14 rows selected.

SQL> alter tablespace undotbs1 add datafile '/home/oracle/oradata/orcl/undotbs02.dbf';
alter tablespace undotbs1 add datafile '/home/oracle/oradata/orcl/undotbs02.dbf'
*
ERROR at line 1:
ORA-01119: error in creating database file '/home/oracle/oradata/orcl/undotbs02.dbf'
ORA-17610: file '/home/oracle/oradata/orcl/undotbs02.dbf' does not exist and no size specified
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


SQL> alter tablespace undotbs1 add datafile '/home/oracle/oradata/orcl/undotbs02.dbf' size 100M;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
CWMLITE                        ONLINE
DRSYS                          ONLINE
EXAMPLE                        ONLINE
INDX                           ONLINE
ODM                            ONLINE
TOOLS                          ONLINE
USERS                          ONLINE
XDB                            ONLINE
STATSPACK                      ONLINE
AQUA_IDX                       ONLINE
AQUA                           ONLINE

14 rows selected.

SQL> alter system set undo_management='AUTO' scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  147920392 bytes
Fixed Size                   452104 bytes
Variable Size             121634816 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> select file#,status from v$datafile;

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 OFFLINE
         3 ONLINE
         4 ONLINE
         5 ONLINE
         6 ONLINE
         7 ONLINE
         8 ONLINE
         9 ONLINE
        10 ONLINE
        11 ONLINE
        12 ONLINE
        13 ONLINE
        14 ONLINE

14 rows selected.

SQL> select  name,file# from v$datafile;

NAME
---------------------------------------------------------------------------------------------------------------------------------------
     FILE#
----------
/home/oracle/oradata/orcl/system01.dbf
         1

/home/oracle/oradata/orcl/undotbs01.dbf
         2

/home/oracle/oradata/orcl/cwmlite01.dbf
         3

/home/oracle/oradata/orcl/drsys01.dbf
         4

/home/oracle/oradata/orcl/example01.dbf
         5

/home/oracle/oradata/orcl/indx01.dbf
         6

/home/oracle/oradata/orcl/odm01.dbf
         7

/home/oracle/oradata/orcl/tools01.dbf
         8

/home/oracle/oradata/orcl/users01.dbf
         9

/home/oracle/oradata/orcl/xdb01.dbf
        10

/home/oracle/oradata/orcl/statspack01.dbf
        11

/home/oracle/oradata/orcl/aqua01.dbf
        12

/home/oracle/oradata/orcl/aqua_idx01.dbf
        13

/home/oracle/oradata/orcl/undotbs02.dbf

NAME
---------------------------------------------------------------------------------------------------------------------------------------
     FILE#
----------
        14


14 rows selected.

总结, undo表空间丢失以后, 可以试图以如下步骤恢复

1 将spfile/pfile中的undo_management设置为MANUAL.
2 使用alter database datafile filename offline drop的方式删除文件.
3 将数据库启动到open状态.
4 建立新的undo表空间,或者向原有表空间增加数据文件.
5 将spfile/pfile中的undo_management设置为auto, 如果新建立了undo表空间, 还需要设置spfile/pfile中undo_tablespace为相应值, 并重新启动数据库.

请参考我另外一篇文章<<UNDO表空间丢失恢复一例(续)>>