DOAG2015 OracleText presentation available for Download!

This year at DOAG conference in Nuremberg i was talking about Oracle Text, beginning with the architecture and till some examples using Oracle Text.

It was a pleasure for me to be a speaker at DOAG2015 again!

The conference was once more great! Really looking forward to DOAG2016.

Save the date for #DOAG2016: 15. – 18.11.2016, Nuremberg

Presentation Download here:
Oracle Text: AllesTextOderWas?

~Benedikt.

Disable Oracle RECYCLEBIN in 11g database

Hi guys,

in this article i would like to explain, how to disable RECYCLEBIN in oracle database 11g.

oracle@localhost [DB: ORADBN] [/home/oracle]
$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 27 13:27:22 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning option
SQL>
SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
recyclebin                           string      ON
SQL>

To disable RECYCLEBIN execute following command:

SQL> alter system set recyclebin=off deferred;
System altered.
SQL>

Now let’s check the Change:

SQL> show parameter recyclebin;
NAME                                 TYPE        VALUE
———————————— ———– ——————————
recyclebin                           string      ON
SQL>

Well, recyclebin seems still be active. But be careful – deferred means it only will effect subsequent sessions. So for check the Change, you have to Logout and Login again.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning option
oracle@localhost [DB: ORADBN] [/home/oracle]
$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 27 13:33:36 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning option
SQL> show parameter recyclebin;
NAME                                 TYPE        VALUE
———————————— ———– ——————————
recyclebin                           string      OFF
SQL>

Finally recyclebin is disabled.

Stay tuned,
Benedikt

Oracle Statspack und SQL zur Performanceanalyse – Kostenlos aber effektiv

Sie möchten mehr zu Performanceanalyse mit Oracle Statspack und SQL erfahren?

Durch Informatik aktuell hatte ich die Gelegenheit, zu diesem interessanten Thema einen Aritkel zu verfassen:

http://www.informatik-aktuell.de/betrieb/datenbanken/oracle-statspack-und-sql-zur-performanceanalyse-kostenlos-aber-effektiv.html

Oracle 11gR2 on Solaris 11: ORA-00371: not enough shared pool memory during instance startup with default shared_pool_size

Dear folks,

today i ran into an oracle bug. On Oracle Solaris 11 the Database (11.2.0.4.3) will not start with default shared_pool_size in init.ora parameter-file.

SQL> startup nomount pfile=’/home/oracle/pfile/pinit.ora’;
ORA-00371: not enough shared pool memory, should be atleast ‘XXXXXX’ bytes

The paramter file pinit.ora contains no SHARED_POOL_SIZE parameter. To solve the problem please use the workaround of setting the SHARED_POOL_SIZE parameter to a sufficient value. This bug will be fixed in 12.1.

Oracle Documentation Note:
https://support.oracle.com/epmos/faces/DocumentDisplay?id=1560386.1

The issue was investigated in the unpuplished Bug 13606499 – PHSB: DEFAULT MEMORY PARAMETER (INIT.ORA) IS NOT BIG ENOUGH TO START A INSTANCE

Stay tuned,
Benedikt

my #DOAG2014 presentation is available for download

Today i was speaking for the first time at DOAG conference in Nuremberg. Amazing, how many people joined my session about “Kostenlos aber effektiv: Performanceanalyse mit Statspack und SQL*”. Thanks a lot to everyone!

You can find my presentation under the following link:
http://www.performing-databases.com/index.php?id=38&L=1 …

Regards,
Benedikt

Oracle Database Upgrade to 11gR2 fails with – ORA-01722

Last days i tried to migrate a database to the latest 11.2.0.4.3 release on Solaris OS.

During the upgrade following error occured:

SQL > SELECT TO_NUMBER(‘DATA_VAULT_OPTION_ON’) FROM v$option
     2      WHERE
     3      value=’TRUE’ and parameter = ‘Oracle Database Vault’;

SELECT TO_NUMBER(‘DATA_VAULT_OPTION_ON’) from v$option;

ERROR at line 1:
ORA-01722: invalid number

