Ò»¡¢Ê¼þ¸ÅÊö Êý¾Ý¿â°æ±¾£ºOracle 11gR2 ½üÈÕÔÚÏîÄ¿ÖÐÀ©ÈÝundo±í¿Õ¼äʱ£¬Oracle racÊý¾Ý¿âµÄundo±í¿Õ¼ä²»×㣬ÔÚÔö¼Ódatafileʱд´íÁËÎļþ´óС£¬ÓÚÊÇɾ³ýÐÂÔöµÄdatafileÎļþ£¬½á¹ûɾ³ý³ö´í£¬²»Äָܻ´£¬µ¼ÖÂн¨µÄundo²»ÄÜʹÓᣠ¶þ¡¢À©ÈÝundo,Ôö¼Ódatafile£º Uodotbs01£¨ÊµÀý1ʹÓÃ)ºÍundotbs02£¨ÊµÀý2ʹÓÃ)¾ùΪ30G£¬¼Æ»®Ôö¼ÓÒ»¸ö30GµÄdatafileÎļþ£¬undotbs01µÄÐÂdatafile´óСд´íÁË¡£ SQL>alter tablespace undotbs01 add datafie¡¯+DATA/hfms2db/datafile/undotbs1_01.dbf¡¯ size 3000m autoextend off; SQL>alter tablespace undotbs02 add datafie¡¯+DATA/hfms2db/datafile/undotbs2_01.dbf¡¯ size 30g autoextend off; --undotbs1_01.dbf´óСд³ÉÁË3000m,д´íÁË£¬ÓÚÊÇÖ´ÐÐÁËÈçϲÙ×÷ Alter database datafile¡®+DATA/hfms2db/datafile/undotbs1_01.dbf¡¯offline drop; ºó¾­²é×ÊÁÏÈ·ÈÏ£¬´Ë²Ù×÷ÊÇ´íÎóµÄ£º 1¡¢Ôڹ鵵ģʽÏÂϵͳ»òºöÂÔdrop£¬ÊÔÊÔ½«datafie offline£¬¶ø²»»ádrop; 2¡¢ÕýÈ·µÄÃüÁîÓ¦¸ÃÊÇ Alter tablespace undotbs1 drop datafile¡®+DATA/hfms2db/datafile/undotbs1_01.dbf¡¯; 3¡¢É¾³ýÖ®ºó²éѯdatafileÏÔʾÈçÏ SQL>select FILE#,name,bytes/1024/1024 BYTES,status from v$datafile; 3 +DATA/hfms2db/datafile/undotbs1.261.871389013 30000 online 13 +DATA/hfms2db/datafile/undotbs2.272.871390663 30000 online 142 +DATA/hfms2db/datafile/undotbs1_01.dbf 3000 recover 143 +DATA/hfms2db/datafile/undotbs2_01.dbf 30720 online ¿ÉÒÔ¿´µ½Êµ¼Êdatafile²¢Ã»ÓÐÕæÕýµÄɾ³ý¡£ Èý¡¢É¾³ýÐÂÔöµÄdatafileʧ°Ü ʹÓÃÕýÈ·µÄÃüÁîɾ³ýÊý¾ÝÎļþundotbs1_01.dbf SQL>Alter tablespace undotbs1 drop datafile¡®+DATA/hfms2db/datafile/undotbs1_01.dbf¡¯; ±¨´íÈçÏ£º ERROR at line 1 ORA-03264 cannot drop offline datafile of locally managed tablespace ½«undotbs1_01.dbfÖÃΪonline±¨´íÈçÏ£º SQL>Alter database datafile 142 online£» ERROR at line 1 ORA-01113£ºfile142 needs media recovery ORA-01110:£ºdata file 142: '+DATA/hfms2db/datafile/undotbs1_01.dbf¡¯ Ö»ÄÜ°´ÕÕÌáʾrecover undotbs1_01.dbf SQL>recover datafile 142 ORA-00279:change 981220327 generated at 12/12/2020 19:04:07 needed for thread 1 ORA-00289:suggestion:+ARCH ORA-00280:change 981220327 for thread 1 is in sequence #62066 ORA-00278: log file '+ARCH' no longer needed for this recovery ORA-00308: cannot open archived log +ARCH' ORA-17503: ksfdopn:2 Failed to open file +ARCH ORA-15045: ASM file name '+ARCH' is not in reference form ¾­ÍøÉϲéѯ˵ÕâÊÇÓÉÓÚÕÒ²»µ½¹éµµÈÕÖ¾µ¼Öµģ¬¹éµµÈÕ־ûÓмǼÉÏdropµÄ²Ù×÷¡£ÉèÖÃ×Ô¶¯¹éµµÈÕÖ¾ÒÀ¾É±¨´í£¬ÈçÏ£º SQL>set autorecovery on; SQL>recover automatic datafile 142; ORA-00279:change 981220327 generated at 12/12/2020 19:10:07 needed for thread 1 ORA-00289:suggestion:+ARCH ORA-00280:change 981220327 for thread 1 is in sequence #62066 ORA-00278: log file '+ARCH' no longer needed for this recovery ORA-00308: cannot open archived log +ARCH' ORA-17503: ksfdopn:2 Failed to open file +ARCH ORA-15045: ASM file name '+ARCH' is not in reference form ÖÁ´ËʵÔÚÕÒ²»µ½¸Ò³¢ÊԵķ½·¨£¬Ò²²»¸ÒäĿÖØÆôÊý¾Ý¿â£¬Ö»ÄÜÖؽ¨undo ËÄ¡¢Öؽ¨undo±í¿Õ¼ä ΪʵÀý1´´½¨Ò»¸öеÄundo:undotbs3 SQL>create undo tablespace undotbs3 datafile ¡®+DATA/hfms2db/datafile/undotbs3_01.dbf¡¯size 25g autoextend off; Çл»undo SQL>alter system set undo tablespace=undotbs3 scope=both sid=¡¯hfms2db1¡¯; ×¢Ò⣬һ¶¨ÒªÖ¸¶¨sid=¡¯hfms2db1¡¯²»È»ÖØÆôÊý¾Ý¿â»¹»á»Øµ½undotbs1 ²é¿´ÐÞ¸ÄÊÇ·ñ³É¹¦£º SQL>show parameter undo; È·ÈÏundotbs1ÉÏÊÇ·ñËùÓеĻعö¶Î¶¼½áÊøÁË SQL>select segment_name,status,tablespace_name from dba_rollback_segs where (tablespace_name=¡¯undotbs1¡¯or tablespace_name=¡¯undotbs3¡¯)and status=¡¯online¡¯; Î塢ɾ³ýundotbs1ʧ°Ü ²»¹ÜÊÇ·ñ»¹´æÔڵĻعö¶Î£¬É¾³ýundotbs1,±¨´í SQL>drop tablespace undotbs1 including contents and datafiles; ERROR at line 1 ORA-30013:undo tablespace 'UNDOTBS1' is currently in use ½«undotbs1ĬÈϵÄdatafile offline. SQL>Alter database datafile¡®+DATA/hfms2db/datafile/undotbs1.261.871389013¡¯ offline£» ÔÙ´Îɾ³ýundotbs1±¨´íÒÀ¾É£¬Ä¬ÈϵÄdatafileÒ²±ä³ÉÁËrecover,Ö´ÐÐrecover±¨Í¬ÑùµÄ´í SQL>recover datafile 3; Áù¡¢ÖØÆôÊý¾Ý¿âºóɾ³ýundotbs1Ò²ÈÔȻʧ°Ü ʹÓÃÁ½ÖÖÖØÆôÊý¾Ý¿â·½·¨ Oracle$srvctl stop database -d hfms2db Oracle$srvctl start database -d hfms2db Ò²³¢ÊÔÁË shutdown immediate ºÍ startupµÄ·½Ê½ÖØÆôÊý¾Ý¿âºó£¬Ö´ÐÐɾ³ý±¨´íÈçÏ£º SQL>drop tablespace undotbs1 including contents and datafiles; ERROR at line 1 ORA-01548:active rollback segment '_SYSSMU6$' found, terminate dropping tablespace Êý¾Ý¿âÒ²±ä³ÉÁËrecoverģʽ Æß¡¢¶ÀռģʽÆô¶¯Êý¾Ý¿â½â¾öÎÊÌâ ÔÚracģʽϲÅÓöÀռģʽÆô¶¯Êý¾Ý¿â Ê×ÏÈ´´½¨ÓÃÓÚÆô¶¯Êý¾Ý¿âµÄpfileÎļþasm»·¾³Öв»ÄÜÖ±½Ócp pfile.ÒªÖ´ÐÐÈçÏÂÃüÁîÉú³É SQL>create pfile=¡¯/tmp/pfile.ora¡¯from spfile; ÐÞ¸Ä/tmp/pfile.ora£¬¼ÓÈëÒÔÏÂÄÚÈÝ -corrupted_rollback_segments=(_SYSSMU33_529594311$) ¹Ø±Õ¼¯Èº£¬root²Ù×÷ #./crsctl stop has -f ÔÚÒ»¸ö½ÚµãÉÏÆô¶¯¶Àռģʽ #./crsctl start crs -excl -nocrs ×¢ÒâÈç¹ûÊý¾Ý¿âË漯ȺÆô¶¯ÔòÒªÊÖ¶¯¹Ø±ÕÊý¾Ý¿â£¬²ÉÓÃÈçÏ·½Ê½Æô¶¯£º SQL>startup pfile=¡®/tmp/pfile.ora¡¯ Ö±½Óɾ³ýundotbs1 SQL>drop tablespace undotbs1 including contents and datafiles; ɾ³ý³É¹¦ ʹÓÃgridÔÚasmcmdÖв鿴datafileÊÇ·ñ´æÔÚ Asmcmd>pwd +DATA/hfms2db/datafile Asmcmd>ls ÒѾ­Ã»ÓÐÁËundotbs1µÄdatafile ¹Ø±Õ¼¯Èº£¬Õý³£Æô¶¯¼¯ÈººÍÊý¾Ý¿â£¬²é¿´undo£¬hfms2db1ÒѾ­Õý³£Ê¹ÓÃundotbs3. ÖÁ´Ë½â¾öÍê±Ï¡£