12c 模拟cdb的undo文件丢失

来源：互联网发布：流量统计软件编辑：程序博客网时间：2024/06/05 13:50

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

查看数据库状态

SQL> show user USER is "SYS"SQL> show con_nameCON_NAME------------------------------CDB$ROOTSQL> 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.dbf11 rows selected.SQL> select tablespace_name from dba_tablespaces;TABLESPACE_NAME------------------------------SYSTEMSYSAUXUNDOTBS1TEMPUSERS

删除undo（这里mv一下）

SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.[oracle@fish dave]$ lscontrol01.ctl  davepdb  redo01.log  redo03.log    system01.dbf  undotbs01.dbfcontrol02.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> startupORACLE instance started.Total System Global Area  968884224 bytesFixed Size                  8799800 bytesVariable Size             666896840 bytesDatabase Buffers          289406976 bytesRedo Buffers                3780608 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 4 - see DBWR trace fileORA-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 errorORA-01092: ORACLE instance terminated. Disconnection forcedORA-00604: error occurred at recursive SQL level 1ORA-00376: file 4 cannot be read at this timeORA-01110: data file 4: '/oradata/dave/undotbs01.dbf'Process ID: 2140Session 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 bytesFixed Size                  8799800 bytesVariable Size             666896840 bytesDatabase Buffers          289406976 bytesRedo Buffers                3780608 bytesDatabase mounted.ORA-00603: ORACLE server session terminated by fatal errorORA-01092: ORACLE instance terminated. Disconnection forcedORA-00604: error occurred at recursive SQL level 1ORA-00376: file 4 cannot be read at this timeORA-01110: data file 4: '/oradata/dave/undotbs01.dbf'Process ID: 2877Session 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的数据库产生影响。

0 0