oracle undo表空间误删恢复

有rman备份常规恢复
[oracle@localhost pahc]$ mv undotbs01.dbf undotbs01.dbf.bak
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size      2251856 bytes
Variable Size    163578800 bytes
Database Buffers    50331648 bytes
Redo Buffers      5169152 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/oracle/app/oradata/pahc/undotbs01.dbf'

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 5 11:08:51 2016

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

connected to target database: PAHC (DBID=1813560051, not open)

RMAN> restore datafile 3;

Starting restore at 05-APR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/oracle/app/oradata/pahc/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oracle/app/fast_recovery_area/HCDBDG/backupset/2016_04_05/o1_mf_nnndf_TAG20160405T110319_cj6bqr7f_.bkp
channel ORA_DISK_1: piece handle=/u01/oracle/app/fast_recovery_area/HCDBDG/backupset/2016_04_05/o1_mf_nnndf_TAG20160405T110319_cj6bqr7f_.bkp tag=TAG20160405T110319
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 05-APR-16

RMAN> recover datafile 3;

Starting recover at 05-APR-16
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 05-APR-16

 

SQL> alter database open;

Database altered.

没有备份非正常关闭数据库
没有备份正常关闭数据库
修改pfile参数
*.undo_management='MANUAL'
*._allow_resetlogs_corruption=true
_corrupted_rollback_segments=('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')

_offline_rollback_segments=('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')

SQL> select * from v$rollname;

       USN NAME
---------- ------------------------------
  0 SYSTEM
  1 _SYSSMU1_3724004606$
  2 _SYSSMU2_2996391332$
  3 _SYSSMU3_1723003836$
  4 _SYSSMU4_1254879796$
  5 _SYSSMU5_898567397$
  6 _SYSSMU6_1263032392$
  7 _SYSSMU7_2070203016$
  8 _SYSSMU8_517538920$
  9 _SYSSMU9_1650507775$
 10 _SYSSMU10_1197734989$

11 rows selected.

SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
undo_management        string  AUTO
undo_retention        integer  900
undo_tablespace        string  UNDOTBS1
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount pfile='/u01/oracle/app/oracle/product/11.2/dbhome_1/dbs/initpahc.ora';
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size      2251856 bytes
Variable Size    163578800 bytes
Database Buffers    50331648 bytes
Redo Buffers      5169152 bytes
Database mounted.
SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_
---------- ------- -------
ERROR             CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
  3 ONLINE  ONLINE
FILE NOT FOUND           0

 

SQL> alter database datafile 3 offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> create undo tablespace undotbs02 datafile '/u01/oracle/app/oradata/pahc/undotbs02.dbf' size 100m;

Tablespace created.

SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
undo_management        string  MANUAL
undo_retention        integer  900
undo_tablespace        string
SQL> alter system set undo_tablespace=undotbs02;
alter system set undo_tablespace=undotbs02
*
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> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount pfile='/u01/oracle/app/oracle/product/11.2/dbhome_1/dbs/initpahc.ora';
ORACLE instance started.

Total System Global Area  221331456 bytes
Fixed Size      2251856 bytes
Variable Size    163578800 bytes
Database Buffers    50331648 bytes
Redo Buffers      5169152 bytes
Database mounted.
SQL> alter database open;

Database altered.

SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
undo_management        string  AUTO
undo_retention        integer  900
undo_tablespace        string  UNDOTBS02
SQL> create spfile from pfile;

File created.

SQL>

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

转载于:http://blog.itpub.net/29497510/viewspace-2075556/