After investigating some time in analysis, i found MOS note 1409844.1 that describes how to fix the Problem:
Catupgrd Returns: Ora-01722 Select To_number(‘DATA_VAULT_OPTION_ON’) from v$option
Link: https://support.oracle.com/epmos/faces/DocContentDisplay?id=1409844.1

As described in the Note, follow the steps and disable the Data Vault Option for the new environment before starting upgrade the database:

$ chopt disable dv

Afterwards start the upgrade again, you will confirm, that ORA-01722 has been fixed. Optionally you can after successful migration re-enable the Database Vault Option again:

$ chopt enable dv

Hopefully this Blog Post will help in solving your problems.

Benedikt Nahlovsky

Kostenlos aber effektiv: Performanceanalyse mit Statspack und SQL* at #DOAG2014

I am so happy – DOAG e.V. accepted my talk for DOAG conference 2014 in Nuremberg. It is the first time for me to visit DOAG conference as speaker, looking forward to tell the guys something about best practices for Performance Analysis with Statspack and SQL*.

The talk will be in german, here is the abstract:

Der Vortrag “Kostenlos aber effektiv: Performanceanalyse mit Statspack und SQL*” stellt Möglichkeiten dar, mit Hilfe von Statspack und SQL Plus / SQL Developer die Datenbankleistung zu analysieren. Best Practices zu Installation, Konfiguration und Nutzung von Statspack aus bewährter Praxis werden gezeigt. Anhand von fünf W-Fragen berichtet der Vortrag über die Taktik zur Lösungsfindung und Problembehebung.

Hope to see you in Nuremberg, 18th – 20th November 2014 at CCN East.

Regards,
Benedikt

 

Oracle Database crashes due to ORA-00240 and ORA-15064

In Oracle Database 11.2.0.3.5 there is a Bug which can make your database instance restart by itself.

If you get the following errors in your database alert.log you know your database is affected through the bug:
ORA-00240: control file enqueue held for more than 120 seconds
ORA-15064: communication failure with ASM instance

Bug:
Bug 13914613- Excessive time holding shared pool latch in kghfrunp with auto memory management (Doc ID: 13914613.8)

Affected Versions:
Check MOS Doc ID 13914613.8 for more details and the versions affected by this Bug, and the versions the fix will be in place.

Workaround:
You can use the simple workaround of setting the following underscore parameter to the database instance (instance needs to be restarted):
SQL> alter system set “_enable_shared_pool_durations”=FALSE  scope=spfile;

This Bug can be fixed by updating your Oracle Database to 11.2.0.3.6 or fix Bug 13914613!

Hope this will help to solve your problem.

Benedikt

Oracle Data Guard Setup Patch – How to with minimal downtime?

Actually i was planning to patch my Oracle Database and Grid Infrastructure 11.2.0.4.1 with Data Guard Setup to the new PSU2. In the following i want to describe my best practices to patch de Oracle Data Guard Environment with minimum downtime for the primary instances.

Here you can find my bascially architecture. Some facts:
– 2 Nodes, Oracle Enterprise Linux 6.4 with Oracle Restart installed
– 2 Primary Instances on Node 1 (Oracle Database 11.2.0.4.1)
– 2 Physical Standby Instances on Node 2 (Oracle Database 11.2.0.4.1)

– Data Guard Configuration is managed by Data Guard broker

MY JOB: Patch the complete System to 11.2.0.4.2 with minimal downtime for the Primary Instances!!!

My approach: 

1) Update your OPatch Utility to 11.2.0.3.6 on Node 2 for GI, Physical Standby 1 and 2

2) Shutdown immediate both physical standby instances on node 2

3) Perform binary installation of the patch on grid infrastructure and both physical standby instances according the patch README on Node 2
NOTE: Do not perform SQL installation (e.g. do not run catbundle.sql) for the patch at this time. SQL installation is performed after the Switchover from the primary instances to Node 2.

4) Startup mount both physical standby instances on Node 2

5) Check your Data Guard Status on Node 1 for two primary instances.
NOTE: The Data Guard broker will automatically restart the media recovery!

6) Switchover two Primary instances to Node 2 with dgmgrl

7) Perform any required SQL installation steps on Node 2 for the new Primary instances (catbundle.sql psu apply)

