Ask The Real Tom

June 1, 2012



What is “Enq: FB contention” in oracle?

Filed under: oracle — Tags: , — admin @ 1:56 pm

“Enq: FB contention” is an ASSM enque wich ensures that only one process format the block. This happen usually on parallel inserts on the same table.

April 27, 2012



Downgrade Oracle Database 11.2.0.3 to 11.2.0.2

Filed under: oracle — admin @ 8:02 am

Downgrading 11.2.0.3 to 11.2.0.2 works. But like everything with oracle 11.2 you have to apply a patch.
Oracle Restart (GI) is used for this setup.


# current release
shutdown immediate
startup downgrade
spool downgrade
drop user sysman cascade;
@catdwgrd.sql
spool off
shutdown immediate

shutdown immediate
# change environment
srvctl downgrade database -d dsdb01 -o /opt/app/oracle/product/11.2.0/rdbms1 -t 11.2.0.2
oratab
sid dsdb01
srvctl modify database -d dsdb01 -p /opt/app/oracle/product/11.2.0/rdbms1/dbs/spfiledsdb01.ora
srvctl config database -d dsdb01
???

# get cat reload from patch 11811073
unzip p11811073_112020_Generic.zip
cd 11811073
/opt/app/oracle/product/11.2.0/rdbms1/OPatch/opatch apply

startup upgrade
spool reload.log
@$ORACLE_HOME/rdbms/admin/catrelod.sql
spool off
shutdown immediate
startup
@utlrp

August 3, 2011



Check if oracle online patch is active on an specific instance

Filed under: oracle — Tags: , , , — admin @ 8:21 am

Actually i go an error ORA-600 [kkoipt:incorrect pwj] on 11.2 an i applied the patch 9929660.

But the error occured on a instance again. So i was wondering how i can check if the online patch is activated.

opatch lsinventory just shows if the patch is installed on the oracle home.

The solution was:

oradebug patch list

Here you see if the patch is activated on the instance. Aswell you could disable the patch for the instance.

April 11, 2011



ORA-00600: internal error code, arguments: [ktfa_get_hist_objn]

Filed under: oracle — Tags: , , , — admin @ 1:39 pm

I hade recently some trouble with Flashback Data Archive (Total Recal, FBDA) on Oracle 11.2.0.2

On a dissasciate Statement like:
exec DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA('USER','TABLE');

Following Oracle error occured:
ORA-00600: internal error code, arguments: [ktfa_get_hist_objn]

The problem was a oracle bug. If you name any column in your table “DATE” – FDBA does not work properly.

November 1, 2010



Oracle Unbreakable Enterprise Linux Kernel – Info Pice

Filed under: operating system,oracle — Tags: , , , — admin @ 2:47 pm

I am just search since hours for the oracleasm-2.6.32 package … an just coulnd find it.

Now I just came across this pice of information. Maybe it shortens my search ūüôā

http://www.oracle.com/us/technologies/linux/uek-for-linux-177034.pdf

Be advised that you will have to re-install oracleasm and ocfs2 if you want to revert to the previous PV kernel. Unbreakable Enterprise Kernel itself already includes ocfs2 and oracleasm, so the separate RPMs are not needed.

July 30, 2010



Oracle ASM Disk Overview

Filed under: oracle — Tags: , , , , — tkalo @ 3:38 pm

I’ve wrote a simple SQL script to list all ASM disks with state, size and their diskgroups, if any.

set pages 5000 lines 180
set heading on
set feedback off
set serveroutput on

col LABEL for a28
col DISKGROUP for a9
col STATUS for a9
col TOTAL for a11
col MOUNT_STATE for a11

exec dbms_output.put_line('---');
exec dbms_output.put_line('--- DISK OVERVIEW');
exec dbms_output.put_line('---');

SELECT
        D.LABEL||' ('||D.NAME||')' LABEL,
        (SELECT
          NAME
         FROM
          V$ASM_DISKGROUP
         WHERE
          GROUP_NUMBER=D.GROUP_NUMBER)
        DISKGROUP,
        D.STATE STATUS,
        lpad(D.TOTAL_MB/1024,6)||' GB' TOTAL,
        D.MOUNT_STATUS MOUNT_STATE
