5 Replies Latest reply: Mar 13, 2013 6:24 AM by Vite DBA RSS

    Restoring an Oracle Enterprise Edition DB into XE for use with APEX

    richard_cunningham
      Hello, we're just in the process of decomissioning a load of servers and I came across an old Oracle DB box which has an install of APEX (well, HTMLDB) and a backend DB on an Enterprise Edition server, after battling with Oracle for 2 days I finally managed to get it to a state where I think it's restored successfully onto XE but up on the final "startup" I was presented with:

      ORA-01092: ORACLE instance terminated. Disconnection forced
      ORA-39712: XE edition server cannot open database

      I really want\need to get this DB off of this server, because as you all probably know - Oracle DB EE isn't exactly cheap, and seen as though it's used for this historical app I kinda need to get it off of this hardware\software and into a low-end VM for it to chug away happily and be accessed when needs be...

      Another option I though of, if I were to do a reinstall of XE, and then data pump the info from the EE to XE? Would that work?

      I apologies for any numptiness in this question, but I really haven't got any experience with Oracle - and it's been a learning curve of much frustration coming from my easy to use MSSQL interfaces!

      Thanks,

      Rich.
        • 1. Re: Restoring an Oracle Enterprise Edition DB into XE for use with APEX
          jariola
          Hi,

          What steps you did take to restore database to XE?

          I think your question is not relate to APEX at all.

          Regards,
          Jari
          -----
          My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
          Twitter: http://www.twitter.com/jariolai
          • 2. Re: Restoring an Oracle Enterprise Edition DB into XE for use with APEX
            richard_cunningham
            Hi Jarola, I couldn't find an XE forum, and APEX came with XE - so I decided to post on here...

            But the steps I took were:

            1) Run nid to rename the DB from "XE" to "ESTALTST"
            a) Do some faffing about with tnsnames and listener.ora to get it to realise ESTALTST
            2) Take a backup of logs\db from source
            3) Catalog the backups
            4) Restore control file
            5) A lot of messing about getting the DB happy with the structures of things
            6) recover database;
            7) alter database resetlogs; <- this is where it said I had to do startup upgrade;
            8) shutdown immediate; startup upgrade;
            9) Where I am now.
            • 3. Re: Restoring an Oracle Enterprise Edition DB into XE for use with APEX
              fac586
              rich0dify wrote:
              Hi Jarola, I couldn't find an XE forum, and APEX came with XE - so I decided to post on here...
              The XE forum is +{forum:id=251}+
              • 4. Re: Restoring an Oracle Enterprise Edition DB into XE for use with APEX
                jariola
                rich0dify wrote:
                Hi Jarola, I couldn't find an XE forum, and APEX came with XE - so I decided to post on here...

                But the steps I took were:

                1) Run nid to rename the DB from "XE" to "ESTALTST"
                a) Do some faffing about with tnsnames and listener.ora to get it to realise ESTALTST
                2) Take a backup of logs\db from source
                3) Catalog the backups
                4) Restore control file
                5) A lot of messing about getting the DB happy with the structures of things
                6) recover database;
                7) alter database resetlogs; <- this is where it said I had to do startup upgrade;
                8) shutdown immediate; startup upgrade;
                9) Where I am now.
                Hi,

                Well, I think it might not work that way.

                Use exp command to export your application schemas from old DB and then imp command to import to XE.
                Then export workspaces and applications from old DB and import those to XE.

                Regards,
                Jari
                -----
                My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
                Twitter: http://www.twitter.com/jariolai
                • 5. Re: Restoring an Oracle Enterprise Edition DB into XE for use with APEX
                  Vite DBA
                  rich0dify wrote:
                  Hi Jarola, I couldn't find an XE forum, and APEX came with XE - so I decided to post on here...

                  But the steps I took were:

                  1) Run nid to rename the DB from "XE" to "ESTALTST"
                  a) Do some faffing about with tnsnames and listener.ora to get it to realise ESTALTST
                  2) Take a backup of logs\db from source
                  3) Catalog the backups
                  4) Restore control file
                  5) A lot of messing about getting the DB happy with the structures of things
                  6) recover database;
                  7) alter database resetlogs; <- this is where it said I had to do startup upgrade;
                  8) shutdown immediate; startup upgrade;
                  9) Where I am now.
                  Without knowing which versions you are going from and to, there are still any number of reasons that this won't work. The main being that you are going from a from a full featured cost version to a cut down free version. As advised by others, the best option is export/import of data on an application schema level and export/import of Apex objects.

                  Even then, there are things to be wary of.

                  First of all, XE, as a free version is limited in the amount of data it can hold, 11gb in XE 11.2. Secondly, there is a large number of features that aren't available, such as patitioning, which means any imports of partitioned objects will need to be handled individually. Another thing to consider is upgrading the version of Apex that comes with XE to the latest before you do any of the Apex imports. Also to be considered is how you are connecting to Apex, eg HTTP Server, EPG or Apex Listener.

                  Unfortunately, things are never as easy as they may at first seem, especially when you are using a real database, as opposed to a dinky toy ;-)

                  Regards
                  Andre