Oracle 11gR2 Released

Oracle has released 11gR2 to some fanfare and there seems to be some major improvements especially in the way Oracle uses storage.

I thought I'd jot down some key points:

Automatic Storage Management (ASM)

11gR2 seems to be all about ASM.

It has moved into the cluster framework.

They have announce ASM Cluster File System (ACFS) which can host all files including Oracle binaries, Voting Disks, OCR, flat files etc.

You can set up disk pools in ASM of different tier disks so that when a table partition ages you can set a policy for ASM to migrate it to a different tier disk.

There are also more tuning options in ASM

There is a full featured ASMCMD

Oracle 11gR2 Data Guard.

If Oracle detects a corrupt block and the database is in a Data Guard configuration, it will check the same block on the standby. If the same block with the same SCN is not corrupted on the standby then it will automatically repair the corrupt block on the primary.

Oracle 11gR2 OLTP compression.

There is a new compression algorithm with regard to table compression. It doesn't actually compress the data in the traditional sense, instead it stores it in the block differently. It puts each distinct value into the header of the block and then records in the record a small size unique value that references it. Using this, there are dramatic improvements in reads and disk usage especially with data of low cardinality.

It was recommended that you do this for the top 10 tables in the database rather than all. Also db_block_size is a big factor in how well compression works.

Oracle 11gR2 RAC

Oracle seem to be pushing RAC as the standard. So much so that they are incorporating Oracle One Node (CRS services on a single node) as standard with the Oracle 11gR2 database allowing you to transition single node databases to another server in a RAC style graceful fail over.

They kept stressing that everything now is built and tested first for RAC.

There is also a concept of RAC server pools. You can guarantee services a particular number of nodes. If servers become available, lower priority services will make way for those with a higher priority.

New Oracle Installer

Oracle have developed a new Installer replacing the OUI which apparently is more friendly in error handling solution scripts if problems are faced during install.

Oracle 11g New Features

I found a good series on Oracle 11g database new features at Oracle Technology Network

Identifying rows that cause ORA-02298 when enabling constraints

When doing bulk loads of data into tables it is quicker to disable the contraints on the tables and then enable them at the end.

One of the problems with this approach is that the constraint may not validate because of rogue data. One of the great features is being able to log the offending rows an exceptions table.

Lets take a look at an example

SQL> ALTER TABLE EMP ENABLE CONSTRAINT EMP_FK2;

ALTER TABLE EMP ENABLE CONSTRAINT EMP_FK2 

                                                                                  *

ERROR at line 1:

ORA-02298: cannot validate (EMP_FK2) - parent keys not found

Lets identify the offending rows:

1. Create the exceptions table using utlexcpt.sql (utlexcpt.sql uses rowid, utlexpt1.sql uses urowuid)

SQL>  @?/rdbms/admin/utlexcpt.sql

2. Try and enable the constraint again

SQL> ALTER TABLE EMP ENABLE CONSTRAINT EMP_FK2 EXCEPTIONS INTO EXCEPTIONS;

ALTER TABLE EMP ENABLE CONSTRAINT EMP_FK2 EXCEPTIONS INTO EXCEPTIONS

                                  *

ERROR at line 1:

ORA-02298: cannot validate (EMP_FK2) - parent keys not found

3. Have a look at the exceptions table

SQL> select * from exceptions;

ROW_ID             OWNER                          TABLE_NAME            CONSTRAINT

------------------ ------------------------------ --------------------- ---------------------

AAANd/AARAAABAmAAs SCOTT                          EMP                   EMP_FK2

4. We can now identify the row as follows:

SQL> select a.* from EMP a, EXCEPTIONS e

     where a.rowid = e.row_id;

Delete offending rows or add parent rows as necessary

Cache Hit Ratios

Just thought I'd list the cache hit ratios using the gv$ views instead of v$ view so that the ratios can be retrieved for all instances in one query.

Even with 10g and Oracle automatically sizing the pools within the SGA, these ratios are a good guide in whether enough memory is available to each instance.


db_cache hit ratio
Sizing for db_cache_size
Here we are aiming for a cache hit ratio > 95%

select ses.inst_id, 1-(PHY.VALUE - LOB.VALUE - DIR.VALUE)/SES.VALUE "DB_CACHE HIT RATIO"
FROM GV$SYSSTAT SES, GV$SYSSTAT LOB, GV$SYSSTAT DIR, GV$SYSSTAT PHY
WHERE ses.name ='session logical reads'
and dir.name ='physical reads direct'
and phy.name ='physical reads'
and lob.name ='physical reads direct (lob)'
and ses.inst_id = lob.inst_id
and ses.inst_id = dir.inst_id
and ses.inst_id = phy.inst_id;