8) Update your OPatch Utility to 11.2.0.3.6 on Node 1 for GI, Physical Standby 1 and 2

9) Shutdown immediate both physical standby instances on node 1

10) Perform binary installation of the patch on grid infrastructure and both new physical standby instances according the patch README on Node1

11) Startup mount both physical standby instances on Node 1

12) Check again your Data Guard Status

Optionally you can test again the Switchover from Node 2 to Node 1.

In my opinion is accurately planning a patch the half story 🙂 Finally you have your Data Guard Setup patched to version 11.2.0.4.2!

Stay tuned,
Benedikt

Oracle Database 12c: Datapump export / import job in PDB gets ORA-07445

Hello guys,

in a internal development database i found a crazy problem. We have one Root Container and two pluggable databases, PROD and INTERNAL. So during a lot succesfull Export and Import Datapump Jobs, one Day in INTERNAL PDB no Export / Import Datapump Jobs was possible. I got the error: Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ stopped due to fatal Error
So in one of the written trace files i found the following error: ORA-07445: exception encountered: core dump [PC:0x0][SIGSEV][ADDR:0x0][PC:0x0][Address not mapped to object] []

My first thougt was to test Datapump in PROD PDB – no errors, job executed successfully.
For sure, nothing has changed during the time on the database, operating system, etc.

After some analysis i found that in SYS schema a package was invalid:

SQL> select status, object_id, object_type, owner||’.’||object_name “OWNER.OBJECT”
from dba_objects
where status != ‘VALID’ and owner in (‘SYS’, ‘XDB’)
order by 4,2;

STATUS     OBJECT_ID      OBJECT_TYPE          OWNER.OBJECT
INVALID    16233               PACKAGE BODY       SYS.KUPW$WORKER

Didn’t found a solution in time, recompiling package body, running utlrp.sql script didn’t fix the problem, i decided to contact My Oracle Support to get help. After a lot of Action Plans and tests they decided to create a Bug. (Bug 18201716 – ORA-7445 [PC:0x0][SIGSEV][ADDR:0x0] COMPILING SYS.KUPW$WORKER IN PDB) 

However, while the processing time i was dependent on an Datapump export / import. So I found an workaround: Using original export and import clients (exp and imp)!!!

Few days later there was a new Action Plan in the service request, i tested it and recognized: MY PROBLEM IS FIXED 🙂
In the following are the steps to get the Package valid again!

Recreate the package in PBD INTERNAL as follows – executing prvtbpw.plb in $ORACLE_HOME/rdbms/admin
cd $ORACLE_HOME/rdbms/admin
sqlplus “/@ora07:1521/internal as sysdba”

SQL> set lines 120 numwidth 12 pages 10000 long 2000000000
SQL> alter session set NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’;

Session altered.

SQL> show user
USER is “SYS”

SQL> select status, object_id, object_type, owner||’.’||object_name “OWNER.OBJECT” 
from dba_objects where status != ‘VALID’
and owner in (‘SYS’, ‘XDB’) order by 4,2;

STATUS     OBJECT_ID OBJECT_TYPE         OWNER.OBJECT
——-          ————    ———————–    ——————
INVALID    16233          PACKAGE BODY      SYS.KUPW$WORKER

Elapsed: 00:00:00.09
14:22:43 SQL> @prvtbpw.plb

Session altered.

Elapsed: 00:00:00.00

Package body created.

Elapsed: 00:00:00.19

Session altered.

Elapsed: 00:00:00.00
SQL> select status, object_id, object_type, owner||’.’||object_name “OWNER.OBJECT”
2 from dba_objects
3 where status != ‘VALID’ and owner in (‘SYS’, ‘XDB’)
4 order by 4,2;

no rows selected

Elapsed: 00:00:00.08

SQL> @utlrp.sql

TIMESTAMP
——————————————————————————————–
COMP_TIMESTAMP UTLRP_BGN 2014-02-22 14:23:11

