oracle数据库再恢复中会用到两个东西:recover,restore
recover:把备份的文件恢复回去
restore:利用日志重做
1 undo表空间丢失后的恢复
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/orcl/system01.dbf
/u01/oracle/oradata/orcl/sysaux01.dbf
/u01/oracle/oradata/orcl/undotbs01.dbf
/u01/oracle/oradata/orcl/users01.dbf
/u01/oracle/oradata/orcl/user02.dbf
/u01/oracle/oradata/orcl/tmpspace0327
6 rows selected.
SQL> ho rm -rf /u01/oracle/oradata/orcl/undotbs01.dbf;
# undo表空间被丢失了
SQL> conn scott/tiger
Connected.
#这个时候还能切换数据库使用,是因为undo还在内存中
SQL> conn / as sysdba
ERROR:
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/oracle/oradata/orcl/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
# 切换会sys的时候已经提示文件不在了。
现在可以恢复数据库
由于现在数据库还是可以使用的,所以可以采用不关闭数据库的情况下新建一个undo进行切换
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/orcl/system01.dbf
/u01/oracle/oradata/orcl/sysaux01.dbf
/u01/oracle/oradata/orcl/undotbs01.dbf
/u01/oracle/oradata/orcl/users01.dbf
/u01/oracle/oradata/orcl/user02.dbf
/u01/oracle/oradata/orcl/tmpspace0327
SQL> ho ls -l /u01/oracle/oradata/orcl/*.dbf
-rw-r----- 1 oracle dba 650125312 Apr 9 15:27 /u01/oracle/oradata/orcl/sysaux01.dbf
-rw-r----- 1 oracle dba 1027612672 Apr 9 15:26 /u01/oracle/oradata/orcl/system01.dbf
-rw-r----- 1 oracle dba 275783680 Apr 9 14:32 /u01/oracle/oradata/orcl/temp1.dbf
-rw-r----- 1 oracle dba 10493952 Apr 9 14:18 /u01/oracle/oradata/orcl/user02.dbf
-rw-r----- 1 oracle dba 5251072 Apr 9 14:18 /u01/oracle/oradata/orcl/users01.dbf
# 比较 就可以看出来undo 文件确实不在了
创建新的undo表空间
SQL> create undo tablespace undotbs2 datafile '/u01/oracle/oradata/orcl/undotbs02.dbf' size 10M autoextend on;
SQL> ho ls -l /u01/oracle/oradata/orcl/*.dbf
-rw-r----- 1 oracle dba 650125312 Apr 9 15:38 /u01/oracle/oradata/orcl/sysaux01.dbf
-rw-r----- 1 oracle dba 1027612672 Apr 9 15:37 /u01/oracle/oradata/orcl/system01.dbf
-rw-r----- 1 oracle dba 275783680 Apr 9 14:32 /u01/oracle/oradata/orcl/temp1.dbf
-rw-r----- 1 oracle dba 10493952 Apr 9 15:29 /u01/oracle/oradata/orcl/undotbs02.dbf
-rw-r----- 1 oracle dba 10493952 Apr 9 14:18 /u01/oracle/oradata/orcl/user02.dbf
-rw-r----- 1 oracle dba 5251072 Apr 9 14:18 /u01/oracle/oradata/orcl/users01.dbf
# 新的undotbs2 已经创建
SQL> select TABLESPACE_NAME,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
USER02 ONLINE
TMP_SPACE_0327 ONLINE
TEMP ONLINE
设置系统的undo 表空间
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
# 当前系统的参数undo_tablespace 还是原来的UNDOTBS1,所以需要切换
SQL> SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
# 已经切换成功
然后把原来的undo删除
SQL> drop tablespace UNDOTBS1;
drop tablespace UNDOTBS1
*
ERROR at line 1:
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/oracle/oradata/orcl/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
#文件不在,删除失败
可以把原来的表空间下线
SQL> alter tablespace UNDOTBS1 offline;
alter tablespace UNDOTBS1 offline
*
ERROR at line 1:
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/oracle/oradata/orcl/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
#文件不在,下线失败
可以使用文件下线
SQL> alter database datafile 3 offline;
Database altered.
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- -------------------------------------------------- -------
1 /u01/oracle/oradata/orcl/system01.dbf SYSTEM
2 /u01/oracle/oradata/orcl/sysaux01.dbf ONLINE
3 /u01/oracle/oradata/orcl/undotbs01.dbf RECOVER
4 /u01/oracle/oradata/orcl/users01.dbf ONLINE
5 /u01/oracle/oradata/orcl/user02.dbf ONLINE
6 /u01/oracle/oradata/orcl/tmpspace0327 ONLINE
7 /u01/oracle/oradata/orcl/undotbs02.dbf ONLINE
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 584568832 bytes
Fixed Size 2230552 bytes
Variable Size 440403688 bytes
Database Buffers 134217728 bytes
Redo Buffers 7716864 bytes
Database mounted.
Database opened.
SQL> conn scott/tiger
Connected.
SQL> conn /as sysdba
Connected.
# 数据库已经可以正常关闭启动了,到此undo表空间丢失问题已经处理成功。
2 还有一种处理undo 表空间丢失的方法。
可以使用备份文件进行恢复。
注意:前提是undo表空间已经被备份过
下面我们就再模拟一次undo丢失恢复的过程
2.1 先再rman 备份
RMAN> backup tablespace UNDOTBS2;
Starting backup at 09-APR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/oracle/oradata/orcl/undotbs02.dbf
channel ORA_DISK_1: starting piece 1 at 09-APR-17
channel ORA_DISK_1: finished piece 1 at 09-APR-17
piece handle=/tmp/0os1904r_1_1 tag=TAG20170409T160859 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-APR-17
2.2 删除undo
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /u01/oracle/oradata/orcl/system01.dbf
2 /u01/oracle/oradata/orcl/sysaux01.dbf
3 /u01/oracle/oradata/orcl/undotbs01.dbf
4 /u01/oracle/oradata/orcl/users01.dbf
5 /u01/oracle/oradata/orcl/user02.dbf
6 /u01/oracle/oradata/orcl/tmpspace0327
7 /u01/oracle/oradata/orcl/undotbs02.dbf
7 rows selected.
SQL> ho rm -rf /u01/oracle/oradata/orcl/undotbs02.dbf;
刷新
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush GLOBAL CONTEXT;
System altered.
切换用户
SQL> conn scott/tiger
ERROR:
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/u01/oracle/oradata/orcl/undotbs02.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Warning: You are no longer connected to ORACLE.
可以看出来数据库已经无法使用。
强制关闭shutdown abort
SQL> shutdown abort;
ORACLE instance shut down.
启动到mount
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 584568832 bytes
Fixed Size 2230552 bytes
Variable Size 440403688 bytes
Database Buffers 134217728 bytes
Redo Buffers 7716864 bytes
Database mounted.
文件恢复(在rman里面)
RMAN> restore datafile 7;
Starting restore at 09-APR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=138 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 00007 to /u01/oracle/oradata/orcl/undotbs02.dbf
channel ORA_DISK_1: reading from backup piece /tmp/0os1904r_1_1
channel ORA_DISK_1: piece handle=/tmp/0os1904r_1_1 tag=TAG20170409T160859
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 09-APR-17
文件重做
RMAN> recover datafile 7;
Starting recover at 09-APR-17
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 09-APR-17
打开数据库
SQL> alter database open;
Database altered.
SQL> conn scott/tiger
Connected.
到此undo 表空间又被完全恢复了。
但是这样做是需要重启数据库的,如果数据库需要7*24 开机,则这中办法不可取。