library cache hit ratio
Sizing for shared_pool_size
Here we are aiming for a cache hit ratio > 99%

SELECT inst_id, SUM(PINS-RELOADS)/SUM(PINS)*100 "LIBRARY HIT RATIO"
FROM GV$LIBRARYCACHE
group by inst_id;


library cache reload to pins ratio
Sizing for shared_pool_size
Here we are aiming for a ratio < 1%

SELECT inst_id, SUM(PINS) "EXECUTIONS", SUM(RELOADS) "MISSES", SUM(RELOADS)/SUM(PINS)
"RELOAD RATIO"
FROM gV$LIBRARYCACHE
group by inst_id;


dictionary cache hit ratio
Here we are aiming for a cache hit ratio > 95%

SELECT inst_id, (SUM(GETS-GETMISSES))/SUM(GETS) "DICTIONARY CACHE HIT RATIO"
FROM GV$ROWCACHE
group by inst_id
/

ASMCMD is very slow

asmcmd provides a command line interface for Oracle's Automatic Storage Management. It gives a familiar Unix style feel to negotiate around it rather than having to construct SQL statements through sqlplus.

One of the big problems with it though is that it is very slow. Is there a way to speed it up?

Yes there is. asmcmd is actually a perl script that queries the underlying V$ASM_FILE and V$ASM_DISKGROUP views. The script of interest is the file $ORACLE_HOME/bin/asmcmdcore

Edit this file and change references to V$ASM_DISKGROUP with V$ASM_DISKGROUP_STAT

You will notice a dramatic increase in speed. V$ASM_DISKGROUP with V$ASM_DISKGROUP_STAT return the same information. The difference is that V$ASM_DISKGROUP_STAT retrieves it's information from cache where as V$ASM_DISKGROUP retrieves its information from disk every time.

Deadlock on SYS.CDC_ALTER_CTABLE_BEFORE

Ran into this problem on one of our old Oracle 9.2 test databases.

Running exp had failed due to DBMS_EXPORT_EXTENSIONS being invalid.

Upon shutting down the database cleanly using a shutdown immediate. The database would not start due to the following error:

SQL> startup
ORACLE instance started.

Total System Global Area 185684368 bytes
Fixed Size 455056 bytes
Variable Size 150994944 bytes
Database Buffers 33554432 bytes
Redo Buffers 679936 bytes
Database mounted.
ORA-00604: error occurred at recursive SQL level 1
ORA-04020: deadlock detected while trying to lock object
SYS.CDC_ALTER_CTABLE_BEFORE



To get the database open I had to shut the database down and change the following parameters in the pfile:


_system_trig_enabled=false
aq_tm_processes=0
job_queue_processes=0


SYS.CDC_ALTER_CTABLE_BEFORE is a system trigger and setting the hidden parameter _system_trig_enabled enabled the database to start without firing any triggers.

A startup restrict then allowed me to start up the database in the knowledge that other applications could not access the database while I diagnosed further.

The following SQL

select owner, object_type, count(*)
from dba_objects
where status = 'INVALID'
group by owner, object_type;

Revealed thousands of SYS objects to be invalidated. To compile them I ran the script utlrp.sql

SQL> @?/rdbms/admin/utlrp.sql

This revealed that 82 SYS objects were still invalid including DBMS_EXPORT_EXTENSIONS.

There is obviously something wrong with the data dictionary so lets recreate it:

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql

Shut down the database
remove the parameters we changed
start the database up.

ORA-04031 errors

I recently received ORA-04031 errors on one of our production databases.

ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","lock table sys.mon_mods$ in ...","sga heap(1,0)","kglsim object batch")

At first glance it looks like it has run out of memory and we need to increase sga_max_size and sga_max_target. However this is not the cause of the problem and eventually all you'll end up doing is prolonging the time before the error occurs agains as opposed to fixing the root cause.

So how do we diagnose the root cause?

The best place to start is the associated trace file that is created in the database's background_dump_dest directory.

The trace file will contain the Heading

=================================
Begin 4031 Diagnostic Information
=================================

Scroll down to the heading

==============================
Memory Utilization of Subpool 1
================================
Allocation Name Size
_________________________ __________
"free memory " 101905912


This section shows the breakdown of the memory allocation. What we are looking is an allocation that looks unusually large.

In our example there was the following very large allocation