Elapsed: 00:00:00.04
14:23:11 DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
14:23:11 DOC> objects in the database. Recompilation time is proportional to the
14:23:11 DOC> number of invalid objects in the database, so this command may take
14:23:11 DOC> a long time to execute on a database with a large number of invalid
14:23:11 DOC> objects.
14:23:11 DOC>
14:23:11 DOC> Use the following queries to track recompilation progress:
14:23:11 DOC>
14:23:11 DOC> 1. Query returning the number of invalid objects remaining. This
14:23:11 DOC> number should decrease with time.
14:23:11 DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
14:23:11 DOC>
14:23:11 DOC> 2. Query returning the number of objects compiled so far. This number
14:23:11 DOC> should increase with time.
14:23:11 DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
14:23:11 DOC>
14:23:11 DOC> This script automatically chooses serial or parallel recompilation
14:23:11 DOC> based on the number of CPUs available (parameter cpu_count) multiplied
14:23:11 DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
14:23:11 DOC> On RAC, this number is added across all RAC nodes.
14:23:11 DOC>
14:23:11 DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
14:23:11 DOC> recompilation. Jobs are created without instance affinity so that they
14:23:11 DOC> can migrate across RAC nodes. Use the following queries to verify
14:23:11 DOC> whether UTL_RECOMP jobs are being created and run correctly:
14:23:11 DOC>
14:23:11 DOC> 1. Query showing jobs created by UTL_RECOMP
14:23:11 DOC> SELECT job_name FROM dba_scheduler_jobs
14:23:11 DOC> WHERE job_name like ‘UTL_RECOMP_SLAVE_%’;
14:23:11 DOC>
14:23:11 DOC> 2. Query showing UTL_RECOMP jobs that are running
14:23:11 DOC> SELECT job_name FROM dba_scheduler_running_jobs
14:23:11 DOC> WHERE job_name like ‘UTL_RECOMP_SLAVE_%’;
14:23:11 DOC>#

PL/SQL procedure successfully completed.

Elapsed: 00:02:59.99

TIMESTAMP
——————————————————————————————–
COMP_TIMESTAMP UTLRP_END 2014-02-22 14:26:11

Elapsed: 00:00:00.00
14:26:11 DOC> The following query reports the number of objects that have compiled
14:26:11 DOC> with errors.
14:26:11 DOC>
14:26:11 DOC> If the number is higher than expected, please examine the error
14:26:11 DOC> messages reported with each object (using SHOW ERRORS) to see if they
14:26:11 DOC> point to system misconfiguration or resource constraints that must be
14:26:11 DOC> fixed before attempting to recompile these objects.
14:26:11 DOC>#

OBJECTS WITH ERRORS
——————-
0

Elapsed: 00:00:00.00
14:26:11 DOC> The following query reports the number of errors caught during
14:26:11 DOC> recompilation. If this number is non-zero, please query the error
14:26:11 DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
14:26:11 DOC> are due to misconfiguration or resource constraints that must be
14:26:11 DOC> fixed before objects can compile successfully.
14:26:11 DOC>#

ERRORS DURING RECOMPILATION
—————————
0

Elapsed: 00:00:00.00

Function created.

Elapsed: 00:00:00.01

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.09

Function dropped.

Elapsed: 00:00:00.01
…Database user “SYS”, database schema “APEX_040200”, user# “98” 14:26:52
…Compiled 0 out of 2998 objects considered, 0 failed compilation 14:26:52
…263 packages
…255 package bodies
…453 tables
…11 functions
…16 procedures
…3 sequences
…458 triggers
…1322 indexes
…207 views
…0 libraries
…6 types
…0 type bodies
…0 operators
…0 index types
…Begin key object existence check 14:26:52
…Completed key object existence check 14:26:52
…Setting DBMS Registry 14:26:52
…Setting DBMS Registry Complete 14:26:53
…Exiting validate 14:26:53

PL/SQL procedure successfully completed.

Elapsed: 00:00:40.69
SQL>
SQL> select status, object_id, object_type, owner||’.’||object_name “OWNER.OBJECT”
2 from dba_objects
3 where status != ‘VALID’ and owner in (‘SYS’, ‘XDB’)
4 order by 4,2;

no rows selected

SQL> spool off

Well, there are often few Bugs in new Oracle Database Versions, but don’t care, sometime all problems should be fixed 🙂

Stay tuned,
Benedikt