当前位置:操作系统 > Unix/Linux >>

怎么恢复损坏后的undo表空间(一)

怎么恢复损坏后的undo表空间(一)
 
  数据库运行在非归档模式下,在关闭数据库的时候采用正常关闭。打开 数据库之后提示undo表空间损坏。信息如下:
 
   对于这种情况进行的恢复步骤如下:
[oracle@oracle-one RHYS]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 14 21:12:09 2013

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

Connected to an idle instance.

SQL> 
SQL> 
SQL> startup
ORACLE instance started.

Total System Global Area  405020672 bytes
Fixed Size                  2213816 bytes
Variable Size             251660360 bytes
Database Buffers          146800640 bytes
Redo Buffers                4345856 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf'


SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle-one RHYS]$ adrci

ADRCI: Release 11.2.0.1.0 - Production on Wed Aug 14 21:15:48 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/opt/app/oracle"
adrci> show homepath
ADR Homes: 
diag/tnslsnr/oracle-one/listener
diag/rdbms/rhys/RHYS
adrci> set homepath 
DIA-48431: Must specify at least one ADR home path

adrci> set homepath diag/rdbms/rhys/RHYS
adrci> show alert -tail 20
2013-08-14 21:12:13.533000 +08:00
PMON started with pid=2, OS id=3650 
VKTM started with pid=3, OS id=3652 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
GEN0 started with pid=4, OS id=3656 
DIAG started with pid=5, OS id=3658 
DBRM started with pid=6, OS id=3660 
PSP0 started with pid=7, OS id=3662 
DIA0 started with pid=8, OS id=3664 
MMAN started with pid=9, OS id=3666 
DBW0 started with pid=10, OS id=3668 
LGWR started with pid=11, OS id=3670 
CKPT started with pid=12, OS id=3672 
SMON started with pid=13, OS id=3674 
RECO started with pid=14, OS id=3676 
MMON started with pid=15, OS id=3678 
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=16, OS id=3680 
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /opt/app/oracle
ALTER DATABASE   MOUNT
2013-08-14 21:12:19.026000 +08:00
Successful mount of redo thread 1, with mount id 2735871469
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
ALTER DATABASE OPEN
Errors in file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_dbw0_3668.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
Errors in file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_3691.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf'
ORA-1157 signalled during: ALTER DATABASE OPEN...
2013-08-14 21:12:20.498000 +08:00
Checker run found 1 new persistent data failures
adrci> exit
[oracle@oracle-one RHYS]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 14 21:16:48 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database datafile '/opt/app/oracle/RHYS/undotbs01.dbf' offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_optimizer_undo_cost_change          string      11.2.0.1
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1


SQL> create undo tablespace undotbs2 datafile '/opt/app/oracle/RHYS/undotbs2.dbf' size 50M autoextend on next 5M maxsize 200M;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2;

System altered.

SQL> select usn,status from v$rollstat;

       USN STATUS
---------- ---------------
         0 ONLINE
        11 ONLINE
        12 ONLINE
        13 ONLINE
        14 ONLINE
        15 ONLINE
        16 ONLINE
        17 ONLINE
        18 ONLINE
        19 ONLINE
        20 ONLINE

11 rows selected.

SQL> col name for a60
SQL> set linesize 200
SQL> select file#,status from v$datafile;

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 ONLINE
         3 OFFLINE
         4 ONLINE
         5 ONLINE
         6 ONLINE

6 rows selected.

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

     FILE# STATUS  NAME
---------- ------- ------------------------------------------------------------
         1 SYSTEM  /opt/app/oracle/RHYS/system01.dbf
         2 ONLINE  /opt/app/oracle/RHYS/sysaux01.dbf
         3 OFFLINE /opt/app/oracle/RHYS/undotbs01.dbf
         4 ONLINE  /opt/app/oracle/RHYS/users01.dbf
         5 ONLINE  /opt/app/oracle/RHYS/test.dbf
         6 ONLINE  /opt/app/oracle/RHYS/undotbs2.dbf

6 rows selected.

SQL> drop tablespace undotbs1 including contengs and datafiles;
drop tablespace undotbs1 including contengs and datafiles
                                   *
ERROR at line 1:
ORA-01911: CONTENTS keyword expected


SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

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

     FILE# STATUS  NAME
---------- ------- ------------------------------------------------------------
         1 SYSTEM  /opt/app/oracle/RHYS/system01.dbf
         2 ONLINE  /opt/app/oracle/RHYS/sysaux01.dbf
         4 ONLINE  /opt/app/oracle/RHYS/users01.dbf
         5 ONLINE  /opt/app/oracle/RHYS/test.dbf
         6 ONLINE  /opt/app/oracle/RHYS/undotbs2.dbf

SQL>

 


CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,