Oracle 12c undo exception handling-root pdb undo exception solution

In the 12c pdb environment, if the root pdb's undo file is abnormal, how can the database be restored? This article simulates recovery when undo is lost.
Simulated Environment

Three sessions. The first session operates on the tables in pdb1, and some transactions are not committed. The second session operates on pdb2, and no transactions are committed; the third session directly abort the database, simulate a sudden library exception, and then delete the undo file under root pdb
-- Session 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,201 6, 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
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
 
 
-- Session 2

[Oracle @ ora1221 ~] $ Ss
 
SQL * Plus: Release 12.2.0.0.3 Production on Thu Jun 16 22:34:01 2016
 
Copyright (c) 1982,201 6, Oracle. All rights reserved.
 
 
Connected:
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
 
-- Session 3
[Oracle @ ora1221 ~] $ Ss
 
SQL * Plus: Release 12.2.0.0.3 Production on Thu Jun 16 22:36:16 2016
 
Copyright (c) 1982,201 6, Oracle. All rights reserved.
 
 
Connected:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3-64bit Production
 
SQL> shutdown abort
ORACLE instance shut down.
 
-- Delete the cdb undo file
[Oracle @ ora1221 orcl12c2] $ ls-ltr
Total 2040912
Drwxr-x ---. 2 oracle oinstall 4096 Jun 16 pdbseed
Drwxr-x ---. 2 oracle oinstall 4096 Jun 16 pdb2
Drwxr-x ---. 2 oracle oinstall 4096 Jun 16 pdb1
-Rw-r -----. 1 oracle oinstall 209715712 Jun 16 redo03.log
-Rw-r -----. 1 oracle oinstall 5251072 Jun 16 users01.dbf
-Rw-r -----. 1 oracle oinstall 34611200 Jun 16 temp01.dbf
-Rw-r -----. 1 oracle oinstall 849354752 Jun 16 system01.dbf
-Rw-r -----. 1 oracle oinstall 73408512 Jun 16 undotbs01.dbf
-Rw-r -----. 1 oracle oinstall 492838912 Jun 16 sysaux01.dbf
-Rw-r -----. 1 oracle oinstall 209715712 Jun 16 redo02.log
-Rw-r -----. 1 oracle oinstall 209715712 Jun 16 redo01.log
-Rw-r -----. 1 oracle oinstall 18726912 Jun 16 control02.ctl
-Rw-r -----. 1 oracle oinstall 18726912 Jun 16 control01.ctl
[Oracle @ ora1221 orcl12c2] $ rm undotbs01.dbf
[Oracle @ ora1221 orcl12c2] $ ls-l un *
Ls: cannot access un *: No such file or directory

START database

Because of undo File loss database detected file loss at startup (ORA-01157), unable to open, offline file still cannot start (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,201 6, 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 data file
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
The Session ID is 16 Serial number: 56234.
After undo_management is changed to manual, the database is started and the ORA-00376 is still reported.
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
The Session ID is 16 Serial number: 56572.
Set the _ uploupted_rollback_segments parameter.
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 _ uploupted_rollback_segments;
 
NAME TYPE
----------------------------------------------------------
VALUE
------------------------------
_ Uploupted_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.
After the _ uploupted_rollback_segments parameter is set, the database starts normally.

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
At this point, the database is basically restored, but the data in the two Test tables in pdb is slightly different from the data we predicted. It seems that the undo in cdb and the undo in pdb are still dependent on each other. it also shows that the root undo exception has little impact on the recovery of other pdb open. next test undo exception handling in business pdb

Contact: mobile phone (13429648788) QQ (107644445)
Original article: http://www.xifenfei.com/2016/07/oracle-12c-undo-failure-recovery-root-pdb.html