PROBLEM: Received  ORA-4063 error while taking expdp, like below. $ expdp system/manager directory=EXPDP dumpfile=exptest.dmp  logfile=exp_test.log schemas=TEST Export: Release 11.2.0.4.0 – Production on Tue Feb 10 05:50:17 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production With the Partitioning, OLAP, Data […]

Sometimes catalog and catproc components become invalid due to some invalid objects. You need to find those objects and take necessary action. SQL> select comp_id, status, version from dba_registry; COMP_ID STATUS VERSION ———— ———– ————– CATALOG INVALID 11.2.0.3.0 CATPROC INVALID 11.2.0.3.0 Use below script to get the objects which is causing the issue: set serveroutput […]

When i tried to connect to database, i got the ORA-00020: maximum number of processes (%s) exceeded error. Even from sysdba I was not able to connect. Solution: 1. connect to database with prelim option and shutdown abort. $ sqlplus -prelim SQL> shutdown abort. exit 2. change the processes parameter to a higher value in […]

You may get ORA-39087 and ORA-39070 while doing expdp.   Actual Error: [oracle@localhost ~]$ expdp dumpfile=test.dmp directory=DATA_PUMP_DIR logfile=test.log parallel=2 tables=raj.test Export: Release 12.1.0.2.0 – Production on Thu Aug 27 10:55:36 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: raj/raj@orcl Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit […]

This ORA-01000 error comes when current open_cursors exceeds the defined open_cursor parameter database. SELECT max(a.value) as highest_open_cur, p.value as max_open_cur FROM v$sesstat a, v$statname b, v$parameter p WHERE a.statistic# = b.statistic# and b.name = ‘opened cursors current’ and p.name= ‘open_cursors’ group by p.value; HIGHEST_OPEN_CUR MAX_OPEN_CUR —————- ———————– 320 300 SQL> show parameter open_cursor NAME TYPE […]

You might get this error, when you tried to drop the undo table. Its because , there are some active transactions in the undo. The solution is to find that transaction and kill the same. set pagesize 200 set lines 200 set long 999 col username for a9 SELECT a.name,b.status , d.username , d.sid , […]

There are scenarios where you database will be in hung state and we won’t be able to connect to the database using sysdba, even to shutdown the instance also.   In that case, we can connect using PRELIM option. This is the backdoor entry to the database. This prelim option connects to SGA, but it […]

While export if you are getting below : . exporting rollback segment definitions . exporting database links . exporting sequence numbers . exporting directory aliases . exporting context namespaces . exporting foreign function library names . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions . exporting system procedural objects […]

There can be scenarios where you will get below error while running utlrpl.sql SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql SELECT dbms_registry_sys.time_stamp(‘utlrp_bgn’) as timestamp from dual                                                                   * ERROR at line 1: ORA-04045: errors during recompilation/revalidation of SYS.DBMS_REGISTRY_SYS ORA-01031: insufficient privileges work around solution for this problem:   SQL> ALTER SYSTEM SET “_system_trig_enabled” = FALSE scope=both; System altered. Now run […]

PROBLEM: If you are trying to take export using exclude option from kshell prompt, then you may get an error as ORA-39071: Value for EXCLUDE is badly formed. SOLUTION: The workaround to avoid this : expdp dumpfile=pdp1037782.dmp logfile=pdp1037782.log schemas=HELLOPDBA directory=EXPORT exclude=TABLE:\”IN (‘PVMERSADDRESS’,’VERSIONS’)\””   SEE ALSO: How to upgrade database from 11g to 12c manually How […]