undo表空间文件丢失恢复(4)--无备份无recover的情况下恢复

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/lihuarongaini/article/details/99174219

undo表空间的数据文件丢失,如果没有备份的情况下,而且丢失的undo文件可以置为offline状态后(注意是offline不是recover状态),则可以如下恢复,下边给出一个例子。

 

undo表空间文件丢失恢复(1)--有备份的情况下恢复:http://blog.itpub.net/26736162/viewspace-1458654/

undo表空间文件丢失恢复(2)--无备份有redo的情况下恢复:http://blog.itpub.net/26736162/viewspace-1458663/

undo表空间文件丢失恢复(3)--无备份无redo的情况下恢复:http://blog.itpub.net/26736162/viewspace-1458750/

 

 

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Mar 12 18:13:13 2015

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

 

SQL> show parameter undo

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

SQL>

 

---这里一致性关闭后重启

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL> ho rm   /u03/app/oracle/oradata/ora1024g/undotbs01.dbf

 

 

SQL> startup;

ORACLE instance started.

 

Total System Global Area  448790528 bytes

Fixed Size                  2084616 bytes

Variable Size             130023672 bytes

Database Buffers          310378496 bytes

Redo Buffers                6303744 bytes

Database mounted.

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

ORA-01110: data file 2: '/u03/app/oracle/oradata/ora1024g/undotbs01.dbf'

 

 

 

SQL> set line 9999

SQL> col name format a100

SQL> select name,status,enabled from v$datafile;

 

NAME                                                                                                 STATUS  ENABLED

---------------------------------------------------------------------------------------------------- ------- ----------

/u03/app/oracle/oradata/ora1024g/system01.dbf                                                        SYSTEM  READ WRITE

/u03/app/oracle/oradata/ora1024g/undotbs01.dbf                                                       ONLINE  READ WRITE

/u03/app/oracle/oradata/ora1024g/sysaux01.dbf                                                        ONLINE  READ WRITE

/u03/app/oracle/oradata/ora1024g/users01.dbf                                                         ONLINE  READ WRITE

/u03/app/oracle/oradata/ora1024g/example01.dbf                                                       ONLINE  READ WRITE

/u03/app/oracle/oradata/ora1024g/undotbs02.dbf                                                       ONLINE  READ WRITE

 

6 rows selected.

 

SQL>

SQL> alter database datafile 2 offline;

 

Database altered.

 

SQL> select name,status,enabled from v$datafile;

 

NAME                                                                                                 STATUS  ENABLED

---------------------------------------------------------------------------------------------------- ------- ----------

/u03/app/oracle/oradata/ora1024g/system01.dbf                                                        SYSTEM  READ WRITE

/u03/app/oracle/oradata/ora1024g/undotbs01.dbf                                                       OFFLINE READ WRITE

/u03/app/oracle/oradata/ora1024g/sysaux01.dbf                                                        ONLINE  READ WRITE

/u03/app/oracle/oradata/ora1024g/users01.dbf                                                         ONLINE  READ WRITE

/u03/app/oracle/oradata/ora1024g/example01.dbf                                                       ONLINE  READ WRITE

/u03/app/oracle/oradata/ora1024g/undotbs02.dbf                                                       ONLINE  READ WRITE

 

6 rows selected.

 

SQL>

 

SQL> alter database open;

Database altered.

 

SQL>

 

SQL> set line 9999

SQL> col name format a100

SQL> select name,status,enabled from v$datafile;

 

NAME                                                                                                 STATUS  ENABLED

---------------------------------------------------------------------------------------------------- ------- ----------

/u03/app/oracle/oradata/ora1024g/system01.dbf                                                        SYSTEM  READ WRITE

/u03/app/oracle/oradata/ora1024g/undotbs01.dbf                                                       OFFLINE READ WRITE

/u03/app/oracle/oradata/ora1024g/sysaux01.dbf                                                        ONLINE  READ WRITE

/u03/app/oracle/oradata/ora1024g/users01.dbf                                                         ONLINE  READ WRITE

/u03/app/oracle/oradata/ora1024g/example01.dbf                                                       ONLINE  READ WRITE

/u03/app/oracle/oradata/ora1024g/undotbs02.dbf                                                       ONLINE  READ WRITE

 

6 rows selected.

 

SQL> select segment_name,status,tablespace_name from dba_rollback_segs;

 

SEGMENT_NAME                   STATUS           TABLESPACE_NAME

------------------------------ ---------------- ------------------------------

SYSTEM                         ONLINE           SYSTEM

_SYSSMU10$                     OFFLINE          UNDOTBS1

_SYSSMU9$                      OFFLINE          UNDOTBS1

_SYSSMU8$                      OFFLINE          UNDOTBS1

_SYSSMU7$                      OFFLINE          UNDOTBS1

_SYSSMU6$                      OFFLINE          UNDOTBS1

_SYSSMU5$                      OFFLINE          UNDOTBS1

_SYSSMU4$                      OFFLINE          UNDOTBS1

_SYSSMU3$                      OFFLINE          UNDOTBS1

_SYSSMU2$                      OFFLINE          UNDOTBS1

_SYSSMU1$                      OFFLINE          UNDOTBS1

_SYSSMU20$                     OFFLINE          UNDOTBS2

_SYSSMU19$                     OFFLINE          UNDOTBS2

_SYSSMU18$                     OFFLINE          UNDOTBS2

_SYSSMU17$                     OFFLINE          UNDOTBS2

_SYSSMU16$                     OFFLINE          UNDOTBS2

_SYSSMU15$                     OFFLINE          UNDOTBS2

_SYSSMU14$                     OFFLINE          UNDOTBS2

_SYSSMU13$                     OFFLINE          UNDOTBS2

_SYSSMU12$                     OFFLINE          UNDOTBS2

_SYSSMU11$                     OFFLINE          UNDOTBS2

 

21 rows selected.

 

SQL> create undo tablespace undotbs2 datafile '/u03/app/oracle/oradata/ora1024g/undotbs02.dbf' size 5m autoextend on;

 

Tablespace created.

 

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

 

System altered.

 

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

 

System altered.

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  448790528 bytes

Fixed Size                  2084616 bytes

Variable Size             130023672 bytes

Database Buffers          310378496 bytes

Redo Buffers                6303744 bytes

Database mounted.

Database opened.

SQL>

 

SQL> drop tablespace UNDOTBS1;

 

Tablespace dropped.

 

SQL>

 

SQL> create undo tablespace undotbs1 datafile '/u03/app/oracle/oradata/ora1024g/undotbs01.dbf' size 50m autoextend on;

 

Tablespace created.

 

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

 

System altered.

 

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  448790528 bytes

Fixed Size                  2084616 bytes

Variable Size             130023672 bytes

Database Buffers          310378496 bytes

Redo Buffers                6303744 bytes

Database mounted.

Database opened.

SQL> show parameter undo

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

SQL>

 

 

 

展开阅读全文

没有更多推荐了,返回首页