oracle undo数据文件坏,oracle 恢复丢失的undo表空间数据文件

模拟了一把丢失了undo表空间datafile 后的恢复方法，完整的操作过挰

…

—————–

SQL> run

1* select file_name,tablespace_name from dba_data_files

FILE_NAME                                                                        TABLESPACE_NAME

——————————————————————————– ————————————————————

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_6cgckxds_.dbf                  USERS

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_6cgckx9p_.dbf                 SYSAUX

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_6cgckx9x_.dbf               UNDOTBS1

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_6cgckx95_.dbf                 SYSTEM

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_6cgckxc7_.dbf                EXAMPLE

/u01/app/oracle/oradata/ORCL/datafile/tt.dbf                                     TT

+ASMDATA/asmts01.dbf                                                             ASMTS

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_tbs4_6cgcmnfg_.dbf                   TBS4

/u01/app/oracle/oradata/ORCL/datafile/tt1.dbf                                    TT

/u01/app/oracle/oradata/ORCL/datafile/o1_mf_tbs1_6okmoslq_.dbf                   TBS1

/u01/app/oracle/oradata/ORCL/datafile/tbsimp.dbf                                 TBSIMP

11 rows selected.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production

With the Partitioning, OLAP and Data Mining options

[oracle@orazhang datafile]$ rm /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_6cgckx9x_.dbf

SQL> shutdown immediate

ORA-01116: error in opening database file 2

ORA-01110: data file 2: ‘/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_6cgckx9x_.dbf’

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SQL> shutdown abort

ORACLE instance shut down.

[oracle@orazhang datafile]$ ora

SQL*Plus: Release 10.2.0.1.0 – Production on 星期一 3月 14 22:04:46 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  562036736 bytes

Fixed Size                  1220556 bytes

Variable Size             192938036 bytes

Database Buffers          360710144 bytes

Redo Buffers                7168000 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 2 – see DBWR trace file

ORA-01110: data file 2:

‘/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_6cgckx9x_.dbf’

SQL> select open_mode from v$database;

OPEN_MODE

——————–

MOUNTED

SQL> alter database datafile 2 offline drop;

Database altered.

SQL> alter system set undo_management=’manual’ scope=spfile;

System altered.

SQL> create undo tablespace undotbs2 datafile ‘/u01/app/oracle/oradata/ORCL/datafile/undotbs2.dbf’ size 50m;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2;

alter system set undo_tablespace=undotbs2

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-30014: operation only supported in Automatic Undo Management mode

SQL> alter system set undo_management=auto scope=spfile;

System altered.

SQL> alter system set undo_tablespace=undotbs2;

alter system set undo_tablespace=undotbs2

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-30014: operation only supported in Automatic Undo Management mode

SQL> alter system set undo_tablespace=undotbs2 scope=spfile;

System altered.

SQL> shutdown

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> starup

SP2-0042: unknown command “starup” – rest of line ignored.

SQL> startup

ORACLE instance started.

Total System Global Area  562036736 bytes

Fixed Size                  1220556 bytes

Variable Size             197132340 bytes

Database Buffers          356515840 bytes

Redo Buffers                7168000 bytes

Database mounted.

Database opened.

SQL>

—-正常——————

打赏

微信扫一扫，打赏作者吧～