Oracle UNDO Recreate & Recovery
来源:互联网 发布:unity3d建模教程 编辑:程序博客网 时间:2024/06/05 20:08
UNDO表空间的数据文件从OS直接删除了,数据库还没shutduwn,UNDO需要恢复。
1,数据库正常运行状态中;
2,rm - fundotbs02.dbf, 数据库在不知情的情况下接着运行,直到有日志错误告警。
Fri Apr 19 13:27:03 2013[3942] ORA-01554 reason: failed to get txn-enqueue TX-0x000d0000-0x00000000Errors in file /u01/app/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_smon_3942.trc:ORA-01595: error freeing extent (17) of rollback segment (13))ORA-01554: transaction concurrency limit reached reason:failed to get TX-enqueue params:851968, 0Fri Apr 19 13:27:51 2013Errors in file /u01/app/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_j000_10253.trc:ORA-12012: error on auto execute of job 43ORA-01116: error in opening database file 7ORA-01110: data file 7: '/u01/app/oracle/ORA11G/undotbs02.dbf'ORA-27041: unable to open fileLinux Error: 2: No such file or directoryAdditional information: 3Errors in file /u01/app/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_j000_10253.trc:ORA-12012: error on auto execute of job 43ORA-01116: error in opening database file 7ORA-01110: data file 7: '/u01/app/oracle/ORA11G/undotbs02.dbf'ORA-27041: unable to open fileLinux Error: 2: No such file or directoryAdditional information: 33,此时创建spfile也会报错。
SQL> create pfile from spfile;
create pfile from spfile
*
ERROR at line 1:
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/u01/app/oracle/ORA11G/undotbs02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
4,此时,注意保留现场,不要随意关闭数据库,查询有哪些UNDO段正在使用。
SQL> select * from v$rollname;
USN NAME
---------- ------------------------------
0 SYSTEM
11 _SYSSMU11_2087506584$
12 _SYSSMU12_1903386335$
13 _SYSSMU13_3816552520$
14 _SYSSMU14_2728152801$
15 _SYSSMU15_2082928664$
16 _SYSSMU16_1938921541$
17 _SYSSMU17_3062294638$
8 rows selected.
如果数据库已经强行关闭了,使用隐含参数再次打开数据库后,可以进行如下查询,获取UNDO段信息。
SQL> select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
11 _SYSSMU11_2087506584$ NEEDS RECOVERY UNDOTBS2
12 _SYSSMU12_1903386335$ NEEDS RECOVERY UNDOTBS2
13 _SYSSMU13_3816552520$ NEEDS RECOVERY UNDOTBS2
14 _SYSSMU14_2728152801$ NEEDS RECOVERY UNDOTBS2
15 _SYSSMU15_2082928664$ NEEDS RECOVERY UNDOTBS2
16 _SYSSMU16_1938921541$ NEEDS RECOVERY UNDOTBS2
17 _SYSSMU17_3062294638$ NEEDS RECOVERY UNDOTBS2
SQL> shutdown immediate;
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/u01/app/oracle/ORA11G/undotbs02.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
shutdown abort
注意:
如果不能正常关闭数据库,意味着数据库状态出现了不一致,如果再次启动,此时需要启用下面的参数_offline_rollback_segments去绕过一致性检查。
如果能正常关闭数据库,意味着数据库状态一致,此时_offline_rollback_segments这个参数就不需要启用。
6,使用spfile的内容创建pfile,并对以下参数进行修改。
#*.undo_tablespace='UNDOTBS2'
undo_management='MANUAL'
rollback_segments='SYSTEM'
_offline_rollback_segments=(_SYSSMU11_2087506584$,_SYSSMU12_1903386335$,_SYSSMU13_3816552520$,_SYSSMU14_2728152801$,_SYSSMU15_2082928664$,_SYSSMU16_1938921541$,_SYSSMU17_3062294638$)
这里如果不使用_offline_rollback_segments参数,会出现下面的错误
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/app/oracle/ORA11G/undotbs02.dbf'
SQL> alter database datafile '/u01/app/oracle/ORA11G/undotbs02.dbf' offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> drop tablespace UNDOTBS2;
drop tablespace UNDOTBS2
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU11_2087506584$' found, terminate
dropping tablespace
7,从pfile启动数据库。
startup mount pfile='initORA11G.oa';
alter database datafile '/u01/app/oracle/ORA11G/undotbs02.dbf' offline drop;
alter database open;
drop tablespace UNDOTBS2;
8,重建UNDO表空间
create undo tablespace UNDOTBS3 datafile '/u01/app/oracle/ORA11G/undotbs03.dbf' size 1G;
shutdown immediate;
9,修改initORA11G.ora,将之前修改的参数改回来。
*.undo_tablespace='UNDOTBS3'
*.undo_management='AUTO'
10,启动数据
startup mount pfile='initORA11G.ora';
alter database open;
create spfile from pfile;
shutdown immediate;
startup;
UNDO表空间的数据文件从OS直接删除了,数据库已经shutduwn,UNDO需要恢复。
1,通过备份进行完全恢复。
2,通过上面的UNDO参数修改,重建UNDO表空间恢复。
无论哪种方式恢复,建议首先保留现场,进行冷备份。
- Oracle UNDO Recreate & Recovery
- Oracle 10g Recreate EM
- Oracle 11.2.0.1 EM Recreate
- Undo Segment Corruption and Recovery
- ORACLE 闪回恢复区(Flashback recovery area)与undo tablespace
- Oracle Undo
- Oracle undo
- Recreate EM For Oracle 10g RAC
- Oracle命令emca -repos recreate的使用
- ORACLE RECOVERY
- Oracle Undo的学习
- Oracle managing undo data
- Oracle undo 管理
- Oracle dump undo 说明
- oracle redo undo
- Oracle undo 管理
- oracle redo 和undo
- Oracle dump undo 说明
- defer属性导致引用JQuery的页面报“浏览器无法打开网站xxx,操作被中止”错误的解决方法
- linux消息队列的内核限制
- TestCpp里面常用代码摘抄<重力感应和动作管理类>
- Oracle触发器5-Instead of触发器
- 快速可靠协议 - KCP
- Oracle UNDO Recreate & Recovery
- 关于破解的一些问题
- Android 中input event的分析
- 第六次课堂作业
- 修改mysql字符编码出现Job failed to start解决办法
- MFC中CString转换成char数组的问题
- gp 参数设置postgresql.conf
- ELI
- linux-c udp编程注意事项