FROM
        V$ASM_DISK D
ORDER BY 2;

exec dbms_output.put_line('---');
set feedback on

The output looks as follow:

---
--- DISK OVERVIEW
---

LABEL                        DISKGROUP STATUS    TOTAL       MOUNT_STATE
---------------------------- --------- --------- ----------- -----------
ORAASM1 (ORAASM1)            DG1       NORMAL    99.984 GB   CACHED
ORAASM3 (ORAASM3)            DG1       NORMAL    99.984 GB   CACHED
ORAASM5 (ORAASM5)            DG1       NORMAL    99.984 GB   CACHED
ORAASM4 (ORAASM4)            DG1       NORMAL    99.984 GB   CACHED
ORAASM2 (ORAASM2)            FRA1      NORMAL    99.984 GB   CACHED
ORAASM6 ()                             NORMAL         0 GB   CLOSED
---

July 29, 2010



Set lock_sga to true on Linux (SLES)

Filed under: oracle,Uncategorized — Tags: , , — admin @ 1:01 pm

To lock the SGA into physical memory you have to set the lock_sga parameter to true.

Usually you get following Error message.

SQL> startup
SQL> ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memory

The problem it ulimit -l
You have to set it in /etc/security/limits.conf and relogon to the system and restart database.

cat /etc/security/limits.conf
...
oracle soft memlock unlimited
oracle hard memlock unlimited


alter system set lock_sga=true scope=spfile;
shutdown immediate
startup

And you SGA stays in physical memory.

If you SGA gets pages out you will encounter following waits.
Wait Event: latch: shared pool

July 28, 2010



Enable and Validate Constraints in Parallel (Oracle)

Enabling and validating constraints is can take a huge amount of time in big oracle databases.
To speed the validaten up, it can be done in parallel.
This can save you valuable time in release windows.

  1. Enable Constraint with novalidate option
  2. Set parallel degree on the table or on session with force ddl parallel
  3. Enable nable the constraints with validation

Example:

ALTER TABLE T_1 ADD CONSTRAINT NC_1 not null (colname) disabled;
ALTER TABLE T_1 ENABLE NOVALIDATE CONSTRAINT NC_1;
ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;
ALTER TABLE T_1 PARALLEL 8;
ALTER TABLE T_1 ENABLE VALIDATE CONSTRAINT NC_1;
ALTER SESSION DISABLE PARALLEL DDL;



ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device

Problem:


SQL> startup nomount
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device

There are 64G physical memoray avaliable.
shmmax is set to 52G
But still its not possibel to start the Oracle instance with more than 30G SGA.

Solution:

kernel.shmall is set to small.


cat /proc/sys/kernel/shmall
8248733
getconf PAGE_SIZE
4096

This means that only 8248733 memory pages can be allocate.
4096 x 8248733 = 32G

To allocate 50G we need to set shmall to at least 13107200

July 15, 2010



ORA-56708: Could not find any datafiles with asynchronous i/o capability

Filed under: oracle — Tags: , , , — admin @ 8:57 am

I tried to run IO calibrate to check what the system is able to handle.
But I run into following error (using ASM / ASMlib)


ORA-56708: Could not find any datafiles with asynchronous i/o capability
ORA-06512: at "SYS.DBMS_RMIN", line 456
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1285
ORA-06512: at line 6

The ASM files show ASYNC_ON

SELECT name, asynch_io
FROM v$datafile f,v$iostat_file i
WHERE f.file# = i.file_no
AND filetype_name = 'Data File'
/
+DG1/xxxx/datafile/system.260.721649043
ASYNC_ON
....

did not help as well

alter system set filesystemio_options=setall scope=spfile;
shutdown immediate
startup

I guess that the problem is that moste datafiles (smallfiles) are full (max. number of blocks). So the io calibrate tool can not write to this files.

System aio slots:

To find out the maximum avalable asynch I/O slots:
$ cat /proc/sys/fs/aio-max-nr
65536

To find out how many are being used:
$ cat /proc/sys/fs/aio-nr

increase fs.aio-max-nr if nessesary (or restart some db's)
65536
Older Posts »

Powered by WordPress