"mvobj part des " 495739432

After more investigation it was revealed that we had hit a bug in Oracle 10.2.0.3 where there is a memory leak with Partitioning DDL. This is documented as fixed in 10.2.0.4. There is an interim patch released to address this.

Oracle high availability white papers

Found this white paper on Enterprise Manager high availability for future reference. The index for other High Availabilty best practise white papers are here

OC4J~OC4J_EM~default_island~1 crashing every 10 minutes

We had a problem with Enterprise Manager management server having outages every 10-15 minutes

Looking at the ipm.log we were getting the following:
08/03/24 02:48:00 [4] Starting Process: OC4J~OC4J_EM~default_island~1 (999687094:0)
08/03/24 02:51:29 [4] Process Alive: OC4J~OC4J_EM~default_island~1 (999687094:16579)
08/03/24 03:07:09 [4] Process Crashed: OC4J~OC4J_EM~default_island~1 (999687094:16579) - Restarting
08/03/24 03:07:11 [4] Starting Process: OC4J~OC4J_EM~default_island~1 (999687095:0)
08/03/24 03:10:40 [4] Process Alive: OC4J~OC4J_EM~default_island~1 (999687095:21694)
08/03/24 03:12:30 [4] Process Crashed: OC4J~OC4J_EM~default_island~1 (999687095:21694) - Restarting


I found that we had a backlog of uploaded files from the agents. Stopping the management server and deleting the files out of $ORACLE_HOME/sysman/emd/recv greater than 1 day and restarting the management server resolved the problem.

There must have been a file that had been corrupted or the oms could not process. Interestingly, the day that the problems started coincided with when we put the latest Critical Patch on our production servers. For future note we will shutdown the agents and create a blackout before starting to patch the database homes on the monitored machines.

Oracle Enterprise Manager Repository Error Log Table

Found this table today in the OEM repository that lists errors that have occurred in the repository.

select * from sysman.MGMT_SYSTEM_ERROR_LOG
order by occur_date desc

Setting dependencies for RAC instances

I've found that CRS sometimes tries to start RAC database instances before it starts ASM. This casues the databases to not start when the server is bounced as they cannot access the datafiles.

The way around this is to set up a dependency on the RAC instance that states that ASM needs to be started before the instance can be started.

To check if the dependency already exists use

crs_stat -p

oracle@dbnode1> crs_stat -p | more
NAME=ora.DBPRD.DBPRD1.inst
TYPE=application
ACTION_SCRIPT=/apps/oracle/product/10.2.0/db_1/bin/racgwrap
ACTIVE_PLACEMENT=0
AUTO_START=1
CHECK_INTERVAL=600
DESCRIPTION=CRS application for Instance
FAILOVER_DELAY=0
FAILURE_INTERVAL=0
FAILURE_THRESHOLD=0
HOSTING_MEMBERS=dbnode1
OPTIONAL_RESOURCES=
PLACEMENT=restricted
REQUIRED_RESOURCES=ora.dbnode1.ASM1.asm
RESTART_ATTEMPTS=5
SCRIPT_TIMEOUT=600
START_TIMEOUT=0
STOP_TIMEOUT=0
UPTIME_THRESHOLD=7d
USR_ORA_ALERT_NAME=
USR_ORA_CHECK_TIMEOUT=0
USR_ORA_CONNECT_STR=/ as sysdba
USR_ORA_DEBUG=0
USR_ORA_DISCONNECT=false
USR_ORA_FLAGS=
USR_ORA_IF=
USR_ORA_INST_NOT_SHUTDOWN=
USR_ORA_LANG=
USR_ORA_NETMASK=
USR_ORA_OPEN_MODE=
USR_ORA_OPI=false
USR_ORA_PFILE=
USR_ORA_PRECONNECT=none
USR_ORA_SRV=
USR_ORA_START_TIMEOUT=0
USR_ORA_STOP_MODE=immediate
USR_ORA_STOP_TIMEOUT=0
USR_ORA_VIP=


The REQUIRE_RESOURCES line states if a dependency is set up. It the above example we can see that ASM1 on dbnode1 needs to be started before the instance DBPRD1 is started.

To set this dependency use -s flag in the srvctl command:

srvctl modify instance -d DBPRD -i DBPRD1 -s +ASM1

simple shell script to update all instances in a 2 node cluster

for db in DBPRD DB2PRD
do
srvctl modify instance -d ${db} -i ${db}1 -s +ASM1
srvctl modify instance -d ${db} -i ${db}2 -s +ASM2
done