6 Replies Latest reply on Nov 13, 2013 9:55 PM by BillW

    Application Express Appears To Cause RDBMS Restart




      Each morning my Oracle Enterprise Manager Repository Database is RESTARTED cleanly at approximately the same time. I looked at the dbms_jobs and I see an application express job running just a few seconds before the RDBMS restart -


      SQL> select * from dba_jobs;

             JOB LOG_USER                       PRIV_USER
      ---------- ------------------------------ ------------------------------
      SCHEMA_USER                    LAST_DATE LAST_SEC
      ------------------------------ --------- --------------------------------
      THIS_DATE THIS_SEC                         NEXT_DATE
      --------- -------------------------------- ---------
      NEXT_SEC                         TOTAL_TIME B
      -------------------------------- ---------- -
      MISC_ENV                                                           INSTANCE
      ---------------------------------------------------------------- ----------
            4001 SYS                            APEX_030200
      APEX_030200                    12-NOV-13 06:00:16
      14:00:16                                190 N
      sysdate + 8/24
      wwv_flow_cache.purge_sessions(p_purge_sess_older_then_hrs => 24);
      0102000202000000                                                          0


      Note the job is scheduled for 06:00:16. The database shutdown command (as found in the alert log) was issued at 06:00:34. This timing has been very consistent. This APEX just is schedule just seconds before the database shutdown occurs.


      Can someone explain why this APEX job would be restarting my database?


      Additionally, I have not found much helpful in the way of APEX documentation. Specifically I am looking for information re the APEX procedure being executed, wwv_flow_cache.purge_sessions and what is expected behavior.


      Any suggestions greatly appreciated, thank you.

      Bill Wagman

        • 1. Re: Application Express Appears To Cause RDBMS Restart
          Mike Kutz

          wwv_flow_cache.purge_sessions is NOT your problem.

          Oracle is designed to stay up 24+/7/365+


          Your system is acting like someone has a COLD BACKUP script running at 0600 every day.

          Check the OS for cron jobs. (or the windows equivalent.)


          Speaking of windows, (if it is windows) is Automatic Updates turned on?

          last thing you want is the system to reboot in the middle of the day.



          • 2. Re: Application Express Appears To Cause RDBMS Restart



            I am not running on Windows, the OS is RHEL x86_64 running on a VM.


            There are no cronjobs running at that time, the backups are scheduled to run at 10:00PM nightly, the restarts are approximately at 6:00am daily. In poking around a bit more I have found the following things and I suspect they are involved but not being at all familiar with Application Express I am not sure how to proceed.


            The APEX version is and according to DBA_REGISTRY the status is INVALID. I find an APEX_030200 user and an APEX_PUBLIC_USER both of which are expired and locked and I find that the apex_030200 package and body, WWV_FLOW_HELP are invalid. Also, according to DBA_REGISTRY apex has a status of invalid.


            As I mentioned I believe the Application Express may have been done as part of the Enterprise Manager configuration as I did not do anything to install, configure or start it.


            Be that as it may, I know nothing about Application Express and so am not sure how to proceed and haven't been able to find documentation that has been helpful so if you can point me at some I'd be most appreciative.


            Thank you.

            Bill Wagman

            • 3. Re: Application Express Appears To Cause RDBMS Restart
              Mike Kutz

              I'll do this in two posts to separate the information.


              Explanation of APEX

              Let's say that APEX is configured to use the Listener.

              With this setup, APEX (as a whole) is a 3-tier infrastructure.

              Browser is the client.  The middle tier is a Web Server (which runs the Listener).  And the database.


              APEX itself is not an application, but a set of utilities/APIs used for generating web-pages based on 'meta-data'.

              All of these APIs are in the APEX_{version} schema.


              The 'meta-data' describes a collection of pages, what HTML <input> tags should be used, layout, etc., etc, etc.

              The "APEX Developer" and "APEX Administration" web pages happen to be APEX applications themselves.

              Again, the APEX APIs takes that meta data, runs the appropriate SQL statements (DBMS_SQL), and produces web pages. (I believe via SYS.HTP package).


              This 'meta-data' is stored in a Workspace.  From a DBA point of view:  Workspace === Schema


              Any good security conscious application developer will develop an application such that the end-users will have access to the 'meta-data' but not run SQL statements as the owner of the 'meta-data'.  This prevents a user from accidentally (or a hacker purposefully) from DELETE-ing your application.

              (This means that the APEX APIs have a lot of AUTHID CURRENT_USER.)

              This non-workspace-schema user is called the 'Parsing Schema'; it is the schema that 'parses' the SQL statements.


              You can have multiple applications in a workspace (schema).

              And each application can have a different parsing schema.


              The DB connection from the middle tier to the database is always connected and uses a Proxy User account.

              The Proxy User (APEX_PUBLIC_USER for Listener/ANONYMOUS for Embedded Gateway) has nothing more than the CREATE SESSION privilege.

              Before each web page is generated, the APEX does a 'su' to the Parsing Schema (and sets up a lot of NLS_* settings for the session)


              If you have any questions, feel free to post up in a different thread.


              (more info in a 2nd post)



              • 4. Re: Application Express Appears To Cause RDBMS Restart
                Mike Kutz

                APEX_030200 being 'locked'

                good.  nobody should be touching it as it holds all of the APEX APIs, etc.


                APEX_PUBLIC_USER being 'locked'

                If you do not run any APEX applications, good.  You shouldn't have open accounts that you don't use.

                If you run APEX applications, you would have known about this much earlier.

                PS - You should look at the status of ANONYMOUS.


                WWV_FLOW_HELP being 'invalid', along with DB_REGISTRY saying APEX is 'INVALID'

                This should not affect normal operations of the database.


                However, since your restart seems to correlate with one of the APEX's self-management jobs:

                If the job is causing a DB restart, it should be throwing an ORA-600 or something similar.


                You should dig deeper into some of the database's dump files to see if this is the case.

                But, the easiest thing to do would be to disable that particular job.


                Again, APEX is specifically for producing and processing HTML and has nothing to do with normal DB operations.

                It should not be 'restarting' the database.


                OS THINGS TO CHECK

                Double check and make sure 'root' doesn't have any cron jobs.. (and the owner of the database)

                Run 'uptime' to see if the OS is being rebooted.

                Check the VM logs for that VM Guest to see if something is going on. (eg a move to a different VM Host)


                Also, read and re-read the Oracle Licensing documents.

                AFAIK - All of the machines in a VM Cluster needs to be licensed.


                DB Things to Check

                If you are using an RMAN repository, look at all of the RMAN scripts.

                Again, look for dump files.

                If you have to, change the number of JOB processes to 0 for a night or two.  (ie totally disable DBMS_JOB/DBMS_SCHEDULER)


                If it is still being restarted, and there is nothing in the dump files, than you have something running a 'cron' job.

                --> change all OS passwords.  change all SYS, etc. passwords.  change all passwords for schemas with DBA role.



                • 5. Re: Application Express Appears To Cause RDBMS Restart



                  Thank you for the explanation, it is very helpful. To answer your questions -


                  I had opened an SR with Oracle asking for help with this problem and in addition to the default tracing they had me turn on 10046 & 10400 tracing for a 20 minute period around the time of the restart. I have looked through all the trace files for ORA-600 errors and also searched for wwv_flow_cache & purge_sessions and found no occurences of either.


                  Neither root or the oracle user have cron jobs scheduled to run at that time.


                  The server itself has not been rebooted for 26 days to date.


                  I have spoken to the system admin and the VM admins and they report they find nothing which is running at those times. I have looked at network connectivity at those times and there is nothing going on there either.


                  I am not using an RMAN catalog.


                  This database is the repository for my Enterprise Manager Cloud Control 12c and I believe (although must verify) that in order for it to run JOB processes must be > 0.


                  I have been told that APEX is not required for the OEM so I think my next step will be to remove it and see if the problem continues. The next step would then be to change passwords as you suspect.


                  Thanks to for the note about licensing on VM. We have been through the wringer with Oracle over that one and my understanding is our licensing is correct. Again, thank you for your help and suggestions.

                  Bill Wagman

                  • 6. Re: Application Express Appears To Cause RDBMS Restart

                    I removed Application Express. I will see if the problem resolves.


                    Thank you.

                    Bill Wagman