12c 模拟cdb的undo文件丢失

12c的数据库cdb和pdb分别对应不同的undo，今天模拟下cdb下undo数据文件丢失

查看数据库状态

SQL> show user 
USER is "SYS"
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/dave/system01.dbf
/oradata/dave/sysaux01.dbf
/oradata/dave/undotbs01.dbf
/oradata/dave/pdbseed/system01.dbf
/oradata/dave/pdbseed/sysaux01.dbf
/oradata/dave/users01.dbf
/oradata/dave/pdbseed/undotbs01.dbf
/oradata/dave/davepdb/system01.dbf
/oradata/dave/davepdb/sysaux01.dbf
/oradata/dave/davepdb/undotbs01.dbf
/oradata/dave/davepdb/users01.dbf

11 rows selected.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

删除undo（这里mv一下）

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@fish dave]$ ls
control01.ctl  davepdb  redo01.log  redo03.log    system01.dbf  undotbs01.dbf
control02.ctl  pdbseed  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf
[oracle@fish dave]$ mv undotbs01.dbf undotbs01.dbf.bak

[oracle@fish dave]$ ls undo*
undotbs01.dbf.bak

启动数据库报错

SQL> startup
ORACLE instance started.

Total System Global Area  968884224 bytes
Fixed Size                  8799800 bytes
Variable Size             666896840 bytes
Database Buffers          289406976 bytes
Redo Buffers                3780608 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/oradata/dave/undotbs01.dbf'

将undo数据文件offline

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: '/oradata/dave/undotbs01.dbf'
Process ID: 2140
Session ID: 32 Serial number: 25233

报错直接退出了

SQL> conn / as sysdba      
Connected to an idle instance.
SQL> create pfile='/home/oracle/pfile.ora' from spfile;

File created.

修改undo management参数为manual （原来为auto）

undo_management=MANUAL

SQL> startup pfile='/home/oracle/pfile.ora';
ORACLE instance started.


Total System Global Area  968884224 bytes
Fixed Size                  8799800 bytes
Variable Size             666896840 bytes
Database Buffers          289406976 bytes
Redo Buffers                3780608 bytes
Database mounted.
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: '/oradata/dave/undotbs01.dbf'
Process ID: 2877
Session ID: 32 Serial number: 61838

还是报错，还是ora-1110错误，那就是有回滚段需要恢复。使用strings命令查看system表空间数据文件,并且查找字符串"_SYSSMU"就可以得到当前数据库的回滚段列表,然后加入到参数_corrupted_rollback_segments中,再通过指定pfile的方式启动数据库:

[oracle@fish dave]$ strings system01.dbf |grep "_SYSSMU">undo.txt

[oracle@fish dave]$ vi undo.txt
_SYSSMU16_2944207774$
_SYSSMU14_2720857769$
_SYSSMU12_3908674253$
_SYSSMU17_2601259880$
_SYSSMU15_2718605803$
_SYSSMU13_131873868$
_SYSSMU18_670144144$
_SYSSMU19_3732264305$
_SYSSMU9_3454315980$
_SYSSMU8_1882146965$
_SYSSMU7_416615031$
_SYSSMU6_2941669244$
_SYSSMU5_388830241$
_SYSSMU4_2691291427$
_SYSSMU3_2964921246$
_SYSSMU2_2032512800$
_SYSSMU1_3286679881$
_SYSSMU20_3614795100$
_SYSSMU20_3614795100$
_SYSSMU19_3732264305$
_SYSSMU19_3732264305$
_SYSSMU18_670144144$
_SYSSMU18_670144144$
"undo.txt" 689L, 14601C                                       1,1           Top
_SYSSMU14_2720857769$
_SYSSMU13_131873868$
_SYSSMU12_3908674253$
_SYSSMU11_4062767693$
_SYSSMU10_619310961$
_SYSSMU9_3454315980$
_SYSSMU8_1882146965$
_SYSSMU7_416615031$
_SYSSMU6_2941669244$
_SYSSMU5_388830241$
_SYSSMU4_2691291427$
_SYSSMU3_2964921246$
_SYSSMU2_2032512800$
_SYSSMU1_3286679881$
_SYSSMU9_3240272268$
_SYSSMU8_4232757972$
 and substr(drs.segment_name,1,7) != '_SYSSMU'
 and substr(drs.segment_name,1,7) != '_SYSSMU'
