Tuesday, March 29, 2011

ORA-29339 and Configure db_nk_cache_size

While importing TTS we found below error:
ORA-29339: tablespace block size 32768 does not match configured block sizes

Then, on target DB we execute below:

SQL> show parameter db_32k_cache_size;

NAME TYPE VALUE
------------------------------------
db_32k_cache_size big integer 0

here value=0, so, (32768/1024=)32K cache in not congigured. Inorder to import this TTS, first we need to configure this.

According to oracle doc, before configuring this we need to consider below:
- We cannot configure db_nk_cache_size in db_block_size=n. We cannot configure db_32k_cache_size in db_block_size=32768.[ORA-00380]
- This parameter cannot be set to zero if there are any online tablespaces with an nK block size
- We need to consider Operating system-specific block size restrictions[ORA-00382]
We cannot set DB_32K_CACHE_SIZE if the operating system's maximum block size is less than 32 KB.
Also, you cannot set DB_2K_CACHE_SIZE if the minimum block size is greater than 2 KB.
- For 10g and later, nK-cache values are not automatically managed by Oracle and settings for them are subtracted from the total SGA_TARGET before the remainder is distributed amongst the other, automatically managed, SGA components (like the default cache, shared, large and java pools).
So, if SGA_TARGET=8GB and DB_32K_CACHE_SIZE=1GB, then 7GB of memory is available for distribution amongst the automatically-managed SGA components.
- For for 9i and earlier, we need to readjust other memory component size within sga_max_size [ORA-00384]

Our DB was a 9i DB, so we first shirnk db_cache_size by 50M and then set db_32k_cache_size=50M:

SQL> alter system set db_cache_size=158M scope=both;

SQL> alter system set db_32k_cache_size=50M scope=both;

SQL> create pfile from spfile;

No comments:

Post a Comment