Thursday, March 15, 2018

Oracle Application Express is INVALID and Remove Oracle Apex

We are trying to upgrade a database and noticed that “Oracle Application Express” component is “INVALID” .

SQL> select COMP_ID,VERSION,STATUS from dba_registry;
COMP_ID VERSION STATUS
----------------------------------- --------------- ------------------------------
APEX 3.2.1.00.12 INVALID
CATALOG 11.2.0.4.0 VALID
CATPROC 11.2.0.4.0 VALID

SQL> Select COMP_NAME,VERSION,STATUS from dba_registry where COMP_ID=’APEX’;
COMP_NAME STATUS VERSION
----------------------------------- --------------- ------------------------------
Oracle Application Express INVALID 3.2.1.00.10

To avoid issues during the upgrade we decided to remove the Oracle Apex as it is not in use. We have followed below steps to remove the Oracle Application Express.

$ sqlplus / as sysdba
SQL> @$ORACLE_HOME/apex/apxremov.sql


After removing Application Express started the upgrade but received warning that “Oracle Server” component is INVALID.

SQL> select COMP_ID,VERSION,STATUS from dba_registry;
COMP_ID VERSION STATUS
----------------------------------- --------------- ------------------------------
CATALOG 11.2.0.4.0 INVALID
CATPROC 11.2.0.4.0 INVALID

We noticed that object SYS.HTMLDB_SYSTEM is shown as invalid after removing the Oracle APEX. After investigation I have noticed that apexremov.sql script removes APEX, but leaves the HTMLDB_SYSTEM package and synonym.

I ran below command to drop the invalid packages
SQL> drop package htmldb_system;
SQL> drop public synonym htmldb_system;

Now I don’t see any more INVALID objects but I still see that CATALOG,CATPROC are still INVALID.

SQL> select object_name, object_type from dba_objects
2 where owner = 'SYS' and status = 'INVALID';
no rows selected

SQL> select COMP_ID,VERSION,STATUS from dba_registry;
COMP_ID VERSION STATUS
----------------------------------- --------------- ------------------------------
CATALOG 11.2.0.4.0 INVALID
CATPROC 11.2.0.4.0 INVALID

I ran below script to validate CATPROC and CATALOG. Note that run the same CATALOG you need to change the two entries in the script. (i.e. replace CATPROC with CATALOG before running).

sqlplus / as sysdba
set serveroutput on;
declare
start_time date;
end_time date;
object_name varchar(100);
object_id char(10);
begin
SELECT date_loading, date_loaded into start_time, end_time FROM registry$
WHERE cid = 'CATPROC';
SELECT obj#,name into object_id,object_name
FROM obj$
WHERE status > 1 AND
(ctime BETWEEN start_time AND end_time OR
mtime BETWEEN start_time AND end_time OR
stime BETWEEN start_time AND end_time) AND
ROWNUM <=1;
dbms_output.put_line('Please compile Invalid object '||object_name||'
Object_id '||object_id );
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('CATPROC can be validated now' );
end;
/

Ran utlrp.sql script and now the CATALOG and CATPROC showing as VALID.

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> select COMP_ID,VERSION,STATUS from dba_registry;
COMP_ID VERSION STATUS
----------------------------------- --------------- ------------------------------
CATALOG 11.2.0.4.0 VALID
CATPROC 11.2.0.4.0 VALID

We are able to upgrade the database successfully.

Thanks,
Satishbabu Gunukula, Oracle ACE

8 comments:

  1. It is great that you describe such things.
    Holly

    ReplyDelete
  2. The article you shared here is great. This is really interesting information for me. Thank you for sharing! MICROSOFT OFFICE 2013 PRODUCT KEY

    ReplyDelete

  3. A very well-written post. I read and liked the post and have also bookmarked you. We are top CRM Software in Pune.

    ReplyDelete
  4. Thanks a lot for sharing a great blog I was just browsing through the internet looking for some information and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject. Bookmarked this page, will come back for more keep going on it helped me a lot I have gained a lot of knowledge by reading your blog.

    Oracle Apex Training
    apex oracle training
    oracle apex training online
    oracle apex courses online
    Oracle APEX 19.1 Training
    oracle apex 18 training
    oracle apex 5 training

    ReplyDelete
  5. Kerala SSLC Question Paper 2023 are an Important Tool for Secondary and Learning. It Gauges the Understanding of Kerala Students and enables effective Learning. Furthermore, 10th Class Students should Prioritize Practicing Kerala SSLC important Questions 2023 as Kerala Pareeksha Bhavan Exam are Very Important Student Career. Kerala SSLC Question Paper 2023 Students Regular Practice of Kerala SSLC Previous Paper 2023 form of PDF Format for Physics, Mathematics, English, Chemistry, Hindi Students can Download Those Uploaded Kerala SSLC Solved Question Paper 2023 Design to Check out the Complete Exam Pattern & Marking Scheme etc.

    ReplyDelete