ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 当前或非当前的undo表空间的恢复

当前或非当前的undo表空间的恢复

原创 Linux操作系统 作者:oracleclub 时间:2011-12-01 00:49:49 0 删除 编辑
当前或非当前的undo表空间的恢复

SQL> select TABLESPACE_NAME,STATUS,CONTENTS
  2  from dba_tablespaces
  3  where CONTENTS = 'UNDO';

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
UNDOTBS1                       ONLINE    UNDO

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     7200
undo_tablespace                      string      UNDOTBS1

SQL> create undo tablespace test_undo
  2  datafile '/u01/disk1/test_undo.dbf'
  3  size 50M
  4  extent management local;

Tablespace created.

SQL> select TABLESPACE_NAME,STATUS,CONTENTS
  2  from dba_tablespaces
  3  where CONTENTS = 'UNDO';

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
UNDOTBS1                       ONLINE    UNDO
TEST_UNDO                      ONLINE    UNDO


SQL> alter system set undo_tablespace = TEST_UNDO;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     7200
undo_tablespace                      string      TEST_UNDO

SQL> select FILE_ID,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 MB
  2  from dba_data_files
  3  where TABLESPACE_NAME like '%UNDO%';

   FILE_ID FILE_NAME                                          TABLESPACE_NAME         MB
---------- -------------------------------------------------- --------------- ----------
         2 /u01/app/oracle/oradata/orcl/undotbs01.dbf         UNDOTBS1                30
         6 /u01/disk1/test_undo.dbf                           TEST_UNDO               50

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

SQL> ho rm /u01/disk1/test_undo.dbf -rf

SQL> ho ls /u01/disk1/

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/disk1/test_undo.dbf'


SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database datafile 6 offline drop;

Database altered.


SQL> alter database open;

Database altered.

SQL> select FILE_ID,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 MB
  2  from dba_data_files
  3  where TABLESPACE_NAME like '%UNDO%';

   FILE_ID FILE_NAME                                          TABLESPACE_NAME         MB
---------- -------------------------------------------------- --------------- ----------
         2 /u01/app/oracle/oradata/orcl/undotbs01.dbf         UNDOTBS1                30
         6 /u01/disk1/test_undo.dbf                           TEST_UNDO

SQL> select TABLESPACE_NAME,STATUS,CONTENTS
  2  from dba_tablespaces
  3  where CONTENTS='UNDO';

TABLESPACE_NAME STATUS    CONTENTS
--------------- --------- ---------
UNDOTBS1        ONLINE    UNDO
TEST_UNDO       ONLINE    UNDO

但是数据库还认为这个TEST_UNDO还存在,所以要手工删除
SQL> drop tablespace TEST_UNDO;
drop tablespace TEST_UNDO
*
ERROR at line 1:
ORA-30013: undo tablespace 'TEST_UNDO' is currently in use


SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     7200
undo_tablespace                      string      TEST_UNDO
SQL> alter system set undo_tablespace = UNDOTBS1;

System altered.

SQL> drop tablespace TEST_UNDO;

Tablespace dropped.

SQL> select FILE_ID,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 MB
  2  from dba_data_files
  3  where TABLESPACE_NAME like '%UNDO%';

   FILE_ID FILE_NAME                                          TABLESPACE_NAME         MB
---------- -------------------------------------------------- --------------- ----------
         2 /u01/app/oracle/oradata/orcl/undotbs01.dbf         UNDOTBS1                30

SQL> select TABLESPACE_NAME,STATUS,CONTENTS
  2  from dba_tablespaces
  3  where CONTENTS='UNDO';

TABLESPACE_NAME STATUS    CONTENTS
--------------- --------- ---------
UNDOTBS1        ONLINE    UNDO


丢失的表空间只要重建就行了
SQL> create undo tablespace test_undo
  2  datafile '/u01/disk1/test_undo.dbf'
  3  size 50M
  4  extent management local;

Tablespace created.

SQL> select FILE_ID,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024 MB
  2  from dba_data_files
  3  where TABLESPACE_NAME like '%UNDO%';

   FILE_ID FILE_NAME                                          TABLESPACE_NAME         MB
---------- -------------------------------------------------- --------------- ----------
         2 /u01/app/oracle/oradata/orcl/undotbs01.dbf         UNDOTBS1                30
         6 /u01/disk1/test_undo.dbf                           TEST_UNDO               50

SQL> select TABLESPACE_NAME,STATUS,CONTENTS
  2  from dba_tablespaces
  3  where CONTENTS='UNDO';

TABLESPACE_NAME STATUS    CONTENTS
--------------- --------- ---------
UNDOTBS1        ONLINE    UNDO
TEST_UNDO       ONLINE    UNDO

总结:undo表空间,最好保存有两个,方便恢复时将另一个还健存的undo表空间切换成当前表空间,再重建
当前undo表空间的丢失

如果把在线而且当前的undo表空间删除,会数据库会当场down掉,出现以下提示,恢复方法跟上面的非当前的undo表空间方法一样
SQL> shutdown immediate
ORA-03113: end-of-file on communication channel




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26465805/viewspace-712485/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-11-22

  • 博文量
    61
  • 访问量
    82187