Monday, September 14, 2009

ORA-600 [32695] [hash aggregation can't be done] 10.2.0.3

Bug 5893340 : ORA-600 [32695] [hash aggregation can't be done] can occur
for a GROUP BY query if hash aggregation is chosen.
Fixed on 10.2.0.4 and up.

But

Bug 6471770 :

Group-By queries can fail with ora-32690 or
ORA-600 [32695] [hash aggregation can't be done]
when operating on a large volume of data if hash group by
aggregation is used.

Also, it is possible for hash group-bys to over allocate memory beyond
the limit impose by the auto-memory manager.

Fixed in 10.2.0.5 and up


In order to avoid the erros from both bugs :

1) Disable Hash group-by by setting
"_gby_hash_aggregation_enabled" to FALSE,

2) Use NO_USE_HASH_AGGREGATION hint on your query

Friday, September 11, 2009

Change sysman and dbsnmp password for dbconsole 10G

1) Change sysman password:

On node the dbconsole is running

$ export ORACLE_SID=MYDB
$ emctl stop dbconsole --If you have RAC stop dbconsole on other(s) node(s) also.

$ sqlplus sys as sysdba

SQL>alter user sysman identified by newpass;

SQL>exit;

If you are on 10.2.0.4 and up
$ emctl setpasswd dbconsole
   provide the new password

Prior 10.2.0.4
Go to $ORACLE_HOME/host_sid/sysman/config (if RAC on each node)
Save the file emoms.properties to emoms.properties.orig
Edit the file emoms.properties
Search for the line beginning with:
oracle.sysman.eml.mntr.emdRepPwd=
Replace the encrypted value by the new password value
Search for the line:
oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE
Replace TRUE by FALSE

$ emctl start dbconsole --If you have RAC start dbconsole on the other(s) node(s) also.

Check that the password has been encrypted
Edit the file $ORACLE_HOME/host_sid/sysman/config/emoms.properties (if RAC on each node)
Search for the line beginning with:
oracle.sysman.eml.mntr.emdRepPwd=
Check that the password is encrypted
Search for the line beginning with:
oracle.sysman.eml.mntr.emdRepPwdEncrypted=
Check that the value is TRUE

2) Change dbsnmp password:

On node the dbconsole is running
$ export ORACLE_SID=MYDB
$ emctl stop dbconsole --If you have RAC stop dbconsole on other(s) node(s) also.
$ sqlplus sys as sysdba
SQL>alter user dbsnmp identified by newpass;
SQL>exit;

Go to $ORACLE_HOME/host_sid/sysman/emd (if RAC On each node):
Save the file targets.xml to targets.xml.orig
Edit the file targets.xml
Find all occurrences of
Property NAME="password" VALUE="" ENCRYPTED="TRUE"
Replace the encrypted value by the new password value
Replace TRUE by FALSE

On node the dbconsole is running
$ emctl start dbconsole --If you have RAC stop dbconsole on other(s) node(s) also.

References
Oracle Support notes : 259379.1 & 259387.1

Tuesday, September 8, 2009

IBM GPFS and Oracle RAC 10gR2 Tips on AIX 5.3

Here is some tuning tips for GPFS and Oracle RAC

1) Do not use the "dio" mount option for the GPFS file system

2) If using RAID devices, configure a single LUN for each RAID device. Do not create LUNs across RAID devices for use by GPFS as this will ultimately result in significant loss in performance as well as making the removal of a bad RAID more difficult. GPFS will stripe across the multiple LUNs (RAIDs) using its own optimized method.

3) For Oracle RAC databases, set the GPFS file system block using the "mmcrfs" command and the "-B" option, to a large value using the following guidelines:512 KB is generally suggested.

4) GPFS Threads
Use the following guidelines to set the GPFS "worker threads" to allow the maximum parallelism of the Oracle AIO threads, and the GPFS "prefetch threads" to benefit Oracle sequential I/O.
On a 64-bit AIX kernel:
GPFS worker threads can be <= 548.
GPFS worker threads + GPFS prefetch threads <= 550.
When requiring GPFS sequential I/O, set the prefetch threads between 50 and 100 (the default is 64), and set the worker threads to have the remainder.
Example:
"mmchconfig prefetchThreads=75"
"mmchconfig worker1Threads=475"

5) For Oracle RAC node recovery to work correctly, GPFS must be configure to be automatically loaded at boot time and automatically mount the GPFS file systems. Use the following two GPFS commands to configure this:
root@raven:64bit /> mmchconfig autoload=yes
root@raven:64bit /> mmchfs /dev/oragpfs -A yes
mmchfs: 6027-1371 Propagating the changes to all affected nodes.
This is an asynchronous process.


References : Oracle Metalink Document : 302806.1

Wednesday, September 2, 2009

ORA-01429 Index-Organized Table: no data segment to store overflow row-pieces

When you get this error probably you have exceeded the maximum length of an IOT record without specifying the overflow segment. The maximum length of an IOT record, in order to fit into the index block, is limited to half the
block size