;    ' and substr(drs.segment_name,1,7) != ''_SYSSMU'' ' ||
        O9    ' and substr(drs.segment_name,1,7) != ''_SYSSMU''');
_SYSSMU10_619310961$
_SYSSMU9_3454315980$
_SYSSMU9_3454315980$

......篇幅太多，省略。

将undo.txt的内容整理，去掉非_SYSSMUS开头的整理成如下格式

*._corrupted_rollback_segments=(_SYSSMU16_2944207774$,
_SYSSMU14_2720857769$,
_SYSSMU12_3908674253$,
_SYSSMU17_2601259880$,
_SYSSMU15_2718605803$,
_SYSSMU13_131873868$,
_SYSSMU18_670144144$,
_SYSSMU19_3732264305$,
_SYSSMU9_3454315980$,
_SYSSMU8_1882146965$,
_SYSSMU7_416615031$,
_SYSSMU6_2941669244$,
_SYSSMU5_388830241$,
_SYSSMU4_2691291427$,
_SYSSMU3_2964921246$,
_SYSSMU2_2032512800$,
_SYSSMU1_3286679881$,
_SYSSMU20_3614795100$,
_SYSSMU20_3614795100$,
_SYSSMU19_3732264305$,
_SYSSMU19_3732264305$,
_SYSSMU18_670144144$,
_SYSSMU18_670144144$,
_SYSSMU14_2720857769$,
_SYSSMU13_131873868$,
_SYSSMU12_3908674253$,
_SYSSMU11_4062767693$,
_SYSSMU10_619310961$,
_SYSSMU9_3454315980$,
_SYSSMU8_1882146965$,
_SYSSMU7_416615031$,
_SYSSMU6_2941669244$,
_SYSSMU5_388830241$,
_SYSSMU4_2691291427$,
_SYSSMU3_2964921246$,
_SYSSMU2_2032512800$,
_SYSSMU1_3286679881$,
_SYSSMU9_3240272268$,
_SYSSMU8_4232757972$,
_SYSSMU10_619310961$,
_SYSSMU9_3454315980$,
_SYSSMU9_3454315980$,
_SYSSMU8_1882146965$,
_SYSSMU7_416615031$,
_SYSSMU6_2941669244$,
_SYSSMU5_388830241$,
_SYSSMU4_2691291427$,
_SYSSMU3_2964921246$,
_SYSSMU2_2032512800$,
_SYSSMU1_3286679881$,
_SYSSMU20_3614795100$,
_SYSSMU19_3732264305$,
_SYSSMU18_670144144$,
_SYSSMU17_2601259880$,
_SYSSMU16_2944207774$,
_SYSSMU15_2718605803$,
_SYSSMU14_2720857769$,
_SYSSMU13_131873868$,
_SYSSMU12_3908674253$,
_SYSSMU11_4062767693$,
_SYSSMU10_619310961$,
_SYSSMU9_3454315980$,
_SYSSMU8_1882146965$,
_SYSSMU7_416615031$,
_SYSSMU6_2941669244$,
_SYSSMU5_388830241$,
_SYSSMU4_2691291427$,
_SYSSMU3_2964921246$,
_SYSSMU2_2032512800$,
_SYSSMU1_3286679881$,
_SYSSMU9_3240272268$,
_SYSSMU8_4232757972$,
_SYSSMU10_619310961$,
_SYSSMU9_3454315980$,
_SYSSMU9_3454315980$)

启动数据库，测试看看

SQL> alter database open;
 
Database altered.

切换pdb都ok 但是不知道生产环境cdb的undo丢失会不会对pdb的数据库产生影响。