ANBOB™

模拟了一把丢失了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>

—-正常——————

打赏

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