标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,674)
- DB2 (22)
- MySQL (73)
- Oracle (1,536)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (22)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (67)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (562)
- Oracle安装升级 (92)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (78)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- GoldenGate 19安装和打patch
- dd破坏asm磁盘头恢复
- 删除asmlib磁盘导致磁盘组故障恢复
- Kylin Linux 安装19c
- ORA-600 krse_arc_complete.4
- Oracle 19c 202410补丁(RUs+OJVM)
- ntfs MFT损坏(ntfs文件系统故障)导致oracle异常恢复
- .mkp扩展名oracle数据文件加密恢复
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-01092 ORA-00604 ORA-01558故障处理
- ORA-65088: database open should be retried
- Oracle 19c异常恢复—ORA-01209/ORA-65088
- ORA-600 16703故障再现
- 数据库启动报ORA-27102 OSD-00026 O/S-Error: (OS 1455)
- .[metro777@cock.li].Elbie勒索病毒加密数据库恢复
- 应用连接错误,初始化mysql数据库恢复
- RAC默认服务配置优先节点
标签归档:ORA-00376
Oracle 12c undo异常处理—root pdb undo异常
在12c pdb环境中如果root pdb的undo文件异常,数据库该如何恢复呢?这篇文章模拟undo丢失的情况下进行恢复
模拟环境
三个会话,其中第一个会话对pdb1中的表进行操作,并且有事务未提交,第二个会话对pdb2进行操作,也未提交事务;第三个会话直接abort库,模拟突然库异常,然后删除root pdb下面的undo文件
--会话1 [oracle@ora1221 oradata]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.0.3 Production on Thu Jun 16 22:24:20 2016 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8260048 bytes Variable Size 671090224 bytes Database Buffers 1828716544 bytes Redo Buffers 8515584 bytes Database mounted. Database opened. SQL> SQL> SQL> SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED 4 PDB2 MOUNTED SQL> alter session set container=pdb1; Session altered. SQL> alter database open; Database altered. SQL> create user chf identified by oracle; User created. SQL> grant dba to chf; Grant succeeded. SQL> create table chf.t_xifenfei_p1 as 2 select * from dba_objects; Table created. SQL> insert into chf.t_xifenfei_p1 2 select * from dba_objects; 72427 rows created. SQL> select count(*) from chf.t_xifenfei_p1; COUNT(*) ---------- 144853 --会话2 [oracle@ora1221 ~]$ ss SQL*Plus: Release 12.2.0.0.3 Production on Thu Jun 16 22:34:01 2016 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production SQL> alter session set container=pdb2; Session altered. SQL> alter database open; Database altered. SQL> create user chf identified by oracle; User created. SQL> grant dba to chf; Grant succeeded. SQL> create table chf.t_xifenfei_p2 2 as select * from dba_objects; Table created. SQL> delete from chf.t_xifenfei_p2; 72426 rows deleted. SQL> select count(*) from chf.t_xifenfei_p2; COUNT(*) ---------- 0 --会话3 [oracle@ora1221 ~]$ ss SQL*Plus: Release 12.2.0.0.3 Production on Thu Jun 16 22:36:16 2016 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production SQL> shutdown abort ORACLE instance shut down. --删除cdb undo文件 [oracle@ora1221 orcl12c2]$ ls -ltr total 2040912 drwxr-x---. 2 oracle oinstall 4096 Jun 16 18:26 pdbseed drwxr-x---. 2 oracle oinstall 4096 Jun 16 18:27 pdb2 drwxr-x---. 2 oracle oinstall 4096 Jun 16 18:28 pdb1 -rw-r-----. 1 oracle oinstall 209715712 Jun 16 22:24 redo03.log -rw-r-----. 1 oracle oinstall 5251072 Jun 16 22:24 users01.dbf -rw-r-----. 1 oracle oinstall 34611200 Jun 16 22:25 temp01.dbf -rw-r-----. 1 oracle oinstall 849354752 Jun 16 22:35 system01.dbf -rw-r-----. 1 oracle oinstall 73408512 Jun 16 22:35 undotbs01.dbf -rw-r-----. 1 oracle oinstall 492838912 Jun 16 22:35 sysaux01.dbf -rw-r-----. 1 oracle oinstall 209715712 Jun 16 22:36 redo02.log -rw-r-----. 1 oracle oinstall 209715712 Jun 16 22:36 redo01.log -rw-r-----. 1 oracle oinstall 18726912 Jun 16 22:36 control02.ctl -rw-r-----. 1 oracle oinstall 18726912 Jun 16 22:36 control01.ctl [oracle@ora1221 orcl12c2]$ rm undotbs01.dbf [oracle@ora1221 orcl12c2]$ ls -l un* ls: cannot access un*: No such file or directory
启动数据库
由于有undo文件丢失数据库在启动的时候检测到文件丢失(ORA-01157),无法open,offline文件后依旧无法启动(ORA-00376)
[oracle@ora1221 orcl12c2]$ ss SQL*Plus: Release 12.2.0.0.3 Production on Thu Jun 16 22:51:21 2016 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup pfile='/tmp/pfile' ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8260048 bytes Variable Size 671090224 bytes Database Buffers 1828716544 bytes Redo Buffers 8515584 bytes Database mounted. ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl12c2/undotbs01.dbf' offline 数据文件 SQL> alter database datafile 4 offline ; alter database datafile 4 offline * ERROR at line 1: ORA-01145: offline immediate disallowed unless media recovery enabled SQL> alter database datafile 4 offline drop; Database altered. SQL> alter database open; alter database open * ERROR at line 1: ORA-00603: ORACLE server session terminated by fatal error ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 4 cannot be read at this time ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl12c2/undotbs01.dbf' Process ID: 7547 Session ID: 16 Serial number: 56234
把undo_management修改为manual后启动库,依旧报ORA-00376
SQL> startup pfile='/tmp/pfile' mount; ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8260048 bytes Variable Size 671090224 bytes Database Buffers 1828716544 bytes Redo Buffers 8515584 bytes Database mounted. SQL> show parameter undo_management; NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ undo_management string MANUAL SQL> alter database open; alter database open * ERROR at line 1: ORA-00603: ORACLE server session terminated by fatal error ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 4 cannot be read at this time ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl12c2/undotbs01.dbf' Process ID: 7981 Session ID: 16 Serial number: 56572
设置_corrupted_rollback_segments参数
SQL> startup pfile='/tmp/pfile' mount; ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 8260048 bytes Variable Size 671090224 bytes Database Buffers 1828716544 bytes Redo Buffers 8515584 bytes Database mounted. SQL> show parameter _corrupted_rollback_segments; NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ _corrupted_rollback_segments string _SYSSMU1_3200770482$, _SYSSMU2 _3597554035$, _SYSSMU3_2898427 493$, _SYSSMU4_670955920$, _SY SSMU5_1233449977$, _SYSSMU6_32 67641983$, _SYSSMU7_2822479342 $, _SYSSMU8_1645196706$, _SYSS MU9_3032014485$, _SYSSMU10_474 465626$ SQL> alter database open; Database altered.
通过设置_corrupted_rollback_segments参数之后,数据库正常启动,下面继续其他pdb
open pdb1
SQL> alter session set container=pdb1; Session altered. SQL> alter database open; Database altered. SQL> select count(*) from chf.t_xifenfei_p1; COUNT(*) ---------- 72426
pdb2 open
SQL> alter session set container=pdb2; Session altered. SQL> alter database open; Database altered. SQL> select count(*) from chf.t_xifenfei_p2; COUNT(*) ---------- 72426
至此数据库基本上恢复完成,但是看到的pdb里面两个测试表的数据和我们预测的有一定的偏差,看来cdb中的undo和pdb中的undo还是有一定的依赖关系.同时也说明了root的undo异常对于其他pdb的open最少在恢复上面影响不大.下一篇测试业务pdb中undo异常处理
undo异常总结和恢复思路
UNDO异常报错千奇百怪,针对本人遇到的比较常见的undo异常报错进行汇总,仅供参考,数据库恢复过程是千奇百怪的,不能照搬硬套.
ORA-00704/ORA-00376
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: ‘/u01/oracle/oradata/ora11g/undotbs01.dbf’
Error 704 happened during db open, shutting down database
USER (ospid: 17864): terminating the instance due to error 704
Instance terminated by USER, pid = 17864
ORA-1092 signalled during: alter database open…
opiodr aborting process unknown ospid (17864) as a result of ORA-1092
ORA-00600[4097]
Fri Aug 31 23:14:10 2012
Errors in file /u01/oradata/orcl/bdump/orcl_smon_15589.trc:
ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []
Fri Aug 31 23:14:12 2012
Non-fatal internal error happenned while SMON was doing logging scn->time mapping.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
ORA-01595/ORA-00600[4194]
Fri Aug 31 23:14:14 2012
Errors in file /u01/oradata/orcl/bdump/orcl_smon_15589.trc:
ORA-01595: error freeing extent (2) of rollback segment (4))
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [48], [34], [], [], [], [], []
0RA-00600[4193]
Tue Feb 14 09:35:34 2012
Errors in file d:\oracle\product\10.2.0\admin\interlib\udump\interlib_ora_2824.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4193], [2005], [2008], [], [], [], [], []
ORA-00600[kcfrbd_3]
Wed Dec 05 10:26:35 2012
SMON: enabling tx recovery
Wed Dec 05 10:26:35 2012
Database Characterset is ZHS16GBK
Wed Dec 05 10:26:35 2012
Errors in file d:\oracle\product\10.2.0\admin\orcl\bdump\orcl_smon_548.trc:
ORA-00600: internal error code, arguments: [kcfrbd_3], [2], [2279045], [1], [2277120], [2277120], [], []
SMON: terminating instance due to error 474
ORA-00600[4137]
Fri Jul 6 18:00:40 2012
SMON: ignoring slave err,downgrading to serial rollback
Fri Jul 6 18:00:41 2012
Errors in file /usr/local/oracle/admin/techdb/bdump/techdb_smon_16636.trc:
ORA-00600: internal error code, arguments: [4137], [], [], [], [], [], [], []
ORACLE Instance techdb (pid = 8) – Error 600 encountered while recovering transaction (3, 17).
ORA-01595/ORA-01594
Sat May 12 21:54:17 2012
Errors in file /oracle/app/admin/prmdb/bdump/prmdb2_smon_483522.trc:
ORA-01595: error freeing extent (2) of rollback segment (19))
ORA-01594: attempt to wrap into rollback segment (19) extent (2) which is being freed
ORA-00704/ORA-01555
Fri May 4 21:04:21 2012
select ctime, mtime, stime from obj$ where obj# = :1
Fri May 4 21:04:21 2012
Errors in file /oracle/admin/standdb/udump/perfdb_ora_1286288.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 40 with name “_SYSSMU40$” too small
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 1286288
ORA-1092 signalled during: alter database open resetlogs…
ORA-00607/ORA-00600[4194]
Block recovery completed at rba 3994.5.16, scn 0.89979533
Thu Jul 26 13:21:11 2012
Errors in file /orasvr/admin/mispdata/udump/mispdata_ora_2865.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [31], [2], [], [], [], [], []
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Instance terminated by USER, pid = 2865
ORA-1092 signalled during: ALTER DATABASE OPEN…
ORA-00704/ORA-00600[4000]
Thu Feb 28 19:29:13 2013
Errors in file /u1/PROD/prodora/db/tech_st/10.2.0/admin/PROD_oracle/udump/prod_ora_20989.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [4000], [50], [], [], [], [], [], []
Thu Feb 28 19:29:13 2013
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 20989
ORA-1092 signalled during: ALTER DATABASE OPEN RESETLOGS…
undo异常恢复处理思路
除了极少数undo坏块,undo文件丢失外,大部分undo异常是因为redo未被正常进行前滚,从而导致undo回滚异常数据库无法open,解决此类问题,需要结合一般需要结合redo异常处理技巧在其中,一般undo异常处理思路
1.切换undo_management= MANUAL尝试启动数据库,如果不成功进入2
2.设置10513 等event尝试启动数据库,如果不成功进入3
3.使用_offline_rollback_segments/_corrupted_rollback_segments屏蔽回滚段
4.如果依然不能open数据库,考虑使用bbed工具提交事务,修改回滚段状态等操作
5.如果依然还不能open数据库,考虑使用dul
如果您按照上述步骤还不能解决,请联系我们,将为您提供专业数据库技术支持
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com
姊妹篇
ORACLE REDO各种异常恢复
ORACLE丢失各种文件导致数据库不能OPEN恢复
发表在 Oracle备份恢复
标签为 ORA-00376, ORA-00704, ORA-01595, ORA-600 4000, ORA-600 4097, ORA-600 4193, ORA-600 4194, ORA-6004137, oracle异常恢复, ORACLE恢复, undo异常
评论关闭
ORACLE丢失各种文件导致数据库不能OPEN恢复
在ORACLE的运行过程中,总会遇到这样那样的故障,本篇主要大概介绍关于因硬件,系统,误删除等各种原因导致数据库的部分文件丢失,这里列出来由于文件丢失而出现的常见错误和基本处理思路
1.丢失数据文件(ORA-01157)
SQL> startup
ORACLE instance started.
Total System Global Area 260046848 bytes
Fixed Size 1266896 bytes
Variable Size 83888944 bytes
Database Buffers 167772160 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/u01/oracle/oradata/XFF/users01.dbf’
数据文件丢失,处理方法:
1).使用备份还原丢失数据然后
2).非undo,system可以offline 掉该文件继续打开数据库
3).如果是undo需要谨慎,可能导致ORA-00376错误
4).如果是system offline可能导致ORA-01147
2. 丢失redo(ORA-00313)
SQL> startup
ORACLE instance started.
Total System Global Area 260046848 bytes
Fixed Size 1266896 bytes
Variable Size 83888944 bytes
Database Buffers 167772160 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘/u01/oracle/oradata/XFF/redo01.log’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
redo文件丢失,处理步骤:
1).查询v$log确认该redo是否是current或者active
2).确定该redo是否被归档
3).如果是inactive使用clear 或者 clear unarchived
4).如果是active或者current,需要通过不完全恢复,甚至隐含参数等方法解决
3. 丢失undo(ORA-01092 ORA-00376)
SQL> startup
ORACLE instance started.
Total System Global Area 260046848 bytes
Fixed Size 1266896 bytes
Variable Size 83888944 bytes
Database Buffers 167772160 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 – see DBWR trace file
ORA-01110: data file 2: ‘/u01/oracle/oradata/XFF/undotbs01.dbf’
SQL> alter database datafile 2 offline drop;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01092是前台错误,通过查询alert日志发现后台错误主要是:
Fri Oct 25 08:16:36 2013
Errors in file /u01/oracle/admin/XFF/bdump/xff_smon_7437.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: ‘/u01/oracle/oradata/XFF/undotbs01.dbf’
因为undo文件丢失,有事务无法正常回滚,从而出现该错误,需要通过使用隐含参数屏蔽事务来解决
4. 丢失system(ORA-01147)
SQL> startup
ORACLE instance started.
Total System Global Area 260046848 bytes
Fixed Size 1266896 bytes
Variable Size 83888944 bytes
Database Buffers 167772160 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 – see DBWR trace file
ORA-01110: data file 1: ‘/u01/oracle/oradata/XFF/system01.dbf’
SQL> alter database datafile 1 offline drop;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01147: SYSTEM tablespace file 1 is offline
ORA-01110: data file 1: ‘/u01/oracle/oradata/XFF/system01.dbf’
system表空间是系统表空间,该表空间中的数据文件不能被offline,如果该表空间数据文件丢失,数据库无法正常方法,可以考虑使用bbed模拟system文件欺骗数据库(非file# 1)或者使用dul抽取数据
5. 丢失控制文件(ORA-00205 ORA-00202)
SQL> startup
ORACLE instance started.
Total System Global Area 260046848 bytes
Fixed Size 1266896 bytes
Variable Size 83888944 bytes
Database Buffers 167772160 bytes
Redo Buffers 7118848 bytes
ORA-00205: error in identifying control file, check alert log for more info
ORA-00205是前台错误,具体需要结合日志分析:
Fri Oct 25 08:35:40 2013
ALTER DATABASE MOUNT
Fri Oct 25 08:35:40 2013
ORA-00202: control file: ‘/u01/oracle/oradata/XFF/control01.ctl’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
这里可以看出来,是因为控制问文件丢失该值该错误,处理办法:
1).使用备份控制文件还原
2).查找是否还有其他控制文件,拷贝一份
3).列举数据文件重建控制文件
如果你在使用这些思路进行恢复遇到突发情况不能自行解决,请联系我们,将为您提供专业数据库技术支持
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com
姊妹篇
undo异常总结和恢复思路
ORACLE REDO各种异常恢复