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

Webinar: Oracle Database Upgrade to 12c and Available Methods

Oracle Database 12c has many exciting new features and in order to take advantage of these features you need to upgrade the databases from older versions to Oracle 12c.

In this webinar, I will explain best practices and availble methods for you to upgrade/migrate your databases to 12c.

Date and time: Mar 30th 2018 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)


To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com.

Note that registrations are limited, first come and first serve basis only. You will receive an email confirmation with meeting session link.

For Presentation link "Click here". Don’t miss the chance…!

Thanks,
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

Thursday, March 8, 2018

ORA-31613: Master process DM00 failed during startup

While taking export backup using data pump user received following error.

ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01 for user
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1587
ORA-39062: error creating master process DM00
ORA-31613: Master process DM00 failed during startup.

Normally user receives this error when maximum numbers of processes are exceeded. You will see below error in trace files.

ORA-00020: maximum number of processes (150) exceeded

Solution: To fix the issue increase PROCESSES initialization parameter and restart the database.

If you don’t see the error ORA-00020 trace file then look for other error messages which will help to fix the issue. 

I had faced similar issue and  but don't see ORA-00020 error. After looking the other error messages I see that child processes are terminated and in my case restarting the database resolved the issue.

Regards,
Satishbabu Gunukula, Oracle ACE

Thursday, March 1, 2018

One Database Solution for your Enterprise Business – Oracle 12c

Oracle 12c offers new architecture and features, you can manage many databases as ONE by efficient use of resources and reduce IT costs for your Enterprise Business. It also offers one solution for your traditional and cloud environments.

Join the Webinar to learn how this one database solution works for your enterprise business.

Date and time: Mar 16th 2018 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com.

Note that registrations are limited and first come and first serve basis.
You will receive an email confirmation with meeting session link.

For Presentation link "Click here"

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com