标签归档：ORA-01157

Oracle 12c undo异常处理—root pdb undo异常

发表于 2016 年 7 月 18 日由惜分飞

在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异常处理

发表在 ORACLE 12C, Oracle备份恢复 | 标签为 ORA-00376, ORA-01157, undo异常, _corrupted_rollback_segments | 评论关闭

ORACLE丢失各种文件导致数据库不能OPEN恢复

发表于 2014 年 2 月 21 日由惜分飞

在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各种异常恢复

发表在 Oracle备份恢复 | 标签为 ORA-00202, ORA-00205, ORA-00313, ORA-00376, ORA-01092, ORA-01147, ORA-01157, Oracle 恢复, ORACLE恢复, ORACLE数据库恢复 | 评论关闭

17813235971

QQ 咨询

标签归档：ORA-01157

Oracle 12c undo异常处理—root pdb undo异常

ORACLE丢失各种文件导致数据库不能OPEN恢复

文章分类

最近发表

标签归档：ORA-01157

Oracle 12c undo异常处理—root pdb undo异常

ORACLE丢失各种文件导致数据库不能OPEN恢复

标签云

文章分类

最近发表