怎么恢复损坏后的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>