oracle 表空间undo丢失后的恢复

最新推荐文章于 2023-05-09 10:38:07 发布

weixin_33794672

最新推荐文章于 2023-05-09 10:38:07 发布

阅读量474

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/wangzilong/blog/875672

版权

2019独角兽企业重金招聘Python工程师标准>>>

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 开机，则这中办法不可取。

转载于:https://my.oschina.net/wangzilong/blog/875672

weixin_33794672

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 表空间undo丢失后的恢复

2019独角兽企业重金招聘Python工程师标准>>> ...
复制链接

扫一扫