Tuesday, September 1, 2009

Transportable Table Space (TTS)

Transportable Table Space (TTS) is used to take out of the database pieces of data for various reasons (Archiving , Moving to other databases etc). From 10g onwards you can also transport a tablespace across different platforms of O/S using rman to convert the tablespace to the desired endian.

The following steps covering the TTS for an ASM tablespace named DATA.
I will also use the rman convert command to so how to move tablespaces across platforms. Finally i will drop the original tablespace reimport the exported and convert it to asm

1) Check that the tablespace will be seft contained

As sys
SQL> execute sys.dbms_tts.transport_set_check('DATA',TRUE);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected

If you have no rows selected you have a self contained tbs and you can proceed.
Otherwise you must fix the errors before proceed.

2) Make the tablespace read only

alter tablespace data read only;

3) Export the metadata using data pump

SQL> create or replace directory tts_dir as '/data/ttbs';

Directory created.

SQL> GRANT READ,WRITE ON DIRECTORY tts_dir to system;

Grant succeeded.

expdp system DUMPFILE=data.dmp DIRECTORY = tts_dir TRANSPORT_TABLESPACES=DATA TRANSPORT_FULL_CHECK=Y

If the tablespace set being transported is not self-contained, then the export will fail.

And check the metadata file.

[oracle@labdb01 ttbs]$ ls -la /data/ttbs/
total 512
drwxr-xr-x 2 oracle oinstall 4096 Sep 1 15:03 .
drwxr-xr-x 5 26 26 4096 Sep 1 14:13 ..
-rw-rw---- 1 oracle oinstall 81920 Sep 1 15:04 data.dmp
-rw-rw-r-- 1 oracle oinstall 1061 Sep 1 15:04 export.log

4) Use V$TRANSPORTABLE_PLATFORM to find the exact platform name of the
target database. You can execute the following query on target platform
instance or in your instance if you want just to take out the file and reimport it later.

SQL> set line 200

SQL> SELECT tp.platform_id,substr(d.PLATFORM_NAME,1,30) PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------------------------------------------------------------------ --------------
13 Linux x86 64-bit Little
SQL>

5) Use rman to convert and copy the datafile from the ASM to the directory where the metadata exist (/data/ttbs)

rman target /
RMAN> CONVERT TABLESPACE DATA TO PLATFORM 'Linux x86 64-bit' FORMAT '/data/ttbs/%U';

ls -la /data/ttbs
drwxr-xr-x 2 oracle oinstall 4096 Sep 1 15:11 .
drwxr-xr-x 5 26 26 4096 Sep 1 14:13 ..
-rw-rw---- 1 oracle oinstall 81920 Sep 1 15:04 data.dmp
-rw-r----- 1 oracle oinstall 52436992 Sep 1 15:11 data_D-RLABDB_I-1824868768_TS-DATA_FNO-75_01ko5jsn
-rw-rw-r-- 1 oracle oinstall 1061 Sep 1 15:04 export.log

And your done with the export

Import the tablespace and convert it to asm

1) Drop the orginal tablespace

SQL> drop tablespace data including contents;

Tablespace dropped.

2) Use impdp to import it

impdp system DUMPFILE=data.dmp DIRECTORY = tts_dir TRANSPORT_DATAFILES='/data/ttbs/data_D-RLABDB_I-1824868768_TS-DATA_FNO-75_01ko5jsn'

SQL> alter tablespace data read write;

Tablespace altered.

Now the tablespace is accessible from the database but its not in the asm

3) Use RMAN to transfer the tablespace to ASM

rman target /
RMAN> backup as copy datafile '/data/ttbs/data_D-RLABDB_I-1824868768_TS-DATA_FNO-75_01ko5jsn' format '+DATA';

If the 10g database is open you need to offline the datafile first

SQL> alter database datafile '/data/ttbs/data_D-RLABDB_I-1824868768_TS-DATA_FNO-75_01ko5jsn' offline;

Database altered.

Switch to the copy:
RMAN> switch datafile '/data/ttbs/data_D-RLABDB_I-1824868768_TS-DATA_FNO-75_01ko5jsn' to copy;

using target database control file instead of recovery catalog
datafile 75 switched to datafile copy "+DATA/rlabdb/datafile/data.341.696439773"

RMAN> recover datafile '+DATA/rlabdb/datafile/data.341.696439773';

Starting recover at 01-SEP-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=523 instance=rlabdb1 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 01-SEP-09

SQL> alter database datafile '+DATA/rlabdb/datafile/data.341.696439773' online;

Database altered.

And you are done !!!

Create IOTs Examples

Some examples on how to create Index Organized tables

1) create table iot_table (a int , b int, primary key (a))
organization index;

2) create table iot_table (a int , b int, primary key (a,b))
organization index tablespace users;

3) create table iot_table (a int , b int, constraint iot_table_pk primary key (a,b))
organization index tablespace users;