oracle resetlogs 误删,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>