5 回复 由 Prog1024 于 2020-8-2 下午10:47 最新回复

    Cannot start Oracle-XE 18c due to being full

    4082131
    4082131 2020-3-3 上午12:17

      Cannot Start Oracle-XE 18c in order to clear out data

      ====================================================================================================================

      We are having an issue where an Oracle XE instance fills up and for a potentially unrelated reason crashes and cannot be restarted.

       

      What this means is I cannot get back into the instance to cleanout data to resume operation. No matter what path is taken, I continue to be prevented from opening the PDB.

       

      This basically then means I have to rebuild the thing from scratch which is loads of fun.

       

      SQL> ALTER PLUGGABLE DATABASE XEPDB1 OPEN READ ONLY;

      ALTER PLUGGABLE DATABASE XEPDB1 OPEN READ ONLY

      *

      ERROR at line 1:

      ORA-12954: The request exceeds the maximum allowed database size of 12 GB.

       

       

      SQL> ALTER PLUGGABLE DATABASE XEPDB1 OPEN RESTRICTED;

      ALTER PLUGGABLE DATABASE XEPDB1 OPEN RESTRICTED

      *

      ERROR at line 1:

      ORA-12954: The request exceeds the maximum allowed database size of 12 GB.

       

       

      SQL> ALTER PLUGGABLE DATABASE XEPDB1 OPEN READ ONLY FORCE;

      ALTER PLUGGABLE DATABASE XEPDB1 OPEN READ ONLY FORCE

      *

      ERROR at line 1:

      ORA-12954: The request exceeds the maximum allowed database size of 12 GB.

       

      Any suggestions on how I might get the data out or get the process started so I can move forward?

        • 1. Re: Cannot start Oracle-XE 18c due to being full
          Markus Flechtner
          Markus Flechtner 2020-3-3 上午7:53 （回复 4082131）

          Hi,

           

          I assume that your CDB$ROOT is open.

          So, did you try to resize your CDB$ROOT (by shrinking one more datafiles)?

          This could help you to come below the limit so that you can open your PDB.

           

          HTH

          Markus

          • 2. Re: Cannot start Oracle-XE 18c due to being full
            Dude!
            Dude! 2020-3-3 上午8:30 （回复 4082131）

            I think the first things you want to do is to check the actual alert.log, which should be located at:

             

            $ORACLE_BASE/diag/rdbms/<dbname_in_lower_case>/$ORACLE_SID/trace/

             

            Check for clues.

             

            Next, before you make any changes, I suggest to perform a full RMAN backup.

             

            Perhaps you can reclaim some space from the temp datafile, though I'm not sure if the space counts toward the 12GB limit. But perhaps worth a try:

             

            SQL> ALTER TABLESPACE TEMP SHRINK SPACE;

            • 3. Re: Cannot start Oracle-XE 18c due to being full
              Peter_L_
              Peter_L_ 2020-6-15 上午10:30 （回复 4082131）

              Hi,

              I run into the same. It's a very ugly error.

              My only suggestion is to install an Oracle Standard Edition Home and try to run/append the datafiles of XE from the new home, then shrink the tables and export/import back into XE.

              Delete the auxiliary dbhome.

              Cheers Peter

              • 4. Re: Cannot start Oracle-XE 18c due to being full
                Peter_L_
                Peter_L_ 2020-6-17 上午5:17 （回复 Markus Flechtner）

                Hi Markus,

                Same issue in my testlab.

                My XEPDB1 was unable to "open read write" or "open read only" (ora-12954). No chance to cleanup the user tablespace in the mounted XEPDB1. The only way was to append the XE datafiles in a "auxiliary" Oracle 18c home. There I could start this XE. Now I could see which objects filled up the user tablespace. In my case there was the user tablespace mostly filled by fulltext search indexes. The indexes were regular synchronized by a scheduler job with "ctx_ddl.sync_index". I assume during  this user maintenance jobs with ctx_ddl package the 12GB space limit will not correctly checked and an overfill will sadly allowed. I think it's a XE bug.

                However now I could drop the fulltext indexes. Export schema, recreate the tablespace with compress (this is allowed in XE) and import back.

                After appending datafiles back to XE at last I could start the XE instance without problems.

                 

                Cheers Peter

                • 5. Re: Cannot start Oracle-XE 18c due to being full
                  Prog1024
                  Prog1024 2020-8-2 下午10:47 （回复 Peter_L_）

                  I am having the same issue , but i am unable to understand the procedure for "append the XE datafiles in a "auxiliary" Oracle 18c home" i tried to take users tablespace dbf file to another system with a clean xe database install and added this dbf to previously present USERS tablespace in newly installed XE database but i cannot see any content from the tablespace

                   

                  Can you kindly provide some detail on how to perform this procedure , also is it needs to be imported into home database or can it work in a fresh xe install like i am doing?