mysql undo丢失_【undo表空间的丢失-恢复-1】

【undo表空间的丢失-恢复-1】

使用rman进行恢复--undo丢失 restore 把文件还原回去； recover 利用日志文件重做； 关键性的文件丢失和非关键性的文件丢失(system/undo之外的丢失) 1 删除undo文件： [oracle@oracle ~]$ rm /u01/oracle/oradata/jadl10g/undotbs01.dbf [oracle@oracle ~]$ s

使用rman进行恢复--undo丢失

restore 把文件还原回去；

recover 利用日志文件重做；

关键性的文件丢失和非关键性的文件丢失(system/undo之外的丢失)

1>

删除undo文件：

[oracle@oracle ~]$ rm /u01/oracle/oradata/jadl10g/undotbs01.dbf

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 6 14:41:40 2014

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine

and Real Application Testing options

SQL> conn scott/tiger ---此时连接没有报错，是由于undo已经缓存了，清除缓存；

Connected.

SQL> conn / as sysdba

Connected.

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush global context;

System altered.

SQL> conn scott/tiger ----连接scott用户出错，提示undo丢失

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-01116: error in opening database file 2

ORA-01110: data file 2: '/u01/oracle/oradata/jadl10g/undotbs01.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.

SQL> conn / as sysdba

Connected.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine

and Real Application Testing options

[oracle@oracle ~]$ rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Thu Nov 6 14:45:54 2014

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: JADL10G (DBID=2011530396)

RMAN> shutdown abort

using target database control file instead of recovery catalog

Oracle instance shut down

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area 599785472 bytes

Fixed Size 2098112 bytes

Variable Size 163580992 bytes

Database Buffers 427819008 bytes

Redo Buffers 6287360 bytes

RMAN> restore datafile 2;

Starting restore at 06-NOV-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00002 to /u01/oracle/oradata/jadl10g/undotbs01.dbf

channel ORA_DISK_1: reading from backup piece /u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T135107_b5p32dhf_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/oracle/flash_recovery_area/JADL10G/backupset/2014_11_06/o1_mf_nnndf_TAG20141106T135107_b5p32dhf_.bkp tag=TAG20141106T135107

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

Finished restore at 06-NOV-14

RMAN> recover database;

Starting recover at 06-NOV-14

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 06-NOV-14

RMAN> alter database open;

database opened

*****采用此种恢复方法之后不需要重新备份数据库，该方法可用于undo丢失或者系统表空间丢失。****

2>建立新的undo表空间，更改为新建的表空间，前提是能正确执行新建undo表空间的命令；

[oracle@oracle ~]$ rm /u01/oracle/oradata/jadl10g/undotbs01.dbf

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 6 14:52:24 2014

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine

and Real Application Testing options

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/oracle/oradata/jadl10g/system01.dbf

/u01/oracle/oradata/jadl10g/undotbs01.dbf

/u01/oracle/oradata/jadl10g/sysaux01.dbf

/u01/oracle/oradata/jadl10g/users01.dbf

/u01/oracle/oradata/jadl10g/example01.dbf

SQL> ho ls /u01/oracle/oradata/jadl10g/undotbs01.dbf

ls: cannot access /u01/oracle/oradata/jadl10g/undotbs01.dbf: No such file or directory

SQL> create undo tablespace undotbs2 datafile '/u01/oracle/oradata/jadl10g/undotbs02.dbf' size 10m autoextend on;

create undo tablespace undotbs2 datafile '/u01/oracle/oradata/jadl10g/undotbs02.dbf' size 10m autoextend on

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01116: error in opening database file 2

ORA-01110: data file 2: '/u01/oracle/oradata/jadl10g/undotbs01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

create undo tablespace 命令不能正常执行的话，只能采用上面的方法恢复；如果能创建成功，可以不关闭数据库；

alter system set undo_tablespace=undotbs2;

执行以下命令会错：

drop tablespace undotbs01;

alter tablespace undotbs01 offline;

可以执行以下命令脱机不在使用的文件：

alter database datafile 2 offline;

也可以将原来的文件还原回来：

进入rman：

restore datafile 2;

recover datafile 2;

进入sqlplus：

alter database datafile 【本文来自鸿网互联 (http://www.68idc.cn)】2 online;

alter system set undo_tablespace=undotbs1;

****此时需要重新备份数据库；需要注意正在使用的undo是不能脱机的额。

****不清空缓存的话是可以建立的。还有就是建立两个undo，以防止丢失一个，可以切换到另外一个上面。

****system表空间是必须关闭数据库的噢噢

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网