oracle 12c undo,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异常处理