丢失undo表空间后恢复

丢失undo表空间


1.利用RMAN恢复

2.重建新的undo表空间（可以不关闭数据库）

--实验
--1.利用RMAN关库恢复
SYS@PROD2> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD2/system01.dbf
/u01/app/oracle/oradata/PROD2/sysaux01.dbf
/u01/app/oracle/oradata/PROD2/undotbs01.dbf
/u01/app/oracle/oradata/PROD2/users01.dbf
/u01/app/oracle/oradata/PROD2/example01.dbf

SYS@PROD2> ho rm /u01/app/oracle/oradata/PROD2/undotbs01.dbf


SYS@PROD2> alter system flush shared_pool;

System altered.

SYS@PROD2> alter system flush buffer_cache;

System altered.

SYS@PROD2> alter system flush global context;

System altered.

SYS@PROD2> conn scott/tiger;  --此时显示undo表空间丢失
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/app/oracle/oradata/PROD2/undotbs01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3


Warning: You are no longer connected to ORACLE.

[oracle@ocm1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Dec 16 20:31:16 2016

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

connected to target database: PROD2 (DBID=1564287740)

RMAN> shutdown abort

using target database control file instead of recovery catalog
Oracle instance shut down

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area     958341120 bytes

Fixed Size                     1348972 bytes
Variable Size                322964116 bytes
Database Buffers             629145600 bytes
Redo Buffers                   4882432 bytes

RMAN> restore datafile 3;  --还原数据文件

Starting restore at 16-DEC-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 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/app/oracle/oradata/PROD2/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD2/backupset/2016_12_16/o1_mf_nnndf_TAG20161216T202530_d57q9tn3_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD2/backupset/2016_12_16/o1_mf_nnndf_TAG20161216T202530_d57q9tn3_.bkp tag=TAG20161216T202530
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
Finished restore at 16-DEC-16

RMAN> recover database;  --根据日志文件还原数据库

Starting recover at 16-DEC-16
using channel ORA_DISK_1

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

Finished recover at 16-DEC-16

RMAN> exit

SYS@PROD2> conn scott/tiger   --恢复成功
ERROR:
ORA-28001: the password has expired


Changing password for scott
New password: 
Retype new password: 
Password changed
Connected.

--2.重建undo表空间

[oracle@ocm1 ~]$ rm /u01/app/oracle/oradata/PROD2/undotbs01.dbf 

SYS@PROD2> ho ls /u01/app/oracle/oradata/PROD2/undotbs01.dbf  --确认文件丢失
ls: /u01/app/oracle/oradata/PROD2/undotbs01.dbf: No such file or directory

SYS@PROD2> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/PROD2/undotbs2.dbf' size 10m autoextend on;  --创建一个新的undo表空间

Tablespace created.

SYS@PROD2> alter system set undo_tablespace=undotbs2;  

System altered.
更改undo_tablespace参数后数据库所有的DML操作都能正常的工作。

SYS@PROD2> drop tablespace undotbs1;  --删除旧的undo表空间

Tablespace dropped.

--或此时脱机undo表空间进行恢复
--以下实验将undo2删除后新建undo1，再还原undo2内容
SYS@PROD2> alter database datafile 6 offline;

Database altered.

RMAN> restore datafile 6;

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

creating datafile file number=6 name=/u01/app/oracle/oradata/PROD2/undotbs2.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 16-DEC-16

RMAN> recover datafile 6;

Starting recover at 16-DEC-16
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_16/o1_mf_1_12_d57ro1fg_.arc
archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_16/o1_mf_1_13_d57rqf15_.arc
archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_16/o1_mf_1_14_d57rs04p_.arc
archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_16/o1_mf_1_15_d57rs1l3_.arc
archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_16/o1_mf_1_16_d57rs2ln_.arc
archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_16/o1_mf_1_17_d57rs3g5_.arc
archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_16/o1_mf_1_18_d57rs432_.arc
archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_16/o1_mf_1_19_d57rs4pm_.arc
archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_16/o1_mf_1_20_d57rs5c0_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_16/o1_mf_1_12_d57ro1fg_.arc thread=1 sequence=12
archived log file name=/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_16/o1_mf_1_13_d57rqf15_.arc thread=1 sequence=13
archived log file name=/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_16/o1_mf_1_14_d57rs04p_.arc thread=1 sequence=14
archived log file name=/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_16/o1_mf_1_15_d57rs1l3_.arc thread=1 sequence=15
archived log file name=/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_16/o1_mf_1_16_d57rs2ln_.arc thread=1 sequence=16
archived log file name=/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_16/o1_mf_1_17_d57rs3g5_.arc thread=1 sequence=17
archived log file name=/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_12_16/o1_mf_1_18_d57rs432_.arc thread=1 sequence=18
media recovery complete, elapsed time: 00:00:00
Finished recover at 16-DEC-16

SYS@PROD2> alter database datafile 6 online;
--而后是否更改回默认表空间视情况而定